on - edited on by
DRay
Analytical Need
We have multiple sources for our measures and multiple sources to slice & dice these measures.
Modeling Challenge
How do we build a schema that will enable us to perform our needed analysis?
We will need to connect all of our tables so every criteria will be able to filter the population for the measures
Solution
We will build a Multi Fact Schema. This schema contains multiple fact table and multiple dimensions that are connected to them. Best practice recommends that all dimension tables will be directly connected to all Fact tables (this is in order to avoid indirect connections between tables - which can cause unwanted results. Would you like to know more? please refer to Handling Relationship Cycles).
The keys from the fact tables will need to become invisible, in order to prevent confusion in the dashboard design, where several fields will mean the same. That is why we will only keep the key field from the dimension tables and the filters in the dashboard will only use fields from the dim tables.
The elasticube schema will look like this:
Image 1. Typical Multi Fact Schema (Facts on the left , Dims on the right)
Attachments
- 473 KB - Multi Fact.ecdata
