cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lombardit7 Frequent Visitor
Frequent Visitor

Best Practices for PowerBI/Power Pivot Integration?

First time poster here. I have a marketing report i've built for a client that integrates 22 sources of data using PowerQuery (Get and Transform) to bring in, clean, and load to PowerPivot. In PowerPivot, i've created the relationships between the various Fact and Dimension tables, create calculated measures and fields and link to a date table. I've begun importing everything into PowerBI, but am having a difficult time maintaining what is now two seperate data models, especially since the marketing report needs to continue to grow to accommodate for new sources, calculated measures and relationships, etc.

 

Yes, Power BI can handle these things, but it doesn't seem to me that it is designed to be a robust data modeling tool as it lacks many of the modeling and query features. So my question is this: what is the best workflow for a Power Pivot-led data model between BI? At the moment, as soon as I add new relationship and measures in my updates Power Pivot data model, I can no longer change the data model of an existing BI report. I need to create a new Power BI report from scratch. Any ideas/solutions would be very appreciated. 

6 REPLIES 6
lombardit7 Frequent Visitor
Frequent Visitor

Best Practices for Maintaining Power Pivot and Power BI data model

First time poster here. I am building a marketing report for a client that is pulling in 22 different queries from various sources and is using a star schema Power Pivot data model. We are using Get and Transform to bring in, clean and create source-specific foreign keys, and then creating multi-source append tables to load into the data model. We are using Power Pivot for the relationship building, custom measures, and KPI creation. 

 

I am now starting to import our Power Pivot data model relationships and queries into BI but am beginning to have difficulty maintaining what is now two different data models. Let's say I am creating a visual and realize that I need to create a new measure. I can do this Power BI, but do I reallly need to back track and update this in Power Pivot too? If I added new measures into my Power Pivot data model, I would need to create a new Power BI report to be able to use them as there doesn't appear to be a way to refresh the underlying data model...only the pre-built queries. 

 

I realize that Power BI can accommodate our data query and data modeling needs, but it seems to me that it is not meant for robust data modeling and it lacks some of the features to do so (such as Perspectives, KPIs, Measure builders, etc). While I have 2-3 reports we are using in Power BI, I need to continue to make changes to the data model to factor in new reporting sources, relationships and measures. Once I have this, will I really need to rebuild the 2-3 reports I already generated? Was just wondering if there is any other tips out there for different workflows for Power BI and Power Pivot. Thanks!

v-huizhn-msft Super Contributor
Super Contributor

Re: Best Practices for Maintaining Power Pivot and Power BI data model

Hi @lombardit7,

Power BI turns anyone into a master of disseminating information the easiest way with its stunning visualizations and interactive dashboards that provide a consolidated live view of your enterprise and its performance. Many power features can be used in power BI, slicer, map, analytics and so on. And you also can create your own measures in Power BI. For Perspectives in power BI, you can review the original thread.

In order to improve performance and usability, you’d better to operate efficiently as following best practices which display several main practices, you can review in this article.

  1. Make datasets user friendly. Rename tables and columns to descriptive terms, remove unnecessary columns, hide columns not needed by users
  2. Try to always use a separate date table. 
  3. Use a star schema when designating table relationships and so on.

>>While I have 2-3 reports we are using in Power BI, I need to continue to make changes to the data model to factor in new reporting sources, relationships and measures. Once I have this, will I really need to rebuild the 2-3 reports I already generated?

The data will not be changed if you import it to model. You need to load the date into Power BI data model if the new report using other new data. Otherwise, you create or amend the measure, and make changes in new report.

In addition, there are difference between PowerPivot and Power BI.

  1. Power Pivot is an in-memory data modelling component that enables highly-compressed data storage and extremely fast aggregation and calculation. It is also available as part of Excel, and can be used to create a data model within an Excel workbook. Power Pivot can load data itself, or can have data loaded into it by Power Query. It is extremely similar to the SSAS (SQL Server Analysis Services) Tabular model, which is like a server-based version of Power Pivot.

  2. Power BI is a SaaS service which enables business users to service their own business intelligence needs. It provides built-in ability to connect to SaaS services like Salesforce and many others. It provides connectivity to on-premises and cloud sources using a combination of direct query and periodic data refreshes. It is available as a freemium service. It is the successor to 'Power BI for Office 365' that was based on Microsoft's Office 365 and SharePoint Online products, and, through Excel 2013, encompasses Power Query, Power Pivot, and Power View.

If you have any question, please feel free to ask.

Best Regards,
Angelia

lombardit7 Frequent Visitor
Frequent Visitor

Re: Best Practices for Maintaining Power Pivot and Power BI data model

Thanks @v-huizhn-msft!

 

I can be more specific with my questions:

  • Once I import a data model into Power BI, is there anyway to "refresh the model", so that any new measures, queries, relationships created in PowerPivot can be added into my Power BI workbook? 
  • If not, it seems to me that I am forced to commit to Power BI for any additional modeling or querying once i've created a new report. Otherwise, any changes I make to my Power Pivot data model, would need to be mirroed in Power BI, or I would need to create a new report and import the updated data model...and rebuild all of my visualizations. 

Thanks!

Tim

v-huizhn-msft Super Contributor
Super Contributor

Re: Best Practices for Maintaining Power Pivot and Power BI data model

Hi @lombardit7,

If your data source is changed, you can Click the refresh in date model window like the highlighted in following screenshot.

Capture1.PNG

But any new measures, queries, relationships created in PowerPivot cann't be added into my Power BI workbook. You should rebuilt them. You can create copy->paste in the formula editor catelog. Then create a new report.

Best Regards,
Angelia

Davidataylor102 Frequent Visitor
Frequent Visitor

Re: Best Practices for Maintaining Power Pivot and Power BI data model

I am having this very same issue.  I compiled all my data into txt documents, then used Power Query (Excel 2013 user still) to bring it into Power Pivot.  I then created several measures and calculated columns and relationships.  Then, I imported this data model into Power BI and began tinkering with the reports.  BUT, when I then had to build a new calculated measure back in Power Pivot, I could not refresh Power BI - indeed, it wanted me to start from scratch!  

 

I could be mistaken, but i think that Power BI wants you to query source data to be used as a substitute for Power Pivot.  You can make your calculated measures in Power BI.

 

BUT, it sucks that the two don't really talk to each other; at least, that is what i have frustratingly found so far.

ajourneyinto Frequent Visitor
Frequent Visitor

Re: Best Practices for Maintaining Power Pivot and Power BI data model

Hi 

Refresh doesn't work, can see the Excel File in Power BI but acutal changes are not reflected in the Reports.

How to proceed?