Optimized analytical data storage and retrieval using Star schemas

4:45 min

Expert

In the world of data analytics, a well-structured and efficient data model is crucial for extracting meaningful insights. In this article, we will explore the concept of Star schemas and delve into why they are considered a great choice for Online Analytical Processing (OLAP).

Understanding Star Schemas

A star schema is a type of dimensional model that organizes data into a central fact table surrounded by multiple dimension tables. The fact table contains the measurements or metrics, while the dimension tables represent the categories or attributes related to those measurements. This structure resembles a star, hence the name.

Fact table

The Fact table is the core of a star schema data model, storing performance measurements which result from business process events. In a fact table, each row typically corresponds to measurement event at a consistent level of detail (i.e. grain), and (mostly) contains additive measurements. Besides that, you'll also find several Foreign Key (FK) columns in a fact table; each of these reference a Primary Key (PK) of a specific dimension table. With these Foreign Keys, you can easily lookup details around a fact and use those dimension columns to slice-and-dice the data into meaningful insights.

There are several types of Fact tables, each having their own use-cases. The most common and easy-to-understand fact table is a "transactional" fact table, where each row corresponds to a transaction - a specific measurement event at a point in time and space. Another common fact table is the "snapshot", which compare snapshots over time (i.e. a "periodic snapshot"), or evaluate predictable steps in pipeline or workflow processes (i.e. an "accumulating snapshot").

For our example company "Stellar Sales", a transactional fact table would contain one row per order. The row would contain a "Product ID", "Customer ID", "Store ID", etc.; these are references to unique rows in one of the dimension tables (see below). Next to that, the row also contains information about the order itself: for example, an "Order amount", "Order Value", "Order Creation date", etc. In the next sidenote, you can see an example of the structure of such fact table and its related dimension tables. In case we'd like to visualize insights around the workflow process of our order fulfillment (e.g. how long does it take on average to go from "order received" to "order prepared", where are the bottlenecks in our process, etc.), an "accumulating snapshot" fact table would be a good choice; in here, each row will contain at least one column per step in our order fullfillment process (e.g. a "order received date", "order picked up date", "order picked up duration", "order prepared date", "order prepared duration", etc.).

Dimension tables

Dimension tables are integral companions to a fact table, as they contain (textual) context associated with a measurement in a fact table's row; each foreign key in a fact table's column will refer to a primary key in the related dimension table. Typically dimension tables allow you to look up answers to "who, what, where, when, how, and why" questions. While fact tables typically have many rows, dimension tables often have several columns but fewer rows.

Advantages of star schemas

Simplified and performant querying

One of the key advantages of Star schemas is their ability to simplify visualizing analytical insights, which leads to improved query performance and faster data retrieval. With an appropriately designed star schema, it should be easy to understand where to retrieve the necessary data to gain your desired insights; this facilitates and enables dashboard designers to create meaningful data visualizations by slicing-and-dicing one fact and dimension tables.

Scalability and flexibility

Star schemas are highly scalable and flexible. New dimension attributes can be easily added to a schema without impacting the existing structure, new dimension tables would likely require existing fact table rows to be updated with the relevant foreign key to the new dimension table's row. Star schemas allow for seamless integration of new data sources, and the ability to gracefully accomodate ever-changing business requirements.

Example star schemas of different industries

Star schemas find extensive use in various industries and analytical scenarios. Some common use cases include:

Marketing insights: A fact table with one row per event (email clicked, page opened, etc.), and dimension tables with marketing campaigns, channels, recipients, etc.
Human Resources (HR) insights: A fact table with one row per employee workday, and dimension tables with employees, departments, offices, etc.
Project Management insights: A fact table with one row per task, and dimension tables around projects, employees, task types, etc.
Security insights: A fact table with one row per vulnerability in a piece of software, and dimension tables to look up information about the software itself, severity details, response teams, etc.

In conclusion, Star schemas offer a powerful and efficient foundation to organize and analyze data. With their simplified querying, enhanced performance, and scalability, they are well-suited for analytical consumption in BI tools like Luzmo. Now that it's clear what a star schema is and when to use it, our next article will show you the recommended path to designing your own star schema!

Need more information?

Do you still have questions? Let us know how we can help.

Send us feedback!

Course Outline

Introduction (2 lessons)

Designing effective data models for analytics