This title slide introduces a beginner-friendly training session titled "Dimensional Modeling 101." It welcomes participants to the foundational course with a simple, inviting subtitle.

Welcome to Dimensional Modeling 101

Beginner-Friendly Training

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The presentation agenda outlines key topics starting with the foundations of data and databases, including basics like tables, attributes, and relationships covered in slides 1–6. It then progresses to comparing OLTP and OLAP systems, designing star schemas, finance applications with diagrams in slides 7–29, and concludes with key takeaways and next steps.

Presentation Agenda

1. 🏗️ Foundations of Data & Databases

Intro to data basics, databases, tables, attributes, and relationships (Slides 1–6).

2. ⚖️ OLTP vs OLAP and Core Concepts

Compare OLTP/OLAP systems and explain dimensional modeling fundamentals (Slides 7–14).

3. 🛠️ Designing Star Schemas

Step-by-step guide to building and validating star schemas (Slides 15–21).

4. 💰 Finance Applications & Diagrams

Real-world GL, AP, AR examples with visual schema diagrams (Slides 22–29).

5. ✅ Conclusion

Key takeaways, summary, and next steps for beginners. Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This section header slide introduces the foundational concepts of data and databases, marking it as Section 1 in the presentation. It features a subtitle highlighting the basics of data, databases, tables, attributes, and relationships.

Section 1: Foundations of Data & Databases

01

Foundations of Data & Databases

Introducing basics of data, databases, tables, attributes, and relationships

Data is defined as raw facts and figures, such as numbers, text, and images, lacking any context and serving as the foundational building block for information. It gains value only when organized and analyzed to produce meaningful insights.

What is Data?

• Data is raw facts and figures without context.
• Examples include numbers, text, and images.
• It serves as the building block of information.
• Raw data becomes valuable when organized and analyzed.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck. Explain data as raw facts/info. Examples: numbers, text. Visual: Icons of files and info symbols. Keep text short: 'Data is the building block of information.'

Databases organize data in a structured manner, similar to folders, transforming scattered files into efficient storage systems for quick access and management. They utilize tables to securely store related information, as highlighted in the slide's key points.

Understanding Databases

!Image

• Databases organize data like folders but structured
• Transform scattered files into efficient storage systems
• Enable quick access and management of information
• Use tables to store related data securely

Source: Database management system

Tables organize data into rows and columns, where rows store individual records and columns, defined as attributes, hold specific data types like Name or Age. This structure allows for clear and structured data management.

Tables and Attributes

• Tables organize data into rows and columns.
• Attributes define columns, like Name or Age.
• Rows store individual records or entries.
• Columns (attributes) hold specific data types.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

A Primary Key serves as a unique identifier for each row in a table, preventing duplicates and facilitating easy data organization, such as a unique ID for every person. A Foreign Key connects tables by referencing a Primary Key from another table, enabling relationships between datasets, like linking orders to customers.

Relationships: Primary and Foreign Keys

The slide recaps Section 1 by outlining the key progression from data to databases, tables, and their relationships, symbolized as the building blocks of data. It affirms that the viewer now grasps the basics, with foundations established, and previews the next topic: OLTP versus OLAP.

Section 1 Recap

Key Takeaways:

• Data → Databases → Tables → Relationships
• Icon Summary: Building Blocks of Data

Now you understand the basics!

Closing: Foundations laid. Next: OLTP vs OLAP.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This section header slide introduces Section 2, titled "OLTP vs OLAP," which focuses on comparing transactional processing and analytical systems. It highlights the key differences between OLTP, designed for handling real-time transactions, and OLAP, optimized for complex data analysis and querying.

Section 2: OLTP vs OLAP

02

OLTP vs OLAP

Comparing Transactional Processing and Analytical Systems

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

OLTP, or Online Transaction Processing, manages daily operations such as banking transactions using normalized databases to enable fast inserts, updates, and deletes while ensuring data integrity and quick response times for numerous small, real-time transactions. The slide includes a flowchart on the right visualizing speedy transaction flow, from user initiation (e.g., ATM withdrawal) through validation, database update, and confirmation, with icons emphasizing speed and efficiency.

OLTP: Online Transaction Processing

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

OLAP, or Online Analytical Processing, is designed for reporting and analysis tasks like tracking sales trends over time, leveraging denormalized data structures to enable fast, complex queries on large datasets. It is powered by dimensional modeling, which organizes data into facts and dimensions for efficient, intuitive analysis, as illustrated by a query dashboard icon featuring charts and graphs.

