Jeremy Kashel's Blog : PerformancePoint

Audit Trail in PerformancePoint Planning

Jeremy Kashel — Mon, 15 Dec 2008 15:49:05 GMT

I've noticed that the PPS Technet documentation has been updated recently to include an official Microsoft method to carry out auditing in PPS Planning.

PPS will do some basic auditing out of the box, namely to the audit.log file on the server. This will automatically capture key events that occur on the server, e.g. creation of a model, updating of a dimension etc. The audit file does not, however, track changes to the model fact data. There has been a custom solution around for this for a while now - Sacha has written an excellent post here that details what you need to do in order to implement your own PPS audit trail.

Like Sacha's method, the Microsoft approach involves creating auditing tables, which should then be populated by running a custom stored procedure. The stored procedure should then be scheduled on a periodic basis (e.g. hourly) to capture any new activity. This is a bit different to Sacha's method, where triggers are used to capture changes in real-time as they occur. In both cases the idea is to use something like Reporting Services to to view detailed auditing reports on your PPS data.

One thing that did catch my eye on in the Technet documentation is a method to decode the binary 'change list' column that's held in the dbo.Submissions table. Whereas you can manually export the change list to a CSV file, there has historically been no way to take what's in the change list column and automatically decode it into a useful format. The following C# code will read the change list, and then insert it into your newly created auditing table:

