Self-service business intelligence with Excel, PowerPivot for Excel, and Excel Services

  • Article

 

Applies to: SharePoint Server 2010

This article describes how to configure Microsoft Excel 2010 and Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel 2010 to import business intelligence data and then publish reports on a Microsoft SharePoint Server 2010 Web site by using Excel Services in Microsoft SharePoint Server 2010.

The fast growing use of data analysis to run a global business often places a strain on an organization's IT department. In the past, IT departments would typically create sets of standardized templates to use for generating reports. The information worker (IW) was forced to rely on the IT department to generate these reports and this often took several days. The latest Microsoft self-service business intelligence tools help remove the IT strain by letting business users analyze data and generate their own reports and analysis data.

Using Excel Services and PowerPivot for Excel 2010 users can import data from a back-end database such as Microsoft SQL Server 2008 R2, Microsoft SQL Server 2008 Analysis Services (SSAS), Microsoft SQL Server 2008 Reporting Services (SSRS), IBM, Oracle, and SAP systems. Additionally, users can import data from multiple Excel 2010 spreadsheets or workbooks, or Microsoft Access 2010 databases.

After importing required data, whether from a back-end data source, data feed, or data file, users can produce reports by using either Excel 2010 or PowerPivot for Excel and then use Excel Services to display this new data on a SharePoint Server 2010 Web site.

In this article:

  • Configure Excel 2010 and PowerPivot for Excel 2010

  • Import external data with Excel 2010 and PowerPivot for Excel 2010

  • Publish imported data on SharePoint Server 2010 using Excel Services

Configure Excel 2010 and PowerPivot for Excel 2010

PowerPivot for Excel 2010 is a free add-in that adds data analysis and strong computational power to Excel 2010. In order to use PowerPivot for Excel 2010 you must install the PowerPivot for Excel 2010 add-in. PowerPivot for Excel 2010 is available to download at Microsoft | PowerPivot.

To install PowerPivot for Excel 2010

  1. If you are running an operating system other than Microsoft Windows 7 or Microsoft Windows Server 2008 R2 you must install .NET Framework 3.5 SP1.

  2. Install Microsoft Office 2010

  3. Install PowerPivot for Excel 2010

    Important

    If you install the 32-bit version of Excel, you must use the 32-bit version of PowerPivot. If you install the 64-bit version of Excel, you must use the 64-bit version of PowerPivot.

    Note

    When you start Excel 2010 for the first time, Excel prompts you for permission to load PowerPivot for Excel 2010.

After installing PowerPivot for Excel 2010, you can click Help or press F1 to access the PowerPivot for Microsoft Excel Online Help. See PowerPivot for Excel (Business Intelligence) for additional help and downloads.

Import external data with Excel 2010 and PowerPivot for Excel 2010

This section describes how to use Excel 2010 and PowerPivot for Excel 2010 to import external data from multiple sources. Both applications will achieve this, but PowerPivot for Excel 2010 supports files up to 2GB and enables you to work with up to 4GB of data in memory. As mentioned previously, users can import data from a back-end database such as Microsoft SQL Server 2008 R2, Microsoft SQL Server 2008 Analysis Services (SSAS), Microsoft SQL Server 2008 Reporting Services (SSRS), IBM, Oracle, and SAP systems. Users can also use both applications to import data from other Excel spreadsheets and workbooks, and Access 2010 desktop and Web databases.

In this section:

  • Import external data with Excel 2010

  • Import external data with PowerPivot for Excel 2010

Import external data with Excel 2010

There are two ways to import data in Excel 2010. The first way is to use the Get External Data feature. The second way uses the Data Connection Wizard to connect to several data sources. These include the following:

  • Microsoft SQL Server

  • Microsoft SQL Server Analysis Services (SSAS)

  • ODBC DSN

  • Microsoft Data Access - OLEDB Provider for Oracle

  • Other/Advanced

To import data with the Get External Data feature

  1. In Excel 2010 open a new workbook.

  2. On the menu bar, click Data.

  3. On the Ribbon, in the Get External Data section, click From Other Sources, and then select the source from which you want to import external data. The list that appears has the following choices:

    • From Access

    • From Web

    • From Text

    • From Other Sources

      • From SQL Server

      • From Analysis Services

      • From XML Data Import

      • From Data Connection Wizard

      • From Microsoft Query

    • Existing Connections

