Self-Service Analysis with PowerPivot

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

Many business intelligence (BI) solutions require access to centralized, cleansed data in a data warehouse, and there are many good reasons for an organization to continue to maintain a data warehouse for these solutions. There are even self-service tools available that allow users to build ad hoc reports from this data. But for a variety of reasons, business users cannot limit their analyses to data that comes from the corporate data warehouse. In fact, their analyses often require data that will never be part of the data warehouse, such as miscellaneous spreadsheets or text files prepared for specific needs or data obtained from third parties that might be used only once.

Users can spend a great deal of time gathering data from disparate sources and then manually consolidating and integrating the data in the form of one or more Microsoft Excel workbooks. PivotTables and PivotCharts are popular tools for performing analyses, but Excel requires all the data for these objects to be consolidated first into a single table or to be available in the form of a cube in a SQL Server Analysis Services database. What does the user do when the insight is so useful that the spreadsheet needs to be shared with others on a frequent basis with fresh data?

Sometime users are also constrained by the volume of data that they want to analyze. Excel 2007 can support one million rows of data, but what if the user has data that is more than a million rows? These users need a tool that enables them to analyze huge sets of data without dependence on IT support.

Microsoft SQL Server 2008 R2 comes to the rescue for these users with two new features to meet these needs-SQL Server PowerPivot for Excel 2010 and SQL Server PowerPivot for SharePoint 2010. PowerPivot for Excel gives analysts a way to integrate large volumes of data outside of a corporate data warehouse, whether they are creating reports to support decision making or prototyping solutions that will eventually be part of a larger BI implementation. To provide multiple users with centralized access to reports developed with PowerPivot for Excel, information technology staff can implement PowerPivot for SharePoint. This server-side PowerPivot product provides the necessary infrastructure to manage, secure, refresh, and monitor these PowerPivot reports efficiently.

PowerPivot for Excel

PowerPivot for Excel is an add-in that extends the functionality of Excel 2010 to support analysis of large, related datasets on your computer. After installing the add-in, you can import data from external data sources and integrate it with local files, and then develop the presentation objects, all within the Excel environment. You save all your work in a single file that is easy to manage and share.

The PowerPivot Add-in for Excel

To create your own PowerPivot workbooks or to edit workbooks that others have created, you must first install the PowerPivot add-in for Excel 2010.

Modifications to Excel

When you install the add-in, several changes are made to Excel. First, the installation adds the PowerPivot menu to the Excel ribbon. Second, it adds the PowerPivot window, a design environment for working with PowerPivot data within Excel. You can use this design environment to import millions of rows of data, which you can later view as summarized results in Excel worksheets.

When you are ready to create a PowerPivot workbook, you click the PowerPivot tab on the Excel ribbon and click the PowerPivot Window button in the Launch group (shown in Figure 10-1) to open the PowerPivot window. The PowerPivot window opens separately from the Excel window, which allows you to switch back and forth as necessary between working with your PowerPivot data and working with the presentation of that data in Excel worksheets.

FIGURE 10-1 The PowerPivot Window button in the Excel window

The Local Analysis Services Engine

The add-in also installs a local Analysis Services engine on your computer. Installation also adds the client providers necessary for connecting to Analysis Services. PowerPivot uses the Analysis Services engine to compress and process large volumes of data, which Analysis Services loads into workbook objects.

The Analysis Services engine runs exclusively in-process in Excel, which means that there is no need to manage a separate Windows service running on your computer. This version of Analysis Services uses the new VertiPaq storage mode, which works efficiently with large volumes of columnar data in memory. For example, VertiPaq mode allows you to very quickly sort and filter millions of rows of data. Furthermore, you can store workbooks on your local drive because VertiPaq compresses the data by tenfold on average.

The Atom Data Feed Provider

