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