Wednesday, August 8, 2012

SSAS Tabular & DAX intro for T-SQL Folks

This post is intended to introduce a simple SSAS Tabular project and Data Analysis Expressions (DAX) to those who know Transact SQL (T-SQL) well.

This post assumes you have installed SQL Server 2012 DB Engine and SSAS Tabular on your machine.  Please use a sandbox development machine to do this.

I created two simple OLTP tables that you can use.  Usually a separate Product table and even a Date table with hierarchies should be used but for this illustration we are going to keep it simple.

After you download the script and run it on a test database on your sandbox development server you can create an SSAS Tabular Model.

1. Click>Start>All Programs>SQL Server 2012>SQL Server Data Tools (SSDT)

2. In SSDT, click File>New>Project and the following window should appear:

3. Make sure to select Analysis Services Tabular Project Under Business Intelligence and select a location and name that works for you.  I picked the default location and used the name DAXForTSQLFolks for the project.

4. The project will create an empty model.  Navigate to the menu and click Model>Import From Data Source>Microsoft SQL Server.

5. Enter in the credentials for the SQL Server and database that you created the tables in from above, and a server friendly name.  I use the name of the server with a prefix of DEV -, QA -, UAT - , PROD - in case I ever forget what I'm working with for friendly names.

6. Enter the username and password to connect to the SQL Server.

7. Select the two tables Sales and Salesperson to import and click Finish. You should see the tabular model with data.  Looks similar to Powerpivot doesn't it?

8.  Lets create one measure in the SSAS Tabular model.

9. Click on the sum button (For Measures) with the SaleID column still highlighted as above and select count.  You will notice it places a measure in the grid below the SaleID column as "Count of SaleID:=COUNTA([SaleID])".

10. You've just created a measure that is basically the number of sales.

11. You are now ready to deploy the SSAS Tabular database to your sandbox development server.  In Solution Explorer (usually on the right side of SSDT) find the project and right click it to display properties.

12. Click on deployment and make sure the server is set correctly to deploy the tabular model.  I've changed the Cube Name from "Model" to "LearnDAXCube".  All other defaults are fine for this exercise.

13. Click Build and select Deploy "DAXForTSQLFolks" or however you named the project.

14.  Open SQL Server Management Studio (SSMS) and connect to Analysis Services and your server name.

15.  You can browse the cube by right clicking the "LearnDAXCube" in the Object Explorer (usually on the left side of SSMS) and drag and drop "Count of SaleID" and the associated dimensions to get familiar with slicing and dicing the data.

You are now ready to write some DAX.

Open SSMS and a new MDX query window.  Yes this is confusing. There is no DAX query window yet in SSMS. I can read your mind and yes I think this is a bad design as well but sure it is probably coming.

Download the DAX script from below (its saved as .sql file so box will format it) and paste it in the new MDX window and you can cross reference with the below for how to perform common SQL functions in DAX.

--1 How do I perform a "select" in DAX? The equivalent is "evaluate".

--2 How do I perform a "where" in DAX? The equivalent is "filter".

--3 How do I perform a "join" in DAX? The equivalent is done by summarize, crossjoin, calculate, etc.    There is not one way here but a few.  In the example provided here I combine sets by using summarize.

--3/4 How do I perform a "group by" in DAX?  The equivalent is summarize.

--4 How do I perform a "count" in DAX?  The equivalent is count, countx, counta , distinctcount.

--4 How do I perform a calculation in DAX?  The equivalent is just like SQL except with DAX functions.

As you can see from (4) most salespeople have success selling Miami Hurricanes Hats; that's probably because they are 5 time National Champions and have the most active players in the NFL.  Go Canes! :-)

1 comment:

  1. I can see that you are are genuinely passionate about this! I am trying to build my own website and youve helped me with some great