Hoja de repaso: Introduction à la modélisation dimensionnelle Kimball

📋 Plan du Cours

1. Kimball Dimensional Modeling Techniques Overview
2. Retail Sales Case Study in Dimensional Modeling
3. Procurement and Value Chain in Data Warehousing
4. Accounting Dimensional Modeling for General Ledger
5. Customer Relationship Management in DW/BI Systems
6. Financial Services: Supertypes and Subtypes in Banking Models
7. Transportation Case Study: Fact Tables and Granularity
8. Healthcare Industry Complex Dimensional Models
9. Electronic Commerce: Clickstream Web Data Dimensionality
10. Dimensional Modeling Process and Tasks
11. ETL Subsystems and Techniques
12. Common Myths about Dimensional Models

📖 1. Kimball Dimensional Modeling Techniques Overview

🔑 Notions clés & Définitions

• Dimension Table : Table de dimension qui fournit le contexte descriptif, contient de nombreux attributs et possède une clé primaire unique pour assurer l’intégrité référentielle.

📝 Points essentiels

• Fact tables express many-to-many relationships and have composite primary keys composed of foreign keys to dimension tables.
• Dimension tables provide descriptive context with many attributes and have a single primary key for referential integrity.

💡 À retenir

Les structures fondamentales du dimensional modeling reposent sur des tables de faits à grain relationnel et des tables de dimensions descriptives à clé unique. Certaines techniques spécialisées, comme les types 3 et 4 de dimensions à évolution lente, servent à conserver l’historique ou à isoler des attributs très changeants.

📖 2. Retail Sales Case Study in Dimensional Modeling

🔑 Notions clés & Définitions

• Dimensional Modeling : Has emer This book is loaded with specific, practical design recom- mendations based on real-world scenarios.
• Case Study : Bus Matrix .

📝 Points essentiels

• Numeric values used primarily for calculations belong in fact tables; stable numeric values used for filtering belong in dimension attributes.
• In header/line schemas, all header-level dimension foreign keys and degenerate dimensions should be included on the line-level fact table.
• Allocated facts arise when facts of differing granularity, such as header freight charges, must be allocated appropriately in the fact table.
• . . 283 Household Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286 Multivalued Dimensions and Weighting Factors . . . . . . . . . . . . . . . . . 287 Contentsxviii Mini-Dimensions Revisited . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 Adding a Mini-Dimension to a Bridge Table . . . . . . . . . . . . . . . . . . . . 290 Dynamic Value Banding of Facts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291 Supertype and Subtype Schemas for Heterogeneous Products . . . . . . . . . 293 Supertype and Subtype Products with Common Facts . . . . . . . . . . . 295 Hot Swappable Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 11 Telecommunications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 Telecommunications Case Study and Bus Matrix . . . . . . . . . . . . . . . . . . . 297 General Design Review Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 Balance Business Requirements and Source Realities . . . . . . . . . . . . .300 Focus on Business Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .300 Granularity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .300 Single Granularity for Facts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301 Dimension Granularity and Hierarchies . . . . . . . . . . . . . . . . . . . . . . . 301 Date Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302 Degenerate Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Surrogate Keys . . . . . . . .

💡 À retenir

Applying dimensional modeling principles to retail sales requires distinguishing numeric attributes used for filtering from numeric facts used for calculations, and carefully managing transaction hierarchies and fact granularity in header/line schemas.

📖 3. Procurement and Value Chain in Data Warehousing

🔑 Notions clés & Définitions

• Data warehousing : Business intelligence (DW/BI) industry certainly has matured since Ralph Kimball published the first edition of The Data Warehouse Toolkit (Wiley) in 1996.

📝 Points essentiels

• Current row indicator is used to identify the active version of a dimension row for ETL purposes.
• Mini-dimensions are created to handle groups of rapidly changing attributes separated from the base dimension.
• Rapidly changing monster dimensions are large dimensions with frequently changing attributes that benefit from mini-dimension splitting.
• 381 Multivalued Dimension Attributes .
• 380 Slowly Changing Dimensions .

💡 À retenir

La gestion des attributs qui changent rapidement dans les données d'approvisionnement nécessite une conception dimensionnelle spécialisée, telle que le découpage en mini-dimensions, pour maintenir la performance et la précision historique.

📖 4. Accounting Dimensional Modeling for General Ledger

🔑 Notions clés & Définitions

