New PBCS v18.12 ASO range functions in Calculation Manager

Just a very quick entry, as with the recent release of 18.12 there are some additional functions now available when limiting a range or POV to apply a formula to in Calculation Manager for ASO rules.

These are additional MDX statements that would work in the same fashion as the equivalent standard MDX statement:

@COUSIN
@DISTINCT
@EXCEPT
@FILTER
@FIRSTCHILD
@GEN
@GENMBRS
@INTERSECT
@LASTCHILD
@NEXTMBR
@PREVMBR
@ISIBLINGS

To view how the functions work the Oracle documentation covers that.

The first function I wanted to test was the @EXCEPT function as we have had a requirement for that for time.

To test I tried to remove one code from the level zero descendants of my profit and loss accounts; syntax I thought would be simple enough.

@EXCEPT(@Level0Descendants("PL"), "A12345")

Where  A12345 is a level zero account.

However the rule failed to validate with the following error message:

Error Cause: Syntax error in input MDX query on line 1 at token ')' Rule  xxxxx

As the functions were not listed in the release notes of this version I initially figured that they had somehow slipped through the migration cracks and should't have been included in this release, but decided to give it a bit more of a go and then tried the following syntax:

 

 @EXCEPT(@Level0Descendants("PL"), @Level0Descendants("A12345"))

 As these were not documented and this is the first release Oracle may fix this issue, but if not this is how to use the @EXCEPT function

Loading multiple UDA's via Data Management

In a recent project to migrate an existing application from on premise to PBCS we were tasked a straight forward ODI metadata load to a Data Management load - seemed simple enough and was going smooth until we had to load more that one UDA.

I'm not going to go through the process of setting up a metadata load - there are already a few blogs around for that, my pick would be John Goodwin's blog

Our load file in this case is tab delimited, with the two UDA's in double quotes with a tab separating them

We have set the data mapping as a simple like * to * the data validates without any issues and looks ok

While the data load validates if fails on Export with the following error stating the number of fields are wrong.

Looking at the error message it seems that we had to change the delimiter between the UDA's , ODI is comma separated so we tried that,  passing on validation on Export, so we look at the member properties only to find a single UDA has loaded

Not really what we were after, so we revert back to the original format however we adjust the mapping from a straight * to "*"

This time on the import looks like 

 So we are on the right track looking at the final result we can have success

So there you have it, multiple UDA's via Data Management, the solution is simple enough just ensure that there are double quotes around the string of UDA's.