There are situations when your data needs to represent a many to many relationships such that your dimension members are at a lower grain than related facts; aka multivalued dimension. In these cases, a single fact record should relate to multiple dimension values. Here are a few examples from the Kimball Group.
- Patients can have multiple diagnoses.
- Students can have multiple majors.
- Consumers can have multiple hobbies or interests.
- Commercial customers can have multiple industry classifications.
- Employees can have multiple skills or certifications.
- Products can have multiple optional features.
- Bank accounts can have multiple customers.
By using a bridge table, we can model a many-to-many relationship such as the following diagram.
The steps to build this model are:
- Keep the fact at the native grain such that each fact should relate to multiple values in the many-to-many dimension.
- Generate an ID for each distinct group of dimension values referenced by the fact table.
- Create a bridge fact table that relates the group IDs to the individual dimension members.
For a detailed description of how to build this model see the post below. In most cases you will need to build a process that converts records to string to efficiently create the ID groups.
Once you have your underlying data in a model such as the above, the data can be brought into an ElastiCube and appropriate relationships defined. Note that the dimension that contains the group IDs and the bridge fact table do not need to be exposed to users and may be hidden.
Here is an example an ElastiCube handling a many-to-many relationship.
And here is the data.
Now that we have the our many-to-many modeled and the cube built, we can get the results we are looking for. For example, if we are looking for claims total by patient, then the many-to-many does not impact the results.
However, if we are looking for claims by patient diagnosis, then we will see the same claim amount applied to each diagnosis.
This same technique can be used to handle cases where you have dimensions at a granularity lower than your fact grain; multivalued dimension.