• Dimensional modeling : Has emerged as the leading architecture for building integrated DW/BI systems.

📝 Points essentiels

• General ledger fact tables capture financial transactions at the accounting period grain.
• Accounting period dimension provides temporal context specific to financial reporting cycles.
• Chart of accounts dimension organizes financial accounts hierarchically for aggregation and analysis.

💡 À retenir

Accounting dimensional models emphasize precise temporal and hierarchical financial contexts to enable accurate and meaningful general ledger reporting.

📖 5. Customer Relationship Management in DW/BI Systems

🔑 Notions clés & Définitions

• Operational systems : Systèmes utilisés pour l’enregistrement opérationnel des transactions et des activités courantes d’une organisation, distincts des systèmes analytiques.
• These systems : Optimized for high-performance queries as users’ questions often require that hundreds or hundreds of thousands of transactions be searched and compressed into an answer set.
• Systems are optimized : High-performance queries as users’ questions often require that hundreds or hundreds of thousands of transactions be searched and compressed into an answer set.

📝 Points essentiels

• Customer dimension contains descriptive attributes about customers for analysis.
• Customer lifecycle attributes track stages and changes in customer status over time.
• Customer segmentation enables grouping customers based on behavior or demographics for targeted analysis.
• If two performance measures have the same name, they must mean the same thing. Conversely, if two measures don’t mean the same thing, they should be labeled differently. Chapter 14 ■ The DW/BI system must adapt to change. User needs, business conditions, data, and technology are all subject to change. The DW/BI system must be designed to handle this inevitable change gracefully so that it doesn’t invali- date existing data or applications. Existing data and applications should not be changed or disrupted when the business community asks new questions or new data is added to the warehouse. Finally, if descriptive data in the DW/ BI system must be modified, you must appropriately account for the changes and make these changes transparent to the users. ■ The DW/BI system must present information in a timely way. As the DW/ BI system is used more intensively for operational decisions, raw data may need to be converted into actionable information within hours, minutes, or even seconds. The DW/BI team and business users need to have realistic expectations for what it means to deliver data when there is little time to clean or validate it. ■ The DW/BI system must be a secure bastion that protects the information assets. An organization’s informational crown jewels are stored in the data warehouse. At a minimum, the warehouse likely contains information about what you’re selling to whom at what price—potentially harmful details in the hands of the wrong people. The DW/BI system must effectively control access to the organ
• You may be disa Chapter 1 discusses the following concepts: ■ Business-driven goals of data warehousing and business intelligence ■ Publishing metaphor for DW/BI systems ■ Dimensional modeling core concepts and vocabulary, including fact and dimension tables ■ Kimball DW/BI architecture’s components and tenets ■ Comparison of alternative DW/BI architectures, and the role of dimensional modeling within each ■ Misunderstandings about dimensional modeling 1 Chapter 12 Different Worlds of Data Capture and Data Analysis One of the most important assets of any organization is its information.

💡 À retenir

Les modèles dimensionnels CRM mettent l’accent sur la capture des attributs clients et des étapes du cycle de vie afin de permettre une intelligence d’affaires personnalisée et adaptée aux besoins analytiques.

📖 6. Financial Services: Supertypes and Subtypes in Banking Models

🔑 Notions clés & Définitions

• Dimensional models : Analytical data structures designed to be simple and understandable for business users while enabling fast query performance.
• Normalized models : Data structures organized in third normal form (3NF) that reduce redundancy by dividing data into many discrete entities, each represented as a relational table.

📝 Points essentiels

• Supertype entities represent generalized concepts shared by multiple subtypes in banking models.
• Subtype entities capture specialized attributes unique to specific banking products or services.
• Banking product dimension models diverse financial products using supertype-subtype hierarchies for flexibility.

💡 À retenir

Financial services dimensional models leverage supertype-subtype structures to elegantly handle diverse banking product attributes.

📖 7. Transportation Case Study: Fact Tables and Granularity

🔑 Notions clés & Définitions

• Fact tables : Typically large and time consuming to load, but preparing them for the presentation area is typically straightforward.

📝 Points essentiels

• Transportation fact tables often use header/line schemas to represent shipments and shipment lines.
• Granularity defines the level of detail captured in fact tables, critical for accurate analysis.

💡 À retenir

