Star Structure
Have you ever heard about the star structure in data modelling? At Power Partners we try to always use the star schema. The basic idea behind it is the following:
The data model consists of one or more fact tables that are connected to one or multiple dimension tables. A fact table records measurements or metrics for specific events (ex: the number of sales for each day). The dimension table, on the other hand, should be smaller and have more attributes (ex: the salesman address, his first name, surname, …). Those attributes will be characteristics for the fact tables that we don’t want to repeat at each of its rows.
There are 4 different types of fact tables that are going to be discussed in this blog post. The additive, the semi-additive, the non-additive and the factless tables.
Additive and semi-additive fact tables
First, the easiest, the additive fact table. For this kind of table we can sum or count numerical values on every dimension. Let’s take our example of a sales data model. In this case, we could sum the sales per salesperson (using DimSalesPerson table) or sum our sales per day (using DimCalendar table). This type of table is very easy to manage, and you don’t have to worry about making meaningless additions. However, this is not the case of every fact table. For instance, you might face a semi-additive table. If you have a fact table containing the daily inventory, for instance. Then, summing the inventory through the years won’t be valid, as it will sum up pieces of inventory multiple times (if some objects are present for multiple days in the same inventory). You should always think before you aggregate rows on certain dimensions. In this specific case, calculating averages stay valid.
Non-additive and factless tables
Non-additive fact tables are our third type of fact tables. Those can’t be summed up for any dimension, since they are mostly precalculated before being loaded in. A typical case is percentages (ex: conversion ratio per region). You cannot sum them up because it will lose its original meaning.
The last type of fact tables we are going to mention are the factless tables. Those tables only contain foreign keys. There are no fact values, thus no sums can be made.
If you want more information, I would recommend you watching this webinar.
