Refreshing Excel Workbook data from on prem data sources

[diverdown1964]

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?

[jbocachica]

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

[diverdown1964]

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

[JenUnderwood]

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

[JenUnderwood]

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.

[smccormick23]

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]

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.

[timrodman]

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