Is it possible to connecting PowerPivot data from another Excel file?

[toblju]

Hi, I wonder if it is possible to connecting PowerPivot data from another Excel file? I have a file DATA.xlsx which contains loads of PowerPivot data. Then I have a file for reporting (REPORT.xlsx). In this file I would like to grab values from the PowerPivot model in DATA.xlsx with using cube Formulas. Is that possible? How do I name the Connection in the cube formula?

 

[v0vets]

Hi, I wonder if it is possible to connecting PowerPivot data from another Excel file? I have a file DATA.xlsx which contains loads of PowerPivot data. Then I have a file for reporting (REPORT.xlsx). In this file I would like to grab values from the PowerPivot model in DATA.xlsx with using cube Formulas. Is that possible? How do I name the Connection in the cube formula?

Hi! It looks I found some workaround - it works but with certain limitations.

You can create PivotTable in the core file (on the PowerPivot model) and make links from thin file to that Table using standard formulas like =GETPIVOTDATA("[Measures].[Sum of VALUE 2]",[test.xlsb]Sheet4!$B$5,........
However there are two limitations: 1) the core file must be opened with the thin file; 2) all your nessesary data should fit in the PivotTable in core file - in 1m rows and 16k columns (in most cases it should work but the memory data takes could be at least doubled or even tripled - PowerPivot model + PivotTable "model" + Excel formatting etc)