Hierarchy Flattening – Horizontal

I’ve come across BODS developers who knows what hierarchy flattening is, but does not know what is the advantage of using it.

In the below article I have tried to explain the same to the best of my knowledge

I hope it is useful.

Hierarchy Flattening – Horizontal

The Horizontal Hierarchy Flattening Transform is used to convert a Parent-Child table into horizontally flattened structure that can be queried with today’s BI tools more easily.

Consider the below scenario where the multilevel details of a country’s population is stored.

1. Format 1

It can be seen that a lot of redundant data is loaded to the table.

To avoid the redundancy the data can be represented in the following way (as a parent-child table).

1.      Format 2

(Parent, child codes have been formulated by appending the suffixed numbers of the location data)

Now, even though format 2 eliminates redundancy, it will be a tedious job to do reporting on the same.

Consider the scenario where a report needs to be built on the above data, which has breakdowns on the different location levels i.e. Country, State, District and Place.

E.g.

A report like this can be very easily built on Format 1 data, which would only require direct group by’s on the dimensions.

This is where the hierarchy flattening comes into picture; it flattens the hierarchy or converts the multilevel data inside a parent child table (Format 2) back to dimensions (Format 1).

After applying Hierarchy flattening on the Format 2 table and fetching the record on the last level of the flattened data, table 1 of Format 2 would appear as below.

Table1                                                                                Table 2

(The key field can be obtained from the last Level key auto generated by the transform.)

Thanks,

Feddy George