Create offline file for PBI publisher in excel

[Anonymous]

I'm using PBI publisher to view data in excel, and it great.  is there a way to create an offline cache of the cube, so that I don't have to ping the cloud for each time I interact with the pivots.

I see that there's an option to create an "offline OLAP"  under Analyze/OLAP Tools.  When I try to create this offline data file[.cub file], I get a message saying"MS OLE provider for SQL Servier 2016 Analysis Services. This Analysis Services feature is not available with database compatibility level 1200 or higher"

I have no idea what this error message means.  I'm using Excel 2013, but I've tried Excel 2016 with the same result.

Thanks for any help.

[v-yuezhe-msft]

Hi @Anonymous,

Could you please check that if you can create offline cube file when you directly connect to SSAS database in Excel?

Besides, please check your SSAS version following the instructions in this KB article: https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version,-edition-and-update-level-of-sql-server-and-its-components.

Thanks,
Lydia Zhang

[Anonymous]

Hi Lydia,

I've been simply opening my PBI published dataset using the publisher excel addin.  We don't have an on-premise sql server, we're just using the out of the box cloud configuration.  I've tried, using the connection string that the excel addin creates, to connect directly to the PBI server through Excel/Get External Data, but have had no luck.  I 

the connection string using the PBI excel addin is:

Provider=MSOLAP.7;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-********;Data Source=pbiazure://;Location="https://wabi-us-north-central-redirect.analysis.windows.net//xmla?vs=sobe_wowvirtualserver&db=******... Properties="DataSource=pbiazure://";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, *********;Update Isolation Level=2

I really appreciate the help help on this.  It wil be a lifesave if I can get this figured out.

Thank you!

[v-yuezhe-msft]

Hi @Anonymous,

Do you connect to Azure Analysis Services or on-premises SQL Server Analysis Services in Power BI ?

Thanks,
Lydia Zhang

[Anonymous]

I know I'm not connecting through an on-premise SQL server.  I just connect to my data set through the default Power BI addin, where it creates a connection in Excel.  I'm not sure if that's Azure or not.  I know my company hasn't done any infrastructure around PBI, it's configured as it's out of the box.  I hope that provides the info you're looking for.

thanks!

[v-yuezhe-msft]

Hi @Anonymous,

I know that you use the Power BI add-in in Excel to connect to your dataset. Could you please tell us what is the data source in your dataset? In other words, in Power BI Service or Power BI Desktop, how do you connect to your data source?

Thanks,
Lydia Zhang

[Anonymous]

Hello,

In Power BI Desktop, I connec to an access database.  From there I import and publish.

Thank you,

[v-yuezhe-msft]

Hi @Anonymous,

I don't think you can create Offline data file in this scenario. When directly connect to Access database in Excel, the Offline OLAP option is disabled. As far as I know, the offline OLAP option is used to create offline .cub file for SSAS data source.

Regards,
Lydia Zhang

[Anonymous]

To be clear, I'm taking data from access loading it into Power BI cloud, then accessing the PBI cloud from Excel.  Not Excel to Access.

Thanks,

[v-yuezhe-msft]

@Anonymous,

I understand your scenario and I have tested it. As my previous post, it doesn't support to create offline file in this case, please vote the above ideas.

Regards,
Lydia Zhang

[pjohnson4684]

Lydia-  Neither of the ideas you promoted apply to the scenario @Anonymous wants.  This is probably why he dropped conversing with you over the issue.  As it is, I am now in the same position, needing to have a cached version of my Power BI data in the Excel file in which I've used the Power BI connection.  In Power BI, I import data directly from a SQL Server DB (not an analysis cube) using a model I've designed and published with the Power BI for Desktop application.  I can use this model in the cloud or via Excel (with the add-in).  Using Excel, I now need to capture and cache the data so I can use the file offline.  The "Create offline data file" is not available to me as I get the same error as AAllen in his initial post.  No doubt, the system does not currently support this need, but voting for the ideas you promote isn't going to solve the issue either.

[v-yuezhe-msft]

@Anonymous,

Some ideas about this issue have been submitted in the following links, please vote them up.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17682037-read-files-cub-with-power-bi
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6936653-connect-to-offline-external-powerpivot-model


Regards,
Lydia

[Anonymous]

Hi Everyone with the additional information, can someone help me with my original request, of how  to create an offline file for PBI publisher in excel.  Having this would help out, because the data could be loaded once, and manipulated quickly, versus pinging the cloud over and over again.

Thanks!!

[v-yuezhe-msft]

Hi @Anonymous,

Which version of SQL Server Analysis Services do you connect to in your dataset?

Thanks,
Lydia Zhang

[Anonymous]

I'm just publishing to a group, then interacting with that data through the Excel publisher.  I'm honestly not sure which version of SQL Server Analysis Services I'm connecting with.  How can I determine this?

In excel, The data connection reads:

Provider=MSOLAP.7;Integrated Security=....

[Greg_Deckler]

MSOLAP.7 is SQL Server 2016

[Greg_Deckler]

I can recreate this, not sure that is of any help. Using the 64-bit version of Publisher.