PowerPivot for the DBA: Part 2
[This article was contributed by the SQL Azure team.]
In this article I am going to continue to tie some simple terminology and methodology in business intelligence with Transact-SQL – bring it down to earth for the DBA. This is a continuation in a series of blog posts that I started that equates Transact-SQL to PowerPivot.
If you’re not an OLAP guru, you might be baffled by the frequent references to measures in PowerPivot. Basically, a measure in PowerPivot is a predefined calculation that understands the scope of the cell that it is being evaluated in. This is much like a function with Table-Value parameters in Transact-SQL. Conceptually it is like a function in any language, a predefined calculation that takes input and produces output based on that input. The language in a measure is Data Analysis Expressions (DAX).
The dialog in which the measure is defined has multiple entry points:
- “New Measure” button in the PowerPivot ribbon
- Right-click menu item for each table in the field list that says “Add New Measure...”
- Right-click menu item for each measure in the field list that says “Edit Formula”
- Right-click menu item for each measure in the Values well below the field list that says “Edit Measure...”
Choosing any of these entry points will bring up the Measure Settings dialog that allows you to specify the name and formula for your measure, and that dialog looks like this:
This measure is then associated with the table that you created it in (Table name drop down above).
DAX is very similar to an expression in Excel; however it has additional attributes for dealing with scope. In fact, a measure can only be used in a PivotTable, because only a PivotTable has the concept of scope.
Understanding scope is the key to understanding measures. When I talk about scope, I am taking about the number of rows sent to the measure by PowerPivot. For example, in my previous blog post I was using the Adventure Works database to create a PowerPivot example that looked like this:
Which is the same data as this Transact-SQL Statement containing a GROUP BY statement with both OrderDate and ProductCategory.Name:
SELECT ProductCategory.Name, SalesOrderHeader.OrderDate, SUM(LineTotal) FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID INNER JOIN Production.Product ON Product.ProductID = SalesOrderDetail.ProductID INNER JOIN Production.ProductSubcategory ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID INNER JOIN Production.ProductCategory ON ProductSubcategory.ProductCategoryID = ProductCategory.ProductCategoryID GROUP BY ProductCategory.Name, SalesOrderHeader.OrderDate ORDER BY SalesOrderHeader.OrderDate
If I was using measures, the scope sent to the measure in this case is all the rows that match the GROUP BY and all the columns of the inner join that a SELECT * would return. The measure would then be evaluated for every cell in the PivotTable. So for July 1, 2001 and the category Accessories in the example above that would be 37 rows that looked like this:
All 37 rows would be sent to the measure to evaluate for this cell. My example Sales measure just sums the LineTotal column, which is the same result as letting PowerPivot sum the column in the first example.
If I used the Sales measure I created above the PowerPivot table would look like this:
Just like other languages, the measure (or function) can be changed and all the cells will update automatically. The measure can be thought of as a naming abstraction between PowerPivot and the calculation. In fact you can use the Sales measure in multiple PivotTables, or PivotCharts.
The DAX language has built in functions to change the scope of the results within the measure. You can filter the results or expand the scope to include more rows. This really is the power of a measure, the ability to take the cells scope and compare it to an expanded or reduced scope.
In my next blog post in this series I will show how to take and expanded scope in PowerPivot and create a measure that computes a ratio between the cells scope and the product category the cell is in. Plus, I will give you the same results in Transact-SQL.
Do you have questions, concerns, comments? Post them below and we will try to address them.