Summary Tables

Suppose your Airline DWH end users tell you they need to make a lot of queries by Flight Year and Origin Airport.

Of course, this information is available in the Fact table, but in a real life application this table probably has many millions of rows.

To improve query speed for the users, it is quite easy to set up a Workflow that maintains a summary (or aggregate) table for specific needs like this. Below you can see such a workflow:

And below , a screenshot of the resulting Summary Table. Note that I had the workflow also add a flight_count fact, which might come in handy...

In our example Warehouse, the Facts table has 95.392 rows and the Summary table 830 rows. Quite a good reduction.