In this posting I wanted to take a look at the structure behind the Business Intelligence Center in Project Server 2010 so that hopefully if you run in to any issues you can work out what might be wrong. This adds to the TechNet article on configuring reporting at http://technet.microsoft.com/en-us/library/ee662106.aspx which includes some great videos, and also Treb’s blog posting on troubleshooting reporting problems http://blogs.technet.com/b/projectadministration/archive/2009/12/15/troubleshooting-business-intelligence-features.aspx.
When you provision a new PWA site you get a Data Connection library, with folders for each language version you have loaded, then within each folder is a set of Office Data Connection (ODC) files that contain the details of the source of the data, the SQL query that will return the data, and the ID of the Target Application profile used to get the data (ProjectServerApplication is the default). You also get the Sample Reports Library and the Templates libraries during the provision process, which also contain folders for each installed language pack. When you build a cube you will see added another folder for each cube within each language folder for the Templates library and the Data Connections library – which again contains ODC files for the OLAP report templates added in the templates language directories at the same time the cube is built.
So when you click on a sample report, then assuming it is in a trusted file location it will go off to the referenced ODC in the report, and assuming that too is in a trusted data connection location then this will use the credentials in the Target Application and go off and get you data. What could possibly go wrong?
Things can get interesting if you change Alternate Access Mappings (AAM). Now the Sample Reports and Templates will still be there, but referenced by a different URL, so you will need to update the trusted file locations for those. Also your data connections will be on a different URL so the trusted data connections address will also need to be updated. So where will the report look for the ODC file? You guessed it – in the original location. If by pure luck or good planning the original location also happens to have a path that is still in the Alternate Access Mappings list then you will still get your report. If not then a message will appear; The following data connection file does not exist or you do not have permissions to it: and it will look like this::
Now one answer would be to just add the AAM – but this may not be desirable or practical, particularly if the new AAM was added for Internet access and was a fully qualified domain name with SSL to replacing the original NETBIOS server name – or perhaps as in my testing you provisioned everything on one server, got it all working and then added a Web Front End. You could of course just edit each report and update the ODC to look in the right place – but assuming you are just getting started and have not added a whole bunch of your own reports, templates and ODCs there is another way… Delete the Business Intelligence Center! Now obviously this is a big step, and as I said, you would only want to do this if you had added nothing of value that you didn’t mind re-creating, but by deleting the site, and then running the PWA provision ‘Edit’ option it will get re-created and the ODCs will point to wherever the default AAM is now pointing.
To delete the BI Center (once you have re-read the paragraph above, and are really, really sure you want to do this), click on the option in the left navigation for Business Intelligence, then Site Actions, Site Settings, Delete this site (and it doesn’t go into the recycle bin!). Once it is gone then check that your default AAM is pointing where you want it and go to the Project Service Application, select the Edit option from the drop down for the site you wish to re-create your Business Intelligence Center. then without making any changes in the page that comes up - click Edit.
You will see it go through the usual steps of provisioning, but what it is really doing is similar to a repair on a client installation – and when it find the BI Center is not there is will re-create it, but this time with the links to the ODCs now pointing to the right place. Once the site is provisioned then go back to Business Intelligence and all will be good. All libraries and folders for all languages will be there – even the ones for any cubes you have already built.
I think OLAP was the most read topic of my 2007 blogs after common installation issues – so I am sure there will be more topics to add, but hopefully this background will help you understand what can and might go wrong.
Very cool. This will save me a lot of editing of Sample and Templates. In the past I have been correcting the connection URL in excel. I thought this was available, but never thought about deleting the site and re-provisioning.
If adding Language Pack to existing PWA site, BI Sample Reports are not created. Process above would re-create everything (OLAP & ReportingDB items). But what if you have existing content. Can you kick off process to re-create just the ReportingDB Sample Reports, Data Connections & Templates? eg. RDB Refresh? Or are you left, using process above then restore any custom items from a backup?
Just tested this by adding another language pack to my setup, and I do see the sample reports, data connections etc created. Did you run the config wizard succesfully? I'd suggest also checking the ULS logs in case there are some errors there - not sure if these itesm get created asynchronously - so the wizard may not show the errors but ULS should do. I'd expect the steps above to correct things - although you wouldn't need to delete anything - just run the 'Edit' process with no changes and it should run through and validate or update as required. RDB Refresh will do nothing for the BI Center - it purely deletes and re-publishes data to the reporting database.