Learn more about SQL Server tools

 

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 Service Named Sets using the WITH Keyword


By:   |   Read Comments (1)   |   Related Tips: > 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:

  1. a set
  2. a member or
  3. a measure

As with many functions, statements, and methods within SSAS, the WITH keyword does provide rich functionality, but also institutes some limitations.

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


SELECT
ON 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.

Basic Query Results

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.   

Complex query

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.  

Except Results

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. 

Filter by Date

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


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     



Friday, February 14, 2014 - 9:02:00 PM - Tegham Back To Top

Hi,

nice introduction !

if you don't want the all members, why not just choose the correspondent sets that does not include them !

Try the following more consice definition:

 

WITH SET DeliveryMonthList

AS ([Delivery Date].[Calendar].[Month],[Geography].[Country].[Country])

 

Philip


Learn more about SQL Server tools