mdx cube calculation that may need to use some type of subquery

Hi All,

Here is my scenario.

My company owns and runs pubs.

Groups of 40-50 pubs are managed by a Business Development Manager (BDM).

The pubs get reorganised between the BDMs from time to time.

We have created a slowly changing dimension to reflect how the pubs move over time.

Everything works fine using this dimension.

Typical reports will have

A filter - to choose a BDM

Rows - one line per pub

Columns - The last Year's data, grouped by month.

We have the following requirement.

If a pub is currently with a BDM we need to show Last Year's data (typically sales and costs) for that pub even if the pub for that date range was not associated the BDM.

So we need to create a calculation (We will call it LY_Act) that ignores the slowly changing dimension and in essence says

"Get me Totals for each pub that I currently run, for the equivalent time period 12 months ago, and ignore who the BDM was at this time."

Can anyone give me some initial pointers on this.

Many thanks

Rob

This sounds like a job for SCOPE. Using the SCOPE statement, you can explicitly modify the SCOPE of a calculation, which should allow you to pull back any data you want in your calculation.

There's a blog post I wrote here about using SCOPE.

Here's a forum post about SCOPE.

And here's what MSDN says about SCOPE.

Rob,

First and foremost, I am by no means an expert in MDX, but I do think that this solution may fit your needs. You could possibly add a named query and do the calculation inside of it. Essentially creating a New Named Calculation. Will that meet your needs?

Brian

Hi Dustin,

Many thanks for taking the time to have a look at my question.

I've read all the links and the scope construct does look promising - although your solution that applied different scopes dependent on how you are slicing was quite something !!

So at the risk of seeming like I'm asking you to do my job for me, could you perhaps start me off.

Just to repeat - all of this year's numbers need to be filtered using the Company Hiearchy listed below.

All of last years numbers need to NOT be filtered by the Company Hiearchy.

I have one measure we need to adjust, named Act

The Time hierarchy we are using is  [Time].[Fiscal Year].[Fiscal Period].[Date]

The Company hiearchy we are using is [Division].[Director].[BDM].[Pub]

Kind regards

Rob

Many thanks Brian for taking the time to look at my question.

Can you expand what you mean by named query ?

Many thanks

Rob

In a SSAS project in BIDS, when you are creating your data source view, you can change your tables or views to what is called a named query. This named query basically acts like a view, where you can filter values or even create additional calculated columns. You could create a named query that pulls back each of your BDM's with a SUM of Sales and a SUM of Costs. Then of course create a relationship between the BDM to your fact or dimension tables defined in the data source view.

I hope this helps,

Brian

Here's an example of an MDX script that should help you get started:

CREATE MEMBER CURRENTCUBE.[Measures].[Act]
 AS NULL, 
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[SumMeasure]

AS SUM(calculatewhatever here), VISIBLE = 1;  

SCOPE ([Measures].[Act]); 

THIS = NULL; 

    SCOPE ([Product].[Product Categories].[Category].&[1],[Measures].[Act]);     THIS = (PARALLELPERIOD([Date].[Fiscal Year].[Fiscal Year], 1, [Date].[Fiscal Year].CurrentMember), [Measures].[SumMeasure]); 

    END SCOPE; 

END SCOPE; 

In this example, I create a measure called [Measures].[Act] with a default value of NULL. In my SCOPE statement, I begin by defining my subcube as that measure, essentially saying that no matter how that measure is sliced, it will default to a NULL value. Then I use another SCOPE statement to which says that when the SCOPE of an MDX statement includes the Product Category of 1 and the measure, [Measures].[Act], then apply THIS calculation, which is pulling.

In your case, maybe instead of using Product Category, you use Pub. I think with a little effort, you could get this to work. Let me know if you have any other questions.

Thanks Brian for the additional info.

I can now see what driving at.

I am for the moment going to see if I can solve the problem via mdx as I need to get myself more deeply immersed in it.

However, if my head explodes or I reach an impasse, then I will be back to take a look at this approach.

Thanks once again.

Rob

