Creating a dimension with Interface tables

For the ones who have started using BPMA to manage the outline in Hyperion Planning, there are two options to create the dimensions. Either make the .ads file or populate the Interface tables with the relevant information.

Maintaining and developing an .ads file is rather cumbersome and updating the Master version takes considerably more time as compared to pulling the data from the Interface tables.

When I first used the Interface tables, I had could not understand how it went about creating the outline and was unable to control the order of the children. This led to some issues (Shared members were showing up BEFORE the original members).

I had specifically ensured that the interface tables had the members in the exact order that I wanted them to be in.

So basically my hierarchy table would be something as follows

PARENT

CHILD

#root ORG
ORG DOMESTIC
DOMESTIC REGION 1
REGION 1 LOCATION 1_1
REGION 1 LOCATION 1_2
DOMESTIC REGION 2
DOMESTIC REGION 3
DOMESTIC REGION 4
ORG INTERNATIONAL
INTERNATIONAL COUNTRY 1
INTERNATIONAL COUNTRY 2
INTERNATIONAL COUNTRY 3
INTERNATIONAL COUNTRY 4

This should have been giving me the following hierarchy
ORG
-DOMESTIC
–REGION 1
—LOCATION 1_1
—LOCATION 1_2
–REGION 2
–REGION 3
–REGION 4
-INTERNATIONAL
–COUNTRY 1
–COUNTRY 2
–COUNTRY 3
–COUNTRY 4

But when I did run the import, I actually got the following
ORG
-INTERNATIONAL
–COUNTRY 1
–COUNTRY 2
–COUNTRY 3
–COUNTRY 4
-DOMESTIC
–REGION 2
–REGION 3
–REGION 4
–REGION 1
—LOCATION 1_1
—LOCATION 1_2

I could not figure out any particular pattern for this unexpected behavior. Generation 2 was being reversed (or was it a reverse alphabetical order).
The first thought was to ensure some ordering in the Interface table so I quickly renamed the table and decided to create a view that would ensure that the rows would be sent out in a particular sequence.

Before I went on doing this particular task, I decided to try another approach. Basically it would entail a restructuring of the load sequence. In my sequence I had defined the children of each member right below that particular member. I decided to change this sequence and basically list ALL the siblings together, and then going on to the next generation.

So basically my load table became something like this

PARENT

CHILD

#root ORG
ORG DOMESTIC
ORG INTERNATIONAL
DOMESTIC REGION 1
DOMESTIC REGION 2
DOMESTIC REGION 3
DOMESTIC REGION 4
INTERNATIONAL COUNTRY 1
INTERNATIONAL COUNTRY 2
INTERNATIONAL COUNTRY 3
INTERNATIONAL COUNTRY 4
REGION 1 LOCATION 1_1
REGION 1 LOCATION 1_2

After having made these changes (and it took me quite a while to do this particular reordering) but the outline came out just the way I wanted it to be.

So basically, the only purpose of this post is to ensure that the siblings are mentioned right after the other to ensure the correct order.

I’m just guessing here but I think that when BPMA parses the input, it adds the siblings right after the other. However when there is a break in the sequence, it just goes back to the parent and inserts the new member at the top of the list. This would explain why INTERNATIONAL showed up before DOMESTIC and why REGION 2, REGION 3 and REGION 4 were in the correct order but not REGION 1.

This seems to be a very small detail but if you are planning on developing an extensive ETL to bring in the data to create the dimension, then it comes in handy to know this small detail beforehand.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s