Convert MDX into DAX Part1

As we know DAX and MDX expressions can be used to query Tabular Model in Analysis services. Keep in mind that DAX does not work in SSAS Multidimensional Cube for querying data. We will understand basics of producing results from DAX same as MDX. DAX is promisingly giving quick result than MDX.  So if you are using Tabular model cube solution, then prefer writing DAX unless a complex logic came up which can only be resolved through MDX.

Let’s start with simple functions and queries to understand behaviour of DAX in terms of SQL as well as MDX.

Behaviour of DAX expressions:

• DAX is working as a SQL language with difference of functions and syntaxes
• We need not to write JOINS and GROUP BY clauses in DAX as it already understand that the purpose is to retrieve data in this form. Relationships among the tables of Tabular Model/ Power Pivot model are used for getting JOINS in the DAX query execution.
• We need to understand that DAX is not a query language, it’s a query expression. So we are using some table expressions in DAX to retrieve data in table format and further it is used as result-set/ dataset for the SSRS report.
• To evaluate DAX expressions use MDX query pane in SSMS

EVALUATE:

• First step to start with DAX Query Reference. This function can be used to get the data in result set from a table.

  EVALUATE <<Tabular Expression or Name of the table>>

  Argument to the Evaluate function can only be a table name or any expression that return a table. Evaluate   function is added up with different other functions (like VALUES, SUMMARIZE, ADDCOLUMNS, TOPN etc.) to produce different results as per requirements.

  e.g.  Get table data of Employee table in “AdventureWorks Tabular Model SQL 2012”.

  EVALUATE 'Employee'

  As per behaviour of DAX, this is similar to SQL as:

  SELECT * from [Employee]

  We cannot compare here MDX for this DAX, as there is no way of getting table level attributes in MDX. We need to specify attributes required in MDX SELECT statement.

SUMMARIZE:

• Function return required totals and counts, based on several groups (of different columns or say attributes from different tables in Tabular Model).

  We use SUMMARIZE along with EVALUATE for getting desired result in SSMS.

  SUMMARIZE (<<Table>>, <<Grouping Column1>>, <<Grouping Column2>>,,,<<Name for desired Aggregation1>>, <<Expression for Aggregation or Calculated MeasureName1>>, <<Name for desired Aggregation2>>, <<Expression for Aggregation or Calculated MeasureName2>>)

  Although it looks like a complex syntax but use is very simple and there are so many things we can do with this function.

  E.g. Let say you want to see Currency, Customer ID and Internet Sales from Adventure Works tabular model. Here a SQL query on database can be like:

  SELECT [CurrencyName], [CustomerID],SUM([InternetTotalSales]) FROM [InternetSales] A LEFT JOIN Currency B ON B.CurrencyID = A.CurrencyID LEFT JOIN Customer C ON C.CustomerID = A.CustomerID GROUP BY [CurrencyName], [CustomerID]

  Easy, but in MDX it can be as:

   

  SELECT NON EMPTY [Currency].[CurrencyName].[CurrencyName].MEMBERS* [Customer].[Customer Id].[Customer Id].MEMBERS ON 1 ,[Measures].[Internet Total Sales] ON 0 FROM [AdventureWorks_Test]

  Again easy.

  But in DAX, don’t worry it’s again easy. We need to keep in mind SUMMARIZE will need first Primary table (more of table which can be base of relationships among desired columns from different tables). Mostly these primary tables can be fact table, which relates different tables. Other than that JOINS and GROUB BYs are not required as SUMMARIZE take care of creating relationships and aggregating data for you.

  DAX for same situation is:

  EVALUATE SUMMARIZE('Internet Sales' ,'Currency'[CurrencyName] ,'Customer'[Customer Id] ,"Internet Sales",'Internet Sales'[Internet Total Sales]) ORDER BY 'Currency'[CurrencyName], 'Customer'[Customer Id]

  Here 'Internet Sales'[Internet Total Sales] is already a calculated measure, so Name for Measure is given as "Internet Sales". We can replace it as SUM([InternetTotalSales]), which is aggregation that we need.

  ORDER BY is same as SQL ORDER BY.

Most of the complex queries in DAX revolve around SUMMARIZE, so it’s important to understand its basics.

Quick start for SSRS Reporting using DAX

Till now I worked on environments of SSRS reporting through SQL and MDX queries (for getting datasets of report). But I was fascinated by the concept of creating SSRS report by DAX expressions.

Keep in mind few things:

1. DAX expressions can only be used against Tabular Model or Power Pivot (hosted in SharePoint) as Data source
2. DAX is not a query language, although here we are talking about a work around for getting datasets out of it. We need to understand DAX Query Reference, for writing tabular expressions in DAX. For more details go through the link and anyways I will post more examples on same in my upcoming posts.

So let’s quick start with SSRS reporting using DAXs:

1.  Open Visual Studio and create shared data source as in your local, which points to “AdventureWorks Tabular Model SQL 2012” (or any Tabular Model) database. My connection string is

“Data Source=.;Initial Catalog=”AdventureWorks Tabular Model SQL 2012″

Name of the shared data source is “AW2012TabularModel”

2.  Now create dataset (or shared dataset) for new report.

3.  While creating dataset, use shared data source AW2012TabularModel as shown below:

• 

4.  Now for query of dataset use Query Designer as shown in snapshot above

5.  At first you get an option  to create MDX query dataset, but instead of that use DMX query editor as shown below:

• 

6.  In DMX Query you will get option to prepare a mining query but it also execute DAX Expressions (so this is the work around!!). For writing DAX query, click on Design Mode view (shown below in screenshot) and write small DAX to test.

• 

7.  Smallest DAX tabular expression can be

EXECUTE ‘DATE’

It will result in all the data from ‘Date’ table from the Tabular Model. Also corresponding SQL will be:

SELECT * FROM [DATE]

 8.  Above query will give all the columns of the table ‘Date’ as fields for the dataset, which can be used further in SSRS report (any tabular, matrix, or chart) and parameters.

So this is how we can start creating SSRS report with DAX tabular expressions (which are responsible for resulting in Datasets). But a question here is that “How to create a report in DAX similar to MDX?”.

Well answer for that will be: Create DAX equivalent to MDX of your requirements. Result of this can be used to create any sort of report. DAX runs as an SQL and to get a result similar to MDX is quite a work. I will cover few  topics related to this conversion in coming posts. Please enjoy this post and appreciate if you liked. 🙂