DataSet ds = new DataSet();
DataLayer dl = new DataLayer("PPSConnection");
ds = dl.ExecuteDataSetFromSQL("SELECT [SubmissionID]FROM [_AppDB].[dbo].[Submissions] s1 where 
s1.SubmissionID not in (select SubmissionID from [_StagingDB].[dbo].[SubmissionsAudited]) 
and s1.[Status] = 0");
string sSQL = "";

foreach (DataRow r in ds.Tables[0].Rows)
{
    sSQL = @"INSERT INTO SubmissionsAudited(… ) VALUES(";

    // RETRIEVE THE CHANGELIST FOR THIS SUBMISSION 
    DataSetWrapper dsw = new DataSetWrapper((Byte[])r["ChangeList"]);
    foreach (DataRow cldr in dsw.DataSet.Tables[0].Rows)
    {
        // SUBMISSION ROW DATA
        sSQL += r[0].ToString() + ", "
        + r[1].ToString() + ", "
        + r[2].ToString() + ", "
        + r[3].ToString() + ", '"
        + r[4].ToString() + "', ";

        // CHANGELIST ROW DATA
        foreach (object o in cldr.ItemArray)
        {
            sSQL += "," + o.ToString();
        }
        sSQL += ")";
    }
    // STORE EACH CHANGE TO THE AUDIT TABLE
    dl.ExecuteNonQuery(sSQL);

Click here to view the Technet documentation.

Technorati Tags: PerformancePoint,SQL,C#

Entering Dates in PPS Planning Assignments

Jeremy Kashel — Thu, 27 Nov 2008 09:14:57 GMT

In the recent PPS Planning projects that I've been involved in, the challenges have often been around subjects such as business rules, hence the often recurring theme of this blog. Recently the tables were turned though, as I was told by a user that they wanted to enter dates into a PPS assignment.

I was initially a bit concerned that the Excel add-in may not be able to deliver here - after all its great at capturing numbers, but knowing the rigid structure of the fact tables, I couldn't see how it would manage to store a date. Then I remembered something from my VBA days many years ago - that is that Excel stores dates as a number from 30/12/1899, meaning in theory it should be possible to get dates working in PPS. Thankfully it is possible, as this post explains.

Excel Setup

The first step to get this working when designing your form template is to set the matrix to have a matrix style of 'none'. If you don't do this, then the built-in matrix styles will over-ride your formatting changes to the required cells. Speaking of formatting, the next step is to format the data entry cells that will contain dates, just using the standard Excel formatting window:

Once these few simple steps are done, then the assignment will behave just like any other. As the date is stored as a number, the numeric representation of the date will end up in the fact table just as any other piece of data.

Dates in Business Rules

Once the numbers are in the fact table, we need to convert them to dates to use them in business rules in some way. We can't do much in PEL unfortunately, so the options are either NativeMDX or NativeSQL.

As Analysis Services can pickup some of the VBA functions, it's possible to use the VBA DateAdd() function to convert the stored number back into a date. So in the example below, I'm using the DateAdd() function to convert the number to a date, before comparing the resulting date against another date using the VBA DateDiff() function:

WITH 
MEMBER    [Measures].[DateExample] AS VBA!DateAdd("d", [Measures].[Value], "30/12/1899")
MEMBER    [Measures].[DateDiff] AS VBA!DateDiff("d", [Measures].[DateExample], "01/07/1987")
SELECT    Descendants([Time].[Monthly].[Year].&[2008],,leaves) ON 0
FROM      [Strategic Planning]
WHERE     ([Account].[Profit and Loss].&[5010], [Measures].[DateDiff], [Entity].[Divisions].&[5003])

Although the above is just a simple example, it should give you the idea of the kind of calculations that can be performed in Analysis Services. It's possible to use these functions via a NativeMDXScript or a NativeMDXQuery.

It's a similar story with SQL, as it also has its own DateAdd() function, as shown in the simple select statement below:

SELECT    DateAdd(d, [Value], '30/12/1899')
FROM      dbo.[MG_Strategic Planning_MeasureGroup_default_partition]
WHERE     Scenario_memberid = 4 AND Account_MemberId = 5010

So it's a shame that PEL can't work with dates, but the fact that both the database engine and Analysis Services have a DateAdd function means that it's possible to use dates for logic in both definition and procedural business rules.

Technorati Tags: PerformancePoint,MDX,SQL,VBA,Excel

NativeMDXQuery Business Rules in PerformancePoint Planning

Jeremy Kashel — Fri, 24 Oct 2008 10:25:53 GMT

Having posted about NativeSql business rules a while back, I though that I might as well cover NativeMdxQuery business rules also, especially as there isn't too much documentation available on the web for this subject.

NativeMdxQuery is a rule implementation type that can be used with assignment rules in PPS Planning. Rather than writing PEL and having the compiler convert your PEL into SQL or MDX, the idea is that you write the raw MDX directly if you pick NativeMdxQuery.

Why Native Rules?

When when I posted about NativeSql, I mentioned a scenario or two where using NativeSql was very useful. The advantages to writing a NativeMdxQuery, however, are less obvious. This is especially the case when you consider that a)standard MDX rules in PPS Planning are less restrictive that the SQL rules, and b) that PEL is very efficient - you can write a very concise MDX PEL rule vs writing the equivalent in MDX itself.

So what advantages are there? Although a fair amount of MDX functions are included in PEL, it's not possible to use all MDX functions/statements/operators, e.g. Case(), Item(). This is one situation where you might want to write a NativeMdxQuery.

Also, the ability to filter and restrict data in a raw MDX statement is far more powerful than the options available in PEL. For example, you can use the Filter()/IIF functions in PEL, but they're quite slow and you're restricted as to where you can put them. If you use MDX, you can use both a WHERE clause to slice the query and/or a HAVING clause for filtering.

Finally, when just writing queries from an SSAS cube, a technique that's sometimes used is to create query-scoped calculated members, by using the MDX statement WITH MEMBER. This allows you to have calculation steps in your MDX query, and can effectively be used in one sense as a temporary variable store for calculations. If you're trying to do complex calculations in PEL, you have to assign everything to the 'this' keyword. It's much cleaner to have a block of code where you can define any calculations that the main query depends on. This is what WITH MEMBER will let you do.

How to Write a NativeMdxQuery

