OBIEE 12c: Creating hierarchies on business model with more than one fact table

2943498

Hi,

I am trying to model OBIEE 12c repo for two fact tables.

These fact tables are "Incidents" and "Cases". The relation between them is one case consists of zero, 1 or more incidents. One incident, if linked to a case, it is always linked to only one case. So it is one-to-many relationship when both parent and child can exist independently. If it was simple one-to-many relationship I would create one fact table with grain equal to one "Incident" row but can't do it in this model. I also don't have any conformed dimensions.

I trying to model two subject areas:

"Incidents" - in this SA "Case" is a dimension for "Incident" - measure here is number of "Incidents"

"Cases" - just to analyse cases - measure here is number of "cases"

In the "Incidents" SA I created a link between "Incidents" and "Cases" fact tables what means that OBIEE started recognizing "Cases" as dimension - perfectly fine for me.

The problem is when I am trying to create two hierarchies for Case Creation Time and Case Last Update Time (two separate logical tables) - It looks like I can create only one hierarchy - when trying to create second one I get following error:

Logical table: Case Last Update Time must be descendant of the root logical table in associated dimension according to logical many-to-one join relationship.

What I am trying to achieve in BMM is:

                                                 Dim Incident Creation Time (logical dim table)  ->  Hierarchy Incident Creation Time (works fine)

                                                 /                                          Dim Case Creation Time (logical dim table) -> Hierarchy Case Creation Time (works fine)

                                               /                                           /

Incidents (logical fact table)  - Case (logical dim table)

                                              \                                           \

                                                \                                           Dim Case Last Update Time (logical dim table) -> Hierarchy Case Last Update Time (doesn't work)

                                                 Dim Incident Last Update Time (logical dim table) -> Hierarchy Incident Last Update Time(works fine)

When I remove logical join between "Incients" and "Case" the hierarchy works fine.

Have you seen this before? I presume that It might be forbidden but why for second hierarchy.

[Deleted User]

2943498 wrote:Hi,I am trying to model OBIEE 12c repo for two fact tables.These fact tables are "Incidents" and "Cases". The relation between them is one case consists of zero, 1 or more incidents. One incident, if linked to a case, it is always linked to only one case. So it is one-to-many relationship when both parent and child can exist independently. If it was simple one-to-many relationship I would create one fact table with grain equal to one "Incident" row but can't do it in this model. I also don't have any conformed dimensions.I trying to model two subject areas:"Incidents" - in this SA "Case" is a dimension for "Incident" - measure here is number of "Incidents""Cases" - just to analyse cases - measure here is number of "cases"In the "Incidents" SA I created a link between "Incidents" and "Cases" fact tables what means that OBIEE started recognizing "Cases" as dimension - perfectly fine for me.The problem is when I am trying to create two hierarchies for Case Creation Time and Case Last Update Time (two separate logical tables) - It looks like I can create only one hierarchy - when trying to create second one I get following error:Logical table: Case Last Update Time must be descendant of the root logical table in associated dimension according to logical many-to-one join relationship.What I am trying to achieve in BMM is: Dim Incident Creation Time (logical dim table) -> Hierarchy Incident Creation Time (works fine) / Dim Case Creation Time (logical dim table) -> Hierarchy Case Creation Time (works fine) / / Incidents (logical fact table) - Case (logical dim table) \ \ \ Dim Case Last Update Time (logical dim table) -> Hierarchy Case Last Update Time (doesn't work) Dim Incident Last Update Time (logical dim table) -> Hierarchy Incident Last Update Time(works fine)When I remove logical join between "Incients" and "Case" the hierarchy works fine.Have you seen this before? I presume that It might be forbidden but why for second hierarchy.

Nice write-up but I really don't see how your one time dimension should pose any problem. Your case is standard BMM modeling with non-canonical time dimension.

I think your pysical and logical setup of the tables (both physical and logical) is missing something or misconfigured.

Can you screenshot the logical and physical model for the entities in question?

Also in the physical layer: Are you using aliased physical objects?

2943498

Hi Christian,

pelase find screenshots with some description attached to the original post.

[Deleted User]

Ok first and only thing: BMM is a star. Not a snowflake. You snowflake inside a logical dimension table and not from one logical dimension table to another.

2943498

Thanks a lot for help

[Deleted User]

You're welcome