Chapter 7: Advanced Topics in MDX

This chapter follows up on and extends what you learned back in Chapter 3: the basic concepts of the MDX language and how to write MDX queries. You also learned about the MDX operators and functions that are supported by the MDX language; including calculated member and named sets creation. If you are thinking, "that was so four chapters ago, I already forgot everything!" you might want to go back and review the material before continuing here. In Analysis Services 2005 majority of the calculations are defined as a script called MDX scripts. MDX scripts constitute complex calculations on multidimensional data which consist of MDX statements. CALCULATE, SCOPE, IF-THEN-ELSE and CASE are some of the MDX statements that are used within MDX scripts that help you define complex calculations that affect cube data. MDX scripts are structured in a way that the flow of the statements are simple and readable. The scripting language itself is based on a procedural programming model and although it may sound complex, it is actually simpler to use than certain predecessor technologies. This is due to simplifications in syntax. You can actually step through statements in MDX scripts and see results formulated in real-time; a real boon to the debugging process which you learn in Chapter 9.

Of particular importance for successful MDX Script usage is an understanding and mastery of the various ways calculation order can be specified. In this chapter, you learn more about that functionality — specifically, about how calculations are implemented and evaluated in Analysis Services 2005 using MDX scripts. There is a new flow model that replaces explicit manipulation of solve order and calculation pass precedence which were used for ordering calculation is Analysis Services 2000. Additionally, this chapter provides you with examples of common MDX queries that users could write to solve real business problems. These examples are created for use with the sample Adventure Works DW project that ships with the product. For details on the process of debugging calculations, see Chapter 9, which covers this and other advanced topics related to cubes. Some of the MDX queries you will need to write to solve business problems necessitate the use of cube space restriction, empty cell removal, and parameterized queries — all concepts covered in this chapter.

Calculation Fundamentals

At the core of the Analysis Services engine is the ability to model various complex business problems using calculations. In Analysis Services 2000, calculations were based on dimensions such that each dimension typically contained one or more multilevel hierarchies. The calculation model of Analysis Services 2005 differs from the previous version because it combines the traditional OLAP and relational worlds. That combination occurs by leveraging the Unified Dimensional Model through attribute hierarchies (which are entities within a dimension). The attributes and the relationships between the attributes form the basis of the calculation model. The hierarchies (attribute or multilevel) are a way of navigating the dimensional space. Attribute hierarchies typically have two levels, the optional "All" level and another level that contains all the members of the attributes. Hence cells in the cube space can be accessed directly through the attribute hierarchies or multilevel hierarchies, and you will get the same results. Most of the calculation definitions for a cube are defined within an entity called MDX Script, which is part of every cube object. Some calculations are specified as properties of dimension attributes. Even while defining security for various hierarchies within a dimension, you specify the security restrictions through the attribute hierarchies of the dimension. Hence, attributes form the fundamental building blocks for all calculations in Analysis Services 2005.

MDX Scripts

MDX Scripts contains a set of MDX Statements separated by semicolon. MDX Scripts typically contain the calculations that need to be applied to a cube including creation of calculated members, named sets and calculations for the cells in Analysis Services 2005 cubes. The cube is populated based on the calculations defined in the cube. Users of the cube can have different security permissions defined for dimensions and cubes within a database (you learn about securing data in Chapters 9 and 19). Therefore when a user connects to a cube Analysis Services 2005 evaluates the security permissions for the user. After the security permissions are evaluate the user gets assigned a cube context. The calculations being applied to a user's cube context is based up on the security permissions. The data populated within the cube for the user is based on the security permissions for that user. If a cube context for a specific set of permissions already exists then the user is automatically assigned to that specific cube context.

You use MDX Scripts to write your complex business calculations in an easy-to-read manner. All the calculations are a sequence of statements that are self contained, similar to a procedural language. Analysis Services 2005 allows multiple people to collaborate and develop the UDM for a company. Even if more than one user is defining the calculations for the UDM, the calculation can be included in a single MDX script with appropriate comments, which can reduce potential errors. No more quarrels with your co-worker about who made the mistake and why the cube is not working. Analysis Services 2005 tools help you debug MDX scripts interactively, more like debugging a program to identify any semantic errors in calculations defined in the script. Syntactic errors are automatically flagged by Analysis Services when the cube is deployed to Analysis Services. The real value of the MDX script is to define calculations that assign values to cells in the cube space based upon complex business conditions. Analysis Services 2005, with the help of the SCOPE statement, helps you narrow down the cube space to which the calculation needs to be applied. For complex conditions that cannot be covered using the SCOPE statement, use the CASE operator. An example of an assignment is to allocate your sales quota based for next year based on the sales of the current year. You see more about this later in this chapter.

