Modelling Aggregate Facts

Analytical Need

There are cases where our data arrives in aggregate form, like in Google Analytics (where the lowest granularity level is daily). If we wish to free up clutter in the dashboard and prevent the user from figuring out which field comes from which table, we'll need to make some modifications.

Modeling Challenge

We will want to create a unification of tables in order to create a cleaner, simpler schema. However, if there are tables that are aggregating the same field but with different categories we cannot unify them since the measures in the dashboard will create incorrect results.

Solution

Create a union query for all the tables that are aggregating different fields with empty fields in the appropriate locations.

The resulting tables will be connected to each other via the possible dimensions (mainly date). In order to create a custom dimension, please see Creating a common date selection.

From this: