DRM Duplicate Check

One of the things I’ve always wanted to focus on, with this blog, is how the tools we use, sometimes provide simple and effective solutions. For instance, there are always cases, when we develop hierarchies on DRM, where we will need to filter down our exports based on the existence of duplicate attributes. In other words, if a property has the same value for 2 nodes, don’t export it out. Or, filter our shared nodes from a hierarchy.

One way to do this, might be to put a filter on our export.

filter

A filter query is a powerful tool, and can help you in any number of scenarios. But, do we really need to use formulas to filter a duplicate node or duplicate property out? Not really. To look at how we might filter out duplicates, let’s take a look at our hierarchy.

hier

For the hierarchy above, I would like to filter out all nodes with duplicate descriptions. In other words, we only need “unique” descriptions in our export. If we don’t make any changes, this is what our export would look like.

export

And this is what the output would look like:

w_duplicates

 Now, to filter out any nodes with duplicate descriptions, all we have to do, is navigate to the “Columns” tab, and select “Column Options”.

column_options

There are a few different options here. For today, we will focus on the “Primary Key” option.

pk

This allows us to define a field as the primary key to be used when determining whether duplicates exist. I’ve left the option checked.

pk_check

We need to make one more change; let’s navigate to the “Style” tab. We need to make sure the following option is checked, so that DRM knows to do the primary key check.

style

If we run the export, we can see that duplicates have now been removed.

dup_removed

But, notice now that from the earlier file to the new one, we are missing a parent.

missing

I understand why it’s missing, it’s because 2 nodes have the same description of “” (i.e., nothing). So, the second node is excluded from our export, as a duplicate. To fix this, just go back to the column options, and check the node name property as well.

2_checks

Now, DRM understands that it needs to look at a combination of the node name and the description, to check for uniqueness. If we re-run the export:

right_results

We have the right results. An example of how this type of export might be used, might be if you have to export to a data warehouse. The warehouse tables might need to have uniqueness for member names, which you are now able to ensure, by using standard DRM functionality. Until next time, Cheers!

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 hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

2 Comments

  1. Hi,

    Do you know or have you done a DRM validation to for duplicate aliases?
    say you add a new Node with its aliases, to validate that before the node is imported to an export, there is no errors at all ?

    • Hi Lesley,
      Thanks. There are a couple of ways to do this:
      1. You can usually ensure uniqueness by doing a combination of node name + description, as your alias.
      2. You can also set up version/hierarchy level validations which kick in as a batch validation, or as part of your export. What sometimes happens is, aliases may be unique within a hierarchy, but not for the database. Meaning, you could have 2 members (in different dimensions) on your cube, with the same alias in DRM, which would still cause issues on your dimension build. So, a version validation may work in this case.

      Hope this helps.

Leave a Reply

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