Fact Table:

• A fact table contains quantitative data for analysis, typically numerical or measurable data called facts.
• It is the central table in a star schema or snowflake schema of a data warehouse.
• Fact tables usually consist of foreign keys that reference the primary keys in dimension tables, along with the measured or quantitative data related to those keys.
• Fact tables are often large and store historical data.

Dimension Table:

• Dimension tables provide context to the facts stored in the fact table.
• They contain descriptive attributes related to the facts stored in the fact table.
• Dimension tables are typically smaller than fact tables and are used to categorize, describe, and provide context to the data in the fact table.
• Dimension tables are often denormalized, meaning they may store redundant data for the sake of query performance and simplicity in reporting.

note 
- in fact table we will have foriegn key
- in dimesional table we will have primary key

---

https://medium.com/@gunjansinghtandon/data-warehouse-structures-fact-dimension-table-0dd4091c1127

Normalized vs. Denormalized

Normalized vs. Denormalized Form In normalized form, data is stored in multiple tables, joined and referenced by primary-foreign keys. This helps in reducing data redundancy and inconsistency. In the denormalized form, data is stored in a limited number of tables (maybe a single table) to reduce querying time

What is Dimensional Modeling

The data model used to store data in the denormalized form is called Dimensional Modeling. It is the technique of storing data in a Data Warehouse in such a way that enables fast query performance and easy access to its business users. It involves creating a set of dimensional tables

The core concept of dimensional modeling is the creation of a star schema.

Fact Tables

Fact tables are the heart of a data warehouse. They contain quantitative data, often referred to as measures or metrics, and are the focus of most data analysis. These tables store data related to business transactions and events, such as sales figures, revenue, or quantities sold. In essence, fact tables provide the “what” in data analysis.

Dimension Tables

Dimension tables, on the other hand, offer context to the data stored in fact tables. They provide descriptive information that helps users understand the “who,” “where,” and “when” aspects of the data.

Relationship Between Fact and Dimension Tables

Fact and dimension tables are interdependent. Fact tables include foreign keys that link to primary keys in dimension tables.

Real-case scenarios of Dimensional Modeling

Scenario: The e-Commerce industry

The e-commerce industry is widely known for selling and buying goods over the Internet. We are going to create a model for the below three business processes:

• products bought by the customers.
• capturing the delivery status of the product.
• maintaining the inventory so that items don’t run out of stock.

solution

• individual product ordered by a customer
• status of an individual product shipped from the warehouse
• daily inventory for each product in each store

Our dimensional model will have the following dimensions:

• Date Dimension: This dimension table is used in almost every dimensional model as it helps monitor the business’s performance with time.

• Product Dimension: This table will contain information regarding the product ordered.

• Order Dimension: This detail will contain information regarding the order.

• Customer Dimension: This dimension table will contain the customer’s information.

• Promotion Dimension: This table covers the promotion condition under which the product was sold. The promotion conditions include temporary sales, reduction in price, discounts, etc.

Fact Table 1: Individual product of the order per row.

Fact Table 2: Delivery Status of individual products in the order

Fact Table 3: Daily inventory for each product in each store.

If we combine all the three fact tables, our final model will look like this:

Our final model will look like this:

questions

https://medium.com/@goyalarchana17/data-modeling-tackling-scenario-based-questions-561c12d3a492