none
SSAS 2008 - TopCount inside named set?

    Question

  • I've got a problem that I thought should be solved in SSAS 2008....

    Simply put, I want to create a named set inside the cube (pre-fabricated) that shows the Top 10 stores based on their Revenue.

    So far so good, however, I would want this set to listen to the current slice in the cube. So, when the end user filters on year 2008, it should give the top revenue stores for 2008, not for the entire cube. Then when I add a filter for product category 'bicycles', it should give me the top revenue stores for bicycles within 2008. 'I thought this should be solved by the 'Dynamic' setting of the named set, but this does not change anything. It gives me the top 10 stores on revenue all time, that had bicycle sales in 2008. Resulting in about 4 stores.

    When you create the set on the fly in a MDX query on the database, it returns correct results, as you can calculate the topcount on a presliced part of the cube. However, I cannot rely on the end-user to create the set correctly.

    Any thoughts on this? Thanks for your effort in advance!


    Wednesday, February 11, 2009 8:47 AM

Answers

  • After I while, I tested dynamic sets on SSAS 2008, Adventure Works 2008 DW database, because I wanted to make sure my claims are correct and that new SP1 for SQL Server 2008 doesn't change anything (or any other updates). To my surprise, dynamic sets provided in AW 2008 database worked ok. They changed when I put something in slicer. I also noticed that we can sort them Z-A on particular data (measure) column and that Excel preserves that (which I find very nice) when we change slicer. I also tested dynamic sets on another computer, the one without SP1. It also worked ok, changing members on rows (dynamic set) as I changed the slicer. Was I mistaken? I thought I saw it once not working properly. Or wasn't I?

    From what I know about query execution, or should I say thought I know, somehow this violated all the rules. It was obvious that it works as dynamic indeed, but on the other hand I remember I had problems with sets in subselect not being evaluated for slicer which is normal if one considers execution phases (evaluation order of query parts: subselect->slicer->...).

    The fact is, I was tempted twice to delete my false claims in previous post, which is something I can't remember being doing so far, but if they are wrong, they should not mislead readers in this forum. Or make another post saying they are wrong? Hm, I had to test more in order to be absolutely sure. And then I found a needle (in the hay).

    It was Adventure Works 2008 DW all the time. As a carefully prepared artificial database, the one that has dynamic sets made using measures that are almost identical as default cube measure it is many times misleading and this was such a case.

    tss68nl was right! I was right (in my previous post). Dynamic set is actually evaluated for default cube measure and default members on each hierarchy. It only looked like it worked correctly. But in fact, it didn't. A very subtle issue. That's why I will try to elaborate it here, in order to help others in future issues.

    In order to shorten the explaination, I suggest you do the following.

    Open Excel, connect to AW 2008 DW database, put Sales Amount in Values Box, Top 25 Products on Row Labels, 2002 year in slicer. The result will show 13 rows. You can sort them by right-clicking on any number under Sales Amount column, selecting Sort and then Z->A. "Road 150-Red, 56" member will be on top, Mountain-100 Black, 44 on bottom. Values will decrease downwards.

    Date.Calendar CY 2002
    Row Labels Sales Amount
    Road-150 Red, 56 $979.014,67
    Road-150 Red, 62 $941.085,01
    Mountain-200 Black, 42 $934.918,05
    Mountain-200 Black, 38 $896.511,60
    Road-150 Red, 48 $881.685,73
    Mountain-200 Silver, 42 $859.725,33
    Road-150 Red, 52 $831.589,95
    Mountain-200 Silver, 46 $806.696,99
    Road-250 Red, 44 $782.553,94
    Mountain-200 Silver, 38 $764.614,02
    Road-250 Red, 48 $739.485,74
    Mountain-200 Black, 46 $682.499,69
    Mountain-100 Black, 44 $632.557,50





    The next test can be to remove dynamic set (Top 25 Products) from rows and to replace it with attribute hierarchy Product.Product (under Product, more fileds). You can also do that in another sheet, as I did. When you do that, you'll get a list of members. Again, sort the data Z->A and turn on Excel's Top 10 function (Filter, Top 10...), but change 10 with 25. Or leave as is and concentrate on the first 25 rows (the one with highest results).

    Date.Calendar Year CY 2002
    Row Labels Sales Amount
    Road-150 Red, 56 $979.014,67
    Road-150 Red, 62 $941.085,01
    Mountain-200 Black, 42 $934.918,05
    Mountain-200 Black, 38 $896.511,60
    Road-150 Red, 48 $881.685,73
    Mountain-200 Silver, 42 $859.725,33
    Road-150 Red, 52 $831.589,95
    Mountain-200 Silver, 46 $806.696,99
    Road-250 Red, 44 $782.553,94
    Mountain-200 Silver, 38 $764.614,02
    Road-250 Red, 48 $739.485,74
    Road-150 Red, 44 $727.820,12
    Mountain-200 Black, 46 $682.499,69
    Road-250 Black, 44 $681.953,82
    Road-250 Black, 48 $649.347,31
    Mountain-100 Black, 38 $639.883,93
    Mountain-100 Black, 44 $632.557,50
    Road-250 Red, 52 $623.542,92
    Road-250 Black, 52 $611.273,81
    Mountain-100 Black, 48 $610.577,88
    Mountain-100 Silver, 38 $603.710,73
    Mountain-100 Black, 42 $593.829,49
    Mountain-100 Silver, 42 $593.680,75
    Mountain-100 Silver, 44 $588.070,77
    Mountain-100 Silver, 48 $516.458,48
    Grand Total $18.173.088,23




    Now, compare results. You can copy-paste values from second test into first sheet, next to it.

    Date.Calendar CY 2002
    Row Labels Sales Amount      
    Road-150 Red, 56 $979.014,67 Road-150 Red, 56              979.014,67   
    Road-150 Red, 62 $941.085,01 Road-150 Red, 62              941.085,01   
    Mountain-200 Black, 42 $934.918,05 Mountain-200 Black, 42              934.918,05   
    Mountain-200 Black, 38 $896.511,60 Mountain-200 Black, 38              896.511,60   
    Road-150 Red, 48 $881.685,73 Road-150 Red, 48              881.685,73   
    Mountain-200 Silver, 42 $859.725,33 Mountain-200 Silver, 42              859.725,33   
    Road-150 Red, 52 $831.589,95 Road-150 Red, 52              831.589,95   
    Mountain-200 Silver, 46 $806.696,99 Mountain-200 Silver, 46              806.696,99   
    Road-250 Red, 44 $782.553,94 Road-250 Red, 44              782.553,94   
    Mountain-200 Silver, 38 $764.614,02 Mountain-200 Silver, 38              764.614,02   
    Road-250 Red, 48 $739.485,74 Road-250 Red, 48              739.485,74   
    Mountain-200 Black, 46 $682.499,69 Road-150 Red, 44              727.820,12   
    Mountain-100 Black, 44 $632.557,50 Mountain-200 Black, 46              682.499,69   
    Road-250 Black, 44              681.953,82   
    Road-250 Black, 48              649.347,31   
    Mountain-100 Black, 38              639.883,93   
    Mountain-100 Black, 44              632.557,50   
    Road-250 Red, 52              623.542,92   
    Road-250 Black, 52              611.273,81   
    Mountain-100 Black, 48              610.577,88   
    Mountain-100 Silver, 38              603.710,73   
    Mountain-100 Black, 42              593.829,49   
    Mountain-100 Silver, 42              593.680,75   
    Mountain-100 Silver, 44              588.070,77   
    Mountain-100 Silver, 48              516.458,48   






    As you can see, data looks identical, both members and values. Only that in second test we have more rows.

    But if you look closely, you'll notice that last two rows from left part don't have match on the right part (in the same row). Normally, we always focus on the top members and since they are ok (in correct order and with correct values), we think the whole result is ok. But it's not.

    Two things tells us something is wrong. First one is obvious: number of rows returned. That is something easy to notice, as tss68nl did, and neglect (as I guess we all did using AW 2008 DW). The wrong members are a bit difficult thing to spot (requires more concentration, hence the term needle in the hay :-)).

    All that point to this: subselect was evaluated for default measure, not Sales Amount. The problem in AW 2008 DW is that default measure is Reseller Sales Amount, which is also a part of calculated measure Sales Amount (Sales Amount = Internet Sales Amount + Reseller Sales Amount). Because of such high correlation among those two measures and because dynamic set in AW 2008 DW are made using either Internet Sales Amount or Sales Amount, it is very difficult to spot that dynamic set do not work correctly in Excel 2007, because it uses subselect with them in it. A proof about it? Here it is.

    -- post is sliced here because of limit of 60000 chars --

    Regards,

    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Tuesday, April 14, 2009 10:54 PM
    Answerer

  • -- second part of the post --



    If you make one more pivot (third one), this time with Reseller Sales Amount in Values area, Date.CalendarYear hierarchy in Columns area, Top 25 Products in Rows area, you will see a nice matrix. There will be 25 rows in total, 4 years and their Grand Total. If you sort year 2002 Z-A on its data, you'll see there are only 13 rows with data. The same rows we got in the first test. That's how that set was evaluated. But the values are different. In this pivot, we're looking at Reseller Sales Amount, so naturally, they are different. It is important to notice, as tss68nl did, that only the set is evaluated per default measure (context in general, meaning all years in this example), while the final result is shown for another measure, the one in slicer (Sales Amount). Now comes the tricky part which can not be shown anymore in Excel. If you just add Sales Amount measure in pivot, only 5 rows will have values for year 2002. Don't be mistaken by that. Excel generated its MDX and that MDX can not be used anymore as a proof of something. We need to open SSMS and execute another query.

    SELECT
      {
        (
         [Date].[Calendar].[All Periods] ,
         [Measures].[Reseller Sales Amount]
        ),
        (
         [Date].[Calendar].[Calendar Year].&[2002],
         [Measures].[Sales Amount]
        )
      }
    ON AXIS(0),
      NON EMPTY
      TopCount
      (
        {
         [Product].[Product].[Product].MEMBERS
        },
        25,
        [Measures].[Reseller Sales Amount]
      )
    ON AXIS(1)
    FROM
      [Adventure Works]



    That query will return this.

      All Periods CY 2002
    Reseller Sales Amount Sales Amount
    Mountain-200 Black, 38 $1,634,647.94 (null)
    Mountain-200 Black, 38 $1,471,078.72 $896,511.60
    Road-350-W Yellow, 48 $1,380,253.88 (null)
    Touring-1000 Blue, 60 $1,370,784.22 (null)
    Mountain-200 Black, 42 $1,360,828.02 $934,918.05
    Mountain-200 Black, 42 $1,285,524.65 (null)
    Road-350-W Yellow, 40 $1,238,754.64 (null)
    Touring-1000 Yellow, 60 $1,184,363.30 (null)
    Mountain-200 Silver, 38 $1,181,945.82 (null)
    Mountain-200 Silver, 42 $1,175,932.52 $859,725.33
    Mountain-100 Black, 38 $1,174,622.74 $639,883.93
    Mountain-200 Silver, 38 $1,172,269.42 $764,614.02
    Touring-1000 Blue, 46 $1,164,973.18 (null)
    Mountain-100 Black, 44 $1,163,352.98 $632,557.50
    Mountain-200 Silver, 46 $1,157,224.28 $806,696.99
    Mountain-100 Black, 42 $1,102,848.18 $593,829.49
    Road-250 Red, 44 $1,096,280.08 $782,553.94
    Mountain-100 Silver, 38 $1,094,669.28 $603,710.73
    Mountain-100 Silver, 44 $1,050,610.85 $588,070.77
    Mountain-100 Silver, 42 $1,043,695.27 $593,680.75
    Mountain-100 Black, 48 $1,041,901.60 $610,577.88
    Touring-1000 Yellow, 46 $1,016,312.83 (null)
    Mountain-200 Silver, 42 $1,005,111.77 (null)
    Mountain-200 Black, 46 $995,927.43 (null)
    Mountain-200 Silver, 46 $975,932.56 (null)



    So, top 25 product for all years and Reseller Sales Amount are on rows, and values for two tuples of our interest in columns. The second tuple, 2002 and Sales Amount has only 13 rows with data. Those are our 13 rows from pivot 1. When sorted, they are what we got in pivot 1.


    Conclusion.

    Dynamic set is placed in subselect in Excel 2007. Evaluation of slicer follows evaluation of subselect, meaning, subselect doesn't know about slicer and what members are placed there. Therefore, subselect is evaluated against default cube measure and default members on each hierarchy. Finally, dynamic set can be considered static as for Excel 2007 is concerned.

    Possible solutions.

    Use Excel's (i.e. Top 10) native features or use other clients (front-ends) that support dynamic sets in case you really need them.

    Ok, I hope this thorough analysis (again for tss68nl) will help others in future. And again I broke the post limit (60000 chars). I sliced it therefore in 2.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Tuesday, April 14, 2009 10:56 PM
    Answerer

