Issues Importing SSAS 2008 KPIs into PerformancePoint 2007

Getting PerformancePoint 2007 to work with SQL 2008 (and SQL 2008 R2) has always been a bit of a hack.  Officially it wasn’t supported at all until PPS 2007 SP2 was released.

Background

Basically, all the PPS assemblies reference v9.0.242.0 SQL assemblies (SQL 2005) and 4 key SQL 2005 packages from the SQL 2005 Feature Pack are pre-requisites before PPS 2007 can be configured.  Namely, these are:

– Microsoft ADOMD.NET (SQLServer2005_ADOMD)

– Microsoft SQL Server Native Client (SQLNCLI)

– Microsoft SQL Server 2005 Analysis Services 9.0 OLEDB Provider (SQLServer2005_ASOLEDB9)

– Microsoft SQL Server 2005 Management Objects Collection (SQLServer2005_XML)

Issues Importing KPIs

So everything is finally configured and PerformancePoint Dashboard Designer seems to be working nicely with your data sources all set up etc…

But, when you try to create a new Scorecard based on existing KPIs in your SSAS Cubes, you find it fails with the following:

Looking inside the Event Log, you’ll find the following error:

The important stuff….

System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.AnalysisServices, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’
   at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(IBpm pmService, DataSource asDataSource)
   at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)

In other words…

The assembly which controls the import from SSAS (Microsoft.PerformancePoint.Scorecards.Server) has a reference to v9.0.242.0 of Microsoft.AnalysisServices which it cannot find.

Solution

Following many failed attempts to re-install the SQL 2005 components in order to get this assembly installed and references, I decided to manually copy this file from an existing SQL 2005 installation into the GAC of this server. 

This I did, however I then received a subsequent Microsoft.Analysis.Connection.Exception:

Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version ‘10.0.1600.22’.
   at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
   at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(IBpm pmService, DataSource asDataSource)
   at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)

The upshot is that v9 of the assembly is referenced, however I need v10 to be able to connect to my SQL 2008 repository.

So here’s what I finally did:

In .NET we can redirect the assembly bindings in the web.config.  I added the following to the Web.Config file for the PPS Monitoring WebService which Dashboard Designer uses to connect to SSAS:

  <runtime>
    <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″>
    <dependentAssembly>
            <assemblyIdentity name=”Microsoft.AnalysisServices” publicKeyToken=”89845dcd8080cc91″ culture=”neutral” />
            <bindingRedirect oldVersion=”9.0.242.0″ newVersion=”10.0.0.0″/>     
    </dependentAssembly>
    </assemblyBinding>
  </runtime>

Saved the file and did a IISReset.

On reloading Dashboard Designer, I can now successfully Import my KPIs!!!

Additional Info

I must point out that this issue was on a single server development machine and as such, may be a quirk of my setup.  For starters I couldn’t get SQL Server 2005 SP2 Management Objects to install – and this problem may be the result of this. 

I haven’t tried using a separate client machine to import SSAS KPIs using PPS Dashboard Designer.

Regardless of the above, this may help some other PPS / SSAS developers out there who have experienced this problem.

Thanks

Thanks to my colleague Tony Pounder (@WorTony) for help with doing the .NET Assembly redirect statements.  Also, big thanks to Bill Wang’s blog entry on something similar, which led me down this path.