If you want to have a nice look for the matrixes that you create in Performancepoint you want to use the Matrix Styles as this gives you a nice and easy way to make sure that all the matrixes have the same formats. But what do you do when you don’t agree with the build-in styles? Say you would like to change the colors to better match your organisation.

It is pretty easy, but might also take some time to get just the right matrix style. The matrixes styles are based on the “PivotTable Style” in Excel 2007.

The first thing you need to do is to create a small PivotTable otherwise you wouldn’t get the right selects of formats that you want to change.

When you are standing in you PivotTable you have to use the  “Format as Table” in the Home Ribbon and in the Styles section to change/create a PivotTable Style:

The best thing is to select one of the existing formats and select the duplicate function in the right click menu. Now you can change the styles you need to get the Matrix Style you want for your Performancepoint matrix with a little trial and error.

In some difficult situations you might want to use NativeSQL to implement a business rule. It gives you the full power of SQL Server.
 
First you need to create the rule in the Business Modeler to host the NativeSQL business rule. You can use parametres and even use members of dimensions as parameters. When it is time to execute the business rule the user gets a nice interface where the user is able to select the parameter in the dimension.
 
In the SQL it is the memberId of the dimension that is passed into the function and they are of the type int. A start of an implementation can be seen here:
 
create proc Calc.[sp$procedure] @entity int, @scenario int, @startTimePeriod int, @endTimePeriod int as
 
It looks almost as a normal stored procedure in SQL. The only difference is that the name is a preprocessor commands and the Performancepoint translate this to a real stored procedure name, when it is created by the Performancepoint in the SQL Server. The rest of the parametres is just like a normal stored procedure.
 
You implement your stored procedure just like you normally would with transactions, select, insert and delete statements. It can be a good idea to use SQL Server Management Studio to do some testing on the implementation before you add it to Performancepoint. In this implementation it is possible to use more preprocessor commands to reference different kind of columns.
 
When it is time to deploy you have to have the NativeSQL listed as a InActive rule for you to be able to deploy it with the Business Modeler. After this is done you have to have database access to change the bit field in the “RuleSetsOrRules” table from false to true. Then you have to go back to the Business Modeler to do a deployment of the rules again (after a refresh) and it at this time the the Performancepoint creates the stored procedure and if there are any errors in the sql like “Ambiguous column name” or “Invalid column name” they can be found in the Event Viewer. If all this goes well the Business rule is ready to be executed.
 
You can see the implementation of the Business Rule by expanding your stored procedures in you application database and check under the name “Calc.bsp_GeneratedCalc_[Businessmodel label]_Rule:[Business rule label][ModelId]”.
 
A small tip: Check out the the NativeSQL implementation of “Chart_CarryForward”, “Chart_GAP_Movement”, “Chart_Batch_GAP_Movement” and “Chart_Seeding” in  the Financial Model without shares calculation for some tip on how NativeSQL can be used

What do you do when you want to use the description field in the time dimension in Performancepoint Server 2007?

The reason for this question is that I need to be able to show in an Excel input form which days are Easter each year, when is summer vacation and so on. They need this information to do better planning. The most logical answer would be to add this information to the Time dimension as it is information that is closely related to the dates in the Time dimension. You can't add a new field to the Time dimension, so I thought I use the description field which is already in the time dimension. The only problem is that you can't change the description field in the Planning Business Modeler.

I tried to change the table directly in the SQL Server and then did a Deploy Model Site in the Planning Business Modeler. You can see the result here:

This isn’t supported by Microsoft as you go and change something that isn’t change with the normal API or user interface, but it would be a nice change to have in the future 

I don’t know if this will be the final solution, but in case the solution changes or if I find out any side effects from this. I will update this blog entry.