Excel 2013

MDX Calculation GUI

Excel 2013 is the first version to support a GUI for managing MDX calculated measures and members in a PivotTable. After clicking on a PivotTable, these features can be found under the PivotTable Tools... Analyze tab and under the OLAP Tools menu:

Excel2013MDXMenu.png

The MDX Calculated Measure dialog looks like:

Excel2013CalculatedMeasure.png

The MDX Calculated Member dialog lets you add a calculated dimension member:

Excel2013CalculatedMember.png

Finally, the Manage Calculations dialog looks like:

Excel2013ManageCalculations.png

The Excel Blog also covered this feature here.

Why Continue Using OLAP PivotTable Extensions?

As you can see, it greatly improves upon the calculation management UI from the OLAP PivotTable Extensions add-in by adding a very nice field list and function list. So why would you continue using OLAP PivotTable Extensions? Starting with release 0.8.0, OLAP PivotTable Extensions adds support for Excel 2013. The add-in also offers these other features:

Excel Data Model

Excel 2013 natively integrates the xVelocity engine behind PowerPivot and allows loading multiple tables into what's known as the Excel Data Model. This is a built-in in-memory Analysis Services database which is deeply integrated into Excel 2013. Teo Lachev wrote up a good overview.

OLAP PivotTable Extensions v0.8.0 adds support for the Excel Data Model. (Disclaimer: The ability to connect to the Excel Data Model and/or PowerPivot from OLAP PivotTable Extensions is using unsupported APIs and as such the behaviour may change or stop working without notice in future releases. This functionality is provided on an "as-is" basis.) All OLAP PivotTable Extensions features are supported except:
  • MDX calculated measures and members (as the Excel Data Model doesn't support this). Use PowerPivot to create DAX measures instead of using OLAP PivotTable Extensions to create MDX members.
  • Clear PivotTable Cache (since the data is local and this feature doesn't make sense for the Excel Data Model)
  • Change PivotTable Defaults (since "refresh on open" will cause Excel to reconnect to the SQL sources behind the Excel Data Model, potentially causing long delays upon workbook open. If you want to enable this option manually, the native Excel PivotTable Options dialog allows you to set this)
  • Show Property as Caption (currently the Excel Data Model doesn't provide support for member properties)

Click-to-Run Support

Office 365 as part of the Office 2013 release supports a new install approach called Click-to-Run (more information here). OLAP PivotTable Extensions works in this scenario as soon as streaming installation is complete.

Last edited Nov 26, 2013 at 4:17 PM by furmangg, version 9

Comments

No comments yet.