The requirements for a business rule using NativeMdxQuery are that you need to write an MDX select statement, which specifies the target cells that will be written to. Unlike NativeSql statements, you do not need to handle how the data gets inserted - PerformancePoint handles all that for you, as long as you produce the MDX select statement.

An example select statement that makes up the entire content of a NativeMdxQuery assignment rule is shown below. The comments show what the query is doing:

--[Measures].[Last Year] just makes the rule a bit more readable
WITH MEMBER [Measures].[Last Year] AS ([Measures].[Value], [Time].[Monthly].CurrentMember.Lag(12))
CELL CALCULATION queryCalc 
FOR 
 --This specifices the cells that we are overwriting with an expression or value
 '([Measures].[Value],
 [Scenario].[All Members].[Scenario].&[1],
 {[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]},
 {[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]},
 [BusinessProcess].[Standard].[Level 06].&[8],
 [TimeDataView].[All Members].[TimeDataView].&[1],
 Descendants([Entity].[Divisions].[(All)].&[0], ,leaves),
 Descendants([Currency].[All Members].[(All)].&[0], ,leaves),
 Descendants([Product].[Product Category].[(All)].&[0], ,leaves))' 
AS
 --The 100 is the value that we are giving the cells above
 100
--This is the select statement which cells will receive the value above
SELECT NON EMPTY 
 ([Measures].[Value], 
 ({[Scenario].[All Members].[Scenario].&[1]},
 {{[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]}},
 {{[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]}},
 {[BusinessProcess].[Standard].[Level 06].&[8]},
 {[TimeDataView].[All Members].[TimeDataView].&[1]},
 {Descendants([Entity].[Divisions].[(All)].&[0], ,leaves)},
 {Descendants([Currency].[All Members].[(All)].&[0], ,leaves)},
 {Descendants([Product].[Product Category].[(All)].&[0], ,leaves)}))
 --Ensure we only write to cells with a certain value by using HAVING
 HAVING ([Measures].[Last Year] > 100000)
 properties [Scenario].[All Members].Key ,
 [Time].[Monthly].Key ,
 [Account].[Profit and Loss].Key ,
 [BusinessProcess].[Standard].Key ,
 [Entity].[Divisions].Key ,
 [TimeDataView].[All Members].Key ,
 [Currency].[All Members].Key ,
 [Product].[Product Category].Key 
 ON COLUMNS 
FROM [Strategic Planning]
--Filter on a dimension member property by using a WHERE CLAUSE
WHERE ([Entity].[Region].&[North])

The points to note about the above statement are:

You must connect to the correct cube for the current model;
You don't need to include a cell calculation - but it's the way that Microsoft implement business rules that use MDX, and it's hard to see how you would get a rule to be of any use without it;
You must include the member keys as properties, otherwise the rule will error.

Conclusion

Writing MDX is clearly not for all users of PerformancePoint, but does provide the ultimate in flexibility when compared to PEL. Most MDX queries written in PPS will use cell calculations. If you're not used to how these work, or you just want to save some time, remember that an easy way to get started is to use the debug button on an MDX PEL rule in PBM - this will output a query that is similar to the one shown above.

Technorati Tags: PerformancePoint,PPS,MDX,NativeMdxQuery

BI for IT Professionals using PerformancePoint

Jeremy Kashel — Tue, 30 Sep 2008 21:07:18 GMT

The PerformancePoint downloads page has been recently updated to include a framework on how BI can provided to the IT Department.

Normally the IT Department would be helping out with supporting BI solutions, not actually being end users themselves - but it makes sense when you consider the kind of information that Operations Manager captures.

As this video explains, the end goal is to create solutions that will allow effective monitoring the IT infrastructure. An example of the kind of the kind of dashboards that can be produced is shown below:

There is white paper and also a sample solution available for download to learn more.

Technorati Tags: PerformancePoint,System Center,Dashboards

Tracking PerformancePoint Planning Submissions Using Reporting Services

