This week, we will look at a feature of Essbase data load rules that has been around for ages. But, it is not as widely used as it should be (or maybe it is, what do I know). We’ve all been in situations where we perform a data load, but one of the members in the load file does not exist on the outline. The load errors out and we have to go fix the issue. Now there might be instances where you don’t want this to happen and you want the load to go through. To do this, we have to add members dynamically to the outline.
Let’s take a quick look at our outline. Apparently, this cube is used by Pete (you know, one of the other guys on this blog) to store his bank details.
Notice that there is a member called “UNMAPPED_ACCTS” (I can only guess that the IRS must not know about this). This is what we will use to prove out our process. What we will try to achieve, is that if there are any accounts that don’t exist on the outline, they should automatically get added below this member.
Let’s take a look at our data load rule. The excitement happens on the Dimension Build Settings property.
You can see that a couple of options that we normally don’t use are selected. The first one is the “Ignore Conflicts” setting. The technical reference tells us the following, about this option:
So, this can only be used if you are doing an “Add”, as we are in the second setting, “Add as child of”.
And that’s it, really. The only thing to note, is that if you are doing a manual load, make sure to select both dimension build and data load options.
And once the load is done, you can see members dynamically added below the “UNMAPPED_ACCTS” member.
Data will also be added to that member as soon as it is added on the outline. Pete can now continue hiding his assets. As you can see, this could be very useful for an administrator as well. The next step to this, from an automation perspective, would be to alert the administrator once members are added below these “unmapped” members. But that is a post for another day…