OLAP: Online Analytical Processing

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

Dimensional modeling bridges OLTP and OLAP systems to enable seamless analysis and transform complex queries into straightforward reports. It also facilitates intuitive insights from transactional data while supporting fast, user-friendly business intelligence.

Why Dimensional Modeling?

• Bridges OLTP and OLAP for seamless analysis
• Simplifies complex queries into easy reports
• Enables intuitive insights from transactional data
• Supports fast, user-friendly business intelligence

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This slide introduces Section 03 of the Dimensional Modeling 101 training deck, titled "Core Dimensional Modeling Concepts." It explores essential elements like fact tables, dimensions, and schema structures to enable intuitive data analysis.

Dimensional Modeling 101 – Beginner-Friendly Training Deck

03

Core Dimensional Modeling Concepts

Exploring fact tables, dimensions, and schema structures for intuitive data analysis

Source: Dimensional Modeling 101

Fact tables are central database structures that store measurable facts, such as sales amounts, revenue, quantities, or transaction counts, primarily in numeric form for analysis and reporting. They include foreign keys that link to dimension tables to enable comprehensive data relationships.

Fact Tables

• Central tables storing measurable facts, like sales amounts.
• Contain foreign keys linking to dimension tables.
• Hold numeric measures for analysis and reporting.
• Examples include revenue, quantities, or transaction counts.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

Dimension tables are descriptive tables, such as those for customers and products, that provide essential context and details to factual data in a database. They include attributes like Customer ID, Name, Address, and City to enable analysis and filtering.

Dimension Tables

!Image

• Descriptive tables like Customer and Product
• Provide context and details to facts
• Include attributes for analysis and filtering
• Example: Customer ID, Name, Address, City

Source: star schema diagram

The Star Schema features a central fact table that connects to multiple surrounding dimension tables, forming a radiating structure resembling a star. This design simplifies database queries and analysis while being easy for beginners to understand.

Star Schema

!Image

• Central fact table connects to dimensions
• Radiating structure like a star
• Simplifies queries and analysis
• Easy for beginners to understand

Source: Dimensional Modeling 101

The Snowflake Schema is an advanced database design that builds on the star schema by normalizing its dimensions into additional sub-tables, creating a branched structure that extends outward like a snowflake. This approach increases complexity but optimizes storage space by reducing data redundancy.

Snowflake Schema

!Image

• Star schema with normalized dimensions
• Branched structure extends arms outward
• More complex but saves storage space

Source: Snowflake schema

Fact tables in data warehousing come in three main types: transaction fact tables, which capture detailed individual business events; snapshot fact tables, which offer periodic summaries at fixed intervals; and accumulating fact tables, which track progress through multiple process stages. This slide outlines these types to help understand how to model different granularities of data.

Types of Fact Tables

• Transaction Fact Tables: Capture detailed, individual business events.
• Snapshot Fact Tables: Provide periodic summaries at fixed intervals.
• Accumulating Fact Tables: Track progress through multiple process stages.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

Factless fact tables are used in data modeling to track events or occurrences without any numeric measures, containing only foreign keys to dimension tables. They capture scenarios like attendance or enrollments—such as student enrollment records—and help answer questions about whether something happened or how many times it occurred.

Factless Fact Tables

• Factless fact tables track events without numeric measures.
• Capture occurrences like attendance or enrollments.
• Example: Student enrollment records dimension keys only.
• Contain foreign keys to dimensions, no values.
• Answer 'did it happen' or 'how many' questions.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This slide recaps Section 3 by explaining that combining facts and dimensions forms star or snowflake schemas, while highlighting how various fact types address different business needs and demystifying core concepts. It ends with a subtitle encouraging viewers to design their first star schema.

Section 3 Recap

Facts + Dimensions = Star/Snowflake Schemas

Various fact types meet different business needs.

Core concepts demystified!

Ready to design your first star schema?

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

Section 4 introduces the process of building a star schema, a key data modeling technique used in data warehousing. It provides a step-by-step guide to intuitively design your first star schema.

Section 4: How to Build a Star Schema

4

How to Build a Star Schema

Step-by-step guide to designing your first star schema intuitively

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The timeline slide outlines Step 1 in understanding business processes through four phases: identifying key processes like sales and inventory, mapping activities via simple flowcharts, engaging stakeholders through interviews to reveal requirements and challenges, and documenting insights in notes or diagrams. This structured approach focuses efforts to inform subsequent schema design.

Step 1: Understand Business Process