When you have a large script with several calculations, there might be instances where you will want the calculations to be applied in a different order than that dictated by the MDX script. Some calculations are specified as properties of dimension attributes, such as custom rollup column and unary operator column (this is shown in Chapter 8). Calculations applied to a cell not only depend on the calculations in MDX scripts but also how the calculations custom rollups and unary operators change the cell value. Analysis Services 2005 applies the calculations to cube cells based on a set of precedence rules.

CALCULATE Statement

When you create a cube using the Cube Wizard within the Business Intelligence Development Studio, a default MDX script is created for you. You can see the script definitions in the Calculations tabs of the Cube Designer as shown in Chapter 6 and again in Chapter 9. The CALCULATE statement is added to the script by the Cube Wizard. This statement indicates that the Analysis Services instance is to aggregate the data from the lowest level of attributes and hierarchies to higher levels. Aggregation of data to various levels of a hierarchy is illustrated in Figures 7-1 through 7-3. Assume you have a cube that has three dimensions: Geography, Products, and Time, with hierarchies Customer Geography, ProductLine, and Date, respectively. For illustration purposes assume Customer Geography and ProductLine are single-level hierarchies and Date is a multilevel hierarchy with levels Quarter, Semester, and Year.

Figure 7-1

Figure 7-2

Figure 7-3

When a user accesses the cube, the fact data first gets loaded into the cube as shown in Figure 7-1; which represents data for a specific year. Depending on the storage type (ROLAP, MOLAP, or HOLAP) the fact data would be retrieved from the relational data source or from local Analysis Services storage. This is referred to as PASS 0 within Analysis Services. Consider PASS as an analogy of doing a first visit of all the cells within the cube. Once the fact data has been loaded into the cube, Analysis Services applies calculations for the cells based on the calculations specified in the MDX scripts or dimensions attributes. Assume this cube has the default MDX script with a CALCULATE command. After loading the fact data, Analysis Services executes the MDX script. When the CALCULATE statement is encountered, the fact data aggregated for appropriate levels of the dimension hierarchies is made accessible to end users. Because the Product Line and Customer Geography hierarchies have only one level, there is no need to aggregate the data. The Date hierarchy has the levels Semester and Year for which the data needs to be aggregated from the Quarter level. When the CALCULATE statement is encountered a new PASS; PASS 1, is created where aggregated data can be seen for various levels. Analysis Services aggregates the data for the Semester level from the Quarter level as shown in Figure 7-2 and then to the Year level as shown in Figure 7-3. You will be able to query the aggregated data.

If the CALCULATE statement is not specified in the MDX script, you will not be able to query the aggregated data for Semester and Year. If you query the data for the levels Semester and Year you will get null values. A concern about missing a CALCULATE statement in the MDX script is that you can retrieve the fact level data only when you include all hierarchies of all dimensions in your query. For example, if you have the following query:

     SELECT [Measures].[Internet Sales Amount] on COLUMNS,     Product.[Product Line].Members on ROWS     FROM [Adventure Works] 

You will only see null values. In order to retrieve the fact data, you would need to send the following query to Analysis Services:

     SELECT [Measures].[Internet Sales] on COLUMNS,     Product.ProductLine.Members *     Customer.[Customer Geography].members *     Time.Quarter.Members *     Time.Semester.Members *     Time.Year.Members *     Time.Date.Members     on ROWS     FROM [Adventure Works] 

If a cube does not have an MDX script defined then a default MDX script with CALCULATE statement is used as default by Analysis Services. We do not expect users to have MDX scripts without the CALCULATE statement other than by mistake. If you do not have any calculations defined in MDX scripts and your queries return null values for various hierarchies we recommend you check if the CALCULATE statement is included in the MDX script.

Named Sets

In Chapter 3 you learned about Named Sets. You learned that you can create these named sets within your MDX queries and access them. Following is the example of named sets created within an MDX query in Chapter 3:

     WITH SET [EUROPE] AS {[Customer].[Country].[Country].&[France],     [Customer].[Country].[Country].&[Germany],[Customer].[Country].[Country].&[United     Kingdom]}     SELECT Measures.[Sales Amount] on COLUMNS,     [EUROPE] ON ROWS     FROM [Adventure Works] 

When the named sets are created within an MDX query they can only be accessed within the scope of the query. Other queries within the same session or other users in different sessions cannot access these named sets in their queries. Some of the named sets might be useful for several users, and it would be better to create them so that they can be shared across several queries or even several users. Analysis Services provides ways of defining named sets within a specific session where you can send multiple queries, or in a cube's MDX script where they can be accessed by multiple users using the CREATE statement. In both cases you create sets as shown here:

     CREATE SET [Adventure Works].[EUROPE]     AS ' {[Customer].[Country-Region].[Country-Region].&[France],     [Customer].[Country-Region].[Country-Region].&[Germany],[Customer].[Country-     Region].[Country-Region].&[United Kingdom]}'; 

Instead of the WITH clause that you used in the MDX query for set creation, the CREATE statement allows you to create a set within the scope of a session or the entire cube. When you define sets using the CREATE command, you need to specify the cube name as a prefix as shown in the CREATE statement as seen above with the [Adventure Works] cube. You do not specify the name of the dimension when you specify the set. This is because a set can contain tuples that are formed by multiple hierarchies that are from one or more dimensions. Hence irrespective of whether all the tuples in a set are from a single hierarchy or multiple hierarchies, it is not considered to be part of any single dimension.

Once the set has been created using the CREATE command, it can be accessed in any query. If the named set was created within a session, it would be valid only within that specific session and could not be used by users in other sessions. If the named sets are to be used by several users, we recommend you create them in the cube scope by defining them in the MDX script. Named sets can be in one of the three scopes when an MDX query is being executed:

• They can be within the query scope where they are defined with the WITH clause in MDX.

• They can be within the session scope where they can be created within a specific session using the CREATE statement you learned now.

• They can be scoped as global and defined within an MDX script using the CREATE statement.

Analysis Services checks for resolving members or sets in an MDX query within query, session, and global scopes, respectively. Once you have created the named sets (either in session or global scope) executing the following query will result in the same values that you obtained when you had the named set created using the WITH clause:

     SELECT Measures.[Sales Amount] on COLUMNS,     [EUROPE] ON ROWS 

Named sets are really convenient and useful for querying because the MDX queries using them are easy to read and allow multiple users to access them. However, you should be aware that there is a memory cost associated with holding them in Analysis Services. If you need to create a large number of named sets that are quite large in terms of number of tuples, then exercise caution. We recommend you drop any named sets whenever they are not used. Just as there is a CREATE statement, there is a statement to delete named sets or calculated members as well. The DROP statement does the job of deleting sets and calculated members. The syntax for the DROP statement is simple:

     DROP SET <setname> 

Calculated Members

