Get data from Analysis Services

Analysis Services provides dimensional data that is well-suited for data exploration in PivotTables and Power View reports. You can get Analysis Services data from:

  • OLAP cubes on an Analysis Services multidimensional server.
  • Tabular models on an Analysis Services tabular server.
  • Excel 2013 workbooks on SharePoint 2013, if the workbook contains a data model.
  • Power Pivot workbooks on SharePoint 2010.

You can analyze data from an external Analysis Services data source using an online connection. As you pivot, slice, and filter a report, Excel queries Analysis Services for the data you asked for. Analyzing data in this way requires an online connection to the data source.

Another approach is to work offline. You can work offline by importing data into your workbook so that both data and report visualizations are self-contained and fully portable in a single file. If you do import data, you should know that doing so introduces new requirements, including the Power Pivot add-in, some knowledge of MDX, and sufficient disk space to store larger workbooks. This article explains how to import data from different Analysis Services data sources.

Excel workbooks that you save to SharePoint or Office 365 are subject to a maximum file size. By using the Table Import wizard in the Power Pivot add-in, you can selectively import tables, columns, and rows to reduce overall file size. If your workbook is too big to view in Excel Online, you can open it in Excel 2013 instead.

Office 365 prohibits data refresh against external data sources, including Analysis Services solutions that run on servers in a network. If your requirements include refreshable data, choose SharePoint or use a network file share instead.

In this article


Prerequisites

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

Analysis Services tabular model databases are only in SQL Server 2012.

You must know how to connect to an Analysis Services database. Check with your Analysis Services database administrator to determine server and database names, and which credentials to use.

To import data into a data model, you must have the Power Pivot add-in that comes with Office Professional Plus. You might also need to know how to write an MDX query that retrieves the data you want to use. Alternatives to writing MDX query include using a query builder to choose which measures, dimension attributes, and hierarchies you want to analyze.

To connect to a PivotTable or PivotChart interactively via Excel, no MDX expertise is required. Excel connects to an entire OLAP cube or tabular model.

File sizes will be bigger than what you might be accustomed to. By way of comparison, a workbook using a data connection might easily fall under 100 kilobyte, whereas the same workbook with imported data might be ten times the size. If disk space is an issue, you'll need to make room for larger files.

Power Pivot workbooks that you use as data sources can be published to SharePoint 2010 or SharePoint 2013. The SharePoint site must be running on a different computer than the one you are using to create a report. You must have View permissions on the SharePoint site to report against workbooks on SharePoint.

Connect to a cube, tabular model, or Power Pivot data model

The easiest way to analyze data in an Analysis Services cube or model is to set up a connection to an external database. The PivotTable or report will have a live connection to the data source. Each time you drag a field onto a Values, Rows, Columns, or Filter area of a Fields List, Excel builds a query and sends it to Analysis Services.

Having a live connection to the data you’re analyzing has its advantages. You can perform free-form analysis. Add any field to a PivotTable or report, and Analysis Services gives back the data you requested. A Field List that’s connected to Analysis Services includes all of the objects in a cube or model, so you don’t have to write MDX to get the data you want.

Disadvantages include the dependency on a server connection. If the server goes down, or you want to work offline, data interaction stops completely.

Top of Page Top of Page

Import data from a cube

Any data that is contained in a SQL Server Analysis Services database can be copied into a data model in Excel. 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.

The following procedure demonstrates how to use the add-in and MDX to get a subset of data from a traditional cube on an Analysis Service instance. MDX is always used when building a query using the Power Pivot add-in.

This procedure uses the Adventure Works DW Multidimensional 2012 sample database to explain how to import a subset of a cube. If you have access to an Analysis Services server that has the Adventure Works DW Multidimensional 2012 sample database, you can follow these steps to learn how to import data from Analysis Services.

  1. Start the Power Pivot add-in and open a Power Pivot window.
  2. In the Power Pivot window, click Get External Data > From Database > From Analysis Services or Power Pivot.
  3. In Connect to Microsoft SQL Server Analysis Services, in Server or File Name, type the name of the computer that runs Analysis Services.
  4. 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 Adventure Works DW Multidimensional 2012 sample database, you would select Adventure Works DW Multidimensional 2012.
  5. Click Test Connection to verify that the Analysis Services server is available.
  6. Click Next.
  7. 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.

Choose at least one measure and one or more dimensions.

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 use the designer, see Analysis Services MDX Query Designer (Power Pivot).

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 Not Equal; for Filter Expression, click the down arrow and select CY 2009 and CY 2010.

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

  1. Click OK, and review the MDX query that was created by the query designer.
  2. Type a friendly name for the data set. This name will be used as the table name in the data model. If you do not assign a new name, by default the query results are saved in a new table called Query.
  3. Click Finish.
  4. When the data has finished loading, click Close.

All data that you import from a cube database is flattened. In your model, the data shows up as a single table containing all of the columns you specified in your query. If you defined a query that retrieves measures along multiple dimensions, the data will be imported with each dimension in a separate column.

