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.

Contributing to the Technical Community

There is nothing I enjoy more than helping out the technical community and my colleagues.  There are different ways that I do this.  I talk to people about technology to share my excitement and knowledge about what I know, have read, or done.  Besides talking to colleagues, clients, and people that I meet I have been involved in local user groups (PASSMN and BI-SIG), made screencasts (YouTube and Soapbox – because of YouTube 10 minute limit some videos are posted on Soapbox), and actively contribute in the forums (MSDN SQL Server, TechNet PerformancePoint, SQL Server Central, and internally at Magenic).

I also share and inform others when I come across new content or solutions by blogging about it or posting content and information internally on the Magenic SQL Server or Business Intelligence SharePoint sites.  Why do I do all of this?  Very good question and not sure if I have a good response.  I just have this passion inside of me for this technology and I really enjoy sharing it with others.  I really enjoy working with the technology and like to help out others and collaborate.

I would just encourage others to share their knowledge also and help contribute to your local user groups, within the forums, and with your colleagues.  Everyone runs into a roadblock and needs advice or guidance and posting and responding within the forums is a great way to contribute.  Please don’t feel embarrassed to post a question or possible solution – by doing so you are only helping others.  And if you post a question and you come up with the solution or someone provides you the solution, please let others know.  Just don’t reply and say ‘figured it out’ or ‘problem solved’ or ‘resolved the issue’ – share what you did to solve the issue because others would like to know – make the community stronger.

That’s it for now.  On to the next blog posting.

Downloads and Catch-Up

Ok I took a bit of time off from the blogging.  Just got into some really interesting work with a new client and have been holding off on sending out some links to some great content that I have come across over the past month.  I am also in the process of blogging about using the custom PPS SQL Query filter that was released and my experience implementing the filter.

So on to my links, downloads, and other exciting news:

1. Competitive Comparison of SQL Server 2008 Integration Services white paper – This paper compares SQL Server 2008 Integration Services to the extract, transform, and load offerings of competitors, including IBM Information Server, Informatica PowerCenter, Oracle Warehouse Builder, and Oracle Data Integrator. The paper shows that, while competitor products perform competently in many situations, they do not compare to the advanced features and capabilities available in SSIS.
2. Installing Dashboard Designer using an MSI Installer file – may or may not be useful to you, just depends on how you would like to deploy the PPS Dashboard Designer.
3. SQL Server 2008 White Paper: Analysis Services Performance Guide – This white paper describes how application developers can apply query and processing performance-tuning techniques to their SQL Server 2008 Analysis Services OLAP solutions.
4. SSIS Connectors now available for SAP BI, Oracle, and Teradata – Both of the promised post-RTM packages of connectors for use with SQL Server 2008 Enterprise and Developer are now available for download.
5. Office PerformancePoint Server 2007 Technical Library in Compiled Help format – Downloadable CHM version of PerformancePointServer content on TechNet.
6. Cumulative update package 2 for SQL Server 2008 and if you are not ready for that then here is Cumulative update package 1 for SQL Server 2008

   SQL Server 2008 Builds:

   Microsoft SQL Server 2008 (RTM) – 10.0.1600.22

   Microsoft SQL Server 2008 (CU1) – 10.0.1763.0

   Microsoft SQL Server 2008 (CU2) – 10.0.1779.0

7. Reporting Services Scale-Out Deployment Best Practices – This technical note is part of the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series  which provides general guidance on how to set up, implement, and optimize an enterprise scale-out architecture for your Microsoft® SQL Server® Reporting Services environment. This note provides guidance for both SQL Server 2005 and 2008 Reporting Services. The focus of this technical note is Scale-Out Deployment Best Practices – the Reporting Services Windows®/Web Services servers that provide your reports.

That is a good list for now.  I will be posting on the PPS custom filter in the near future once I get everything typed up.  I also wanted to mention that I did get elected into the PASSMN Executive Board for 2009, so I am anxiously awaiting to meet with the group and determine my official position.  If you didn’t see my campaign video, which I only posted internally in the Magenic forums (but the video is on Soapbox) you can check that out right here in my MSN video collection –> PASSMN – Dan English Campaign Video (it was just a funny skit that was put together and no I am definitely not a good actor). 

I have more videos out on YouTube and at some point I need to make sure all of these are on Soapbox (YouTube has the 10 minute limit, so I cannot put all of my videos on that site).  Plus I need to make sure all of the videos are working on YouTube because I received an email stating that some of them were no longer available…sorry and I will make sure that they are working again.

I also want to send out a quick congratulations to the BIDS Helper team which I have gladly supported and blogged about a few times promoting on winning the SQL Server Heroes Contest.  Nicely done and well deserved!

If you are wondering about where I get most of my download information from it is from my RSS feed to the MDSN Download Center which keeps you up-to-date.

I also wanted to put a link to the I’m a PC – Tron Guy which I think is great since I am a fan of the movie and he is from Fairmont, MN.