Similar to named sets, you learned that calculated members can be created using the WITH clause within a query. Following is the query used in Chapter 3 for creating a calculated member:

     WITH MEMBER [MEASURES].[Profit] AS '([Measures].[Sales Amount]-                 [Measures].[Total Product Cost])'     SELECT [MEASURES.[Profit] ON COLUMNS,         [Customer].[Country].MEMBERS ON ROWS     FROM [Adventure Works] 

As with named sets, calculated members can also be created using the CREATE statement as follows:

     CREATE MEMBER [Adventure Works].[Measures].[Profit]     AS '([Measures].[Sales Amount] - [Measures].[Total Product Cost])'; 

For creating a member you use the CREATE MEMBER followed by the member name. While creating named sets you did not specify which dimension the named set belonged to. However, while creating calculated members you do specify the cube and dimension name. Notice that all measures within a cube are always within a special dimension called Measures. Hence in the preceding CREATE statement, the cube name and the dimension Measures is specified. The calculated members that are most often created by users are calculated measures, that is, calculated members on the Measures dimension. For convenience Analysis Services 2005 assumes that a calculated member will be in the Measures dimension if it is not prefixed with Measures. Hence the following statement is a valid syntax:

     CREATE MEMBER [Profit]     AS '([Measures].[Sales Amount] - [Measures].[Total Product Cost])'; 

Once the calculated members have been created, you can use them as shown in the following query. The query scope, session scope, and global scope seen for named sets also apply to calculated members.

     SELECT [MEASURES.[Profit] ON COLUMNS,         [Customer].[Country].MEMBERS ON ROWS     FROM [Adventure Works] 

Similar to named sets, calculated members can also be dropped using the DROP MEMBER statement. The syntax for DROP MEMBER is

     DROP MEMBER <member name> 

Typically, client tools interacting with Analysis Services that create MDX queries dynamically based on user actions on the front end create and drop calculated members within sessions. One example is the OWC Pivot table control, which is used with the Cube Browser. You will see some of the MDX queries that are sent to the Analysis Services instance by client tools in Chapter 15, with detailed explanations of what these MDX queries mean.

Analysis Services 2005 provides a new way to define calculated members at the global scope within MDX scripts. This helps you create a member first without any definition and later define the expression. The MDX statements below help demonstrate this new definition for calculated members. The statements below will evaluate to the same results as that of the CREATE MEMBER statement. If the dimension name is not specified it is assumed the calculated member is part of the Measures dimension.

They are semantically the same but it is a matter of convenience if say, you want to create a calculated measure and are not sure about the actual expression. You can define the calculated member, use it in statements and finally create the actual expression in the MDX script.

     CREATE MEMBER [Profit];     [Measures].[Profit] = [Measures].[Sales Amount] - [Measures].[Total Product Cost]; 

Named sets and calculated members are the basic objects created within MDX scripts most likely to be exploited by users of the cube. You can specify properties such as format string, font, color, and background color for the calculated members as discussed in Chapter 6. Next, you see some of the additional statements that are used within MDX scripts and how they help you define your business calculations.

Cube Space and Autoexists

The cube space (cells) in an Analysis Services cube can be calculated as the product of the member count in each attribute from each dimension. As you can imagine, this space is quite large even for a small cube that contains less than ten dimensions, with each dimension containing around ten attributes. The entire cube space is quite large and often the cells that have the data are quite sparse as compared to the entire space. When we refer to the data in the cube space we do not just refer to the data in the fact table. You can apply calculations through MDX expressions to various cells in your cube space. Most of these calculations are defined within MDX scripts.

Before you learn to specify cell calculations you need to have a better understanding of the cube space that comprises all the cells. Some cells in the cube space retrieve data through calculations or are aggregated up across dimensions based on your business definitions within the cube. For example, consider the budget of your company for next year or the sales quota for next year, which are typically calculated based on profit or sales of the current year. This data is not available in the fact table but is likely based on a calculation of the data in the fact table. Similarly, you can have cell values for which data is aggregated from fact data due to cube modeling scheme.

Assume you have a sales cube with a Time dimension containing a Time hierarchy with levels Year, Quarter, Month, and Date. In this case, the fact table contains data for each day. Analysis Services would aggregate the data for cells corresponding to month, quarter, and year. We refer to the cube space that is accessible to the users, and which can be manipulated through calculations, as the real cube space. Certain cells in the cube space can be accessed through MDX queries but are actually not of the real cube space. For example, assume a Customer dimension that has attributes Name, Gender, and Marital Status. There is customer named Aaron Flores who is Male in the Adventure Works DW sample database. The cell corresponding to Customer.Customer.[Aaron Flores] and Customer.Gender.Female does not exist in the cube space:

     select [Customer].[Customer].[Aaron Flores] on 0,     [Customer].[Gender].&[F] on 1     from [Adventure Works] 

You can request the cell corresponding to this coordinate with the preceding MDX query and you will get a null value. You might want to see multiple attributes of a dimension on a specific axis. If you do a cross-join of multiple attributes you will get the entire cross product of all the member of the attributes involved in the cross-join. However, if you do a cross-join of attributes within the same dimension, Analysis Services eliminates the cells corresponding to attributes members that do not exists with one another in the cube space. This specific behavior is called AUTO EXISTS, which can be interpreted as an EXISTS function automatically being applied to attributes within the same dimension. The EXISTS MDX function, which you learn about later in this chapter, takes two sets and returns members of one set that exist with one or more tuples of one or more other sets. Analysis Services 2005 automatically applies EXISTS for attributes within the same dimension when they are included on the same axis. For example, if you query Internet Sales Amount along with customers across states and countries, your MDX query will be:

     SELECT [Measures].[Internet Sales Amount] on COLUMNS,     [Customer].[Country].[Country].MEMBERS *     [Customer].[State-Province].[State-Province].MEMBERS     ON ROWS     FROM [Adventure Works] 

The results of the above MDX query will only have the states that are within a specific country instead of a regular cross-join of the members of both hierarchies. Alberta, which is a state in Canada, does not exist in Australia and hence you do not have a tuple containing Australia and Alberta in your result.

SCOPE Statement

If you want to perform certain operations within the scope of the entire cube, you will typically have the calculations defined within the MDX scripts. CREATE and DROP SUBCUBE statements (you learn later in this chapter) are statements that can be used to restrict the cube space within the session scope at query time. You cannot use the CREATE SUBCUBE statement within MDX scripts. However, Analysis Services provides another statement called SCOPE. The SCOPE statement restricts the cube space so that all MDX statements and expressions specified within the SCOPE statement are evaluated exactly once against the restricted cube space. Named Sets in the MDX script are not affected by the SCOPE statement. The syntax of the SCOPE statement is:

     SCOPE <SubeCubeExpression>      <MDX Statement>      <MDX Statement> ...     END SCOPE 

You can have one or more MDX statements within the SCOPE statement and you can have nested SCOPE statements. Nested SCOPE statements can often be simplified as a single SCOPE statement as long as all the MDX statements are within the innermost SCOPE statement. MDX statements expressed within SCOPE statements are really cell calculations, which you learn about in the next section. An example of SCOPE statement is:

     SCOPE       (          [Date].[Fiscal Year].&[2002],          [Date].[Fiscal].[Month].Members,          [Measure].[Sales Quota Amount]       ) ;             This = [Date].[Fiscal].CurrentMember.Parent / 3 ;     End Scope ; 

In the above example the SCOPE statement restricts the cube space for Year 2002 all the members of the Month under the Fiscal hierarchy of Date dimension and the measure Sales Quota Amount. The default members of the hierarchies not specified in the subcube expression become part of the subcube. The MDX expression specified within the SCOPE statement use "this" as the keyword. "This" refers to the current subcube. In the preceding example the MDX expression evaluated will take the Sales Quota Amount measure and iterate through all the members in Fiscal hierarchy of the Date dimension and replace existing measure values to one-third the value of the parent of the current member. This MDX expression is referred to as an assignment since the cells referenced within the subcube (using "This") are assigned values based on the MDX expression. In this specific example, the Fiscal hierarchy level month has Sales Quota Amount allocated based on the Quarter level. A nested SCOPE statement can rescope a hierarchy that was already scoped by an earlier SCOPE statement. For example if you have a SCOPE statement to create a subcube for Fiscal Year 2002 and a nested scope to create a subcube for Fiscal Year 2003 then the Fiscal Year hierarchy is re-scoped to subcube of 2003.

Cell Calculations and Assignments

Now you have learned about the cube space and were introduced to calculations affecting cell values. In this section you learn about cell calculations in depth. MDX provides several ways to specify calculations, such as calculated members, calculated measures, custom rollups (to be discussed in Chapter 8), and unary operators (also discussed in Chapter 8). Using these features to affect a group of cell values or even a single cell value is not easy. Analysis Services allows you to create or apply calculations to cell values, which can help you in scenarios such as budgeting. Analysis Services 2000 introduced the statement CREATE CELL CALCULATION, which, similar to calculated members and named sets, can be specified at a query, session, or cube scope. The syntax for CREATE CELL CALCULATION statement is:

     CREATE CELL CALCULATION <CubeName>.<formula name>     FOR <SetExpression> AS <MDX Expression>, <cell property list> CONDITION = <Logical     Expression> 

In the above syntax, the <formula name> is an identifier for the cell calculation statement. The Set expression resolves to a set of tuples for which the cell values will be changed based on the MDX expression. The cell property list is an optional set of properties for the cell such as DISABLED, DESCRIPTION, CALCULATION_PASS_NUMBER, and CALCULATION_PASS_DEPTH, which can be applied to the cells being evaluated separated by commas. These properties help in application of specific properties to certain cells so that the calculations are evaluated in the right order. Further, correct use of cell properties can help client tools identify these cells uniquely so that they can be represented appropriately to the end users.

An example of the CREATE CELL CALCULATION statement for the Adventure Works DW sample database can be applied to the task of making the Sales Quota Amount for the Fiscal Year 2005 set twice the value of the Actual Sales in the Fiscal Year 2004 set:

     CREATE CELL CALCULATION [Adventure Works].[SalesQuota2005]     FOR '([Date].[Fiscal Year].&[2005],         [Date].[Fiscal].[Month].Members,[Measures].[Sales Amount Quota]         )'     AS '(ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])*2 ' 

Here the set expression returns tuples for which the cell values need to be updated — in this case the months of Fiscal Year 2005. The MDX expression evaluates to the Sales Amount values of fiscal year 2004, which is then multiplied by 2. You can verify the results of the the cell calculation via the following MDX query:

     SELECT {[Measures].[Sales Amount Quota],[Measures].[Sales Amount]} on 0,     Descendants ({[Date].[Fiscal].[Fiscal Year].&[2004],     [Date].[Fiscal].[Fiscal Year].&[2005]},3,SELF) on 1     from [Adventure Works] 

You can see that the Sales Amount Quota for the months July and August 2004 (which are months in fiscal year 2005) are exactly twice the Sales Amount for the months July and August of 2003 (the corresponding months for the fiscal year 2004). The particular variant of Descendants function (there are others) used in the preceding MDX query is used to retrieve the months of the fiscal years 2004 and 2005. The second parameter indicates the level in the hierarchy from which members need to be retrieved and the last parameter indicates whether to retrieve members only from the current level or from other levels before or after the current level. For further details on the descendants function, please refer to Appendix A.

For the session scope and global scope you can follow the example mentioned above. For global scope you need to define the cell calculation statements within MDX scripts. If you want to use this within the query scope, you need to use the CREATE CELL CALCULATION statement with the WITH clause, like this:

     WITH CELL CALCULATION [Adventure Works].[SalesQuota2005]     FOR '([Date].[Fiscal Year].&[2005],          [Date].[Fiscal].[Month].Members,[Measures].[Sales Amount Quota]          )'     AS '(ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])*2 '     SELECT {[Measures].[Sales Amount Quota],[Measures].[Sales Amount]} on 0,     Descendants ({[Date].[Fiscal].[Fiscal Year].&[2004],     [Date].[Fiscal].[Fiscal Year].&[2005]},3,SELF) on 1     from [Adventure Works] 

The above example can be re-written with a condition clause as shown in the next code snippet, which will result in exactly the same behavior. In the following MDX statement the condition checks for the Current Member of the Fiscal Year hierarchy and only applies the calculation to the cells if the condition is satisfied. Even though both these cell calculation statements result in the same behavior, we recommend you use the first flavor because the condition gets evaluated for each and every cell. Analysis Services restricts the cube space in the above example due to the selection of Fiscal Year 2005, which would give better performance if the number of cells to be updated is large.

     CREATE CELL CALCULATION [Adventure Works].[SalesQuota2005]     FOR '([Date].[Fiscal].[Month].Members,[Measures].[Sales Amount Quota]          )'     AS '(ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])*2 ',     CONDITION = '[Date].[Fiscal Year].CurrentMember is [Date].[Fiscal Year].&[2005]’ 

Cell calculations not only help you evaluate specific cell values, but also avoid the addition of members in the cube space. The properties CALCULATION_PASS_NUMBER and CALCULATION_PASS_DEPTH provide the functionality to specify complex recursive calculations such as goal-seeking equations. The CALCULATION_PASS_NUMBER specifies the PASS number at which the calculation is to be performed.

Analysis Services 2005 supports the CREATE CELL CALCULATION syntax for backward compatibility reasons. Assignment statements are the recommended way for global cell calculations in Analysis Services 2005. This new syntax allows you to model complex business logic through the SCOPE statement, the CASE operator, and new MDX functions such as Root and Leaves. You must be familiar with the SCOPE statement along with assignments using the "This" keyword from the previous section. Each assignment statement in MDX Scripts results in a new PASS value. The cell calculation example with SCOPE is as follows:

     SCOPE ([Date].[Fiscal Year].&[2005],         [Date].[Fiscal].[Month].Members,         [Measures].[Sales Amount Quota]);     This = (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])*2;     END SCOPE; 

The preceding cell calculation is simple in the sense that it does not require special conditions. It is referred to as a simple assignment because the cell value for the current coordinate indicated by "This" is assigned a value, which is evaluated from the MDX expression on the right-hand side. If you have a complex expression with several conditions to apply the cell calculation, the simple assignment will not be sufficient. You can use the IF statement to check for conditions before applying the cell calculation. For example, if you want the Sales Amount Quota to be two times the previous year's Sales Amount just for the first quarter, your calculation using the IF statement will be:

     SCOPE     ([Date].[Fiscal Year].&[2004],         [Date].[Fiscal].[Month].Members,         [Measures].[Sales Amount Quota]);     This = (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])* 1.3;     IF ([Date].[Fiscal].Currentmember.Parent is [Date].[Fiscal].[Fiscal     Quarter].&[2004]&[1])     THEN This = (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])* 2.0     END IF;     END SCOPE; 

