Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • 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. (NewCalculatedMember.png).

    4. A new 'untitled' member will appear. Fill in the following details for the new member:
    5. 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
    6. 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:
    7. 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
    8. 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:
    9. 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%
    10. 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:

    NamedSet6.png


    NamedSet5.png
    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]}

    NamedSet1.png

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

    NamedSet2.png

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

    NamedSet5.png
    Click on the Advanced tab and select Member Set


    You should see your named set appearing as below, select it and click OK.


    NamedSet3.png


    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.


    NamedSet4.png

    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

    Calc1.png


    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:

    ScopeScript.png

    Importing and Exporting calculations

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

    See Also