I have a "Relationships" table that I'm pulling out of ServiceNow that relates services all the down to servers, but in doing so, it creates duplicate entries.  For example, the SAP HR app is tied to a bunch of different services, and it runs on a bunch of different servers, so the hierarchy looks looks something like this:

But in a relational dataset it looks like this:

When I use the flatten hierarchy function in R12 though, I get errors because duplicates are created.  Anyone else have a similar issue, or any recommendations for how to get around this?

Hi James,

I had a quick look at this and see the same issue, where I would expect the flattening to work better. Can you log a support ticket at support.apptio.com

Michael

James,

the basic problem you are hitting is that that is not what Flatten Hierarchy is for. Basically it is designed for tables listing employees, where the employee goes to one point in the hierarchy, and all the other columns are a hierarchy on that particular primary-key value (specifically a management chain) Here are 2 options for you.

option 1) what specifically are you trying to achieve with the transformed data? I ask because my first thought when I look at your comment is to use unpivot instead.

It would give you a table that looks like:

You can then do a simple if formula to make collapse column a bit more user-friendly, and you have a clean mapping table from server names to apps and services

Option 2)

Make your leaf unique. You can do this by adding a new column that concatenates together all the columns in your table BEFORE you flatten. This should give you the exact output you described (once you filter out the new rows added by this new leaf). Personally I would investigate option 1 first.

@James Funk this helped me solve a problem I was having - thank you sooooo much!!!!