XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

MDX Calculations

Revision as of 17:21, 8 November 2011 by NMaudgil (talk | contribs)

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 (NewCalculatedMember.png).
  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]
Custom calculations dialog with the Gross Profit member
The Gross Profit member
  1. 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%
Custom calculations dialog with the Gross Profit Margin member
The Gross Profit Margin member
  1. 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%
  1. 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.


NS8.png

The report above shows Reseller Sales Amount across All Geographies for fiscal years 2003 and 2004.

We would like to create a named set, Europe which consists of France, Germany and United Kingdom.

Create a calculated set, Europe, as below.

NS9.png

Double-click on Geography hierarchy and Edit Hierarchy:

NS10.png


SetIcon.png

Click on Advanced Tab and then Member Set


The following window will be displayed and you will see the defined named set.

NS11.png


See Also