PowerPivot for the DBA: Part 3

[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 (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:

 =SUM(SalesOrderDetail[LineTotal])/CALCULATE(SUM(SalesOrderDetail[LineTotal]),ALL(ProductCategory))

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.

 SELECT    ProductCategory.Name, SalesOrderHeader.OrderDate, 
    SUM(LineTotal)/ MAX(T1.ProductCategoryTotal)
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
    INNER JOIN (
        SELECT SUM(LineTotal) 'ProductCategoryTotal', SalesOrderHeader.OrderDate
        FROM    Sales.SalesOrderHeader
            INNER JOIN Sales.SalesOrderDetail ON 
                SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
        GROUP BY SalesOrderHeader.OrderDate        
        ) AS T1 ON     SalesOrderHeader.OrderDate = T1.OrderDate
GROUP BY ProductCategory.Name, SalesOrderHeader.OrderDate
ORDER BY SalesOrderHeader.OrderDate

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.

Here is the Transact-SQL to pivot the table:

 SELECT OrderDate, [1] AS Bikes, [2] AS Components, [3] AS Clothing,
    [4] AS Accessories
FROM 
(SELECT    Sales.SalesOrderDetail.LineTotal/T1.ProductCategoryTotal 'LineTotal',
     ProductCategory.ProductCategoryID,
     SalesOrderHeader.OrderDate
    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
        INNER JOIN (
            SELECT SUM(LineTotal) 'ProductCategoryTotal', SalesOrderHeader.OrderDate
            FROM    Sales.SalesOrderHeader
                INNER JOIN Sales.SalesOrderDetail ON 
                    SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
            GROUP BY SalesOrderHeader.OrderDate        
            ) AS T1 ON     SalesOrderHeader.OrderDate = T1.OrderDate
        
) p
PIVOT
(
    SUM(LineTotal)
    FOR ProductCategoryID IN ( [1], [2], [3], [4] )
) AS pvt
ORDER BY pvt.OrderDate;

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.