0

Short version: Is there any way/hack to use the embedded DataModel/PowerPivot cube of an Excel 2013/6 file from another Excel file?

Long version:

We have a large Excel Data Model with >400k rows and >100 measurements, feeding multiple reports (i.e. PivotTable on separate worksheets). As all this is growing, we want to split this out into a (large) data model and multiple reports. I know this could be done with SharePoint or PowerBI - however one of the key requirements is to be able to analyse the data offline. Hence, I'm trying to figure out any way to connect to the data model from another file....

Peter Albert
  • 16,917
  • 5
  • 64
  • 88

2 Answers2

0

There's no way that I know to do what you're asking. Is there any reason you can't just include all the reports in one workbook with the data model? Since you have to be able to analyze offline, anyway, everyone will need a local copy of the model. If the concern is just that there will be too many sheets in a single workbook, you could just put a thin veneer of VBA in it to hide and unhide sheets in groups for ease of use.

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • Thanks! The idea is that users can than individualize their reports. Having the DataModel in a central file would allow to simple "swap" the data file (after a rather long refresh) that is done by one user... If everything is in one file, individualization is not really possible. Also, some of the reports will be rather larger... – Peter Albert Jan 08 '16 at 13:23
0

It looks like Microsoft has added an option to establish connection via ODC file. See this f.e. https://learn.microsoft.com/en-us/sql/reporting-services/report-data/use-an-office-data-connection-odc-with-reports?view=sql-server-ver15

However it's not working out for me, I am using Excel 2016, exported data model from the file with data model as a separate odc file but when I try to add this as a connection in another file - I get the message - can't open the file. Looks like creating ODC file is not that straightforward.

Anyone had similar issues?

Yury Suturin
  • 26
  • 7