Loading the player...
See the power of Data Analysis Expression (DAX) language in PowerPivot, and in SQL Server Data Tools (SSDT), in this free, 20-minute video by Alberto Ferrari, a renowned book author on the subject, consultant at sqlbi.com, and the winner of the Best Overall Session at Microsoft TechEd Europe 2012.
DAX has been introduced in PowerPivot in 2010 and, in 2012, it has been integrated in SQL Server 2012 Analysis Services (SSAS) Tabular Mode, which examplify the new BI Semantic Model (BISM). One of the main reasons for the creation of this new language was the introduction of a new, simpler, columnar modelling paradigm in both PowerPivot for Excel, and in SSAS Tabular (0:41). Together, they leverage the incredibly fast xVelocity In-memory Analytics Engine: a powerful columnar database that is able to store billions of rows of data in a compact way, leading to ultra-fast, yet ﬂexible, querying and calculating options.
In the ﬁrst demo shown in this video, we create a simple data model (1:58), based on the AdventureWorks sample database (download here). Instead of querying it in just a few simplistic ways, we show you how to write a much more challenging formula: counting how many new and returning customers make a purchase every month (7:18). First, you learn how to count customers using DISTINCTCOUNT (8:17). Counting new customers requires the use of DAX expressions such as: CALCULATE, FILTER, and COUNTROWS (9:24).
PowerPivot for Excel is particularly useful for quick prototyping of your tabular expressions, before deploying them to SSAS for security, partitioning, and other reasons, which are explained in depth in this video, which is part of our Introduction to SQL BI online course.
As simple as it seems, this calculation required complex multidimensional modeling in previous versions of SSAS, leading to the need to modify the original data structures through some ETL code. Using DAX, it wasn’t even necessary to change the data model. Working on the simple, relational star-schema structure of the AdventureWorks data warehouse, we just wrote a slightly more complex DAX code that solved the problem without requiring a single step of ETL. Writing such DAX is not an easy task at ﬁrst, it requires the attitude to learn, digest, and master the philosophy of DAX, which is quite diﬀerent from the query language of previous versions of SSAS: MDX (Multidimensional Expressions).
Thinking in DAX represents a new attitude. If you would like to gain this skill, this video, ﬁrst one in our new series focusing on DAX, shows you what could be accomplished without having to change the model of your data. Learning how to write DAX is a more advanced task, which we will cover in subsequent videos.
We also recommend Alberto and Marco’s book, co-authored with Chris Webb: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Modes.