none
mdx question - Percentage of number of visits to a reseller...(measure values (members) in row and percentage measure in colums)

    Question

  • Hi all,
    I'm a newbie in mdx and can't seem to get my head around this query... what i'm looking for is the percentage of number of visits to a reseller in adventure works cube for example returning something like (bogus data):

                Percentage
    1           62%
    2           23%
    4           12%
    7           1%
    14         0,04%
    ....

    I don't know how to have in the rows a value that i assume would need to come from a calculated measure...  this seems quite a normal requisite like for example percentage of page visits so I assum mdx can answer it easily.

    Thanks,
    Monday, July 13, 2009 6:33 PM

Answers

All replies

  • It would need to come from a calcualted measure, you could either define that measure in the cube or if it's just for the query it can be declared inline.

    eg.

    WITH

     

     

    MEMBER Measures.[Reseller Order Pcnt] as [Measures].[Reseller Order Count]

    / ([Measures].[Reseller Order Count],[Reseller].[Reseller Type].[All Resellers])

    ,

    FORMAT_STRING = 'Percent'

    SELECT

    {measures.[Reseller Order Count], Measures.[Reseller Order Pcnt]}

    ON COLUMNS,

    {[Reseller].[Reseller Type].[Business Type]}

    ON ROWS

    FROM

     

    [Adventure Works]


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Monday, July 13, 2009 9:04 PM
    Moderator
  • Hi Darren,

    sorry for only replying now but for some reason I missed the alert on you post and just got the "Proposed answer" one. Maybe I didn't explain myself fully, what I'm looking for is a query that tells me:
     - For a particular reseller and particular period (these will be a parameters of a Reporting Services report)
     - The percentage of nº of visits (1 sale - 1 visit): so having in mind the table I posted earlier :
                 - 62% of the reseller's clients visited him 1 time
                 - 23% of the reseller's clients visited him 2 times
                 - 12% of the reseller's clients visited him 4 times
                 - 1% of the reseller's clients visited him 7 times
                 - 0.04% of the reseller's clients visited him 14 times
    and so on.

    So depending on the parameters one has to find out what nº of visits the reseller has got (in the example above no one has visited him exactly 3 times) and the percentages of clients from his whole.

    So it's not really just a percentage of visits per reseller.

    Thanks,
    Thursday, July 23, 2009 9:36 AM
  • Hi Raul,

    take a look at my recent post: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!325.entry .

    It was inspired by your problem (instead of providing an answer here, I wrote it in my blog since I suspected it would be a long one, and interesting also). I guess it should work :-).

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by RaulQ Friday, July 24, 2009 9:49 AM
    Friday, July 24, 2009 6:28 AM
    Answerer
  • Tomislav you're the man :-), i definitely wouldn't get there on my own...

    Many thanks,
    Friday, July 24, 2009 9:49 AM
  • Hi Tomislav,

    I'm now picking up on I left off regarding this query, and I'm not being able to use your suggestion. Initially I tried to reproduce your post on my SSAS 2005 AdventureWorks Cube without success, I got some errors on the cube processing. The message wasn't directly related but only happen when I added the dummy dimension not connected with any measure group. Will your solution work on SSAS 2005?

    Anyway I managed to get it to work easily on the 2008 samples...So I created a SSAS 2008 solution of my 2005 project solution to try your approach on my initial cube and I can't seem to get it to work (my scenario is pratically the same with transactions from resellers, etc). 
    What happens is that I can't seem to get a "relation" between the dummy dimension and the measure group measures:

    1. When I just drag the dimension to the cube browser the members don't  expand, i just get the Grand Total cell, while in AdventureWorks the meabers are expanded like if one configured the "show empty cells option".
    2. If then drag the "count measure" used in my measure analogous to the "Reseller Orders Frequency" I get an empty cell, like if the dimension wasn't connected to the measure, which doesn't happen in your solution (one getts the total - 3796 for each interval member).
    3. If I drag my "Reseller Orders Frequency" and "Reseller Orders Frequency %" measures  I get 1 row :  0 (interval)    8 (frequency)     100% (frequency percentage) like if there wasn't any transaction for all 8 resellers (using sample data).

    The calculations seem correct, and the dimension|measure|measure group properties are the same as yours...can't seem to figure out what am i doing different... any ideas pops your mind...?

    Thanks again!

    Tuesday, July 28, 2009 5:29 PM
  • Found the problem, I was using IgnoreUnrelatedDimension = False in the measure group properties.... setting to true did the trick :-) .
    • Edited by RaulQ Wednesday, July 29, 2009 4:52 PM typo
    Wednesday, July 29, 2009 4:51 PM