Jeremy Kashel's Blog : PEL

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

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

BI Evening, TVP, 17th July 2008

Jeremy Kashel — Fri, 04 Jul 2008 09:06:01 GMT

I'll be giving a talk titled 'PEL Vs MDX' at the UK SQL Server Community's BI Evening on the 17th July at Microsoft's TVP offices in Reading.

The content will be geared towards MS BI developers, and will highlight the differences between the two languages, with the aim that those with MDX experience will be able to make a fast start with PEL.

Also speaking is Jeremy Kirkup from TAH, on 'Using Excel Services with Analysis Services and MOSS'.

If you are just starting to explore delivering BI solutions with Excel Services and SharePoint then there are some issues that it is wise to be aware of in advance. This session will describe some real world lessons gained while creating BI solutions which expose Analysis Services data through the Excel Web Access web part.

The Excel 2007 client has first class support for the some of the advanced features of Analysis services such as drill-through. However, when exposing pivot tables to a browser through the Excel Web Access web part this feature is not available. The session will discuss a couple of approaches to implementing drill-through functionality for EWA based pivot tables using the MOSS SmartPart, AJAX and the Excel Web Services.

You can register for the event here.

Technorati Tags: PEL,MDX,PerformancePoint,BI Evening

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

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