Kimball Bottom-Up Data Warehouse Architecture

Dimensional Modeling:

• This approach employs a star schema or snowflake schema to structure the data in each data mart.
• Fact tables store quantitative data (e.g., sales revenue, quantity sold), while dimension tables store descriptive data (e.g., product details, time, customer information).

Key Characteristics

• Data Marts First: Develops small, focused data marts for individual business functions, such as sales, marketing, and finance, addressing specific analytical needs.
• Dimensional Modeling: Each data mart is structured using dimensional modeling (e.g., star schema), with fact tables for quantitative data and dimension tables for descriptive details, optimizing query efficiency.
• Conformed Dimensions for Integration: Standardized dimensions like time, geography, and product enable seamless integration and consistent reporting across departments.
• Enterprise Data Warehouse (EDW): As data marts are integrated, they form a central EDW, providing a unified source for cross-functional analytics and consolidated insights.
• Business Intelligence (BI) and Analytics: The EDW supports various BI and analytics tools, allowing departments to leverage dashboards, reports, and insights to make informed decisions.

Advantages

• Incremental Implementation: Enables rapid deployment of data marts, providing immediate value without waiting for a fully developed data warehouse.
• Flexibility and Adaptability: New data marts can be added and integrated as needed, allowing for scalable growth and adaptation to evolving business requirements.
• Focused Optimization: Each data mart is optimized for specific business functions, ensuring efficient, targeted insights for departments.

The following are common database schemas used in relational database management systems (RDBMS), including the Star Schema, from which the Snowflake Schema is derived:

1. Star Schema:
   • The foundational schema that involves a central fact table connected directly to several dimension tables.
   • Simpler structure with denormalized dimension tables.
   • Offers faster query performance due to fewer joins.
2. Snowflake Schema:
   • Derived from the Star Schema.
   • Normalizes dimensions into multiple related tables.
   • Reduces redundancy but can make queries more complex.
3. Galaxy Schema (Fact Constellation Schema):
   • Contains multiple fact tables that share dimension tables.
   • Used for more complex data warehouses with multiple data marts.
4. Hierarchical Schema:
   • Organizes data in a tree-like structure.
   • Used for databases that require a clear parent-child relationship, like organizational charts.
5. Network Schema:
   • Similar to the Hierarchical Schema but allows more complex relationships with many-to-many connections.
   • Often used for more complex, non-hierarchical relationships.
6. Flat Schema:
   • A single table or a simple structure without hierarchical relationships.
   • Typically used for small, simple databases.

These schemas are utilized based on the specific needs of the database structure, data integrity, and query complexity.

RDBMS Star Schema

The Star Schema is a popular database schema design used in data warehousing. It is named for its resemblance to a star, where a central fact table is surrounded by dimension tables.

Components of Star Schema

• Fact Table: Contains the quantitative data for analysis, such as sales amounts or quantities. The fact table links to dimension tables through foreign keys.
• Dimension Tables: Store descriptive attributes related to the facts, such as product names, customer details, or dates.

Example of Star Schema

Consider an e-commerce sales data warehouse:

• Fact Table: Sales
• Dimension Tables:
• Product (ProductID, ProductName, Category, Price)
• Customer (CustomerID, CustomerName, Region, AgeGroup)
• Date (DateID, Date, Year, Quarter, Month)
• Store (StoreID, StoreName, Location)

Advantages

• Simplicity: Easy to understand and query.
• Optimized Query Performance: Efficient joins between fact and dimension tables.
• De-normalization: Reduces the number of joins needed for queries.

Disadvantages

• Redundancy: Can lead to increased storage requirements.
• Limited Flexibility: Best suited for simple queries.