La modélisation dimensionnelle pour le transport repose sur des tables de faits examinées à plusieurs granularités et sur une séparation nette entre attributs de dimension et valeurs de faits. Cette structure reste extensible et permet des analyses sans préférence intégrée pour un type de requête.

📖 8. Healthcare Industry Complex Dimensional Models

🔑 Notions clés & Définitions

📝 Points essentiels

• Patient dimension stores demographic and identifying information for healthcare analysis.
• Medical procedure dimension catalogs treatments and interventions with detailed attributes.
• Healthcare encounter fact tables record events such as visits or treatments at a defined grain.

💡 À retenir

Healthcare dimensional models must integrate complex patient and procedure data within a dimensional, atomic, and process-centric presentation area to enable comprehensive clinical analytics.

📖 9. Electronic Commerce: Clickstream Web Data Dimensionality

🔑 Notions clés & Définitions

• Data Warehouse : dépôt de données destiné à l’analytique, dans lequel les données peuvent être organisées selon une architecture d’entreprise et une zone de présentation dimensionnelle.

• Enterprise Data Warehouse : entrepôt de données d’entreprise qui, dans l’architecture décrite, repose sur des tables normalisées en 3NF et contient des données atomiques.

• Enterprise Data : données d’entreprise centralisées et persistantes, réutilisées dans plusieurs modèles dimensionnels pour permettre l’intégration des données et assurer la cohérence sémantique.

• data governance : ensemble de responsabilités de gouvernance des données à établir sur les principaux noms qui décrivent l’activité, afin de déployer des dimensions cohérentes et adaptées aux besoins de filtrage, de regroupement et d’étiquetage analytiques.

📝 Points essentiels

• Les tables de faits de clickstream capturent les interactions des utilisateurs avec les pages web à un niveau de granularité très fin.
• La dimension de session web regroupe les activités des utilisateurs en sessions pour permettre l’analyse comportementale.
• La dimension d’attribut de page décrit les caractéristiques des pages web afin de soutenir l’analyse de contenu.

💡 À retenir

Les modèles dimensionnels du commerce électronique visent à capturer des données d’interaction utilisateur très détaillées pour produire des analyses fines du comportement sur le web. La granularité élevée des faits, associée à des dimensions adaptées aux sessions et aux pages, est au cœur de cette logique.

📖 10. Dimensional Modeling Process and Tasks

🔑 Notions clés & Définitions

• Dimensional Modeling Process and Tasks : A chapter-level description of the responsibilities, how-tos, and deliverables for dimensional modeling design activity within the Kimball Lifecycle.
• Chapter 18 Dimensional Modeling Process : A chapter that outlines specific recommendations for tackling dimensional modeling tasks and gives a high-level overview of the activities encountered during the life of a typical DW/BI project.

📝 Points essentiels

• Dimensional modeling lifecycle includes requirements gathering, design, implementation, and maintenance.
• Identifying the business process is the first step to define the fact table scope.

💡 À retenir

Dimensional modeling lifecycle includes requirements gathering, design, implementation, and maintenance.

📖 11. ETL Subsystems and Techniques

🔑 Notions clés & Définitions

• ETL Subsystem : Composant responsable de l'extraction, de la transformation et du chargement des données dans les modèles dimensionnels, représentant une part importante du temps et des efforts nécessaires à la construction d'un environnement DW/BI.
• Type 1 Change : Méthode de gestion des changements qui remplace la valeur d'un attribut par une nouvelle valeur sans conserver l'historique des valeurs précédentes.
• Subsystems and Techniques : Extract, transformation, and load system consumes a disproportionate share of the time and effort required to build a DW/BI environment.

📝 Points essentiels

• ETL subsystems manage data extraction, transformation, and loading into dimensional models.
• Type 1 changes overwrite attribute values without preserving history.

💡 À retenir

ETL subsystems manage data extraction, transformation, and loading into dimensional models.

📖 12. Common Myths about Dimensional Models

🔑 Notions clés & Définitions

• Kimball Dimensional Modeling Techniques Overview : A set of design and development methods for dimensional data modeling that emphasize user-focused, intuitive, and high-performance data structures, incorporating lessons learned from extensive real-world business scenarios.

📝 Points essentiels

• Dimensional models are often misunderstood as overly simple, but they handle complex business logic.
• Dimensional schemas are flexible and can evolve, contrary to the myth of fixed schema rigidity.
• Dimensional modeling includes more than star schemas, such as snowflake and galaxy schemas.

