Introduction to Dimensional Modeling

A dimensional model is a data model that has been optimized for data storage and faster data retrieval in a data warehouse. As part of a data warehouse, it reads, analyzes, and summarizes information, thus playing a pivotal role in business analysis and decision-making.

Every dimensional model is composed of fact tables and dimension tables. A fact table is traditionally the central table of the model and consists of quantitative information such as measures and metrics. A dimension table, on the other hand, contains descriptive information and is referenced to in the fact table through a foreign key. Together, fact tables and dimensional tables make up the star schema of the model, with a fact table in the center, surrounded by related dimension tables.

Note: The star schema is the simplest schema for a dimensional model. Some extensions of it include the snowflake schema and galaxy schema.

In Astera Data Warehouse Builder, you can assign an entity type (fact or dimension) to each general entity in a data model, turning it into a dimensional model. For dimension entities, you can assign dimension roles, including surrogate keys, business keys, and slowly changing dimensions, to each field. Similarly, in fact entities, you can assign fact roles. Moreover, the toolbox contains date and time dimension entities that can also be used as part of a dimensional model.

Here is a sample dimensional model that comprises a star schema.

The entity named Sales represents the fact table and the rest of entities represent dimension tables.

In the next article, we’ll talk about converting a data model to a dimensional model in Astera Data Warehouse Builder.