A few months ago I wrote a blog post on how the scores of KPIs are calculated (see article here), this post will expand on that topic and show how the best and worst values can impact your scores.
Recall that the scores of the KPIs are mapped into the standard ranges for the number of KPI bands that you have. Also recall that the band for the highest level ranges from the upper threshold to the best value and the band for the lowest threshold to the worst value. Let's review a couple of simple examples to see how the scores are affected when best and worst values are adjusted.
Example 1:
KPI Value 105
KPI Target 100
Increasing is Better
Thresholds:
Based on the scoring algorithm from the above mentioned post, the score based on these settings is 83.3%.
This score is based off of the best value of 120 - so for values 120 and higher, the score will always reflect 100%, regardless of how high the actual value is.
If we adjust the best value to 200, the score is now 71.2% because the value 105 is much farther away from the best value of 200.
Similarly, if we adjust the best value down to 100, the score is now 100%.
You can see that the stoplight is always green; however, the actual score used when rolling up this KPI into an objective can be significantly different. For this reason, you should carefully select your best values when working with the thresholds so as to most effectively roll up your KPIs and achieve the best results in your scorecards.
Example 2:
KPI Value 75
KPI Target 100
Increasing is Better
Thresholds:
Based on these settings, the KPI score is 31.3%. The value of the KPI is near the threshold 1 setting and very distant form the worst setting so it gets a value very close to 33%.
Now, if we adjust the worst value to 70% - meaning that all KPI values less than 70% of the target should be scored as a zero, we get the following score - 16.7%:
Any KPI value of less than 70 would automatically be scored as 0%.
If we adjust the worst value to 75%, we get a score of 0.
Again, the stoplight is red regardless of how much below the lowest threshold we are, but the score used when rolling this KPI into an objective can be significantly different.
Using this example, if we leave the worst value at 0, we see our objective is yellow:
However, if we change our worst value to 75% and our score goes to 0, we get an objective that is red:
Summary:
Although it may be easy to accept the defaults for best and worst values when defining KPIs, you should really put some thought into the values and ensure that the value you place in the best/worst fields are the true targets that you want your numbers to be scored against. It can significantly affect the objective values and determine whether your objectives are reflected as On Target or not.
Alyson Powell Erwin
alysonp@microsoft.com
When working with data from your PerformancePoint Planning models in the Monitoring server, you may encounter issues with the formatting of your scenario dimension members Actual, Budget, Plan, etc. The numbers will come across as the raw values in the cube, thus, showing unformatted values when viewing them in analytic grids. Currently, there is not a way to format these metrics within the Planning Business Modeler, however, one option that you do have is to apply the formats in SSAS after you have deployed the model. You can add format expressions in the cube script once the model has been deployed. Keep in mind that if you redeploy your model you may need to re-add these format expressions in your cube script - it would be worthwhile to copy this script off to a safe location so you can cut and paste it in whenever needed.
1. Open the model (cube) in SQL Server Business Intelligence Development Studio.
2. Double-click on the cube in order to load it into the workspace.
3. Click the Calculations tab to pull up the cube calculations.
4. Select the script view button to view the calculations as script.
5. Add script at the bottom of the existing script to format the members of the scenario dimension. The script will include a scope and format_string statement for each member in the scenario dimension that you want to have formatted. For example:
In the above example, the Actual/ABP Variance (%) scenario dimension member will be formatted as a percent always containing 2 precision digits with a physical % included at the end. Actual and Current Month Actual will be formatted as currencies with a $ in front.
Applying these formats will enable much easier navigation of your models in the analytic grids in PerformancePoint Monitoring server.
Alyson Powell Erwin
alysonp@microsoft.com
When first viewing your published scorecards you may want your users to see a fully collapsed scorecard with the indicator counts in the heading rather than the expanded KPI list and an aggregated value in the headings. To do this follow the below steps:
1. In Dashboard Designer, collapse the scorecard so all you see is the "Rollup" rows. Example:
2. Click the "Target" column header, then click the "Properties" button in the "Edit" toolbar:
3. In the window that comes up, Change the "Score rollup type" to "Indicator Count".
4. Publish your scorecard to the server. You should now see this by default:
Here's what it looks like when expanded:
Tim Morgan (Microsoft)
I have been asked several times recently on how to adjust the font size on analytic charts and grids. Within the dashboard designer you have this ability although it is not always exposed automatically when editing/creating charts and grids.
When first creating an analytic chart/grid you are presented with the Analytic View Designer in the workspace area of the Dashboard Designer. Unfortunately, the Home tab remains selected in the Dashboard Designer - leaving the editing capabilities hidden from the user initially. If you select the Edit tab in the Dashboard Designer you will see the edit toolbar appear with the many editing capabilities easily exposed in the Dashboard Designer.
From here, you can easily adjust font sizes, change numerical formatting of the chart/grid, adjust the report type, report layout (grid), control legend placement (chart) and adjust the row and column label length (view options - grid).
Alyson Powell Erwin
alysonp@microsoft.com
Download the Code Sample GridViewTransformSample
Grid View Transforms for scorecards are one of the extensibility points of PerformancePoint Monitoring Server. In this post I will discuss how you can easily create your own Grid View Transform to automatically hide all the empty rows in scorecards.
Imagine you have a scorecard that is used by different organizations in your company. Depending on the level of access employees in different organizations have to the cube they may see no "Actual" value for some of the rows in the scorecard. If you have many rows in your scorecard employees may see a lot of these empty rows. This makes it difficult for them to find the rows they need.
One way to solve this problem is to create different scorecards for different organization. This solution works but you have to create and maintain many scorecards. The other solution is to writing a Custom Grid View Transform that automatically hides all empty rows in a scorecard.
Here are the steps:
1. Create an empty windows class library project in Visual Studio and add the Microsoft.PerformancePoint.Scorecard.Client.dll to its references.
2. Create a class that implements the Microsoft.PerformancePoint.Scorecards.Extensions. IGridViewTransform.
3. Implement the GetID, TransformType and Execute method.
The GetId method simply returns a string ID for the Transform. This can be anything you want it to be and it is the key for your transform.
The GetTransformType method returns the type of transform. You have 3 major types of transforms. A PreQuery transform is executed before the cell data is populated with values. In contrast a PostQuery transform is executed after the cell data is populated and the PreRender transforms are executed after the PostQuery transforms and before the GenerateView method finishes its execution. Since we need to know the value of the cells to do the filtering, we will be using the GridViewTransformType.PreRender transform.
The execute method is where we put the logic of the transform. Before I can explain how it's implemented, we need to discuss the structure of the GridViewData.
In a scorecard GridViewData, columns and rows are represented with different trees. The type of the nodes of these trees are type GridHeaderItem.
GridViewData has a reference to the root of the Column headers tree called RootColumnHeader and a reference to the Row Headers tree called RootRowHeader. A cell in a scorecard is represented with the intersection of a leaf of the rows tree and a leaf of a column tree.
GridCell cell = viewData.Cells[header, col];
To implement the Execute method we need to find the Actual Column. Which is a leaf of the column tree with the HeaderType of ScorecardNodeTypes.KpiActual and iterate through all the rows and check for empty display value of the intersecting cells(between the Actual column and the current row). Once we find a match we set the isHiddenPath property of the row to true.
Here is the code
4. Compile your project. Make your generated assembly is strongly named as it need to be placed in the GAC.
5. Placed the compiled assembly in the GAC.
6. Add the line below to the <CustomViewTransform> section of the %Program Files%\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\Web.config
<add key="HideEmptyRowsTransform" value=" MyCompany.GridViewTransforms. HideEmptyRowsTransform, [NameOfMyAssembly] , Version=1.0.0.0, Culture=neutral, PublicKeyToken=[ MyAssemblyPublicKeyToken]"/>
Replace the [NameOfMyAssembly] to the name of your assembly
Replace the [MyAssemblyPublicKeyToken] with the public key token of your assembly.
7. Add the above line to your web.config file for the preview site. %Program Files%\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Preview\Web.config
8. Add the above line to your web.config files for each MOSS/WSS box that has the PerformancePoint web part installed. \inetpub\wwwroot\wss\VirtualDirectories\80\web.config
9. Restart IIS on your servers
To test this transform, Open the designer and load a scorecard that has rows with empty actual. Click on the update button and notice the empty row is marked with red.
External links
http://msdn2.microsoft.com/en-us/library/microsoft.performancepoint.scorecards.extensions.igridviewtransform.aspx
By: Amin Pirzadeh, Catalin Sipos and Stephen Van de Walker Handy
Currently, in v1 of the Monitoring server you cannot apply complex filters, the only filter capabilities in the analytic charts/grids is non empty filtering. You can apply complex filtering by writing custom MDX for an analytic chart/grid, but when you do that you lose the navigation on the view. I have been asked many times if there is some way to filter without losing the navigation. I have come up with a workaround for this - it is not ideal and may not work in every instance though - so proceed with caution! If it works for you great, if not, you will have to wait until v2 when we will be implementing better filtering capabilities in the analytic grids/charts.
The workaround that I have established is to create your analytic grid/chart with the dimension you want to apply the filter to added to the series for a chart or the rows for a grid. Select the "All" member and then apply a sort to the chart/grid. The sort will do nothing with only one member selected but when you have multiple members, it will be in place and ready to go. Then, select the measures or metrics as columns/bottom axis in your view. Add any other hierarchies you want to filter by in the background axis, I added time and geography in my view below.
Now, add the view to a dashboard page and create a filter on the hierarchy that you want to filter on. For my filter I added a Product filter and selected the "All" member as well as one level down in the hierarchy. Add the filter to your dashboard page and then link the filter to the analytic grid by Member UniqueName. Click the Filter Link Formula. button and enter your filter expression in the dialog there:
For my expression, I used a topcount expression and selected members at the Product level in my hierarchy that were descendants of the member selected in my filter. This becomes the set that will pass to my view and be shown in place of the "All" member I used in the initial view.
TopCount ((descendants(<<UniqueName>>, [Product].[Product].[Product])), 20, [Measures].[Sales Amt])
Publish your dashboard, note the results below. Because of the sort that was applied in the view, the view will always be sorted as well. Finally, note that in the last screenshot, you still have full navigation on the view.
Alyson Powell Erwin (alysonp@microsoft.com)
Have you ever wanted to use the ProClarity Report View in a PPS Dashboard and allow the view to be launched full-screen in ProClarity Web Professional (rich-client)? Or have you wanted the ability to cross-drill in a ProClarity Decomposition Tree when opened full-screen?
I found a great PerformancePoint & ProClarity tip recently , that addresses these needs. This tip came from one of our technical specialists out in the field, Howard Morgenstern, with assistance from one of our developers, Tim Morgan. Howard writes a blog called Howard@MSFT Selling BI. This tip will help you embed ProClarity Analytic Server (PAS) views in a PPS Dashboard using UI configuration parameters. Check out the article here.
The neat part about this workaround is that it gives you access to the entire PAS frameset, which in turn gives you access to additional ProClarity UI, like the ProClarity toolbar.
To make full use of this tip, it is useful to have some knowledge of PAS UI configuration parameters. This information is available in the PAS Administrators Guide, pages 90-94. Some of the more useful UI configuration parameters are shown below:
| UI Configuration Parameter | URL Parameter Equivalent | Description |
| tb | tabsVisible=False | Hides the ProClarity logo, UI tabs and left pane. Great for Dashboard use. |
| dt | dimToolvisible=false | Hides the Data Layout tab, preventing the user from changing hierarchies or member selections on rows, columns or background. |
| ht | contentNavigation=false | Removes the PAS library breadcrumb trail, contents tab and previous/next page icons. |
| fs | fullScreen=true | Adds a button to the PAS toolbar that opens the view in a new window. Can be used to open the view in ProClarity Web Professional. |
The following screenshots illustrate the use of the ProClarity toolbar in a PPS Dashboard view. First, here is a Decomposition Tree with the default ProClarity Report options.
Figure 1: Decomposition Tree with default ProClarity Report options
The following screenshot shows the use of UI Configuration parameters to display the ProClarity toolbar, with an icon for launching the Decomposition Tree in ProClarity Web Professional.
Figure 2: Decomposition Tree with tb;ht;fs options
Figure 3: ProClarity Toolbar, Icon to Launch Web Professional
I have found this workaround to be very useful. Thanks, Howard! It's great to get tips like this from the field. Any questions? Do you have a great tip/trick to share with the broader PerformancePoint community? Please comment on this blog, or shoot me an email.
Rex Parker, Program Manager, PerformancePoint Server
Email: rexpark@microsoft.com
Lastly, some Q&A:
FAQs
Q) I've been using the Web Page Report to show ProClarity views in a PPS Dashboard. This allows me to pass URL parameters, and gives me full control over the ProClarity UI. Why would I want to use the "server name" workaround?
A) The Web Page Report does not accept PPS dashboard filter values. The "server name" workaround allows you to configure the ProClarity UI and still accept PPS dashboard filter values.
Q) What is the purpose of the Configuration Options text box for the ProClarity Report View? Can I use this instead of the "server name" workaround?
A) This box allows the user to enter in UI Configuration parameters without using the "server name" workaround. However, the ProClarity Report View only renders a portion of the ProClarity frameset, and does not give the user access to additional portions of the ProClarity UI, such as the ProClarity toolbar. This is an issue that the workaround solves. Also, this workaround eliminates the need to manually copy-and-paste very long PAS URLs.
Q) I used this workaround with the "fs" parameter to give users the ability to open ProClarity views in ProClarity Web Professional (rich client) but I don't see the button in the ProClarity toolbar. What gives?
A) Two things to check: First, make sure that the users have been given access to Web Professional. This is done in the PAS Administration Tool. Second, make sure that the users have downloaded Web Professional. Once these conditions are met, the users should see the "globe" icon in the ProClarity toolbar, which is the icon for opening the view in Web Professional.
It can be difficut to navigate all of the information available for PerformancePoint Server. One of our UA writers, Jeff Carr, recognized this and has put together a great compilation of PerformancePoint Server sites.
It can be found here: http://blogs.technet.com/datapuzzle/archive/2008/03/21/help-how-to-guides-manuals-forums-for-microsoft-office-performancepoint-server-2007.aspx
I know Jeff is interested in your feedback, so take a look and let him know what you think!
There is a new ProClarity blog available at:
http://blogs.technet.com/proclarity/
PerformancePoint Monitoring Server assumes a top-level web site (or root site) is defined for a Microsoft Office SharePoint Server site collection. (This site would typically be accessed by a URL similar to http://Production.) If a top-level web site is not defined, Excel Services reports may not render in published dashboards.
This is likely the issue if your Excel Services reports are shown correctly in Dashboard Designer and the Preview site but display the Operation in Progress indicator (below) indefinitely, rather than the Excel Services report.
To resolve this issue, we recommend two options: 1) create a top-level web site for the MOSS site collection or 2) edit the web.config file for the SharePoint site collection, as described below.
Editing the Monitoring Server web.config file for the SharePoint site collection
1) Open the web.config file for the SharePoint site. By default, this is installed in the following location on the server running SharePoint Server: C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config.
2) In the <appSettings> element, add the following key, replacing "<<subsite>>" with the path to a properly configured subsite for your SharePoint installation:
<add key="Bpm.WebPartResourcePath" value="/sites/<<subsite>>/_wpresources/Microsoft.PerformancePoint.Scorecards.WebParts/3.0.0.0__31bf3856ad364e35/"/>
3) Save and close the web.config file.
Elaine Andersen (elainean@microsoft.com)
When integrating ProClarity Analytic Server (PAS) views in your PerformancePoint Monitoring Server dashboards you will need to install a hotfix on your PAS server in order for the Monitoring server to include the PAS views successfully. Without the hotfix, you will receive "404 Error" or "Page Not Found" errors when viewing the PAS pages in a Monitoring dashboard if the hotfix has not been installed. You can access the hotfix at the following location:
http://support.microsoft.com/kb/942523
Alyson Powell Erwin (alysonp@microsoft.com)
ProClarity Analytic Server (PAS) Performance Map views can easily be included in a PerformancePoint dashboard. However, you may quickly find that the view doesn't render quite as expected. Take a look at the following Performance Map view in the dashboard:
The first thing you will want to do when publishing a Performance Map view to the dashboard is hide the Performance Map setup pane when publishing the view to the PAS server. From ProClarity Professional, right click on the PerformanceMap view and select Performance Map Properties. Unselect the Show Performance Map setup pane option. Save the view and republish the PAS Briefing Book.
Now, refresh your dashboard page and you will see the change to the view. Note, a re-export of the dashboard is not required.
The second option you may want to change, although this one is more involved, is add a Performance Map cache size that will render better within your dashboard view. Notice the grey space on the right and left sides of the above Performance Map view. By default, the PAS server only renders and caches the Performance Maps in 4 predefined sizes. You can add additional image sizes as follows:
Edit the "PTreeViewCtrl.xml" file located here: C:\Inetpub\wwwroot\PAS\en\src
Add additional sizes as shown below, just increase the number of the image. (PerfMap5 and PerfMap6 were added in the file below.)
NOTE: PAS cache must be cleared after this change is made. It may take a few iterations before you find the size that best fits your dashboard view space. Additionally, PAS performance will be affected because we will now create more cache images of the view when initially viewed. Thus, try to minimize the number of additional images you are adding.
This can significantly increase the Performance Map view in your dashboard.
Alyson Powell Erwin (alysonp@microsoft.com)
Creating the Dashboard View
In v1, ProClarity Analytic Server views can be easily included in your dashboards and the dashboard filters can be linked to those ProClarity Analytic Server views.
First, you need to create and publish the ProClarity Analytic Server (PAS) views to your PAS server. Once you have this completed, you can create a new report view in the dashboard designer to reference this view.
In the report view properties, you will enter the following information:
· Server URL - this is the URL to the PAS server, typically of the form
http://servername:portnumber/pas
· Page - click the Browse, button to browse for the page on the PAS server
· Configuration Options - allow for controlling the options users have with the view
You cannot preview the view in the designer, to ensure the view is being incorporated correctly you will need to include the view in a dashboard page and then preview the dashboard or deploy the dashboard to SharePoint.
Security
Please keep in mind that PAS views will be rendered using the NT credentials of the user opening the dashboard page in SharePoint - these views DO NOT follow the Application Security model that the Analytic Charts and Grids native to PerformancePoint Monitoring follow. This will require Kerberos and Constrained Delegation to be configured if PAS is not on the same server as PerformancePoint Monitoring.
Linking Dashboard Filters to the View
The PerformancePoint Monitoring Filters can also be linked to the PAS views - both single select and multi-select filters can be used. To map the filters, drag the MemberUniqueName property of the filter to the PAS view and create the link as shown below:
For the Dashboard item endpoint select "Parameters", this is the only selection available.
For the Source value, ensure that the Member UniqueName is selected.
The dashboard filters will be successfully applied as long as the following conditions are met:
1. The hierarchy used in the dashboard filter must also exist in the cube that the PAS view is being rendered from. Without an exact match in hierarchies, the filters will not pass through successfully.
2. If the hierarchy in questions is shown on the rows of the PAS view, then the selected dashboard filter member(s) will be displayed on the rows. Same for columns. If the hierarchy in question is a not displayed on rows/columns, the dashboard filter will act as a filter on the PAS view.
Once the filters are connected and the dashboard is deployed, the dashboard filters will be passed to the PAS views.
Alyson Powell Erwin
alysonp@microsoft.com
Monitoring Server is part of Microsoft® Office PerformancePointTM Server 2007. This white paper is for PerformancePoint Server administrators and IT professionals who want guidance about how Monitoring Server components authenticate and connect to each other and data sources used to create and render reports. This guidance includes a description of the default configuration and identification of steps required to modify the default configuration to implement per user authentication.
Steve Pontello (Microsoft)
By default, dashboard filters remember the last selected item between sessions. This means user selections will be remembered, even if the dashboard is closed and reopened. For example, Anna opens the dashboard the first time and selects Illinois for District:
The dashboard content is updated for Illinois. Anna finishes her analysis session and closes her browser. The next week, Anna returns to the dashboard to see how her district is performing. Because her previous selection is remembered, Anna doesn't have to select Illinois again to filter the dashboard:
In some cases, you may want to disable this capability to allow the default filter selection to be used anytime the dashboard is opened. In our example above, the District filter would display "All" anytime the dashboard is opened. Anna's selection would not be remembered.
The following steps explain how to disable filter persistence in PerformancePoint Server:
1) Open Microsoft SQL Server Management Studio and connect to the Database Engine where the PPSMonitoring database is located.
2) Expand Databases > PPSMonitoring > Programmability > Stored Procedures.
3) Right-click dbo.ParameterValuesCreate and select Modify.
4) Comment out the following section of code, as shown in these images:
Start Comment Code
End Comment Code
5. Click Execute to make the changes.
To enable filter persistence again, remove the comment markers from the code and click Execute.
Elaine Andersen (elainean@microsoft.com)