Creating a Scorecard Transform in PerformancePoint Server Monitor


Overview

I’ve spent a couple of days doing some prototyping around PerformancePoint Monitor… there have been a few things that I’ve wanted to accomplish, so when the PerformancePoint Monitoring SDK was released, it seemed like the way to do it.  However, there aren’t a lot of resources around Scorecard Transforms out there, so I thought I’d document some of the things I found.  This documentation won’t be exhaustive, but it will hopefully point everyone in the right direction.  Keep in mind this is just prototype code… a number of enhancements should be made before putting this into production.

I’ve created a sample project (and a simple dashboard) to do the following:

  1. Perform a Magic Number transformation – I’ve had clients want a message to be returned to the user instead of a value… such has having conditional logic in a calculated member that returns percentages between 0 – 1, then -1 if no data exists, -2 if the KPI is invalid in the chosen scenario, etc.
  2. Display text on the scorecard that is database driven – I have a request to provide database driven, text metadata for KPIs on each scorecard.

The dashboard contains two scorecards (‘Sample Scorecard’ that the transforms are applied to, and ‘Sample Scorecard 2′ which the transforms are not applied to), with two KPIs.  You will need to publish the objects in the .bswx file (included in the Visual Studio solution) in order to see the results of the transforms.

You can download the sample project here.

Where to Start

The first thing to do is to create a DLL containing your transform.  I started with a C# class library.  Remember that your DLL will have to be strongly named.  The basic steps will be to run SN.exe to create a key file, then using the resulting .snk file in your project.

Once you’ve created your project, you’ll need to implement a class that inherits from IGridViewTransform, and implement three methods in your class:

  1. public GridViewTransformType GetTransformType()
  2. public string GetId()
  3. public void Execute(GridViewData viewData, PropertyBag parameters, IGlobalCache cache)

The first method is used to determine when in the lifecycle of a scorecard the transform is run.  Your options are PreRender, PreQuery, PostQuery, and PerUser.  Here’s a sample:

   1: /// <summary>
   2: /// Returns the type of transform that will be applied
   3: /// </summary>
   4: /// <returns>Grid view transform type</returns>
   5: public GridViewTransformType GetTransformType()
   6: {
   7:     return GridViewTransformType.PreRender;
   8: }

The second method simply returns the name of the transform:

   1: /// <summary>
   2: /// Returns the name of the transform
   3: /// </summary>
   4: /// <returns>Name of transform</returns>
   5: public string GetId()
   6: {
   7:     return "TransformMagicNumberToText";
   8: }

The Execute method is what we’re really interested in… this is run every time a scorecard is displayed.  OK, that’s not quite true… week in mind that every time a scorecard is run for an individual for a particular set of parameters, it get’s cached for awhile… keep that in mind when debugging.  An IISRESET will make sure the transform is run again.

   1: /// <summary>
   2: /// This method is called each time a Scorecard is rendered
   3: /// </summary>
   4: /// <param name="viewData">Scorecard view that the transform acts on</param>
   5: /// <param name="parameters">Parameters passed to the method</param>
   6: /// <param name="cache">Global cache of objects</param>
   7: public void Execute(GridViewData viewData, PropertyBag parameters, IGlobalCache cache)
   8: {
   9:  
  10: }

First, we’ll set up some variables that we’ll use later.  The idea is that there are some of these transforms that should only be run on certain scorecards, columns, KPIs, etc.  I’m prototyping these for a publicly accessible dashboard… so I want to keep performance in mind.  The final version will probably utilize caching in order to increase performance.  Right now, I just threw in some basic functionality to make the transform a little more ‘selective’.

   1: // These will be read from a config file or a database in production
   2: // Use to limit the columns in a Scorecard that this transform will run on
   3: string columnNames = "|Magic Column|Another Column|";  
   4: // Use to limit what scorecards this transform will run on
   5: string scorecardNames = "|Sample Scorecard|Another Scorecard|";
   6: // Value to replace, and text to replace it with; this would probably really be a list
   7: decimal magicValue = Convert.ToDecimal(-1); 
   8: string replacementText = "Invalid Data";
   9:  
  10: // Columns we're interested in that are on the scorecard
  11: List<GridHeaderItem> columnsInScorecard = new List<GridHeaderItem>();

Next, we’re going to get the name of the scorecard, so we will only run the transform on scorecards we want.

   1: // Get the Scorecard that is currently being transformed
   2: Scorecard scorecard = cache.GetScorecard(viewData.ScorecardId);
   3: // Get the name of the scorecard
   4: string scorecardName = scorecard.Name.Text;
   5:  
   6: // Validate that this scorecard should have the transform applied
   7: // If not, short-circuit
   8: if (scorecardNames.IndexOf("|" + scorecardName + "|") < 0)
   9: {
  10:     return;
  11: }

