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

Question

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:

* Find the quantity (F_DETAILS) of products sold by a specific client (D_CLIENT) ?
* 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)

The data:

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

F_DETAILS:

ID COMANDE_ID ITEM_ID QUANTITY UNIT_PRICE

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

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

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)

Did I made the things properly?

Thanks,

Daniel

Daniel Vornicu · Answer

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.

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

Daniel Vornicu · Answer

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

Mallikarjuna Kuppauru-Oracle · Answer

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