Export (0) Print
Expand All

Data sources supported for workbooks in SharePoint Online

 

Applies to: SharePoint Online Enterprise (E3 and E4), Power BI for Office 365, Office 365 ProPlus, Excel Online, Excel 2013

Topic Last Modified: 2014-07-10

Summary: Learn about what kinds of data sources you can use in workbooks stored in SharePoint Online.

In Excel, you can connect to lots of data sources, such as SQL Server Analysis Services cubes, SQL Server tables, OData data feeds, Azure Marketplace data, and so on. But not all of the data sources that you can use in Excel are supported in SharePoint Online. When a workbook data source is not supported, you might not be able to view it in a browser window or update the data.

Depending on the information and your organizational needs, it might be okay if people can’t update the data in a workbook in SharePoint Online. For example, a chart of accounts or a yearly budget doesn’t necessarily require updating very often. In other situations, such as when a manager wants to view the latest sales information, the ability to update data is very important. SharePoint Online, together with Power BI for Office 365, gives you lots of options for working with external data in workbooks that are published to SharePoint Online.

  • External data in a Data Model is a dataset that uses one or more external data sources, such as a Azure SQL Database database, an OData data feed, or a SharePoint list. Depending on the data sources that are used and what users’ Office 365 subscriptions include, users can update the data while they are viewing the workbook in a browser window.

  • Native data in a Data Model is a dataset that consists of one or more tables of native data. Native data is manually updated. Therefore, people cannot update the data while they are viewing the workbook in a browser window.

  • Native data in a worksheet is also known as worksheet data or sheet data. It’s data that is manually entered or imported into a worksheet in Excel. People cannot update the data while they are viewing the workbook in a browser window.

NoteNote:
File size limits apply to workbooks in SharePoint Online. As a best practice, try to keep your workbooks no larger than necessary. When a workbook exceeds the file size limit that is specified for a particular user, that person cannot view the workbook in a browser window. For more information, see File size limits for workbooks in SharePoint Online.

In Excel 2013, you can work with external data without adding that data to a Data Model. However, if you’re planning to publish a workbook that uses external data to a library in SharePoint Online, the data must be added to the Data Model in the workbook. When you create a Data Model, you use Power Pivot functionality to create a relational data source inside an Excel workbook.

The latest release of SharePoint Online supports workbooks that contain a Data Model using the following kinds of external data sources:

TipTip:
If you have Power BI for Office 365, you can use Power Query to find data sources. See Introduction to Microsoft Power Query for Excel.

Once you’ve identified the data sources that you want to use in the workbook, the next step is to create the Data Model. For more information, see Create a Data Model in Excel.

If you’re using a data source that is currently not supported for workbooks in SharePoint Online, you can create a Data Model using native data. To do this, you import data into worksheets in Excel, and then you add one or more tables of data to the Data Model.

TipTip:
Before you perform the following task, make sure that the Power Pivot add-in for Excel is enabled. See Start Power Pivot in Excel 2013 add-in.
To create a Data Model using native data
  1. Manually enter or import data into a worksheet in Excel. Use one worksheet per table of data that you want to use in your Data Model.

  2. Select a worksheet that contains data.

  3. In the worksheet, select a range of data that you want to add to the Data Model, and then format it as a table. (On the Home tab, in the Styles group, choose Format as Table.)

  4. To give the table a name, choose Table Tools > Design > Table Name.

  5. Select the table, and then choose Power Pivot > Add to Data Model.

  6. Repeat steps 2-5 for each worksheet of data.

  7. To create relationships between tables, see Create a Data Model in Excel.

After you have saved the workbook to a library in SharePoint Online, you can manually update the data.

NoteNote:
When you store data in tables and add those tables to the Data Model in Excel, the workbook contains two copies of the same data, which can cause the workbook’s file size to be larger than you might expect. When a workbook exceeds the file size that is specified for a particular user, the workbook won’t display in a browser window. For more information, see File size limits for workbooks in SharePoint Online.
To update a workbook that contains a Data Model using native data
  1. Locate the workbook in SharePoint Online that you want to update, and then open it in Excel for editing.

  2. Select the worksheet that contains the table of data that you want to update, and then manually update the data.

    Repeat this for each table of data that you want to update.

  3. Save and close the workbook.

To import data into Excel, you have several options. You can manually enter data, or you can use an external data connection to bring data into a worksheet. To use an external data source, you’ll have to know the name of the location (such as a server or a website) where the data is stored and what credentials you'll use to connect to the data. When you use native data, you do not rely on external data connections to update the data, but you can use an external data connection to import data into Excel.

To import data into Excel using an external data source
  1. In Excel, on the Data tab, in the Get External Data group, choose one of the following options:

    • Choose From Access to use data that is stored in an Access database.

    • Choose From Web to use data from an internal or external website.

    • Choose From Text to use data that is stored in a text file.

    • Choose From Other Sources to use data that is available in Azure SQL Database, SQL Server, SQL Server Analysis Services, Azure Marketplace, OData, an XML file, or data that is available through a custom provider.

    The Data Connection Wizard opens.

  2. Specify the information that is required for each step of the wizard, and then click Finish.

  3. In the Import Data dialog box, choose Table, select the Add this data to the Data Model check box (if you can do this), and then choose OK.

After you have saved the workbook to a library in SharePoint Online, you can manually update the data.

To update a workbook that contains native data
  1. Locate the workbook in SharePoint Online that you want to update, and then open it in Excel for editing.

  2. Select the worksheet that contains the data, and then manually update the data.

  3. Save and close the workbook.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft