OLAP PivotTable Extensions

Updated Release: Release 0.6.0 (Oct 15, 2008)

furmangg — Wed, 15 Oct 2008 18:48:48 GMT

This release contains the new cube Search feature which searches the field list and dimension data.

There is no need to uninstall previous versions of OLAP PivotTable Extensions as this install will upgrade them in place.

Updated Wiki: Search

furmangg — Wed, 15 Oct 2008 05:24:57 GMT

Search

Finding what you’re looking for in a cube can sometimes be challenging, but the Search feature of OLAP PivotTable Extensions can help. It lets you text search the items in the Field List and their descriptions. It also lets you text search the dimension members in your cube.

There are 4 primary use cases for the Search feature:

USE CASE #1: Finding a field in the field list

Let’s say that you have been directed to cut costs company-wide. You want to research anything related to costs, so you pull up a PivotTable and begin to scroll through the enormous field list trying to find anything related to cost.

Then you install OLAP PivotTable Extensions and can text search the field list for the term “cost.” You quickly see that there are several measures showing various costs, and there is also a Standard Cost attribute of a Product. You check the ones you want and add them to the PivotTable.

USE CASE #2: Finding where a member resides in the tree

Let’s say that you want to slice a PivotTable to see the data for a particular employee named Kim Ralls. You have no idea who manages her so do not know where to look in the tree. (But you do know she is not very senior, so she is probably quite deep in the Employees hierarchy. In fact, she’s 4 levels deep in Adventure Works.) So you add the Employees hierarchy to the filter area, then you right-click on that hierarchy in the PivotTable and choose “Search…”

The OLAP PivotTable Extensions window pops up, it preselects that you just want to look in the Employees hierarchy, and then you search for “ralls”:

Checking Kim T. Ralls and clicking “Add to PivotTable” will set the filter to that employee.

You can also right-click on dimension hierarchies on rows or columns and search them in a similar manner. If the dimension member you find and choose to add to the PivotTable is part of a hierarchy on rows or columns, OLAP PivotTable Extensions tries to make sure that hierarchy is expanded so that the member is visible. However, in some situations, other filters or lack of fact data may cause that member to be invisible even after you click the “Add to PivotTable” button.

USE CASE #3: Finding which dimensions contain information about a term

Let’s say that you are the sales manager for the Northwest part of the Unites States. You are obviously interested in the Northwest, so you can search the cube’s dimensions to see what references to the northwest there are:

You see that the Sales Territory dimension and the Organization dimension both contain a member related to the Northwest. There is also a reseller with an address containing that search term.

USE CASE #4: Searching descriptions of fields

Unfortunately, Excel 2007 does not display the description property as a tooltip in the field list like it should. The Search feature of OLAP PivotTable Extensions lets you search the description property of every field in the field list:

The “description” property (and the “Folder” property) is only searched when you uncheck “Exact match.”

Search Speed

Cubes contain a vast amount of metadata, and dimensions can be very large, so it is good to know how expensive each type of search will be...

FASTEST: Searching the Field List is the fastest search operation as it is simply searching metadata. If you uncheck “Exact match” it also searches the Description property and the Folder property, but this does not impact performance significantly.
FAST: Searching a particular hierarchy by choosing it out of the “Look in” dropdown, and checking “Exact match” will be very fast as it uses the hash indexes for that dimension.
QUICK: Searching dimension data with “Exact match” checked and “Search member properties” unchecked is fairly quick as it uses the hash indexes for the dimensions.
SLOW: Searching dimension data with “Exact match” unchecked is slow as it has to run one query per hierarchy and scan every dimension member. Dimensions are scanned in order of smallest to largest (since smaller dimensions are often used more frequently), so you may see the progress bar moving slower and slower the further it gets.
SLOWEST: Searching dimension data with “Search member properties” checked is very slow as it has to run one query per hierarchy and scan every dimension member and every member property in addition to the member’s caption. Dimensions are scanned in order of the smallest to the largest.

Warning: If you have ROLAP dimensions, this searching dimension data could be very slow, so it is recommended that you search individual hierarchies by picking them out of the “Look in” dropdown. Talk to your Analysis Services administrator to determine if you have any ROLAP dimensions.

Updated Wiki: Search

furmangg — Wed, 15 Oct 2008 05:19:48 GMT

Search

Finding what you’re looking for in a cube can sometimes be challenging, but the Search feature of OLAP PivotTable Extensions can help. It lets you text search the items in the Field List and their descriptions. It also lets you text search the dimension members in your cube.
There are 4 primary use cases for the Search feature:

USE CASE #1: Finding a field in the field list

