Connecting to a Power Pivot model from another Excel workbook

[Anonymous]

Hi,

A simple question: is it possible to connect to an a Power Pivot data model within an Excel workbook from another workbook?

Simple scenario: we have set up a data model with the help of Power Pivot in Excel and we would like to create a number of reports based on this model in various workbooks. The only alternative we currently know is taking the one Excel workbook with the data model and then creating the reports *within the same workbook* (using CUBEVALUE(“ThisWorkbookDataModel” formulas). Is there a way to change the connection from “ThisWorkbookDataModel” to the connection of the workbook with the data model in it

If not, what would be the alternatives (note: we are not using Power BI Desktop or the Power BI Service)? Maybe publish the Excel workbook with the data model in it to SharePoint?

Any suggestion would be much appreciated. Thanks, Balazs

[RafaelVargasBRZ]

The solution has two downwards when dealing with big data: 1) nowadays databases can easily reach the ~1M rows threshold; 2) even if the limit is not reached, the workbook performance becomes extremely bad.

[lapestenoire]

Connect a Power BI file to the Excel file containing the data model. Then create a new Excel file and connect it to the Power BI file. 

[Jimmy801 · Accepted solution]

Hello @Anonymous 

the only possibility to access the data model itself it's only possible when you have opened  the other Excel with the datamodel (if even - i know it's possible with Power BI). But this doesn't suit the most business scenario.

The way I've solved such issues to create in your excel as many pivots as you need in other scenarios and then access the data of the pivot table. However you have to update the excel-file manully, every time your data model changes.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks for the reply, Jimmy...was afraid this would be the case ;). Anyhow, it is what it is...

Regards,
Balazs

[Jimmy801]

Hello @Anonymous 

i don't know if you got me right. The solution i've implemented is working well.

Just use PowerQuery of Excel to get the data of the central excel-file and you can automate everything.

So just create pivot-tables that contain every data you need and thats it

What do you think about it?

Jimmy

[Anonymous]

Yes , I understood you, Jimmy.

The one problem we face is that the complete data model, once all data is displayed, is huge.

It is a pity we cannot use instead of "Thisworkbookdatamodel" something like "THATworkbookdatamodel"...i.e. to reference or connect to another workbook...

Thanks again!

Balazs

[androo]

I'd like to be able to do this too, or, even just connect to the main"output" table in the data model from another workbook. It seems the only way to do it is presently is to return the datamodel table/s to worksheets - which can be done provided no table has more than 1m lines (you could conceivably split a larger table over several/many worksheets, you could automate that with power query, but it's already a kludge and this would make it even slower I guess). There seems to be no good answer at the moment other than sql server and/or other more "industrial" solutions.

[dax]

Hi @Anonymous , 

I am not clear about your requirement, did you mean that you want to change model to another workbook in original workbook? If so, you could try to change the datasource in power query directly like below

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi there Zoe,

Thanks very much for the reply - and apologies for not being clear.

Yes, I know how to connect to the DATA in another workbook - but is there a way to connect to the entire DATA MODEL in another workbook?

Our scenario: in order to avoid crashes (which, alas, do happen...), we decided to have the source data in a table in Workbook 1. In Workbook 2, we then connect to Workbook 1 the way you described and import the data into Power Pivot, setting up the required relationships for our data model. Now we would like to reference to this data model (which resides in Workbook 2) from Workbook 3, Workbook 4, Workbook 5, etc.

Through research, we found this link, mentioning Analysis Services for Power Pivot; however, this would require SQL and SharePoint Server to be set up (if I understood correctly).

My question is, whether there is a viable alternative to connect to a data model in an Excel workbook from /(an)other workbook(s).

Thanks again,

Balazs