Boston Code Camp: DAX Code Samples

Data Analysis Expressions (DAX), Excel, Power BI, Power BI Desktop, SQL Server Analysis Services (SSAS), SQL Server Data Tools (SSDT), SQL Server Reporting Services (SSRS) 2 comments

Hi,

The following five files contain sample DAX queries and functions from yesterday’s Introduction to DAX session at Boston Code Camp.

DAX Samples: Boston Code Camp

  • The .DAX query files and the Report Builder report used the Adventure Works sample database. The Power Pivot file uses Wide World Importers.
  • Per the slides you might go download DAX Studio for work with the DAX files and your dev/testing generally.
  • Be sure to read the comments in the code – some of the queries are intended to fail to explain a point

I believe the combination of several new DAX functions (e.g. NaturalInnerJoin) and variables along with the enhancements to SSRS will make DAX as a query language more and more valuable so I emphasized this with the examples.

In future blogs and possibly for my SSAS 2016 Tabular session at SQL Saturday Providence I may add more detail and supporting examples.

Please let me know if you have any questions or issues with these samples.

 

 

 

2 comments

  1. Hi Brett, I’ve been covering your blog since the fall, you’ve been quite prolific in the content youve been generating. I appreciate the content and levels of detail. Finally having the time to delve into the technical detail. May I ask, the contents of this DAX code – this looks to be querying a transnational (AW) database or a data warehouse, may be an ignorant question but I’m accustom to using DAX against a data model in PowerPivot or formal tabular data model in Visual Studio. Would another application of DAX be applying straight against an OLTP database? Also, doesn’t it matter what AW database I connect to to try these expressions? Thanks! Vinnie

    Like

    Reply

    1. Hi Vinnie,

      Thanks for your nice note – I remember you from the fall and hope all is well. The DAX queries are against SSAS 2016 Tabular models. I used the sample AdventureWorks DW 2016CTP3 and the Wide World Importers relational databases as the sources for different SSAS databases created on my machine – both import mode and Direct Query. One of the top features of SSAS 2016 and Power BI Desktop is Direct Query – models which translate DAX queries into SQL statements at query time and pass this to the source system. My SQL Saturday presentation PowerPoint and other blogs and docs go into the different tradeoffs with default import mode and design considerations but to answer your question Direct Query (from Tabular 2016+ or Power BI) does provide a viable means to apply DAX straight against an OLTP database (while retaining the benefit of a simple UI layer, security model, metadata, etc). You’d of course want to consider the implications of the read workload against the existing OLTP workload and you’d want the OLTP database and your DAX queries designed for acceptable performance (e.g. inner joins via ref integrity, in-memory and/or columnar tech, etc). To answer your final question my AW database isn’t 100% sample – I created a layer of views on top of it and used my own date table to support a more robust sample SSAS database so the DAX queries won’t work against the sample database directly. Also, I should mention that some of the DAX queries were written in such a way to avoid a bug with SSAS 2016 SP1 that was later addressed with SSAS 2016 SP1 CU1. Sorry for the delay in getting back with you. – Brett

      Like

      Reply

Leave a Reply