Microsoft Azure Stack is an extension of Azure—bringing the agility and innovation of cloud computing to your on-premises environment and enabling the only hybrid cloud that allows you to build and deploy hybrid applications anywhere. We bring together the best of the edge and cloud to deliver Azure services anywhere in your environment.

PowerPivot for the DBA: Part 3

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 (part 1, part 2) that equates Transact-SQL to PowerPivot.

Scope

As discussed in this previous blog post, a measure is passed the rows of the cell it is evaluating, the scope of the cell in the PowerPivot table. Which works really well if you are summing a single cell in that scope. However, what if you want to get a ratio between this scope and a larger scope, like that of the row the cell is in, or the whole PivotTable?

Measures have the ability to reach outside of their scope and draw in information from the bigger picture. If a cell is the PivotTable is a set of rows that are in the Bike category and the have an order date of 7/1/2001 the measure has access to all the rows that are in the Bike Category, and all the rows with an order date of 7/1/2001, it even has access to all the rows in the whole PivotTable.

The ability to access more than just the local scope is the power of the measure in PowerPivot. This feature gives the user insight into to how the cell data compares to other tables in the PivotTable.

ALL

In Data Analysis Expressions (DAX), the language used in the measure formula, ALL returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. Here is an example of using ALL to calculate the ratio of total line item sales in the cell to the total line item sales for the category:

Find the division in the formula, to the left is a formula that we already discussed in this previous blog post; it sums all the LineTotal columns in the scope of the cell. To the right of the division is the interesting part of the formula, it invokes the CALCULATE keyword to change the scope of the summation. CALCULATE evaluates an expression in a context that is modified by the specified filters. In this case those filters are all the rows returned from the result in this particular row in the PivotTable. In the example above this is the order date. Here is what the results look like:

Transact-SQL

Now let’s get the same results with Transact-SQL, this turns into a 200 level transact-SQL statement, because of the nested SELECT used as a table (T1) to get the summation of the LineTotal column per date.

This returns all the right results, however it isn’t very pretty compared to the PivotTable in Excel. The percents are not formatted, the results are not pivoted, there are no grand totals and the data isn’t very easy to read.