Let’s say that you have been directed to cut costs company-wide. You want to research anything related to costs, so you pull up a PivotTable and begin to scroll through the enormous field list trying to find anything related to cost.
Then you install OLAP PivotTable Extensions and can text search the field list for the term “cost.” You quickly see that there are several measures showing various costs, and there is also a Standard Cost attribute of a Product. You check the ones you want and add them to the PivotTable.

USE CASE #2: Finding where a member resides in the tree

USE CASE #3: Finding which dimensions contain information about a term

USE CASE #4: Searching descriptions of fields

Search Speed

Cubes contain a vast amount of metadata, and dimensions can be very large, so it is good to know how expensive each type of search will be...

FASTEST: Searching the Field List is the fastest search operation as it is simply searching metadata. If you uncheck “Exact match” it also searches the Description property and the Folder property, but this does not impact performance significantly.
FAST: Searching a particular hierarchy by choosing it out of the “Look in” dropdown, and checking “Exact match” will be very fast as it uses the hash indexes for that dimension.
QUICK: Searching dimension data with “Exact match” checked and “Search member properties” unchecked is fairly quick as it uses the hash indexes for the dimensions.
SLOW: Searching dimension data with “Exact match” unchecked is slow as it has to run one query per hierarchy and scan every dimension member. Dimensions are scanned in order of smallest to largest (since smaller dimensions are often used more frequently), so you may see the progress bar moving slower and slower the further it gets.
SLOWEST: Searching dimension data with “Search member properties” checked is very slow as it has to run one query per hierarchy and scan every dimension member and every member property in addition to the member’s caption. Dimensions are scanned in order of the smallest to the largest.

Updated Wiki: Search

furmangg — Wed, 15 Oct 2008 05:17:30 GMT

Search

Finding what you’re looking for in a cube can sometimes be challenging, but the Search feature of OLAP PivotTable Extensions can help. It lets you text search the items in the Field List and their descriptions. It also lets you text search the dimension members in your cube.
There are 4 primary use cases for the Search feature:

USE CASE #1: Finding a field in the field list

Let’s say that you have been directed to cut costs company-wide. You want to research anything related to costs, so you pull up a PivotTable and begin to scroll through the enormous field list trying to find anything related to cost.
Then you install OLAP PivotTable Extensions and can text search the field list for the term “cost.” You quickly see that there are several measures showing various costs, and there is also a Standard Cost attribute of a Product. You check the ones you want and add them to the PivotTable.

<SearchTab.png>

USE CASE #2: Finding where a member resides in the tree

Let’s say that you want to slice a PivotTable to see the data for a particular employee named Kim Ralls. You have no idea who manages her so do not know where to look in the tree. (But you do know she is not very senior, so she is probably quite deep in the Employees hierarchy. In fact, she’s 4 levels deep in Adventure Works.) So you add the Employees hierarchy to the filter area, then you right-click on that hierarchy in the PivotTable and choose “Search…”

< SearchEmployeeFilterMenu.png>

The OLAP PivotTable Extensions window pops up, it preselects that you just want to look in the Employees hierarchy, and then you search for “ralls”:

< SearchEmployeeKimRalls.png>

Checking Kim T. Ralls and clicking “Add to PivotTable” will set the filter to that employee.

You can also right-click on dimension hierarchies on rows or columns and search them in a similar manner. If the dimension member you find and choose to add to the PivotTable is part of a hierarchy on rows or columns, OLAP PivotTable Extensions tries to make sure that hierarchy is expanded so that the member is visible. However, in some situations, other filters or lack of fact data may cause that member to be invisible even after you click the “Add to PivotTable” button.

USE CASE #3: Finding which dimensions contain information about a term

Let’s say that you are the sales manager for the Northwest part of the Unites States. You are obviously interested in the Northwest, so you can search the cube’s dimensions to see what references to the northwest there are:

< NorthwestSearch.png>

You see that the Sales Territory dimension and the Organization dimension both contain a member related to the Northwest. There is also a reseller with an address containing that search term.

USE CASE #4: Searching descriptions of fields

Unfortunately, Excel 2007 does not display the description property as a tooltip in the field list like it should. The Search feature of OLAP PivotTable Extensions lets you search the description property of every field in the field list:

< SearchDescriptions.png>

The “description” property (and the “Folder” property) is only searched when you uncheck “Exact match.”

Search Speed

Cubes contain a vast amount of metadata, and dimensions can be very large, so it is good to know how expensive each type of search will be...