All replies

  • Take a look at the Top 25 Products named set in the Adventure Works cube of the Adventure Works DW 2008 sample database. It demonstrates the behavior you describe.  Here is its definition:

    Create Dynamic Set CurrentCube.[Top 25 Selling Products]
    As TopCount
    (
    [Product].[Product].[Product].
    Members,
    25,
    [Measures].[Sales Amount]
    )
    ,
    Display_Folder = 'Sets' ;

    If the issue doesn't jump out at you, could you please post the definition of your set to this forum?


    HTHs,
    B.


    Hitachi Consulting -- Inspiring your next success! ®
    Wednesday, February 11, 2009 12:39 PM
    Answerer
  • Thanks for your quick answer.

    What you describe is exactly what I did, although on another cube structure ofcourse.

    This supposed 'dynamic' behaviour of the set behaves the same as it's non-dynamic equivalent. I tested both the dynamic and static set on both the BIDS browser and Microsoft Excel 2007, and in both viewers I get the same behavior. It takes the all time top 10 stores, and then filters them according to the slice.

    I am completely puzzled on this one :) 
    Wednesday, February 11, 2009 4:19 PM
  • Is it possible the results are valid, i.e. that the stores returned are in the top 10 for each selection?

    Also, could you post your set definition to this forum?

    Thanks
    Hitachi Consulting -- Inspiring your next success! ®
    Wednesday, February 11, 2009 4:24 PM
    Answerer
  •  

    Hi, sorry for the late reply, it got a bit hectic here :)

    My code for both sets to compare the new 'Dynamic' feature in SSAS 2008:

    CREATE DYNAMIC SET CURRENTCUBE.[Vestiging - Omzet Top 10 Dyn]

    AS topcount([Vestiging].[Vestiging Plaats].[Vestiging Plaats].Members, 10, [Measures].[Omzet]) ;

    CREATE SET CURRENTCUBE.[Vestiging - Omzet Top 10]

    AS topcount([Vestiging].[Vestiging Plaats].[Vestiging Plaats].Members, 10, [Measures].[Omzet]) ;

    I will clarify by posting some result sets, without actual values however, to protect the innocent :)
    The result set I am after (as constructed in Excel by hand), sliced on Year 2008, and one particular product:

    Correct set, as constructed with Excel Top 10 function:


    Row Labels
    HEERHUGOWAARD
    ZEVENBERGEN
    ST. OEDENRODE
    WIJHE
    SINT ANNALAND
    SCHAIJK
    HEERLEN
    HENGELO
    ZUTPHEN
    OOSTERHOUT (GLD)


    Kept same filters / slice, just replaced with the Dynamic Top 10 set:


    Row Labels
    HEERHUGOWAARD
    ST. OEDENRODE
    HENGELO
    OUDEWATER
    Zevenaar
    BAARLE NASSAU

    Strange....returns 6 values, instead of the expected 10. Seems it first determines the set, then slices.


    Again kep the filter/slice, now replaced with the Static Top 10 set:

    Row Labels
    HEERHUGOWAARD
    ST. OEDENRODE
    HENGELO
    OUDEWATER
    Zevenaar
    BAARLE NASSAU


    Turns out exactly the same, this time however this works as expected, as the static set should first determine the set, then slice. Now lets see how the all time top 10 looks like, to see whether these store locations actually exist:

    All time top 10:

    Row Labels
    Zevenaar
    HEERHUGOWAARD
    HEERDE
    OUDEWATER
    BORNE
    ST. OEDENRODE
    MAASSLUIS
    KAATSHEUVEL
    HENGELO
    BAARLE NASSAU


    This matches the results I got above. Both the dynamic and static top 10 return the same subset of the all time top 10. These happen to be the stores that have selled the product in 2008 out of this all time top 10.
    The 'dynamic' setting should return a top 10 from this particular product in 2008 though.

    I hope this clarifies? Thanks for your help!

    Thursday, February 12, 2009 9:31 AM
  • Hi,

    take a look at these threads in order to understand why this happens in Excel 2007 and not when you run query in SSMS or some other client:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/bf292ad5-dc63-45c3-b90e-8cde6e9ce961

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/6f1a7d4f-7193-4367-839c-f2c32d0d0c7c/

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/d66f744f-8ca8-4d9a-88ab-2a8dd1eb2d22 (end of it only, for explaination of what happens in OWC)

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/663184f4-88b1-4705-bcf8-d35e34884d86/ (parts of it, explains how Excel generates MDX, actually, you started that one :-))

    The first link is of greatest relevance for your case. In short, in Excel 2007, set is evaluated in subselect, meaning before it has a chance to "see" what's on slicer and adjust accordingly, so it is de facto static in both cases.

    Hope it brings some light,

    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Tuesday, April 14, 2009 4:06 PM
    Answerer
  • After I while, I tested dynamic sets on SSAS 2008, Adventure Works 2008 DW database, because I wanted to make sure my claims are correct and that new SP1 for SQL Server 2008 doesn't change anything (or any other updates). To my surprise, dynamic sets provided in AW 2008 database worked ok. They changed when I put something in slicer. I also noticed that we can sort them Z-A on particular data (measure) column and that Excel preserves that (which I find very nice) when we change slicer. I also tested dynamic sets on another computer, the one without SP1. It also worked ok, changing members on rows (dynamic set) as I changed the slicer. Was I mistaken? I thought I saw it once not working properly. Or wasn't I?

    From what I know about query execution, or should I say thought I know, somehow this violated all the rules. It was obvious that it works as dynamic indeed, but on the other hand I remember I had problems with sets in subselect not being evaluated for slicer which is normal if one considers execution phases (evaluation order of query parts: subselect->slicer->...).

    The fact is, I was tempted twice to delete my false claims in previous post, which is something I can't remember being doing so far, but if they are wrong, they should not mislead readers in this forum. Or make another post saying they are wrong? Hm, I had to test more in order to be absolutely sure. And then I found a needle (in the hay).

    It was Adventure Works 2008 DW all the time. As a carefully prepared artificial database, the one that has dynamic sets made using measures that are almost identical as default cube measure it is many times misleading and this was such a case.

    tss68nl was right! I was right (in my previous post). Dynamic set is actually evaluated for default cube measure and default members on each hierarchy. It only looked like it worked correctly. But in fact, it didn't. A very subtle issue. That's why I will try to elaborate it here, in order to help others in future issues.

    In order to shorten the explaination, I suggest you do the following.

    Open Excel, connect to AW 2008 DW database, put Sales Amount in Values Box, Top 25 Products on Row Labels, 2002 year in slicer. The result will show 13 rows. You can sort them by right-clicking on any number under Sales Amount column, selecting Sort and then Z->A. "Road 150-Red, 56" member will be on top, Mountain-100 Black, 44 on bottom. Values will decrease downwards.

    Date.Calendar CY 2002
    Row Labels Sales Amount
    Road-150 Red, 56 $979.014,67
    Road-150 Red, 62 $941.085,01
    Mountain-200 Black, 42 $934.918,05
    Mountain-200 Black, 38 $896.511,60
    Road-150 Red, 48 $881.685,73
    Mountain-200 Silver, 42 $859.725,33
    Road-150 Red, 52 $831.589,95
    Mountain-200 Silver, 46 $806.696,99
    Road-250 Red, 44 $782.553,94
    Mountain-200 Silver, 38 $764.614,02
    Road-250 Red, 48 $739.485,74
    Mountain-200 Black, 46 $682.499,69
    Mountain-100 Black, 44 $632.557,50





    The next test can be to remove dynamic set (Top 25 Products) from rows and to replace it with attribute hierarchy Product.Product (under Product, more fileds). You can also do that in another sheet, as I did. When you do that, you'll get a list of members. Again, sort the data Z->A and turn on Excel's Top 10 function (Filter, Top 10...), but change 10 with 25. Or leave as is and concentrate on the first 25 rows (the one with highest results).

    Date.Calendar Year CY 2002
    Row Labels Sales Amount
    Road-150 Red, 56 $979.014,67
    Road-150 Red, 62 $941.085,01
    Mountain-200 Black, 42 $934.918,05
    Mountain-200 Black, 38 $896.511,60
    Road-150 Red, 48 $881.685,73
    Mountain-200 Silver, 42 $859.725,33
    Road-150 Red, 52 $831.589,95
    Mountain-200 Silver, 46 $806.696,99
    Road-250 Red, 44 $782.553,94
    Mountain-200 Silver, 38 $764.614,02
    Road-250 Red, 48 $739.485,74
    Road-150 Red, 44 $727.820,12
    Mountain-200 Black, 46 $682.499,69
    Road-250 Black, 44 $681.953,82
    Road-250 Black, 48 $649.347,31
    Mountain-100 Black, 38 $639.883,93
    Mountain-100 Black, 44 $632.557,50
    Road-250 Red, 52 $623.542,92
    Road-250 Black, 52 $611.273,81
    Mountain-100 Black, 48 $610.577,88
    Mountain-100 Silver, 38 $603.710,73
    Mountain-100 Black, 42 $593.829,49
    Mountain-100 Silver, 42 $593.680,75
    Mountain-100 Silver, 44 $588.070,77
    Mountain-100 Silver, 48 $516.458,48
    Grand Total $18.173.088,23




    Now, compare results. You can copy-paste values from second test into first sheet, next to it.

    Date.Calendar CY 2002
    Row Labels Sales Amount      
    Road-150 Red, 56 $979.014,67 Road-150 Red, 56              979.014,67   
    Road-150 Red, 62 $941.085,01 Road-150 Red, 62              941.085,01   
    Mountain-200 Black, 42 $934.918,05 Mountain-200 Black, 42              934.918,05   
    Mountain-200 Black, 38 $896.511,60 Mountain-200 Black, 38              896.511,60   
    Road-150 Red, 48 $881.685,73 Road-150 Red, 48              881.685,73   
    Mountain-200 Silver, 42 $859.725,33 Mountain-200 Silver, 42              859.725,33   
    Road-150 Red, 52 $831.589,95 Road-150 Red, 52              831.589,95   
    Mountain-200 Silver, 46 $806.696,99 Mountain-200 Silver, 46              806.696,99   
    Road-250 Red, 44 $782.553,94 Road-250 Red, 44              782.553,94   
    Mountain-200 Silver, 38 $764.614,02 Mountain-200 Silver, 38              764.614,02   
    Road-250 Red, 48 $739.485,74 Road-250 Red, 48              739.485,74   
    Mountain-200 Black, 46 $682.499,69 Road-150 Red, 44              727.820,12   
    Mountain-100 Black, 44 $632.557,50 Mountain-200 Black, 46              682.499,69   
    Road-250 Black, 44              681.953,82   
    Road-250 Black, 48              649.347,31   
    Mountain-100 Black, 38              639.883,93   
    Mountain-100 Black, 44              632.557,50   
    Road-250 Red, 52              623.542,92   
    Road-250 Black, 52              611.273,81   
    Mountain-100 Black, 48              610.577,88   
    Mountain-100 Silver, 38              603.710,73   
    Mountain-100 Black, 42              593.829,49   
    Mountain-100 Silver, 42              593.680,75   
    Mountain-100 Silver, 44              588.070,77   
    Mountain-100 Silver, 48              516.458,48   






    As you can see, data looks identical, both members and values. Only that in second test we have more rows.

    But if you look closely, you'll notice that last two rows from left part don't have match on the right part (in the same row). Normally, we always focus on the top members and since they are ok (in correct order and with correct values), we think the whole result is ok. But it's not.

    Two things tells us something is wrong. First one is obvious: number of rows returned. That is something easy to notice, as tss68nl did, and neglect (as I guess we all did using AW 2008 DW). The wrong members are a bit difficult thing to spot (requires more concentration, hence the term needle in the hay :-)).

    All that point to this: subselect was evaluated for default measure, not Sales Amount. The problem in AW 2008 DW is that default measure is Reseller Sales Amount, which is also a part of calculated measure Sales Amount (Sales Amount = Internet Sales Amount + Reseller Sales Amount). Because of such high correlation among those two measures and because dynamic set in AW 2008 DW are made using either Internet Sales Amount or Sales Amount, it is very difficult to spot that dynamic set do not work correctly in Excel 2007, because it uses subselect with them in it. A proof about it? Here it is.

    -- post is sliced here because of limit of 60000 chars --

    Regards,

    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Tuesday, April 14, 2009 10:54 PM
    Answerer

  • -- second part of the post --



    If you make one more pivot (third one), this time with Reseller Sales Amount in Values area, Date.CalendarYear hierarchy in Columns area, Top 25 Products in Rows area, you will see a nice matrix. There will be 25 rows in total, 4 years and their Grand Total. If you sort year 2002 Z-A on its data, you'll see there are only 13 rows with data. The same rows we got in the first test. That's how that set was evaluated. But the values are different. In this pivot, we're looking at Reseller Sales Amount, so naturally, they are different. It is important to notice, as tss68nl did, that only the set is evaluated per default measure (context in general, meaning all years in this example), while the final result is shown for another measure, the one in slicer (Sales Amount). Now comes the tricky part which can not be shown anymore in Excel. If you just add Sales Amount measure in pivot, only 5 rows will have values for year 2002. Don't be mistaken by that. Excel generated its MDX and that MDX can not be used anymore as a proof of something. We need to open SSMS and execute another query.

    SELECT
      {
        (
         [Date].[Calendar].[All Periods] ,
         [Measures].[Reseller Sales Amount]
        ),
        (
         [Date].[Calendar].[Calendar Year].&[2002],
         [Measures].[Sales Amount]
        )
      }
    ON AXIS(0),
      NON EMPTY
      TopCount
      (
        {
         [Product].[Product].[Product].MEMBERS
        },
        25,
        [Measures].[Reseller Sales Amount]
      )
    ON AXIS(1)
    FROM
      [Adventure Works]



    That query will return this.

      All Periods CY 2002
    Reseller Sales Amount Sales Amount
    Mountain-200 Black, 38 $1,634,647.94 (null)
    Mountain-200 Black, 38 $1,471,078.72 $896,511.60
    Road-350-W Yellow, 48 $1,380,253.88 (null)
    Touring-1000 Blue, 60 $1,370,784.22 (null)
    Mountain-200 Black, 42 $1,360,828.02 $934,918.05
    Mountain-200 Black, 42 $1,285,524.65 (null)
    Road-350-W Yellow, 40 $1,238,754.64 (null)
    Touring-1000 Yellow, 60 $1,184,363.30 (null)
    Mountain-200 Silver, 38 $1,181,945.82 (null)
    Mountain-200 Silver, 42 $1,175,932.52 $859,725.33
    Mountain-100 Black, 38 $1,174,622.74 $639,883.93
    Mountain-200 Silver, 38 $1,172,269.42 $764,614.02
    Touring-1000 Blue, 46 $1,164,973.18 (null)
    Mountain-100 Black, 44 $1,163,352.98 $632,557.50
    Mountain-200 Silver, 46 $1,157,224.28 $806,696.99
    Mountain-100 Black, 42 $1,102,848.18 $593,829.49
    Road-250 Red, 44 $1,096,280.08 $782,553.94
    Mountain-100 Silver, 38 $1,094,669.28 $603,710.73
    Mountain-100 Silver, 44 $1,050,610.85 $588,070.77
    Mountain-100 Silver, 42 $1,043,695.27 $593,680.75
    Mountain-100 Black, 48 $1,041,901.60 $610,577.88
    Touring-1000 Yellow, 46 $1,016,312.83 (null)
    Mountain-200 Silver, 42 $1,005,111.77 (null)
    Mountain-200 Black, 46 $995,927.43 (null)
    Mountain-200 Silver, 46 $975,932.56 (null)



    So, top 25 product for all years and Reseller Sales Amount are on rows, and values for two tuples of our interest in columns. The second tuple, 2002 and Sales Amount has only 13 rows with data. Those are our 13 rows from pivot 1. When sorted, they are what we got in pivot 1.


    Conclusion.

    Dynamic set is placed in subselect in Excel 2007. Evaluation of slicer follows evaluation of subselect, meaning, subselect doesn't know about slicer and what members are placed there. Therefore, subselect is evaluated against default cube measure and default members on each hierarchy. Finally, dynamic set can be considered static as for Excel 2007 is concerned.

    Possible solutions.

    Use Excel's (i.e. Top 10) native features or use other clients (front-ends) that support dynamic sets in case you really need them.

    Ok, I hope this thorough analysis (again for tss68nl) will help others in future. And again I broke the post limit (60000 chars). I sliced it therefore in 2.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Tuesday, April 14, 2009 10:56 PM
    Answerer
  • Oops, very late reply from my side this time, I forgot about this thread as I marked Dynamic Sets not working/inconsistent. ;)

    What an analysis again from you Tomislav, I am impressed! This might be something to forward to the microsoft team, as it seems a bug / omission in Excel. From what I gather Excel just needs to formulate it's MDX differently and things will be solved.

    Is there any way I could bring this to the attention of the development team?

    Wednesday, June 17, 2009 8:20 AM
  • Thanks for the feedback!

    I believe Excel team already knows about this and similar issues. Excel's intense use of subselect and problems caused by it has been discussed a lot in the last year or more, in forums, blogs, etc.

    In my opinion, this is not a bug. It's an architectural flaw. The engine itself (MDX generator in Excel) is constructed that way. Which means it can't be fixed easily. It's too risky operation, too deep one. Normally, you can fix holes on surface, happens to everyone, but if there's a hole inside, you better start from scratch. Which I believe is happening.

    You could clasify this as "by design", literally. Still, if you want, submit it to Connect portal: http://connect.microsoft.com/ .

    Best regards 'til next time ;-)


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr

    Wednesday, June 17, 2009 12:03 PM
    Answerer
  • Great thread!

    Now, why do we have the dynamic set and when should it be used?

    Dirk
    Saturday, July 25, 2009 12:00 PM
  • I'm glad you liked it Dirk.

    The answer to your question can be found here: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!332.entry .

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Monday, July 27, 2009 7:13 PM
    Answerer
  • Hi Tomislav,

    I am facing the same problem in my setup when I use dynamic set.Appreciate if you provide any workaround for resolving this issue?


    Thanks

    Kaushik
    Tuesday, December 08, 2009 1:08 PM
  • Hi,

    I suppose you're using Excel 2007, right? See what I've mentioned as possible solutions in my long analysis, i.e. other features in Excel like Top10. That's not exactly it (it won't work right away, you have to set it up each time, meaning, user has to perform that activity), but it should work. The other option is to use another front-end.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Tuesday, December 08, 2009 11:11 PM
    Answerer