Jeremy Kashel — Fri, 26 Sep 2008 16:29:29 GMT

The standard operational reports that come with PerformancePoint will allow you to report on a variety of PerformancePoint admin related activities, such as cycles, assignments, forms, jobs and associations.

I find that the assignments report is particularly useful - after all, finding out who has/hasn't submitted is an important part of any data-gathering exercise. Whilst it is useful, I do find that the assignments report is the one that admin users want changed, especially when a model site exists containing many cycles and assignments.

Extra Functionality

With a large PPS Planning implementation you can easily end up with many assignment instances, cycles and users. I've been involved in such an implementation recently, and, due to the large number of assignments, the admin user requested a bit more filtering capability than the out of the box assignments report provides.

Also, the existing assignments report tells will tell you that user A has submitted their assignment, but it won't go into any detail about what the submission actually contained. E.g. did the user submit all their entities? For some users it is quite key to know what other users have been submitting - for one thing it makes navigation easier if as an approver you know exactly which department/entity to pick in the assignment filters.

Examples

By knowing which tables to use, you can write an SSRS report that provides the additional functionality mentioned above.

The starting point is to get the base report query right. In my case, as I'm in a separate auditing database, the query goes inside a custom stored procedure, and is as follows:

SELECT              A.AssignmentId,    C.CycleInstanceName, AD.AssignmentDefName, A.AssignmentName,
                    U.UserId, U.UserName, ENT.Name AS EntityName, CUST.Name As CustomerName,
                    CASE
                        WHEN A.Status = 'partial' OR A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END AS Draft_Submitted,
                    CASE 
                        WHEN A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END As Final_Submitted,
                    CASE
                        WHEN A.Status =  'Approved' THEN 1
                        ELSE 0
                    END AS Approved, Approve.UserName As Approver
FROM                dbo.Assignments A 
LEFT OUTER JOIN     dbo.[MG_Planning_MeasureGroup_default_partition] Fact
                    ON A.AssignmentID = Fact.AssignmentID
LEFT OUTER JOIN     dbo.AssignmentDefinitions AD ON AD.AssignmentDefID = A.AssignmentDefID
LEFT OUTER JOIN     dbo.CycleInstances C ON C.CycleInstanceID = A.CycleInstanceID
LEFT OUTER JOIN     dbo.D_Entity ENT ON ENT.MemberId = Fact.Entity_MemberId
LEFT OUTER JOIN     dbo.D_Customer CUST ON CUST.MemberId = Fact.[Customer_MemberId]
LEFT OUTER JOIN     dbo.BizUsers U ON U.UserID = A.ContributorUserId
LEFT OUTER JOIN     dbo.ApproverList AL ON AL.AssignmentID = A.AssignmentId
LEFT OUTER JOIN     dbo.BizUsers Approve ON Approve.UserID = AL.ApproverUserID

You can figure out most of the tables to use by looking at a view called AssignmentsView within the application database.

One thing that I have taken into account is assignment definitions. If you have large number of users completing an assignment, then the chances are that you will have set up an assignment definition that points at a business role or a submission hierarchy. You ideally want to be able to filter on the assignment definition to return all assignment instances that belong to that assignment definition. Therefore, in my case I have three filters for the report, but you could easily add more:

The final view is a report that shows the status of the assignments returned by the filter, but also, when expanded, shows the the entities and customers that the contributor has submitted:

The above is just a taster of what can be achieved. A couple of ways that it can be extended include:

Integrating with Sacha's data auditing idea to provide detailed history on what values the contributor has changed;
Including comments, annotations and deadlines.

Technorati Tags: PerformancePoint,Planning,PPS,SSRS,Reporting Services,SQL Server 2005

Analysis Services Properties for PerformancePoint MDX Rules

Jeremy Kashel — Sun, 07 Sep 2008 20:55:05 GMT

