Handling Hierarchies Modelled as Bridge Table in Database

[avarshney]

Hi,

This Image shows the hierarchy that has to be modelled here.

The hierarchy is ragged in the sense that the number of levels is fixed (4), but the levels in between may be skipped e.g the leaf node 5 in the diagram.Fact table has data allocated to the lowest level.
This has been modelled as 3 tables.
1.Product_Dim_V - This has descriptions for the keys.
2.Product_Hierarchy_Dim - This stores the exploded hierarchy information, i.e all possible parent child relationships are stored.
3.Fact table - Having Balance for lowest level of dimension.

Problem: I have to flatten the hierarchy in cognos and have all these levels as columns of a Query Subject. From the available tables I want data in the form -
Level 0 | Level 1 - Level 2 | Level 3

Is this possible to be flattened in Cognos REPORTNET?

Thanks
Anubhuti

[bdybldr]

Hi, I'm not sure that I completely understand your requirements but I would try to accomplish this using child tabular models within a tabular model.

For example,

tab_model
        |
        child tab model 1 (level 0)
        |
        child tab model 2 (level 1 - 2)
        |
        child tab model 3 (level 3)

You can define the criteria for each level using filters.  I hope this helps.  If not, if you provide more details we can help you more.

[cognosfreelancer]

Hi Anubhuti

It is easier if you flatten it in the database. You could use a SQL statement with a connect by clause (if it is Oracle) to traverse the hierarchy.

The SQL statement can then be used as a data source query subject in your FM model.

NKT

[avarshney]

Thanks NKT,

We have implemented flattened hierarchical View in database itself, for better handling.

Regards
Anubhuti