By: Paul Schuliger
During a recent reporting project, I came across a situation in which the solution was to provide the reporting tool (Power BI sourcing from an SSAS tabular model) a sort order for each level of a dimensional hierarchy. Without a sort order for each level of a hierarchy, the results in the report were not properly ordered.
Even though there were (and are) many ways to accomplish this in SQL, I wanted to use SQL Window functions to do the job…
And doing so makes for good blog content as well!
For easy accessibility, the examples featured in this article use the Microsoft AdventureWorks database. The Sales Territory table and the Country Region table provide the necessary data to illustrate the SQL Window functions of ROW_NUMBER, DENSE_RANK, FIRST, and LAST. The hierarchy within the data is as follows:
Territory Hierarchy Levels
Level 1 = Sales Territory Group
Level 2 = Sales Territory Region
Level 3 = Sales Territory (lowest level)
The first SQL statement (SQL #1) returns the data in the order of the Level3 ID, which is the primary key of the table. Notice that the ID does not sort the results at the lowest level in an alphabetical fashion, which is what we are going to do to illustrate the SQL Window function. And yes, we could use the ORDER BY clause to sort alphabetically and be done, but that is not as much fun as learning how to use the ROW_NUMBER and DENSE_RANK Window function!
Problem
SQL #1 – Data is in Leaf level order (Territory ID) and no Window function
|
1 2 3 4 5 6 7 8 9 10 |
SELECT A.[TerritoryID] AS Level3_ID , A.[Group] AS Level1 , B.[Name] AS Level2 , A.[Name] AS Level3 , A.[SalesYTD] , A.[SalesLastYear] FROM [AdventureWorks2012].[Sales].[SalesTerritory] A INNER JOIN [AdventureWorks2012].[Person].[CountryRegion] B ON A.CountryRegionCode = B.CountryRegionCode ORDER BY A.[TerritoryID] |
Solution
SQL #2 – Data is in correct Sort Order for each hierarchy level
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT A.[TerritoryID] , A.[Group] AS Level1 , DENSE_RANK() OVER(ORDER BY A.[Group]) AS Level1_SortOrder , B.[Name] AS Level2 , DENSE_RANK() OVER(ORDER BY B.[Name]) AS Level2_SortOrder , A.[Name] AS Level3 , ROW_NUMBER() OVER(ORDER BY A.[Name]) AS Level3_SortOrder , A.[SalesYTD] , A.[SalesLastYear] FROM [AdventureWorks2012].[Sales].[SalesTerritory] A INNER JOIN [AdventureWorks2012].[Person].[CountryRegion] B ON A.CountryRegionCode = B.CountryRegionCode ORDER BY DENSE_RANK() OVER(ORDER BY A.[Group]) , DENSE_RANK() OVER(ORDER BY B.[Name]) , ROW_NUMBER() OVER(ORDER BY A.[Name]) |
So how do the SQL Window functions work? Let’s start with ROW_NUMBER(). The syntax looks like the following:
|
1 |
ROW_NUMBER() OVER(ORDER BY A.[Name]) AS Level3_SortOrder |
The function call (ROW_NUMBER()) purpose is to provide a distinct number for each row returned in the resultset. The OVER(ORDER BY A.[Name]) tells the ROW_NUMBER() function to spread the numbers over the resultset using the [Name] field. The ROW_NUMBER() function is used here because it is known that the Territory Name is distinct within the resultset.
The ROW_NUMBER() function will not provide the correct results for this solution’s purposes if applied to the Level 1 or Level 2 fields, as it would provide an individual row number for each value. However, the DENSE_RANK() provides the functionality that is needed.
The DENSE_RANK() function provides a number for each unique value for the field that its OVER() is applied to. In this case, Level 1 ends up with 3 distinct values and Level 2 ends up with 6 distinct values, with each level sorted in alphabetical order:
Now in Power BI, the Level X Sort Order fields can be used to sort the Level X fields. Though my usage was in a tabular model, you can also do the same when importing the query into Power BI on the Data panel (as shown below). Select the column that needs to be ordered and then choose the sorting column using the Sort by Column functionality on the Modeling tab.
Select the field that needs to be sorted:
Select the Sort by Column functionality in the Modeling tab:
Select the field to use for sorting (in this case Level1_SortOrder):
And that’s how easy it is to use a SQL Window Function! Next time we will cover some other Window functions to demonstrate their power in helping solve data problems. Don’t hesitate to leave us a comment if you have any questions!
Keep your data analytics sharp by subscribing to our mailing list
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!