Hi Dustin,

Thanks for the fantastic code snippet.

Here is my attempt at applying it to my problem.

I have added it to my calculations tab and processed my cube with any errors but when browsing the cube no data is showing in [LY_Fix_Act]  

Can you have a look at my code  and see what you think

CREATE MEMBER CURRENTCUBE.[Measures].[LY_Fix_Act]  
AS NULL,   
VISIBLE = 1;  

CREATE MEMBER CURRENTCUBE.[Measures].[SumMeasure]  
AS [Measures].[Actual], 
VISIBLE = 1;    

   

SCOPE ([Measures].[LY_Fix_Act]);   
THIS = NULL;   

     SCOPE ([Cost Centre].[Operational Hierarchy],[Measures].[LY_Fix_Act]);
       THIS = (PARALLELPERIOD([Time].[Fiscal Year Period].[Fiscal Year], 1, [Time].[Fiscal Year Period].CurrentMember), [Measures].[SumMeasure]);   


     END SCOPE;   
 END SCOPE; 

 -----------------------

I suspect that my second scope command may be wrong but I am unsure how to ensure that the [LY_Fix_Act]  uses the THIS calculation in all scenarios.

Do I even need ([Cost Centre].[Operational Hierarchy] in the scope statement ?

Kind regards

Rob

Please ignore the javasript bits - newbie to using the insert code widget on the site

CREATE MEMBER CURRENTCUBE.[Measures].[LY_Fix_Act]  
AS NULL,   
VISIBLE = 1;  

CREATE MEMBER CURRENTCUBE.[Measures].[SumMeasure]  
AS [Measures].[Actual], 
VISIBLE = 1;    

   

SCOPE ([Measures].[LY_Fix_Act]);   
THIS = NULL;   

     SCOPE ([Cost Centre].[Operational Hierarchy],[Measures].[LY_Fix_Act]);
       THIS = (PARALLELPERIOD([Time].[Fiscal Year Period].[Fiscal Year], 1, [Time].[Fiscal Year Period].CurrentMember), [Measures].[SumMeasure]);   


     END SCOPE;   
 END SCOPE; 

Debugging goofy SCOPE statements can be a head ache. This is what I would do: Take out your calculations and replace it with 1. Then adjust the SCOPE part of your script until you see 1 popping up in your cube when you browse. Keep adjusting the SCOPE statement until 1 pops up in the correct place. Then once you get that right, you can work on getting the right calculation.

I think for you to get the right scope, you may have to avoid the hierarchy since the hierarchy relies on the current BDM. Try scoping the subcube to include the measure and the Pub dimension members.

DustinRyan said:
 Take out your calculations and replace it with 1.
Dustin,

Is this what you mean ?

CREATE MEMBER CURRENTCUBE.[Measures].[LY_Fix_Act]  
AS NULL,   
VISIBLE = 1;  

CREATE MEMBER CURRENTCUBE.[Measures].[SumMeasure]  
AS [Measures].[Actual], 
VISIBLE = 1;    

   

SCOPE ([Measures].[LY_Fix_Act]);   
THIS = NULL;   

     SCOPE ([Cost Centre].[Cost Centre],[Measures].[LY_Fix_Act]);
       THIS = 1

     END SCOPE;   
 END SCOPE; 

I think I have adjusted the scope as you suggested - pub is called Cost Centre within the Cost Centre Dimension.

You got it. You may have to do [Cost Centre].[Cost Centre].members, but try that and let me know. Once you have 1 popping up in the right place when you browse you know you are close.

Hi Dustin,

Well I have something now happening using the following code

CREATE MEMBER CURRENTCUBE.[Measures].[LY_Fix_Act]  
AS NULL,   
FORMAT_STRING = "#,#0;(#,#0)",
VISIBLE = 1;  

CREATE MEMBER CURRENTCUBE.[Measures].[SumMeasure]  
AS [Measures].[Actual], 

VISIBLE = 1;    
   

SCOPE ([Measures].[LY_Fix_Act]);   
THIS = NULL;   

     SCOPE ([Cost Centre].[Cost Centre].Members,[Measures].[LY_Fix_Act]);
      THIS = (ParallelPeriod([Time].[Fiscal Year Period].[Fiscal Year],1,[Time].[Fiscal Year Period].CurrentMember),[Measures].[SumMeasure]);  

     END SCOPE;   
 END SCOPE; 

I am displaying both my standard LY_Act calculation and my LY_Fix_Act next to each other for each period and they are exactly the same.

The only difference is that LY_Act is correctly shows the total for the year whereas LY_Act doesn't for some reason - it would be nice to get LY_Act to work in this ways as well.

Can you suggest some next steps ?

Thanks

Rob

Which measure is displaying correctly and which one is incorrect? I'm a little confused, but I think you're saying that the SCOPE statement is working and LY_Fix_Act is correct and LY_Act is incorrect. Let me know if thats not the case. If the SCOPE statement is working correctly and you want to duplicate the results for LY_Act, just duplicate the script except instead of setting the scope to LY_Fix_Act, set the scope for LY_Act.

Hi Justin,

LY_Act is my original function and works fine when showing data at the period level. So this means that it will only show data for LY_Act if the pub was with the BDM during that time period. For some reason it does NOT show a value when rolled up into Year.

CREATE MEMBER CURRENTCUBE.[MEASURES].[LY Act]
 AS (ParallelPeriod([Time].[Fiscal Year Period].[Fiscal Period],
12,
[Time].[Fiscal Year Period].CurrentMember),
[Measures].[Actual]), 
FORMAT_STRING = "#,#0;(#,#0)", 
VISIBLE = 0;            

My new Calculation [LY_Fix_Act] displays data but it always shows exactly the same values as LY_Act.

So it is as though the scope that I am trying to create is not having any effect at all.

The only difference is that the roll up into year gives the correct number rather than a blank.

CREATE MEMBER CURRENTCUBE.[Measures].[LY_Fix_Act]  
AS NULL,   
FORMAT_STRING = "#,#0;(#,#0)",
VISIBLE = 1;   

CREATE MEMBER CURRENTCUBE.[Measures].[SumMeasure]  
AS [Measures].[Actual], 

VISIBLE = 1;     
   

SCOPE ([Measures].[LY_Fix_Act]);    
THIS = NULL;    

     SCOPE ([Cost Centre].[Cost Centre].Members,[Measures].[LY_Fix_Act]); 
      THIS = (ParallelPeriod([Time].[Fiscal Year Period].[Fiscal Year],1,[Time].[Fiscal Year Period].CurrentMember),[Measures].[SumMeasure]);   

     END SCOPE;    
 END SCOPE;  

Can I send you a screen shot of what it looks like in excel ?

Rob

Sure, if you like. You can email it to me at dryan@pragmaticworks.com.

Whats the status on your calculation? Did you ever manage to get it straightened out?

Hi Dustin,

Sorry for not having been in contact but I got pulled off this work for the last week or so to work on something else....

I haven't made any more progress since coming back to the issue today.

Also I realise that my requirement is more complex than I initially specified in the following way.

I only want to show Last Year's Value if the BDM has it this year.

The rules could be summarised as

If BDM1 has the pub in June 2010 AND June 2009 then show a value for LY_ACT when displaying June 2010 data for BDM1

If BDM1 has the pub in June 2010 but NOT in June 2009 then show a value for LY_ACT when displaying June 2010 data for BDM1

If BDM1 does NOT have the pub in June 2010 and but does have the pub  in  June 2009 then do NOT show a value for LY_ACT when display June 2010 data for BDM1

In terms of mdx I need to understand why the code I am currently using is making no real difference the outputs.

Could you perhaps suggest what might be worth amending in my code.

Many thanks

Rob

Well its obviously not a problem with the SCOPE portion of your script since only the correct tuples are being modified, which leaves the issue as the calculation itself. Try creating a calculated measure in your cube that uses the formula you want. Once you get the numbers correct, then use that formula in your script.