fun with scoped assignments
DESCRIPTION
Fun with Scoped Assignments. Chris Webb www.crossjoin.co.uk. Who Am I?. Chris Webb [email protected] Independent Analysis Services and MDX consultant and trainer Author: “MDX Solutions” and “Expert Cube Development with SSAS 2008” SQL Server MVP Blogger: http://cwebbbi.wordpress.com. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/1.jpg)
Fun with Scoped Assignments
Chris Webbwww.crossjoin.co.uk
![Page 2: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/2.jpg)
Who Am I?
• Chris Webb [email protected]
• Independent Analysis Services and MDX consultant and trainer
• Author: “MDX Solutions” and “Expert Cube Development with SSAS 2008”
• SQL Server MVP• Blogger: http://cwebbbi.wordpress.com
![Page 3: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/3.jpg)
Agenda
• What are scoped assignments and why should I use them?
• Defining subcubes• Assignments to calculated measures• Complex subcubes• Assignments to real measures and aggregation• FREEZE and IF• Assigning to properties
![Page 4: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/4.jpg)
What are scoped assignments?
• Scoped assignments allow you to overwrite the value of cells inside your cube
• Contrast this with calculated members, which extend the space of the cube
• They are defined on the MDX Script of your cube, on the Calculations tab in BIDS
![Page 5: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/5.jpg)
Calculated Members
![Page 6: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/6.jpg)
Calculated Members
![Page 7: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/7.jpg)
Scoped Assignments
![Page 8: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/8.jpg)
Scoped Assignments
![Page 9: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/9.jpg)
Why should I use them?
• Among other things, they allow you to:– Make calculations behave in different ways in
different parts of the cube– Break up logic for complex calculations into more
comprehensible parts– Perform calculations at low levels of granularity
that can then be aggregated up very efficiently• They are often the most efficient way of
writing certain calculations
![Page 10: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/10.jpg)
Defining subcubes
• Scoped assignments have two parts:1. The subcube definition - the part of the cube
whose value will be overwritten2. The assignment - the new value written to the
subcube• There are two ways of defining a subcube– Direct assignment– The SCOPE statement
![Page 11: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/11.jpg)
Direct assignment syntax
• Direct assignment uses the following syntax:(subcube definition) = value;
• For example:([Measures].[Internet Sales Amount])=1;
• I prefer not to use it, because this syntax can very quickly become unreadable
![Page 12: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/12.jpg)
SCOPE statements
• SCOPE statements take the form:SCOPE(subcube definition);
THIS = value;END SCOPE;
• For example:SCOPE([Measures].[Internet Sales Amount]);
THIS=1;END SCOPE;
![Page 13: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/13.jpg)
Nesting SCOPE statements
• The advantage of using SCOPE statements is that you can nest several to build up complex subcubes, eg:SCOPE([Measures].[Internet Sales Amount]);
SCOPE([Date].[Calendar Year].[Calendar Year].MEMBERS);THIS=1;END SCOPE;
END SCOPE;
• This has obvious benefits for readability• You can also make assignments at different levels
within the nesting
![Page 14: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/14.jpg)
Subcube definition rules
• A subcube definition is made up of one or more sets of members from different hierarchies
• These sets can be included in a single SCOPE statement, or several nested SCOPE statements
• They define the boundaries of the subcube on different hierarchies
![Page 15: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/15.jpg)
Subcube definition rules
![Page 16: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/16.jpg)
Subcube definition rules
![Page 17: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/17.jpg)
Subcube definition rules
![Page 18: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/18.jpg)
Subcube definition rules
![Page 19: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/19.jpg)
Subcube definition rules
• There are limitations on the shape of a subcube you can assign to– If you break them, you will get the infamous ‘arbitrary
shaped set’ error• These are that a set of members can only contain:– One member from an attribute hierarchy– Several members from an attribute hierarchy, not
including the All Member– Every member from an attribute hierarchy, including
the All Member
![Page 20: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/20.jpg)
Not allowed!
![Page 21: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/21.jpg)
Subcube definition best practices
• Best to ignore user hierarchies• Include sets of members from different
hierarchies on the same dimension in the same SCOPE statement– Avoids problems with attribute overwrite
• Specifically scope on one or more measures– Unless you really do want to scope on all measures– Even then can cause problems if you add new
measures/measure groups in the future
![Page 22: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/22.jpg)
Assignments to calculated members
• Scoped assignments can be made to calculated members as well as real members– In fact, assignments to calculated measures are
probably the most common use– Calculated measures don’t aggregate up…
• You should not assign to a set containing both calculated members and real members– In most cases you can’t do this
![Page 23: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/23.jpg)
Complex subcubes
• Often we will want to scope on several attributes in a dimension at once– Eg all calendar year attributes, or all financial year attributes
• To do this, you need to know your attribute relationships
• The rule is:– For the lowest attribute, scope on all members in the
hierarchy including the All Member – For the highest attribute, scope on all members in the
hierarchy except the All Member
![Page 24: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/24.jpg)
Controlling area of calculation• Calculated measures often need to do different things in different
places• Some calculations should not be done at all in some places
– Eg you may not want to see a 3 month moving average at the year level– Eg previous period growth for your first year with data
• Some calculations may need different logic in different part of the cube– Eg tax calculations could be different every year– Eg calculation logic may change for business reasons, but you want to
see the older algorithm used for historical data– Eg previous year growth could mean ‘previous financial year’ or
‘previous calendar year’ depending on which hierarchy you’re using
![Page 25: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/25.jpg)
Assignments to real measures
• When you assign to real (ie not calculated) measures, any values you assign will automatically aggregate up through the cube
• This in turn may overwrite previous assignments that you have made
• …and make it very difficult to know what’s going on…
![Page 26: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/26.jpg)
Assignments and aggregation: example #1
120 30 30 30 30
40 10 10 10 10
40 10 10 10 10
40 10 10 10 10
![Page 27: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/27.jpg)
Assignments and aggregation: example #1
120 30 30 30 30
40 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 28: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/28.jpg)
Assignments and aggregation: example #1
120 30 40 30 30
50 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 29: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/29.jpg)
Assignments and aggregation: example #1
130 30 40 30 30
50 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 30: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/30.jpg)
Assignments and aggregation: example #1
120 30 30 30 30
40 10 10 10 10
40 10 10 10 10
40 10 10 10 10
![Page 31: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/31.jpg)
Assignments and aggregation: example #1
500 30 30 30 30
40 10 10 10 10
40 10 10 10 10
40 10 10 10 10
![Page 32: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/32.jpg)
Assignments and aggregation: example #1
500 30 30 30 30
40 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 33: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/33.jpg)
Assignments and aggregation: example #1
500 or 130?
30 40 30 30
50 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 34: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/34.jpg)
Assignments and aggregation: example #1
130 30 40 30 30
50 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 35: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/35.jpg)
Special cases for aggregation
• In some cases, a cell can be calculated in two different ways from previous assignments– When this happens, the assignment made last in
the script will take precedence– This is the ‘last pass wins’ rule
• Also, when there are unary operators, these will always take precedence– This is the ‘closest pass wins’ rule
![Page 36: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/36.jpg)
The ‘Last Pass Wins’ rule
120 30 30 30 30
40 10 10 10 10
40 10 10 10 10
40 10 10 10 10
![Page 37: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/37.jpg)
The ‘Last Pass Wins’ rule
120 30 30 30 30
40 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 38: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/38.jpg)
The ‘Last Pass Wins’ rule
120 30 40 30 30
50 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 39: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/39.jpg)
The ‘Last Pass Wins’ rule
130 30 40 30 30
50 10 20 10 10
40 10 10 10 10
40 10 10 10 10
![Page 40: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/40.jpg)
The ‘Last Pass Wins’ rule
130 30 40 30 30
50 10 20 10 10
50 50 50 50 50
40 10 10 10 10
![Page 41: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/41.jpg)
The ‘Last Pass Wins’ rule
140 or 290?
70 80 70 70
50 10 20 10 10
50 50 50 50 50
40 10 10 10 10
![Page 42: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/42.jpg)
The ‘Last Pass Wins’ rule
140 70 80 70 70
50 10 20 10 10
50 50 50 50 50
40 10 10 10 10
![Page 43: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/43.jpg)
The ‘Closest Pass Wins’ rule
60 15 15 15 15
40 10 10 10 10
40 10 10 10 10
20 5 5 5 5
+
+
-
![Page 44: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/44.jpg)
The ‘Closest Pass Wins’ rule
60 15 15 15 50
40 10 10 10 10
40 10 10 10 10
20 5 5 5 5
+
+
-
![Page 45: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/45.jpg)
The ‘Closest Pass Wins’ rule
60 15 15 15 50
40 10 10 10 10
40 10 10 10 10
20 5 5 5 5
+
+
-
![Page 46: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/46.jpg)
Uses for assignments to real measures
• There are several practical uses for making assignments to real measures:
• Replacing custom member formulas, so you can have all your code in the MDX Script– Always scope on members on the lowest level first
then work upwards• Performing calculations at a low level of
granularity and them aggregating the results– Eg currency conversion
![Page 47: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/47.jpg)
Assignment ‘Passes’
• Each new assignment creates a new ‘pass’ in the script
• Think of the cube like an onion, with each new pass as a new layer in the skin on the outside
• When you refer to an existing cell value in an assignment, you are actually referring to that cell value in the previous pass– Therefore avoiding infinite recursion
![Page 48: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/48.jpg)
The FREEZE statement
• When you assign a value to a cell, you create something like a pointer to that cell– So you don’t copy the new values assigned subcube
• So if the values the assignment points to subsequently change, the values in your subcube will change
• Using the FREEZE statement on a subcube stops this happening
• Do not use FREEZE as a substitute for correct ordering of calculations– There is a performance overhead for using it!
![Page 49: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/49.jpg)
The IF statement• Conditional assignments can be made using the IF statement:
SCOPE(MEASURES.SALES);IF
MEASURES.SALES>100THEN
THIS=0END IF;
END SCOPE;
• Note that even when the condition evaluates to false and no assignment is made, a new pass is created
![Page 50: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/50.jpg)
Assigning cell property values• The following cell property values can also be directly
assigned to: Format_String, Non_Empty_Behavior, Language, Font_Size, Font_Flags, Font_Name, Fore_Color, Back_Color
• Error values can be assigned to cells using the ERROR statement
• Useful when:– A single measure holds values in different currencies which
should be formatted differently depending on the currency– Debugging complex subcube definitions
![Page 51: Fun with Scoped Assignments](https://reader035.vdocuments.us/reader035/viewer/2022062315/5681652e550346895dd7b144/html5/thumbnails/51.jpg)
Thanks!