- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
Senior BI Consultant
Information Workers
http://www.iwco.co
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks John - Yup - the connector is great for those live connections, but I'd love to see some clarity on the DMG story.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

There will be more information on the DMG and on prem data refresh coming soon this "summer".
Principal Program Manager, BI
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:
- More frequent refreshes – you can now set up a schedule to refresh more than once a day
- 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.
Principal Program Manager, BI
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-22-2024 10:38 AM | |||
03-25-2024 06:05 PM | |||
12-21-2017 11:38 AM | |||
04-22-2024 02:12 AM | |||
Anonymous
| 01-06-2020 10:26 AM |
User | Count |
---|---|
32 | |
18 | |
14 | |
11 | |
8 |