Using PowerPivot for SharePoint 2013
- 5/15/2013
- A brief history
- When do I use PowerPivot for SharePoint?
- Publishing to SharePoint
- Scheduling data refreshes
- Workbooks as a data source
Workbooks as a data source
You can use a workbook hosted in a SharePoint site only if you install PowerPivot for SharePoint 2013. With this feature, you can designate your workbook as a data source for others. For instance, you can create an Excel 2013 workbook (or other tools like Power View, Panorama, ProClarity, and so on) that uses another Excel 2013 workbook that is hosted in SharePoint 2013 with PowerPivot for SharePoint. In the Data Connection Wizard, in the Server Name text box, provide the URL for the workbook in SharePoint, as demonstrated in the Figure 7-19.
){kind=link}
Figure 7-19 Using a workbook as a data source.
In the Log On Credentials section, choose the authentication method (in the example, we use Windows Authentication) and then click Next. On the Select Database And Table page of the wizard, the workbook is shown as a Model of type Cube, as illustrated in Figure 7-20.
){kind=link}
Figure 7-20 Selecting the data model from the workbook.
The actions to complete creating a connection to a data source are no different from the actions described in Chapter 4, in the section “Creating the Data Model” (see also Figure 4-5).
Monitoring with PowerPivot for SharePoint
The PowerPivot Management Dashboard provides administrators who are responsible for the server side of PowerPivot with the capabilities they need to understand usage patterns of the PowerPivot workbooks in SharePoint and to take appropriate actions. For example, the growing size of a particular workbook might indicate the need to acquire more memory. You can access the PowerPivot Management Dashboard by going to the SharePoint Central Administration page and then clicking General Application Settings, as depicted in the Figure 7-21.
)
Figure 7-21 Accessing the PowerPivot Management Dashboard.
Clicking the PowerPivot Management Dashboard link takes you to the Dashboard page, as shown in Figure 7-22.
The PowerPivot Management Dashboard can be broken down into five main areas (Web Parts) Table 7-2 presents an overview of each area, and detailed descriptions are given in the subsections that follow.
Table 7-2 The PowerPivot Management Dashboard main areas
|
Web Part |
Description |
|
Infrastructure – Server Health |
This section provides information about infrastructure; it shows the CPU and memory usage trends over time. It also contains a histogram of overall query response for the SQL Server Analysis Services in SharePoint mode. |
|
Workbook Activity |
This section provides a high-level representation of the number of users, the number of queries sent to a workbook, and the size of the workbook in time. |
|
Actions |
An administrator can use this section to configure PowerPivot-specific settings within a SharePoint farm. |
|
Data Refresh |
This section provides a breakdown of the recent activities and recent failures for PowerPivot data refresh in SharePoint. |
|
Reports |
An administrator can use this section to view the source Excel workbooks and databases used by the PowerPivot Management Dashboard |
)
Figure 7-22 The PowerPivot Management Dashboard.
Infrastructure – Server Health
This section of the PowerPivot Management Dashboard provides indicators of the server’s health. It does so through the following indicators:
- Query Response Times
- Average Instance CPU
- Average Instance Memory
- Activity
- Performance
Query Response Times
The Query Response Times view is the default view of the Server Health Web Part (see Figure 7-23). The purpose of this chart is to provide a quick overview so that you can determine whether the majority of the queries are running as expected or running too slowly.
)
Figure 7-23 The Query Response Times view.
When query response time increases, you will want to determine which queries are running slowly, and why.
Table 7-3 summarizes the default query response time definitions. These definitions can be modified by selecting Central Administration | General Application Settings | PowerPivot | Configure Service Application Settings.
Table 7-3 Query Response Times category definitions
|
Category |
Definition (in milliseconds) |
|
Trivial |
0 < time < 500 |
|
Quick |
500 < time < 1000 |
|
Expected |
1000 < time < 3000 |
|
Long |
3000 < time < 10000 |
|
Exceeded |
≥10000 |
Average Instance CPU
Switching to the Average Instance CPU view (see Figure 7-24) in the Server Health Web Part shows the CPU load on the SharePoint Application Server on which PowerPivot is installed.
Figure 7-24 shows that for that SharePoint Application Server, the CPU load is not an issue because, on average, it uses less than 30 percent of the CPU capacity.
)
Figure 7-24 The Average Instance CPU view.
Average Instance Memory
Memory can become a concern for your environment because the Analysis Services in SharePoint Mode loads the workbook in memory. As the number of users and the size of their workbooks grow, they require an increasing portion of the server’s memory. Taking a quick look at the Average Instance Memory view, you can easily see when more memory is being used over time, as demonstrated in Figure 7-25.
)
Figure 7-25 The Average Instance Memory view.
Activity and Performance
Although you can toggle between the Infrastructure – Server Health Activity and Performance views, you can get an even better view of this data by using the Workbook Activity And Server Health reports directly. To do that, click in either the Workbook Activity or the Server Health workbook located in the Reports area of the PowerPivot Management Dashboard (refer to Figure 7-22).
Workbook Activity
This area comprises two parts: a Chart and a List.
Chart
This Chart Web Part is a Silverlight control that displays a bubble chart. Figure 7-26 shows that the chart’s axes represent the number of users and the number of queries sent to a workbook. A sliding bar indicates the date. As you move the pointer over each bubble, the name of the workbook it corresponds to and the number of users that are connected are displayed, along with the number of queries sent to the workbook. In addition, as you move the date sliding bar, it shows animation on bubble size, which represents how the size of the workbook is growing over time.
)
Figure 7-26 The Workbook Activity – Chart.
List
The Workbook Activity – List section provides a quick way to view the current activity attributes (workbook name, number of queries, users, and size) of the server, as demonstrated in Figure 7-27.
){kind=link}
Figure 7-27 The Workbook Activity – List.
Data Refresh
The PowerPivot scheduled data-refresh mechanism has many activities that run in the background. In the PowerPivot Management Dashboard, you can find a section dedicated to reporting the recent data refresh–related activities in the environment.
Recent Activity
As the name suggests, this Web Part informs the data-refresh activity in this environment. It reports the most recent PowerPivot workbook data refreshes along with the time that it completed the refresh and its duration, as shown in Figure 7-28.
){kind=link}
Figure 7-28 The Data refresh – Recent Activity Web Part.
Clicking one of the workbooks in the Recent Activity report redirects you to that workbook’s data refresh history page, on which you can find details related to the failure. Figure 7-29 shows the data refresh history page for the AdventureWorks2013_Bikes2.xlsx workbook.
)
Figure 7-29 The Data Refresh History page.
Recent Failures
This Web Part focuses on reporting recent data refresh failures. With this information in hand, you can go back to Recent Activity Web Part and begin investigating the reason why a particular data refresh failed.
Reports
As shown in Figure 7-30, the Reports Web Part contains the Excel workbooks that are the source for the PowerPivot Management Dashboard charts. Clicking one of the workbooks opens that workbook in the browser, and you can identify the charts shown in the PowerPivot Management Dashboard.
){kind=link}
Figure 7-30 The Reports Web Part.
Summary
This chapter briefly introduced you to PowerPivot for SharePoint. It demonstrated how to publish a PowerPivot workbook to SharePoint and how to schedule data refreshes, and it explained how IT professionals can manage PowerPivot for SharePoint by using the PowerPivot Management Dashboard.
To learn more about PowerPivot, you can look for books dedicated to PowerPivot for Excel and to PowerPivot for SharePoint. You can also find more information by referencing the following resources:
- The official MSDN blog at http://blogs.msdn.com/b/analysisservices/.
- Rob Collie’s blog at http://www.powerpivotpro.com (for PowerPivot for Excel). Rob Collie was a Program Manager in the Analysis Services team that worked on PowerPivot for Excel.
- Dave Wickert’s blog at http://www.powerpivotgeek.com (for PowerPivot for SharePoint). Dave Wickert is a Program Manager on the Analysis Services team, working on PowerPivot for SharePoint.