Dimensional modeling is a modeling technique adapted to decision-making data. This technique is oriented by business processes to structure historical data which will be queried by complex queries in order to produce the indicators necessary for decision-makers.
Let’s review the fundamental concepts to be assimilated before producing an initial dimensional schema.
Star schema
A star schema is structured around fact tables and dimension tables. Dimension tables are used to describe business entities, for example products, customers, suppliers, dates. They contain columns used to describe these entities. Fact tables contain measures used to describe business events, for example the number of products, the time taken to receive an order. The fact table contains key columns that reference the primary keys of the dimension tables.
A star schema is a non-normalized method of structuring data as opposed to the normalization approach used in relational databases. We also present the reasons why modeling in snowflake should be avoided.
Granularity
The concept of granularity defines the degree of precision of our data. Granularity is deceptively difficult to define, because concepts are commonly confused in everyday language. This stage therefore requires all your attention.
Facts
The star schema revolves around the notion of facts, which enables the definition of observations or events that are measured using metrics. It is important to carefully consider the nature of the facts to be defined in order to avoid sources of errors. If you have one solution of 60° alcohol in your cupboard and another 90° solution in your medicine chest, how many bottles do you have? And if you mix them together, what will be the strength of the resulting mixture? You must constantly bear in mind that not everything can be added together …
Types of keys
Rediscover the different types of keys used in relational databases, and discover which keys are specific to business intelligence.
Degenerate dimension
A star schema is based on facts that we want to measure and which are observed according to several dimensions (time, geography, client, product, etc.). These dimensions correspond to data dimensions and can be likened to lists (of days, places, customers, products) possessing different properties. But how should we proceed if one of these dimensions has no properties?
Conformed dimensions
Conformed dimensions form the cornerstone of data warehouses by ensuring the consistency of data in different datamarts (each based on a star schema). They ensure the consistency of the results produced for two queries concerning two different business processes. Wouldn’t it be a shame if the use of different names made it impossible to link the products sold to the products ordered? In reality, this type of problem is a frequent occurrence if rigorous data modeling is not carried out.
A set of fact tables and conformed dimensions can be used to create a bus architecture.