I'm learning dimensional modeling and have a question.

I understand those basic examples such as a FactSale has DimItem, DimLocation.

But when it comes to complicated cases with many-to-many relationships, how should I design the dimension table?

For example, in an example of Author-Book-Reviewer, a book can be written by multiple authors and can be reviewed by multiple reviewers; One author can write multiple books; One reviewer can review multiple books.

In a relational model it looks like this Text

But how do I transform this (OLTP) model into a (OLAP) star schma model?

Can I make "Book" another dimension? and we have DimBook, DimReviewer, DimAuthor? Text

But if I model it like this, the single fact seems lost its meaning. Because the same book id can appear multiple times, and the one author and one reviewer doesn't make sense in the fact table.

So if I wanted to make Book a fact, should I use "Bridge table" technique from Ralph Kimball's book?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.