In SCSM 2012 we can open an OLAP Cube in Excel and create a report by using PowerPivot. If you have the report open in Excel you can refresh the report to see updated data by right click on the report and choose Refresh. Another alternative is to refresh the report everytime you open the report by chosing Data\Properties\Refresh data when opening the file:
This worked as expected for me until I tried to do this as a regular helpdesk user instead of the SCSM admin account. I got an error that said the user does not have access to the DWASDataBase database, or the database does not exist.
When clicking OK I got a new error message:
I found that there was two user roles in SQL Analysis Server when I opened it in SQL Server Management Studio. The user roles was SCDW_Administrators and SCDW_Report_Readers.
When I found that I was pretty sure that this should be solved if I put in my user in the SCDW_Report_Readers role. Unfortunately it didn´t. It worked if I put in the user in SCDW_Administrators but don´t want to give the user more rights than needed. So the solution that I come up with was to create a new role with the rights to read and put in my user in that role. See below which setting I used in the role:
After I created the new role and added my account I could refresh the report. I hope this can help you to if you have the same problem.
Pingback: How to create an Incident Dashboard using Excel in System Center 2012 SP1 - Service Manager (SCSM) 2012 - Stay Classy, With System Center Ramblings from San Diego - Site Home - TechNet Blogs