Last, the add-in installs an Atom data feed provider to allow you to import data from Atom data feeds into a PowerPivot workbook. A data feed provides data to a client application on request. The structure remains the same each time you request data, but the data can change between requests. Usually, you identify the online data source as a URL-addressable HTTP endpoint. The online data source, or data service, responds to requests at this endpoint by returning an atomsvs document that describes how to retrieve the data feed. When you open an atomsvc document, the PowerPivot Atom data feed provider detects the file type and prompts you to load data into PowerPivot. When you confirm the load operation, the provider connects to the data service, which in turn encapsulates the data in XML by using the Atom 1.0 format and sends the data to the provider.

Data Sources

Your first step in the process of developing a PowerPivot workbook is to create data sources and import data into the workbook. You can import data from a variety of external data sources, including relational or multidimensional databases, text files, and Web services. You can also import data by linking to tables in Excel, or simply by copying and pasting data. Each data source that you add to the workbook becomes a separate table.

External Data

When your data comes from an external data source, you use the applicable button in the Get External Data group of the ribbon in the PowerPivot window, as shown in Figure 10-2. The button you choose launches the Table Import Wizard for the type of data that you are importing.

FIGURE 10-2 The Get External Data group in the PowerPivot window

You can choose from a wide variety of data sources:

Databases

• SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and Windows Azure
• Microsoft Office Access 2003, Access 2007, and Access 2010
• SQL Server 2005 Analysis Services, SQL Server 2008 Analysis Services, and SQL Server 2008 R2 Analysis Services
• Oracle 9i, Oracle 10g, and Oracle 11g
• Teradata V2R6 and Teradata V12
• Informix
• IBM DB2 8.1
• Sybase
• Any database that can be accessed by using an OLE DB provider or an ODBC driver

Files

• Delimited text files (.txt, .tab, and .csv)
• Files from Excel 97 through Excel 2010
• PowerPivot workbooks published to a PowerPivot-enabled Microsoft SharePoint
  Server 2010 farm

Data feeds