Next, we’ll run through the list of GridHeaderItems that exists on the Scorecard.  We’ll build a list of the the GridHeaderItems who’s display text matches the columns we want to be able to transform.

   1: // Get the row and column headers
   2: List<GridHeaderItem> rowHeaders = viewData.RootRowHeader.GetAllHeadersInTree();
   3: List<GridHeaderItem> columnHeaders = viewData.RootColumnHeader.GetAllHeadersInTree();
   4:  
   5: // Iterate through the Column headers to get a list
   6: // containing the columns we're interested in
   7: foreach (GridHeaderItem ghi in columnHeaders)
   8: {
   9:     // See if the column exists in our list
  10:     if (columnNames.IndexOf("|" + ghi.DisplayText + "|") >= 0)
  11:     {
  12:         // Add it to the list if so
  13:         columnsInScorecard.Add(ghi);
  14:     }
  15: }

Now we’re going to look through all the the columns and rows on the scorecard.  We’ll look at each display condition, see if it matches our criteria, then replace it if it does.  We accomplish changing a number to text by removing the initial Display Element, and replacing it with a new one.

Now we have a basic transform that, for a particular scorecard and column, will replace specific numbers with text.

   1: // Iterate through the list of columns we're interested in
   2: foreach (GridHeaderItem columnHeader in columnsInScorecard)
   3: {
   4:    // Look at each row on the scorecard
   5:    foreach (GridHeaderItem rowHeader in rowHeaders)
   6:    {
   7:        // Variable to hold the index that we want to remove
   8:        // This may need to become a list, or another type of structure
   9:        int indexToRemove = -1;
  10:  
  11:        for (int i = 0; i < viewData.Cells[rowHeader, columnHeader].DisplayElements.Count; ++i)
  12:        {
  13:            GridDisplayElement gde = viewData.Cells[rowHeader, columnHeader].DisplayElements[i];
  14:            
  15:            // Look for number in the display properties
  16:            // In this case, look for the Value, not the formatted display
  17:            if (gde.DisplayElementType == DisplayElementTypes.Number)
  18:            {
  19:                // Find our magic value that we use as a key
  20:                // to know when to replace the value 
  21:                if (gde.Value == magicValue)
  22:                {
  23:                    indexToRemove = i;
  24:                }
  25:            }
  26:        }
  27:  
  28:        if (indexToRemove > -1)
  29:        {
  30:            viewData.Cells[rowHeader, columnHeader].DisplayElements.RemoveAt(indexToRemove);
  31:  
  32:            GridDisplayElement newGde = new GridDisplayElement();
  33:            newGde.DisplayElementType = DisplayElementTypes.Text;
  34:            newGde.Text = replacementText;
  35:  
  36:            viewData.Cells[rowHeader, columnHeader].DisplayElements.Add(newGde);
  37:        }
  38:    }
  39: }

 

Deploying the Transform

Alright, so we’ve written and compiled our first transform.  Now, all we need to do is actually install it.  There isn’t any documentation (at the time of writing this blog) in the PerformancePoint Monitoring SDK on how to install a Scorecard Transform… but there is documentation on how to Install Report Viewer Extensions.  I took a guess that the process would be pretty much the same (albeit instead of using the <CustomReportViews> section you use the <ScorecardTemplateExtensions> section in the config files), and it appears so.  You can just follow those steps with that minor change.

Here is an example of what your new <ScorecardTemplateExtensions> section will look like:

   1: ...
   2: <CustomViewTransforms>
   3:   <add key="ExpandNamedSets" value="Microsoft.PerformancePoint.Scorecards.GridViewTransforms.ExpandNamedSets, Microsoft.PerformancePoint.Scorecards.Server, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
   4:   <add key="RowsColumnsFilterTransform" value="Microsoft.PerformancePoint.Scorecards.GridViewTransforms.RowsColumnsFilterTransform, Microsoft.PerformancePoint.Scorecards.Server, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
   5:   <add key="AnnotationTransform" value="Microsoft.PerformancePoint.Scorecards.GridViewTransforms.AnnotationTransform, Microsoft.PerformancePoint.Scorecards.Server, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
   6:   <add key="UpdateDisplayText" value="Microsoft.PerformancePoint.Scorecards.GridViewTransforms.UpdateDisplayText, Microsoft.PerformancePoint.Scorecards.Client, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
   7:   <add key="ComputeRollups" value="Microsoft.PerformancePoint.Scorecards.GridViewTransforms.ComputeRollups, Microsoft.PerformancePoint.Scorecards.Client, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
   8:   <add key="ComputeAggregations" value="Microsoft.PerformancePoint.Scorecards.GridViewTransforms.ComputeAggregations, Microsoft.PerformancePoint.Scorecards.Client, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
   9:   <!-- add key="ApplyDefaultFormatInfo" value="Microsoft.PerformancePoint.Scorecards.Client.ApplyDefaultFormatInfo, Microsoft.PerformancePoint.Scorecards.Client, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/-->
  10:   <!-- New Scorecard Transforms -->
  11:   <add key="TransformReplacePropertyText" value="ScorecardTransformPrototypeLibrary.TransformReplacePropertyText, ScorecardTransformPrototypeLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=dc084a0be77df9c1" />
  12:   <add key="TransformMagicNumberToText" value="ScorecardTransformPrototypeLibrary.TransformMagicNumberToText, ScorecardTransformPrototypeLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=dc084a0be77df9c1" />
  13:   <!-- End New Scorecard Transforms -->
  14: </CustomViewTransforms>
  15: ...

After modifying your three web.config files and GACing your assembly, your scorecard should be ready to run!

Debugging your Assembly

OK, so you’re brand new assembly is running… and not exactly doing what you’re expecting it to.  Or perhaps you just want to see what’s going on in all those objects that you’re using in your code.  Unless you’re already developing on your SharePoint box, you’ve now got a few hoops to jump through in order to be able to debug.  The configuration I use is to develop locally, then deploy to a server… so I’ll run through how to get that working.

First, you need to setup remote debugging on your server.

Next, you’ll need to put your debug symbols into the GAC with your your assembly.

Then, you’ll need to restart IIS so your new assembly gets loaded (otherwise, you’ll probably get some funky results).

I went ahead and created a batch script to do this (included in the Visual Studio solution… I created a folder on the SharePoint server for this to live in)… your filenames and paths will be different, but this is the basic concept:

   1: @ECHO OFF
   2: REM: This batch file will pull the assembly and debug symbols
   3: REM: from the development machine, GAC the assembly, and 
   4: REM: put the debug symbols into the GAC folder.
   5:  
   6: REM: Copy the scorecard files to this server
   7: COPY "\\DDARDEN\C$\Users\ddarden\Documents\Visual Studio 2005\Projects\ScorecardTransformPrototype\ScorecardTransformPrototypeLibrary\bin\Debug\Score*.*" .
   8: ECHO Copied the scorecard files to the server
   9:  
  10: REM: Add the DLL to the GAC
  11: "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /i ScorecardTransformPrototypeLibrary.dll
  12: ECHO GAC'd the DLL
  13:  
  14: REM: Copy the debug symbols to the GAC directory to enable remote debugging
  15: COPY ScorecardTransformPrototypeLibrary.pdb C:\WINDOWS\assembly\GAC_MSIL\ScorecardTransformPrototypeLibrary\1.0.0.0__dc084a0be77df9c1
  16: ECHO Loaded the symbols
  17:  
  18: REM: Recycle IIS to reload the assembly
  19: IISRESET
  20: ECHO Reset IIS

Now you’re going to need to attach to the correct process on the remote machine.  You want the w3wp process… there may be a few, so you might have to experiment a little to find the right one (in my configuration, I want the one running under the network service – NOT the SharePoint domain service account).  When you attach to a process, look at the output to see if your assembly got loaded with symbols.  The process your looking for will have the other PerformancePoint Server Monitor assemblies loaded, such as:

   1: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.WebParts\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.WebParts.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   2: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.Client\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.Client.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   3: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.Server\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.Server.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   4: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.DataSourceProviders.Standard\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.DataSourceProviders.Standard.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   5: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.Client.resources\3.0.0.0_en_31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.Client.resources.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   6: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.WebControls\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.WebControls.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   7: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.Script\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.Script.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   8: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.Common\3.0.0.0__31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
   9: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.WebParts.resources\3.0.0.0_en_31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.WebParts.resources.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
  10: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.Script.resources\3.0.0.0_en_31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.Script.resources.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
  11: 'w3wp.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.PerformancePoint.Scorecards.WebControls.resources\3.0.0.0_en_31bf3856ad364e35\Microsoft.PerformancePoint.Scorecards.WebControls.resources.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

If you see them being loaded, but you don’t see yours, then you probably messed up registering your assembly.

Now, you should be able to set break points, and debug your assembly!

Conclusion

OK… those are the steps to create a new Scorecard Transform, deploy it to the server, and to attach to the process and debug it.  Happy Monitoring!

2 Comments

  1. rfsalas says:

    Hey David, I am glad to see you decided to share some of what you know…good deal!

  2. http:// says:

    Great post, David. This helped me get started with the SDK.

    Have you had any luck editing the Values in the cell? I am trying to create a custom calculation to compute the score when the column name contains “Delta”.

Leave a Reply