The syntax of the IF statement is:

     IF <conditional_expression> THEN <assignment_expression> END IF; 

As you can you see from the statement it is pretty straightforward. In fact it is quite easy to debug statements in MDX scripts with the help of the MDX debugger within the Cube Designer (you will learn debugging MDX scripts in Chapter 9). The assignment_expression is a valid MDX assignment statement. Examples of assignment_expression include MDX expressions assigned to a subcube or calculation properties applied to a subcube. END IF indicates the end of the IF statement. In the preceding example, you first assign values to all the cells corresponding to the subcube to be 1.3 times the value of the Sales amount in the previous year. Later you have the conditional IF statement to update the cell values corresponding to the first quarter to be two times the Sales Amount.

You can get into more complex expressions which may require multiple IF statements which can lead to updating cells multiple times. The cube space is large, and applying cell calculations on a large cube space can lead to performance degradation. Hence, Analysis Services provides the CASE expression to perform assignments.

The syntax for the CASE expression is:

     Statement =     CASE <value_expression>     WHEN <value_expression> THEN <statement>     ELSE <statement>     END; 

Here an MDX statement is assigned one of the values being returned by the CASE expression. Assume for the Fiscal Year 2004, you need to specify the Sales Amount Quota based on some condition, such as the Sales Quota for the first quarter must be 1.3 times the previous year's sales amount, for the second quarter the quota is 2 times the previous year's sales amount, and for the third and fourth quarters the quota is 1.75 times the previous year's sales amount. You can specify this condition easily using the CASE statement as follows:

     SCOPE     ([Date].[Fiscal Year].&[2004],         [Date].[Fiscal].[Month].Members,         [Measures].[Sales Amount Quota]);     This =     CASE WHEN ([Date].[Fiscal].Currentmember.Parent is [Date].[Fiscal].[Fiscal     Quarter].&[2004]&[1])     THEN     (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])* 1.3     WHEN ([Date].[Fiscal].Currentmember.Parent is [Date].[Fiscal].[Fiscal     Quarter].&[2004]&[2])     THEN     (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])* 2.0     ELSE     (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],     1,[Date].[Fiscal].CurrentMember),[Measures].[Sales Amount])* 1.75     END;     END SCOPE; 

