Share via

Import Data from Analysis Services or PowerPivot

  • Article

In PowerPivot for Excel, you can use an Analysis Services database as a data source for a PowerPivot workbook. That database can either be a traditional cube, built by using SQL Server Analysis Services, or another PowerPivot workbook that has been published to a SharePoint Server.

This topic contains the following sections:

Prerequisites

Choose an import approach

Import data from a cube

Import data from a PowerPivot workbook

Connect to a PowerPivot workbook as an external data source

How PowerPivot Interacts with Analysis Services Cubes

Prerequisites

Analysis Services cubes must be version SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2.

PowerPivot workbooks that you use as data sources must be published to a SharePoint 2010 site. The SharePoint site must be running on a different computer than the one you are using to import data.

You must have View permissions on the SharePoint site to import data from the PowerPivot workbooks.

Choose an import approach

You can use any of the following approaches to work with Analysis Services or PowerPivot data in an Excel workbook.

Application

Approach

Link

PowerPivot for Excel

Click From Analysis Services or PowerPivot to import data from an Analysis Services cube.

How do I…

PowerPivot for Excel

Click From Analysis Services or PowerPivot to import data from a PowerPivot workbook that is published to a SharePoint server.

How do I…

Excel

Click From Other Sources in the Get External Data group to set up a connection to a PowerPivot workbook that is published to a SharePoint server.

How do I…

Import data from a cube

Any data that is contained in a SQL Server Analysis Services database can be imported into a PowerPivot workbook. You can extract all or part of a dimension, or get slices and aggregates from the cube, such as the sum of sales, month by month, for the current year. However, you should keep in mind the following restrictions:

  • All data that you import from a cube or another PowerPivot workbook is flattened. Therefore, if you define a query that retrieves measures along multiple dimensions, the data will be imported with each dimension in a separate column.

  • The data is static after it is imported. It is not updated from the Analysis Services server on demand. If you want to refresh a workbook to pick up changes in the Analysis Services database, you must create a data refresh schedule after the workbook is published to SharePoint. Alternatively, you can manually refresh data in PowerPivot for Excel. For more information, see Refreshing or Changing Imported Data.

The following procedure demonstrates how to get a subset of data from a traditional cube on an Analysis Service instance. This procedure uses the AdventureWorksDW2008R2 sample database to explain how to import a subset of a cube. If you have access to an Analysis Services server that has the AdventureWorksDW2008R2 sample database, you can follow these steps to learn how to import data from Analysis Services.

  1. In the PowerPivot window, in the Get External Data group, click From Database, and then select, From Analysis Services or PowerPivot.

    The Table Import wizard starts.

  2. In the page, Connect to Microsoft SQL Server Analysis Services, for Friendly connection name, type a descriptive name for the data connection.

  3. For Server or File Name, type the name of the machine that hosts the instance, and the instance name: for example, Contoso-srv\CONTOSO.

  4. Optionally, click Advanced to open a dialog box where you can configure properties that are specific to the provider. Click OK.

  5. Click the down arrow to the right of the Database name list, and select an Analysis Services database from the list. For example, if you have access to the AdventureWorksDW2008R2 sample database, you would select Adventure Works 2008 R2.

  6. Click Test Connection to verify that the Analysis Services server is available.

  7. Click Next.

  8. In the Specify a MDX Query page, click Design to open the MDX query builder.

    In this step, you drag into the large query design area all of the measures, dimension attributes, hierarchies, and calculated members that you want to import into the PowerPivot workbook.

    If you have an existing MDX statement that you want to use, paste the statement into the text box, and click Validate to make sure that the statement will work. For more information about how to build MDX queries, see Analysis Services MDX Query Designer (PowerPivot).

    For this procedure, using the Adventure Works sample cube as an example, do the following:

    1. In the Metadata pane, expand Measures, and then expand Sales Summary.

    2. Drag Average Sales Amount into the large design pane.

    3. In the Metadata pane, expand the Product dimension.

    4. Drag Product Categories to the left of Average Sales Amount in the large design area.

    5. In the Metadata pane, expand the Date dimension, and then expand Calendar.

    6. Drag Date.Calendar Year to the left of Category in the large design area.

    7. Optionally, add a filter to import a subset of the data. In the pane at the top right of the designer, for Dimension, drag Date into the dimension field. In Hierarchy, select Date.Calendar Year; for Operator, select Range (Exclusive); for Filter Expression, click the down arrow and select Year 2005.

      This creates a filter on the cube so that you exclude the values for 2005.

  9. Click OK, and review the MDX query that was created by the query designer.

  10. Type a friendly name for the data set. This name will be used as the table name in the workbook. If you do not assign a new name, by default the query results are saved in a new table called Query.

  11. Click Finish.

  12. When the data has finished loading, click Close.

