We have one central source for our measures and multiple sources to slice & dice these measures.
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
We will build a Star Schema. This schema contains one fact table and multiple dimensions that are connected to it. This is the most recommended model performance wise (when possible) and the simplest one to understand (Except for a schema with one table).
The elasticube schema will look like this:
Image 1. Typical Star Schema