Phase 1: Identify Key Processes List main business activities, such as sales, inventory, and customer service, to focus the modeling effort. Phase 2: Map Out Activities Create a simple flowchart illustrating how business tasks connect and flow from start to end. Phase 3: Engage Stakeholders Interview team members to uncover requirements, challenges, and key metrics for the processes. Phase 4: Document Insights Summarize findings in notes or diagrams to guide the next steps in schema design.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The slide defines "grain" as the finest level of detail in a fact table, such as per order line, to avoid aggregation issues and specify exact measurement units. It explains how this guides the identification of facts and dimensions while establishing a foundation for accurate and consistent analysis.

Step 2: Define the Grain

• Grain: Finest level of detail in fact table (e.g., per order line).
• Avoids aggregation issues by specifying exact measurement unit.
• Guides fact and dimension identification.
• Sets foundation for accurate, consistent analysis.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

In Step 3, dimensions provide the contextual details of data analysis, such as Who, What, Where, and When (e.g., Customer, Product, Date), while facts represent the numerical measures to be analyzed (e.g., Sales Amount, Quantity). The process begins by identifying descriptive dimensions from the business process, followed by selecting key metrics as facts, as illustrated in sales examples where Date and Product serve as dimensions and Revenue as a fact.

Step 3: Identify Dimensions and Facts

• Dimensions describe context: Who, What, Where, When (e.g., Customer, Product, Date).
• Facts are measures: Numerical values to analyze (e.g., Sales Amount, Quantity).
• Start with business process to list descriptive dimensions.
• Follow with key metrics as facts for analysis.
• Examples: In sales, Date and Product are dimensions; Revenue is a fact.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

In Step 4, the slide outlines designing a schema by creating fact and dimension tables and defining relationships using foreign keys. This process connects facts to dimensions to form a star schema structure.

Step 4: Design the Schema

!Image

• Create fact and dimension tables
• Define relationships with foreign keys
• Connect facts to dimensions forming a star

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

Step 5 focuses on validating the design by verifying the completeness of facts and dimensions, checking for appropriate denormalization, and ensuring alignment with business requirements. It also involves validating grain and key relationships while testing for query efficiency and accuracy.

Step 5: Validate the Design

• Verify completeness of facts and dimensions
• Check appropriate denormalization in dimensions
• Ensure alignment with business requirements
• Validate grain and key relationships
• Test for query efficiency and accuracy

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

In Step 6 of the timeline, the process begins in January 2023 by initiating the ETL pipeline to extract and transform raw data for schema compatibility, followed in February by loading the transformed facts and dimensions into the star schema database. This continues in March with executing validation queries to confirm data accuracy and completeness, and concludes in April by verifying system performance through monitoring query times and optimizing for efficient OLAP reporting.

Step 6: Load and Test

2023-01: Initiate ETL Pipeline Extract raw data from source systems and apply transformations for schema compatibility. 2023-02: Load Data into Tables Insert transformed facts and dimensions into the star schema database. 2023-03: Execute Query Tests Run validation queries to ensure data accuracy and completeness. 2023-04: Verify System Performance Monitor query execution times and optimize for efficient OLAP reporting.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This conclusion slide recaps Section 4 by outlining six key steps to building a robust star schema. It features a "Completed Puzzle Icon" subtitle and emphasizes that practice makes perfect.

Section 4 Recap

6 steps to a robust star schema. Practice makes perfect!

Completed Puzzle Icon

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This slide introduces Section 5 of the presentation, titled "Real Finance Examples." It features a subtitle that explores dimensional models applied to General Ledger (GL), Accounts Payable (AP), and Accounts Receivable (AR) in finance.

Section 5: Real Finance Examples

5

Real Finance Examples

Exploring Dimensional Models for GL, AP, and AR in Finance

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The slide presents the General Ledger (GL) Model as a simple star schema diagram. It features a central Fact Table for Transactions, connected to key Dimensions including Account, Date, and Journal.

General Ledger (GL) Model

!Image

• Fact Table: Transactions
• Dimensions: Account, Date, Journal
• Simple GL star schema diagram

Source: Image from Wikipedia article "Star schema"

The Accounts Payable (AP) example slide outlines a fact table that captures key invoice amounts as measures, supported by dimensions such as vendor, date, and purchase order details. It contrasts AP's focus on operational processes with general ledger (GL) accounting and includes a visual aid in the form of an AP process flow chart for clarity.

Accounts Payable (AP) Example

