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
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?
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?