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 (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 by selecting the XLCubed > Custom Calculations ribbon or menu option.
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 combining cube data, arithmetic operators, numbers and functions.

You create a named set from the Custom Calculation tab:

Click Insert a new calculated set

In this example I have created a named set, RacksAndStands, which is a grouping of Bike Racks and Bike Stands.

The expression needs to be inside braces as below { } and separated by commas.

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

The next step is to include the named set in the report.

You must be editing the hierarchy/dimension that the named set is linked to, in this example Product Model Categories.

Click on the Advanced tab and select Member Set

You should see your named set appearing 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 slicer 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