💡 À retenir

Dispelling myths about dimensional modeling reveals its capability to manage complex business logic and its flexible schema designs, making it a powerful and adaptable approach for diverse analytical needs.

🧩 Compléments de couverture

1. Dimensional Modeling The Data Warehouse Toolkit: The Defi nitive Guide to Dimensional Modeling, Third Edition Published by John Wiley & Sons, Inc
2. Four-Step Dimensional Design Process
3. Step 3: Identify the Dimensions
4. Avoiding Fact-to-Fact Table Joins
5. Country-Specific Calendars as Outriggers
6. Mistake 1: Fail to Conform Facts and Dimensions
7. Step 5: Populate Dimension Tables with Historic Data
8. Chapter 5: Procurement This chapter reinforces the importance of looking at your organization’s value chain as you plot your DW/BI environment
9. 1996), Ralph Kimball devoted an entire chapter to describe the dichotomy between the worlds of opera- tional processing and data warehousing
10. iver results quickly and efficiently. Imagine an executive who describes her business as, “We sell products in various markets and measure our performance over time.” Dimensional designers listen carefully to the emphasis on product, market
11. Entity-relationship diagrams (ER diagrams or ERDs) are drawings that com- municate the relationships between tables
12. Figure 1-2, if there is no sales activity for a given product, you don’t put any rows in the table
13. Figure 1-6, dimension attributes supply the report filters and labeling, whereas the fact tables supply the report’s numeric values
14. Chapter 19: ETL Subsystems and Techniques, but we want to introduce this fundamental piece of the overall DW/BI system puzzle
15. First and foremost, does the restaurant serve good food? That’s the res- taurant’s primary deliverable. However, the decor, service, and cost factors also affect the patrons’ overall dining experience and are considerations when evaluating
16. ETL ETL ETL ETL Figure 1-8: Simplified illustration of the independent data mart “architecture
17. Myth 5: Dimensional Models Can’t Be Integrated Dimensional models most certainly can be integrated if they conform to the enterprise data warehouse bus architecture
18. Chapter 17: Kimball DW/BI Lifecycle Overview and Chapter 18: Dimensional Modeling Process and Tasks, but wanted to plant the seeds early so they have time to germinate
19. Semi-Additive, Non-Additive Facts The numeric measures in a fact table fall into three categories
20. 1. While multiple surrogate keys may be associated with an employee over time as their profile changes, the durable key never changes
21. Dimensions Null-valued dimension attributes result when a given dimension row has not been fully populated, or when there are attributes that are not applicable to all the dimen- sion’s rows
22. Chapter 254 Type 0: Retain Original With type 0, the dimension attribute value never changes, so facts are always grouped by this original value
23. Fact table surrogate keys, which are not associated with any dimension, are assigned sequentially during the ETL load process and are used 1) as the single column primary key of the fact table; 2) to serve as an immediate identifier of a fa
24. Avoid Fact-to-Fact Table Joins A BI application must never issue SQL that joins two fact tables together across the fact table’s foreign keys

📊 Tableaux de Synthèse

Fact tables vs dimension tables

Modeling techniques and cases

⚠️ Pièges & Confusions Fréquentes

1. Confusing numeric values used for calculations with stable numeric values used for filtering
2. Putting header-level dimension foreign keys only at the header level instead of the line-level fact table in a header/line schema
3. Forgetting that allocated facts must be handled when facts have differing granularity
4. Treating rapidly changing monster dimensions as a single base dimension instead of splitting them with mini-dimensions
5. Mixing up supertype entities with subtype entities in banking models
6. Joining two fact tables together across their foreign keys
7. Assuming operational systems are the same as analytical DW/BI systems

✅ Checklist Examen

1. Define fact tables as many-to-many structures with composite primary keys
2. Define dimension tables as descriptive tables with a single primary key
3. Place numeric measures used for calculations in fact tables
4. Place stable numeric values used for filtering in dimension attributes
5. Include header-level foreign keys and degenerate dimensions on the line-level fact table in header/line schemas
6. Recognize allocated facts when granularity differs across facts
7. Use mini-dimensions for rapidly changing attribute groups
8. Use the current row indicator to identify the active version of a dimension row
9. Model banking products with supertype-subtype hierarchies when needed
10. Avoid fact-to-fact table joins