The preceding CASE expression applies the correct calculations based on the Quarter the month belongs to. The CASE expression is similar to the switch case statement in C/C++. Based on the conditional expression provided after the WHEN, the statement is assigned to the expression on the left hand side ("This" in the above code sample). You can have multiple WHEN-THEN's within the CASE expression as shown in the preceding example.

In the all previous examples you have seen SCOPE-END SCOPE being used. Use SCOPE when you have multiple calculations that need to be applied within the SCOPE. However, if it is a single MDX expression, you can write the cell calculation by direct assignment to the subcube as shown below.

     ([Date].[Fiscal Year].&[2005], [Date].[Fiscal].[Month].Members,           [Measures].[Sales Amount Quota]) =                     (ParallelPeriod ( [Date].[Fiscal].[Fiscal Year],1,     [Date].[Fiscal].CurrentMember) ,[Measures].[Sales Amount])*2; 

You have so far learned about the cell calculations using assignments in Analysis Services 2005, which is recommended over the CREATE CELL CALCULATION syntax. You learned about the IF statement and the CASE expression to apply cell calculations based on business conditions. There are two MDX functions in Analysis Services 2005 that help you write cell calculations with ease. These functions are Root and Leaves, which are great if you want to apply cell calculation to the leaf-level members or the root of a hierarchy. These functions appropriately position the coordinate so that the cell calculations can be applied to that coordinate. Following are some examples that use the Root and Leaves MDX functions:

     CREATE MEMBER CurrentCube.[Measures].[Ratio to All Products]     As [Measures].[Sales Amount]        /        (          Root ( [Product] ),          [Measures].[Sales Amount]        ),    Format_String = "Percent",    Non_Empty_Behavior = [Sales Amount] ;    SCOPE (Leaves ([Date]),[Measures].[Sales Amount Quota]); this =         this*1.2;    END SCOPE; 

