DRM External Lookups

Today, I thought we’d look at one of the new features that DRM has rolled out. Version 11.1.2.4.330 allows you to do a lot of cool things. Perhaps my favorite among them, is the ability to perform external operations, whether to web services or to databases. Let’s look at how we can set up external lookup properties using this feature.

One thing that gets asked all the time, during implementations, is can we talk to external tables to set up lookups? This sort of scenario is exactly what Oracle had in mind with this feature. For instance, we might have a mapping table external to DRM, which we may need to interact with, to populate property values. In my example below, we have a table which contains genre information for some TV shows.

21_1_Table

External Connection

I would like to use the table above to form source to target lookups for one of my hierarchies. To do this, we must first add this table to our external connections (check this link for a refresher on external connections). Notice that there is a new option when you add a connection, called External Operations?

21_2_External

When you select the tab, you have the ability to add a new operation.

21_3_External_2

On the window that opens up, you can select from 2 types of database actions, whether to run a statement, or run a stored procedure (!!! <geeks out>). I chose the Statement option.

21_4_DB

Let’s try a simple select statement.

21_5_DB_2

Notice the test button on the top right section of your window. When you hit the Test Connection button, you should see the results, in the Result tab.

21_6_DB_Results

Well, what if I wanted to order the records differently. You can absolutely do that. In fact, it seems like most query level actions permitted by the database layer appear to be allowed here.

21_7_SQL

Notice, we get a nice and ordered list. Save your changes so that you may use this for the property setup step.

21_8_SQL_Results

External Lookup Property

The property setup is no different from how we set up new properties. The only difference, is that there is a new property type now available, called External Lookup.

21_9_Property

Once you select this option, a new tab opens up, where you can specify external connections.

21_10_Property_2

From the dropdown list, you can select the target value for your source property. In my example, I chose the GENRE column.

21_11_Mapping

Once you save it off, and navigate to your hierarchies, you can select a lookup value, just like you would on other lookup properties.

21_12_Hier

You get the option to choose from the values that are available from the external source.

21_13_Lookup

Just to check that table updates work as expected, I updated the table with a new record…

21_14_New_Record

The update is instantaneous on DRM.

21_15_Update

This new addition to DRM is extremely useful and I am really impressed with this. There are other external operations you can perform as well, like invoking web services. This is a huge step for the tool and I am sure, it will be extremely useful for users as well.

Avatar

About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He is, surprisingly, an Oracle ACE Associate. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

5 Comments

  1. Good Job…Love DRM..

Leave a Reply

Your email address will not be published. Required fields are marked *