none
Calculated Member Puzzle

    Question

  • Hello Friends

    Is there any function or way to get Totals and Grand Totals that appears in a cube?
    I need to make a calculated member that returns the percent of representation of a measure in order to selected universe. If I have a way to know the Grand Total and Total by hierarchy my problem was solved!
    Below, I post a sample:


    Mark  Origin        Total Vehicles Representativity
    VW                         25         25%
    VW    Imported              5         20%
    VW    National             20         80%
     
    GM                         30         30%
    GM    Imported             10         33%
    GM    National             20         77%

     

    Honda                      45         45%
    Honda Imported             30         67%
    Honda National             15         13%
    -------------------------------------------------------------
    Grand Total                100        100%

     

    Like above, 25 cars of VW represents 25% of total os veichles. 25 / 100 = 25%

    Someone can helps me to solve this, please?

    Thanks!

    Wednesday, August 27, 2008 2:59 PM

Answers

  •  

    Hi George and others interested,

     

    here is my MDX for this puzzle, adjusted to be even more universal.

     

    Code Snippet

    WITH

     

    MEMBER [Measures].[Count of columns] AS

    Axis(1).Item(0).Count

     

    MEMBER [Measures].[Is ALL Member] AS

    iif( Axis(1).Item(0).Item( [Measures].[Count of columns] -

    1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null )

     

    MEMBER [Measures].[ALL are ALL Member] AS

    StrToValue(

    Generate( Head(Axis(1), [Measures].[Count of columns] ) AS L,

         "Axis(1).Item(0).Item(" +

    CStr(L.CurrentOrdinal - 1) +

    ").Hierarchy.CurrentMember.Level.Ordinal = 0",

    " AND " )

    )

     

    MEMBER [Measures].[Smart percentage of 1st measure in columns %] AS

    iif( [Measures].[Is ALL Member],

    Axis(0).Item(0)

    /

    Sum( Filter( Axis(1), [Measures].[ALL are ALL Member] ),

    Axis(0).Item(0)

       ),

    Axis(0).Item(0)

    /

    ( Axis(1).Item(0).Item([Measures].[Count of columns] - 

      1).Hierarchy.CurrentMember.Parent,

      Axis(0).Item(0)

    )

       ),

    FORMAT_STRING = "percent"

     

    SELECT

    {

    [Measures].[Sales Amount],

    [Measures].[Smart percentage of 1st measure in columns %]

    }

    ON AXIS(0),

     

    NON EMPTY

    [Promotion].[Promotion Category].Members

    *

    [Product].[Category].MEMBERS

    *

    [Sales Territory].[Sales Territory].MEMBERS

    ON AXIS(1)

     

    FROM

    [Adventure Works]

     

     

    As before, no existing cube structure (dimensions or measures) is mentioned above the SELECT part. Meaning, it supports any dimensions combination on rows. However, slight restriction can be noticed.

     

    In my previous MDX I supposed that only last dimension in rows will have full structure (AllMembers), as in the first example. And, I believe my previous MDX works with no problem with any other dimension or dimensions before it as long as they only show level members. So, no totals (ALL) in them. You pointed out that it doesn't have to be so - user can place full structure for any dimension before. Well, this is better, I admit, since all subtotals will be visible in that scenario (more data to analyze). Hence I adjusted that MDX into this MDX. But, now I require that all dimensions have AllMembers structure. I admit, it's not totally universal (yet), but it is an enhanced model. As in physics, a model is good enough, until somebody notices its restrictions. There is no better one out there so it is used still. Yet one day it comes, fitting much better. Until this also gets proved as buggy one. And the circle continues, all in search for universal solution.

     

    Why am I saying this? Because it's time to stop for now. Currently available MDX function are limiting severely search for a general solution (like available technology in the past). So, I'll drop it for now, while reaching this level of universality. Who knows, maybe someone else gets inspired by this quest or even I continue one day. After all, it is a great experience in MDX world.

     

    OK, now for the hungry minds, here is the explaination.

     

    I places 3 dimensions/hierarchies on rows. All display full structure, so many subtotals are there to be expected. Also, one total is also there, where 3 ALL members meet. As requested in example. Only this guy will be on top here, first row of results.

     

    Columns (axis 0) stays the same. Our measure "Smart percentage" gets changed a bit, while 3 new members get introduced on top. The first one comes handy (is this the proper phrase in English?) since it is repeatedly referenced below - dimensionality of rows. In this MDX it gives 3, ok?

     

    Second member is used to test whether we encountered ALL member from last hierarchy. Member is used as Boolean in later iifs.

     

    Third one was the wall breaker. It allowed the mechanism for testing different things. Remember I said I found my wonder function. This is it - StrToValue. Sounds simple and nothing special, but combined with string-type Generate, it does magic. Now, this one is hard to explain, so I'll go slow (why am I explaining you all this? - for someone else to breakthrough to next level!). The core of it is Generate function. You see, Generate and Filter are only functions that allow iteration. Something so simple like looping in VB.NET, C#, etc. is very hard here, as in T-SQL views (because T-SQL views are pandan to MDX queries, sprocs are special). Meaning we have to do all kinds of tricks to simulate wanted loops. Here I needed to loop 3 times. How to achieve it? Use first 3 rows of query. I mean, you have to help yourself to make a for i = 1 to 3 loop if you need it, however you can. So I did. Of course it's a week point if query returns less than 3 rows, but let's be realistic, ok. Chances are small to hit a rock.

     

    OK, let's continue. We name that set L. This Generate function is string-type, meaning it will return concatenated string. We need that string, and you'll see later why. For each of those 3 (dummy) items in a set L, we build a string where we use L's itemindex (currentordinal in MDX). Strings will be concatenated using word " AND " which is logical operator to be soon. 3 items will have different 3rd part and that represents hierarchy in rows. So we achieved with this several things. We found out what hierarchies are there (no, there's no MDX function for that yet), and we build a string to test whether this is an ALL member there. To be frank, I tried to make iteration in iteration to loop 1:N and 1:3 where N is rowcounts, but it just didn't work. Maybe it's restricted. This way I used first row to test it, and it will work as expected only because crossjoin of hierarchies will place all 3 ALL members exactly on row 1. Now, it slipped me - I noticed this only now while analyzing it for you. However, I made another member which is not visible there now. It had the same definition as this one, only it used OR as separator. Strange to say, it worked anywhere on rows. So, I might be wrong saying frankly what I feel about this member's behaviour, maybe it works. Not to be lazy I tested it and it works indeed!

     

    Ah, I see, again one dummy. That Item(0) (second part) is used because I don't know the actual rowindex during iteration. So, I just pick its hierarchy and then I apply currentmember on it to retrieve that hierarchy's currentmember which is very valuable to me in this moment - for testing purposes of what kind of member do we have here. That's the key in this whole story, to be able to catch every single cell of metadate and find out what it is. This is the winning point in this MDX! And I almost forgot it  ! You see, these kind of puzzles are so, so, hard, you have to keep your mind focused all time long. That's why it took so long to make this MDX.

     

    Back to track. Although we could test for many things there (I leave that to enthusiastic individuals among you), I just ask is it an ALL member. After Generate finishes, a string comes out having an expression (that was my intention!). StrToValue is the function that evaluates that expression. I never used it so far, but now I see its power.

     

    Remember, all those calc members are waiting on us. They have their definitions, but they do not fire yet. They will so once when we start to iterate building resultset (rows and columns of the result).

     

    We're almost finished. Smart percentage is modified a bit. It tests for ALL member of last hierarchy only by using predefined measure this time. Isn't it better to read/analyze like that? False part stays the same, it was doing a good job before, so we'll spare him of any changes. The True part gets changed. Its denominator now knows what the total of rows is, but correct one. And that brings us to the end of this analysis.

     

    For those of you who endured this explaination I congratulate from my heart!

     

    Special thanks goes to you George (and other coauthors), since your books (OLAP Solutions and MDX Solutions) have uttered a rock-solid path to my understanding of OLAP world. I guess you did a good job if student whacks like this  .

     

    As always, comments are welcome.

     

    Over-and-out.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Friday, August 29, 2008 6:42 PM
    Answerer

