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