MDX Calculations

Not all OLAP environments are as rich as we, the analysts would like. These examples show how XLCubed allows the power of OLAP to be simply used to improve our analyses.

Creating Calculated Members

To create a custom calculated member, open the Manage Calculations dialog from the XLCubed > Custom Calculations ribbon option.

Select the Insert a new calculated member toolbar button (). This will create a new blank calculation from which you can fill in the following fields:

• Name: The name for the new calculated member.
• Parent hierarchy: The hierarchy the calculated member will belong to.
• Parent member: The unique name of the parent member for the new calculation. If no member is specified, the calculated member will be created at the top level of the hierarchy.
• Expression: The MDX expression for the calculated member. You can use the treeview on the left to drag and drop members into the expression.

All calculated members in the workbook appear on the left. Any calculation can be edited by selecting it in this control and then overwriting the fields on the right.

To delete a calculation, select the desired calculation and click the Delete the selected calculation toolbar button.

Example (based on the Bicycle Sales cube)

We will create new Gross Profit and Gross Profit Margin measures.

Start by creating a connection to the BicycleSales Demo Cube. This cube only has one measure, Value, but several different 'views' of the data, provided by the KeyFigures hierarchy.

To create the calculated measures:

1. Open the Manage Calculations dialog (XLCubed > Custom Calculations).
2. To create the Gross Profit member, click the Insert a new calculated member toolbar button
3. A new 'untitled' member will appear. Fill in the following details for the new member:

Name	Gross Profit
Parent hierarchy	KeyFigures
Parent member	[KeyFigures].[All]
Expression	[KeyFigures].[All].&[Revenue] - [KeyFigures].[All].&[Cost of Sales] - [KeyFigures].[All].&[Production Costs]


The Gross Profit member




4. Create a second calculated member for the Gross Profit Margin member, and fill in the following details. To enter the format string, Show advanced controls must be clicked:

Name	Gross Profit Margin
Parent hierarchy	KeyFigures
Parent member	[KeyFigures].[All]
Expression	[KeyFigures].[All].[Gross Profit] / [KeyFigures].[All].&[Revenue]
Format string	0%


The Gross Profit Margin member




5. We now have members in the KeyFigures hierarchy, but if we want to use them as normal measures, we need to create two more members in the Measures hierarchy:

Name	Gross Profit
Parent hierarchy	Measures
Parent member	Leave empty
Expression	([KeyFigures].[All].[Gross Profit],[Measures].[Value])




Name	Gross Profit Margin
Parent hierarchy	Measures
Parent member	Leave empty
Expression	([KeyFigures].[All].[Gross Profit Margin],[Measures].[Value])
Format string	0%

6. Click on OK to create the calculated members.

We can now see the new measures in context. Let's find badly performing product groups for 2004.

1. Click on the XLCubed > Grid ribbon item (or the XLCubed > Design Grid menu item in Excel 2003 and below). For more information about using this dialog, see Report Designer.
2. Move Measures to columns, Product to rows and Time to headers.
3. Click on the Time hierarchy, then select 2004, the year we want to analyse.
4. Click on the Measures hierarchy to select the Gross Profit and Gross Profit Margin measures.
5. Click OK to insert the Grid.
6. When we drill down on the Product hierarchy, we can see that Road S8000 is the failing product.

We could extend this report to use In-Cell Charts. See Visual Grids for Performance Analysis to continue.

Creating Named Sets

A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. Named sets can be created at cube level or within an application such as XLCubed. They can be created by combining cube data, arithmetic operators, numbers and functions.

Within the Manage Calculations dialog, click Insert a new calculated set ( ).

A new calculation will appear with two fields on the right:

• Name
• Expression: The expression needs to be inside braces as below { } and separated by commas (see example below).

The screenshot below shows a created a named set, RacksAndStands, which is a grouping of Bike Racks and Bike Stands. The expression is:

{[Product].[Product Model Categories].[Subcategory].&[26],[Product].[Product Model Categories].[Subcategory].&[27]}

Using Named Sets

To use a named set within a report, go to the Member Selector of the hierarchy/dimension that the named set is linked to, in this example Product Model Categories. Under the Advanced tab, select the Member Set icon .

The named set will appear as below, select it and click OK.

You can then select to include/exclude the named set in your report.

In this example the members of the named set will not appear in the report.

Dynamic sets

New in Version 8.1 you can flag a set as being dynamic, which means they will be calculated when the query is run. This is useful when the members in the set change based on the slice being analysed, e.g. Top 10 products, with the year changing in a slicer.

More information can be found here: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

Show with Captions

New in v7.2 there is an option within Custom Calculations to switch from showing unique names to captions

Custom Scripts

Custom scripts can run any piece of MDX script you need to alter your cube. There is a restriction in Analysis services that each command can only contain one MDX statement.

The most common use of these is to create Scope assignments, for example:

Importing and Exporting calculations

From XLCubed v8.0, you can import and export XML files containing the calculations in the current workbook.

See Also

• Mdx: - Allows creating simple calculations using Excel references.
• Calculations Overview
• Grid Reporting Overview
• Error when editing calculated members based on calculated sets

• Microsoft listing of MDX functions
• Listing of VBA functions supported in MDX