Search code examples
oracledata-warehouse

Dimension table, bridge table and fact table join

I have four tables in total: dimension tables- books and authors bridge table- book/author transactions fact

Since there are multiple authors for books and multiple books for an author, to resolve the many-to-many relationship a bridge table is used

Tables Structures are as follows:

Dim_Books:

book_id (PK) 
ISBN 
Title

Dim Author:

Author_id (PK)
FName
LName

Book_auth_bridge:

book_auth_id (PK)
book_id
auth_id

Fact_Sales:

Sale_amount
quantity
Book_id
book_auth_id

I am trying to retrieve the total sales of authors from the fact table, since I am joining with the bridge table, I always get multiple rows from the inner join and the amounts are duplicated.

How do I write a SQL to get the total sales for an author?

Solution

  • The most flexible solution is to include an "allocation factor" column in your bridge table and multiple that with your measure(s) to get the required proportions.

    For example, if you wanted to allocate equally between 3 people then you'd have a factor of 0.333 in all 3 bridge table records. However, if you wanted to allocate 50% to a lead author and split the remainder equally with the other authors you would have factors of 0.5, 0.25 and 0.25