Archive

Archive for August, 2010

Issues Importing SSAS 2008 KPIs into PerformancePoint 2007

August 25, 2010 5 comments

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:

image

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

image

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!!!

image

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.

 

 

Categories: Uncategorized Tags: ,

Maintaining a Custom Project Server 2007 Workspace Template

August 4, 2010 3 comments
Technorati Tags:

Introduction

In Project Server 2007 you may customise the SharePoint site which it provisions by default when creating a workspace associated with a project. This tutorial guides you through the steps required to achieve this.

Maintenance

Tip: For maintaining your custom Project Workspace going forwards, I recommend using your major/minor version numbers as part of the template (STP) filename and name.  Although version history can be switched on in the Site Template Gallery, Project Server requires that the STP is promoted to global (farm) status.  When you, or an administrator later comes to review which templates are currently global, the list does not include any version or date created information, making it difficult to establish if your latest changes indeed made it to global status.

Important!

Due to the way Project Server synchronises some data in the SharePoint Workspace with the Project Data held in the database, there are some lists which cannot have any fields removed, although it is OK to add new fields. The lists are as follows:

– Issues

– Risks

– Deliverables

Please minimise the customisations made to these lists.

Solution Tutorial

– Make all required customisations to your Project Server Workspace template. Remember to remove any list items which you have tested but do not want to include with the template.

– When complete, select Site Actions > Site Settings and select ‘Save site as template’ from the Site Settings page.

clip_image002

– On the ‘Save Site as Template’ page, supply the following data:

o A filename in the format: company_pwa_template_vX_X where X_X is the next available version number which is suitable based on the author’s discretion as to whether the changes are major or minor

o A template name in the format CompanyPWATemplatevX_X where X_X matches the filename above.

o Ensure the ‘Include Content’ check box is ticked. This will ensure the Project Overview document is included.

– Click OK

clip_image004

– After a short while, the template will be stored in the Site Templates gallery, and a page will display confirmation of this.

clip_image006

– Click the link to be taken to the Site Template gallery

– The gallery should display all previous versions of the template, including the latest one just saved.

clip_image008

This template has now been saved locally (to the Site Collection) however it must be promoted to global status for it to be picked up by Project Server settings.

– Click on the name of the template, and click the ‘save’ button when prompted

clip_image010

– Save the .stp file to a consistent location on a server in the farm (ideally the same server for consistency). If the file is saved on a local machine at this point, it must be transferred to the server at this stage.

o NOTE: All of the above steps can be completed on a client machine via the browser; however the next steps MUST be completed on one of the servers in the MOSS/Project Server farm, ideally the same server for consistency, by a Farm Administrator.

– Open a command prompt window, and change to the following directory location: 

o C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN

– Type and execute the following command:

o STSADM –o addtemplate –filename “Location\company_pwa_template_vX_X” –name “CompanyPWATemplatevX_X”

– After a short while, the command should complete with ‘Operation Successful’ message

– Type and execute the following command:

o iisreset /noforce

o NOTE: the above IISReset command is necessary for Project Server to recognise the global update. However this will affect all services on the Web Application hosting Project Web Access site collection for a brief period of time.

o NOTE: The workspace template is now published at a global (farm) level. The final steps can be completed from a client machine

– At the PWA Root Site, click the Server Settings menu from the left hand navigation, and select ‘Project Workspace Provisioning Settings’

clip_image012

– On the Project Workspace Provisioning Settings screen, select the new Default Project Workspace template from the list, and click Save.

clip_image014

Categories: Uncategorized Tags: