Offline OLAP Cube export from Excel fails with message 'This Analysis Feature is not available with database compatibility 1200 or higher'

Question

I have a pivot table in excel which is connected to a local instance of Microsoft Analysis Server. I would like the table to be accessible offline, so I can send the workbook to colleagues.

I tried exporting the data as an offline OLAP Cube (Pivot Table Analyze>Calculations>OLAP Tools>Offline OLAP and then select 'create offline data file')

However I receive the error message 'Microsoft OLE DB Provider for Analysis Services: 'This Analysis Feature is not available with database compatibility 1200 or higher'. The message appears after all the preliminaries have been completed, such as selecting the tables, hierarchies and measures to export, and appears regardless of my selection of items.

I don't want to lower the compatibility level of the model because I need features that are only available for Compatibility Level 1400 and upwards, such as the Hide Members option to deal with ragged hierarchies.

I haven't been able to find any reference to the feature being disabled in the documentation and it seems odd to me that Excel allows me to go through the whole process of specifying what is to be exported, only to deliver the message at the end. It makes me wonder if the message is misreporting a different problem.

I'd be grateful to know if others have experienced this problem, and if anyone can suggest a work-around to be able to export an Analysis Server Model so it can be used by Excel offline. I wouldn't have thought this is an uncommon requirement.

Of course I can always reconstruct the model inside the Workbook using Power Pivot, but then i am not able to use the 'Hide Members' facility and also it wastes time (and risks errors) to reproduce the model manually, when there is a 'standard version' on my local Analysis Server.

Answer 1

Hi,

I think the OLAP tools in PowerPivot is referring to multi-dimensional OLAP cube. (Actually in official doc, OLAP is only a name for MD cube)

As far as I know, for now the offline is not supported for tabular model. You could save the book sheet after you import the data to pivot table. But some activity is limited for this saved book sheet, we could not have same experience as an online connection.

And I think the error could be a misreporting info. I tried with 1050 compatibility level tabular, it will still error and could not work out.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks @Lukas Yu -MSFT

You are right that the feature doesn't seem to be supported, because the option to upload an offline cube is greyed out - I did not see this before, because I was focussed on producing a cube rather than using it (since until I could produce one, there were no cubes to upload).

So it seems to me that, since the feature is disabled, it's odd to have it in the ribbon.

If the 'hide blank members' option was available in Power Pivot it would solve my problem because then I could just build everything in the Excel Data Model. Though it would also be good to have a workable route to get the online data into the Data Model directly from Analysis Server (or Power BI)