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

OAC - modeling problem: link 2 fact tables(1-N) by a pseudo-dimension

Received Response
81
Views
3
Comments
Daniel Vornicu
Daniel Vornicu Rank 5 - Community Champion
edited Apr 3, 2024 4:44PM in Oracle Analytics Cloud and Server

Hello,

I have to solve a larger modeling problem: one fact table linked to 15-20 dimensions, second fact table linked 2 anothers extra dimensions and the 2 facts are in 1-N relation linked by an id (we can consider it like a pseudo dimension). I want to cross any mesure from the Fact2 by any of the 15 dimensions and vice versa any mesure from the Fact 1 by any of the 2 extra dimensions.

To explain that I find a simple example (orders-details) that I will describe below. I seems to work with my idea but I need a confirmation before applying the same principle to my larger case.

Questions:

  1. Find the quantity (F_DETAILS) of products sold by a specific client (D_CLIENT) ?
  2. The orders total amount (F_COMANDES) where a specfic item (D_ITEM) was involved ?

So I extract a dimension (FK_COMANDES) table from the first order table (F_COMANDES) only to link this preudo-dimension to the both fact tables. Then I need to add an extra join expresion ( 1 = 1 ) to solve the first question (empty fields if not).

The physical diagram ( Orders = Commandes in french)

image.png

The data:

F_COMANDES (the prefix A_ is used for marking the alias table)
ID CLIENT_ID COMANDE_ID TOTAL_COMANDE

image.png

F_DETAILS:
ID COMANDE_ID ITEM_ID QUANTITY UNIT_PRICE

image.png

To solve the second question I added a extra field 'Total des commandes' like below:

image.png

What I obtain in OAC is good. The only minor drawback is that I need to chose each time another mesure (I cannot fusion 'Total commande' and 'Total des commandes' in one single mesure)

Q1: Find the quantity (F_DETAILS) of products sold by a specific client (D_CLIENT) ?

The quantity is good and the green bordered total also. The other total is multiplied 2 and 3 times respectively

image.png

Q2: The orders total amount (F_COMANDES) where a specfic item (D_ITEM) was involved ?

The blue borderd total is good. The other total is the sum of the 3 orders total (not good for the the item Article 1)

image.png

Did I made the things properly?

Thanks,

Daniel

Answers

  • Mallikarjuna Kuppauru-Oracle
    Mallikarjuna Kuppauru-Oracle Rank 8 - Analytics Strategist

    Hi @Daniel,

    What is the difference between 'Total commande' and 'Total des commandes' these 2 measure calculation? Is it duplicate column of Total Commande or is an total of that value. ?

    Regards,

    Arjun

  • Daniel Vornicu
    Daniel Vornicu Rank 5 - Community Champion
    edited Apr 8, 2024 8:15AM

    Hello,

    As you can see in the semantic model capture (from the logical layer) the 2 calculations point to the same table column in the master table (F_COMANDES). The agregation is Sum for the both.

    I have 2 fact tables in my logical layer:

    1.Master table Fait-commande with LTS: A_COMMANDES and the calculation 'Total commande'

    2.Detail table Fait-details with LTS: A_F_DETAILS, but joins with A_FK_COMANDES, A_F_COMANDES(as in diagram) and the calculation 'Total des commandes' retrieved also from the master table

    For Q1 (top to bottom in 1-N relation) I use 'Total commande'

    For Q2 (bottom → top) I use 'Total des commandes'

    Regards,

    Daniel

  • Daniel Vornicu
    Daniel Vornicu Rank 5 - Community Champion

    Hello,

    BIG drawback in my model for the question bellow.

    Q3: The orders total amount (F_COMANDES) by item type (D_ITEM)?

    The first two items (Article 1 et Article 2 have both the type 1) et Article 3 of type 2. The both calculations are false in this case (in red). The quantity field (Cantite) is good.

    image.png

    Because each command has multiples occurrences in the detail table, 'Total des commandes' is multiplied when I want to display the order total (from F_COMANDES) by item type (from D_ITEM). By item name it's ok because the item is unique on the each order, but I have many identical item types on the same order.

    How I could solve this: filter first on item type, make a distinct select on command_id (in detail), then join to the master table to calculate the orders total.

    Remark: Any calculation on detail table (quantity * price) is EXCLUDED. I explicitly want to sum the field TOTAL_COMANDE (from the master table) because in the real exemple there are others calculations in the master table(durations etc) that cannot be moved in detail table.

    Thanks for help!

    Daniel