Forum Discussion
EPM - Enterprise Project Manager - on-prem - problem consuming tabular model from Excel 2013 in PBI
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!
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
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.