General advice about Power Pivot and Power BI

[ruthhacche]

I am struggling to understand all the links to the underlying data sources and how data refreshes.

I build a model in Desktop PowerBi and create all my reports there and publish. But then I can also use that published dataset via GetData in excel to create various pivots for processing etc. But in those instances (I think) I have to refresh the model in BI (by schedule or manual) before new data is available in those pivots. Is this correct? If I had built the pivots from the source data directly using power query in excel (recreating the model there) then they would be realtime? I doubt I have specified my connections correctly and no doubt my model should be somewhere else completely..

Can someone point me in the direction of a really basic explanation of this and data gateways and connection types etc so that I can work out where my model needs to be and how I can use one model to create all my reports and be a source of real-time data for my pivots. All the ones I have looked at assume a greater underlying knowledge of networks and connections than I have - I am looking for a real dummies' guide.

 

[denzo36]

Hi your question is a bit confused but Power BI, Power Query and Power Pivot are all different. Power Pivot and Power Query are Excel based, Power BI is a different program but the transform data bit looks similar to Power Query in Excel.

You can connect Excel directly to your data source via Power Query (such as SQL server database) - when you connect you can then use Power Query to clean and transform your data and load the cleaned data back into excel via a table, pivot table or you can load it to the data model which is basically Power Pivot. Everytime you want to refresh the data, you just need to refresh the data connection via the Query tools, this has to be done manually (or you could write a macro that refreshes when you open the workbook or use Windows task scheduler to run a script to refresh for you).

I'm not too familiar with Power BI but it's a similar story except I think you can set you're data to automatically refresh in Power BI. The process is the same, connect to a data source, clean and transform your data then build views, dashboards, tables etc from that.

What kind of data source are you connecting to?

 

[ruthhacche]

Thank you Denzo. It is quite hard for me to phrase my question but thanks for making the effort to try and understand me. I can refresh the data in excel but I am trying to ascertain to what I am refreshing.

I have both SQL and SalesForce data in my model. If I build my model in powerBI and then pull the data into a pivot in excel I have two levels of refresh:
- the data as last refreshed either manually or by schedule within the BI dataset (when I refresh there I get the live position in SF and SQL)
- the data then refreshed manually within an excel pivot pulled from that BI dataset.

If I build my model using power query in excel directly I refresh directly into the underlying data getting the live position.

Currently, as structured, when I refresh in excel it is only updating to the version of the data in the dataset in powerBI (I think). Or is there some sort of chain reaction that when I refresh in excel it automatically refreshes the dataset in powerBI also? It appears not from what I can seebut this is essentially my question. Can I not have live data in pivots that are pulled from a BI dataset until that dataset itself refreshes? No-one wants data more than a few minutes old in this day and age so refreshing to data that was refreshed to source an hour ago is not good enough.

I do not think this is right anyway. There must be a solution either in how I am structuring things or where my data set sits. Microsoft clearly want us to be able to pivot directly from BI models (there is money to be had by making the BI add-on an almost essential part of excel) so the level of refresh this route has to make sense somehow.

 

[denzo36]

My feeling is that if your pulling your pivots in excel from your model in Power BI that the Power BI data because the master source so to speak so that would need to be refreshed for your excel pivots to have the most up to date data.

In my experience Excel does not have automatic refresh functionality, you either have to manually refresh the pivot tables the old school way (right clicking on each one and 'refresh') or having a macro that will update all pivot tables. If you have many pivot tables a macro is probably the best way.

Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT

    Next WS

End Sub

I could be wrong here but I don't think it's possible to have excel automatically update when your source data updates, there has to be some manual intervention. You can maybe use on Open Workbook change event that runs the refresh macro above when the workbook opens but its seems from your post that you would have your excel open most of the time if you are looking to have the data up to date by the minute.

Hope that helps!

 

[ruthhacche]

So I am importing all my tables in BI (not direct query) which means I can only refresh up to 8 times a day in power BI. And refresh in excel can only be to the latest refreshed dataset. This is fine for my dashboards and BI reports but will not cut it for my operational data in pivots in excel. I have just tested it and I can get live refreshable data if I recreate the model in power query in excel. I am sure I do not need to do things twice (even if it is only copying and pasting from the editor) so am off now in pursuit of a better solution. Thank you Denzo.

 

[ruthhacche]

found this
Re: Export data model from Power BI Desktop to excel (Power Pivot) - see the response from Mike which is what I have been looking for,

 

[denzo36]

thanks for that, glad you got there in the end!