nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

Failing to model simple many-to-many relationships.

Mike V

Hi.

I have really simple data to analyse - list of employee activities, for which i have created Type and Time dimensions, and this is working fine. However, i have issues when trying to add Employes dimensions, which has many-to-many relationships with activities. There is underlying intersection table, which i've tried to bring into LTS as described in the docs.

1) Created D Employee dimension based on physical table S_CONTACT

2) Added S_ACT_EMP table (which is intersection table) to LTS for D Employee

3) Created logical join between D Employee and fact table

Now, when i try to create analysis with Employee dimension i get the following error

None of the fact tables are compatible with the query request D Employee.Full Name. (HY000)

What i'm doing wrong ? Obiee version is 11.1.1.1.7

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Personally I norma model this with the intersection as the fact and S_CONTACT (not S_PARTY?) and S_EVT_ACT as dimensions.

Gianni Ceresa

+1 for Christian, you probably can get most (all?) of your metrics by using the S_ACT_EMP as fact table and activities and employees as dimensions.

Mike V

Hmm, the only fact i'm actually interested is the count of activities for a given combinations of dimension. I'm not sure i can get this count with S_ACT_EMP as a fact table ?

Gianni Ceresa

In S_ACT_EMP don't you have the activity ID (the foreign key) ? Set a COUNT aggregation on it and you get your number, all the dimensions can be modeled starting from there ...

Mike V

Hmm, that could work, will try this approach.

[Deleted User]

Gianni was faster.

Yes count the activity ID (distinct) to get #Activities and emp id to get #Employees. This way you get all metrics correctly without an double countjng from M:N relationships and have a nice way of showing multiple activities for emps and multiple emps for activities.

In operational modelling the intersecrion tables are perfect candidate for facts.

Mike V

Ok, i've changed the model to have intersction as fact, and now i get "The specified criteria didn't result in any data" when doing the simplest analysis (count by type)

Gianni Ceresa

You are the one having the model in front of you, so double check all the joins etc.

Mainly if you changed the model from an existing one with a different format you maybe forgot to add / delete / edit few things.

Upcoming Event

Analytics - Fusion Data Intelligence: Semantic Model Extensions in FDI: Implementation Patterns, Security, and Best Practices
Build on the foundational concepts from Part 1 with a deeper look at implementation patterns for extending the FDI semantic model. This session covers common use cases such as modifying subject areas, creating custom subject areas, combining external application models, and supporting custom security requirements. It also…

Top Leaders