OK, first thing first. We all know the famous Adventure Works multidimensional sample project. To learn the tubular mode/DAX, we also have a sample tabular project.

Download sample database and projects from CodePlex

You can follow this link to CodePlex to download the following files: tinyurl/AdventureWorks2012.

• AdventureWorksDW2012_Data.mdf
• AdventureWorks DW Tabular Model SQL Server 2012.zip
• AW Internet Sales Tabular Model.zip
• AdventureWorks Multidimensional Models SQL Server 2012.zip (also download this multidimensional sample project while you are there for leaning SSAS/MDX, if you haven’t done so.)

I suggest that you unzip all the files onto a one level folder on your C drive, instead of the Windows built-in Downloads folder or the Documents folder. If you have seen the annoying “Path too long” error when you copy/past or unzip folders with very deep levels, you will appreciate my suggestion.

On Window 7 and 8, also make sure that unblock all the folders/files, and your user ID has full control on them.

Assume you have installed SQL Server 2012

I’ll assume that you have installed the following using the SQL Server Developer Edition or Enterprise Edition 2012:

• SQL Server 2012 Database Engine (server instance): this is the relational database instance.
• SQL Server 2012 Analysis Services – Multidimensional model (server instance): this is not required for learning DAX, but it can be extremely important to learn MDX and DAX side-by-side.
• SQL Server 2012 Analysis Services – Tabular model (server instance)
• SQL Server 2012 Management Studio (desktop tool): older version of SSMS does not support Analysis Services tubular model.
• SQL Server Data Tools for Visual Studio 2012 (desktop tool): if you have Microsoft Visual Studio Professional 2012, you should get SSDT and also

To keep things simple, I have installed all three instances on my laptop. The database engine is the default instance, and both the multidimensional model and the tabular model are named instances. I have chosen to name them as:

• localhost
• localhost\MULTI2012
• localhost\TABULAR2012

To make things easy for myself, I always add my own user ID as the administrator for each instance.

(Disclaimer: do not use this type of architecture on your production server.)

Also need PowerPivot

While you are at the installation, also make sure that you have PowerPivot. PowerPivot is the desktop tool for analyst for creating the tubular model, which is eventually consumed by Pivot Table for reporting.

You can run PowerPivot in either Excel 2010 or Excel 2013. If you have Excel 2013, you are all set because PowerPivot is included by default. If you are still running Excel 2010 like me, follow this link to download the free Power Pivot add-in.

www.powerpivot.com

Attach AdventureWorksDW2012_Data.mdf

A typical multidimensional model is built on top of a relational star schema data warehouse. To create a tabular model, we also need data source(s). The most frequently used data sources are the common relational database systems, text files, and excel workbooks. The Adventure Works DW 2012 is the relational star schema database the sample tabular model database uses.

This .mdf file you have just downloaded does not have the matching log file. You can use the following T-SQL command with the option of FOR ATTACH_REBUILD_LOG to attach the database to the SQL Server database instance.

CREATE DATABASE AdventureWorksDW2012EE

ON (FILENAME = N’C:\Adventure Works for SQL Server 2012\AdventureWorksDW2012_Data.mdf’)

FOR ATTACH_REBUILD_LOG;

I’ve named the new database AdventureWorksDW2012EE for Enterprise Edition. This database and the data will be the source of both of my tubular and multidimensional model.

I don’t need to worry about backing up the log for this database, so I also made sure that the Recovery Model is set to Simple.

To learn more about how to attach a database, please refer to this TechNet article, Attach a Database.

Create the Adventure Works DW Tabular Model Database

If you are familiar with Analysis Services, you’d know that Analysis Services database can only be created by deploying the correspondent SQL Server Data Tools project. Unlike the SQL Server relational database, you will not be able to attaché nor import an Analysis Services database.

Fortunately SSDT tabular project you have downloaded is ready for deployment. After you have successfully deployed a tubular model project, you should be able to see two tubular model databases from SSMS.

Once you open the tabular model sample project in SSDT, be sure to make the following adjustments to ensure a smooth deployment.

To begin, right-click on a SSDT 2012 solution, and go to Add – Existing project…. Then find the following sample tubular project you just downloaded.

AdventureWorks Tabular Model SQL 2012.smproj

1.      Test Workspace server connection

As soon as you attempt to open the existing tubular project, you will be asked to identify an Analysis Services instance to use for authoring tabular model projects. I entered localhost\TABULAR2012, and also click Test Connection. Any failure of the connection must be resolved before you can continue. Connection failures mostly indicate permission problems with your Analysis Services instance. To keep things easy on my own laptop, I’ve chosen to use my own user ID to start the Analysis Services tabular model instance, instead of the default service account (again not recommended for your-know-what).

2.      Be sure to check the existing connection to the relational database

What I need to make sure is that the underneath relation database is pointing to my AdventureWorksDW2012EE database on localhost.

Go to menu MODEL – Existing Connections – Edit.

After modify the database name, click Test Connection again to test it. At this point, I also want to change the credential for Impersonation to use my own user ID, instead of the service account.

3. Be sure to modify the tabular project property page

I also need to make sure I have the right Analysis Services instance for the deployment.

Go to the menu PROJECT – Properties…. I modified the Server name to be localhost\TABULAR2012, and accepted the default database name “AdventureWorks Tabular Model SQL 2012”.

4. Process a table

Assume that you have successfully opened the sample project, and both the connections to the relational database and the tabular database are correct. Now you should have all the dimension tables and fact tables imported into the project. Before you deploy the project, try to process one table. With one table highlighted in the model, go to MODEL à Process à Process Table. All is good, the processing will complete without error.

Processing data means getting the latest data from external sources. The test is to make sure that your source connection works as planned.

A bit more information about processing data. When authoring your model project in SSDT, you can only manually initiate the process actions. After a model has been deployed, process operations can be performed by using SQL Server Management Studio or scheduled by using a script.

Now deploy the tabular project by right-clicking on the project, and choosing Deploy. If all is good, you should see this dialog when the deployment is complete.

Validate the deployment in SSMS

Now go to SSMS, you should see two tabular model databases as shown in the screenshot in the previous section.

Let’s open up a MDX query window by right-clicking on AdventureWorks Tabular Model SQL 2012, and choose Mew Query – MDX.

The DAX function EVALUATE is kind of like the SELECT * FROM command in T-SQL. It can be used to get the data in result set from a table.

Try this and you will see all the customers returned:

evaluate Customer

If you are familiar with MDX, the following MDX query should also work.

select

{} on 0,

[Customer].[First Name].[First Name].members on 1

from

[Internet Operation]

Now that the installation of the sample Adventure Works tabular model database is done, you are all set to learn DAX.