Using NativeSQL Business Rules for Seeding or What Ifs

You need to give really careful consideration to procedural business rules in PerformancePoint Planning. This is where multi-dimensional knowledge and experience helps, as you need to know whether its best to use a SQL or MDX implementation for your rules.

User Requirements

One area where I’ve found that this is particularly relevant is user requests for procedural rules that accept parameters that are member(s) of a dimension. E.g. Lets run the rule for the selected member in the entity dimension, perhaps to carry out a what if on a particular entity. This is fine for a single member, but on occasion I’ve found that the users want to pick either a)a leaf level member (which is fine) or b)a non-leaf member, and for the rule to apply the logic to all descendants of that member.

The Technical Problem

What technical problem? You can use the descendants function to achieve this right? You can with an MDX rule, but you can’t with a SQL rule. So, surely just use an MDX rule then?

This is where you have to think relational world Vs OLAP world. Putting a statement such as Descendants($Entity$, 10, leaves) in the scope of your rule will mean that when the rule is run, then it will always operate on the leaves of the dimension, regardless of whether you pick a leaf or non-leaf member. But the issue is that if your scope statement covers cells that have definition rules against them, then an MDX query will pick up the results of of those cells and and shove them into the fact table!

So can you use the Except() function or something similar to filter out those cells that you don’t need? Yes you can, but not if the members start to span different levels, the compiler won’t let you do that. As I said, you can’t use a SQL implementation because using Descendants($Entity$, 10, leaves) in the scope statement will tell you – “A SQL implementation is forbidden by a parameter or variable reference in a MemberSet expression unless the reference is the entire expression.”

The Solution

Rules that operate on fact data should really use SQL, its quicker and avoids hitting calculated data. I’m sure that you’ve seen this coming a mile off (!), but one way to go about this is to use a NativeSQL rule.

You can reference database objects in your NativeSQL rule, one of which will make it easier for you carry out descendant/hierarchy type functions in SQL itself. A function will get created in the Calc schema of your Planning application database in the format fnCalc_0_DimensionName_MemberSetName_IsAncestorOrSelf – this will occur when you deploy your model site. Using this function in conjunction with NativeSQL will allow you to get the leaves of a member quite easily.

The following NativeSQL rule creates a copy of one scenario’s data into another scenario, which is referenced by a parameter:

--Although integers, the parameters are actually 'members of a dimension' parameters
create proc Calc.[sp$procedure] @scenario int, @entity int as
begin
begin transaction

insert into

dbo.[tbl$factTable] (alldim$except(dim$Entity, dim$Scenario), dim$Entity, dim$Scenario,
    column$ruleId, Value, LoadingControlID, bmo$CreateDateTime, bmo$ChangeDateTime, ContextID)
    select alldim$except(dim$Entity, dim$Scenario), dim$Entity, @scenario, $ruleId, Value, -9, getutcdate(),
    getutcdate(), -1
    --No need to hard code the fact table name
    from dbo.[tbl$factTable] t
    inner join
    (
        select e.memberId from dbo.d_Entity e
        --Get leaves of the member key that is in @entity
        where Calc.fnCalc_0_Entity_Divisions_IsAncestorOrSelf(@entity, e.memberid) = 1 and
        e.memberid not in (select h.parentmemberid from dbo.H_Entity_Divisions h)
    )    lv on lv.memberid = dim$Entity
    where t.dim$Time >= 200701 and t.dim$Time <= 200712 and (dim$Scenario = 1)

if @@error <> 0
rollback transaction
else
commit transaction

end

I suppose the above rule may look a little bit daunting, due to all the dollar signs everywhere. These are just the preprocessor commands, which will get replaced when the stored procedure gets created. But other than that, its just an insert statement. In some cases the syntax is actually quite handy, as you can use statements such as alldim$except(), which is a short hand way of returning fact table dimension column names very easily. The idea behind the preprocessor commands is that you can protect the logic in your rule from minor structural database changes.

The key is that when the rule gets run, it has normal parameters, meaning the user will see the following interface:

In the case above, the member key of North America Operations will end up in the stored procedure, as I have a parameter in the stored procedure called @entity. Inside the stored procedure it is then used in combination with the IsAncestorOrSelf function to pick up the leaf level descendants.

The only caveat that I will make is that this hasn’t come from a production environment, so I’ve skimped a bit on a few of the operational details such as deleting the existing data, decent error handling and returning the number of records affected. These are all things that you should add in if you use this kind of thing in a production environment!