Author: Ankur Goyal, Microsoft Analysis Service Test team
Part 1: Getting Started with Usage
1. When components within the various PowerPivot components decide to log “event” they call into the SharePoint Usage system APIs and Usage files are create to collect these events. This is a very high-throughput, low-overhead process since it is based on the OS Eventing system. Usage data files (.usage) gets collected in the SharePoint log directory (system drive->\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS). And by default .usage files get created every 30 minutes. To change the 30 minute interval, see below or a posting here: http://powerpivotgeek.com/2010/01/18/how-to-manually-update-your-powerpivot-management-dashboard/ (for a general discussion around manually updating the dashboard).
2. Usage configuration and collection settings are on the Central Administration site (go to central admin site -> Monitoring -> Reporting ->Configure usage and health data collection). As a default we collect PowerPivot query usage, load, unload, and connection events. This settings page also list the directory name where the .usage files are getting store and the SharePoint logging database server and database name.
3. There are two SharePoint timer jobs, which are required to run in order to move usage data from .usage files to the PowerPivot database. These timer are located on the Central Administration site (go to Central Admin site -> Monitoring -> Review job definitions, and click on View All:
- Usage Data Import timer job: This timer moves .usage files from the log directory to the centralized SharePoint logging database and it runs every 30 minutes as a default.
- Usage Data Processing timer job: This timer job moves usage data from the SharePoint logging database to the PowerPivot database and it runs once every day in the middle of the night between 1 -3 AM.
4. Finally, as part of the Usage Data Processing timer job, Usage data gets collected into the PowerPivot database in the respective usage tables, as shown below:
Part 2: Troubleshooting Usage
1. In order to verify whether Usage data collection process is working properly, we first need to change .usage collection time from default 30 minutes to 1 minute. Here are the steps that we need to follow to make this change:
a. Open SharePoint PowerShell window in the administrative mode (ProgramFiles->MicrosoftSharePoint 2010 Products->SharePoint 2010 Management Shell)
b. Then type the following cmdlet and hit enter:
Set-SPUsageService –UsageLogCutTime 1
c. Now you can go to the Log directory (system drive->\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS) and make sure that the .usage files are getting generated every minute.
2. Now we will generate some usage data by uploading a PowerPivot workbook on SharePoint site and connecting to it from SSMS (SQL Server Management Studio). Upload a workbook to SharePoint site and connect to it from SSMS using server type as Analysis Services. Please make sure that workbook url look like:
3. Once you are connected to the workbook, expand nodes and browse the cube in SSMS. This will generate some Usage data.
4. You can also verify the size of the .usage file will increase (go to %CommonProgramFiles%\Microsoft Shared\Web Server Extensions\14\LOGS\” folder).
5. Now we need to run two Timer jobs in order to move the usage data from .usage files to the PowerPivot database: (go to central admin site -> Monitoring -> Review job definitions, and click on View All.
a. First run the Data Import timer job. If this timer job fails to run please open the corresponding ULS log and looks for the error\exception generated by Usage Data Import timer job.
b. After it, run the Data Processing timer job. If this timer job fails to run please open the corresponding ULS log and looks for the error\exception generated by Usage Data Processing timer job.
6. Verify the rows in the PowerPivot database, by running the following select statements against the default PowerPivot db:
SELECT * FROM Usage.Loads
SELECT * FROM Usage.Connections
SELECT * FROM Usage.Requests
It will show you some rows in the respective tables
7. Please note: if the system drive where the ULS\usage data is getting collected runs out of space, ULS logs and .usage files will stop generating.
So, please make sure that you have enough space on the hard drive to store ULS logs and .usage files. Also make sure that SharePoint Tracing service is running in order to generate ULS logs.
Part 3: Getting Started with PowerPivot Management Dashboard
1. You can access the PowerPivot Management Dashboard page from the central admin site (go to central admin site -> General Application Settings -> PowerPivot -> Management Dashboard). The purpose of dashboard is to show usage information (as shown above) from PowerPivot database in a most useful way to the IT users.
2. The starting point of the PowerPivot Management is shown below. Dashboard has seven different web parts and each web part shows results\charts on usage information.
|Web part #1||Displays the CPU and memory usage for the PowerPivot Analysis Services service and the overall query response histogram for the service application.|
|Web part #2||Contains links to other pages in Central Administrations.|
|Web part #3||Bubble chart that shows report on frequency of data access. You can learn how many users are connected to PowerPivot data sources and how many queries were sent on a daily or weekly basis.|
|Web part #4||A list view that shows report on frequency of data access. You can learn how many users are connected to PowerPivot data sources and how many queries were sent on a daily or weekly basis.|
|Web part #5||Reports on the status of data refresh jobs, including jobs that failed to run.|
|Web part #6||List data sources and their respective PowerPivot workbooks that failed on data refresh.|
|Web part #7||Links to other reports and a data connection file (ODC) that links to PowerPivot management data. You can add your own workbook to this web part and it will show up in our dashboard. This is how you extend the dashboard so it contains your own custom Usage reports|
Please note: the very first time when you open the dashboard page, all the web parts shows an empty report because the PowerPivot Management Dashboard has not captured the usage data from the PowerPivot database yet.
3. Once usage data is collected into the PowerPivot database (as describe in the section above), we need to run “PowerPivot Management Dashboard Processing Timer Job” to show reports on the dashboard.
4. In order to run PowerPivot Management Dashboard Processing timer job, go to Actions web part on the dashboard and click on the Review timer job definitions link.
On the Job Definition page, now click on the PowerPivot Management Dashboard Processing Timer Job and click on the Run now button. As a default this timer job only runs once a day in the middle of the night after usage processing timer job gets done.
5. Under Timer Links section, please click on the Running jobs link and make sure that the PowerPivot Management Dashboard Processing Timer Job is running.
6. Once dashboard processing timer job finished, it process all the usage data into a PowerPivot cube, which is stored on the Central Admin site (Go to Site Actions -> View All Site Content -> under Document Libraries click PowerPivot Management folder).
Click on the guid folder and you will see “PowerPivot Management Data” workbook which contain the cube that has all the necessary usage data to show the required web parts on the dashboard page. (further details of this workbook is described in the troubleshooting section below)
7. After the timer job finishes running, go back to the SP central administration home page, and go back to the management dashboard page. Now you will see the same dashboard page with most of the charts and reports. You can also select other types of reports from the drop down (highlighted below) to see CPU and memory usage.
8. All the web parts (except the Data Refresh Activity and Failures) on the dashboard get the results from the “PowerPivot Management Data” workbook, however data refresh web parts get the results directly from the SQL database, where other usage data is stored.
Part 4: Troubleshooting PowerPivot Management Dashboard
1. When you open PowerPivot Management Dashboard first time, you might not even see the empty “Workbook Activity –Chart” web part if you haven’t installed Silverlight 3.0, you can install it now by going to http://silverlight.net to download the Silverlight 3.0 runtime for windows. Install it and refresh the page.
2. You might get a user credential delegation error on the Server Health web part (as shown below). It is a known ECS limitation if the workbook accessing the dashboard report is in a different zone or web application than the default Central Admin web application.
3. In past we had a bug that prevented to load “Workbook Activity –Chart” on Glob machines (ex: German OS running English product), this is a Silverlight chart that shows activities of different PowerPivot workbooks. In order to make sure the error, please check the corresponding ULS log by search the “bubble chart” string.
4. As described before, PowerPivot Management Dashboard Processing timer job process the usage data and store it in a PowerPivot cube (“PowerPivot Management Data” workbook) on Central Admin.
5. Under “Reports” you can see Server Health and Workbook Activity workbooks, which can be opened in the browser and they will show you the same reports\charts that you see in the Server Health and Workbook Activity web part.