Import Power Pivot (DataModel) into Power BI

[Anonymous]

I am working with enterpirse EPM (MSFT Enterprise Project Manager). We want to consume data from the EPM system in Power BI (we may set up a Tabular SSAS data model outside of PBI as well). When we export the data in the "extended reporting layer" from EPM, it exports as an Excel 2013 file, with a Power Pivot data model imbedded in it. In Excel 2013 we can view the data model in Power Pivot "manage" (view the data model), we can also use Power Pivot to pviot and chart the data in Excel 2013.

We want to connect the SSAS Tabular model (Power Pivot) inside of the Excel 2013 to Power BI. Using "get data" in Power BI, when we connect to the Excel 2013, we do not see the Tabular model from our Excel 2013 file in the Power BI "get date" (query designer) tool. The Excel file shows a Pivot table (that came pre-created in the Excel 2013 file when we exported the data from EPM), but it does not show any tables or the data model that supports that pivot table, even though, when we look in Excel 2013, we can navigate to the data model. There IS a Tabular model in the Excel 2013 file, but for whatever reason, we can "see" or connect to it in Power BI.

We can make a "flat" Pivot Table in the Excel 2013 file, and when we do that, and connect to it, we can import from the "falt" pivot table - which seems odd. We cannont see the data model, put we can see anything put into a flattened Power Pivot table - this seems like a sub-optimal work around.

My thoughts on the issue: 1) configuration of EPM and supporting SSAS tabular model are not correct, or optimally configured, could be permissions on the model, could be that the EPM/SSAS configaration is set up to make the Excel 2013 connection a "pass though"/live/direct connection to the DB (and when we connect it to Power BI the data doesn't flow all the way through, or the queries do not translate/connect properly in Power BI - they break? permissions?). 2) Direct query mode is being used for the Excel 2013 file and Power BI is not capturing those queries form the model in Excel 2013.

We are still configuring EPM with our arhictects so there are some changes happening in the environment, to that end we are trying to make sure that whatever is exproted out of EPM (on-prem SP) will be able to be consumed by Power BI, either by directely connecting with O Data, or by connecting Power BI to an Excel 2013 file with Power Pivot data model from EPM (this is the solution we are trying now).

Any thoughts or assistance would be greatly appreacited. thank you!

[v-sihou-msft · Accepted solution]

@Anonymous

In Power BI, when connecting to Excel file, it will only load the tables/sheets within Excel. To use the data model like Power Pivot, you need to import the Excel workbook. See: Import Excel workbooks into Power BI Desktop.

In this scenario, since you want to use live connection to your data source, I suggest you build a Tabular model based on Power Pivot, just use "Import from Power Pivot" in SSDT. 

Then you can just open live connection to that tabular model in Power BI Desktop. See: Analysis Services Tabular data in Power BI Desktop

Regards,