Accumulating Snapshots

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

These fact tables represent the complete life of an activity or process, that has a definite beginning and end. They have multiple date dimensions (roles) to refer to the events that occur during a process. These kind of tables are used for to the spans of time between different events (lag calculations).

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

Periodic Snapshots

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

When we need to track the values of some measurements at regular, predictable time intervals, we can use periodic snapshots. Instead of having a row for each event we take a picture (snapshot) of the activity at the end of the day, week, or month. Sometimes, periodic snapshots are just an aggregation of the transactions that occurred during a time period, however they have more predictable sparseness as there is one row of facts generated for each combination of the dimensions that it refers to. Periodic snapshots are suited for tracking long-running processes.

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

Factless Fact Tables

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

These tables have no facts. They are used to represent event tracking and coverage. Those that are used for events, relate different dimensions values at a point in time and space (e.g. encounters, orders). However, when designing such tables we add a dummy fact, which is usually called table_name_count that takes the value 1, just to make the SQL queries more clean. These tables can also be used to track coverage (for example have a fact table with one row for every test, location, day combination with a single fact denoting if it was used or not).

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

Transactional Fact Tables

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

These fact tables represent events that occurred at a specific point in time and space. They let us analyze data in extreme detail. Rows in these tables are found only if events take place. For example if there is no observation for a patient a given period then there is no information available for him and we won`t get any data for him in that period. Because of their unpredictable sparseness, we use other types of fact tables as described below.

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

Dimensional Roles

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

Sometimes a dimension is linked with a fact table multiple times. Each of these is called a dimension role. Although the dimension is stored as one physical table, each of its roles is implemented as a separate view, ideally with unique column names. This enhances the usability of the schema when querying.

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful