Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.
Excel 2010 does not allow end-users to add their own private MDX calculations.
A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:
1) Check Excel Version (32-bit/64-Bit)
Open Excel > File > Help> About Microsoft Excel
2) Download OLAP Pivot Table Extensions and Install it
Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.
In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.
Close Excel and Install add-in
3) Add simple measure
Connect to Cube.
Let’s add a simple measure that calculates the difference between two measures.
So I created a simple Pivot Table that looks like:
Select the Pivot Table > Right Click > OLAP Pivot Table Extensions
On the next screen, please enter the name Difference. and the simple formula:
(please replace measure1 & measure2 with the measure names from your cube)
And click on Add to Pivot Table
1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.
2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.
That’s about it for this post! Your feedback is most welcome!