• Fact Table: Captures invoice amounts as key measures
• Dimensions: Vendor, Date, and Purchase Order details
• Comparison to GL: Focuses on operational processes
• Visual Aid: AP process flow chart for clarity

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The Accounts Receivable (AR) Model slide outlines a system that tracks invoice payments and amounts as a key fact. It incorporates dimensions like customer, date, and product details to effectively manage customer billing processes.

Accounts Receivable (AR) Model

!Image

• Fact: Tracks invoice payments and amounts.
• Dimensions: Customer, Date, Product details.
• Manages customer billing processes effectively.

Source: Star schema

The slide compares General Ledger (GL), Accounts Payable (AP), and Accounts Receivable (AR) modules, highlighting their similarities in using date dimensions for time-based analysis, account hierarchies for categorization, core transaction facts, and foreign keys for data linking. It contrasts these by noting that GL emphasizes high-level account summaries, while AP and AR provide granular details on individual invoices, vendors or customers, payment terms, and due dates for precise tracking.

Comparing GL, AP, AR

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The "Benefits in Finance" stats slide highlights key efficiencies from optimized data models and workflows, including 50% faster queries achieved with dimensional models and 30% reduced storage for finance data. It also showcases 75% improved reporting speed in GL and AR processes, alongside 40% cost savings from streamlined AP workflows.

Benefits in Finance

• 50%: Faster Queries

Achieved with dimensional models

• 30%: Reduced Storage

Optimized for finance data efficiency

• 75%: Improved Reporting Speed

In GL and AR processes

• 40%: Cost Savings

From streamlined AP workflows Source: Dimensional Modeling Studies

This conclusion slide recaps Section 5 by highlighting how finance models simplify reporting, with the General Ledger offering overview insights and Accounts Payable/Receivable providing detailed transaction data, emphasizing real-world applications. It subtitles the key takeaway as mastering dimensional modeling for intuitive finance analysis.

Section 5 Recap

Finance models simplify reporting. GL provides overview insights, AP/AR delivers detailed transactions. Real-world application!

Master dimensional modeling for intuitive finance analysis.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

This section header slide, titled "Visualize the Models," introduces Section 6 on Star Schema Diagrams. It features visual representations of the dimensional models for General Ledger (GL), Accounts Payable (AP), and Accounts Receivable (AR).

Visualize the Models

6

Star Schema Diagrams

Visual representations of GL, AP, and AR dimensional models

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The slide presents a GL Star Schema Diagram, featuring a central Fact Table for GL Transactions that includes key measures such as Amount and Quantity. Surrounding it are Dimension Tables for Account, Date, Product, and Location, connected via foreign keys to the fact table's primary keys for relational linking.

GL Star Schema Diagram

!Image

• Fact Table: Central GL Transactions with measures like Amount and Quantity.
• Dimension Tables: Account, Date, Product, Location surrounding the fact table.
• Foreign Keys: Link dimensions to fact table via arrows.
• Primary Keys: Unique IDs in each dimension for relationships.

Source: Wikipedia: Star schema

The AP Star Schema Diagram illustrates a central AP Fact Table that serves as the hub for invoice amounts and transaction details, connected to key dimensions like Vendor for supplier information and Invoice Date for time-based analysis. It also highlights the Amount Measure as a core quantitative element for aggregating payables data.

AP Star Schema Diagram

!Image

• AP Fact Table: Central hub for invoice amounts and transaction details.
• Vendor Dimension: Stores supplier information like name and location.
• Invoice Date Dimension: Enables time-based reporting and trends.
• Amount Measure: Quantitative value for aggregating payables data.

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck

The AR Star Schema Diagram illustrates a central fact table containing invoice amounts and transaction IDs, surrounded by key dimension tables for analytical processing. These include the Customer Dimension with client details like locations and contacts, the Date Dimension for payment due dates and time-based analysis, and the Item Dimension covering products, prices, and categories.

AR Star Schema Diagram

!Image

• AR Fact Table: Central hub with invoice amounts and transaction IDs.
• Customer Dimension: Details on clients, locations, and contact info.
• Date Dimension: Payment due dates for time-based analysis.
• Item Dimension: Products sold, prices, and categories.

Source: Star schema

The slide simplifies dimensional modeling, highlighting key visuals of star schemas for General Ledger (GL), Accounts Payable (AP), and Accounts Receivable (AR). It concludes with a thank you, an invitation to discuss, and an open call for questions.

Final Thoughts & Q&A

Dimensional modeling made simple!

Key visuals: Star schemas for GL, AP, AR.

Thank you! 😊

Questions?

Let's discuss!

Source: Dimensional Modeling 101 – Beginner-Friendly Training Deck