All replies

  • Looks like Representivity at the Mark level (aka [Origin].[All]) is
    [Measures].[Total Vehicles] / ([Measures].[Total Vehicles], [Mark].[All])

    I'm not sure I understand the GM National 77%, nor the Honda National 13%. Are those typos, with intended values of 67% and 33%?

    If so, you could use a single-hierarchy formula of
    [Measures].[Representativity] AS
    iif (hierarchy.CurrentMember is hierarchy.[All],
      [Measures].[Total Vehicles] / [Measures].[Total Vehicles],  // this phrasing gives you NULL when no vehicles available
      [Measures].[Total Vehicles] / ( [Measures].[Total Vehicles], hierarchy.CurrentMember.Parent)
    )

    Alternatively, to be able to use the attribute dimensions interchangeably, use the following sequence of definitions in the cube:

    [Measures].[Representativity] AS
      // scope for mark by origin
      [Measures].[Total Vehicles] / ( [Measures].[Total Vehicles], [Origin].[All] )
    ;

    (
    [Measures].[Representativity],
     [Origin].[All],
     [Mark].[Mark].Members
    ) =
    [Measures].[Total Vehicles] / ( [Measures].[Total Vehicles], [Mark].[All] )
    ;

    (
    [Measures].[Representativity],
     [Mark].[All],
     [Origin].[Origin].Members
    ) =
    [Measures].[Total Vehicles] / ( [Measures].[Total Vehicles], [Origin].[All] )
    ;

    ([Measures].[Representativity],
     [Origin].[All],
     
    [Mark].[All]
    ) =
    [Measures].[Total Vehicles] / ( [Measures].[Total Vehicles]
    ;

    HTH

    George Spofford
    http://www.dsslab.com
    MDX Solutions: http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html

    Wednesday, August 27, 2008 4:43 PM
  • George

     

    Thanks for your interesting. Really my sample has erros, here is the correct data:

     

    Mark  Origin        Total Vehicles Representativity
    VW                         25         25%
    VW    Imported              5         20%
    VW    National             20         80%
     
    GM                         30         30%
    GM    Imported             10         23%
    GM    National             20         77%

    Honda                      45         45%
    Honda Imported             30         67%
    Honda National             15         33%
    ------------------------------------------
    Grand Total                100        100%

     

    But this calculations must be works with any dimensions that the user drops to cube, here is my problem.

    This needs to works dinamically.

     

    Thanks!

    Wednesday, August 27, 2008 5:11 PM
  • If you need it to work with any number of attribute hierarchies being dropped on the rows, then it may not be possible. The reason is that the right-most non-All member needs to have its value calculated against the tuple of all members to its left. You can code up specific cases for 1 dimension, 2 dimensions, and so on, but not for N dimensions, and it gets ugly.


    For example, here is code that does the work for 2 hierarchies on the rows, and returns "too many dimensions" otherwise:

    iif (
      Axis(1).Item(0).Count = 2,
      iif (
        Axis(1).Item(0).Item(1).Hierarchy.CurrentMember.Level.Ordinal > 0,
        [Measures].[Total Vehicles]
        / (
          [Measures].[Total Vehicles],
          Axis(1).Item(0).Item(1).Hierarchy.CurrentMember.Parent
        ),
        iif (
          Axis(1).Item(0).Item(0).Hierarchy.CurrentMember.Level.Ordinal > 0,
          [Measures].[Total Vehicles]
          / (
            [Measures].[Total Vehicles],
            Axis(1).Item(0).Item(0).Hierarchy.CurrentMember.Parent
          ),
          [Measures].[Total Vehicles] / [Measures].[Total Vehicles]
        )
      )
      , "Too many dimensions"
    )



    If it's really necessary, you could alter the code to handle the cases of 1, 3, and other numbers of dimensions, with the final ELSE part of the IIF() indicating an error to the user.

    HTH.

    George Spofford
    http://www.dsslab.com
    MDX Solutions: http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html


    Wednesday, August 27, 2008 10:13 PM
  •  

    I have a surprise for you, George and Alexandre! I remember someone's sig in this forum saying: Nothing is impossible. I must say - I feel the same!

     

    Here is the MDX I wrote for similar problem: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3724070&SiteID=17. Solution is universal, without limitation on number of attributes placed on rows or dimension names.

     

    Now, I'll adjust that MDX to fit this specific scenario. All that in order to achieve universality (and taste the pleasure of solving the puzzle). It's made and tested on AW demo database, following the good practice of enabling everyone to use it, test it and adjust it for their projects.

     

    Code Snippet

    WITH

    MEMBER [Measures].[Smart percentage of 1st measure in columns %] AS

    iif(

    Axis(1).Item(0).Item(Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Level.Ordinal = 0,

     

    Axis(0).Item(0)

    /

    Sum(

    Filter( Axis(1),

       Axis(1).Item(0).Item(Axis(1).Item(0).Count -

       1).Hierarchy.CurrentMember.Level.Ordinal = 0 ),

    Axis(0).Item(0)

       ),

     

    Axis(0).Item(0)

    /

    (

    Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember.Parent,

    Axis(0).Item(0)

    )

        ),

    FORMAT_STRING = "percent"

    SELECT

    {

    [Measures].[Sales Amount],

    [Measures].[Smart percentage of 1st measure in columns %]

    }

    ON AXIS(0),

    NON EMPTY

    [Product].[Category].[Category].MEMBERS

    *

    [Sales Territory].[Sales Territory].MEMBERS

    ON AXIS(1)

    FROM

    [Adventure Works]

     

     

    If you look carefully, definition of calc member is ugly. I mean, there's nothing to recognize inside, no measure, no dimension. But, hey, it does a *** of a job. So for those interested, dive into it  . And here's the explaination.

     

    Axis(0) is column axis. Axis(1) is rows axis. Item(number) is used to pick a member from a set/tupple. Number is 0-based ordinal. Hierarchy() takes out hierarchy of a member. It is used because CurrentMember must have explicitely written hierarchy before it, it is not possible to use anything else like set or axis. .Level is a level for a member. .Ordinal is 0-based number for some items (like levels, etc). .Count is used to get the total of items in a set/tupple. Sum(), Filter() and .Parent are commonly used MDX functions, no need to explain them.

     

    We refer several times to Axis(0).Item(0). That represents our measure that is enlisted as a first measure in columns. I used that to achieve even greater independancy on elements put in query (on rows/columns). Meaning, you can use it for any query of that type, or adjust it for similar types (i.e. if you would have 2 measures and want to get 2 smart percentages).

     

    We refer to Axis(1) by 3 parts (part is my term, parts are separated by dots). First part is a set (Axis(1)), representing everything on rows (crossjoin of those 2 dimensions in this case). Second part is rowindex, meaning .Item(0) is the first row. We usually take first or last in these kind of calculations. First, because we assume a query will return at least one row, and there we can analyze its content. Third part is column in that first row. But, pay attention, not data or Axis(0) column, but this metadata's column. Meaning hierarchy on rows. .Item(0) would be product member in this example, .Item(1) sales territory. OK? Now, that's about Axis and referencing certain part of it. Still following  ?

     

    So, what does that definition says?

     

    First check if we are on root member of last hierarchy on rows. Why? Because the request has a split personality, sort to speak. It requires one type of calculation for ALL member, and another on other members. Hence iif. If we are on top of that dimension, we calculate percentage of that member (only measure in nominator, because current context is established, no need to write it) according to hierarchies on left. And that is achieved with Sum() that sums only those rows that have last hierarchy member set to ALL. ALL member is like default, he does nothing, he doesn't limit anything, he is like identity in math (1 for * or 0 for +) so that sum can be read as sum of everything on left as the last hierarchy gets ignored. Got that?

     

    Now, if we are below that root member, we do parent percentage calculation. And that's an easy one.

     

    Also, I deliberately placed an user hierarchy on rows (sales territory is written as dimension.hierarchy meaning it will give whole hierarchized tree of members) to show that it works good in that scenario also, not just for shallow attribute hierarchies.

     

     Comments are welcome!

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Thursday, August 28, 2008 12:52 AM
    Answerer
  • Tomislav,

    I like the idea of trying to do this fully dynamically, but I don't think the answer is really general yet. A dynamic Filter() is required, but this filter isn't discrimating enough. If you change the rows to the following:

        [Product].[Category].[Category].MEMBERS
        *
        [Sales Territory].[Sales Territory Region].MEMBERS
        *
        [Promotion].[Promotion Category].Members

    you will see that the innermost dimension is calculated correctly, but not the middle one.
                Sales Amount    Smart percentage of 1st measure in columns %
    Accessories    All Sales Territories    All Promotions    $1,272,057.89    0.58%
    Accessories    All Sales Territories    No Discount    $1,126,316.44    88.54%
    Accessories    All Sales Territories    Reseller    $145,741.44    11.46%
    Accessories    Australia    All Promotions    $162,638.16    0.07%
    Accessories    Australia    No Discount    $160,990.17    98.99%
    Accessories    Australia    Reseller    $1,647.99    1.01%
    Accessories    Canada    All Promotions    $221,505.20    0.10%
    Accessories    Canada    No Discount    $192,107.72    86.73%
    Accessories    Canada    Reseller    $29,397.48    13.27%

    For example, consider the two data values:
    (Accessories, All Sales Territories, All Promotions) is $1,272,057.89
    (Accessories, Australia, All Promotions) is $162,638.16

    Australia's value is about 12.79% of the All Territories value, but it's reported as 0.07%, because the filter is including Bikes, Clothing, etc.

    The trick will be that each nesting level of dimension needs to filter differently, comparing against the all value for the less-nested dimension(s). I know a stored procedure might be able to do the work if it's really necessary.

    Can you take another whack at this?

    George Spofford
    Thursday, August 28, 2008 4:13 PM
  •  

    George, I think I did it!!!

     

    I spent around 8 hours solving this puzzle! Basically, I was searching for a proper combination of available MDX functions that will give me what I want. And I found THE function that does the trick . But, I'm very tired now. Details soon, when I analyze what can be better and prepare that MDX for posting here.

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Friday, August 29, 2008 4:12 AM
    Answerer
  • Tomislav Piasevoli, what a wonderful answer!

     

    I test it, and it works when I made a mdx query.

    Sorry for my ignorance, but, is possible to made something like this to run in cube? Like a calculated member?

     

    Many Thanks!

    Friday, August 29, 2008 6:18 PM
  •  

    Hi George and others interested,

     

    here is my MDX for this puzzle, adjusted to be even more universal.

     

    Code Snippet

    WITH

     

    MEMBER [Measures].[Count of columns] AS

    Axis(1).Item(0).Count

     

    MEMBER [Measures].[Is ALL Member] AS

    iif( Axis(1).Item(0).Item( [Measures].[Count of columns] -

    1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null )

     

    MEMBER [Measures].[ALL are ALL Member] AS

    StrToValue(

    Generate( Head(Axis(1), [Measures].[Count of columns] ) AS L,

         "Axis(1).Item(0).Item(" +

    CStr(L.CurrentOrdinal - 1) +

    ").Hierarchy.CurrentMember.Level.Ordinal = 0",

    " AND " )

    )

     

    MEMBER [Measures].[Smart percentage of 1st measure in columns %] AS

    iif( [Measures].[Is ALL Member],

    Axis(0).Item(0)

    /

    Sum( Filter( Axis(1), [Measures].[ALL are ALL Member] ),

    Axis(0).Item(0)

       ),

    Axis(0).Item(0)

    /

    ( Axis(1).Item(0).Item([Measures].[Count of columns] - 

      1).Hierarchy.CurrentMember.Parent,

      Axis(0).Item(0)

    )

       ),

    FORMAT_STRING = "percent"

     

    SELECT

    {

    [Measures].[Sales Amount],

    [Measures].[Smart percentage of 1st measure in columns %]

    }

    ON AXIS(0),

     

    NON EMPTY

    [Promotion].[Promotion Category].Members

    *

    [Product].[Category].MEMBERS

    *

    [Sales Territory].[Sales Territory].MEMBERS

    ON AXIS(1)

     

    FROM

    [Adventure Works]

     

     

    As before, no existing cube structure (dimensions or measures) is mentioned above the SELECT part. Meaning, it supports any dimensions combination on rows. However, slight restriction can be noticed.

     

    In my previous MDX I supposed that only last dimension in rows will have full structure (AllMembers), as in the first example. And, I believe my previous MDX works with no problem with any other dimension or dimensions before it as long as they only show level members. So, no totals (ALL) in them. You pointed out that it doesn't have to be so - user can place full structure for any dimension before. Well, this is better, I admit, since all subtotals will be visible in that scenario (more data to analyze). Hence I adjusted that MDX into this MDX. But, now I require that all dimensions have AllMembers structure. I admit, it's not totally universal (yet), but it is an enhanced model. As in physics, a model is good enough, until somebody notices its restrictions. There is no better one out there so it is used still. Yet one day it comes, fitting much better. Until this also gets proved as buggy one. And the circle continues, all in search for universal solution.

     

    Why am I saying this? Because it's time to stop for now. Currently available MDX function are limiting severely search for a general solution (like available technology in the past). So, I'll drop it for now, while reaching this level of universality. Who knows, maybe someone else gets inspired by this quest or even I continue one day. After all, it is a great experience in MDX world.

     

    OK, now for the hungry minds, here is the explaination.

     

    I places 3 dimensions/hierarchies on rows. All display full structure, so many subtotals are there to be expected. Also, one total is also there, where 3 ALL members meet. As requested in example. Only this guy will be on top here, first row of results.

     

    Columns (axis 0) stays the same. Our measure "Smart percentage" gets changed a bit, while 3 new members get introduced on top. The first one comes handy (is this the proper phrase in English?) since it is repeatedly referenced below - dimensionality of rows. In this MDX it gives 3, ok?

     

    Second member is used to test whether we encountered ALL member from last hierarchy. Member is used as Boolean in later iifs.

     

    Third one was the wall breaker. It allowed the mechanism for testing different things. Remember I said I found my wonder function. This is it - StrToValue. Sounds simple and nothing special, but combined with string-type Generate, it does magic. Now, this one is hard to explain, so I'll go slow (why am I explaining you all this? - for someone else to breakthrough to next level!). The core of it is Generate function. You see, Generate and Filter are only functions that allow iteration. Something so simple like looping in VB.NET, C#, etc. is very hard here, as in T-SQL views (because T-SQL views are pandan to MDX queries, sprocs are special). Meaning we have to do all kinds of tricks to simulate wanted loops. Here I needed to loop 3 times. How to achieve it? Use first 3 rows of query. I mean, you have to help yourself to make a for i = 1 to 3 loop if you need it, however you can. So I did. Of course it's a week point if query returns less than 3 rows, but let's be realistic, ok. Chances are small to hit a rock.

     

    OK, let's continue. We name that set L. This Generate function is string-type, meaning it will return concatenated string. We need that string, and you'll see later why. For each of those 3 (dummy) items in a set L, we build a string where we use L's itemindex (currentordinal in MDX). Strings will be concatenated using word " AND " which is logical operator to be soon. 3 items will have different 3rd part and that represents hierarchy in rows. So we achieved with this several things. We found out what hierarchies are there (no, there's no MDX function for that yet), and we build a string to test whether this is an ALL member there. To be frank, I tried to make iteration in iteration to loop 1:N and 1:3 where N is rowcounts, but it just didn't work. Maybe it's restricted. This way I used first row to test it, and it will work as expected only because crossjoin of hierarchies will place all 3 ALL members exactly on row 1. Now, it slipped me - I noticed this only now while analyzing it for you. However, I made another member which is not visible there now. It had the same definition as this one, only it used OR as separator. Strange to say, it worked anywhere on rows. So, I might be wrong saying frankly what I feel about this member's behaviour, maybe it works. Not to be lazy I tested it and it works indeed!

     

    Ah, I see, again one dummy. That Item(0) (second part) is used because I don't know the actual rowindex during iteration. So, I just pick its hierarchy and then I apply currentmember on it to retrieve that hierarchy's currentmember which is very valuable to me in this moment - for testing purposes of what kind of member do we have here. That's the key in this whole story, to be able to catch every single cell of metadate and find out what it is. This is the winning point in this MDX! And I almost forgot it  ! You see, these kind of puzzles are so, so, hard, you have to keep your mind focused all time long. That's why it took so long to make this MDX.

     

    Back to track. Although we could test for many things there (I leave that to enthusiastic individuals among you), I just ask is it an ALL member. After Generate finishes, a string comes out having an expression (that was my intention!). StrToValue is the function that evaluates that expression. I never used it so far, but now I see its power.

     

    Remember, all those calc members are waiting on us. They have their definitions, but they do not fire yet. They will so once when we start to iterate building resultset (rows and columns of the result).

     

    We're almost finished. Smart percentage is modified a bit. It tests for ALL member of last hierarchy only by using predefined measure this time. Isn't it better to read/analyze like that? False part stays the same, it was doing a good job before, so we'll spare him of any changes. The True part gets changed. Its denominator now knows what the total of rows is, but correct one. And that brings us to the end of this analysis.

     

    For those of you who endured this explaination I congratulate from my heart!

     

    Special thanks goes to you George (and other coauthors), since your books (OLAP Solutions and MDX Solutions) have uttered a rock-solid path to my understanding of OLAP world. I guess you did a good job if student whacks like this  .

     

    As always, comments are welcome.

     

    Over-and-out.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Friday, August 29, 2008 6:42 PM
    Answerer
  •  

    Alexandre,

     

    writing such a huge post (as my last one) takes some time. In the meantime, as I see, you replied. Here is my answer to that ... Great, it works for you!!! That makes me happy. The effort was not in vain.

     

    Yes, you can make it in MDX script (that's what you ment, right?). Go to your cube, and in Cube Designer, Calculations tab enter this in the end:

     

    CREATE MEMBER CurrentCube.[Measures].[Smart percentage of 1st measure in columns %] AS

    tra-la-la (aka members definition) ;

     

    Notice semicolon in the end of this definition. Don't forget it.

     

    That's it, now it's reusable.

     

    Be sure to read my last post with additional improvements. Or simply use it if it works for you.

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Friday, August 29, 2008 6:55 PM
    Answerer
  • Nice work, Tomislav !

     

    Let me put my 2c in it too. Filter and Generate are not the only ways to iterate in MDX. Here is proof of concept solution for the very same problem, which does iteration differently. Performance will be horrible (42 seconds on my machine), but I wasn't focused on perf, just wanted to show alternative approach. This solution is simpler, as it also doesn't rely on StrToValue over carefully constructed string (but I liked how you put CStr over CurrentOrdinal to adjust the index), so I don't think it needs additional explanations.

     

     

    Code Snippet

    with
     member measures.index as rank([Date].[Date].CurrentMember, [Date].[Date].[Date].MEMBERS)
     member measures.hcount as axis(1).item(0).count
     member measures.hindex as
      iif(index > hcount, -1,
       iif (axis(1).item(0).item(hcount-index).hierarchy.currentmember.parent is null,
        (hindex,[Date].[Date].NextMember),index))
     member ihier as (hindex, [Date].[Date].[Date].MEMBERS.Item(0))
     member ratio as iif(ihier < 0, 1, measures.[Sales Amount]/(measures.[Sales Amount],axis(1).item(0).item(hcount-ihier).hierarchy.parent))
     ,format_string = 'percent'
    select
     {measures.[Sales Amount],ratio} on 0
    ,
    non empty
    (
    [Promotion].[Promotion Category].Members
    ,
    [Product].[Category].MEMBERS
    ,
    [Sales Territory].[Sales Territory].MEMBERS
    )
    on 1
    from [Adventure Works]

     

     

    Wednesday, September 03, 2008 6:35 AM
  •  

    Wow!

     

    A recursion masterpiece I must say! Congratulation on providing such an interesting proof of concept!

     

    Yes, it is slow indeed. I mean, 73 seconds on my 5 year old home P4, just to compare how small improvement in speed was lately, assuming you have more recent configuration. But, it works just the way George wanted (I think Alexandre was happy with just a 2 hierarchies on rows). And I can not say the same for mine. So, once again - my respect.

     

    I'll remember this cunning approach in future puzzles. Till then ...

     

    Best regards,

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

    Wednesday, September 03, 2008 11:45 PM
    Answerer
  • Hey Mosha,

    That's an interesting approach. To take it out of proof-of-concept mode, you'd need to not use the Date dimension, but introduce an additional dimension into the cube with 1 member per possible dimension in the query. (The number of degrees of freedom in a query is limited to the number of available dimensions, it seems, so the answer to many problems is to add purpose-specific dimensions.) Otherwise, it fails for rows clauses like:

    (
        [Promotion].[Promotion Category].Members
        *
        [Product].[Style].MEMBERS
        *
        [Sales Territory].[Sales Territory Region].MEMBERS
        *
        [Date].[Calendar Year].Members
    )



    Since such a dimension can be hidden from end users, it seems like its presence wouldn't violate most requirements, except perhaps performance, but rank across a small (i.e. 16-member) dimension would probably perform better than rank across Date.Date.Date, yes? (Not sure if performance would be all that good...)

    Still, I'm glad to see a way to take the knowledge of the right-most non-All member in the set. Thanks for applying your brain to this one.

    George Spofford


    Thursday, September 04, 2008 4:59 AM
  • Hi George

     

    Well, to do it purely, the recursion should not even go over any of the dimensions, and certainly I don't consider solution to be pure if it requires cube schema changes (such as introduction of special dimension to the cube), although for practical reasons some times it is acceptable. So to do it purely, I would iterate over passes instead of over members of dimension, since we have unlimited number of passes, and there are enough MDX functions to inspect current pass (CalculationCurrentPass) and change it (CalculationPassValue). The reason I didn't do it in the first place was because it wasn't adding much value to the concept of recursive solution, and anyway, I don't consider any of the solutions on this thread practical - they are all just for the theoretical interest. The real practical solution to this problem is outlined here:

     

    Thursday, September 04, 2008 5:18 AM
  •  

    Is this is a joke, or you've forgotten to paste a link there?

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Friday, September 05, 2008 8:50 AM
    Answerer
  • Hello friends,

    I saw this wonderful effort and want to present my problem.I have to calculate weighted averages, Here is my Pivot table



                          Total Volume       White %      Weighted W%

    China               30000                   1.5              30000x1.5/70000

    Taiwan             40000                   2.5              40000X2.5/70000

                           70000                   4.0             


    I need a formula which is as general as possible

    Could you give me some ideas or help




    Tuesday, November 04, 2008 8:01 PM
  •  

    Hi,

     

    open a separate (new) thread with your problem on SSAS forum, then post one more time here pasting a link to your new thread in case anyone's interested to know the solution. I'll reply there, not here.

     

    Edited later: I found your thread here: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=4087637&SiteID=17 . I'll post my answer there.

     

    This way threads will stay focused, and we'll have 1:N instead of many-to-many questions and answers. OK?

     

    Regards,

     

     

    Tomislav Piasevoli

    Business Intelligence Specialist

    http://www.softpro.hr

     

    Wednesday, November 05, 2008 11:26 AM
    Answerer
  • How would you write your code below as  a calculated member in olap cube? I need to convert it to calculated member. Thanks.

    WITH

     

    MEMBER

    [Measures].[Count of columns] AS

    Axis

    (1).Item(0).Count

     

    MEMBER

    [Measures].[Is ALL Member] AS

    iif

    ( Axis(1).Item(0).Item( [Measures].[Count of columns] -

    1).

    Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null )

     

    MEMBER

    [Measures].[ALL are ALL Member] AS

    StrToValue

    (

    Generate

    ( Head(Axis(1), [Measures].[Count of columns] ) AS L,

    "Axis(1).Item(0).Item(" +

    CStr(L.

    CurrentOrdinal - 1) +

    ").Hierarchy.CurrentMember.Level.Ordinal = 0",

    " AND " )

    )

     

    MEMBER

    [Measures].[Smart percentage of 1st measure in columns %] AS

    iif

    ( [Measures].[Is ALL Member],

    Axis

    (0).Item(0)

    /

    Sum

    ( Filter( Axis(1), [Measures].[ALL are ALL Member] ),

    Axis

    (0).Item(0)

    ),

    Axis

    (0).Item(0)

    /

    (

    Axis(1).Item(0).Item([Measures].[Count of columns] -

    1).

    Hierarchy.CurrentMember.Parent,

     

    Axis(0).Item(0)

    )

    ),

    FORMAT_STRING

    = "percent"

     

     

    SELECT

    {

    [Measures].[Returns  Dollar],

    [Measures].[Smart percentage of 1st measure in columns %]

    }

    ON

    AXIS(0),

     

    NON

    EMPTY

     

    [Product Master].[Product ].

    AXIS(1)

     

    FROM

    sales

    members

     

     

    ON

    Tuesday, January 18, 2011 7:06 AM