Forum Discussion

diverdown1964's avatar
diverdown1964
Most Valuable Professional
11 years ago

Refreshing Excel Workbook data from on prem data sources

What is the on prem refresh story for Excel workbooks in Office 365 in the new service? Currently the Data Management Gateway facilitates this. Will the DMG continue to be used, or will it be replace...
smccormick23's avatar
smccormick23
New Member
11 years ago

Hi Jen -

 

I have installed DMG and I'm trying to schedule a refresh of an Excel dataset on my local machine. It was not created with PowerQuery or PowerView, but the text from your link suggests that it isn't required:

 

"... an Excel workbook with data imported into the workbook using Power Query or Power Pivot. An Excel workbook with only worksheet data (without data model) cannot be refreshed in Power BI. For these datasets the Power BI Personal Gateway enables automated refreshes against on-premises data sources in your organization, all via a single gateway you can install directly on your desktop machine."

 

At least, as I'm reading this, if you don't use Power Query/Pivot, then the DMG should be used for refreshes. Is that not right?

 

I can't share the report without adding it to a dashboard, and I can't add it to a dashboard without being able to schedule a refresh. So, if a regular Excel dataset can't be refreshed without using Power Query/Pivot, what is the point of using the BI Desktop application at all?

 

Thanks,

Scott

mgriffin's avatar
mgriffin
Frequent Visitor
11 years ago

Hi Scott,

 

I interpreted that sentence starting with "For these data sets...." to reference the workbooks built with PowerQuery and PowerPivot. I believe the sentence about "a workbook with only worksheet data" was intended to specify what is exluded from working with PowerBI.

 

Here's what I'v experienced:

A workbook with a data model built with PowerQuery and PowerPivot can be refreshed from PowerBI using the personal data management gateway. In fact, you can make updates to the workbooks that feed your data model workbook (the one using PowerQuery and PowerPivot). Then, refresh your data set in PowerBI (the dataset would be the workbook you pushed into PowerBI with the data model)

 

This refresh actually "moves through" your data model and pulls the source changes. You don't actually have to update the data model (PowerPivot/PowerQuery). I find this feature very attractive. The data model becomes a behind the scenes tools that users don't have to interact with. (edit note: this behaviour only seems possible with on-premises files, not files stored on OneDrive)

 

I've also realized that you can use a workbook without a PowerPivot/PowerQuery data model in the Power BI desktop BUT, the data in the source workbook has to be a table (a table ojbect, not just rows and columns). I'm not crazy about this requirement but don't care all that much given that I can build the data model in a workboook, push it to PowerBI, then refresh from PowerBI without having to touch the data model.

 

I hope this helps a bit.

Regards,

Mike G.