In this ending part of the tri series posts let’s check out the third option in the calculations tab which is “Script Commands”. Here, I am focusing on the SCOPE statement with which we can apply a calculation to a specific portion of the cube. I have already demonstrated the first two options i.e. Calculated Members and Named Sets in my previous posts which are available in the Part I and Part II of this series.
Any calculation or script command that we add inside a cube applies to the entire cube i.e. the default scope of any calculation or script command is the whole cube. Scope assignment is used to limit the scope of a calculation so as to apply it only to a part of the cube. To achieve this, a SCOPE statement is used to define a subcube, which defines the scope of all subsequent MDX expressions and statements within the calculation script until the current scope ends or is re-scoped. The THIS function is then used, which applies the MDX script to the current scope i.e. to a particular cube space.
When we create a cube, a default MDX script is also created which defines calculations in the cube. Open the calculation tab in the cube designer and you will find the calculate command which is the very first statement inside the script.
/* The CALCULATE command controls the aggregation of leaf cells in the cube. If the CALCULATE command is deleted or modified, the data within the cube is affected. You should edit this command only if you manually specify how the cube is aggregated. */ CALCULATE;
Each time we create a new calculated member or a named set, the code gets added to this script. The script informs the server to calculate the cube. These MDX statements, expressions etc. then processed in the same order they appear in the Script Organizer.
Confused…Ok, let’s understand this step by step with the help of a simple example to know how it exactly works using Adventure Works cube. Consider the following view where I am browsing the cube by slicing the Sales Amount measure by Sales Territory Group, Sales Territory Country and Order Date Year.
You can see the Sales by Sales Territory Country for the Orders placed in the year 2001, 2003 and 2004. Now suppose we want to have a forecasted Sales figure for the year 2006 and that too for the Sales Territory group Europe. We can achieve this by writing an MDX calculation script using the SCOPE statement. As we need to apply this calculation only to a specific portion of the cube, we need to first define our subcube using the SCOPE statement as follows,
SCOPE
(
[Measures].[Sales Amount],
[Order Date].[Calendar].[Year].[2006],
[Sales Territory].[Sales Territory Group].&[Europe]
);
THIS = [Order Date].[Calendar].[Year].[2004] * 1.1;
End Scope;
The THIS function is applying the calculation to the subcube i.e. the Sales figure for the Year 2006 is arrieved at by adding 10% margin to the Sales value of 2004. Deploy and process the cube and click on Reconnect to see the results of the calculation as follows,
The forecasted Sale for 2006 is highlighted in green. Notice that the rest of the values are intact. But why we can not see the Sales Amount value against each Country within Europe. This is because it depends on how we define the subcube. To have a country wise breakup of the Sales value, modify the SCOPE statement as follows,
SCOPE
(
[Measures].[Sales Amount],
[Order Date].[Calendar].[Year].[2006],
[Sales Territory].[Sales Territory Group].&[Europe],
[Sales Territory].[Sales Territory Country].[Sales Territory Country].members
);
THIS = [Order Date].[Calendar].[Year].[2004] * 1.1;
End Scope;
Deploy and process the cube and you can see the country wise uplifted Sales as highlighted in green,
So now, if a user asks you to manipulate some measures for specific dimension members, you will definitely give it a try. Also good knowledge of MDX will definitely help you to write the calculations in a better and innovative way. Whatever I have shown in my series is just a tip of an iceberg and there are many more things to explore. Hope the series will serve as a starting point to help you understand the concept behind using the Calculations tab to define calculated members, named sets, or scoped assignments in a cube.
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Excellent, simple and very informative article. Kudos to you Amit.
Great article Amit. I have a question. How to use the SCOPE to return the slice by other dimension? Here is my problem. I have a list of products that can be order from multiple different manufacturers. For example, we order 100% of productA and 50% of productB from manufacturerA. And and the order amount are $200 & $300 as below. So from manufacturerA, we SHOULD have $350 order amount; However, instead the mdx returns $750. This is because (100% + 50%) X ($200 + $300) = 150% X $500 = $750.
manufacturerA productA 100% $200 = $200
manufacturerA productB 50% $300 = $150
I tried the SCOPE command below but get the same result. Would you please help?
SCOPE ( Measures.orderAmount, Manufacturer);
This = Measures.orderAmount * rate;
End Scope
Hi Jane,
This is a common issue when working with the percentage data like Discount Percentage etc. I am assuming here that you are talking about the MDX Script which can be written in the Calculations tab of Cube Designer in BIDS. (Correct me if I am wrong)
I guess, in the SCOPE command which you have written, simply replacing the Manufacturer with [All] Product Level will solve the issue as you want to add the amount by each product to arrive at the total amount at the manufacturer level.
Please go through the following explanation for more clarity in which I have suggested an approach that I usually follow…
I am naming the column in question as “OrderAmtByManufacturer”.
One way is to define this column at the DSV level as a “Named Calculation” in the fact table (OrderAmount * Rate) which will calculate the OrderAmtByManufacturer for each row. This will automatically solve the issue of aggregating the data i.e.
manufacturerA productA 100% $200 = $200
manufacturerA productB 50% $300 = $150
Total = $350
Another way is to create a Calculated Member using the calculations tab and then write the MDX Script to define the scope of the calculation as you have tried.
The following script will create a calculated measure…
CREATE MEMBER CURRENTCUBE.[Measures].OrderAmtByManufacturer
AS [Measures].[OrderAmount] * [Measures].[Rate],
FORMAT_STRING = “Currency”,
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = ‘Measure Group Name’;
This will arrive at the OrderAmtByManufacturer by multiplying Order Amount with the Rate (Percentage) at the leaf level.
Next is to define the Scope of the calculation when the data aggregates by manufacturer by adding the OrderAmtByManufacturer at each product level.
SCOPE([Product].[Product].[All]);
[Measures].OrderAmtByManufacturer=
sum
(
[Product].[Product].[Product].members,
[Measures].OrderAmtByManufacturer
);
END SCOPE;
The above code is for reference purpose only and based totally on my assumption of the cube structure in question.
Do the changes in your code accordingly and it should work…
Best Luck…
Amit
Amit, you are absolutely awesome!! I tried the second method (with scope) and it work great!! You have made my day. Thank you!!!!!
Welcome Jane… In fact I have also learnt some new things 🙂
hi amit. the example you gave to Jane, with the scope set at [product].[product].[all]… how to resolve if i want to scope to apply, doesn’t matter if it’s sliced by any dimension. i also have 2 columns (amount * price), and the calculation must always just occur at ‘leaf’ level. if my calculated member is the only value summed up, then it must also only apply that multiplication at leaf level, and sum up the resulting values. in effect its currently doing: sum(amount)*sum(price), across all my facts… i understand i can easily fix this in my dsv, but i would like to learn the correct mdx too..
thanks
HI Amit,
I’m new to the mdx world by looking into your article I got some how scope works in different situation
Can you please give similar kind expalnation for below code
Scope
(
[Date]
.[Fiscal Year].&[2009],
[Date]
.[Fiscal].[Fiscal Quarter].Members,
[Measures]
.[Sales Amount Quota]
);
This
= ParallelPeriod
(
[Date]
.[Fiscal].[Fiscal Year], 1,
[Date]
.[Fiscal].CurrentMember
) * 1.35;
/*– Allocate equally to months in FY 2006 —————————–*/
Scope
(
[Date]
.[Fiscal Year].&[2006],
[Date]
.[Fiscal].[Month].Members
);
This
= [Date].[Fiscal].CurrentMember.Parent / 3;
End Scope;
/*– Weighted allocation to remaining months —————————*/
// Pin quarterly values prior to assigning weights for months
// This is done in order to avoid overwriting the quarterly values
// once weights are entered for monthly values.
Freeze
(
[Date]
.[Fiscal].[Fiscal Quarter].Members,
[Measures]
.[Sales Amount Quota]
);
// Scope on month level in FY 2007 and onwards
Scope
(
[Date]
.[Fiscal Year].&[2007] : Null,
[Date]
.[Fiscal].[Month].Members
);
// Compute weights based on reseller sales ratio in previous year
This
=
(
ParallelPeriod
// Fetch reseller sales amount in previous year
(
[Date]
.[Fiscal].[Fiscal Year], 1,
[Date]
.[Fiscal].CurrentMember
),
[Measures]
.[Reseller Sales Amount]
)
/
(
ParallelPeriod
// Divide monthly value by quarterly value to obtain ratio
(
[Date]
.[Fiscal].[Fiscal Year], 1,
[Date]
.[Fiscal].CurrentMember.Parent
),
[Measures]
.[Reseller Sales Amount]
);
// Allocate quarterly values to months according to weight
This
= [Measures].CurrentMember * [Date].[Fiscal].Parent;
End Scope;
End
Scope;
Hi ,
I want to show child level measure at parent level using scope. I can use the below query to show minimum of measures.
scope( [Customer].[Hierarchy].[Country],agg2);
this=(tail(order( nonempty( descendants([Customer].[Hierarchy].currentmember,[Customer].[Hierarchy].[State-Province]),[Measures].[Internet Sales Amount]),[Measures].[Internet Sales Amount],bdesc),1).item(0) ,[Measures].[Internet Sales Amount]);
end scope;
But I need to assign [Measures].[Internet Sales Amount] of dimension [Customer].[Hierarchy].[State-Province] to its parent level which is [Customer].[Hierarchy].[Country].
I tried like this way, but showing error.
scope( [Customer].[Hierarchy].[Country],agg2);
this=(descendants([Customer].[Hierarchy].currentmember,[Customer].[Hierarchy].[State-Province].parent),[Measures].[Internet Sales Amount]);
end scope;
Any help is appreciated..
Thanks in advance,