EPM - Enterprise Project Manager - on-prem - problem consuming tabular model from Excel 2013 in PBI

[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!

[Bitwize_PowerBI]

Hello,

PowerPivot cannot be used as a data source for Power BI.

You should create a tabular model in SSAS (you could 'convert' the powerpivot workbook to an SSAS tabular model using SSDT - sql server data tools - in visual studio).  Then connect Power BI to the SSAS tabular model.

Regards,

Dries

[Anonymous]

I have consumed Power Pivot data models in Power BI before...

Perhpas my phrasing is off, but without a doubt, I have connected Power BI to an Excel workbook with Power Pivot add-in enabled (Excel 2010 or newer), and have been able to DIRECTLY import the data model from Excel Power Pivot into Power BI - literally 1:1.

I have been able to bring in data sets from Excel that have been converted into Tables (which were then added to the Power Pivot data model in Excel) by just brining in the table from the Excel workbook side, and I have also been able to bring in those exact same table from the Excel file from the Power Pivot data model after those tables were loaded to the Power Pivot data model - meaning loading them in SSAS Tabular model format directly from the model format, and not from the Excel workbook table format.

So what I am saying is - you definitely CAN import an power pivot data model from excel into Power BI, and oyu can do that in a number of different ways...

Likewise, it appears you can also conusme DATA CONNECTIONS (without the actual data) from Excel (say from a SharePoint list you have connected to in Excel, or, in my case, EPM!). Howerer, consuming these connections can occasionally be problematic.

Again, my SYNTAX here may be incorrect, but i have, for example, consumeed in Power BI, as SharePoint list, that was first created in Excel 2013 by: 1) exporting the SP list to an Excel file, 2) taking that table (SP List export) and using the "Add to model" feature of Power Pivot to add that table to a Power Pivot model - that creates a linked table in Excel (of sorts), where the Excel file has the connection string to SP, and you can refresh the data in Excel on the Data ribbon by using the "Refresh button" (you can also view the exact connection string to SP by clicking on the "connections" button on the Data tab). 3) from there you can open Power BI, connect to your Excel file, find the Power Pivot data model (tables) in your Excel file that are created from you connected SP list.

I HAVE DONE exactly that - it is not the most direct way to get data from an SP list (on prem) into Power BI - but in my case I cannot use the O Data connection to get to the SP site, so I am using this solution, which is semi-automated...

This is an examople of what I mean when I say, "consume a Power Pivot data model (using data from another service that is provided by an automated process - connection string [SP] or pre-developed tabular model [EMP]) in Power BI".

The Excel data file I am getting from EPM has the O data connection string in it (i can view them), and it also already has a Tabular model (Power Pivot) embedded in it, complete with a star schema for some of the tables. This tells me (i think) that someone has already developed the tabular model that provides this solution to me using our EPM data. However, I may not have permision to get direct access to the back-end of our EPM instance. TBD.

In the meantime, I am trying to just take the exported Excel file and the embedded Power Pivot (Tabular) data model and consume/connect to it using Power BI.

It seems like it should work - similar soolutions have - the data is IN the Excel file IN a tabular model - in fact if i do a flat pivot table in Excel and then connect Power BI, i CAN consume any columns that have been included in the pivot table - so i could just make flat pivot tables from all of my EPM tables and just connect Power BI and consume the data like that... but it seems a bit silly.

I think I may be able to resolve this through configuration and permissions, but if anyone has encountered tis type of issue: Tabular Power Pivot data model in Excel that just DOES NOT APPEAR in the query/connection tool of Power BI (as it typcially does and has for many other solutions and use cases I have had), please LMK what you have experienced or what I am missing.

Again, if my terminology or understnading is off, please tell me how, why, and where specifically and I will put it to good use.

Thanks for the initial reply!

[Bitwize_PowerBI]

Ok, there's a lot of confusion here.

Allthough SSAS tabular models, powerpivot models and Power BI models use the same technology behind the scenes, they are not the same:

- an SSAS tabular model lives in SQL Server

- a powerpivot model lives in Excel

- a Power BI model lives in Power BI desktop or the Power BI service

Now, there is a difference between an import and a data connection.  When you import, there is no more 'link'

- you can import a powerpivot model in Power BI

- you cannot create a data connection from Power BI to a powerpivot model

without going into detail, if you want to automate your EPM reporting, you have some options depending on your requirements, wishes and budget:

- put your powerpivot workbook (that has a connection to the EPM database) on onedrive for business (you need office365 for that), connect this workbook to the Power BI service, install a gateway and schedule an auto-refresh

- create an SSAS tabular model that connects to your EPM, schedule a refresh of the SSAS tabular model and connect Power BI to that SSAS tabular model

I strongly suggest you hire a Business intelligence/Power BI expert like me to help you work out the details.

Regards,

Dries

[Anonymous]

I am working to get access directly to the back end of the EPM SSAS Tabular model, which I know exists because the data export is coming out of EPM into Power Pivot in a Tabular Model with a pre-defined star-schema. I had been working on a POC with just the data/model export in Excel becuase I was not given direct access to the SSAS model that is supporting the EPM solution. I had been hopefule that I would be able to imprort the Power Pivot Data model from Excel directly into Power BI, which I have been able to do with other solutons, but for some reason, the data model coming from EPM (In Excel Power Pivot) does not appear to me in Power BI when I got to import it... This is very strange to me, but I have not been able to trouble shoot that - again, if you, or anyone can explain that anomoly to me, I would be curious to know why that data model does not appear in Excel - I am going to continue to research that issue, as it seems like an aboration of Power Pivot model behavoir/integration with Power BI.

I am also working with some coutnerparts in another group to get direct access to the SSAS model the supports the EPM solution. This would of course be the preferred solution, as it would give me direct access to the SSAS model and i would not have to export to Excel and consume the model from there. When I started out, I just ran with what I had available to me (Excel data export from EPM). Since encountering this issue, I am going to work to gain direct access to the Tabular model in SSAS.

Unfortunelty, we do not use O365. Most of the Power BI to EPM integration I have seen is for Project Online and O365.

I appreacite the help. I was basically headed towards trying to stand up my own SSAS intance or working to gain access to the instance that supports our EPM platform. I appreciate the confirmaiton on best practices here.

I would still like to crack the code on the Power Pivot (from EPM) to Power BI import, but that remians TBD - doesn't quite makes sense to me why I do not see the Power Pivot model in Power BI, but I will work with that if it becomes necessary.