By: Scott Murray | Comments (1) | Related: > 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.
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.
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.
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.
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.
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
- Review SSAS dimensions - https://technet.microsoft.com/en-us/library/ms174527%28v=sql.110%29.aspx
- Interview questions on dimensions - /sqlservertip/2683/sql-server-analysis-services-interview-questions-on-dimensions-hierarchies-and-properties/
- Check out all SQL Server Business Intelligence Resources.
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
