Skip to Content

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

Country

State

District

Place

Population

C1

S1

D1

P1

1000

C1

S1

D1

P2

2000

C1

S1

D2

P3

3000

C1

S1

D2

P4

4000

C1

S2

D3

P5

5000

C1

S2

D3

P6

6000

C1

S2

D4

P7

7000

C1

S2

D4

P8

8000

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

Location

Parent

Child

Key

Polulation

C1

0

1

1111

1000

S1

1

11

1112

2000

D1

11

111

1123

3000

P1

111

1111

1124

4000

P2

111

1112

1235

5000

D2

11

112

1236

6000

P3

112

1123

1247

7000

P4

112

1124

1248

8000

S2

1

12

D3

12

123

P5

123

1235

P6

123

1236

D4

12

124

P7

124

1247

P8

124

1248

(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.

Country

State

District

Place

Population

C1

.

.

.

.

S1

.

.

.

.

D1

.

.

.

.

P1

1000

.

.

.

P2

2000

.

.

Total D1:

.

3000

.

.

D2

.

.

.

.

P3

3000

.

.

.

P4

4000

.

.

Total D2:

.

7000

.

Total S1:

.

.

10000

Total C1:

.

36000

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

Country

State

District

Place

Key

Key

Population

C1

S1

D1

P1

1111

1111

1000

C1

S1

D1

P2

1112

1112

2000

C1

S1

D2

P3

1123

1123

3000

C1

S1

D2

P4

1124

1124

4000

C1

S2

D3

P5

1235

1235

5000

C1

S2

D3

P6

1236

1236

6000

C1

S2

D4

P7

1247

1247

7000

C1

S2

D4

P8

1247

1247

8000

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

Thanks,

Feddy George

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply