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

Crosstabs in Cognos (Pivot Tables)

Here are a few important things that i have learnt till date about Crosstabs

1) In the olden days of Cognos ReportNet their Crosstab engine was very rigid.

2) Rigid in the sense it had its one particular way of wokring not that it has changed much but the way we can pass a context or even assosciate a context (otherwise dimensions of a crosstab) that govern the calculation of the metrics has evolved.

3)Initial implementations were had to be done in a specific manner Year,Month and Date had to be indented either Horizontally or Vertically to make sense for the measures to be governed by time and then year and month had to hacked hidden if they were not required on the report spec.

4)Drill throughs were a challenge as passing both the dimension parameters was a task at hand.

5) With the Cognos 8 and its releases Crosstab has improved day in and out.

6) Now if we need to drill through to a particular target report based on relational data it is not a task at hand anymore.

7)There is a Property (property) associated to the Dimensions of the crosstab which can be used very effectively to pass context of the dimensions to the target report.

8) There is a general misconception that a crosstab (atleast for the new comers) can only accomodate only one measure(is not true)

9) Infact now it lets us define a Default measure and then further lets us drop in multiple measure governed by the same set of Dimensions

10) Solve Order functionality has helped in implementations a lot

11) Any calculated field(%age calcs) can be achieved effortlessly using Solve Order

12) If a calculated field is dependent on other data items then giving it a Higher Solve Order tells the crosstab engine that the calculation has to be done only after the dependent data items have been aggregated or calculated hence generating accurate results.

13) There is a functionality to Overwrite Content of a particular cell location using an appropriate query calculation/data item.

14) The sorting has also greatly improved. Data->Advanced Sorting functioanlity gives Report Authors a huge amount of flexibility in the way they want their dimensions to be sorted.

15) Crosstabs can be put on top of a an already built cube (which actually is accessed as a Package recent 8.4 version) in the cognos connection enabling effortless drill up/down depending upon the cube's size and query performance.

Crosstabs can be used very effectively in time series reporting solutions since they are extremely dynamic in their span of dimensions posed by the report auther.

Moral of the story i have seen some amount of hesitation on part of report authors to use crosstabs (again this is completely my view) in the industry but with the evolving everyday scenarios crosstabs are very effective in portraying dimensional representation of data for canned reports.