Bursting Reports to the File System in Cognos 8

http://www.youtube.com/watch?v=cYca2b8qZYg

Ragged Hierarchy

One of the typical scenarios in any field.

Lots have been written about traversing ragged hierarchy. Data sets like retail, healthcare, education, loans etc have ragged hierarchies to be concerned with in many aspects.

What is a ragged hierarchy anyways? Well simple example would be for example in college.

There is a Degree, Degree has its courses, Courses have classes offered, Courses are offered in different campuses, campus have students from both bachelors and masters.

Degree A

Course D, E, F

Course D has Campus P , Q

Campus P has students J and K

Degree A

Course D E F

Campus P Q

Students J K

Now there is a perfect solution for all this from Ralph Kimball which is use a Bridge/Helper Table and traverse the hierarchy. Very good and authentic solution where in you are not forced to change anything much just plug your Bridge Table in between your Dimension and Fact and you can seamlessly traverse.

How would the bridge table look and what would it have. It would have paths stored in the table from every node to every node with corresponding depth attributes from parent nodes.

Well here are the columns and from our example here is how the bridge table will be populated

Parent Child DepthfromtheParent Topmost_Level Lowest_Level

A A 0 Y N

A D 1 Y N

A P 2 Y N

A Q 2 Y N

A J 3 Y Y

A K 3 Y Y

D D 0 N N

D P 1 N N

D Q 1 N N

D J 2 N Y

D K 2 N Y

P P 0 N Y

P J 1 N Y

P K 1 N Y

With a combination of the Depth from Parent, Topmost_Flag and Bottommost_Flag we will be able to traverse the children from Child column of a Parent from the Parent Column.

This bridge table is very effective as mentioned earlier in data warehouse solutions where ragged hierarchy is involved.

Now if we take the PL/SQL route to code this bridge table all cursor based approach its very doable.

The challenge is to make an ETL tool to go about populating this Bridge Table solution.

We know the algorithm/logic now we have to put a wrap around that logic and make it intelligent enough to say traverse a complete route and populate the bridge table for Parent A. How would be tell the ETL tool to stop at node K for A.

Most of the ETL tools are adept at flattening things out and pivoting rows to columns.

How would we attain this recursive nature to implement the solution is the task at hand.

Another aspect is the Business Intelligence tool involved in reporting. Whether its IBM Cognos, SAP Business Objects, Microstrategy how would their performance be while bringing in a whole new table into the picture?

Query path would get affected, joins will increase. Every BI tool says they have a solution for ragged hierarchy but they really don't. Roll-ups are of major concern for reporting while using this scenario.

How would present the whole solution to the business and let them know that we have a solution and we can exactly tell you how level deep your data is.

This is a very interesting scenario and even though loads of people have dealt with it they have tailored the bridge table to their needs depending on time at hand and business priorities.

I will write more on this topic in a later blog.

For now this is good enough to ponder on.....