By: Scott Murray | Updated: 2014-02-14 | Comments (2) | Related: > Analysis Services Development
Problem
The "WITH" keyword in SQL Server relational engine creates a Common Table Expression (or CTE); what does it do in SQL Server Analysis Services (SSAS)? Check out this tip to learn more.
Solution
The "WITH" keyword in SQL Server Analysis Services does not create a CTE as it does in the relational SQL Server engine. However, its results do share some similarities with the CTE results. The SSAS WITH keyword can create one of three results:
- a set
- a member or
- a measure
As with many functions, statements, and methods within SSAS, the WITH keyword does provide rich functionality, but also institutes some limitations.
- First, one of the main limitations with creating a calculated measure using "WITH Measure" is that this functionality is only available if your cube is in tabular mode, and thus we will not discuss in this tip; to understand difference between the tabular and multidimensional models please see Arshad Ali's two exceptional tips (http://www.mssqltips.com/sqlservertip/2818/understanding-the-sql-server-2012-bi-semantic-model-bism/ and http://www.mssqltips.com/sqlservertip/2821/getting-started-with-tabular-model-in-sql-server-2012--part-1/) which describe the differences.
- Additionally, as with any MDX, a cube designer needs to be fully aware of the score of your calculations; a value could easily be outside the scope level of a query and thus not return data.
Now that the caveats are out of the way, creating a named set (or measure) first and foremost provides a way to clarify a complex MDX query, just like a CTE. By defining the set within the WITH statement it also allows the query to only traverse over the values within that set. Also, the set defined within the WITH clause can be filtered, which again limits the set before it is placed on the SELECT statement axis used by the MDX query. Furthermore, since we are creating a set, we can cross join multiple dimensions when creating the set. Finally, the WITH statement lasts only as long as the query exists, similar to a CTE. The sets which are created can be defined as a specific set of values or can be created more relative or dynamic in nature as we will see in the below example.
SSAS WITH Statement
In order to see the full power of creating sets using the WITH statement, let's take a look at an example. All the examples for this tip will come from the AdventureWorksDW2012 SSAS database. This Analysis services database / cube is available on CodePlex (http://msftdbprodsamples.codeplex.com/releases/view/55330). First, let us examine the basic syntax of the WITH statement. As shown below, you first must specify the WITH keyword and then the SET keyword. Next, the set is given a name or alias. Then, the keyword AS is used and then the set specifications are defined; this area is where we specify the exact items we want returned in the set. Multiple tuples can be cross joined or even listed out individually within the set specification. Finally, we define the MDX select statement which lists out what items will get placed on each axis (rows and columns). Take a look Dallas Snider's tip, Comparison of Queries Written in T-SQL and SQL Server MDX, if you need to review the basics of MDX query writing.
WITH SET
AS
SELECTON COLUMNS, ON ROWS
FROM
WHERE
A basic example of this type of query is included below. In this query we are simply creating a named set called DeliveryMonthList which will return all members from the [Delivery Date].[Calendar].[Month] dimension and then places it on the rows axis of the MDX SELECT query. On the columns, we are placing the Reseller Sales Amount, Discount Amount, and Reseller Tax Amount measures.
WITH SET
DeliveryMonthList
AS
(
{[Delivery Date].[Calendar].[Month].Members}
)
SELECT
{
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount],
[Measures].[Reseller Tax Amount]
} ON COLUMNS,
NON EMPTY{[DeliveryMonthList]} ON ROWS
FROM
[Adventure Works]
A partial list of the results are included below. Notice how all Delivery Date Months are included.
To further expand this example, let's adjust our results to be a more complex set. In this instance, we will cross join the delivery date with the country field from the geography dimension.
WITH SET
DeliveryMonthList
AS
CROSSJOIN
(
{[Delivery Date].[Calendar].[Month].Members},
{[Geography].[Country].Members}
)
SELECT
{
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount],
[Measures].[Reseller Tax Amount]
} ON COLUMNS,
NON EMPTY{[DeliveryMonthList]} ON ROWS
FROM
[Adventure Works]
This query greatly expands the result sets as included below. Notice how our set is actually the intersection of each delivery month with each country in the geography dimension, including the "All Geographies" rollup. Of course, this set is quite portable to other queries, or we can actually filter it to exclude items such as the All Geographies dimension rollup member.
Using the same query and adding an EXCEPT statement within the set definition, as illustrated below, provides us with a way to exclude the All Geographies from the query results. The EXCEPT statement basically asks you to define the set and then define which values to exclude from that set.
WITH SET
DeliveryMonthList
AS
EXCEPT
(
CROSSJOIN
(
{[Delivery Date].[Calendar].[Month].Members},
{[Geography].[Country].Members}
)
,
CROSSJOIN
(
{[Delivery Date].[Calendar].[Month].Members},
{[Geography].[Country].[All Geographies]}
)
)
SELECT
{
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount],
[Measures].[Reseller Tax Amount]
} ON COLUMNS,
NON EMPTY{[DeliveryMonthList]} ON ROWS
FROM
[Adventure Works]
Now, our results include just the detail intersection for each delivery month and country. A partial list of these results are shown below.
Finally, we could dynamically create a set to see just the last 36 months of reseller sales (based on the periods available in the AdventureWorks database). In this example, I am using the BOTTOMCOUNT function to create a set with just the last 36 periods in the [Delivery Date].[Calendar] dimension. To make the example a little clearer, the EXCEPT statement was removed from the previous example, but we could still find the bottom 36 items in the set. One alternative to the BOTTOMCOUNT function would be to use the CLOSINGPERIOD function in a way similar to the following: CLOSINGPERIOD([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12): ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).
WITH SET
DeliveryMonthList
AS
BOTTOMCOUNT
(
{[Delivery Date].[Calendar].[Month].Members}
,36
)
SELECT
{
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount],
[Measures].[Reseller Tax Amount]
} ON COLUMNS,
{[DeliveryMonthList]} ON ROWS
FROM
[Adventure Works]
The above query returns the last 36 months of data as displayed below. Notice, though, that 2009 and 2010 have no values, and 2008 only includes half the year. We could remove the NULL values by adding the Non Empty clause; however, providing the full data set provides better clarity for this part of the example.
Conclusion
Creating a set using the WITH MDX keyword is an excellent way to generate a defined set for your MDX SELECT statements. By creating a set, you are able to create a cleaner and somewhat easier to troubleshoot MDX query. This cleaner view of the query matches the reason many DBA's uses CTE's in the SQL Server relational engine, which also begins by using the "WITH" keyword. The WITH keyword can also be used to create a calculated member or calculated measure. However the latter is only available if your cube is in tabular mode. Creating a set can be as simple or complex as desired; the set creation can include such items as filters, bottom and top counts, and except to name a few. As with most MDX, you must verify the scope of each of your query parts to be sure you are returning desired data.
Next Steps
- Check out Ray Barley's Building Calculated Members for SSRS Reports tip.
- Creating a Sessions Scoped Set - http://technet.microsoft.com/en-us/library/ms145976.aspx.
- Check out all the SQL Server Business Intelligence resources on MSSQLTips.com.
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
Article Last Updated: 2014-02-14
