Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Failing to model simple many-to-many relationships.

Received Response
11
Views
8
Comments
Mike V
Mike V Rank 1 - Community Starter
edited Aug 13, 2024 6:59PM in Oracle Analytics Cloud and Server

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

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    Gianni Ceresa Mod

    +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
    Mike V Rank 1 - Community Starter

    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
    Gianni Ceresa Mod

    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
    Mike V Rank 1 - Community Starter

    Hmm, that could work, will try this approach.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    Mike V Rank 1 - Community Starter

    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
    Gianni Ceresa Mod

    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.