What is dimensional modeling? Design the data warehouse for a wholesale furniture company. The data warehouse has to allow analyzing the company's situation at least with respect to the furniture. Customer and Time. More ever, the company needs to analyze: The furniture with respect to its type, category, and material. The customer with respect to their spatial location, by considering at least cities, regions, and states. The company is interested in learning the quantity, income, and discount of its sales.

Dimensional Modeling

• Dimensional Modeling (DM) is a data structure technique that is used to optimize the data storage in a Data warehouse.

• This will optimize the database for faster retrieval of data.

• A Dimensional Model in the data warehouse is designed to read, summarize, and analyze numeric information like values, balances, counts, weights, etc. in a data warehouse.

• In contrast, Relation Models are optimized for adding, updating, and deletion of data in a real-time Online Transaction System.

• In the Relational Model, normalization and ER models reduce redundancy in data.

• But, the Dimensional Model in the data warehouse arranges data in such a way that it is easier to retrieve information and generate reports.

• Therefore, Dimensional models are used in data warehouse systems and are not a good fit for relational systems.

Data Warehouse for Wholesale Furniture Company

• Let's see the Elements of the Dimensional Data Model for the given Wholesale Furniture Company.

Fact -

• Facts are the measurements or metrics or facts from the business process.
• Therefore, for the given wholesale furniture company, the measurement would be Sales Number.

Dimension -

• Dimension provides the context surrounding a business process event.
• In simple terms, they give who, what, whereof a fact.
• A dimension is a window to view the information in the facts.
• In the given wholesale furniture company, for the fact Sales Number, the dimension would be

Furniture (Type, Category, Material)

Customer (Age, Gender, City → Region → State)

Time (Day → Month → Year)

Measures -

• Measures provide the terms in which performance or profit is decided for the businesses.
• In the given wholesale furniture company, the measures would be

Quantity, Income, Discount

Operational Database Schema -

• Based on the above information like facts, dimensions, and measures operational database schema should be represented as follows:

Attribute Tree & Fact Schema -

• Based on the above information from facts sales the attribute tree and fact schema should be represented as follows:

Star Schema -

• In this dimensional model for the given Wholesale Furniture Company is implemented in the form of Star Schema.
• The schema is nothing but the database structure or arrangement of tables.
• It is called a star schema because the diagram resembles a star, with points radiating from a center.
• The center of the star consists of the fact table, and the points of the star are dimension tables.
• The fact tables in a star schema are in the Third Normal Form (3 NF) whereas dimensional tables are de-normalized.