To establish a data source connection with the Data Connection Wizard

  1. In Excel 2010 open a blank workbook.

  2. On the menu bar, click Data, and then click Connections in the Connections group.

  3. In the Workbook Connections window, click Add.

  4. In the Existing Connections window all current connection files are displayed. Click Browse for More if you want a connection that is not listed.

  5. In the Select Data Source window, more connection files are displayed. Click New Source if you have to find a connection that is not listed.

  6. When the Data Connection Wizard appears, the following list of data sources is displayed:

    • Microsoft SQL Server

    • Microsoft SQL Server Analysis Services (SSAS)

    • ODBC DSN

    • Microsoft Data Access - OLEDB Provider for Oracle

    • Other/Advanced

  7. Click one of the data sources and then click Next to start the Wizard.

Import external data with PowerPivot for Excel 2010

Importing external data with PowerPivot for Excel 2010 is the default feature for this Excel 2010 add-in. Various sources can be used to import data. PowerPivot for Excel 2010 resembles Excel 2010 in the way that you can import data and connect to a data source for importing data. The following list shows the many data sources that PowerPivot for Excel 2010 can use:

  • Access databases

  • SQL Server relational databases

  • Oracle relational databases

  • Teradata relational databases

  • Informix relational databases

  • IBM DB2 relational databases

  • Sybase relational databases

  • Other relational databases (OLE DB provider or ODBC driver)

  • Text files

  • Microsoft Excel files

  • PowerPivot workbook

  • Analysis Services cube

  • Data feeds

To import data from a database

  1. In Excel 2010, click PowerPivot on the menu bar, and then click PowerPivot Window in the Launch section.

  2. In PowerPivot for Excel 2010 click From Database in the Get External Data group.

  3. Choose the source that you want from the available list:

    • From SQL Server

    • From Access

    • From Analysis Services or PowerPivot

  4. Complete the wizard.

To import data from a report

  1. In PowerPivot for Excel 2010 click From Report in the Get External Data group.

  2. In the Table Import Wizard, type the name for this connection in the Friendly connection namespace.

  3. Click Browse, and select a Microsoft SQL Server Reporting Services (SSRS) server or URL. Note that if you often use reports on a report server, the server might be listed in Recent Site and Servers. If there are no SQL Server Reporting Services servers or sites listed, type an address to a Reporting Services server or site in the Name space and then click Open and then browse the folders on the server or select the report.

  4. The Table Import Wizard connects to the report and then displays it in the preview area.

  5. Click Next, and select the report that you want to import and then click Open.

  6. The Table Import Wizard then displays the Select Tables and Views window where you can select the tables and views from which you want to import data. If there is only one table in the report that you are importing you can also click Preview & Filter to select the columns that you want to import.

  7. Click Finish when you have selected the data that you want to import and then click Close when the Table Import Wizard displays Success for the import operation.

For more information see, Excel 2010 Help and How-to.

Publish imported data on SharePoint Server 2010 using Excel Services

After you have imported the external data into PowerPivot for Excel 2010 and then saved it as either an Excel spreadsheet or workbook, the next step is to publish it on SharePoint Server 2010.

To publish imported data on SharePoint Server 2010

  1. In Excel 2010, click the File Ribbon, click Save & Send from the menu, and then click Save to SharePoint in the Save & Send section.

  2. In the Recent Locations section, click a location from the list and then click Save As. If you have not already published any workbooks, there are no locations listed in the Recent Locations section. In this case, click Browse for a location in the Locations section and type the URL for the SharePoint Server site in the Save As dialog box and then browse to the site or library where you want to save your spreadsheet or workbook.

    Note

    You must explicitly trust the SharePoint Server library, UNC paths, or HTTP Web sites before you can display any Excel 2010 workbooks in a browser after publishing on a SharePoint Server Web site. For more information see, Plan Excel Services authentication (SharePoint Server 2010) and Manage Excel Services trusted locations (SharePoint Server 2010).

  3. On the Save As dialog box, you can click Publish Options if you only want to publish individual sheets from your workbook, specific items in the workbook, or set parameters so specific cells are editable when the workbook is viewed. On the Publish Options window, you can also select Open with Excel in the browser to immediately display the workbook in a browser after it is published and Save Thumbnail. When you are finished, click Save.

Note that you can also use the Add document feature in the SharePoint Server document library to save files directly. Additionally, if you have WebDAV installed on the SharePoint Server computer you can copy a file to the SharePoint Server farm as a file share, for example \\sharepoint-farm\sute\subsite\doclib.

For more information about how to deploy self-service business intelligence in your organization see the following links: