PerformancePoint Custom Dashboard Filter Experience

Over the last couple of weeks I was asked to setup a filter off an ODBC source (going against and Oracle database server and have to use the ODBC source – using that instead of a SQL Server Linked source and SQL Server Table Tabular List source).  I was like sure you can do that, no problem.  Then I was trying to setup the filter after getting the ODBC source setup and tested and I was like…hmmm…guess not.

So I recall in my numerous blog readings that there was a sample available (got to love code sharing) out on the PerformancePoint Team’s blog – Creating a Custom Dashboard Filter for PerformancePoint Server 2007 ~ Code Sample done by Joe Hayes of Microsoft.  I went ahead and download the sample and read through the documentation.

There are a couple of things you need to do once you download the code sample, you will need to compile the code to create the dll to reference and you will also need some additional .NET SDK tools to be able to create certificates and to build the modified ClickOnce Dashboard Designer application.  If you do not have the developer tools to compile the code you can download the C# Express Edition and you can download the .NET 2.0 SDK tools or the new Windows SDK for Server 2008 and 3.5 Framework to create the certificate and build the modified ClickOnce Dashboard Designer application.

Now the instructions that come with the code sample for the SQL Query Parameter Filter are fairly good and there are additional instructions that are available out on MSDN – How to: Install Data Providers for Filters and How to: Install Filter Designers.  The big part that gets skipped over in my opinion in the instructions is the process of creating the certificate file that is needed to update and sign the ClickOnce Dashboard Designer manifest and to rebuild the application.

After much searching and asking people I came across a couple of nice blogs that helped me to finally complete the process and point me in the right direction of the commands I needed to complete (Create your own .pfx file for ClickOnce and Code Signing for Developers – An Authenticode How-To).

So once you have completed the instructions of building the dll (when you build the dll it will use the SqlQueryParameterProvider.snk to provide a strong name it during the build process – this is configured in the project properties), loading it into the GAC with the gacutil.exe utility, updated the three web.config files (Preview, Web Service, and SharePoint), and updated the PSCBuilder.exe.config file (and placed the dll in the DesignerInstall3.0 directory) you are then ready to create your certificate if you do not have one available and complete the process with the following steps:

1. Create the certificate with the makecert.exe application – run from the Bin directory of the .NET 2.0 SDK (I place the files in a Temp directory on the C-drive):

C:Program FilesMicrosoft Visual Studio 8SDKv2.0Bin>makecert.exe -sv c:tempPPSKey.pvk -n "CN=MyCompany" c:tempPPSKey.cer

2. This will prompt you for a password (I will be using Pass@word1 for my examples here):

 

You will be asked to create the password and confirm it

And then enter the password

3.  Create the pfx file to complete the certification process that is properly signed with the pvk2pfx.exe application and provide the password supplied above when creating the pvk and cer files:

C:Program FilesMicrosoft Visual Studio 8SDKv2.0Bin>"C:Program FilesMicrosoft Visual Studio 8Common7ToolsBinpvk2pfx.exe" -pvk c:tempPPSKey.pvk -pi Pass@word1 -spc c:tempPPSKey.cer -pfx c:tempPPSpfx.pfx -po Pass@word1

4. Now you are ready to sign the PSCBuilder.manifest with the mage.exe application:

C:Program FilesMicrosoft Visual Studio 8SDKv2.0Bin>mage -update "C:Program FilesMicrosoft Office PerformancePoint Server3.0MonitoringPPSMonitoring_1DesignerInstall3.0PSCBuilder.exe.manifest" -fd "C:Program FilesMicrosoft Office PerformancePoint Server3.0MonitoringPPSMonitoring_1DesignerInstall3.0" -certfile c:tempPPSpfx.pfx -pwd Pass@word1

5. And finally sign the PSCBuilder.application with the mage.exe application:

C:Program FilesMicrosoft Visual Studio 8SDKv2.0Bin>mage -Update "C:Program FilesMicrosoft Office PerformancePoint Server3.0MonitoringPPSMonitoring_1DesignerInstallPSCBuilder.application"    -AppManifest "C:Program FilesMicrosoft Office PerformancePoint Server3.0MonitoringPPSMonitoring_1DesignerInstall3.0PSCBuilder.exe.manifest" -CertFile PPSpfx.pfx -pwd Pass@word1

So assuming you completed the other steps you are ready to go to PerformancePoint Monitoring Central and run the updated Dashboard Designer application.  If for some reason the new version does not install then you will need to clear out your ClickOnce temporary install directory so that you will be prompted to run the installation. To remove the files in Vista or Windows Server 2008 you would remove all of the files in the following directory for your user — C:Users<USERNAME>AppDataLocalApps2.0 and in Windows XP and Windows Server 2003 it would be in — C:Documents and Settings<USERNAME>Local SettingsApps2.0.  Just remember that in doing this you will need to reinstall all ClickOnce applications that have been installed on the workstation.

After that is all said and done you will be ready to use the new SQL Query filter in Dashboard Designer and create filters against the ODBC data sources.  Congratulations!  Now the only thing is to prepare for PPS SP2 in late December and see how all of these customizations get alternated and will most likely have to be done all over again:)

UPDATE (11/26/2008)

One quick note to mention is that in order to get the custom filter to work once deployed to SharePoint (which is running on different servers than PPS) against the Oracle ODBC source, the Oracle Client tools had to be installed on the SharePoint servers and configured appropriately and rebooted.  So it appears that the web part is going directly from SharePoint to the source and not through PPS (except to get the metadata for the part).

Here is the error message we got prior to installing the Oracle Client tools on the SharePoint servers: "An unknown error has occurred. If the problem persists contact an administrator. There may be additional information in the server application event log. Contact the administrator for more details."  and in the SharePoint server Application Event Log the error was – System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Anyway, it is up and running and this is great news!

 

UPDATE (1/28/2009)

Just wanted to do an additional follow up.  We ended up having to back away from using this as a solution because it required an elevated level of permissions on the server.  This solution worked for the development team, but not for the end-user.  In monitoring the server we could see all of the registry and files being accessed and modified in order to utilize the Oracle components.  We decided to simple make sure that everything that we needed to access was available within the SSAS data source to be able to use as filters instead.  We did not want to be dependent on using SQL Server either and setting up a linked server.  This was rather disappointing and Microsoft is looking into this.

UPDATE (2/27/2009)

Just wanted to let everyone know that the permission issue has been figured out.  As long as the user being used to access the data source has at least ‘Editor’ permissions in PPS (or of course ‘Admin’ PPS permissions would work, but not recommended) then the SQL View filter will work and display data in the filters.  It worked in Preview mode for us because the Application Pool identity had the necessary permissions, but in SharePoint the PerUser connection is being used for security and the end-users only had ‘Reader’ permissions.  We thought this hotfix for PPS would have fixed the issue (http://support.microsoft.com/default.aspx/kb/967343), but it did not.  So even on version 3.0.4311.00 we still experienced the issue where the filter would display for the user, but it just wouldn’t populate with data.  As soon as we granted them ‘Editor’ rights on the data source then it populated with data.