Concepts of dimensional data modeling

To build a dimensional database, you start with a dimensional data model. The dimensional data model provides a method for making databases simple and understandable. You can conceive of a dimensional database as a database cube of three or four dimensions where users can access a slice of the database along any of its dimensions. To create a dimensional database, you need a model that lets you visualize the data.

Suppose your business sells products in different markets and you want to evaluate the performance over time. It is easy to conceive of this business process as a cube of data, which contains dimensions for time, products, and markets. The following figure shows this dimensional model. The various intersections along the lines of the cube would contain the measures of the business. The measures correspond to a particular combination: product, market, and time data.
Figure 1. A dimensional model of a business that has time, product, and market dimensions
A cube is shown. The three axes of the cube (x, y, and z) are marked as "product", "market", and "time" respectively.
Another name for the dimensional model is the star schema. The database designers use this name because the diagram for this model looks like a star with one central table around which a set of other tables are displayed. The central table is the only table in the schema with multiple joins connecting it to all the other tables. This central table is called the fact table and the other tables are called dimension tables. The dimension tables all have only a single join that attaches them to the fact table, regardless of the query. The following figure shows a simple dimensional model of a business that sells products in different markets and evaluates business performance over time.
Figure 2. A typical dimensional model
There are four tables represented by rectangles. Each rectangle contains a list of the column names that are part of that table. Lines connect columns between tables where there is a join. The "Sales fact table" is in the center. It is joined to the other three tables. It has the following columns: "time_key", "product_key", "store_key", "dollars_sold", "units_sold", and "dollars_cost". The "Sales fact table" is joined to the "Time dimension" table through the column "time_key". The columns of the "Time dimension" table are: "time_key", "day_of_the_week", "month", "quarter", "year", and "holiday_flag". The "Sales fact table" is joined to the "Product dimension" table through the "product_key" column. The columns of the "Product dimension" table are: "product_key", "description", "brand", and "category". The "Sales fact table" is joined to the "Store dimension" table through the "store_key" column. The columns of the "Store dimension" table are: "store_key", "store_name", "address", and "floor_plan_type".
Parent topic: Design a dimensional data model
Copyright© 2019 HCL Technologies Limited