What is a multi-Valued Dimension

It is a dimension with a many-to-many relationship to the fac table, implying that it has a lower granularity than the fac table. Let me give you an example to help you understand:

The design of this data warehouse includes two dimensions, an author, a book, and one fact table. We'd like to receive a report on the sales of articles, some of which have multiple authors.

Assume that the first article was written by only one person, Alex, and the second article was written by two people, Joe and Peter.

As a result of integrating the data into the data warehouse, we will have the following types of data:

The issue here is that we have two different authors with the same article (two authors collaborate to write the same book), and calculating the total price will be incorrect because the article is being calculated twice.

So, how are we going to solve this problem?

To begin, we must create a group table with an ID (surrogate key), an Author ID, and a weighting Factor. A bridge table connects this table to the Author Group ID table.

The solution is summarized in the schema below:

As you can see, the bridge table refers to a group of authors who worked on a single book, so when we load the data into the Fact table, we'll have one group referring to one book, with a one-to-one relationship. The AuthorGrp Relationship, which contains the authors' contribution weights, is also mentioned in the bridge table.