FASTEST: Searching the Field List is the fastest search operation as it is simply searching metadata. If you uncheck “Exact match” it also searches the Description property and the Folder property, but this does not impact performance significantly.
FAST: Searching a particular hierarchy by choosing it out of the “Look in” dropdown, and checking “Exact match” will be very fast as it uses the hash indexes for that dimension.
QUICK: Searching dimension data with “Exact match” checked and “Search member properties” unchecked is fairly quick as it uses the hash indexes for the dimensions.
SLOW: Searching dimension data with “Exact match” unchecked is slow as it has to run one query per hierarchy and scan every dimension member. Dimensions are scanned in order of smallest to largest (since smaller dimensions are often used more frequently), so you may see the progress bar moving slower and slower the further it gets.
SLOWEST: Searching dimension data with “Search member properties” checked is very slow as it has to run one query per hierarchy and scan every dimension member and every member property in addition to the member’s caption. Dimensions are scanned in order of the smallest to the largest.

Created Release: Release 0.6.0 (Oct 15, 2008)

furmangg — Wed, 15 Oct 2008 05:12:15 GMT

Released: Release 0.6.0 (Oct 15, 2008)

Wed, 15 Oct 2008 05:12:15 GMT

Updated Wiki: Home

furmangg — Wed, 15 Oct 2008 05:00:55 GMT

OLAP PivotTable Extensions is an Excel 2007 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel 2007 API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality. It can be launched from the following menu option in the right-click menu for PivotTables:

Private Calculated Members

Any calculated members which are part of the Analysis Service cube on the server can be added to PivotTables. But there is no built-in UI to define your own MDX calculations. Adding extra Excel calculations in the cells surrounding the PivotTable has some limitations as they are not part of the PivotTable and can be wiped out if the dimensions of the PivotTable change, and plain Excel calculations must only operate on data visible in the PivotTable.

OLAP PivotTable Extensions let you define your own calculated measures which are private to that particular PivotTable:

Those calculations appear in the PivotTable just like any other calculations:

They appear in the Field List pane at the very top under the Values grouping:

For help with advanced calculation properties for these private PivotTable calculations, or for help with MDX expressions, refer to our Calculations Help page.

Best Practice: In order to ensure a single version of the truth, it is a best practice to define important calculations as part of the cube source code. But some calculations like simple ratios or differences may clutter the cube and may be more appropriate if defined in the PivotTable itself. In addition, certain ad hoc research or prototyping may be more appropriate to be done as calculations private to a PivotTable until they are finalized and are ready to be added to the cube source code.

Calculations Library

Since all calculations you define are private to that one PivotTable, OLAP PivotTable Extensions automatically creates a Calculations Library for you which contains all the calculations you create. This allows you to pick any previous calculation you've used from a dropdown and add it to the current PivotTable:

You can also perform Calculation Library maintenance by importing, exporting, and deleting calculations:

View PivotTable MDX

If a PivotTable is performing poorly or returning incorrect numbers, it may be necessary for the Analysis Services administrator to troubleshoot the MDX query which the PivotTable is using. The MDX tab of the OLAP PivotTable Extensions dialog shows you this MDX.

The MDX is exactly what is sent to the server with one exception. Any private calculations you've created for your PivotTable are defined as session calculated members. The MDX query exposed on the MDX tab displays the formulas for these calculations as query calculated members in the WITH clause of the MDX query. This allows an administrator to copy and paste the MDX query and troubleshoot it more easily in Management Studio or MDX Studio.

Changing PivotTable Defaults

Certain settings must be manually changed after creating a new PivotTable. For instance, if your dimensions have calculated members you wish to see in your PivotTable, you must manually right click on the PivotTable, choose PivotTable Options, flip to the Display tab, then check "Show calculated members from OLAP server". The Defaults tab of OLAP PivotTable Extensions lets you default this setting to be on in any new PivotTables you create in the future:

Searching

Finding what you’re looking for in a cube can sometimes be challenging, but the Search feature of OLAP PivotTable Extensions can help. It lets you text search the items in the Field List and their descriptions. It also lets you text search the dimension members in your cube.

For more detailed information about the Search feature, see the dedicated Search page.

Distributing PivotTables

OLAP PivotTable Extensions need only be installed on computers which need to create new private PivotTable calculations. After those calculations have been defined, the PivotTable can be distributed to others without problem. If published to Excel Services, the private calculations you define will still be active in the PivotTable. And if you distribute the Excel 2007 workbook to other Excel 2007 users, they will be able to continue designing and manipulating that PivotTable without problem.

The Calculation Library does not need to be distributed unless other users wish to start brand new PivotTables and reuse certain calculations you have created using OLAP PivotTable Extensions.

Installation Requirements

Excel 2007 is required.
Access to a cube on an Analysis Services server is required.
.NET Framework version 2.0 is required.
Visual Studio 2005 Tools for Office Second Edition Runtime is required.

Feedback

To report bugs or suggestions, please post an item to the Issue Tracker tab.

To ask questions about MDX formulas such as the formulas used for private PivotTable calculations, see our Calculations Help page, or post a question to the Microsoft Analysis Services Forum, or contact your Analysis Services administrator.

To discuss OLAP PivotTable Extensions, please start a thread on the Discussions tab.

Acknowledgments and Additional Information

For links to other resources, view our Acknowledgments and Additional Information page.

Source code checked in, #23587

furmangg — Wed, 15 Oct 2008 04:55:29 GMT

adding the Search feature (version 0.6.0.0)

Updated Wiki: Acknowledgments and Additional Information

furmangg — Tue, 07 Oct 2008 22:02:01 GMT

Acknowledgments and Additional Information

Special thanks go to:

Marco Russo and his post on viewing the MDX behind a PivotTable
The fantastic Excel team blog. Particularly, this post described how to add private calculated members to a PivotTable was helpful.
The PivotPower add-in which contains some useful PivotTable extensions (which are mostly focused on non-OLAP PivotTables, though some of its features are useful with OLAP PivotTables).

Additional Resources:

UPDATED RELEASE: Release 0.5.2 (Jun 27, 2008)

Sun, 29 Jun 2008 05:56:44 GMT

No updated functionality from Release 0.5.0 or Release 0.5.1. This release only fixes the installer. The installer now succeeds on machines where Excel was installed without certain options.

Please uninstall previous versions before installing this version.

To install, unzip then run setup.exe.

New Post: add-in is not working in excel 2007

furmangg — Fri, 27 Jun 2008 22:48:12 GMT

Release 0.5.2 should fix the problem. I think the problem was that when you installed Excel, you didn't install with the option checked to install .NET Programmability Support for Excel 2007. I changed the installer to install the necessary helper DLLs and not assume you've already got them.

Please let me know how it works for you.

UPDATED RELEASE: Release 0.5.2 (Jun 27, 2008)

Fri, 27 Jun 2008 22:47:34 GMT

RELEASED: Release 0.5.2 (Jun 27, 2008)

Fri, 27 Jun 2008 22:47:34 GMT

Source code checked in, #18862

furmangg — Fri, 27 Jun 2008 22:47:06 GMT

installer changes for release 0.5.2 no functionality changes, just installer fixes

New Post: Can I execute my MDX query with this extension?

micaman — Tue, 24 Jun 2008 01:30:56 GMT

I have my reports in Excel, using a SSAS project as source. This Excel file updates automatically everytime i open it. I didn't want to copy/paste everytime i update my cubes...

I guess the only way a can execute my own MDX querys is in SSRS... Too bad.

Thanks anyway ;)

New Post: Can I execute my MDX query with this extension?

furmangg — Mon, 23 Jun 2008 16:08:10 GMT

If you already have an MDX query, then you may want to run it in Management Studio (part of the SQL Server installation) then copy and paste to Excel.

This extension is for PivotTables built in Excel (designed from scratch in Excel) to let you see the MDX query it's executing against the server. It doesn't let you modify or specify the MDX query.

New Post: Can I execute my MDX query with this extension?

micaman — Thu, 19 Jun 2008 17:04:33 GMT

I have a MDX query, but I don't know how to run it and get the results in Excel. I've installed this extension, but it seems that it is only capable of reading the existing MDX of the imported tables. Need some kind of MDX query ad-hoc, and the results in Excel. Please help me...

NEW POST: NOT WORK WITH EXCEL 2007 ON XP SYSTEM WITH OFLINE CUBE ".CUB" CREATED WITH EXECEL 2003

marcoangelin — Fri, 28 Mar 2008 15:12:12 GMT

ERROR MESSAGE:

The execution of the query or the opening of the database table failed.

Check the database server or check with the database.

Make sure the database is not available or has been moved or reorganized, and then repeat the operation.

NEW POST: add-in is not working in excel 2007

lucied — Mon, 24 Mar 2008 23:43:36 GMT

i hope we can fix it soon.. i dont want to add calculated member to my olap cube. ty..

NEW POST: add-in is not working in excel 2007

furmangg — Mon, 24 Mar 2008 15:17:41 GMT

You don't need to install the vstor.exe.manifest file.

I'm perplexed why it's not working. I'll keep you posted if I discover anything else. Please do the same.