One of the great features of PEL is that you can choose either a SQL or MDX implementation for your rules, depending on what you want to achieve. Whilst the MDX rules are much less restrictive than the SQL rules, they can sometimes run slower, depending of course on how your environment is set up.

When the MDX rules do take a long time to run, it's possible that you might see this message:

What has essentially happened within Analysis Services is that an object (e.g. a cube or dimension) has been processed and is waiting to commit. Unfortunately this is not possible whilst an existing query is running, so therefore AS waits for the query to complete. It will, however, only wait so long, which is defined by one of the Analysis Services Properties called ForceCommitTimeout. Once this threshold has been reached, then the offending query is canceled, resulting in the error message above.

Finding the right balance for the Analysis Services ForceCommitTimeout and the PerformancePoint PAC 'OLAP Cube Refresh Interval' setting is key. If you have set PPS to re-process its cubes too often then you may well see the above message. On the other hand, if you set the ForceCommitTimeout too high, then queries executed whilst the cube is waiting to commit will be made to wait, meaning your big query will get though ok, but other users may see bad performance.

Darren Gosbell has written an excellent post here that provides a thorough explanation of ForceCommitTimeout and other related properties.

Technorati Tags: Analysis Services,MDX,PerformancePoint,PPS

Renaming a PerformancePoint Planning Server

Jeremy Kashel — Sun, 31 Aug 2008 16:42:37 GMT

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.

Technorati Tags: PerformancePoint,PPS

Troubleshooting the PEL Allocate Statement

Jeremy Kashel — Wed, 13 Aug 2008 22:01:15 GMT

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.

Error Message

Solution/Explanation

No hierarchy ‘xyz’ exists in [dimension]

Check that you have specified the correct member set in the source, target and scope parts of the rule.

Member set is not defined correctly for cross model mapping. The number of members in source and target member sets in cross model mapping must match.

This message can have 2 meanings. Firstly, as it suggests, it can be that the number of members in the source and target mapping arguments do not match. Or, prior to SP1, it can be this rather more serious problem - http://support.microsoft.com/kb/942640

Execution of rule ‘xyz’ failed. This may have occurred because changes have been made to the model structure without redeploying the model site.

This can occur at execution time when you try and map two members twice. E.g. if your source is {2009, 2009} and your target is {Jan, Feb}. It doesn’t like the 2009 in there twice.

Each dimension scope in cross model mapping must be a list of concrete dimension members

Unfortunate but true – you can’t use relative member functions such as Descendants in the 4^th and 5^th arguments of an allocate statement.

Some of the model's dimension members are invalid or missing. The model may need to be redeployed.

This is a run time error that can occur when you are trying to write to non-leaf members. The first argument of the allocate statement will contain the problem, as this is where the cells to receive the allocation are specified. Often it’s because ‘this’ has been entered as the first argument, which could result in using non-leafs for the argument, as it will inherit any non-leafs specified in the rule’s scope statement.

Allocation statement must have 5 parameters when they appear in Cross Model Allocation Rule.

Cross model allocation rules expect 5 parameters, in the correct format. So if you don’t have 5 parameters, then there’s the problem. However, be careful how you specify the parameters. For example, if you specify 2 members in the source mappings, such as [Time].[Monthly].[2007], [Time].[Monthly].[2008], then be sure to enclose them in brackets, otherwise the 2^nd member will be interpreted as your 5^th argument.

Unable to compile the cross model allocation rule. The target contains the dimension ‘xyx’ but no explicit mapping has been provided.

If an extra dimension(s) exists in the target model, the allocate statement requires you to specify what member you expect the data to be written to in that dimension.

Technorati Tags: PerformancePoint,PPS,PEL,Cross Model Allocation

Using NativeSQL Business Rules for Seeding or What Ifs

Jeremy Kashel — Tue, 05 Aug 2008 11:45:00 GMT

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!

Technorati Tags: PerformancePoint,SQL,NativeSQL,Business Rules

Multiple Assumption Models in PerformancePoint