After you import the data into the data model you might want to check the data type of columns that contain numeric or financial data. If Power Pivot finds empty values in a column, it changes the data type to Text. You can verify and change the data type by selecting each column and viewing Data Type in the Formatting group on the ribbon. You can use the Data Type option to correct the data type if your numeric or financial data is assigned to the wrong type.

To use the data in a PivotTable, switch back to Excel:

  1. Click Insert > Table > PivotTable.
  2. Click Use an external data sources, and click Choose Connections.
  3. Click Tables.
  4. In This Workbook Data Model, select the table you just imported.

Top of Page Top of Page

Import data from a tabular model

The following procedure describes data import using the tabular sample database as an example. You can use the sample, Adventure Works Tabular Model SQL 2012, running on an Analysis Services tabular server, to follow along.

  1. Make sure the tabular database contains at least one measure. Import will fail if the database is missing measures. If you’re using the sample solution, it includes measures.
  2. Start the Power Pivot add-in and open a Power Pivot window.
  3. In the Power Pivot window, click Get External Data > From Database > From Analysis Services or Power Pivot.
  4. In Connect to Microsoft SQL Server Analysis Services, in Server or File Name, type the name of the computer that runs Analysis Services.
  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 AW Internet Sales Tabular Model sample database, you would select Adventure Works Tabular Model SQL 2012
  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, columns, and hierarchies that you want to import.

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 the designer, see Analysis Services MDX Query Designer (Power Pivot).

For this procedure, using the sample model as an example, do the following:

  1. In the Metadata pane, expand Measures, and then expand Internet Sales.
  2. Drag Internet Total Sales into the large design pane.
  3. Expand the Product table.
  4. Scroll to the bottom of the list and drag Category to the left of Internet Total Sales in the large design area. This is a hierarchy, so it will return all of the fields in the hierarchy.
  5. Expand the Date table.
  6. Drag Calendar Year to the left of Category in the large design area.
  7. Expand Sales Territory.
  8. Drag Sales Territory Region to the Filter area at the top of the designer. In Filter expression, select Australia.

MDX query to sample tabular database

  1. Click OK, and review the MDX query that was created by the query designer.
  2. Type a friendly name for the data set. This name will be used as the table name in the data model. If you do not assign a new name, by default the query results are saved in a new table called Query.
  3. Click Finish.
  4. When the data has finished loading, click Close.

After you import the data into the data model you might want to check the data type of columns that contain numeric or financial data. If Power Pivot finds empty values in a column, it changes the data type to Text. You can verify and change the data type by selecting each column and viewing Data Type in the Formatting group on the ribbon. You can use the Data Type option to correct the data type if your numeric or financial data is assigned to the wrong type.

To use the data in a PivotTable, switch back to Excel:

  1. Click Insert > Table > PivotTable.
  2. Click Use an external data sources, and click Choose Connections.
  3. Click Tables.
  4. In This Workbook Data Model, select the table you just imported.

Top of Page Top of Page

Import data from a workbook data model on SharePoint

SharePoint might need additional software to load the data. If you are using SharePoint 2010, you must have Power Pivot for SharePoint 2010. In contrast, SharePoint 2013 includes built-in capability for loading and querying a data model. If you are using SharePoint 2013, ask your SharePoint administrator whether Excel Services is enabled and configured for BI workloads.

  1. In the Power Pivot window, click Get External Data > From Database > From Analysis Services or Power Pivot.
  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, http://Contoso-srv/Shared Documents/ContosoSales.xlsx.

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

  1. Click Test Connection to verify that the workbook is available on SharePoint.
  2. Click Next.
  3. Click Design.
  4. 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.
  5. Click OK.
  6. Click Validate.
  7. Click Finish.

Power Pivot data is copied to the data model and stored in a compressed format. After the data is imported, the connection to the workbook is closed. To re-query the original data, you can refresh the workbook in Excel.

Top of Page Top of Page

Refresh data from an external Analysis Services database

In Excel, click Data > Connections > Refresh All to reconnect to an Analysis Services database and refresh the data in your workbook.

Refresh will update individual cells and add rows introduced into the external database since the last import. Only rows and existing columns are refreshed; if you want to add a new column to the model, you’ll need to import it using the steps provided earlier in this article.

Refresh re-runs the query used to import the data. If the data source is no longer at the same location, or if tables or columns are removed or renamed, refresh will fail, but you will still have the data that you previously imported. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window. Click Design > Table Properties to view the query.

Analysis Services uses your Windows user account to read data from its databases. Before you could import data, a database administrator had to grant your Windows user account read permissions on the database. These same permissions are also used to refresh data. If other people want to refresh the data, they also need read permissions on the database.

Remember that how you share your workbook will determine whether data refresh can occur. On Office 365, you cannot refresh data in a workbook that is saved to Office 365. On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power Pivot in Excel 2013