Get insight to SharePoint PowerPivot usage without Central admin rights using custom SSRS reports

I just love the PowerPivot management dashboard, I think it’s a great dashboard and shows you all kind of information you need to maintain your PowerPivot environment. Unfortunately the management dashboard is placed inside the Central admin.You need access to the central admin  to be able to visit the PowerPivot management page, this means you also have access to all the other SharePoint admin pages.

I can understand your sharepoint admins aren’t too happy when your BI department is running around in your central admin.

To solve this have I have created a report based on the data inside the PowerPivot Management Dashboard data, that you can use without having the credentials. For more information check Dave Wickerts blog post: Customizing the PowerPivot Management Dashboard (too bad i had finished my report when this article was released )

The report will show you the usage of reports by users, nr of queries and load in the last 7 days and in the last 30 days. This way you can quickly identify which workbooks is being used a lot and you need to take a look at as BI department.

The data is placed inside a PowerPivot file which is stored at Central Administration site (Go to Site Actions -> View All Site Content -> under Document Libraries click PowerPivot Management folder). The file will be placed inside the directory with the name of a guid which you see there. Inside this folder you find “PowerPivot Management Data.xlsx” which is the PowerPivot file which contains the PowerPivot usage data.

We are going to create a user in the AD and give this user access to the central admin and use this to run our report with:

One thing to keep in mind, you need to use a reporting services that is integrated in the same SharePoint, i was unable to connect to the data using a separate reporting services. I guess this has something to do with claims / kerberos.

Ok on to the report. I created a report in BIDS. First I created a shared datasource connection using a Microsoft SQL Server Analysis Services connection type pointing to: “data source=http://sp2010rc:5566/PowerPivot Management/466f3002-543f-4c06-93bf-4d922641a73f/PowerPivot Management Data.xlsx”.  So we can reuse this connection for multiple reports we can build in the future. Change this link to the location of your PowerPivot Management Data.xlsx file.

Next i created the report, in this report i wanted to show the usage from the last 7 and 30 days of my PowerPivot Files. To get the data I altered a SSRS generated dataset in MDX to always return the last 7 days.

To determine the measure I wanted i used the “Workbook Activity.xlsx” provided in the management dashboard. The three measure used there to determine workbook activity are:

For dimension members i selected:

• [Date].[Month] : Month a request was done in
• [Date].[Year] : Year a request was done in
• [Date].[Date] : The date a request was done in
• [Documents].[AuthorLoginName] : Author of the document requested
• [Documents].[FileUrlRelativeToSite] : Filename of the document requested
• [Users].[FullName] : user that requested the filename

This results in the following MDX statement:

SELECT NON EMPTY { [Measures].[UserDCnt], [Measures].[QuerySum], [Measures].[SizeMBMax] } ON COLUMNS,
	  NON EMPTY {
				(	[Date].[Month].[Month].ALLMEMBERS *
					[Date].[Year].[Year].ALLMEMBERS *
					{ ParallelPeriod([Date].[Date].[Date], 7, StrToMember("[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]"))
						: StrToMember("[Date].[Date].[" + Format(now(), "yyyy-MM-dd") + "]") }*
					[Documents].[AuthorLoginName].[AuthorLoginName].ALLMEMBERS *
					[Documents].[FileUrlRelativeToSite].[FileUrlRelativeToSite].ALLMEMBERS *
					[Users].[FullName].[FullName].ALLMEMBERS
				)
				}
	DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Sandbox]

Take notice on the Date part:

{ ParallelPeriod([Date].[Date].[Date], 7, StrToMember(“[Date].[Date].[” + Format(now(), “yyyy-MM-dd”) + “]”))
: StrToMember(“[Date].[Date].[” + Format(now(), “yyyy-MM-dd”) + “]”) }
What happens here is that i only want to select the measures that in  the date range from 7 days ago to today, we can do this because the date dimension contains all dates and thus can do a strtomember. You can change this number to whatever you want, or maybe even make it a parameter in ssrs.

Putting this in a report i show the powerpivot file together with its author, total nr of users, nr of queries and the size of the workbook, then i used a sparkline to show the nr of users for each day, and in a databar i show the numer of querys * the size of the workbook to show the load it has on the server.

This results in the following report:

Too bad that I didn’t have more data in my management dashboard so the trend doesn’t really come out as it should, but it should give you an idea what you can do. When we actually will have more data we can fine tune this report.

I have published this report to SharePoint and made sure the report is executed as the user we just created and gave access to the central admin:

The report will now have access to the powerpivot management data while the report user doesn’t.

Feel free to download the the SSRS 2008 R2 project here. Tell me what you like or dislike from it or what you changed.