[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.