Calculating Share Percentage for each Working Dimension

Question

Hi Everyone,

I'd like to dynamically calculate the share percent for each and any working dimension / level.  Example and some facts are provided below for your reference.  Your time and effort is greatly greatly appreciated!!!

For example:

County              Plan Type          Count                Share Percent

Richmond          Drugs Only        4                      40%

Richmond          Drugs Only        3                      30%

Richmond          Drugs Only        3                      30%

Subtotal                                   10                     100%

Richmond          Medical Only      4                      40%

Richmond          Medical Only      3                      30%

Richmond          Medical Only      3                      30%

Subtotal                                                10                     100%

Riverside           Drugs Only        5                      50%

Riverside           Drugs Only        3                      30%

Riverside           Drugs Only        2                      20%

Subtotal                                                10                     100%

…

…

..

 

Riverside          


Fact

CountyID     CarrierID           PlanTypeID        PlanID        TimeID                               Premium
1                 3                        2                       3              20090610                                  5.00       

1                 3                        2                       3              20090630                                 15.00        

1                 3                        2                       3              20090630                                 20.00                                                                                                           
2                 4                        2                       4              20090630                                15.00                                 
1                 1                        1                       1              20090630                                10.00                                
2                 2                        1                       2              20090630                                20.00                                 
…

…

…

Dimensions

Geograghy
CountyID      County         State        Region
1                  Richmond     WA           North West
2                  Riverside      CA           South West

Plan Type
PlanID        PlanType
1                MedicalDrug
2                DrugOnly

Plan
PlanID        PlanName
1                Horizon Medical and Drugs
2                Humane Medical and Drugs
3                United Pacific Drugs
4                Well Health Drugs

Carrier
CarrierID    CarrierName
1                Horizon Healthcare
2                Humane Healthcare Provider
3                United Healthcare
4                Wells Healthcare

Answer 1

Hi Rhonda,

take a look at this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/c6ef810d-4efb-46dd-959f-72dd5ba3eac1 .

Also, examples in this blog article might help: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!259.entry (download them).

Although it can be done in MDX with some restrictions, Mosha suggests stored procedures as the best solution for this problem.

In case you need additional help in adjusting those solutions to your case, don't hesitate to ask.

Regards,


Tomislav Piasevoli
Business Intelligence Specialist
www.softpro.hr

Answer 2

Tomislav,

Thank you so very much for your guidane!  I'll check it out and try...I'm sure I'll be bugging again for additional help and tips!

rhonda

Answer 3

Hi Tomislav,

I've spent sometime learnnig your code...it's a bit too complex for me :-)...So I broke your code down into four different Calculated Members....

CREATE

 

MEMBER CURRENTCUBE.[MEASURES].[Count of Columns]
AS Axis(1).Item(0).Count,
VISIBLE = 1;

CREATE

 