In the first example, you can see a calculated measure that calculates contribution of Sales of a product as a portion of total product sales. This is accomplished through use of the Root (Product), which will provide the sales information for all the products. Root (Product) is often used to calculate ratios of a measure for a single member against all the members in the dimension. In the second example, the Sales Amount Quota is being applied to Leaf members of the Date dimension so that the Sales Amount Quota can be increased by 20%. Leaves MDX function would help in budgeting and financial calculations where you want the calculations applied only to the leaf-level members and then rolled up to the members at other levels. The Root and Leaves MDX functions are new in Analysis Services 2005. Both take a dimension as the argument and return the leaf-level members of the dimension that exists with the granularity attribute of the dimension.

Recursion

Recursive calculations can be quite common in MDX. Recursive calculation occurs when a calculated member references itself for calculations. For example if you want to calculate the cumulative sales over time then you can apply an MDX expression which calculates the Sales of current time member and the cumulative sales for previous time member. This leads to recursion since the cumulative sales of previous time member needs to be evaluated with the same MDX expression for the previous time member. In Analysis Services 2000 you can end up with a calculation that can lead to infinite recursion. You need to have special conditional checks to avoid infinite recursion errors. In Analysis Services 2005 infinite recursions due to single expression are avoided since a new PASS value. Consider the following MDX statements in your MDX script.

     SCOPE ([Date].[Fiscal Year].&[2004])     [Sales Amount Quota] = [Sales Amount Quota] * 1.2;     END SCOPE 