Jeremy Kashel — Tue, 24 Jun 2008 22:02:16 GMT

PerformancePoint gives you the flexibility to have multiple assumption models to use as you please in your main models. Although this is great, I've found a problem when the two assumption models have different member sets for the same dimension, and so in an extension to my last assumption model post, this post provides a workaround for the issue.

Consider the following example. I've got a main model where I want to use 2 assumption models, namely:

HR Assumptions - Uses the HR member set from the BusinessDriver dimension;
Strategic Assumptions - Uses the Strategic member set from the BusinessDriver dimension.

If you go and add the two assumption models to the main model at the same time, then everything looks normal, as shown in the screen shot below:

Once you deploy successfully, you will of course want to write a business rule to pick up the assumption model data. However, when writing the rule and trying to pick from the BusinessDriver member selector, you will see that you can unfortunately only select from one member set, as shown below:

If you need to write rules that reference specific members in both member sets, then you will be out of luck. It's not even possible in any kind of native MDX rule, as the main model cube that gets created in Analysis Services only contains the dimension that has been created from the 'HR' member set. It would seem that PerformancePoint just picks the member set that is first alphabetically.

The workaround for this issue is simply to create a single member set that combines the two original member sets. Therefore, each assumption model will contain more members than required, but that's far better than not being able to write the rules that you need.

So just something to be aware of, and catch, at design time - rather than in the middle of your build.

Technorati Tags: PerformancePoint,PPS,Assumptions

Executing Rule Sets in PerformancePoint to Move Data

Jeremy Kashel — Fri, 16 May 2008 09:15:59 GMT

As I mentioned in my post a few weeks a ago, the PEL Allocate statement is a powerful method of moving data between PerformancePoint models.

Although it's powerful, the end result of an Allocate statement is simply that the destination model will contain data that has been queried from the source model. What if the destination model already contains data for the target scope? If this is the case, then you will have double counting. When creating the Allocate rule, there is unfortunately no option that lets you decide what you want to do with existing data. Ideally you need to have the 'Existing Data' option that you get when running an association, which gives you the 'Append', 'Scoped Replacement' and 'Full Replacement' options.

To get around this, I tend to place two rules inside a rule set. The first rule is a SQL implementation assignment rule with the same scope as the allocate rule, and deletes existing data by using the PEL statement this = NULL inside the rule. Then the second rule is the allocate statement, which appends the data. This is shown below:

The idea is then to execute the rule set, which will execute each of its rules in order. The advantage to doing this is that your whole data movement process can be executed in one clean step (perhaps scheduled or via Excel), without the hassle of executing several individual rules.

Technorati Tags: PerformancePoint,PPS

Assumption Model Design Techniques and Business Rules

Jeremy Kashel — Thu, 08 May 2008 13:57:05 GMT

PerformancePoint assumption models are very useful if you want the data in one model to drive and affect another model. However, if you’ve been using assumption models, then the chances are that you may have noticed that they can sometimes be a bit awkward to use. This post highlights a couple of issues that I’ve experienced with assumption models, and provides a few workarounds.

Firstly, when you initially link the assumption model to the main model, you may get the error message ‘the assumption model uses different member sets for the following dimensions’, as shown below:

Lets presume that you ignore this warning and continue to link to the assumption model. Further down the line, when you come to write your PEL rule to pick up the assumption model data, you may find that you get an error when validating the rule. This will occur if you want to populate a member in one member set by referencing a member in another member set, which resides in the assumption model.

To illustrate this, if we want to populate an account called ‘Total Sales’ in the ‘Operational’ member set of the main model by using an account called ‘Price’ in the ‘Drivers’ member set in the assumption model, then we would have the following PEL rule:

scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales]) ;
     
this = [Account].[Operational].[Volume] * (MODEL([Sales Assumptions]), [Account].[Drivers].[Price]);

end scope;

