Configuring Excel Service Application for Business Intelligence Center in PWA

  • Article

My teammate went on vacation and asked me to demonstrate reporting using the Excel Service Application in Project Server 2010 for reporting to his customer. He thanked me profusely for stepping in to cover him and then hightailed it out of the office. Huh. Now, I have to figure out how to set this up and get it working. I already know I'm not skilled at building/manipulating reports, but I figure I can configure the Excel Services Application that's part of SharePoint Server 2010 and use PWA to access Excel spreadsheets where the report data resides. The customer will have to provide the skilled labor to develop the reports, but I should be able to get him to that point successfully.

Naturally, I started off with search terms, specifically Project Server 2010 Excel Services Technet.  Search parameters: https://www.bing.com/search?q=project+server+2010+excel+services+technet&src=IE-SearchBox&FORM=IE8SRC.  This easy search rewarded me with this link:  Configure reporting for Project Server 2010: https://technet.microsoft.com/en-us/library/ee662106.aspx

I decided to use the /PWAdemo instance on my web front end/app server \\brwhitewfe2010 and database server \\brwhitedb2010. My test domain, white.com, has been up and running for a long time now and all my test servers and workstations are part of that domain. 

Following the Configure reporting for Project Server 2010 link above, I started working my way through the steps. As always for customers, I recommend setting this up in a test environment so you can become familiar with the steps and how to deal with any issues that arise in a non-production environment so that when you roll out your changes in production, it's a non-event and there are no surprises. The story below is the description of what I did to get this all working. It was surprisingly easy. I recommend you open the link and use it in conjunction with my notes below.

  1. Created groups and accounts
    1. Report Authors group = Report Authors
    2. Report Viewers group = Report Viewers
    3. External Report Viewers group = Report Viewers – External
    4. Application Pool account = white\ excelapppoolaccount
    5. Secure Store Target Application account = white\securestoretargetappaccount
  2. Added a login for the report authors group to SQL Server.
  3. Installed AMO on my single application/wfe server.  SQL Server 2008 Analysis Management Objects (https://go.microsoft.com/fwlink/p/?LinkId=130655)
  4. Had to launch IE using “Run as administrator” in order to see the Manage services on server link and restart the Project Application Service.
  5. Start Excel Services – this was already running.
  6. Configure Excel Services settings
    1. Created a site collection to hold two sites prior to running these instructions. I'm not sure this was stictly necessary because the Business Intelligence Center has these two folders.
    2. Site Collection: Project Business Intelligence Center at https://brwhitewfe2010/sites/PBIC.
    3. Project Business Intelligence Center – Templates at https://brwhitewfe2010/sites/PBIC/Templates. 
    4. Project Business Intelligence Center – Sample Reports at https://brwhitewfe2010/sites/PBIC/Sample Reports  
  7. Determined the URL for the data connection library
    1. https://brwhitewfe2010/PWAdemo/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)
  8. Start the Secure Store Service – was already running.
    1. Had to generate a new key. Used “Tonight, we make soap.” as the passphrase.
    2. At that point, I could click New on the Edit tab.
    4. I clicked Next on the above screen and the instructions from Technet said to click Next on the screen after that, as well, so I did.
    5. I used the white\farmadmin and the white\sqlserver service accounts in the Target Application Administrators box for good measure.
    7. At this point, I added white\securestoretargetappaccount to the white\Report Authors AD group and verified that group had db_datareader rights to the ProjectServer_Reporting DB in SQL Server. 
    8. While I was at it, I added my Farm Admin account and the SQL Server service account because I frequently log in as those users. I also added white\test1 and white\test2 user accounts to the white\Report Viewers AD group while I was in AD Users and Computers. I never ended up using those accounts because I used my farmadmin account to upload Excel spreadsheets, but customers will want to add whatever AD groups are appropriate to the group
  9. Populate the Report Authors and Report Viewers Active Directory groups
    1. Added white\Report Authors group to white\Report Viewers group in AD.
  10. Configure Business Intelligence Center access
    1. I didn’t need to do anything here because all the accounts I’m using are already user accounts in my PWAdemo instance.
  11. I attempted to access PWA and got an HTTP Error 503. The service is unavailable. error. I'm not certain why, exactly, but several application pools had stopped so PWA and SharePoint Central Admin were not available. I ended up having to restart several of the pools in IIS Admin and PWA was available again.

 

So now that that's all be configured, I can start with the Excel report creation...

  1. I ran a cube build in PWA by accessing Server Settings > OLAP Database Management.  Instructions on creating a cube in Project Server 2010 can be found here: https://technet.microsoft.com/en-us/library/ff465341
  2. I used the links below to learn about BI in SharePoint.
    1. Business intelligence in SharePoint Server 2010 - https://technet.microsoft.com/en-US/sharepoint/ee692578.aspx
    2. Module 2 “Creating Reports in the Excel Client” leads to https://technet.microsoft.com/en-us/sharepoint/hh126809 (scroll down to see the video). 4:45min – describes how to create a data connection.
  3. I installed Office 2010 and using Excel, I created a file called KV_Cube.xls and used Data tab > From Other Sources > From Analysis Services to create the ODC file necessary to access the cube. Here's what I used to create the ODC file in my test environment so the spreadsheet could connect to the OLAP cube.
    1. Use brwhitedb2010
    2. KV database in Analysis Services
    3. Use MSP_Project_SharePoint
    4. Chose to create PivotTable report.
  4. Uploaded KV_Cube.xlsx to the Dashboards area (https://brwhitewfe2010/PWAdemo/ProjectBICenter/Dashboards/Forms/AllItems.aspx) as Web Part Page.
  5. I was able to access the file by launching PWA and going to Business Intelligence in the Quick Launch bar, then I clicked on Dashboards.
  6. The file was there and by clicking the Name, I could view the report in the browser. 
  7. I should note that you can store the ODC file in SharePoint.  In fact, there's a a Data Connections library you can use to store ODC files.  You can resuse these ODC files for different reports and not have to keep creating new ones.