In the above statement the evaluation of [Sales Amount Quota] leads to infinite recursion. In such a situation where single expression leads to infinite recursion, Analysis Services 2005 detects it and assigns a value from the previous PASS value.

Freeze Statement

Freeze Statement is used in circumstances where you might want to change the cell value that was used in an MDX expression to determine results for another cell value without changing the cell value from an earlier calculation. The syntax for Freeze Statement is:

     Freeze <subcube> 

This Freeze statement is only used within MDX Scripts. It is easier to understand the Freeze statement with an example. Assume the following MDX statements below where A, B, and C are MDX expressions.

     A=B;     B=C; 

Due to recursion the final value for A will be equal to the value of C. However, if you want to ensure that the value of A is pinned to the value assigned by MDX expression B, you would introduce a Freeze statement between the two assignments as shown here.

     A=B;     Freeze (A);     B=C; 

You can use the Freeze statement when you perform budget allocations. An example of Freeze statement is used in the sample Adventure Works DW database. Budget is first allocated the current year's quarters based on the previous year's quarter values and Freeze statement is applied to the quarters so that their values are not changed. Then the months are allocated weights based on the previous years followed by the budget for the months which allocated as a ratio of the quarter value. If the Freeze statement is not applied to the budget value for quarters their values would be overwritten when the ratios for the months are calculated.