• SQL Server 2008 R2 Reporting Services Atom data feeds
• SharePoint lists
• ADO.NET Data Services
• Commercial datasets, such as Microsoft Codename "Dallas" (http://pinpoint.com/en-US/Dallas)

TIP A new feature in SQL Server 2008 R2 Reporting Services is the ability to export an Atom data feed for any report, whether you export from a native mode or from an integrated mode report server. If the PowerPivot client is installed on your computer when you perform the export, PowerPivot detects the document type and opens a wizard for you to use to import the data directly into a table. You might find it beneficial to get some of your data integrated in a report first and take advantage of Reporting Services' support for calculations, aggregations, data sources, and refresh schedules before you bring the data into PowerPivot.

The wizard walks you through the process of specifying connection information for the source and selecting data to import. If your source is a database, you can choose to select either tables or views or to provide a query for the data selection. Regardless of the data source type, the wizard gives you two options for filtering the data before you import it. First, you can select specific columns rather than importing every column from the source table. Second, you can apply a filter to a column to select the row values to include in the import. By applying these filtering options, you can eliminate unnecessary overhead in your workbook, reducing both the file size of the workbook and the amount of time necessary to refresh and recalculate the workbook.

TIP When you are working with large datasets, you should use the filtering options to import only the columns you need for analysis. By limiting the workbook to the essential columns, you can import more rows of data.

Linked Tables

If your data is in an Excel table already, or if you convert a range of data into an Excel table, you can add the table to your workbook in the Excel window and then use the Create Linked Table button to import the data into the PowerPivot window. You can find this button on the PowerPivot ribbon in the Excel window, as shown in Figure 10-3. After the data is available in the PowerPivot window, you can then enhance it by defining relationships with other tables or by adding calculations.

FIGURE 10-3 The Create Linked Table button

One of the benefits of using an Excel table as a source for a PowerPivot table is the ability to change the data in the Excel table to immediately update the PowerPivot table. Because you cannot make changes to data in the PowerPivot window, a linked table is the quickest and easiest way to edit the data in a PowerPivot table.It is also a great way to try out different values in "what-if" scenarios or to use variable values in a calculation.

Another reason you might consider using a linked table is to support Time Intelligence functions in PowerPivot's formula language. Examples of Time Intelligence functions include TotalMTD, StartOfYear, and PreviousQuarter. Often, source data includes dates and times but does not have the corresponding attributes to describe these dates and times, such as month, quarter, or year. You can create your own table in Excel with the necessary attributes, link it to PowerPivot, and then use Time Intelligence functions to support analysis involving comparative time periods.

Copying and Pasting

If you do not need to change data after importing into PowerPivot, you can copy the data from another Excel workbook and then in the PowerPivot window, click the Paste button in the Clipboard group of the PowerPivot ribbon. The Paste preview dialog box displays to shows the data to be pasted into PowerPivot. Although you cannot directly edit the data after adding it to PowerPivot, you can replace it by pasting in fresh data or add to it by appending additional data. To do this, you use the Paste Replace or Paste Append button, respectively.

Data Preparation

After importing data into tables, your next step is to prepare the data for analysis by defining relationships between tables. You can also choose to enhance the data by applying filters and modifying column properties.

Relationships

By building relationships between the data, you can analyze the data as if it all came from a common source. Relationships enable you to use related data in the same PivotTable even though the underlying data actually comes from different sources. Defining relationship between columns into two PowerPivot tables is similar to defining a foreign key relationship between two columns in a relational database. Excel power users can understand defining relationships as analogous to using the VLOOKUP function to reference data elsewhere. In addition to consolidating data for PivotTables, there are other benefits of building relationships. You can filter data in a table based on data found in related columns, or you can use the formula language to perform a lookup of values in a related column. These techniques provide alternative ways to eliminate data redundancy, which keeps the workbook smaller.

When you import related tables at the same time, the Table Import Wizard automatically detects that they are related and creates the detected relationships. You can also manually create relationships by using the Create Relationship button on the Design tab of the Power- Pivot ribbon, as shown in Figure 10-4.

NOTE A column cannot participate in more than one relationship, and you cannot create circular relationships.

FIGURE 10-4 The Create Relationship button

Filters

After you import data into PowerPivot, you cannot delete rows from the resulting PowerPivot table. To keep your workbook as small as possible, you should apply filters during the import process to exclude unneeded rows right away. After completing the import, you can modify the table properties to add a filter, and then update the table to keep only rows that meet the filter criteria.

You can also apply filters to the imported data if you want the data to be available for other purposes later, while hiding specific rows from the presentation layer in the current report. You can filter by name in the same way that you normally filter in Excel, by selecting from a list of values in a column to identify the rows that you want to keep. As an alternative, you can filter a numeric column by value, as shown in Figure 10-5. For example, you can use the Between operator to apply a filter that will select rows with a value in a range that you specify.

FIGURE 10-5 Filtering a numeric column by value

IMPORTANT Use of a filter is not a security measure. Although a filter effectively hides data from a presentation, anyone who can open the Excel workbook can also clear the filters and view the data if he or she has installed the PowerPivot add-in.

Columns

As part of the data preparation process, you might need to make changes to column properties. On the Home tab of the PowerPivot ribbon, you can access tools to make some of these changes, as shown in Figure 10-6. For example, you can select a column in the table and then use the ribbon buttons to change the formatting of the column. You can also change the width of the column for better viewing of its contents, or you can freeze a column to make it easier to explore the data as you scroll horizontally.

FIGURE 10-6 The Home tab of the PowerPivot ribbon

Although the Table Import Wizard detects and sets column data types, you can use the Data Type drop-down list on the ribbon to change a data type if necessary. You might need to adjust data types to create a relationship between two tables, for example. PowerPivot supports only the following data types:

• Currency
• Decimal Number
• Text
• TRUE/FALSE
• Whole Number

You can use the Hide and Unhide button on the Design tab (shown in Figure 10-4) to control the appearance of a column in the PowerPivot window and also in the PivotTable Field List. For example, you might choose to display a column in the PowerPivot window, but hide that column in the PivotTable window because you want to use it in a formula for a calculated column.

PowerPivot Reports

A PowerPivot report is an Excel worksheet that presents your PowerPivot data in a summarized form by using at least one PivotTable or PivotChart. You can convert a PivotTable to a collection of cube function formulas if you prefer a free-form layout of your PowerPivot data. Regardless of which layout you choose for the report, you can add slicers to support interactive filtering.

PivotTables

You create a report by selecting a layout template from the PivotTable menu (available from the PivotTable button on the PowerPivot ribbon, as shown in Figure 10-7) and specifying a target worksheet in the Excel workbook. You can create a layout independently of the available templates by selecting Single PivotTable or Single PivotChart as many times as you need and targeting a different location on the same worksheet for each object.

FIGURE 10-7 Report layout templates

NOTE The standard Excel ribbon also includes buttons for building a PivotTable or PivotChart, but you must use the buttons on the PowerPivot ribbon when you want to use PowerPivot data.

Assume that you select the Chart And Table (Horizontal) template. Placeholders for the chart and table appear on the worksheet, and a new worksheet appears in the workbook to store the data that you selected for the chart. Just as you do with a standard PivotTable or PivotChart, you select the placeholder and then use the associated field list to select and arrange fields for the selected object, as shown in Figure 10-8.

FIGURE 10-8 A PivotChart and PivotTable report

Cube Functions

As an alternative to the symmetrical layout of a PivotTable, you can use cube functions in cell formulas to arrange PowerPivot data in a free-form arrangement of cells. Cube functions, introduced in Excel 2007, allow you to query an Analysis Services database and return metadata or values from a cube. Because PowerPivot creates an in-memory version of an Analysis Services database, you can also use cube functions with your PowerPivot data.

Although you can create a formula that uses a cube function in any cell in your PowerPivot workbook, the simplest way to get started with these functions is to convert an existing Pivot- Table. To do this, click the OLAP Tools button on the Options tab under PivotTable Tools, and click Convert To Formulas. The conversion replaces the row and column labels with a formula using the CUBEMEMBER function and replaces values with the CUBEVALUE function, as shown in Figure 10-9. The first argument of either of these functions references the data connection, which by default is Sandbox for embedded PowerPivot data. All other arguments are pointers to dimension member names that define the coordinates of the value to retrieve from the in-memory cube.

FIGURE 10-9 The CUBEVALUE function

Slicers

The task pane for PowerPivot is similar to the one you use for an Excel PivotTable, but it includes two additional drop zones for slicers. Slicers are a new feature in Excel 2010 that can be associated with PowerPivot. Slices work much like report filters but link to multiple objects, such as a PivotTable and a PivotChart, so that the slicer selection can filter an entire report. If two slicers are related, a selection of items in one slicer automatically highlights and filters the related items in the second slicer. For example, if you select a year in one slicer, the quarters related to that year in a second slicer will also be selected, as shown in Figure 10-10.

FIGURE 10-10 Selecting Year slicer values also selects QuarterCode slicer values.

Data Analysis Expressions

The ability to combine data from multiple sources into a single PivotTable is amazingly powerful, but you can create even more powerful reports by enriching the PowerPivot data with Data Analysis Expressions (DAX) to add custom aggregations, calculations, and filters to your report. DAX is a new expression language for use with PowerPivot for Excel. DAX formulas are similar to Excel formulas. However, rather than working with cells, ranges, or arrays as in Excel, DAX works only with tables and columns. You can use DAX either to create calculated columns or to create new measures.

Calculated Columns

A calculated column is the set of values resulting from an expression that you apply to a table column or another calculated column. For example, you can concatenate values from two separate columns to produce a single string value that displays in a third column. You can also perform mathematical operations, manipulate strings, look up values in related tables, or compare values to produce results in a calculated column. To add a calculated column, click an empty cell under the Add Column column heading and type an expression in the formula bar. In your report, you can use the new calculated column just like any other column from your PowerPivot data. An expression that calculates gross profit looks like this:

=[Sales Amount]-[Total Product Cost]

Measures

A measure is a dynamic calculation that is displayed in the value area of the PivotTable. Its value depends on the current selection of items in rows and columns and in the report filter. A measure differs from a calculated column in that the calculated column values persist in the PowerPivot data whereas the measure values calculate at query time and do not persist in the data store. The calculated column values are scalar, and the measure values are aggregates. Last, a calculated column may contain string values or numeric values, but a measure is always a numeric value.

As an example, consider a calculated column that shows gross profit. The PowerPivot table would include a gross profit value for each sales transaction, which a PivotTable can later aggregate. However, if you create a calculated column to store a gross profit margin percentage value, the aggregate in the PivotTable will not be correct because percentage values are not additive.

To create a measure, you must first create a PivotTable or PivotChart. In the Excel window, select the PivotTable or PivotChart, and then click the New Measure button on the Power- Pivot tab of the ribbon. You then provide a name for the measure for all PivotTables in the

report, provide a name for the current PivotTable if you want, and then specify the formula for the measure, as shown in Figure 10-11.

FIGURE 10-11 Measure settings

DAX Functions

The examples shown for a calculated column and a measure are very basic, although representative of the common ways that you would use DAX. Table 10-1 lists the types of functions that DAX provides:

TABLE 10-1 DAX Function Types



PowerPivot for SharePoint

PowerPivot for SharePoint provides server-side support for PowerPivot workbooks by extending the capabilities of SharePoint and Excel Services in SharePoint. SharePoint provides centralized management of the PowerPivot workbooks, and Excel Services manages data queries and the rendering of the query results in the browser. Installation of PowerPivot for SharePoint adds services to the SharePoint farm and includes a document library template, content types, dashboards, and Web parts that provide access to PowerPivot reports and support monitoring their usage.

Architecture

PowerPivot for SharePoint requires SharePoint Enterprise Edition and Excel Services. You must install Analysis Services with SharePoint Integration on a SharePoint Web front end. In SharePoint Central Administration, you configure the PowerPivot System Service and activate the PowerPivot feature on the target site collection. PowerPivot for SharePoint uses a scalable architecture (shown in Figure 10-12) that allows you to add or remove instances as needed when you require more or less processing capacity. When you add an instance, the Share- Point autodiscovery feature ensures that the new instance can be found, and the PowerPivot System Service has a load balancing feature that will use the new instance when possible.



FIGURE 10-12 PowerPivot for SharePoint Architecture

Analysis Services in VertiPaq Mode

To support users without the PowerPivot for Excel client, Excel Services connects to a server instance of Analysis Services in VertiPaq mode to process PowerPivot workbooks and respond to user queries. This type of Analysis Services server instance enables in-memory data storage on a large scale for multiple users and provides rapid processing of large PowerPivot data sets. Just like the in-memory version of VertiPaq mode on the client, the server version uses data compression and columnar storage. Unlike a standard Analysis Services instance that you manage using SQL Server Management Studio, you manage Analysis Services in VertiPaq mode exclusively in SharePoint Central Administration.

In response to requests for PowerPivot data, Analysis Services loads the cube into memory where it stays until no longer required or until SharePoint monitoring detects that contention for resources has reached a threshold requiring action. You can monitor system performance through usage data, as explained later in this chapter. Analysis Services loads the PowerPivot data from the workbook as raw, unaggregated data into the cube, compresses the data, and dynamically restructures the data based on the user's actions.

The PowerPivot System Service

The PowerPivot service runs as a service application on SharePoint called PowerPivot System Service. A service application is configurable independently of other service applications and isolates service application data. You can install one physical instance of a server but then create multiple service applications to isolate data at the application level. Another benefit of the service application model is the ability to delegate administration. The PowerPivot System Service listens for requests for PowerPivot data, connects to Analysis Services to manage the loading and unloading of PowerPivot data, collects usage data, and monitors system health and availability of Analysis Services servers. It also provides load balancing across servers for query processing if multiple servers are available. Furthermore, the PowerPivot System Service manages the connections for active, reusable, and cached connections to PowerPivot workbooks, as well as administrative connections to other Power- Pivot System Services on the SharePoint farm.

To speed up access to data, the PowerPivot System Service caches a local copy of a workbook and stores it in Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\ Backup. The service unloads this copy of the workbook from memory if no one has accessed the workbook after 48 hours and deletes it from the folder after an additional 72 hours of inactivity. If a user updates the workbook in SharePoint and a copy of the workbook already exists in the cache, the PowerPivot System Service also removes the older cache copy.

The PowerPivot Database

Each service application has its own relational database, called the PowerPivot database. In particular, this PowerPivot database stores the load or cache status of workbooks, server usage information, and schedule information for data refresh operations. More specifically, the application database stores an instance map that identifies whether a workbook is currently loaded on the server or in the cache. Usage information in the application database applies to connections, query response times, load and unload events, and other information pertinent to server health statistics. The data refresh schedule information includes details about data sources, users, and the workbooks associated with a schedule. None of the workbook content is in the PowerPivot database. Instead, workbooks are stored in the SharePoint content database.

The PowerPivot Web Service

The PowerPivot Web Service is a thin middle-tier connection manager implemented as a Windows Communication Foundation (WCF) Web service that runs on a SharePoint Web front end. The Web service listens on the port assigned to a Web application enabled for PowerPivot, and responds to requests by coordinating the request-response exchange between client applications and PowerPivot for SharePoint instances in the farm. This Web service requires no separate configuration or management.

The PowerPivot Managed Extension

The PowerPivot Managed Extension is an assembly in the Analysis Services OLE DB provider client. This provider client is installed on a client computer when you install the PowerPivot for Excel add-in, and on the SharePoint server when you install PowerPivot for SharePoint. For managed connections, the Web service and the managed extension operate the same way. The query processing request determines which one is used.

Content Management

Content management for PowerPivot is quite simple because the data and the presentation layout are kept in the same document. If they weren't, you would have to maintain separate files in different formats and then manually integrate them each time one of the files required replacement with fresh data. By storing the PowerPivot workbooks in SharePoint, you can reap the benefits applicable to any content type, such as workflows, retention policies, and versioning. For example, you can copy data to a new location by copying the document. Or if you need to formally approve data before allowing others to access it, you can easily set up a document approval workflow.

The PowerPivot Gallery

The PowerPivot Gallery is a special type of document library that provides document management capabilities for PowerPivot workbooks. You can use it to preview and open Power- Pivot workbooks from a central location. In the PowerPivot Gallery, shown in Figure 10-13, you can see all available sheets in the workbook as thumbnails with current data, without opening the workbook. A snapshot service creates the thumbnail images by periodically reading the workbooks file.

FIGURE 10-13 The PowerPivot Gallery

In addition to the default Gallery view, the PowerPivot Gallery also includes the Theater and Carousel views, which are most useful when you want to highlight a small number of workbooks. In Theater view, you can see a central preview area, and thumbnails of the other reports in the workbook display at the bottom of the page. In Carousel view, the thumbnails appear to the left and right of the preview area. In either of these views, you can click the left

or right arrow to bring a different thumbnail into the preview area. You can also switch to All Documents view, which allows you to see all the workbooks in a standard document library view. You can then download a document, check documents in or out, or perform any other activity that is permissible within a document library.

The Data Feed Library

A special type of document library is available for the storage of Atom svc documents, also known as data service documents. You can share these documents for the use of other PowerPivot authors who want to import data feeds into PowerPivot tables. You can create a data service document in the document library by specifying the URL request to the data service or Web application that serves data on request. The URL request should include a parameter that requests data in the Atom 1.0 format.

Data Refresh

In addition to the content management support, another good reason to share a PowerPivot workbook in SharePoint is to manage the data refresh process. Usually, data that appears in a PowerPivot table changes from time to time. To keep the workbook up to date and relevant, you must periodically update the data. You can automate this process by assigning a refresh schedule to each data source in the workbook.

The data refresh feature is not enabled by default. When you enable data refresh, a timer job runs every minute on the PowerPivot server. This job is a trigger for the PowerPivot System service, which in turn reads the predefined schedule found in the PowerPivot database. When a schedule to run is found, the PowerPivot System Service gets the list of data sources and the credentials to use, and initiates the data refresh. If the workbook is not checked out or in edit mode, the data refresh job saves the new data to the workbook.

Linked Documents

Your PowerPivot workbook can be used as a data source for other report types. When viewing the workbooks in the PowerPivot Gallery, you can use the Create Linked Document button to create either a Reporting Services report or a PowerPivot report in Excel. You must have the appropriate client application for the report type that you choose. That is, to build a Reporting Services report, you must first install SQL Server 2008 R2 Report Builder 3.0, and to build a PowerPivot report, you must install the PowerPivot for Excel add-in. The query designer in Report Builder and the Field List in Excel display only the fields presented in the source workbook rather than all fields available in that workbook's embedded data.

The PowerPivot Web Service

Another way to use a PowerPivot workbook as a data source is by using the PowerPivot Web Service to connect to the embedded data. That way, you can reuse the data in multiple places without having to duplicate all the effort required to create the initial workbook. Any client application that can connect to Analysis Services directly can use the PowerPivot Web Service. You simply use the SharePoint URL for the workbook instead of an Analysis Services server name in the connection string of the provider. For example, if you have a workbook named Bike Sales.xlsx in the PowerPivot Gallery located at http://<servername>/PowerPivot Gallery, the SharePoint URL to use as an Analysis Services data source is http://<servername>/PowerPivot Gallery/Bike Sales.xlsx.

The PowerPivot Management Dashboard

PowerPivot for SharePoint includes several tools for configuring the service application and for monitoring usage in a management dashboard. All management tools are accessible to farm and service administrators in Central Administration. The easiest way to access settings related to PowerPoint for SharePoint is to use the PowerPivot Management Dashboard. The PowerPivot Management Dashboard displays data for one service application at a time. In this dashboard, you can see a collection of Web parts and PowerPivot reports that display data that is collected daily from multiple sources. One of the Web parts displays a chart showing CPU and memory usage over time to help you determine whether the server is running at maximum capacity or whether it is underutilized. Another Web part shows trending of query response times, which you can use to determine whether queries are responding within configurable thresholds. The dashboard page includes links to the PowerPivot reports that provide the source data for these Web parts. These reports consist of data from an internal reporting database that in turn collects data from the PowerPivot database, SharePoint usage log data, and other sources. You can build new reports using this internal reporting database as a source, but you cannot change it. In addition to giving you information about the state of the server, the dashboard also provides insight into the usage of published workbooks. An interactive chart allows you to monitor which workbooks users access most frequently and which workbooks have recent activity. You can view this information at the daily or weekly level.

One section of the dashboard provides information about data refresh activity, providing a single location from which you can verify whether data refreshes are occurring as scheduled. One Web part in this section lists recent activity for data refresh jobs by workbook and also includes the job duration. Another Web part lists the workbooks for which the data refresh job fails, and displays the data refresh error message as a tooltip.

The dashboard is also extensible. It includes a link to add new items, which you can use to add more workbooks to access from the dashboard page. For example, you can create a new PowerPivot workbook by using the Usage workbook as a data source, and then upload your workbook to the same document library.

Last, the dashboard page includes links to pages in Central Administration that you can use to check or reconfigure the settings for PowerPivot. One link takes you to the service settings page, where you can schedule database timeouts, data refresh hours, and query response time thresholds. You can use another link to review timer job settings for data refresh, dashboard processing, PowerPivot configuration, and the health statistics collector. A third link takes you to the settings page for usage log collection.