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

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,
Question
Answers

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
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 Marked as answer by RaymondLeeMicrosoft employee, Moderator Wednesday, July 22, 2009 9:02 AM
 Unmarked as answer by RaulQ Thursday, July 23, 2009 9:24 AM

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, 
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


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: 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".
 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).
 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 dimensionmeasuremeasure 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! 