Jeremy Kashel

Jeremy Kashel's Blog

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.

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 4th and 5th 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 2nd member will be interpreted as your 5th 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.

 

Comments (2) -

  • JerryLaughs

    9/2/2008 7:52:03 PM | Reply

    Hey Jeremy,

    Thanks for the great post here and on the msdn site - I managed to get the ALLOCATE rule working, but ran into the problem of always appending.  No issue, just create that 'this=NULL' rule.  

    However, I am using a PushCrossModelAllocation rule, so I don't have the source members to select for my scope when creating the 'this=NULL' rule.  

    How do I get around that?

    Thanks

    Jerry

  • Jeremy Kashel

    9/4/2008 10:41:03 PM | Reply

    Hi Jerry,

    Great to hear that you found it useful.

    Unfortunately the Pull and PushCrossModelAllocation rules require an ALLOCATE or TRANSFER statement, meaning that you can’t use ‘this=NULL’ to delete the data in the destination model.

    One option that you have is to create a PullCrossModelAllocation rule in the destination model. Alternatively, you could write a Native SQL rule to do the delete. With the Native SQL rules, you can effectively simulate cross model operations. All that you would need is essentially a delete statement and a where clause against the destination model fact table. You could then execute a rule set containing 1)The Native SQL delete and 2)Your PushCrossModelAllocation rule - both from the source model.

    So unfortunately there’s no easy way around the problem. You have a choice of either re-writing your rule to do a Pull or getting stuck into the Native SQL rules. Hope that one of those work out for you – if not let me know

    Thanks

    Jeremy

Loading