However, when validating this rule we get told “No hierarchy Drivers exists in dimension Account”. The only account member set that PerformancePoint will let you use in the above rule is 'Operational'. Note: you'll be ok if 'Price' exists in the main model, but you will just need to refer to it as [Account].[Operational].[Price]. But if 'Price' doesn't exist in the main model, then a different approach is needed.....

The simple way around it is to use an approach found in the Alpine Ski House sample application. Add the accounts (if that’s the dimension causing you problems) into another dimension called Business Driver. You can then use the Business Driver dimension in your PEL rule, as PerformancePoint is more than happy for you to reference assumption model specific dimensions. This is shown below:

scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales]) ;

//The rule is almost identical, but validates ok as we're using the BusinessDriver dimension.     
this = [Account].[Operational].[Volume] * (MODEL([Sales Assumptions]), [BusinessDriver].[Drivers].[Price]);

end scope;

Another problem that you may experience is when you have a difference in granularity between member sets used in the assumption model and the main model. For example, you may have an Entity member set called ‘Regional’ used in the assumption model, but a more detailed member set used for the Entity dimension in the main model.

So in the above example, we want to populate members in the more detailed 'Company' member set by using a value from the member's parent in the higher level 'Regional' member set. In order to do this, we can just use the Parent function on the current member of the main model's entity member set. Even though we've referenced the main model member set inside the MODEL function, if the current member's parent exists in the assumption model, then it will pick it up without any problems:

scope (
[Scenario].[All Members].[Forecast],
[Account].[Operational].[Sales],
Descendants([Entity].[Company].[TOT], 1073741823, leaves)) ;
     
this = [Account].[Operational].[Volume] * 
      //Its ok here to use [Dimension].[Main Model Member Set].CurrentMember.Parent
      (MODEL([Sales Assumptions]), [Entity].[Company].CurrentMember.Parent,
      [BusinessDriver].[Drivers].[Price])

end scope;

So both very simple design techniques, but it’s worth being aware of them before you actually start building your models.

Technorati Tags: Assumptions,PerformancePoint,PPS,PEL

Intercompany Eliminations Video for PerformancePoint Planning

Jeremy Kashel — Wed, 30 Apr 2008 18:33:47 GMT

Useful post on Norm's PerformancePoint Server Blog showing a short video on how to do intercompany eliminations in PerformancePoint.

The video is actually by Forrest Dermid, who sets the scene by explaining why you would actually want to do an intercompany elimination. He then goes on to show the relevant areas of Planning Business Modeler, demonstrating in detail the changes that you need to make to your account dimension and model properties, amongst others.

Really worth watching in my opinion. You can find it here.

Technorati Tags: PerformancePoint,Intercompany

PerformancePoint Planning Performance Tuning White Paper

Jeremy Kashel — Tue, 15 Apr 2008 15:38:00 GMT

A PerformancePoint Planning white paper has recently been published that documents the performance and scalability of Planning Server.

Included are some very interesting test results that will come in handy when planning the deployment of PPS for a large number of users. There are also a few tips for the Excel add-in and configuration of the Planning Server.

The following page contains links to both the new Planning white paper and also to a Monitoring white paper that was published a while ago:

http://technet.microsoft.com/en-us/library/bb660521.aspx

Technorati Tags: PerformancePoint,Planning,Scalability,PPS

PEL Allocate Statement Vs Associations

Jeremy Kashel — Sun, 06 Apr 2008 22:33:00 GMT

PerformancePoint provides you with a few options when you want to move data between models, the most obvious of which is Associations. However, there is also the powerful PEL Allocate statement....

So which method should you use to move data? Associations are very easy to setup and will transfer your data for you - all out of the box. However, they do have a few limitations, namely:

You can only apply a minimum, maximum or total aggregation to the source data;
You can't have parameters to dynamically select members;
If you delete a dimension that's used in an association, then you can't view or edit the association. Although there should hopefully be no need to do this in production(!), it can be a bit annoying during development.

