Tabla de hechos y dimensiones | Modelo Estrella 💫 [DATA MART]

Introduction to Designing and Developing a Star Schema

In this video, we will learn about designing and developing a star schema using a normalized database with sales data from a car agency.

Normalization of the Relational Database

• A normalized database ensures there is no data repetition in tables.

• Non-key values are not repeated in any table.

• Fields in a table depend on the key.

• Values of each field should be indivisible.

Types of Relationships in the Tables

• One-to-many relationships exist between brands and products.

• One-to-many relationships also exist between types and products.

• Brands are linked to specific products through their primary key (brand_id).

• Types link products as either trucks or cars through their primary key (type_id).

Creating the Dimension Table: Products

• The dimensional model integrates the three entities into one table called "products".

• This table contains values from brands, types, and products that were previously normalized.

• All values are related to the product's primary key since it has the lowest granularity level.

Creation of Fact Table

Now we will create the fact table by combining information from two tables: invoices and invoice details.

Invoices Table

• Contains general sales information such as invoice date, payment status, and dealership location.

Invoice Details Table

• Contains detailed information about each sale, including product sold, quantity, price, etc.

Fact Table: Sales

• Combines data from invoices and invoice details tables.

• Includes quantitative values like quantity sold, unit price, subtotal, tax amount, total sales amount.

• Each record includes a date and foreign keys linking to dimensions for further analysis.

Benefits of Star Schema Design

Although star schema design may seem different from relational models, it offers several benefits for data analysis.

Analysis Dimension

• Star schema allows for dimensional analysis, such as analyzing product quantity sold by brand and state over a specific time period.

• This type of analysis is possible due to the star schema's design in a data warehouse.

• The fact table with related dimensions is known as a data mart.

Data Mart and Data Warehouse

A data mart is part of a larger structure called a data warehouse. Let's explore this concept further.

Data Mart: Sales

• Represents sales data from an ERP or point-of-sale system stored in a database.

• Extracted using ETL tools into a star schema for business intelligence purposes.

Data Warehouse

• Consists of multiple data marts serving different analytical purposes (e.g., finance, production, human resources).

• Each data mart has its own fact tables, metrics, indicators, and related dimensions.

Conclusion

In this video, we learned about designing and developing a star schema using normalized databases. We explored the benefits of star schema design for dimensional analysis and how it fits within the larger structure of a data warehouse.