Scheduled Data Refresh Update: New Data Sources

Scheduled Data Refresh Update: New Data Sources

Rate This
  • Comments 8

In this Post

Download the Data Management Gateway

Extended data source support

Step-by-step Power Query connection configuration

Additional information

 

We are excited to announce new data sources for the scheduled data refresh feature of Power BI. To support these enhancements, a new version of Data Management Gateway is available. Installation package (version 1.2) can be downloaded from Microsoft Download Center.

 

 

Extended data source support

Supported data source types from Power Query connection string in the Data Management Gateway version 1.2:

Gateway version

Newly supported data source

Authentication type

1.2

 

SQL Server

Windows, Basic

Oracle

Windows, Basic

File (CSV, XML, Text, Excel, Access)

Windows

Folder

Windows

SharePoint List (Online)

Anonymous

SharePoint List (On-prem)

Anonymous, Windows

OData Feed

Anonymous, Windows, Basic, Key (Data Market)

Azure Marketplace

Key (Data Market)

Azure HDInsight

Key (Azure Account)

Azure Blob Storage

Key (Azure Account)

Azure Table Storage

Key (Azure Account)

PostgreSQL

Basic

Teradata

Windows, Basic

Sybase

Windows, Basic

DB2

Windows, Basic

MySQL

Windows, Basic

SQL Azure

Basic

Query that is not accessing data sources

 

[1]: Native queries (custom SQL statements) for relational databases are not supported yet.

[2]:.It is best to place file and folder data sources on shared folders, so that the accessibility of the data sources is more reliable. However, if the data sources are available on every machine with the gateway instance installed, the Power Query connection is still able to get refreshed successfully.

[4]: Web API key and OAuth2 are not supported yet.

Step-by-step Power Query connection configuration

Here is a step by step guide showing the UI improvements for Power Query connection configuration introduced since our May update

1. In the data source page, click new data source > Power Query

 

In addition to SQL Server, Oracle and a Power Query connection, the July update to Power BI Admin Center also supports SharePoint Online Document Library for data indexing. Please refer to related online documentation for more information.

2. In the connection info page, enter a valid Power Query connection string, and click next.


We’ve added more information on this page to help you determine the information needed for a valid Power Query connection string, as well as a complete matrix of supported data sources.
Please note that currently we only support Power Query connection strings from the DATA tab in the Excel workbook, please refer to online document for details. Direct copying Power Query connection string from Power Pivot is not supported. A valid Power Query connection string should contain:

    • Provider=Microsoft.Mashup.OleDb.1;
    • Data Source=$EmbeddedMashup(SomeGUID)$;
    • Location=SomePowerQueryName;

3. All data sources in the Power Query connection will be shown in the data source info page.


To improve the user experience, we’ve added a DETAIL column to demonstrate the information of the data source, and the Details in the right pane is expanded by default.

4. In order to configure a non-configured data source, specify Name and Description (optional) for the data source and select a Gateway. Then click the set credentials button to launch the data source settings dialog.
The layout of the data source settings dialog depends on the Credential type for the data source. For example, you can specify Database or Windows authentication for Teradata, and Account key for Azure HDInsight.


 
You can specify other data source settings in the dialog including privacy level and encrypt connection for relational databases.
You must test connection before clicking ok to save your credentials. And don’t forget to click save in the Admin Center to commit your modifications to the data source . The data source status will be changed to configured.



Please note you will need the latest version (v1.2) of Data Management Gateway for data sources other than SQL Server and Oracle. And all data source in the Power Query connection still need to be on the same gateway. However, you can add more instances to one gateway to solve any scalability issues, which is also a new feature in this July update.

5. When all data sources within the Power Query connection are appropriately configured, you should test Power Query connection before clicking next to make sure the Power Query connection works.

 

6. In the users and groups page, specify users and groups that are allowed to access these data sources to refresh Power Query connections. If a data source already exists, the specified users and groups will be appended to the existing users and groups list of the data source.

 

Now you can successfully refresh your Excel workbook with a data model using the Power Query connection on the Power BI site!

Additional information

 

  • Please refer to data source prerequisites for required providers to access the corresponding data sources. Please note that #literals, Web, SAP BusinessObjects, Active Directory, HDFS, Facebook, Exchange and Current Excel workbook are not yet supported sources.
  • Power Query connection string from Power Pivot is still not supported. You can only get a valid Power Query connection string from a data table.
  • All data sources in the Power Query connection must still be hosted on the same gateway. You may consider adding more instances to the gateway to solve any scalability problems.
  • Data source details are now displayed in the DETAIL column to improve visibility when configuring a Power Query connection.
  • Since privacy levelis a part of data source settings for Power Query connections, it is required to specify a value for privacy level and to confirm the credential before trying to update privacy level.
    • Privacy level for existing SQL Server and Oracle data sources will by default be assigned as organizational. You can update the privacy level afterwards.
    • Privacy level for anonymous data sources is fixed to be public.
    • For data sources not used in Power Query connections, the value of this setting will not take effect and it is safe to choose an arbitrary value.

 

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • This is great, just tested it with a folder with csv files... it WORKS....

    Keep up the great work, you make my working life more fun with every update.

    Thanks

    Wouter

  • Thanks Wouter! We love that you love it.

  • Does this imply that SSAS Tabular is now supported as a Data Source with DMG?

  • This is great progress. Will sass multi dimensional and tabular be supported soon?

  • Great feedback!

    One question still remains: When will SSAS Multidimensional and Tabular data sources be supported?

  • This is great, but how come Teradata is supported, and not MS it's own SSAS Tabular? What are the priorities?

    In my opinion it is a bit of a failure to promote PowerBI and Tabular, but not being able to host a power BI site with a connection to a Tabular model... We're still doing workarounds with local excel distribution (argh).

  • Could you please tell me when this is supported -

    [4]: Web API key and OAuth2 are not supported yet.

  • Thanks for the great info!

    Any idea when scheduled data refresh for Power Query for Project Online will be supported?

Page 1 of 1 (8 items)