Oracle DRM Blog

Apologize that I have been quite busy for the last few weeks. But thank you for all the support. I have getting pinged to provide some help in using the DRM Tools that I posted a while back. In addition, I am getting some great questions and solving them as soon as they come in. We’re on fire!!! Keep them coming, guys.

And with the latest ping from Ming, I decided to move on and continue this series of blogs aimed to demonstrate how you could be using Oracle Data Relationship Management in building out your hierarchies in minutes and not days — yes literally!

Back in August, I blogged about a tool that I have created on my own which helped me tremendously in developing hierarchies for a long time. The tool started out as a script that I used very often that I decided to make it web-based and generic enough for you to use. Once you start using it to build your 1st hierarchy, you’ll realize the power. Let’s start the tutorial…

Why am I saying this? The simple reason is that in DRM, there’re only 3 ways to create your hierarchy.

1. Type it in manually

2. Import it using the import function

3. Use the Automator

I’d assume most developer would dread at the though of option 1 so that’s out the window (unless you’re the final business user). Option 2 does an import but you cannot have import multiple hierarchies into the same version. And so, how would you be able to build a version of hierarchies consisting of Account, Company, Department, Product etc? Well you guessed it, you’d use Option 3 – the Automator.

Now since the DRM Automator is the only best practice in building a solid version of multiple hierarchies, learning and creating that Automator script becomes a skill that you’ll need. For beginners though, you don’t need to know this. You just need to use the DRM Parent Child Import Automator tool that you will learn in this blog and it’s done — I promise.

What’s on the flip side? Well I have seen people spending days in Excel in creating Automator files, or alternatively, you can simply use the instructions below to create your 1st financial hierarchy effortlessly. Once you have used it, you’ll never use anything else.

1. Before we start, we assume you’d have a hierarchy you want to create. If you’re using Essbase, you’d be familiar with the “Parent | Child | Child Description” text format to represent your entire hierarchy. You can literally get this kind of extract from EDW, Hyperion quite easily. If not, even doing it in Excel would be simple. And so we have prepare one like the one below.

The file has 5 columns separated by comma. However, only the first 3 columns are necessary. The order of the columns are not important. You just need to know where they’re in the file. In our example, the parent node is in column 1, the children node is in column 2 and description in column 3.

Column 4 and column 5 are attributes of the children node. These are referred to as properties in DRM. If you don’t know what properties are, do read up on your own.

Hopefully, you can understand from looking at the file, based on the format, that ACCOUNT is the top node. Account 20000 and 40000 are the children of ACCOUNT. 31000 is the children of 20000. 31100 is the children of 31000 and so on.

2. Once you have the file created or extracted, you can open the Parent-Child Import Autmator tool. Enter a version name (e.g. the fiscal calendar period FY08-AUG, it can be anything). Now enter a hierarchy name (e.g. ACCOUNT). Next click on the BROWSE button to select the file we created/extracted from step 1.

3. Since our file has a header row, enter ‘yes’ to tell the tool to skip the first row of our parent-child file. Next, we need to tell which column has our parent node, children node and description (i.e. 2,3 and 1 in this order for our file).

4. Let’s skip the optional parameters (we’ll talk about this in later blogs). Simply click the button at the end. Within seconds, you’ll be asked to save the Automator file. Yes!!! Within the last 3 seconds, the Parent-Child Import Automator read the file you uploaded, processed it and created the Automator script necessary to create the entire hierarchy. It’s that cool. OK, go save it into your hard drive — a place where you can locate it later.

5. You can open this file and take a look. Each node will be created from top to bottom in its right order. Even if you didn’t create your Parent-Child text file in the right order, the tool will rectify this and put it back into the right place. What that means is that DRM will not let you create a children if you haven’t already create the parent first. Therefore, it’s important that we have the script in a top-down manner to ensure all the parents are created before all the children from the top levels to the lowest children node.

6. Our next step is to run this script in DRM, and we’ll cover that in the next blog entry. Please join us.