Create a Data Source and Enable Cloud Access

Notes   This article applies to the previous Power BI experience (commonly called Power BI for Office 365), and not to the new Power BI experience. Try the new Power BI. Read more about migrating from Power BI for Office 365 to the new Power BI.

This article applies to the previous Power BI experience (commonly called Power BI for Office 365), and does not apply to the current Power BI experience.

In this article

Introduction

Creating a data source in Admin Center

To create a SQL Server or Oracle data source

To create a Power Query data source

To create a SharePoint Online Document Library data source

Get a connection string from an Excel workbook

Get a connection string from the Data Model in Power Pivot

Get a connection string from a data table

Create a connection string manually

To manually create a connection string

See Also

Introduction

When you create an Excel workbook with a Power Pivot model that uses data from an on-premises data source and stores it in SharePoint Online, you will not be able to refresh the workbook unless an IT Administrator enables the on-premises data source to be accessible from cloud by using the Power BI for Office 365 Admin Center. An information worker sends an IT Administrator a link to the Excel workbook in SharePoint Online or a copy of the Excel workbook via email, or the connection string for the connection used in the Excel workbook. An IT Administrator performs the following steps to enable the cloud access for the data source:

  1. Install and configure Data Management Gateway if it is not already installed. The Data Management Gateway is a client agent that provides access to on-premises data sources in your organization. To register an on-premises data source with the Admin Center, an administrator must create a gateway in the Admin Center. See Install, Configure, and Register Data Management Gateway for the detailed steps for creating a gateway in the Admin Center. If you try to create a data source without a gateway, you will be redirected to the gateway creation process.

  2. Extract the connection string from the Excel workbook if needed and use the connection string to register the on-premises data source with the Admin Center, enable cloud access for the data source, grant users/groups who can refresh the workbook on the SharePoint Online. This article provides detailed steps to do this step.

Note    For a list of supported data sources and data types, see Supported Data Sources and Data Types.

Important    Maximum number of data sources allowed per tenant is 1000.

Creating a data source in Admin Center

In this step, you will register a data source in the Admin Center by using the connection string that you either received directly from the information worker or extracted from the Excel workbook. The following procedure provides steps for registering the data source in the Admin Center. See the Troubleshooting topic if you still can’t refresh the workbook after performing the steps in the following procedure.

Note    If you have multiple connections that use the same connection string, you will need to create only one data source for all the connections.

To create a SQL Server or Oracle data source

  1. In the Power BI Admin Center, switch to the data sources page. See Introduction to Power BI Admin Center for different ways to access the Admin Center.

  2. In the data sources page, click new data source > SQL Server or Oracle.

  3. In the data source usage page, select how you want to use your data source.

    • Select Enable Cloud Access to refresh your workbooks that reference in your enterprise (or on-premises) data source can be refreshed in SharePoint Online.

    • Select Enable OData Feed to enable data from an on-premises data source as an OData feed that can be accessed by user's corporate-wide from Power Query. To enable an on-premises data source as an OData feed, you also set the tables and views to be exposed in the OData feed. You set tables and views in the data settings page.

  4. In the connection info page:

    1. In the Name textbox, enter a valid data source name. A data source name has characters, numbers, hyphens and underscores. It cannot contain spaces and must start with a character.

      Note    You cannot change the name after you save the data source.

    2. You can enter a Description.

    3. Select a Gateway to use to access the on-premises data source. You can only change the gateway if the cloud credential store for the current gateway is enabled, and only the gateways using the same certificate are available. For more information about Gateway’s, see Introduction to Data Management Gateway.

      Note    You cannot change the gateway after the data source is created.

  1. Select a Data Source Type of SQL Server or Oracle.

  2. You connect to your data source by setting connection properties or entering a connection string. For example, you can select .NET Framework Data Provider for OLE DB for Connection Provider and copy/paste the connection string from the previous procedure.

    • To set Connection Properties:

  3. Select a Connection provider.

  4. Enter a Server name.

  5. Enter a Database name (for SQL Server only). Enter the right case if your server collation is case sensitive.

  6. To set credentials for a data source, and enable access to a data source, click Credentials. The data source cannot be accessed without this information. This will install the ClickOnce application when you click for the first time. You should see the Data Source Manager dialog box. If you run into any issues with using this dialog box, see Troubleshooting issues with using the Data Source Manager.

  7. In the Data Source Manager dialog box, select Authentication Type, and enter user name and password that you want the client gateway to use to access the data source.

    Important    These credentials are used by the data management gateway running on on-premises computer to connect to the on-premises data source.

  8. Click Test Connection to test the connection. If the test is successful, OK is enabled.

  9. Click OK to save the credential settings for the data source.

  10. Click Next.

  • In the data settings page for an OData Feed:

    • Select the tables and views you want to access as an OData feed.

    • To search keywords from an OData feed, you enable the feed. Click Index the feed to select an Index schema only option to allow, or not allow, previewing of the feed in Power Query.

    • If you index a feed, you can specify a schedule to update the index for the feed.

    • Click next.

  • In the users and groups page you specify Office 365 users and groups allowed to search an OData feed in Power Query for Excel and/or access this data source to refresh Excel workbooks in SharePoint Online.

