Dimensional Data Modelling training course

1. Why data modelling:
   • The importance of data modelling in the context of database design and management. It will highlight how data modelling helps in organizing data, improving data quality, and facilitating data analysis.
2. The importance of data modelling:
   • This section will delve deeper into the benefits of data modelling, such as improving communication between stakeholders, reducing redundancy, and enhancing data consistency.
3. Which data model: normalization vs denormalization:
   • Two main approaches to data modelling: normalization and denormalization. Why to break the golden rules of Normalization and how to do it.
   • Difference between highly i/o databases (operational databases) and a data warehouse organised for analysis/reporting
4. How much do we de-normalize: star vs snowflake:
   • Understand the STAR schema
   • Analise when this solution can be extended to a SNOWFLAKE schema and why
   • STAR schema and performance
5. Kimbal methodology vs Immon’s:
   • This point will compare the two leading methodologies for data warehousing: the Kimball methodology and the Inmon methodology. It will highlight the key differences and provide guidance on which methodology to use in different scenarios.
6. What is a fact table:
   • This section will explain the concept of a fact table - what’s to be included in a fact table
   • How many fact tables do we need
   • Data marts.
7. What is a dimension table:
   • Define the role of Dimensional tables
   • What is going into dimensional tables
   • How many dimensions do we need
8. Fact Table: how to choose the granularity:
   • Defying granularity is key to building a fact table
     • How  do we define granularity
     • What’s the impact on performance
     • What level of granularity do we REALLY need
9. Special dimensions:
   • Dimensions into a fact table degenerate dimension
   • Junk dimension
   • Date dimension
10. Factless fact tables:
    •  Tables to capture  to capture events, conditions, connections between tables
11. Dimension tables:
    • Attributes:
    • Conformed dimensions:
    • Bus matrix:
      • Decide how dimension and fact are connected
      • Which dimension can be used in different datamarts
      • Which dimension can be re-used
      • How the datamarts connect.
12. Slow changing dimensions: type 1,2,3,6:
    • How to integrate change in the dimension table without losing history

• Type 0: Retain
• Type 1: Overwrite 
• Type 2: Add New Row 
• Type 3: Add New Attribute 
• Type 4: Add History Table 
• Type 6: Combined Approach

13. Role playing dimensions:
    • How dimensions that can be used in multiple roles within a data warehouse.
14. Date dimension:
    • why we need it
    •  how we build it
    • How big should it be
15. Relationship between tables:
    • One to many
    • Many-to-many?
    • Relate Fact Tables?
    • Relate dimensional tables
16. Keys and surrogate keys:
    • What type of keys can we use
    • What is the purpose of a surrogate key
    • Do we need unique keys in Fact tables

Database planning and building:

This section will provide an overview of the process of planning and building a database, including defining the requirements, designing the schema, and implementing the database.

17. Business requirements: gathering and definition:
    • The importance of gathering and defining business requirements before designing a database
    • how to gather requirements and document them effectively.
18. The logical design: why and how:
    • the steps involved in creating a logical design
    • what tools can we use
19. The physical design:

• Hardware and Software Selection: Choose the appropriate hardware and software platforms for the data warehouse. This includes selecting servers, storage solutions, database management systems, and ETL (Extract, Transform, Load) tools.
• Infrastructure Setup: Set up the physical infrastructure, including servers, storage, and networking equipment. This involves configuring hardware, installing software, and setting up network connectivity.
• Data Integration: Develop ETL processes to extract data from source systems, transform it into the desired format, and load it into the data warehouse. This step requires significant effort to ensure data quality, consistency, and reliability.
• Data Storage Design: Design the physical storage layout, including partitioning, indexing, and storage allocation. Optimize the storage for performance, scalability, and manageability.
• Data Loading and Validation: Load the data into the data warehouse and validate it to ensure accuracy and completeness. This involves running data validation checks and performing data cleansing activities.
• Performance Tuning: Optimize the data warehouse for performance by tuning queries, indexing, and storage. This step may involve ongoing monitoring and adjustments to ensure optimal performance.
• Security and Access Control: Implement security measures to protect the data warehouse, including user authentication, authorization, and encryption. Define access controls to ensure that only authorized users can access the data.
• Testing and Validation: Conduct thorough testing of the data warehouse, including functional, performance, and integration testing. Validate that the data warehouse meets the business requirements and performs as expected.
• Documentation and Training: Document the data warehouse design, processes, and usage guidelines. Provide training to users and administrators to ensure they understand how to use and maintain the data warehouse.
• Deployment and Maintenance: Deploy the data warehouse to the production environment. Monitor, maintain, and update the data warehouse to ensure it continues to meet business needs and performs effectively.

20. Multiple sourcing:
    • How do we integrate multiple sources into a data warehouse in a seamless manner
21. Data staging:
    • Where do we store staging data
    • Do we need a staging database?
22. ETL or ELT?:
    •  Extract, Transform, Load (ETL)
    •  Extract, Load, Transform (ELT).
    • explain the pros and cons of each approach and provide guidance on when to use each one.
23. Database, data warehouse, data lake, lakehouse:

• differences between a database, a data warehouse, a data lake, and a lakehouse. It will provide examples of when to use each type of data storage solution.