After you import the data into the PowerPivot window, you can verify the data type by selecting each column and viewing Data Type in the Formatting group on the ribbon. Be sure to check the data type of columns that contain numeric or financial data. PowerPivot will sometimes change the data type to Text if it encounters empty values. You can use the Data Type option to correct the data type if your numeric or financial data is assigned to the wrong type.

Import data from a PowerPivot workbook

  1. In the PowerPivot window, in the Get External Data group, click From Database, and then select From Analysis Services or PowerPivot.

    The Table Import wizard starts.

  2. In Connect to Microsoft SQL Server Analysis Services, for Friendly connection name, type a descriptive name for the data connection. Using descriptive names for the connection can help you remember how the connection is used.

  3. In Server or File Name, type the URL address of the published .xlsx file. For example, https://Contoso-srv/Shared Documents/ContosoSales.xlsx.

    Note

    You cannot use a local PowerPivot workbook as a data source; the PowerPivot workbook must be published to a SharePoint site.

  4. Optionally, click Advanced to open a dialog box where you can configure properties that are specific to the provider. Click OK.

  5. Click Test Connection to verify that the PowerPivot workbook is available.

  6. Click Next.

  7. Click Design.

  8. Build the query by dragging measures, dimension attributes, or hierarchies to the large design area. Optionally, use the filter pane on the top right corner to select a subset of data for the import. Refer to the steps in the previous section for an example of how to build the query.

  9. Click OK.

  10. Click Validate.

  11. Click Finish.

PowerPivot data is copied to the workbook and stored in a compressed format, separate from the original workbook. After the data is imported, the connection to the workbook is closed. To re-query the original data, you can refresh the workbook. For more information, see Refreshing or Changing Imported Data.

Connect to a PowerPivot workbook as an external data source

You can use PowerPivot data as an external data source in Excel without embedding the data in the workbook. You do not need PowerPivot for Excel for this scenario, but you must have the correct version of the Analysis Services OLE DB Provider. To get the latest version of the provider, download and install the Microsoft SQL Server 2008 R2 Analysis Services OLE DB Provider from the SQL Server 2008 Feature Pack page on the Microsoft Web site.

  1. On the Data tab in Excel, in the Get External Data group, click From Other Sources.

  2. Click From Analysis Services.

  3. In the Server Name box, type the address to the PowerPivot workbook. The address must include the .xlsx file that contains the data (for example, https://constoso-srv/team site/shared documents/contoso-quarterly-sales.xlsx).

    Note

    If you get the "XML parsing failed at line 1, column 1" error, you most likely do not have the correct version of the Analysis Services OLE DB provider. You can either install PowerPivot for Excel, or download and install the Microsoft SQL Server 2008 R2 Analysis Services OLE DB Provider from the SQL Server 2008 Feature Pack page on the Microsoft Web site.

  4. Click Next.

  5. In Select Database and Table, click Finish.

  6. In Import Data, specify how you want the data to appear (for example, choose PivotTable Report).

  7. Click Properties and then open the Definition tab to verify that the connection string specifies Provider=MSOLAP.4. This step verifies that you have the correct OLE DB provider.

  8. Click OK, and then click Finish to set up the connection.

A PivotTable field list appears in the workspace that contains fields from the PowerPivot workbook.

How PowerPivot Interacts with Analysis Services Cubes

When you use the wizard to connect to an Analysis Services data source, the PowerPivot VertiPaq engine composes an MDX query against the Analysis Services database and then downloads the data into the workbook. The data cannot be refreshed and is not automatically updated when the data in the cube changes.

Data that you import to a PowerPivot workbook is self-contained after the data import phase. Rather than considering a PowerPivot workbook as a browsing surface for an existing cube, you should consider it as a workspace where you can obtain useful subsets of cube data and from that derive new analyses that are independent from the cube and from other data sources.

If you want to see the MDX statements that are generated by the PowerPivot workbook during import, you can create a trace file. For information about how to create a trace file, see PowerPivot Options & Diagnostics Dialog Box.

If you are accustomed to working with Analysis Services cubes in Excel, you should know that some Excel features cannot be used with PowerPivot workbooks. When you are connected to a PowerPivot cube, the following Excel features are not supported:

  • Offline cubes

  • Grouping in PivotTables

  • The drillthrough command

See Also

Other Resources

Preparing Data for Analysis

Data Sources Supported in PowerPivot Workbooks