Google
 

Wednesday, October 1, 2008

Snatched Moments - Create a NativeSQL Business Rule

It is usually recommended that all rules be written in PerformancePoint Expression Language (PEL) in to ensure upgrade compatibility.

However, some rules (either throwaway or complex) need to be driven by either Native SQL or MDX code.

Here is a blog posting outlining how to write a Native SQL rule.

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.

Snatched Moments - Create a NativeSQL Business Rule

No comments: