DRM External Lookups

Today, I thought we’d look at one of the new features that DRM has rolled out. Version 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.


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?


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


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.


Let’s try a simple select statement.


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.


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.


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


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.


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


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


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


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


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


The update is instantaneous on DRM.


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.


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…


  1. Good Job…Love DRM..

Leave a Reply

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