Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
diverdown1964
Most Valuable Professional
Most Valuable Professional

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 replaced by a different mechanism?

7 REPLIES 7
jbocachica
Resolver II
Resolver II

Hi, the DMG will not be replaced, just updated, by now, the Analysis Services connector is available for download and testing, now you will be able to update your dashboards and reports not only from your OLTP but from your Analysis Services Tabular also.

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co
diverdown1964
Most Valuable Professional
Most Valuable Professional

Thanks John - Yup - the connector is great for those live connections, but I'd love to see some clarity on the DMG story.

There will be more information on the DMG and on prem data refresh coming soon this "summer".

Jen Underwood
Principal Program Manager, BI

There was a significant on-prem data refresh release today. Check out http://blogs.msdn.com/b/powerbi/archive/2015/07/07/refresh-for-on-premises-sources-is-here.aspx 

 

Starting today, in Power BI you can refresh datasets connecting to your on-premises sources such as SQL Server. Any workbook created using Power BI Designer, Power Query or Power Pivot are supported. 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.

All common data sources are supported:

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List (on-premises)
  • File (CSV, XML, Text, Excel, Access)
  • On-premises SQL Server Analysis Services models (uploaded data; not live connections)
  • Folder
  • Custom SQL/native SQL

In addition to on-premises sources refresh, we have enabled two new features for refresh from cloud or on-premises:

  1. More frequent refreshes – you can now set up a schedule to refresh more than once a day
  2. Refresh failure notification/history – you can be notified via email when a refresh fails and also find detailed information in the service for the cause of the failure. You can also view your refresh history and see any failures/successes.

In this blog post, we’ll introduce these new features.

In order to refresh a dataset connecting to an on-premises data source you need to install and configure the Power BI Personal Gateway.

 

More information is available on the Power BI blog.

 

 

Jen Underwood
Principal Program Manager, BI

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

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.

Sweet!!! I'm looking forward to taking it for a test drive.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.