MEMBER CURRENTCUBE.[MEASURES].[Is All Member]
AS IIF( Axis(1).Item(0).Item( [Measures].[Count of Columns] - 1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null ),
VISIBLE = 1 ;

CREATE

 

MEMBER CURRENTCUBE.[MEASURES].[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 " )),
VISIBLE = 1 ;

CREATE

 

MEMBER CURRENTCUBE.[MEASURES].[Share of Plans]
AS IIF([Measures].[Is All Member], Axis(0).Item(0) / Sum( Filter( Axis(1), [Measures].[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",
VISIBLE = 1;

 

 

 

Note:  [Measure].[Share of Plans] is the name I have to use...

When I use the Browser to verify the results dragging Measure.[Pan Count] and Measure.[hare of Plans]...The values are only being calculated for Measure.[Plan Count] and nothing displayed not even an error message for the Measure.[Share of Plans]...

However, I use the following MDX SELECT statement using the MSM...It works wonderfully, the result returns...with the right percentages...

SELECT {[Measures].[Plan Count], [Measures].[Share of Plans]} ON AXIS(0),
NON EMPTY
[Dim Geography].[State Code].Members * [Dim Geography].[County Name].MEMBERS * [Dim Plan Type].[Plan Type].MEMBERS ON AXIS(1)
FROM [Plans];

Am I missing something in MDX Script that I used to define those calculated members?

What's wrong?!  Please, advise!!! Please, help!!!  As always, thank you very much for your time and assistance! Hope hearing back from you soon!

rhonda

Answer 4

Hi Rhonda,

you managed it!

Nevermind the Cube Browser, it switches the coordinates. Meaning Axis(1) is Axis(0) for it and vice versa. If it works in SSMS, then that's it, you wrote it fine. Now test in the tool of your choice. If it works fine, you solved the problem, if not (or Cube Browser is your tool of choice), try replacing the axes everywhere. Or add additional iif() to test where the measures are. If they are on columns (Axis 0), leave the code as is (for True part). If not, reverse axes (for False part of iif). Testing for measures can be done using this or similar condition: Extract(Axis(1), Measures).Count > 0.

Regards,


Tomislav Piasevoli    
Business Intelligence Specialist
www.softpro.hr

Answer 5

Hi Tomislav,

Thank you very much for your prompt response! I'm sorry for being slow here...!!!  Can you be more explicit of how to use the test condition (Extract(Axis(1), Measures).Count > 0) to transverse the Axis coordinates...within the MDX script...

As always, thank you very much for your time and assistance!

rhonda

Answer 6

Hi Rhonda,

like this.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of Columns]
AS 
iif( IsError( Extract(Axis(1), Measures ) ), 
     Axis(0).Item(0).Count,
     Axis(1).Item(0).Count -- this is the original definition
   )
VISIBLE = 1;

And so on for all calc measures. Meaning, you test for an error and provide alternate expression for True part. The False part is the part you originaly had.

It not bulletproof, but it will suffice for your scenario.

Regards,


Tomislav Piasevoli    
Business Intelligence Specialist
www.softpro.hr

Answer 7

Hi Tomislav,

Thank you for the code!  I have problem deploying the others...The error message for others..."MdxScript(Plans)(5,150) Too many arguments were passed to the IIF MDX function. No more than 3 arguments are allowed."

Please, refer to the following MDX expressions that i defined for the other three Calc Members!

Measures.[Is All Members]

(IsError(Extract(Axis(1), Measures)),
IIF( Axis(0).Item(0).Item( [Measures].[Count of Columns] - 1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null ),
IIF( Axis(1).Item(0).Item( [Measures].[Count of Columns] - 1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null )
)

Measures.[All Members]

 

 

 

StrToValue(Generate(
IIF(IsError(Extract(Axis(1), Measures)),
Head(Axis(0), [Measures].[Count of Columns] ) AS L,"Axis(0).Item(0).Item(" + CStr(L.CurrentOrdinal - 1) + ").Hierarchy.CurrentMember.Level.Ordinal = 0", " AND " ,
Head(Axis(1), [Measures].[Count of Columns] ) AS L,"Axis(1).Item(0).Item(" + CStr(L.CurrentOrdinal - 1) + ").Hierarchy.CurrentMember.Level.Ordinal = 0", " AND "
)))  

 

 I encountered error messages when deploying the new codes, so i did not want to give it a shot for this one....The original expression is below.

IIF([Measures].[Is All Member], Axis(0).Item(0) / Sum( Filter( Axis(1), [Measures].[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)))

 

I tried to code those expression with nested IIF...i don't think i did write in the correct syntax...Please, help!  Thank you very much for your time and assistance!

BTW, do you have any trick to improve the performance a little?  It's taking too long relatively to other measures.

rhonda

PS: You can tell how novice i am with MDX or SSAS in general... you practically spoonfed me the code...but I still bug you for assistance  :-(

Answer 8

Try this, I haven't tested it though:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of Columns] AS
iif( IsError( Extract(Axis(1), Measures ) ), 
     Axis(0).Item(0).Count,
     Axis(1).Item(0).Count
   ),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Is All Member] AS
iif( IsError( Extract(Axis(1), Measures ) ), 
     iif( Axis(1).Item(0).Item( [Measures].[Count of Columns] - 1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null ),
     iif( Axis(1).Item(0).Item( [Measures].[Count of Columns] - 1).Hierarchy.CurrentMember.Level.Ordinal = 0, 1, null )
   ),
VISIBLE = 1 ; 

CREATE MEMBER CURRENTCUBE.[MEASURES].[All Member] AS
iif( IsError( Extract(Axis(1), Measures ) ), 
     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 " )
     ), 
     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 " )
     )
   ),
VISIBLE = 1 ; 

CREATE MEMBER CURRENTCUBE.[MEASURES].[Share of Plans] AS
iif( IsError( Extract(Axis(1), Measures ) ), 
     IIF([Measures].[Is All Member], 
	Axis(0).Item(0) /
	Sum( Filter( Axis(1), [Measures].[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))),
     IIF([Measures].[Is All Member], 
	Axis(0).Item(0) /
	Sum( Filter( Axis(1), [Measures].[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", 
VISIBLE = 1; 

You don't have to use iif() in case you're sure you'll use OWC or Excel. You can simply use previous definitions and replace "Axis(1)" with "Axis(0)".

That's for now. I'm busy at the moment. Optimization comes later.

Regards,


Tomislav Piasevoli    
Business Intelligence Specialist
www.softpro.hr

Answer 9

Hi Tomislav,

Thank you very much for your time...!  I tried the syntax that you had above as well....Perhaps, I was missing something...And with you magic, I hope it'll work out...!

Once again, your time and effort is greatly appreciated!

rhonda

Answer 10

Hi Rhonda,

I apologize for prolonging this. As I said, I was busy. Today, a timeframe opened so I took on the task to finish this properly.

In my previous post there were errors. I made it in a hurry. I've only replaced Axis(1) to Axis(0) in the first measure, not in the others. Otherwise, the syntax was ok, clumsy, but a working one.

Additionally, I took a look at your initial post since there usually lies explicit goal what should be reached. Having made a second look, I saw that you need 100% for subtotals. Therefore, I started modifying MDX. By doing so, it occured to me how it could be simplified (and therefore optimized as well). This time I did perform some tests on AdventureWorks :-). The solution is a variant of an MDX that can be found as an answer to a thread specified as the first link in my post above (suggested as an answer by Sid).

Be sure to test this for various scenarios, to see if it fits as required. Here's the MDX:

CREATE MEMBER CURRENTCUBE.[Measures].[Universal share percent %] AS
iif( IsError( Extract( Axis(1), Measures ) ), 
     iif( Axis(0).Item(0).Item( Axis(0).Item(0).Count - 1
                               ).Hierarchy.CurrentMember.Level.Ordinal = 0, 
          1,
	 Axis(1).Item(0) /
	 ( Axis(0).Item(0).Item( Axis(0).Item(0).Count - 1).Hierarchy.CurrentMember.Parent,
	   Axis(1).Item(0) )
        ),
     iif( Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1
                               ).Hierarchy.CurrentMember.Level.Ordinal = 0, 
          1,
	 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", 
VISIBLE = 1;

Regards,


Tomislav Piasevoli    
Business Intelligence Specialist
www.softpro.hr

Answer 11

Hi Tomislav,

I finally could solve a supposedly "big" problem with a simple solution...So, i could test the solution...I tested the "Universal Share Percent%", it did return an error when I drag and drop onto the "Total Area" using the Cube Browser..."MDXScript(Plans)(94,6) the function expects a string / numeric expression for the argument. A tuple set expression was used".  It's the same message for the Coordinate switch test condition...It works without the Coordinate test condition...So for now, i'm just using the expression without the If condition for Axis coordinate.

As always, your time and effort is greatly greatly greatly appreciated!

rhonda

Answer 12

Ehm, this turned to be more complex than I expected. I performed many tests. No matter how many tries I made, each time I encounter a case where something doesn't work. I modified the calculation over and over again, to comply with all types of queries OWC generates in the background. And not just that, it also depended on a version of SSAS underneath!

My previous calculation worked well on SSAS 2008 (I tested it), but didn't work on SSAS 2005 (that I haven't expected). Moreover, it had an logical error, so it couldn't work in all scenarios. Finally, it didn't work when there was something on columns (because OWC might put measures on Axis(2) in that situation).

Since I spent many hours on this, I decided to put the final solution on my blog: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!403.entry

I believe it finally works the way you want, Rhonda.

Regards,


Tomislav Piasevoli    
Business Intelligence Specialist
www.softpro.hr

Answer 13

Here's a direct link to a new article on my blog, covering additional problems discussed in a related thread that followed this one.

Related thread: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/71b4a8cc-9bb9-4084-bedf-17bc3fb4cfc3 .

Article: http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!436.entry .

Explanation included in the article.



Tomislav Piasevoli
Business Intelligence Specialist
www.softpro.hr