SSAS Dynamic Named Sets in Calculated Members

Posted on by — 13 Comments ↓

Recently at one of my customers we were doing some performance tuning on a SSAS Multidimensional cube and I came across something I have not seen before yet in my lifetime as a SSAS developer. Even the simplest select queries where running for several seconds, even if executed on warm cache! So first I thought this may be related to some SCOPE assignments overwriting values etc. Using my MdxScriptDebugger I could easily identify that none of the SCOPE assignments had any impact on the actual result of the query. However, what the result of the MdxScriptDebugger trace also revealed was that the query-time increased after a calculated member was created. Important to note here is that this specific member was NOT used in the test-query at all! Investigating into the calculated member turned out that it was using a Dynamic Named Set.

Dynamic Named Sets are usually used if you want a set to be re-evaluated in the current context opposed to Static Named Sets which are only evaluated once during creation. For example you can create a Dynamic Named Set for your Top 10 customers. Changing the Year or Country would cause a re-evaluation of the set and different customers are returned for your filter selections. These type of calculated sets is usually not a problem.

Another reason to used Dynamic Named Sets is to deal with Sub-Selects. Some client tools, foremost Microsoft Excel Pivot Tables, use Sub-Selects to deal with multiple selections on the same attribute. Lets do a little example on how to use Dynamic Named Sets here. Assume you need to calculate the average yearly sales for the selected years. This may sound very trivial at first sight but can be very tricky. In order to calculated the yearly average we first need to calculated how many years are in the currently selected:

  1. CREATE MEMBER CURRENTCUBE.[Measures].[CountYears_EXISTING] AS (
  2. COUNT(EXISTING [Date].[Calendar Year].[Calendar Year].members)
  3. );

However, this does not work if Sub-Selects are used in the query:
PivotTable_SSAS_DynamicNamedSet_Wrong
The calculated member returns “6” (the overall number of years) instead of “2” (the actually selected number of years). The issue here is that the calculation is not aware of any Sub-Select or filters within the Sub-Select as it is executed only outside of the Sub-Select.

To work around this issue you can create a Dynamic Name Set and refer to it in your calculated member:

  1. CREATE DYNAMIC SET [ExistingYears] AS {
  2. EXISTING [Date].[Calendar Year].[Calendar Year].members
  3. };
  4.  
  5. CREATE MEMBER CURRENTCUBE.[Measures].[CountYears_DynamicSet] AS (
  6. COUNT([ExistingYears])
  7. );

PivotTable_SSAS_DynamicNamedSet_Working

Now we get the correct results for our Count of Years calculation and could simply divide our Sales Amount by this value to get average yearly sales. The reason for this is that Dynamic Named Sets are also evaluated within the Sub-Select and therefore a COUNT() on it returns the correct results here.

So this technique is quite powerful and is also the only feasible workaround to deal with this kind of problem. But as I initially said, this can also cause some performance issues!

To illustrate this issue on Adventure Works simply add these two calculations to your MDX Script:

  1. CREATE DYNAMIC SET [ExpensiveSet] AS {
  2. Exists(
  3.     [Product].[Category].[Category].members,
  4.     Filter(
  5.         Generate(
  6.             Head([Date].[Calendar].[Month].MEMBERS, 30),
  7.             CrossJoin(
  8.                 {[Date].[Calendar].CURRENTMEMBER},
  9.                 Head(
  10.                     Order(
  11.                         [Customer].[Customer].[Customer].MEMBERS,
  12.                         [Measures].[Internet Sales Amount],
  13.                         BDESC),
  14.                     10000))),
  15.         [Measures].[Internet Order Quantity] > -1
  16.     ),
  17.     'Internet Sales'
  18. )
  19. };
  20.  
  21. CREATE MEMBER CURRENTCUBE.[Measures].[UnusedCalc] AS (
  22. COUNT([ExpensiveSet])
  23. );

