Mdx (Analysis Services (sql server)) mdx

Creating a Cell Within the Context of a Cube

For certain applications, you may want to return data for a single cell within a cube. For example, executives might have a decision support application written in Microsoft® Excel that uses data from a multidimensional data store. Suppose that when the application starts each day, the executives want to view, at the top of the application's main window, the quarter-to-date worldwide sales for the current year across all products and customers.

The solution is to create a dataset for which all dimensions are slicer dimensions. The Multidimensional Expressions (MDX) statement for doing this takes the following form:

SELECT FROM cube_name WHERE slicer_specification

This results in a dataset with one cell. Because no axis dimensions are specified, the slicer specification focuses on the desired point in the entire cube.

In this case, where there are no axes and hence only one cell, the following conditions apply:

• 
  The IMDDataset::GetAxisInfo method returns 0 for *pcAxes and a null pointer in *prgAxisInfo.
  
• 
  The axis rowsets for all axes will be empty, except for the axis MDAXIS_SLICERS. The axis for the slicer dimension will contain information on the slicer conditions that created the single cell.
  
• 
  The single cell can be addressed by using the cell ordinal 0.
  

Working with the RollupChildren Function

The use of the RollupChildren function in Multidimensional Expressions (MDX) statements is simple to explain, but the impact of this function on MDX queries can be wide-ranging.

The RollupChildren function rolls up the children of a member, applying a different unary operator to each child, and returns the value of this rollup as a number. The unary operator used can be supplied by a member property associated with the child member, or it can be a string expression provided directly to the function.

The impact of the RollupChildren function occurs in MDX queries designed to perform selective analysis on existing cube data. For example, the following table contains a list of child members for the Net Sales parent member, with their unary operators (represented by the UNARY_OPERATOR member property) shown in parentheses.

Parent member	Child member
Net Sales	Domestic Sales (+) Domestic Returns (-) Foreign Sales (+) Foreign Returns (-)

The Net Sales parent member currently provides a total of net sales minus the gross domestic and foreign sales values, with the domestic and foreign returns subtracted as part of the rollup.

Now, if you want to provide a quick and easy forecast of domestic and foreign gross sales plus 10%, ignoring the domestic and foreign returns, there are two ways to perform this action using the RollupChildren function.

Custom Member Properties

If this is to be a commonly performed operation, one method is to create a member property that stores the operator to be used for each child for a given function. For example, a member property called SALES_OPERATOR is created, and the following unary operators are assigned to it, as shown in the following table.

Parent member	Child member
Net Sales	Domestic Sales (+) Domestic Returns (~) Foreign Sales (+) Foreign Returns (~)

With this new member property, the following MDX statement performs the gross sales estimate operation quickly and efficiently:

RollupChildren([Net Sales], [Net Sales].CurrentMember.Properties("SALES_OPERATOR")) * 1.1

When the function is called, the value of each child is applied to a total using the operator stored in the member property. The following table displays valid unary operators and describes the expected result.

Operator	Result
+	total = total + current child
-	total = total - current child
*	total = total * current child
/	total = total / current child
~	Child is not used in the rollup. Its value is ignored.

The tilde (~) unary operator indicates that this member is to be ignored when generating rollups totals. The members for domestic and foreign returns are ignored and the rollup total returned by the RollupChildren function is multiplied by 1.1.

IIf Function

However, if the example operation is not commonplace or if it applies only to one MDX query, then the IIf function can be used with the RollupChildren function to provide the same result. The following MDX query provides the same result as the earlier MDX example, but does so without resorting to the use of a custom member property:

RollupChildren([Net Sales],

IIf([Net Sales].CurrentMember.Properties("UNARY_OPERATOR") = "-", "~",

[Net Sales].CurrentMember.Properties("UNARY_OPERATOR))) * 1.1

The MDX statement checks the unary operator of the child member; if it is used for subtraction (as with the domestic and foreign returns members), the tilde (~) unary operator is substituted by the IIf function. Otherwise, the unary operator of the child member is used. Finally, the returned rollup total is then multiplied by 1.1 to provide the domestic and foreign gross sales forecast value.