Dynamic Named Sets in SQL Server Analysis Service 2008
By : Kasim Wirama, MCDBA
One of improvement in area of Analysis Service 2008 is dynamic named sets feature in MDX (multi dimensional expression). DMX language is used by analysis service client to interact with Analysis Service Engine. There are several scope in named sets, i.e. : query scope, session scope (possibly several query in one session), or cube scope (the named sets is defined inside cube with MDX scripting). In previous version before SQL Server 2008, named sets is static now in SQL Server 2008, you have options whether to define the named set static or dynamic.
Below sub section, I present problems on static named sets as reasons why dynamic named sets comes in handy.
Problem of static named sets and solution with dynamic named sets
Let see the following example of static named sets:
CREATE SET [adventure works].[NegativeGrossProfitMargin] AS
FILTER([Product].[Subcategory].[Subcategory] HAVING [Measures].[Gross Profit Margin] < 0);
The named sets above (NegativeGrossProfitMargin) is to display member of subcategories that have negative gross profit margin. The static named set is defined in Cube.
Then, the static named sets is used in the following query issued from Excel 2007 as one of SSAS client tool:
SELECT
[Measures].[Gross Profit Margin] ON 0,
NON EMPTY(NegativeGrossProfitMargin) ON 1
FROM [Adventure Works];
The results for DMX query is Caps, Jerseys and Touring Frames. Now, I slice with WHERE to display negative margin profit in 2004 for these subcategories with the following query:
SELECT
[Measures].[Gross Profit Margin] ON 0,
NON EMPTY(NegativeGrossProfitMargin) ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];
The result for above query is Caps, Jerseys and Touring Frames. The results is incorrect, because Jersey and Touring Frames are not in negative gross profit margin in 2004. That’s the problem of static named sets. The query just ignore WHERE, and is evaluated based on all year in [Calendar Year].
So to consider WHERE criteria, I define the dynamic named sets as follows:
CREATE DYNAMIC SET [adventure works].[NegativeGrossProfitMargin] AS
FILTER([Product].[Subcategory].[Subcategory] HAVING [Measures].[Gross Profit Margin] < 0);
And the results for query below are guarantee correct:
SELECT
[Measures].[Gross Profit Margin] ON 0,
NON EMPTY(NegativeGrossProfitMargin) ON 1
FROM [Adventure Works];
SELECT
[Measures].[Gross Profit Margin] ON 0,
NON EMPTY(NegativeGrossProfitMargin) ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];
It will get evaluated for each query because they are in equivalent with this query below :
WITH SET NegativeGrossProfitMargin
SELECT
[Measures].[Gross Profit Margin] ON 0,
NON EMPTY(NegativeGrossProfitMargin) ON 1
FROM [Adventure Works];
and the second query below:
WITH SET NegativeGrossProfitMargin
SELECT
[Measures].[Gross Profit Margin] ON 0,
NON EMPTY(NegativeGrossProfitMargin) ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2004];
The WITH SETs on 2 queries above are static named set in query scope. Dynamic named set solve static named set problem in cube scope.
Next issue is about DMX performance, example see this query below :
WITH SET EmployeesBySalesAmountDesc
AS ORDER([Employee].[Employee].[Employee].members, [Measures].[Reseller Sales Amount], BDESC)
MEMBER [Measures].[Employee Sales Rank]
AS RANK([Employee].[Employee].CurrentMember, EmployeesBySalesAmountDesc )
SELECT [Measures].[Employee Sales Rank] ON 0
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works];
The above query ranks employee based on their Reseller Sales Amount from the highest to the lowest. The performance problem comes when WHERE applied to the query :
WITH SET EmployeesBySalesAmountDesc
AS ORDER([Employee].[Employee].[Employee].members, [Measures].[Reseller Sales Amount], BDESC)
MEMBER [Measures].[Employee Sales Rank]
AS RANK([Employee].[Employee].CurrentMember, EmployeesBySalesAmountDesc )
SELECT [Measures].[Employee Sales Rank] ON 0
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Ship Date].[Calendar].[Month].&[2004]&[1];
Filtering doesn’t get applied to the static named sets, thus RANK is done to unfiltered sets rather than filtered named sets, so performance will slow, to solve this issue, I create dynamic named set:
CREATE DYNAMIC SET [Adventure Works]. EmployeesBySalesAmountDesc AS
ORDER([Employee].[Employee].[Employee].members, [Measures].[Reseller Sales Amount], BDESC);
Next, I reference the dynamic named sets from DMX query below :
WITH
MEMBER [Measures].[Employee Sales Rank] AS RANK([Employee].[Employee].CurrentMember, EmployeesBySalesAmountDesc )
SELECT [Measures].[Employee Sales Rank] ON 0
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Ship Date].[Calendar].[Month].&[2004]&[1];
The dynamic named sets will get evaluated together with WHERE filter, so performance will faster than referencing to static named sets.
Another problem is subselect query problem, see example below :
WITH MEMBER Measures.NumberOfDays AS Count(Descendants([Ship Date].[Calendar].CurrentMember,[Ship Date].[Calendar].[Date],SELF))
SELECT NumberOfDays ON 0
FROM
(SELECT [Ship Date].[Calendar].[Month].&[2004]&[1] ON 0
FROM
[Adventure Works]);
Actual correct result is 31 (number of days in January 2004), but It returns more than 1000 rows.
Again, it can be solved by implementing dynamic named sets below :
CREATE DYNAMIC SET [Adventure Works].Days AS [Ship Date].[Calendar].[Date];
Then the DMX query could be rewritten to return correct result :
WITH MEMBER Measures.NumberOfDays AS Count(Days)
SELECT NumberOfDays ON 0
FROM
(SELECT [Ship Date].[Calendar].[Month].&[2004]&[1] ON 0
FROM
[Adventure Works]);
I can define more than one column for February 2004 besides Januari 2004 as follows :
WITH MEMBER Measures.NumberOfDays AS Count(Days)
SELECT NumberOfDays ON 0
FROM
(SELECT {[Ship Date].[Calendar].[Month].&[2004]&[1],[Ship Date].[Calendar].[Month].&[2004]&[2]} ON 0
FROM
[Adventure Works]);
Conclusion
Dynamic named sets gives better DMX query performance, correct results when DMX request comes from SSAS client to reference named sets which is specified in cube (global) level.