I’ve had a lot of requests for more MDX content on my blog, so here’s something I’ve been meaning to write up for a long time: a worked example of how to use scoped assignments to implement two different types year-to-date calculation on two different hierarchies in the same dimension. Knowledge of how to use scoped assignments is the sign of a true MDX master (you can watch a video of a session I gave on the basics of scoped assignments at SQLBits here if you’re unfamiliar with them) but that’s because they can be very difficult to write and there’s surprisingly little information out there on the internet about them. They are incredibly powerful, though, and often they provide the most elegant and best-performing way to solve a problem.
Let’s start by looking at the Date dimension in the Adventure Works DW sample database, and more specifically the attributes, user hierarchies and attribute relationships:
Notice how we have two user hierarchies for Calendar Years (which start on January 1st) and Fiscal Years (which start on July 1st), called Calendar and Fiscal. Now, let’s say that you have a requirement to to show year-to-date values for a measure for both the Calendar and Fiscal hierarchies. It would be very easy to implement this as two separate calculated measures but what if you needed to show both types of year-to-date in the same calculated measure, showing Fiscal year-to-dates when the Fiscal hierarchy was used in a query and Calendar year-to-dates when the Calendar hierarchy was used in a query?
This is possible using scoped assignments. The first thing to point out, though, is that this is only going to be possible if you change the structure of the dimension. Why? Well, take a look at the Date levels of both hierarchies: they are both built using the Date attribute. If you were running a query with your YTD calculation on columns and only the Date attribute hierarchy on rows, would you expect to see Calendar or Fiscal YTD values? Certainly you couldn’t see both in the same cell, and this is the problem: if you expect to see Calendar YTD values at the bottom of your Calendar hierarchy, and Fiscal YTD values at the bottom of your Fiscal hierarchy, you need two separate Date attributes to do this. If you overwrite the values in the same cells twice using a scoped assignment, then you will only see the result of the second assignment.
Therefore, what we need to do is to create two new attributes, Calendar Date and Fiscal Date, to serve as the lowest levels of the Calendar and Fiscal hierarchies instead of the Date attribute. Here’s what the new attribute relationships look like:
From the end-user’s point of view nothing seems to have changed (these new attributes can have their AttributeHierarchyVisible property set to False) but this now means we have two attributes, two different slices of the cube, whose values we can overwrite separately instead of just one.
Now for the MDX. A good rule to follow when writing scoped assignments is to always use attribute hierarchies and never use user hierarchies; this is because there are rules you have to obey about the shape of the subcube of data you are overwriting with your scoped assignment. When defining a scope using only attribute hierarchies, you can only use the following types of set:
- Every single member on the attribute hierarchy
- Just one member on the attribute hierarchy
- Multiple members on the attribute hierarchy not including the All Member
I wrote up a detailed set of rules for defining scopes in MDX Solutions, if you’re interested; if you don’t follow these rules you’ll get the infamous “An arbitrary shape of the sets is not allowed in the current context” error (I know a joke about that, incidentally).
For this calculation, we need to make two scoped assignments on a single calculated measure called [YTD Sales]: one to show the Fiscal YTD calculation for the Fiscal Date, Fiscal Month Name, Fiscal Quarter, Fiscal Semester and Fiscal Year attributes; and one to show the Calendar YTD calculation for the Calendar Date, Calendar Month Name, Calendar Quarter, Calendar Semester and Calendar Year attributes. When scoping on ranges of attributes like this, there’s another easy rule to follow: scope on the set of every member, including the All Member, from the attribute hierarchy at the lowest granularity attribute, and the set of every member, not including the All Member, from the highest granularity attribute. These two sets also need to be in the same, rather than separate, SCOPE statements for reasons I explain here.
This results in the following MDX:
Now you can see the YTD Sales calculated measure returns Calendar YTD values for the Calendar hierarchy:
…and it returns Fiscal YTD values for the Fiscal hierarchy:
There’s one last trick I want to show. It can be very difficult to know that your scoped assignment is covering the subcube you want it to cover, so while debugging scoped assignments I find it helps to assign values to the BACK_COLOR cell property so I can see exactly where I’m scoping. Here’s the MDX above with extra assignments to set the cell background colour to orange for the Calendar YTD calculation and blue for the Fiscal YTD calculations:
This now shows up in an Excel PivotTable as you can see below:
It can also help you understand what’s going on in the scenarios where the scopes overlap, for example if you put the Calendar and Fiscal hierarchies on rows and columns in the same PivotTable: the Fiscal hierarchy takes precedence, because it’s defined second.
Ignou Eso-11 Free Solved Assignment 2012
4877 WordsOct 8th, 201220 Pages
IGNOU ESO-11 Free Solved Assignment 2012
Presented by http://www.IGNOU4U.Blogspot.com
Tutor Marked Assignment (TMA)
Programme Code: BDP
Course Code: ESO-11
Assignment Code: ESO-11/
Section – I
Answer the following in about 500 words each.
1. Describe the major concerns of Sociology and discuss its nature and scope. 20
Solution: Sociology concentrated heavily on society and its major units and their dynamics. It has been striving to analyse the dynamics of the society in terms of organised patterns of social relations.
Firstly, the major concern of sociology is sociological analysis. It means the sociologist seeks to provide an analysis of human society and culture with a sociological perspective. He evinces his…show more content…
Outline the features of a complex society with suitable examples. 20
Solution: A complex society is a social formation that is otherwise described as a formative or developed state. The main criteria of complexity are the extent of a division of labour in which members of society are more or less permanently specialized in particular activities and depend on others for goods and services, within a system regulated by custom and laws, the population size of a human community; the larger the population, the more complex and variegated the co-existence of people tends to become. Social complexity in this sense thus refers typically to political complexity, specifically the presence of a hierarchy in the form of a ruling elite supported by bureaucrats, with associated paraphernalia such as administrative buildings and elite residences in urban or proto-urban population centres. Complex societies under this definition are also agricultural to provide the surplus required to support a social (non-food producing) elite. Explaining the origins of these types of social formations, which appear in many areas of the world, is one of the tasks of archaeology (see, e.g., History & Mathematics: Historical Dynamics and Development of Complex Societies).
There are, however, problems with the term "complexity" when used in this manner. It has been argued that using political organisation (or