Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Dimensions, Levels and Hierarchy


By:   |   Read Comments (1)   |   Related Tips: > Analysis Services Development

Problem

I would like to get a better understanding of my cube data and would like to know if there are ways to interrogate the level, hierarchy, and dimension for a SQL Server Analysis Services cube using MDX.

Solution

Often as report and cube developers begin to design queries in MDX, they will need to determine or set the level or hierarchy of the set or tuple they are querying. MDX provides three main functions which can assist in this type of query. First you have the dimension which serves as a method for getting or setting the dimension in use. Next you have hierarchy which provides methods to evaluate and determine the hierarchy placement. Finally the level function determines the step or position of an item in the structure of a dimension or hierarchy.

We will use the Adventure Works databases as the basis for our SSAS cube processing and reporting. The 2014 versions of the regular and data warehouse databases, along with the SSAS cube database backups are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server and SSAS databases, we will subsequently use the SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 and SQL Server Management Studio to develop queries. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

SQL Server Analysis Services Level Function

The Level function and its cousin function, Levels (with an s) allow a MDX query writer the ability to navigate the various levels of a dimension. The basic syntax of the commands are as follows:

  • LEVEL -- Member_Expression.Level -- for this function, you specify a Member Expression
  • .
    • Returns the name of the level belonging to the noted member expression.
  • LEVELS -- Hierarchy_Expression.Levels( Level_Number ) / Hierarchy_Expression.Levels( Level_Name ) - for this function you specify a Hierarchy or Dimension Expression and you specify either a level number or a level name.
    • Returns the members of the level specified
    • Level number argument is 0 based.

As you can see, these two functions actually work somewhat in opposite directions. Let us look at a few MDX query examples to clarify its use.

WITH
MEMBER MEASURES.LEVELNAME AS [Date].[Calendar].LEVEL.Name
SELECT
{
MEASURES.LEVELNAME
}
ON COLUMNS ,
{
[Date].[Calendar].LEVELS(1),
[Date].[Calendar].LEVELS('Calendar Semester')
}
ON ROWS
FROM
[Adventure Works]

The above query does several things which we can run through. First, lines 1 and 2 creates a calculated member; here we are using the [Date].[Calendar] hierarchy and requesting the query return the member name for the designated hierarchy via the LEVEL function. Specifically, we are requesting that the name be returned on the columns part of the results. Second, the next 5 lines just designate this calculated member to be displayed on the columns. Finally, all the remaining lines except the last 2, use the LEVELS functions to return, for each row, the members of the [Date].[Calendar] hierarchy that first belonging to level 1 (remember the levels are 0 based and start at n-1 levels back) of the hierarchy and then second, the Calendar Semester Level (aka Level 2). Notice how we use the two variations for the available arguments of the levels function, one using the level number, and one using the level name. A partial list of the results is included below.

Level and Levels

Notice in the above screen print, level 1 refers to the Calendar Year level while listing the related member values for the years, such as CY 2012. The second levels expression refers to the Calendar Semester level. This level matches the semester listing, such as H1 CY 2005 and H2 CY 2007. The matching level names are listed next to each member value. These values can easily be used to list varying levels within an MDX query; as shown next we could easily add the Internet Sales Amount to our query.

WITH
MEMBER MEASURES.LEVELNAME AS [Date].[Calendar].LEVEL.Name
MEMBER MEASURES.InterentSalesAmt AS [Measures].[Internet Sales Amount]
SELECT
{
MEASURES.LEVELNAME,
MEASURES.InterentSalesAmt
}
ON COLUMNS ,
{
[Date].[Calendar].LEVELS(1),
[Date].[Calendar].LEVELS('Calendar Semester')
}
ON ROWS
FROM [Adventure Works]

This query results in the inclusion of the Internet Sales Amount at each of the levels requested with a sample set of results shown below.

levels and ISA

 

SQL Server Analysis Services Dimension Function

In a similar fashion, the Dimension function allows us to explore the various components of a dimension hierarchy, level, or member. It can work similar to the level and levels functions, in certain circumstances. The basic syntax is as follows: Hierarchy/Level/Member_Expression.Dimension.

Adjusting our previous query, we can now explore the component of a dimension. In the below code, we retrieve the dimension name for two levels of our dimension and then, as a bonus, we also return the number of dimension attributes in each level.

WITH
MEMBER MEASURES.DimNAME AS [Date].[Calendar].Dimension.Name
MEMBER MEASURES.DimLEVEL AS [Date].[Calendar].Dimension.Level.Count
MEMBER MEASURES.InterentSalesAmt AS [Measures].[Internet Sales Amount]
SELECT
{
MEASURES.DimNAME,
MEASURES.DimLEVEL,
MEASURES.InterentSalesAmt
}
ON COLUMNS ,
{
[Date].[Calendar].LEVELS(1),
[Date].[Calendar].LEVELS('Calendar Semester')
}
ON ROWS
FROM [Adventure Works]

As shown in the below screen print, the dimension function interacts with the name and count functions to provide the related dimension name and the number of members within each specified dimension level. These values could easily be used as input for other parts of an MDX query.

dimension level

 

SQL Server Analysis Services Hierarchy Function

The final related function in our group of functions is the hierarchy function. This function is quite similar to the dimension function except that it returns the hierarchy information and not the dimension information. Adjusting our query to use the Hierarchy function, we can again review the hierarchy name and count. The syntax for the hierarchy function is: Member_Expression/Level Expression.Hierarchy.

MEMBER MEASURES.HierarchyNAME AS [Date].[Calendar].Hierarchy.Name
MEMBER MEASURES.HierarchyLEVEL AS [Date].[Calendar].Hierarchy.Level.Count
SELECT
{
MEASURES.HierarchyNAME,
MEASURES.HierarchyLEVEL
}
ON COLUMNS ,
{
[Date].[Calendar].LEVELS(3),
[Date].[Calendar].LEVELS('Calendar Semester')
}
ON ROWS
FROM [Adventure Works]

As illustrated next, the hierarchy functions results again give us the ability to interact with the hierarchy level and name; these values could in turn be used to filter or limit a larger MDX query.

hierarchy results

Another cousin function, this time to the hierarchy function, is the Hierarchize function which will order a set as a hierarchy. Using the post argument within the hierarchize function, the set will be ordered with the children members before their related parents. However, if the post argument is not included, the child members will follow their parent. The following is a simple example of using the hierarchize function to create a hierarchy of Product Sub Category.

SELECT
Hierarchize
(
[Product].[Subcategory].CURRENTMEMBER.CHILDREN
)
ON COLUMNS
FROM
[Adventure Works]

The hierarchize set is shown below.

hierarchize set

Conclusion

Within MDX a set of functions exists which allow for the exploration and review of the various dimensions, hierarchies, and levels that permeate any cube. These functions let you interrogate a dimension and its members to determine the various level names and what level on a hierarchy it resides. Furthermore, a list of dimension members can be obtained or a count of the dimensions can be calculated. These functions can work in unison with each other to convey full details about a dimension and its related hierarchies and levels.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, June 10, 2015 - 1:22:54 AM - Ken W Back To Top

Another great SSAS article! Thanks Scott.


Learn more about SQL Server tools