The [ExpensiveSet] is just a Dynamic Named Set which needs some time to be evaluated and the [UnusedCalc] measure does a simple count over the [ExpensiveSet]. Having these calculations in place you can now run any query against your cube and will notice that even the simplest query now takes some seconds to execute even if the new calculated member is not used:

  1. SELECT
  2. [Measures].[Internet Sales Amount] ON 0
  3. FROM [Adventure Works]

I am quite sure that this behavior is related to how Dynamic Named Sets are evaluated and why they also work for Sub-Selects. However, I also think that calculations that are not used in a query should not impact the results and/or performance of other queries!

I just created a bug on Connect in case you want to vote it:
https://connect.microsoft.com/SQLServer/feedback/details/1049569

I know that Dynamic Named Sets in combination with calculated members is a not a widely used technique as I guess most developers are not even aware of its power. For those who are, please keep in mind that these kind of calculations get evaluated for every query which can be crucial if your Dynamic Named Set is expensive to calculate! This has also impact on meta-data queries!

13 Replies to “SSAS Dynamic Named Sets in Calculated Members”

  1. I was happy to see someone else noticing this problem and finding the connect item. However I’m shocked they have closed it resolved “as by design” which is absurd. This completely renders dynamic sets useless in many cases. We had a system with 5 dynamic sets when we discovered the problem. We were able to avoid the problem in that case by being able to remove 2 sets and optimize the 3 other.

    Also the MSDN page for CREATE SET says “DYNAMIC = Indicates that the set is to be evaluated every time it is used in a query.” Which is obviously bullshit, because the set is to be evaluated every time no matter if it is in the query or not. This is also very illogical so you will surely not realise it before you hae problems with it.

    As a programmer this seems like a feature that would be really easy to implement, but I guess at the moment they build the dependency tree after the evaluation of dynamic sets and thus don’t know which sets are used by the query at the right stage.

    Reply ↓

    • well, I did not find the connect-item, I created it 🙂
      I think it is by design as dynamic named sets are somehow treated different by the engine, especially in this case where there is also a calculated member referencing it.
      this is also the reason why dynamic named sets can be used to work around sub-selects (=excel multi-selcts) where all other approaches fail. They are deeply psuhed down to the SSAS engine which makes this approach quite powerful but also brings some drawbacks and this behavior is “by design”

      Reply ↓

  2. Can you please help translate the below SQL to MDX query

    select
    sum
    (
    select
    t1.manufacturerid
    ,sum(salesqtyCY)
    from tbl t1
    inner join
    (
    select
    manufacturerid
    ,min(minytd)
    ,max(maxytd)
    from tbl
    group by manufacturerid
    ) t2 on t1.manufacturerid = t2.manufacturerid
    where dateid between t2.minytd and t2.maxytd
    )

    Reply ↓

  3. Dynamic Named Sets appear to be an interesting concept!

    Would it be appropriate for the following scenario – Preparing a Year over Year Variance Drilldown on a monthly basis?

    Specifically, I have a text file in the comma separated values format containing transactional-level data for 2013, 2014, 2015 and YTD 2016 with the following columns (over 900,000 records!);

    Date, Provider, CustomerName, PaidAmt

    Specifically, I need to determine the following on a monthly basis;

    Step 1: Top 5 months with the greatest variances in the paid amount – Year over Year

    Step 2: Then, the Top 25 providers – one provider for each of the 5 months in Step 1

    Step 3: Then, the Top 5 customers (by PaidAmt) for the 125 providers in Step 2

    Currently, I manually create the three pivot tables and perform the analysis which is somewhat time consuming.

    Pivot table #1 – I have the PaidAmt by Year and Month. For example,

    Row: Month

    Column: Year

    Values: Sum of PaidAmt, Difference, %-age Increase(Decrease)

    Pivot table #2 – I have the PaidAmt by Month and providers.

    And, the third pivot table is the PaidAmt by Provider and by CustomerName.

    Note, Within a MS Excel pivot table, I cannot rank on the calculated column, “%-age Increase(Decrease)”, whether creating the

    pivot table manually or with VBA. In essence, I cannot sort due to positional reference restrictions.

    Initially, I believed that the creation of custom dynamic sets within the pivot table, using MDX, would be the best solution?

    More so than the use of;

    PowerPivot with the use of time intelligence functions via DAX

    or Using SSIS to import the text file into Sql Server and using T-SQL?

    However, per your article, I am having second thoughts about the use of Dynamic Named Sets.

    Any insight as to how you would perform this task?

    Any suggestions on how best to “automate” this process using time intelligence functions within Power Pivot?

    Can this even be performed within a single pivot table?

    Thanks in advance for any insight.

    Reply ↓

    • I created a little MDX query which basically does what you want on Adventure Works Sample DB (Tabular)
      WITH

      SET TopMonths AS {
      TOPCOUNT(
      CROSSJOIN(
      [Date].[Calendar Year].children,
      [Date].[Calendar].[Month].members),
      5,
      [Measures].[Internet Total Sales])
      }

      SET TopMonthCity AS {
      GENERATE(
      TopMonths AS _topMonths,
      TOPCOUNT(
      _topMonths.CURRENT * [Geography].[City].[City].members,
      3,
      [Measures].[Internet Total Sales]
      )
      )
      }

      SET TopMonthCityCust AS {
      GENERATE(
      TopMonthCity AS _topMonthCity,
      TOPCOUNT(
      _topMonthCity.CURRENT * [Customer].[Customer Id].[Customer Id].members,
      3,
      [Measures].[Internet Total Sales]
      )
      )
      }

      SELECT
      {[Measures].[Internet Total Sales]
      } ON 0,
      TopMonthCityCust ON 1
      FROM [Adventure Works]

      the three sets that it creates should be similar to what you want to achieve. Of course you can use any measure you want for the TOPCOUNT-functions.
      If you create those sets in Excel make sure to check “Recalculate Set on every Update” to make them dynamic named sets (otherwise they are static)

      -gerhard

      Reply ↓

  4. Hello Gerhard,
    I hope maybe you could help me with one MDX query as I didn’t find any answer for this online.
    I have created dynamic data set with customers that meet some criteria (e.g. customers that have more than 1000 transactions):

    CREATE DYNAMIC SET [FCR Customer] AS

    Filter( ( {[Customer].[Customer Account].Members – [Customer].[Customer Account].[All] } ),
    [Measures].[Transaction count]>1000
    );

    And in next step I have created calculated measure that is supposed to show number of completions only for customers from dynamic data set (for other customers should be NULL:

    CREATE MEMBER CURRENTCUBE.[Measures].[FCR]
    AS ( ([FCR Customer], [Measures].[Completion count]) ),
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Counts’ ;

    But this measure returns NULL for all customers. When I add some aggregate function at the beginning of the measure definition (e.g. SUM), results are OK if measure is not sliced by customers. But if I add customers to the rows, I get the same value for all customers (sum for all selected customers).

    Is this expected behavior for measures created on dynamic data sets or I missed something in measure definition?

    Thanks a lot.

    Reply ↓

    • well, if you have a set like your [FCR Customer] and you want to use it in a measure, you need to specify the aggregation (e.g. SUM) within the measure
      Otherwise the engine would not know how to aggregate these values so this is kind of the expected behavior

      Reply ↓

  5. Hi, Gerhard

    Would it be possible to use your dynamic named set to select the months of last year?

    Thanks, Benedito.

    Reply ↓

    • Hi Benedito,
      sure, you can use dynamic named sets to select the months of last year but I dont think that this is the best way to address this issue.
      I would usually recommend to add an attribute to the Time-dimension whcih allows you to filter on the current/prevous/pre-previous/… year etc.

      But all this has nothing to do with the actual content and purpose of this blog post

      regards,
      -gerhard

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

*