By writing a PEL Data Movement rule of type PushCrossModelAllocation or PullCrossModelAllocation, we get bit more flexibility. With the PEL Allocate statement you can:

Use PEL functions or operators to alter the source value that gets written to the target model;
Similarly, apply a ratio to the source value so that we only transfer the relevant portion of the whole data value. E.g. we can transfer 20% of head office costs;
Use rule parameters to dynamically select members.

So if you find that Associations don't quite do what you need, then how do you use a PEL Allocate rule? The following is an example of a simple PushCrossModelAllocation PEL Allocate statement:

scope 
(
  $TransferScenario$,
  [Time].[Monthly].[All].LeafMembers,
  [Organisation].[Europe].[France],
  [Account].[All Members].[Total Cost]
) ;
     
  Allocate
  (
    //The scope of cells that will receive the data
    //The this keyword indicates the target is the same cells as defined in the scope
    this,
    //The source value. If its the same as the scope then we just use ()
    //Or we can apply a simple calculation
    [Account].[All Members].[Volume] * [Account].[All Members].[Cost Per Unit],
    //The scaling ratio that gets applied to the source value
    //0.2 indicates that we only want to apply a fraction of the source value
    0.2,
    //The last two arguments are only relevant for Push and PullCrossModelAllocation
    //They specify the mappings between the source and target members
    //Here I want France (in the source) to be mapped to France (in the target)
    (
      [Organisation].[Europe].[France]  
    ),
    (
      [Organisation].[Group].[France]
    )
  ) ;

end scope;

There are a fair few arguments to the Allocate statement, but I've found that a good way to learn what its doing is to debug the rule in order to see the MDX statement that gets generated. The MDX query that gets generated for the above PEL statement is as follows:

WITH CELL CALCULATION queryCalc 
FOR
 //This part of the MDX comes from the 1st argument in the PEL statement
 '([Account].[All Members].[Account].&[5005],
 [Measures].[Value],
 [Scenario].[All Members].[Scenario].&[1],
 Descendants([Time].[Monthly].[(All)].&[0], 1073741823, LEAVES),
 [Organisation].[Europe].[Level 02].&[11])' 
AS
 //This is arguments 2 and 3 of the PEL Allocate statement
 //Namely the simple A*B calculation and then multiplying the value by the ratio
 ((([Account].[All Members].[Account].&[5003],[Measures].[Value]) *
 ([Account].[All Members].[Account].&[5004],[Measures].[Value])) * 0.2)
SELECT NON EMPTY 
 ([Account].[All Members].[Account].&[5005],
 [Measures].[Value], 
 NonEmpty(({[Scenario].[All Members].[Scenario].&[1]},
 {Descendants([Time].[Monthly].[(All)].&[0], 1073741823, LEAVES)},
 {[Organisation].[Europe].[Level 02].&[11]}))) 
 properties [Scenario].[All Members].Key ,
 [Time].[Monthly].Key ,
 [Organisation].[Europe].Key ,
 [Account].[All Members].Key 
ON COLUMNS 
FROM [Europe]

Here we can clearly tell (as marked in the above MDX code) how the first, second and third PEL arguments translate into MDX. If you find that the allocate statement is not quite doing what you want, then its quite useful to execute the MDX within SSMS to figure out exactly how the source model is being queried.

Once you're finished coding your business rule it can be executed via a scheduled job, manually, or via Excel.

To be fair to Associations, you're probably more likely to use them for bulk transfer of data, perhaps to facilitate cross model consolidation - and they're extremely useful for this purpose. The Allocate statement is perhaps more relevant for specific pieces of data that need to be adjusted in some way before being transferred to the destination. Despite this, the Allocate statement is the more flexible method and will allow you to support more sophisticated cross model data transfer - if it’s needed.

Technorati Tags: PerformancePoint,Allocate,PEL,MDX