Analyze data from SQL Server Analysis Services by using Excel

 

Applies to: SharePoint Server 2010

You can use Microsoft Excel 2010 and Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel 2010 to create PivotTable and PivotChart reports to analyze data imported from Microsoft SQL Server 2008 Analysis Services (SSAS). PowerPivot for Excel 2010 is an add-in to Excel 2010 that you can download from the Microsoft Download Center (https://go.microsoft.com/fwlink/p/?LinkId=224183).

Whether you use Excel 2010 or PowerPivot for Excel 2010 depends largely on how much data that you are analyzing. PowerPivot for Excel 2010 supports more rows of data and lets users import data from multiple sources. It also uses all native Excel 2010 features but processes calculations faster through the in-memory business intelligence engine.

The network administrator or IT department deploys and maintains the various servers in large and mid-size organizations, but the information worker (IW) often has to access the data that is stored on these servers. Excel 2010 and PowerPivot for Excel 2010 enable IWs to perform self-service tasks for business intelligence. Importing the business intelligence data from SQL Server 2008 Analysis Services is one of the first steps required before any analysis can be done. Typically, IT departments control the back-end data for organizations and can answer several important questions:

  • What data do I need?

  • Where does this data reside?

  • Do I have permission to access this data?

  • Are there data connection files that I can use to access this data?

Once the data is imported with either Excel 2010 or PowerPivot for Excel 2010, you can filter, organize, or sort it for further analysis.

PivotTable reports provide an interactive way to summarize large amounts of data, analyze, explore and present summary data. PivotChart reports provide a visual aspect of the workbook data with the PivotTable and can help you compare, and find any patterns or trends in the data. Creating and maintaining data in Excel provides the ability to continually edit values in the workbook or worksheet, and you then use this data for analysis in either Excel 2010 or PowerPivot.

When you create a PivotTable report from external data, you are creating a connection to the external data source, typically by using an Office Data Connection (ODC) file. Connection information can also be embedded inside an Excel workbook, referenced from a local or network drive, or referenced from a SharePoint Server trusted location, such as a data connection library. The ODCs support the following data providers:

  • SQL Server, SQL Server Analysis Services, and PowerPivot

  • Oracle

  • IBM DB2

Note

A SharePoint Server administrator can install and register additional data providers.

In this article:

  • Analyzing data in Excel_2nd_CurrentVer

  • Analyzing data in PowerPivot4Excel_2nd_CurrentVer

  • Saving the PivotTable or PivotChart to MOSS_2nd_CurrentVer

Analyzing data in Excel 2010

Analyzing data from an external source with the Excel client can involve some or all of the following steps:

  • To create a PivotTable or PivotChart report that uses an external data source

  • To configure the PivotTable Report

  • Using slicers in PivotTables

This section first explains how to create an ODC file to store a connection to SQL Server 2008 Analysis Services. Next, it describes how to use the stored connection to import data from the external source and finally, how to use PivotTable or PivotChart reports to analyze the data with Excel 2010.

The following procedures describe how to create an ODC file to store a connection to SQL Server 2008 Analysis Services to import external data and then configure a PivotTable and PivotChart report.

To create a PivotTable or PivotChart report that uses an external data source

  1. In Excel, click the Insert tab, and in the Tables group, click the arrow under PivotTable and then click PivotTable. Note that you can also create a PivotChart based on the PivotTable report at the same time. To do so, click PivotChart.

  2. On the Create PivotTable window, in the Choose the data that you want to analyze section, click Use an external data source to connect to an external data source, for example SQL Server 2008 Analysis Services. Next, click Choose Connection, where you can use an existing Office Data Connection file (ODC), or click Browse for More and then on the Select Data Source window, click New Source to open the Data Connection Wizard.

    The Data Connection Wizard requires the following information:

    • Server or instance name

    • Logon credentials (Integrated Windows authentication or fixed credentials)

    • Database (Relational or OLAP)

    • Table or view for a relational database, or a cube or perspective for OLAP

    • Name and storage location of the data connection file (such as a local or network drive), or a SharePoint Server trusted location (data connection library)

    • Optional description

    • Search keywords

    • Friendly name (name for the data connection file seen in the workbook)

    • If Excel Services will be used with the connection file, one of the following authentication settings must be set:

      • Windows Authentication   Default authentication method that uses the current user's credentials

      • SSS   Credentials are saved in a Secure Store Service (SSS) application (requires the ID of the SSS or application)

      • None   The authentication information is embedded in the connection string and requires the unattended service account

  3. On the Data Connection Wizard window, select the data source that you want to connect to from the available list, and then click Next.

  4. In the Connect to Database Server section, type the server name in the first box, and select the Log on credentials to use to connect. In this example, select Use Windows Authentication as the logon credentials, and then click Next.

  5. In the Select Database and Table section, click the arrow in the Select the database that contains the data that you want section, and then select the database from the list. Next, click Connect to a specific cube or table if you know the specific data that you want to connect to, select the cube or table from the list at the bottom of this window, and then click Next.

  6. On the Save Data Connection File and Finish window, type the name and description for this new Data Connection file (ODC), and then click Finish to save. You can also type Search Keywords at the bottom of this window, and select the Always attempt to use this file to refresh data option if it is needed. Click Authentication Settings if you want to specify the kind of authentication that you want for this data connection when the workbook is accessed by using Excel Services, and then click OK. Click Finish on the Data Connection Wizard.

  7. Now that you have created a new ODC connection to use for this external data source connection, you are returned to the Create PivotTable window. In the Choose where you want the PivotTable report to be placed section, select one of the following location options:

    • Click the New Worksheet option to place the PivotTable report in a new worksheet starting at the first cell, A1.

    • Click the Existing Worksheet option to place the PivotTable report in an existing worksheet, and then in the Location box, specify the first cell in the range of cells where you want to position it. The default location is in the first worksheet at the first cell, shown as, Sheet1!$A$1.

  8. Click OK when you have finished.

Note

If you create a PivotChart report while you create a PivotTable report, Excel displays the chart on the associated PivotTable report. A PivotChart report and its associated PivotTable report must always be in the same Excel workbook.

Excel adds an empty PivotTable report to the location that you specified and displays the PivotTable field list so that you can add fields, create a layout, and customize the report according to what data that you want to analyze. In this scenario, we added the empty PivotTable report to a new Excel worksheet. Next we add fields to the report by using the following procedure.

To configure the PivotTable Report

  1. To add fields to the empty PivotTable report in the Excel worksheet, use any of the methods in the following list.

    Note

    If you create a PivotChart report at the same time as a PivotTable report then Excel displays the chart on the PivotTable report. The PivotTable report supplies the source data for the PivotChart report. This means that at any time that the data or layout is changed in one report, the other report changes too.

    • Select the check box next to each field name in the field section. The field is positioned in a default area of the layout section. However, you can rearrange the fields if you want.

      Note

      Nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Column Labels area.

    • Right-click the field name and then select the appropriate command that you want to use. The following commands are available:

      • Add to Report Filter

      • Add to Column Label

      • Add to Row Label

      • Add to Values

  2. Alternately, you can drag a field to a layout section area. In the Choose fields to add to report box, click and hold a field, and then drag it from the field section to the layout section where you want to position it.

In the PivotTable field list, you can control the PivotTable and PivotChart data by adjusting the field layout of the data. This lets you customize your data to make it easier to analyze. For more information, see Pivot data in a PivotTable or PivotChart report (https://go.microsoft.com/fwlink/p/?LinkId=224955), on Office.com.

Using slicers in PivotTables

Use slicers in Excel 2010 to filter data in a PivotTable report. Slicers are filtering components that provide a set of buttons that enable filtering. Each slicer filters on a specific PivotTable field so generally you use multiple slicers on each PivotTable report. Shared slicers are used on multiple PivotTables, and local slicers are used in one PivotTable. You can also create a stand-alone slicer that is referenced from OLAP Cube functions and can be used by any PivotTable in the future.

To create a slicer in an existing PivotTable

  1. In Excel 2010 open a PivotTable report and then click anywhere in the report. This enables the PivotTable Tools (group) that includes Options and Design.

  2. Click Options, and then in the Sort & Filter section, click Insert Slicer.

  3. In the Insert Slicers window, select the check box of the PivotTable fields for which you want to create a slicer. To select multiple items, hold down the Ctrl key, and then click the other items that you want to filter.

  4. Click OK. The slicer is displayed that includes every field that you selected.

To create a stand-alone slicer

  1. In Excel 2010 open a PivotTable report, on the ribbon click the Insert tab, and in the Filter group, click Slicer.

  2. In the Existing Connections dialog box, in the Show box, select one of the following connection types:

    • All Connections   Displays all available connections.

    • Connections in this Workbook   Displays only the list of connections that are created for this workbook—for example, connections that were created that use the Data Connection Wizard.

    • Connection files on the Network   Displays only the connections that are available in the network. These connections typically reside in a Data Connection Library on SharePoint Server 2010.

    • Connection files on this computer   Displays only the connections that are available on this computer.

      If you have to create a new data connection file, click Browse for More, and then click New Source. This opens the Data Connection Wizard where you can create a new connection to a remote data source.

  3. In the Select a Connection window, click the connection that you want to use, and then click Open.

  4. In the Insert Slicers dialog box, click the check box for the fields that you want to create a slicer for, and then click OK.

To share a slicer with another PivotTable

  1. Open a PivotTable that contains the slicer you want to share and then click the slicer. The Slicer Tools tab on the ribbon appears.

  2. Click the Options tab on the ribbon and in the Slicer group, click PivotTable Connections.

  3. In the PivotTable Connections dialog box, select the check box of the PivotTable that you want to share, and then click OK.

To use a shared slicer from another PivotTable

  1. In an Excel 2010 workbook, on the ribbon click the Data tab and in the Get External Data section, click Existing Connections.

  2. In the Existing Connections dialog box, in the Show box, verify that All Connections is selected. Select the connection that you want to use and then click Open.

  3. In the Import Data window, click the option button for PivotTable Report, and then click OK.

  4. After the PivotTable report appears, click anywhere in the report to make the PivotTable Tools appear on the ribbon. Click the Options tab, and in the Sort & Filter section, click Insert Slicer, and then click Slicer Connections.

  5. In the Slicer Connections window, select the check box of the slicers that you want to use, and then click OK.

  6. In each slicer, click the items that you want to filter. Note that all PivotTables that share the slicer will display the same filters.

Analyzing data in PowerPivot for Excel 2010

PowerPivot for Excel 2010 provides the ability to import data and then analyze it as you want. After you import the data into the PowerPivot for Excel window, you can then do any of the following tasks to prepare it for analysis:

  • Review the data

  • Filter the information accordingly

  • Manipulate the data by adding or deleting table columns

  • Connect multiple tables with relationships

  • Create new calculations to show the purpose of the data

The flexibility of PowerPivot for Excel 2010 means that these processes are not required every time that you prepare to analyze data, but can often be done independently of one another to help you achieve the same results. For more information, see Prepare Data for Analysis in PowerPivot.

This section explains how to work with the data that you have added to the PowerPivot for Excel window. The following areas are featured in this section.

  • Import data from a SQL Server Analysis Services cube

  • Create a PivotTable or PivotChart report

  • Create a measure in a PivotTable or PivotChart

  • Filter data in PowerPivot by using slicers

  • Save the PivotTable or PivotChart to SharePoint Server 2010

Import data from a SQL Server Analysis Services cube

This section explains how to import data from a SQL Server Analysis Services cube with PowerPivot for Excel 2010. When you import data from a cube with PowerPivot for Excel you must use a query to specify and retrieve the data. Also, note that any data that you import from a cube is flattened. That is, if you define a query to retrieve measures along multiple dimensions, the data will be imported with each dimension in a separate column.

To import data from a cube

  1. In Excel, click the PowerPivot tab, and then in the Launch section, click PowerPivot Window.

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

  3. In the Table Import Wizard window, in the Connect to Microsoft SQL Server Analysis Services page, type a descriptive name for this data connection in the Friendly connection name box.

  4. In the Server or File Name box, type the name of the server that hosts the SQL Server Analysis Services instance and the SQL Server Analysis Services instance name. Use this example: Contoso\SSAS.

  5. In the Database name, click the down arrow next to the box and select, an Analysis Services database from the list.

  6. Click Next to continue.

  7. In the Specify a MDX Query page, either paste an existing MDX query statement that you want to use in the box or click Design to open the MDX query builder.

    If you paste your own MDX query in the box, click Validate to check that the query works.

  8. The following steps use the sample Contoso cube as an example for building a query in the MDX query builder:

    1. Click Select Cube to expand the list of available cubes. This button is located on the left side of the query builder and at the top of the Metadata pane. Select the Sales cube to use from this list.

    2. In the Metadata pane, expand Measures, and then expand Sales.

    3. Drag Sales Amount to the right into the Data pane.

    4. In the Metadata pane, expand the Product dimension and drag Product Category Name to the left of Sales Amount in the Data pane.

    5. In the Metadata pane, expand the Date dimension and drag Calendar Year to the left of Product Category Name in the Data pane.

    6. In the Metadata pane, drag Date to the Dimension column in the Filter pane. In Hierarchy, select Calendar Year. In Operator, select Range (Exclusive). In Filter Expression, click the down arrow and select Year 2005 on the left side, and select Year 2009 on the right side.

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

    7. Click OK, and the MDX query appears in the MDX Statement box.

    8. Type a name for this data set in the Friendly Query Name box, and then click Finish.

    9. In the Importing page, click Close when the data has finished loading.

For more information about how to build MDX queries, see Analysis Services MDX Query Designer (PowerPivot).

Create a PivotTable or PivotChart report

Using an Excel workbook with PowerPivot for Excel, you can create PivotTables and PivotCharts in two locations. You can create these either in the PowerPivot window on the Home tab, or in the Excel window on the PowerPivot tab. You must use one of these options when you use the data in a PowerPivot window to build a PivotTable or PivotChart. Note that the PivotTable button in the Insert tab on the Excel window only uses data that is stored in the Excel workbook, not PowerPivot data.

To create a PivotTable report or PivotChart report

  1. In the PowerPivot window, on the Home tab, in the Reports group, click the down arrow under the PivotTable button.

  2. Select the option that you want from the list that appears:

    • PivotTable

    • PivotChart

    • Chart and Table (Horizontal)

    • Chart and Table (Vertical)

    • Two Charts (Horizontal)

    • Two Charts (Vertical)

    • Four Charts

    • Flattened PivotTable

  3. To specify the location that you want to create the new PivotChart or PivotTable, click New Worksheet or Existing Worksheet.

  4. Click OK. Excel then adds the report to the location that you selected and displays the PowerPivot Field List, with which you can add fields and customize the report.

For more information, see Create a PivotTable or PivotChart Report and PowerPivot Field List.

Create a measure in a PivotTable or PivotChart

After you have created a PivotTable or PivotChart report, you can create a measure to analyze the report. A measure is a formula that is created specifically for a PivotTable or PivotChart and uses PowerPivot data. Use the Measure Settings dialog box to create a measure that contains a standard aggregation, such as a sum, an average, or another calculation that uses the columns and tables in the PowerPivot window. Create standard aggregations exactly like in Excel, by dragging fields to the Values field area and then selecting one of the standard aggregation methods, such as COUNT, SIM, AVERAGE, MIN< or MAX. When you save the measure, it is saved with the base table. However, you can use it anywhere in the PivotTable or PivotChart report.

Create a custom aggregation or user-defined measure with the Measure Settings dialog box. The formulas for a PowerPivot measure are like formulas in Excel but offer various additional functions to work with tables and calculate filtered aggregations, and also to use with time intelligence.

To create a measure

  1. In the Excel window, click inside an existing PivotTable that is associated with a table in the PowerPivot window.

  2. Click the PowerPivot tab, and in the Measures section, click New Measure.

  3. In the Measure Settings dialog box, click the arrow next to Table name and select a table from the drop-down list.

  4. In Measure Name (All PivotTables), type the name to use as the identifier for this measure. Note that the identifier name must be unique. This name cannot be the same name as any column, table, or measure already in the workbook.

  5. In Custom Name (This PivotTable), type the name to use as the display name for this measure in the PivotTable.

  6. In the Formula box, type the formula that you want to use, or click Fx to select a function from a list and build a formula.

    For more information about how to build formulas see Build Formulas for Calculations.

  7. Click Check formula to validate your formula, and then click OK.

To edit a measure

  1. In the Excel window, click inside an existing PivotTable or PivotChart area to make the PowerPivot Field List appear.

  2. In the PowerPivot field list, locate the name of the table that contains the measure that you want to edit, right-click the measure, and then click Edit Formula. Note that Measures appear with a small calculator icon next to the name.

  3. In the Measure Settings dialog box, edit the formula. When finished, click Check formula, and then click OK.

Filter data in PowerPivot by using slicers

Slicers in PowerPivot are the same as in Excel. They are single-click filtering controls that focus the data in PivotTables and PivotCharts. Slicers provide a collaborative method to select conditions and then see the changes. Note that slicers do not add more rows to a table but only provide an interactive view into the data. The following procedure explains how to use slicers in PowerPivot.

You can add slicers by using the ribbon in Excel 2010 or by dragging the field items into either the Slicers Vertical area or the Slicers Horizontal area in the PowerPivot Field List.

To add slicers by using the PowerPivot field list

  1. In the Excel 2010 window, click anywhere in a PivotTable to display the PowerPivot Field List.

  2. In the PowerPivot Filed List, do one of the following tasks:

    • Right-click a field that you want to use to filter and then click either Add to Slicers Horizontal or Add to Slicers Vertical.

    • Drag the field that you want use to filter into the Slicers Vertical area or the Slicers Horizontal areas.

    The slicer appears in the workbook and the slicer items are displayed. Note that by default, all slicer items are selected.

  3. Click the slicer items that you want to apply a filter for in the PivotTable data. Hold the Ctrl key while you click each slicer item to add multiple items.

To arrange the order of slicers in a workbook

  • In the PowerPivot Field List, right-click the field name in either the Slicers Vertical or the Slicers Horizontal areas, and then select one of the following to arrange the order: Move Up, Move Down, Move to Beginning, or Move to End.

To resize and reposition slicers

  1. In the Excel 2010 window, click anywhere in a PivotTable to display the PowerPivot Field List. Note that any Slicers that have already been added using PowerPivot appear inside a gray border.

  2. Click and drag the gray border surrounding the slicer zone to modify the size or realign it.

  3. To position multiple slicers side-by-side, drag them next to one another in the order that you want and they will snap inside the gray border.

Saving the PivotTable or PivotChart to SharePoint Server 2010

After creating the PivotTable and PivotChart reports, you can save them to a SharePoint Server 2010 site so that other users can analyze this data in a Web browser without having Excel installed on their computers. When you save a workbook to a SharePoint Server 2010 site, the complete workbook is saved but you can select individual worksheets or workbook items to publish.

To save PivotTable and PivotChart reports to SharePoint Server 2010

  1. In Excel 2010, on the ribbon click the File tab, click Save & Send, and then click Save to SharePoint.

  2. To select specific worksheets or workbook items to publish, click Publish Options.

  3. In Publish Options, on the Show tab, click the arrow next to the default Entire Workbook in the list, and then click Items in the Workbook. This lets you select specific items to show in the browser such as named ranges, charts, tables, or PivotTables, depending on your workbook.

  4. In the next box, select the items that you want to save from the list, for example All Charts and All PivotTables.

  5. On the Parameters tab, type any defined names that you want to use to specify cells that are editable when users view the workbook by using Excel in the browser. Then click OK to close the Publish Options dialog box.

  6. Choose a SharePoint Server 2010 site to save your workbook from the Current Location, Recent Locations, or Locations lists. If there are no saved sites listed, double-click Browse for a location to open the Save As dialog box.

  7. In the Save As dialog box, type the Web address for the SharePoint Server 2010 site, and then browse to the site where you want to save your workbook.

  8. In the File name box, acknowledge the name as correct or rename the file.

  9. Click the Open with Excel in the browser check box if you want to display the workbook contents in a browser window after you save it.

  10. Click Save.

See Also

Other Resources

Create a Reporting Services Report with PowerPivot Data