Important    The user or group will have access to the on-premises data source.

  1. Click finish or save if you are updating an existing user or group.

To create a Power Query data source

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

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

    To copy a connection sting from Excel, see Get a connection string from an Excel data table. Please note that connection strings from Power Pivot are not supported.

  3. In the data source info page, select a data source that has not been configured.

  4. Enter data source information including a description:

    • Name is a required setting, which is used to identify a data source in Admin Center.

    • Select a Gateway. For more information about how to create a Power BI Gateway, see Power BI for Office 365 Admin Center Help.

      Note    Please make sure that the Gateway has been upgraded to the latest version to support Power Query data sources.

  5. To set credentials for a data source, and enable access to a data source, click set credentials. The data source cannot be accessed without this information.

  6. In the data source settings dialog box, select Authentication Type, enter your credentials for the data source, select a Privacy Level, and click ok. For more information about Privacy Levels, see Microsoft Power Query for Excel Help. To validate your credentials for a connection, click test connection.

  7. You can expand details to view detailed data source information.

  8. Click save, and the data source status is changed to configured.

  9. When all the data sources within the Power Query connection are appropriately configured, click test Power Query connection to test the entire Power Query connection.

  10. Click next.

  11. In the users and groups page you specify users and groups allowed to access this data source to refresh Excel workbooks in SharePoint Online. The specified users and groups will be appended to the existing users and groups list of the data sources.

  12. Click finish or save if you are updating an existing user or group.

    Note   See Supported Data Sources and Data Types for a list of supported data sources for Power Query connections.

Power Query data source limitations

  • SQL statements are not supported. SQL statements are also known as Native Queries.

  • #literal is not supported.

  • .NET Framework 3.5 is required for gateway version 1.1.

To create a SharePoint Online Document Library data source

  1. In the data sources page, click new data source connection > SharePoint Online Document Library.

  2. In the connection info page, enter a Name, Description, and a SharePoint Online Document Library location. To learn how to find a document library location, see How to find a SharePoint Online Document Library location.

  3. Click set credentials.

  4. In the Data source connection settings dialog box, enter your credentials to the Sharepoint Online Document Library location. Your credentials are not stored in the cloud. Click OK.

  5. Click next.

  6. In the user and groups page, choose who is allowed to search for data sources from the SharePoint Document Library in Power Query.

  7. Click next.

  8. In the review page, click back to modify your settings or create to create a SharePoint Online Document Library data source connection.

  9. The data sources page shows your new data source. To view information about a SharePoint Online Document Library data source, hover over the data source item. INDEX NOW is disabled while we crawl the document library. Click TEST CONNECTION to test the new data source connection.

How to find a SharePoint Online Document Library location

To find a SharePoint Online Document Library web address location:

  1. Browse to a SharePoint Online document library.

  2. Right click Documents or a subfolder, and click Properties.

  3. In the Properties dialog box, copy the Address (URL).

    SPO Documents

Get a connection string from an Excel workbook

You can get a connection string for an Excel workbook in two different ways.

        From the Data Model in Power Pivot

        From a data table

Get a connection string from the Data Model in Power Pivot

1.      From the POWERPIVOT ribbon tab, click Manage.

2.      From the Home tab, click Existing Connections.

3.      In the Existing Connections dialog box, select a connection, and click Edit.

4.      In the Edit Connection dialog box, click Advanced, and copy the connection string from the Connection String text box.

In Excel 2010, switch to Power Pivot tab, click Power Pivot Window on the ribbon, switch to Design tab in the Power Pivot window, and click Existing Connections.

Get a connection string from a data table

1.      From the DATA ribbon tab, click Connections.

2.      In the Workbook Connections dialog box, click Properties.

3.      Click the Definitions tab, and copy the connection string from the Connection string textbox.

Note    If the connection is used by a data model, the Connection string textbox is not enabled.

Create a connection string manually

A connection string is a string version of the initialization properties needed to connect to a data source. The connection syntax is unique to the data provider.

.NET Framework Data Provider for SQL Server

Windows Authentication (Trusted connection)

Server=myServerName\myInstanceName; Initial Catalog={Case sensitive database name};Trusted_Connection=True;

SQL Server Authentication (Requires a user name and password)

Server=myServerName\myInstanceName; Initial Catalog={Case sensitive database name};User Id=myUsername; Password=myPassword;

.NET Framework Data Provider for Oracle (Oracle 8i release 3 or later)

Standard

Data Source={Case sensitive database name};Integrated Security=yes;

Specifying username and password

Data Source=={Case sensitive database name};User Id=myUsername;Password=myPassword;
Integrated Security=no;

To manually create a connection string

  1. Enter a connection string for the specific data provider.

  2. Use a case sensitive database name that matches the database name in the Excel connection string.

    Note    If the case of the database name does not match, the refresh of the workbook will fail.

  3. The provider selected in the connection info page must match the provider in the connection string from the Excel workbook.

See Also

Applies To: Power BI



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language