Solving Many-to-Many Relationship in Dimensional Modeling

0
238
3 min read

Bridge table solution

We will use a simplified book sales dimensional model as an example to demonstrate our bridge solution.

Our book sales model initially has the SALES_FACT fact table and two dimension tables: BOOK_DIM and DATE_DIM. The granularity of the model is sales amount by date (daily) and by book.

Assume the BOOK_DIM table has five rows:

BOOK_SK

TITLE

AUTHOR

1

Programming in Java

King, Chan

3

Learning Python

Simpson

2

Introduction to BIRT

Chan, Gupta, Simpson (Editor)

4

Advanced Java

King, Chan

5

Beginning XML

King, Chan (Foreword)

The DATE_DIM has two rows:

DATE_SK

DT

1

11-DEC-2009

2

12-DEC-2009

3

13-DEC-2009

And, the SALES_FACT table has ten rows:

DATE_SK

BOOK_SK

SALES_AMT

1

1

1000

1

2

2000

1

3

3000

1

4

4000

2

2

2500

2

3

3500

2

4

4500

2

5

5500

3

3

8000

3

4

8500

Note that:
The columns with _sk suffixes in the dimension tables are surrogate keys of the dimension tables; these surrogate keys relate the rows of the fact table to the rows in the dimension tables.
King and Chan have collaborated in three books; two as co-authors, while in the “Beginning XML” Chan’s contribution is writing its foreword. Chan also co-authors the “Introduction to BIRT”.
Simpson singly writes the “Learning Python” and is an editor for “Introduction to BIRT”.

To analyze daily book sales, you simply run a query, joining the dimension tables to the fact table:

SELECT dt, title, sales_amt 
FROM sales_fact s, date_dim d, book_dim b
WHERE s.date_sk = d.date_sk
AND s.book_sk = b.book_sk

This query produces the result showing the daily sales amount of every book that has a sale:

DT

TITLE

SALES_AMT

11-DEC-09

Advanced Java

4000

11-DEC-09

Introduction to BIRT

2000

11-DEC-09

Learning Python

3000

11-DEC-09

Programming in Java

1000

12-DEC-09

Advanced Java

4500

12-DEC-09

Beginning XML

5500

12-DEC-09

Introduction to BIRT

2500

12-DEC-09

Learning Python

3500

13-DEC-09

Advanced Java

8500

13-DEC-09

Learning Python

8000

You will notice that the model does not allow you to readily analyze the sales by individual writer—the AUTHOR column is multi-value, not normalized, which violates the dimension modeling rule (we can resolve this by creating a view to “bundle” the AUTHOR_DIM with the SALES_FACT tables such that the AUTHORtable connects to the view as a normal dimension. We will create the view a bit later in this section). We can solve this issue by adding an AUTHOR_DIM and its AUTHOR_GROUP bridge table.

The AUTHOR_DIM must contain all individual contributors, which you will have to extract from the books and enter into the table. In our example we have four authors.

AUTHOR_SK

NAME

1

Chan

2

King

3

Gupta

4

Simpson

The weighting_factor column in the AUTHOR_GROUP bridge table contains a fractional numeric value that determines the contribution of an author to a book. Typically the authors have equal contribution to the book they write, but you might want to have different weighting_factor for different roles; for example, an editor and a foreword writer have smaller weighting_factors than that of an author. The total weighting_factors for a book must always equal to 1.

The AUTHOR_GROUP bridge table has one surrogate key for every group of authors (a single author is considered a group that has one author only), and as many rows with that surrogate key for every contributor in the group.

LEAVE A REPLY

Please enter your comment!
Please enter your name here