Jeremy Kashel's Blog

Renaming a PerformancePoint Planning Server

I had a strange situation to deal with a few weeks ago - the IT department at one of our clients wanted to rename the server that was running PerformancePoint Planning, and wanted to know if that would be ok! This was of course a bit of a worry - its not something that I've seen documented, so I wasn't sure what impact it would have on models, form templates, etc.

I was aware from the Planning Server Mirroring Technique that there are several references to the server name within the application, system and service database on a Planning Server. My first step was to follow the aforementioned mirroring technique, and to update all the necessary tables.

The mirroring technique also instructs you to update the config file called PerformancePoint.config, which can be found in Program Files under \Microsoft Office PerformancePoint Server\3.0\Config\.

After updating the tables/config files, everything seemed fine at first, but then I noticed a couple of problems. Although you can of course edit it, the server connection text box in PAC always defaulted to the old server name. In addition, and more of a concern, the auditing and tracing had stopped working.

Getting the sever name to default correctly was taken care of quite easily by updating the following XML that's found at \Program Files\Microsoft Office PerformancePoint Server\3.0\AdminConsole\Web.Config:

<add key="PerformancePointServer" value="http://SERVERNAME:46787/" />

Getting the auditing/tracing to work again required a bit more digging, as you can't change the 'audit writer computer name' that's found in the ManageAuditing.aspx page in PAC. These locked values for auditing are held in the dbo.Configuration table of PPSPlanningSystem, in an XML column, as follows:

<PerformancePoint.audit highLimit="1048576" pollInterval="60000" fileName="D:\Microsoft
Office PerformancePoint Server\3.0\Audit Log\audit.log" writerMachineName="SERVERNAME" />

<listener maxLength="10" maxBackup="9" writerMachineName="SERVERNAME" fileName="D:\Microsoft Office
PerformancePoint Server\3.0\Trace Log\server.log" format="single" name="FileLog" type=
"Microsoft.PerformancePoint.Planning.Common.Configuration.FileListenerElement, Microsoft.
PerformancePoint.Planning.Common, Version=3.0.0000.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />

After updating the above values, everything returned to normal, and the auditing/tracing kicked in immediately.

Finally, one thing to think about if you ever have to do this, check that no local administrators have objects checked out! As you are renaming the sever, your local admin account will belong to a different server name, meaning check outs to OldServerName\Username will be invalid.

Troubleshooting the PEL Allocate Statement

The PEL Allocate statement can sometimes throw unexpected error messages, especially when you're setting up a pull or push cross model allocation rule.

I've been keeping track of a few of the more obscure ones, which I've listed in the table below. Hopefully this will help anyone out there who's struggling to get the cross model rules working.

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!