📊 Data Platform Architectures & Dimensional Modeling
Data platform architectures are fundamental for managing and analyzing enterprise data. A data warehouse (DW) serves as a central repository, optimized for analytical queries, contrasting sharply with operational databases designed for transactional processing. Understanding the interplay between various data architectures, processing patterns like OLTP and OLAP, and modern concepts such as data lakes and lakehouses is crucial for effective data engineering.
🏛️ Data Warehouse Fundamentals
A data warehouse is a core component of business intelligence, providing a structured, queryable repository for historical data. It addresses the limitations of directly querying operational databases, which are typically normalized for transactional efficiency and thus perform poorly on complex analytical queries.
🚨 The Challenge Without a Data Warehouse
Directly querying operational databases (OLTP systems) for analytical purposes presents several issues:
Slow Query Performance: Normalized data structures (3NF) are not optimized for large, complex analytical queries.
System Strain: Analytical queries can overtax operational systems, impacting real-time transactions.
Complex Joins: Extensive joins are often required to retrieve meaningful analytical insights from normalized data.
A data warehouse overcomes these challenges by storing data in formats optimized for reading, aggregating, and querying large volumes of historical data.
🔄 OLTP vs OLAP Access Patterns
Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) represent fundamentally different data access patterns, necessitating distinct architectural approaches.
OLTP Systems:
Handle frequent, small transactions.
Optimized for individual transactions and row-level data operations.
Focus on real-time data modifications.
Examples: order entry, banking transactions.
OLAP Systems:
Process complex queries across vast datasets.
Organize data into multidimensional cubes (time, geography, product categories).
Enable interactive operations like slice, dice, drill down, and pivot.
Provide rapid aggregation and pre-calculated summaries for fast responses.
Support complex analytical functions.
Data warehouses often use dimensional modeling, organizing data into fact tables (measurements) and dimension tables (context) for intuitive analysis.
📊 When OLTP Systems Fall Short: The Need for OLAP
Operational databases, while excellent for transactions, struggle with deep analytical questions that require aggregating vast historical datasets across multiple dimensions. This is where data warehouses and OLAP systems excel.
Examples of complex analytical questions that OLTP systems struggle with:
Complex Sales Trends: Analyzing year-over-year sales growth by product line, region, and customer segment to identify seasonal patterns.
Predictive Inventory: Forecasting optimal inventory levels by combining historical sales, supplier lead times, and promotional impacts.
Customer Value Metrics: Calculating the total revenue a customer is expected to generate over their relationship with the business.
Financial Performance: Comparing budgeted expenditures and revenues against actual figures across departments for variance analysis.
📝 Defining the Data Warehouse
Prominent figures in data management have provided foundational definitions:
These definitions emphasize that a data warehouse is structured data designed to support decision-making and business intelligence.
🏷️ Key Traditional Data Warehousing Terminology
Data Warehouse: A centralized repository for integrated, historical data, designed for analytical reporting and business intelligence.
Normalized Model: A database design approach reducing data redundancy and improving integrity, primarily for transactional systems.
BI (Business Intelligence): Tools and processes that transform raw data into meaningful insights for informed business decision-making.
Data Mart: A focused subset of a data warehouse, serving the analytical needs of a specific department or business function.
OLAP (Online Analytical Processing): Technology enabling interactive, multidimensional analysis of large volumes of data for strategic insights.
Data Mining Model: Algorithms and techniques used to discover patterns, trends, and anomalies from large datasets for predictive analysis.
Dimensional Model, Star Schema: A logical data model (facts + dimensions) optimizing data warehouses for fast, intuitive querying.
ETL (Extraction, Transformation, Load): The core process of moving data from source systems, cleaning/restructuring it, and loading into a data warehouse.
Ad-hoc Query: A query formulated on-the-fly to answer a specific, spontaneous business question.
🚀 Evolving Data Landscape: Key Concepts
As data volumes and varieties grow, new architectural paradigms have emerged to meet modern analytical demands.
Lakehouse Architecture: A hybrid data architecture combining the flexibility and cost-efficiency of data lakes with the robust data management and ACID transaction capabilities of data warehouses. It supports both structured and unstructured data for diverse analytics and machine learning.
Data Mesh: A decentralized data architecture that treats data as a product, organized by business domains. Each domain manages its own data infrastructure, products, and standards, fostering agility, scalability, and data ownership.
Data Lake: A centralized repository for massive volumes of raw, unprocessed data in its native format. It is schema-agnostic, ideal for big data analytics and machine learning.
Data Fabric: An architecture providing a unified, intelligent view of an organization's data across disparate environments (on-premise, cloud, hybrid). It integrates data management tools and leverages AI/ML for automated data discovery, governance, and consumption.
Streaming Data: Data generated continuously from numerous sources (IoT, web clicks, financial transactions), requiring immediate processing for instant insights.
⚙️ The Data Engineering Process
The data engineering process involves a series of steps to move, transform, and prepare data for analysis.
Data Ingestion: Acquiring raw data from various sources.
Data Transformation: Cleaning, restructuring, and enriching data.
Data Serving: Making processed data available for analysis or applications.
Data Flow Orchestration: Managing the automated workflows.
Additional key concepts in data engineering:
Data Migration: Moving data between systems or environments, e.g., on-premises to cloud.
Data Integration: Consolidating information from diverse systems and IoT devices into unified data streams.
Data Wrangling: Converting raw, unstructured data into clean, usable formats for analytics and machine learning.
Database Operations: Copying tables, maintaining synchronization, and supporting backup/disaster recovery.
🏗️ Data Architecture - Layers
Modern data architectures, like the Lakehouse, are often layered to manage data maturity and processing.
Common layers include:
Data Sources Layer: Multiple data sources (OLTP, ERP, CRM, IoT, web, social media) feeding into the architecture.
Ingestion Layer: Real-time streaming (Kinesis, Event Hubs) and batch ingestion processes.
Storage Layer: Raw data storage (Data Lake - S3, ADLS) and structured storage (Data Warehouse - Redshift, Snowflake). Different zones (Landing, Raw, Trusted, Curated) for data maturity.
Processing Layer: Data transformation, cleansing, ETL/ELT pipelines (Spark, Databricks), quality checks, aggregation, and enrichment.
Consumption Layer: BI tools, APIs, Machine Learning, reports, and data science.
🏛️ Two-tier Data Warehouse Architecture
A common architectural pattern for data warehouses involves multiple tiers to separate concerns and optimize performance.
Source Systems Layer: Operational and external data sources.
Data Staging/ETL Layer:
Extract, Transform, Load processes.
Data cleansing and validation.
Application of business rules.
Enterprise Data Warehouse Layer (EDW):
Centralized repository for integrated, historical data.
Single source of truth for the organization.
Includes metadata about ETL processes and data catalog.
Data Marts Layer:
Department-specific or subject-oriented subsets.
Optimized for specific business functions (Sales, Finance).
Denormalized for query performance.
Derived from the central EDW.
🌐 A Complete Data Engineering Ecosystem: Technology Perspective
A modern data engineering ecosystem integrates various components to create a unified, scalable architecture.
This ecosystem includes:
Sources: OLTP databases, ERP, operational apps, event collectors, logs, APIs, file/object storage.
Ingestion and Transport: Data replication (Fivetran, Stitch), workflow managers (Airflow), event streaming (Kafka, Kinesis), Reverse ETL.
Storage: Lakehouse, Data Lake (Delta, Iceberg, Hudi, S3), Data Warehouse (Snowflake, BigQuery, Redshift).
Query and Processing: Spark platforms (Databricks, EMR), SQL query engines (Presto, Trino), DS/ML platforms, real-time analytics databases (Druid, ClickHouse), stream processing.
Transformation: Metrics layer (LookML, dbt), data modeling, workflow managers.
Analysis and Output: Dashboards (Looker, Tableau), embedded analytics, augmented analytics, data workspaces, DS/ML tooling, custom applications.
Cross-cutting Concerns: Data Discovery (Amundsen, DataHub), Data Governance (Collibra), Data Observability (Monte Carlo), Entitlements & Security (Privacera, Immuta).
ETL vs. ELT Pipelines 🔄
Understanding the difference between ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) is crucial for designing modern data pipelines.
📦 ETL Pipeline Architecture (Inmon & Kimball)
ETL is the traditional approach where data transformation occurs before loading into the target system. This ensures data quality and consistency from the outset.
Extract: Retrieving raw data from multiple sources (databases, APIs, flat files, streaming platforms) in its original format.
Transform: Standardizing and cleansing data to meet specific format requirements. This involves applying business rules, data quality checks, and formatting, significantly improving data discoverability and usability.
Load: Saving transformed data to its final destination, typically a data warehouse, ready for immediate analysis and reporting.
☁️ ELT Pipeline Architecture (Lakehouse)
ELT reverses the traditional ETL sequence by loading raw data first and transforming it later. This modern approach leverages the processing power of cloud data warehouses and provides greater flexibility.
Different Processing Order: Extract, Load, Transform. Data is ingested in its raw form and transformed only when needed for specific use cases.
Maximum Data Ingestion: ELT pipelines excel at capturing as much data as possible upfront, allowing transformation decisions to be made later based on emerging business requirements and analytical needs.
Schema Flexibility: The ELT architecture doesn't require upfront decisions about data types, structures, and formats. This schema-on-read approach provides maximum agility for data exploration and analysis.
🎯 Data Marts: Focused Analytics
Data marts are smaller, focused data warehouses designed to serve the specific analytical needs of a particular business unit, department, or subject area.
Departmental Data Repositories: They provide faster query performance and simplified access for end-users by containing only the relevant subset of enterprise data.
⬆️ Bottom-Up Approach (Kimball)
Multiple specialized data marts can be combined to create a comprehensive enterprise data warehouse. This approach emphasizes building for specific business processes first.
⬇️ Top-Down Approach (Inmon)
A central data warehouse can be decomposed into focused data marts serving specific departments or functions. This approach prioritizes a single, integrated source of truth.
⚖️ Inmon vs. Kimball Approaches
Two prominent methodologies guide data warehouse design: Bill Inmon's top-down approach and Ralph Kimball's bottom-up approach.
⬆️ Inmon - Top-down approach
Inmon's approach emphasizes building a normalized Enterprise Data Warehouse (EDW) first, which then feeds specialized data marts.
Key Principles:
Builds a normalized EDW first.
Data flows from source systems into the EDW, then to specialized data marts.
Focuses on comprehensive data integration to create a single source of truth.
Utilizes Third Normal Form (3NF) for the core warehouse structure, ensuring robust data consistency and reduced redundancy.
Limitations:
Time-consuming and expensive to implement.
Requires significant upfront planning and design.
Complex ETL processes due to normalization.
Slower query performance compared to dimensional models.
Less agile and difficult to adapt to changing business requirements.
Requires a deep understanding of the entire enterprise data landscape.
⬇️ Kimball - Bottom-up approach
Kimball's approach emphasizes building dimensional data marts first, typically using star schemas, and then integrating them through conformed dimensions.
Key Principles:
Emphasizes dimensional modeling, primarily using star schemas for data marts.
Data marts are built first, focusing on specific business processes (bottom-up).
Focuses on ease of use and rapid query performance for business users.
Uses denormalized tables (facts and dimensions) to reduce joins and improve speed.
Promotes a "bus architecture" for integrating data marts with conformed dimensions.
Designed for agile development and quick delivery of analytical solutions.
Limitations:
Potential for data redundancy across different data marts.
Can lead to inconsistencies if conformed dimensions are not strictly managed.
Less suitable for complex, enterprise-wide data integration needs without careful planning.
May not handle all analytical use cases as effectively as a highly normalized EDW.
Challenges in maintaining and evolving a large number of disparate data marts.
Initial setup might require significant effort in defining dimensions and facts.
🌊 Big Data Engineering & Data Lakes
Data lakes represent a paradigm shift in how organizations store and manage enterprise data, embracing the full diversity of modern data sources at massive scale.
Unlike traditional warehouses with rigid schemas, data lakes embrace the full diversity of modern data sources, storing everything in its native format at massive scale.
On top of the data lake, multiple layers can be created during various transformation steps.
🆚 Data Warehouse vs Data Lake
Data Warehouse: Data is processed and organized into a single schema before being put into the warehouse. Analysis is done on cleansed data.
Data Lake: Raw and unstructured data goes into a data lake. Data is selected and organized as and when needed (schema-on-read).
🏗️ Data Lake Architecture
Data flows from diverse sources through ingestion layers, into storage zones with varying levels of refinement, and ultimately serves multiple consumption patterns.
What Defines a Data Lake?
A data lake is a centralized repository that stores all enterprise data in its natural, raw format. This includes structured, semi-structured, unstructured, and binary data.
It's like a natural lake with multiple tributaries, receiving continuous streams of various data types in real-time.
Breaking Down Data Silos
Data lakes emerged to solve enterprise data fragmentation. Organizations consolidate all data into a single, scalable repository, typically built on distributed systems like Hadoop or cloud object storage.
Raw Source Data: Unmodified copies of data from source systems, preserving original context.
Transformed Data: Processed data optimized for reporting, visualization, advanced analytics, and machine learning.
Analytical Assets: Curated datasets, models, and features ready for immediate business insights.
Data Types in a Data Lake
Data lakes embrace all data formats without discrimination, allowing organizations to capture value from all information assets.
Structured Data: Relational database tables with defined schemas.
Unstructured Data: Emails, documents, PDFs, text files lacking predefined models.
Semi-Structured Data: CSV, logs, XML, JSON with flexible schemas.
Binary Data: Images, audio, video content.
Unlimited Scale: Data lakes can store every type of data in its native format with no fixed limits, increasing analytic performance and enabling native integration.
🌟 Benefits and Principles of Data Lakes
Strategic Advantages
The primary objective of data lakes is to offer an unrefined, comprehensive view of data to data scientists and analysts, leading to a competitive edge.
New Analytics Capabilities: Enable novel analytical approaches using previously unavailable data sources.
Schema Flexibility: No need for upfront data modeling into rigid enterprise-wide schemas.
Enhanced Quality: Access to raw data improves the depth and accuracy of analyses.
Business Agility: Rapid adaptation to changing business questions and analytical needs.
AI and ML Ready: Native support for machine learning and artificial intelligence workloads.
Competitive Edge: Data-driven insights create sustainable competitive advantages.
Unified Architecture: Eliminates data silos and fragmented storage systems.
Core Principles
Accept All Data: No data is refused entry; all data is loaded from various source systems and retained indefinitely.
Preserve Raw State: Data is stored in an untransformed or minimally transformed state, exactly as received.
Transform on Demand: Data is transformed and structured based on specific analysis requirements, not predetermined schemas.
🔑 Essential Concepts in Data Lake Management
Effective data lake management requires robust capabilities across several areas.
Data Ingestion: Connectors retrieve data from diverse sources and load it into the data lake.
Data Storage: Scalable, cost-effective storage with fast access.
Data Governance: Managing availability, usability, security, and integrity across all data assets.
Security: Multi-layered protection implemented at every layer.
Data Quality: Ensuring high-quality data to generate accurate business insights; poor quality leads to poor decisions.
Data Discovery: Tagging and cataloging techniques to express and share data understanding.
Data Auditing: Evaluating risk, compliance, and adherence to regulatory requirements.
Data Lineage: Tracking data origins, transformations, and movement throughout the ecosystem.
Data Exploration: Initial stage of data analysis enabling hypothesis formation.
📊 Data Warehouse vs Data Lake Comparison
Organizations typically require both a data warehouse and a data lake, as they serve fundamentally different needs and use cases.
Data Warehouse Characteristics
Predefined Structure: Data structure and schema are defined in advance (schema-on-write).
Clean and Enriched: Data is cleaned, enriched, and transformed before storage.
Single Source of Truth: Serves as the authoritative, consistent view of enterprise data.
Structured Focus: Primarily handles relational data with well-defined schemas.
Business Users: Optimized for SQL-based reporting and business intelligence tools.
Data Lake Characteristics
Flexible Structure: Data structure or schema is not defined at ingestion time (schema-on-read).
Raw Data Storage: Data is stored in its native format without upfront transformation.
Multiple Possibilities: Supports many possible analytics approaches on the same data.
Diverse Data Types: Accommodates relational and non-relational data (mobile apps, IoT, social media).
Data Scientists: Enables advanced analytics, machine learning, and exploratory data science.
Detailed Comparison Matrix
Characteristics | Data Warehouse | Data Lake |
|---|---|---|
Data | Relational from transactional systems, operational databases, and line of business applications | Non-relational and relational from IoT devices, web sites, mobile apps, social media and corporate applications |
Schema | Designed prior to the DW implementation (schema-on-write) | Written at the time of analysis (schema-on-read) |
Price/Performance | Fastest query results using higher cost storage | Query results getting faster using low-cost storage |
Data Quality | Highly curated data that serves as the central version of the truth | Any data that may or may not be curated (ie. raw data) |
Users | Business analysts | Data scientists, Data developers, and Business analysts (using curated data) |
Analytics | Batch reporting; BI and visualizations | Machine Learning, Predictive analytics, data discovery profiling and |
Parameters | Data Lakes | Data Warehouse |
|---|---|---|
Data | Data lakes store everything: | Data Warehouse focuses only on Business Processes. |
Processing | Data are mainly unprocessed | Highly processed data. |
Type of Data | It can be Unstructured, semi-structured and structured. | It is mostly in tabular form & structure. |
Task | Share data stewardship | Optimized for data retrieval |
Agility | Highly agile; configure and reconfigure as needed. | Compare to Data lake it is less agile and has fixed configuration. |
Users | Data Lake is mostly used by Data Scientist | Business professionals use data Warehouse widely |
Storage | Data lakes design for low-cost storage. | Expensive storage that give fast response times are used |
Security | Offers lesser control. | Allows better control of the data. |
Replacement of EDW | Data lake can be source for EDW | Complementary to EDW (not replacement) |
Schema | Schema on reading (no predefined schemas) | Schema on write (predefined schemas) |
Data Processing | Helps for fast ingestion of new data. | Time-consuming to introduce new content. |
Data Granularity | Data at a low level of detail or granularity. | Data at the summary or aggregated level of detail. |
Tools | Can use open source/tools like Hadoop/ Map Reduce | Mostly commercial tools. |
Architecture Decision: Modern data architectures often implement both systems in a complementary fashion, using data lakes for raw data storage and exploration, while maintaining data warehouses for curated, high-performance analytical workloads.
🚫 Data Lake vs Data Swamp: Avoiding Pitfalls
A data swamp is a data lake that has deteriorated into an unmanaged repository, where data becomes inaccessible or provides minimal value due to inadequate data governance.
The Risk of Poor Governance: Without proper cataloging, quality controls, and metadata management, a data lake can become a dumping ground rather than a valuable asset.
Practical Accessibility Challenges: While data lakes theoretically offer access to all data, difficulty in parsing unstructured data creates barriers to insight generation.
Organizations must invest in data cataloging, governance frameworks, and quality management to prevent data lakes from becoming swamps.
Metadata Management: Comprehensive cataloging and tagging of all data assets for discoverability.
Access Policies: Clear governance policies defining who can access what data.
Data Quality Controls: Automated validation and quality checks to ensure data reliability.
Lifecycle Management: Defined retention policies and archival strategies to prevent unlimited data accumulation.
💎 Lakehouse (Medallion) Architecture
The Lakehouse (Medallion) Architecture is a hybrid data architecture that combines the flexibility and cost-efficiency of data lakes with the robust data management and ACID transaction capabilities of data warehouses. It organizes data into distinct layers, progressively improving data quality.
Key Principles
Layered Approach: Organizes data into three distinct layers: Bronze (raw), Silver (refined), and Gold (curated), improving data quality at each stage.
Delta Lake Foundation: Often built on Delta Lake, providing ACID transactions, schema enforcement, data versioning, and time travel.
Bronze Layer (Raw): Ingests source data as-is, ensuring historical immutability and full traceability.
Silver Layer (Refined): Applies cleansing, filtering, and basic transformations, often joining data from multiple Bronze tables for a consistent view.
Gold Layer (Curated): Delivers aggregated, denormalized, and highly optimized data for specific business intelligence, reporting, and machine learning applications.
Progressive Data Quality: Data quality and reliability increase as data moves through the layers.
Unified processing for streaming and batch data.
Limitations
Increased Complexity: Multiple layers can add complexity to data pipelines, especially for smaller projects.
Databricks/Delta Lake Specific: Full implementation often leverages specific features of the Databricks Lakehouse Platform, potentially leading to vendor lock-in.
Resource Intensive: Storing and processing data across three layers can consume more storage and compute resources if not optimized.
🗺️ Medallion Architecture: Zones Explained
The Medallion Architecture categorizes data into distinct zones, each with a specific purpose.
Bronze (Raw Data): The initial staging/landing zone where raw, immutable data is ingested as-is, serving as the historical record of source data. It allows different ways of ingesting data from various sources.
Silver (Cleaned & Refined): The trusted zone where data is cleaned, validated, and transformed. It serves as a reliable single source of truth for further processing. It supports parallel/independent ingestion.
Gold (Curated & Aggregated): This zone delivers highly curated, aggregated, and optimized data for specific business intelligence, reporting, and machine learning applications. It features consolidated data and conformed dimensions.
Additional Specialized Zones
Sandbox Zone: A flexible, isolated environment for data scientists and analysts to experiment without impacting production.
Backup Zone: Dedicated to storing copies of data for disaster recovery, ensuring business continuity.
Archive Zone: For long-term (cold) storage of inactive historical data, crucial for compliance and reducing costs.
🔒 Medallion: PII Protection Perspective
From a privacy and security perspective, the medallion architecture implements progressive data protection and personally identifiable information (PII) handling.
Bronze: PII Encrypted: Raw data contains encrypted PII fields, maintaining compliance while preserving data utility for authorized users.
Silver: PII Masked: Sensitive fields are masked or tokenized, allowing broader access while protecting individual privacy.
Gold: PII Aggregated: Aggregated metrics eliminate individual-level data, enabling wide distribution without privacy concerns.
This layered approach supports regulatory requirements like GDPR, CCPA, and HIPAA.
🏷️ Medallion Zones - Alternative Names
Landing | Raw | Harmonized | Distilled | Explorative | Delivery | |
|---|---|---|---|---|---|---|
Granularity (Raw Aggregated) | Raw | Raw | Raw | Aggregated | Any | Any |
Schema (Any Consolidated) | Any | Any | Consolidated | Consolidated, enriched | Any | Any |
Syntax (Unchanged Consolidated) | Basic transformations | Basic transformations | Consolidated | Consolidated | Any | Any |
Semantics (Unchanged Processed) | Mostly unchanged, unless needed for compliance | Mostly unchanged, unless needed for compliance | Mostly unchanged, unless needed for compliance | Complex processing | Any | Any |
Properties | Governed, non-historized, non-persistent; protected part; use case independent | Governed, historized, persistent, protected part; use case independent | Governed, historized, persistent, protected part; use case independent | Governed, historized, persistent; protected part; use case dependent | Not governed, non - persistent, protected part; use case dependent | Governed, persistent, protected part; use case dependent |
User Groups | Systems, processes | Data scientists, systems, processes | Data scientists; systems, processes | Data scientists; domain experts, systems, processes | Data scientists | human users, systems, processes Any |
Modeling Approach | Any | Any | Standardized | Standardized | Any | Any |
☁️ Multi-cloud Medallion Architecture
A Multi-cloud Medallion Architecture is designed to process and refine data across various cloud providers (AWS, Azure, GCP).
Integration & Synchronization: Uses cloud-agnostic tools or direct cloud-to-cloud connections to ensure seamless data flow and consistency.
Benefits:
Avoiding Vendor Lock-in: Flexibility to choose the best services from different providers.
Leveraging Best-of-Breed Services: Utilize specialized services unique to each cloud.
Geographic Distribution: Enhance data locality, disaster recovery, and compliance.
Minimizing data movement and complying with strict data residency regulations.
Maintaining business continuity.
🏠 Hybrid On-Prem Approach
This approach extends the medallion architecture to on-premises environments, allowing organizations to maintain control over their data.
Medallion zones (Bronze, Silver, Gold) can be implemented using a variety of on-premises storage and processing technologies.
Storage options: HDFS (Hadoop Distributed File System) and MinIO (S3-compatible object storage).
Table formats and processing: Hive, Kudu, and Apache Iceberg.
This flexibility allows full utilization of existing on-prem infrastructure without compromising the medallion pattern's integrity.
Crucial for addressing stringent data sovereignty and compliance requirements.
🧠 Medallion in RAG-based Systems
The Medallion Architecture can be adapted for Retrieval Augmented Generation (RAG) based systems, ensuring a structured approach to data processing for optimal Large Language Model (LLM) retrieval.
Bronze Layer: Ingests raw, multi-modal data (text, images, audio, video) directly from various sources, stored in its original, immutable format.
Silver Layer: Processes data from Bronze, involving cleaning, normalization, chunking of text, generation of embeddings, OCR for images, and transcription for audio. Goal is to refine data into a consistent and usable format.
Gold Layer: Houses an optimized vector database containing indexed embeddings. This refined data is ready for efficient retrieval by LLMs, forming a high-quality knowledge base for RAG workflows.
This systematic approach ensures superior data quality, full traceability of data lineage, and significantly optimized retrieval performance for RAG applications.
⏳ Batch vs. Streaming Data Processing
Understanding the fundamental differences between batch and streaming data processing is crucial for designing efficient and responsive data architectures.
Batch Processing
Processes large volumes of data at scheduled intervals.
Data is collected over time and processed in chunks.
Suitable for operations where latency is not critical (e.g., daily reports, monthly payroll).
Optimized for high throughput and complex transformations.
Example: End-of-day financial reconciliations.
Streaming Processing
Processes data continuously as it arrives.
Data is processed in small increments or events.
Essential for scenarios demanding real-time insights and immediate actions (e.g., fraud detection, live dashboards).
Optimized for low latency and rapid response.
Example: Real-time sensor data analysis.
Integrating Streaming and Batch Data: Challenges
Integrating both processing types introduces specific challenges:
Real-Time Ingestion: Facts and dimensions from customer-facing applications are consumed in near real-time, transformed, and stored in delta tables.
Batch Processing: Dimensions not yet streamed are ingested in batch ETLs from different parts of the system.
Join Challenge: Some streamed facts may reference dimension table keys that do not yet exist, requiring careful handling.
📐 Dimensional Modeling: Bus Matrix
The Bus Matrix is a foundational tool in enterprise data warehousing, guiding the development of analytical solutions by mapping business processes to shared dimensions. Ralph Kimball's approach emphasizes that dimensional modeling is built on business processes, with the Enterprise Data Warehouse being the sum of conformed and integrated models.
🚌 The Bus Matrix: Foundation of Enterprise Data Warehousing
The Bus Matrix is a high-level model of business processes executed daily, weekly, or monthly. It lists business processes in simple business language, explaining what each process is, its frequency, what can be measured, and who the key players are.
It lists the fact tables and dimension tables needed.
It can be created and understood by business stakeholders.
It stores all information the data team needs.
❓ Define a Business Problem
Dimensional modeling begins with understanding the business problem. The Bus Matrix maps shared dimensions (columns) against business processes (rows), enabling incremental data asset development.
Design determines fact data grain and required dimensions for accessible, faster queries.
🚶 The Four Steps of Dimensional Modeling
To model a business process dimensionally, follow these four steps:
Identify the Business Process: Define the specific business activity to be measured and analyzed.
Identify the Dimensions: Define the objects and context that describe the business process.
Identify the Grain: Determine the level of detail for each measurement in the process.
Identify the Facts: Specify the measurable metrics that quantify the business process.
This process typically takes 1-2 days per business process, requiring a subject matter expert and a data architect with dimensional modeling knowledge.
🍋 Example: Happy Lemonade Stand
Consider a lemonade stand business with several processes:
Selling Lemonade: Transactional process of selling glasses ($1 for 33cl) or bottles ($3 for 1 liter) to customers.
Process Characteristics: Transactional, grain at individual sale, not all customers have loyalty cards.
Loyalty Program Benefits: 5 cups for 4 (vs. 5 regular), 2 bottles for 5.50 (vs. 6 regular), requires social media follow.
Purchase Ingredients: Procurement process for lemons and other ingredients.
Making Lemonade: Production process (50-liter gallon each weekend, 10:00 to 14:00).
Building the Bus Matrix Model
For the "Selling Lemonade" process:
Step 3: Identify Dimensions:
Date & Time: When the sale occurred during weekend operations (10:00-14:00).
Product: Glass (33 cl) or bottle (1 liter) with volume and pricing attributes.
Loyalty Program: Optional dimension (marked with O) for discount tracking.
Step 4: Identify Measures:
Key Measures: Units sold, price, product volume, and discount given.
Dimensions Needed: Date, time, loyalty program, and product.
The result is a model of a single process: a transactional fact table with 4 dimension columns and 4 measures. This model can be tested by asking questions and evaluating reporting needs without writing any code.
📝 Bus Matrix Model - Example of Processes
Business Process | Grain | Measures | Loyalty Program | Product | Date | Time | Batch # | Ingredient | Waste Cause |
|---|---|---|---|---|---|---|---|---|---|
Selling Lemonade | One row for each product sold to a customer of a given datetime transaction | Sales Units, Sales Value, Sales Volume, Discount Value | O | O | O | O | X | X | X |
Making Lemonade | One row for each batch of lemonade made, per product | Volume, Units | X | O | O | O | O | X | X |
Purchase Ingredients | One row for each item purchased for making lemonade | Units, Cost | X | X | O | X | X | O | X |
Waste | One row for each ingredient or product that is thrown to the garbage as waste | Units | O | O | O | X | X | O | O |
📈 Complete Bus Matrix: Driving Value
By repeating the 4 steps for all processes, a comprehensive Bus Matrix is created, linking multiple fact tables through shared dimensions.
Complex Analysis: Perform analysis across all 4 processes as fact tables link via shared dimensions.
Process Optimization: Identify batches with most waste and manage purchase orders to meet expected demand.
Agile Development: Drive prioritization and apply agile methodologies by de-selecting parts without missing important elements.
The Bus Matrix allows for building an enterprise data warehouse incrementally, delivering business value process by process while maintaining integration and flexibility for future growth.
🌟 Data Modeling Approaches: Star Schema
Data modeling approaches form a hierarchical pyramid, reflecting how data is progressively refined and optimized for different purposes within a data platform.
This pyramid illustrates the flow from highly structured operational data, through integration layers, to simplified analytical models for business consumption.
🏛️ Transactional Database (3NF)
Purpose: Manages day-to-day operations of an organization (e.g., sales, inventory, orders).
Data Type: Stores current, real-time, and highly detailed data.
Structure: Optimized for CRUD (Create, Read, Update, Delete) operations.
Design: Highly normalized to minimize redundancy and ensure data integrity (typically Third Normal Form - 3NF).
Query Pattern: Primarily handles simple, quick queries for transaction processing.
Performance: Focused on fast data insertion and updates rather than complex analytical queries.
Examples: ERP systems, CRM systems, order processing systems.
🌠 Dimensional Modeling: Star Schema
The star schema is a foundational design for data warehouses, optimizing data for analytical processing.
Star Schema Characteristics
Purpose: Organizes data into facts and dimensions for analytical processing (OLAP).
Structure: A central fact table connected to multiple dimension tables, resembling a star shape.
Fact Table: Stores quantitative data (measures), such as sales, revenue, or quantities.
Dimension Tables: Contain descriptive attributes (context), e.g., customer, time, product.
Normalization: Typically denormalized for performance in data retrieval and reporting, reducing the need for joins.
Query Performance: Optimized for fast read-heavy operations and aggregations.
Simple Design: Easy to understand and query, suitable for BI tools.
❄️ Dimensional Modeling: Snowflake Schema
The snowflake schema is a more normalized version of the star schema.
Purpose: A more normalized version of the star schema, used for analytical processing.
Structure: Fact table at the center, surrounded by normalized dimension tables, which may have further sub-dimensions. This creates a branching structure.
Normalization: Higher normalization (typically 3NF) reduces redundancy but can complicate queries due to more joins.
Fact Table: Stores quantitative data (e.g., sales revenue, units sold).
Dimension Tables: More complex and divided into multiple related tables.
Query Performance: May be slower than star schema due to increased joins.
Storage Efficiency: More efficient for storage due to reduced redundancy.
Example Use Case: Complex analytics where data consistency and storage are critical.
📊 Fact Table
The fact table is the central component in a star or snowflake schema, storing measurable business metrics.
Purpose: Contains measurable business metrics related to events (sales, appointments, etc.).
Data Type: Stores numeric data, such as sales revenue, transaction count, or profit.
Granularity: Defined by the level of detail of the data (e.g., daily, hourly).
Key Columns: Contains foreign keys to dimension tables (e.g.,
product_id,time_id,customer_id).Measures: Contains numeric facts or aggregatable measures (e.g., total sales).
Performance: Optimized for fast aggregation and analysis.
The sales fact table contains foreign keys to dimensions plus numeric metrics (e.g., Revenue, UnitsSold) that measure business performance.
🌳 Dimension Table
Dimension tables provide the descriptive context for the facts in a data warehouse.
Purpose: Describes the context of the facts in a data warehouse or analytical model.
Data Type: Stores descriptive, categorical data (e.g., customer names, product categories).
Structure: Contains attributes that provide the "dimensions" for analysis (e.g., time, location).
Keys: Each dimensional table is usually linked to a fact table via a foreign key.
Denormalization: Often denormalized for faster querying and simplicity.
Example Columns: Customer name, product type, region, time period.
Use Case: Allows users to slice and dice facts by different attributes (e.g., sales by region, product).
Product, Store, Customer, and Date dimensions provide contextual information, joining with facts to answer specific business questions like monthly product popularity or quarterly store performance.
🛠️ Steps of Designing a Star Schema Model
Designing an effective star schema model involves a systematic approach:
Identify the Business Process: Define the key business activities to be analyzed, including relevant metrics, KPIs, and initial dimensions.
Select Granularity: Determine the lowest level of detail for your data. Start at the finest grain, considering trade-offs between storage costs and query performance.
Determine Dimensions: Design dimension tables with unique, not-null primary keys. These tables provide descriptive context for the facts.
Consolidate the Facts: Store quantitative metrics and measures in fact tables, linking them to dimension tables using foreign keys.
Build a Schema: Choose the appropriate schema type (star, snowflake, or fact constellation) and implement it, ideally via infrastructure as code with automated tests.
Optimize for Query Performance: Index the fact table on key columns (foreign keys) for efficient querying. Create derived fact tables for high-level aggregations.
✅ Benefits of Star Schema Models
Simplicity: Easy to understand and navigate due to its clear, intuitive structure. Queries are straightforward with minimal joins.
Query Performance: Optimized for read-heavy analytical queries, especially when aggregating data. Denormalized structure leads to faster query performance and reduced need for joins.
Separation of Facts and Dimensions: Easy to maintain and update facts and dimensions independently. Dimensions remain stable over time, while facts may change or grow.
Scalability: Easily extendable as new dimensions or facts can be added without significant redesign. Great for large datasets with many dimensions and measures.
Business User Accessibility: Simple, understandable structure is more accessible to business analysts and non-technical users. Direct support for Business Intelligence (BI) tools and dashboards.
❌ Drawbacks of Star Schema Models
Limited Agility for Changes:
Adding new sources or making structural changes can be challenging.
Changes in business requirements often require redesign or schema restructuring.
Changes of fact table granularity require reloading.
Historic refill (backfill) can be challenging.
Maintenance Complexity:
Managing large fact tables with a high number of records can become difficult.
Ensuring consistency across fact tables with different granularities can be complex.
Potential for Data Integrity Issues: If dimension tables are not properly maintained, data integrity issues may arise, especially with attributes that change frequently.
Data Redundancy: Due to denormalization, dimension tables may contain redundant data, leading to higher storage costs. Updates to dimension attributes require updating multiple rows.
Not Ideal for Real-Time Processing: Optimized for historical data analysis and may not support real-time transaction processing or low-latency updates.
Lack of Flexibility for Complex Relationships: More complex many-to-many relationships may be harder to model.
Maintenance Nightmare: The effort and cost of maintaining information marts can increase significantly over time.
🌳 Dimensional Modeling: Dimension Tables
Dimension tables are crucial in dimensional modeling, providing descriptive context for the facts in a data warehouse. They represent entities relevant to business and analytics requirements, such as products, people, places, dates, and times.
📝 Understanding Dimension Tables
In a dimensional model, a dimension table describes an entity relevant to your business and analytics requirements. These tables typically represent:
What: Items sold or manufactured.
Where: Locations, regions, stores.
Who: Customers, employees, salespeople.
When: Dates and timestamps of events.
Dimension tables are often prefixed with d_ or Dim_ for easy identification.
Example d_Salesperson table structure:
CREATE TABLE d_Salesperson (
Salesperson_SK INT NOT NULL, -- Surrogate key
EmployeeID VARCHAR(20) NOT NULL, -- Natural key(s)
FirstName VARCHAR(20) NOT NULL,
-- ... other dimension attributes
SalesRegion_FK INT NOT NULL, -- Foreign key(s) to other dimensions
-- Historical tracking attributes (SCD type 2)
RecChangeDate_FK INT NOT NULL,
RecValidFromKey INT NOT NULL,
RecValidToKey INT NOT NULL,
RecReason VARCHAR(15) NOT NULL,
RecIsCurrent BIT NOT NULL,
-- Audit attributes
AuditMissing BIT NOT NULL,
AuditIsInferred BIT NOT NULL,
AuditCreatedDate DATE NOT NULL,
AuditCreatedBy VARCHAR(15) NOT NULL,
AuditLastModifiedDate DATE NOT NULL,
AuditLastModifiedBy VARCHAR(15) NOT NULL
);🔑 Surrogate Keys: The Foundation
A surrogate key is a single-column unique identifier, generated and stored in the dimension table. It serves as the primary key for relating to other tables in the dimensional model.
Key Benefits
Consolidate multiple data sources without identifier clashes.
Convert multi-column natural keys into efficient single-column keys.
Track dimension history with SCD type 2.
Optimize fact table storage with the smallest possible integer data type.
Best Practice
A surrogate key column is recommended even when a natural key seems acceptable. Avoid giving meaning to key values, except for date and time dimension keys.
Dimension Surrogate Keys Generation Approaches
Approach | Pros | Cons |
|---|---|---|
Hash of the business (natural) key (e.g., SHA256 or MD5) | Can be processed in a distributed environment; Reloads generate the same values; Doesn't require joins to dimensions to identify the surrogate key when loading fact tables | Requires that the business key is stable; Not as user-friendly as INT keys |
Generate GUID (Globally Unique Identifier) | Can be processed in a distributed environment | Requires joins to dimensions to load fact tables; Not as user-friendly as INT keys; Reloads generate new values, so if a dimension table is reloaded, all fact tables that reference it should be reloaded as well |
Generate an autoincremented value (identity column) | User (engineer)-friendly values; Facilitates more efficient ordering by INTs that corresponds to the loading sequence | Requires joins to dimensions to load fact tables; Reloads generate new values, so if a dimension table is reloaded, all fact tables that reference it should be reloaded as well; Inefficient to generate in distributed environments |
Caution: Never perform a truncate and full reload of a dimension table with automatically generated surrogate keys if it's referenced by fact tables or supports SCD type 2 changes.
🗑️ Dimension Member Deletions
Avoid synchronizing deletions with the dimension table, unless members were created in error and have no related fact records.
Soft Delete Approach
Mark dimension members as no longer active or valid using a Boolean attribute (e.g., IsDeleted or IsActive). Update this column to TRUE (1) for deleted members.
Current Version Tracking
The current, latest version of a dimension member can be marked with a Boolean (bit) value in IsCurrent or IsActive columns.
Reporting Filters
All reporting queries and Power BI semantic models should filter out soft-deleted records to maintain data integrity.
🕵️ Audit Attributes
Audit attributes are optional but highly recommended. They track when and how dimension records were created or modified, and can include diagnostic information from ETL processes.
Temporal Tracking: Track who (or what process) updated a row and when.
Diagnostics: Help diagnose challenging problems, like when an ETL process stops unexpectedly.
Quality Flags: Flag dimension members as errors or inferred members for data quality monitoring.
📏 Dimension Table Size Considerations
The most useful and versatile dimensions are often big, wide dimensions (millions+ rows, hundreds of attributes). Size isn't the primary concern; what matters is that the dimension supports required filtering, grouping, and accurate historical analysis.
Big Dimensions: Sourced from multiple systems, requiring combining, merging, deduplicating, and standardizing data while assigning surrogate keys.
Small Dimensions: Tiny lookup tables with few records and attributes, often storing category values.
Consolidation Tip: When many small dimensions exist, consider consolidating them into a junk dimension to reduce clutter and improve efficiency.
⚖️ Denormalization vs. Normalization
Snowflake Dimensions
A snowflake dimension is a normalized dimension, storing data across several related tables.
When to Use Snowflake Dimensions
The dimension is extremely large, and storage costs outweigh query performance needs.
You need keys to relate the dimension to higher-grain facts.
You need to track historical changes at higher levels of granularity.
Important: A hierarchy in a semantic model can only be based on columns from a single table. A snowflake dimension should deliver a denormalized result using a view that joins the snowflake tables together.
The diagram shows a snowflake dimension comprising three related dimension tables: Product, Subcategory, and Category, each with specific attributes at its level of granularity.
🌲 Understanding Hierarchies
Dimension columns commonly produce hierarchies that enable exploring data at distinct levels of summarization (e.g., drilling down from yearly to quarterly sales).
Single Denormalized Dimension: Columns from one table storing all hierarchy levels.
Snowflake Dimension: Multiple related tables forming hierarchy levels.
Parent-Child Relationship: Self-referencing dimension for unbalanced hierarchies.
Hierarchies can be balanced, unbalanced, or ragged.
Balanced Hierarchies
Balanced hierarchies are the most common, having the same number of levels across all branches.
Common Examples:
Calendar hierarchy: Year $\rightarrow$ Quarter $\rightarrow$ Month $\rightarrow$ Date
Geography hierarchy: Country $\rightarrow$ State $\rightarrow$ City
Product hierarchy: Category $\rightarrow$ Subcategory $\rightarrow$ Product
Levels are based on columns from a single, denormalized dimension, or from tables that form a snowflake dimension.
Facts always relate to a single level, typically the lowest, allowing facts to be aggregated (rolled up) to the highest level.
Unbalanced Hierarchies
Unbalanced hierarchies are less common and have levels based on a parent-child relationship. The number of levels is determined by the dimension rows, not specific columns, meaning different branches can have different depths.
Common Examples: Employee hierarchies (reporting structures), bill of materials, company ownership models, general ledger accounts.
Recommendation: Naturalize the hierarchy by transforming and storing hierarchy levels as columns in the dimension.
Ragged Hierarchies
A hierarchy is ragged when the parent of a member exists at a level that's not immediately above it. Missing level values repeat the value of the parent.
Example: In a geography hierarchy, New Zealand may have no states. The country value ("New Zealand") would be repeated in the
StateProvincecolumn.
⏳ Managing Historical Change
Slowly Changing Dimensions (SCDs) are mechanisms to manage how changes to dimension data are handled over time, preserving historical context.
SCD Type 1: Overwrite
Action: Overwrites the existing dimension row, as there's no need to keep track of changes. Also used to correct errors.
Impact: No history preserved. The existing row is updated, which can result in different higher-level aggregations for historical data.
Example: If a salesperson is assigned to a new region, historic sales will roll up to the new region as if they were always assigned there.
SCD Type 2: Versioning
Action: Inserts a new row representing a time-based version of a dimension member. There's always a current version.
Key Requirements: Requires a surrogate key because there will be duplicate natural keys. Historical tracking attributes (e.g.,
RecValidFromKey,RecValidToKey,RecIsCurrent) store validity periods.Impact: Full history preserved. The granularity of related fact tables is at the member version level, allowing historic results to roll up correctly, but analysts must navigate multiple member versions.
SCD Type 2 Implementation Details
Update Current Version: Set the end validity date to the ETL processing date and the current flag to
FALSE.Insert New Version: Set the start validity date to the previous end date and the current flag to
TRUE.Balance: Avoid too many SCD type 2 changes on a dimension, as it can complicate analysis. Consider adding frequently changing attributes to the fact table instead.
SCD Type 3: Limited History
Action: Tracks limited history by storing previous and current values in separate columns.
Use Case: Useful when you only need to preserve the most recent change.
Mechanism: Overwrites the dimension row while keeping the previous value in a dedicated column (e.g.,
PreviousSalesRegion).Limitations: Not practical in real-life due to frequent schema changes required for tracking new columns. Not commonly used in semantic models.
📅 Date Dimension
The date (or calendar) dimension is the most common dimension, storing one row per date and supporting filtering/grouping by periods.
Natural Key: Should use the
DATEdata type.Surrogate Key: Should store the date in YYYYMMDD format using the
INTdata type, making it human-readable, efficient, and ISO 8601 compliant.Attributes: Includes columns for Year, Quarter, Month, Day, WeekOfYear, IsHoliday, HolidayText, IsWeekday, LastDayOfMonth, and relative offsets (e.g.,
RelativeMonthOffset).Population: Generated beforehand (e.g., 100 years * 365 = 36,500 rows) and rarely modified. Should include future dates for targets or forecasts.
⏰ Time Dimension
When facts require analysis at a specific point in time, a dedicated time (or clock) dimension is essential.
Granularity: Can range from minutes (1,440 rows) to seconds (86,400 rows).
Natural Key: Should use the
TIMEdata type.Surrogate Key: Consider a human-readable
INTformat likeHHMMorHHMMSS.Attributes: Includes Hour, HalfHour, QuarterHour, Minute, time period labels (morning, afternoon), work shift names, and peak/off-peak flags.
Important: A date dimension should not include a grain that extends to time of day. Use both a date dimension and a separate time dimension.
🤝 Conformed Dimensions
Conformed dimensions are shared across multiple fact tables, delivering consistency and reducing development effort.
Common Examples: Date dimensions (used by nearly all fact tables) and product dimensions.
Benefit: Ensures consistent data analysis across different business areas.
Entity Overlap: When entities overlap (e.g., employees who are also system users), combine their attributes into a single conformed dimension.
🌌 Fact Constellation and Galaxy Schemas
Fact Constellation Schema
A constellation schema consists of a set of star schemas with hierarchically linked fact tables.
The links between various fact tables provide the ability to "drill down" between levels of detail (e.g., from Sale to Sale Item).
Fact Galaxy Schema
More generally, a set of star schemas or constellations can be combined to form a galaxy.
A galaxy is a collection of star schemas with shared dimensions.
Unlike a constellation schema, the fact tables in a galaxy do not need to be directly related.
🎭 Role-Playing Dimensions
A role-playing dimension occurs when a dimension is referenced multiple times in a fact table, with each reference representing a distinct role.
Example: A sales fact table may have
OrderDate_Date_FK,ShipDate_Date_FK, andDeliveryDate_Date_FKkeys, all referencing the same physical date dimension.Another Example: An Airport dimension related twice to a Flight fact table as
Departure AirportandArrival Airport.
🗑️ Junk Dimensions
A junk dimension consolidates many small dimensions into a single dimension. It is useful when there are many independent dimensions with few attributes and low cardinality.
Benefits:
Reduce the number of dimensions.
Decrease fact table keys and storage size.
Reduce Data pane clutter.
Good Candidates: Flags and indicators, order status, customer demographic states (gender, age group).
A junk dimension table typically stores the Cartesian product of all dimension attribute values with a surrogate key.
👻 Degenerate Dimensions
A degenerate dimension occurs when the dimension is at the same grain as the related facts. A common example is a sales order number dimension that relates to a sales fact table.
Typically, the invoice number is a single, non-hierarchical attribute in the fact table. It's accepted practice not to copy this data to create a separate dimension table.
You can create a view in a Fabric Warehouse that presents the degenerate dimension as a dimension for querying purposes.
⛵ Outrigger Dimensions
When a dimension table relates to other dimension tables, it's known as an outrigger dimension. An outrigger dimension helps to conform and reuse definitions in the dimensional model.
Example: A geography dimension storing geographic locations for every postal code, referenced by both customer and salesperson dimensions. This ensures consistent geographic locations for analysis.
The date dimension can be used as an outrigger dimension when other dimension table attributes store dates (e.g., birth date in a customer dimension using the date dimension's surrogate key).
↔️ Multivalued Dimensions
When a dimension attribute must store multiple values, you implement a multivalued dimension using a bridge table (or join table) to store the many-to-many relationship between entities.
Example: If salespeople can be assigned to multiple sales regions, a bridge table stores each salesperson-region relationship, creating a logical many-to-many relationship.
Practical Approach: Have direct Foreign Keys (FKs) from the fact table to all combinations stemming from the many-to-many relationship (e.g., FKs to both Customer and Account).
🎯 Dimensions: Key Takeaways
Design Principles
Use surrogate keys for all dimensions.
Denormalize for performance and usability.
Include audit attributes for tracking.
Balance historical tracking needs with complexity.
Common Patterns
Implement date and time dimensions for temporal analysis.
Use conformed dimensions for consistency.
Apply SCD types based on business requirements.
Leverage role-playing dimensions when appropriate.
Special Considerations
Consolidate small dimensions into junk dimensions.
Naturalize unbalanced hierarchies when possible.
Use bridge tables for many-to-many relationships.
Create views for degenerate dimensions.
🏷️ Dimensional Modeling: Entity Classification
Entity classification is the first step in converting an Entity Relationship (ER) model into a dimensional model for analytics and reporting. The goal is to understand the role each entity plays in analysis and prepare for building fact and dimension tables in a star schema.
📈 Step 1: Classify Entities Overview
This initial stage helps transform complex operational models into simplified analytical models.
Entities are classified into three distinct categories based on how they represent business activity and context: Transaction, Component, and Classification Entities.
Why Classification Matters
Identify Business Events: Helps pinpoint which business events decision-makers want to analyze and track.
Structure Star Schemas: Provides a clear structure for organizing fact tables and dimension tables.
Simplify Models: Simplifies complex operational models for analytical use while preserving essential business meaning.
💰 Transaction Entities
Transaction entities record details about specific business events that occur at a point in time. These events are typically the primary focus of analysis and represent measurable activities.
Key Characteristics
Describe an event that happens at a specific moment in time.
Contain measurements or quantities (e.g., dollar amounts, weights, volumes, counts).
Most many-to-many relationships represent transaction entities.
Role in Dimensional Modeling
Transaction entities are the most important in a data warehouse.
They form the basis for constructing fact tables in star schemas.
User input is required to identify which transactions are relevant for analytical requirements.
Common Examples
Orders | Insurance Claims |
|---|---|
Customer purchase transactions with line items and totals | Filed claims with amounts and processing details |
Salary Payments | Hotel Bookings |
Payroll transactions with amounts and payment dates | Reservation events with rates and stay duration |
Attendance | Log In |
Entering a room | Each log in and log out event in a system |
IoT measurements | Exam Attempt |
Measuring a specific value by a sensor at any point in time | Each exam attempt, even a failed one, is an event |
🧩 Component Entities
Component entities are directly related to transaction entities through one-to-many relationships. They describe the individual components or dimensions that define each business event, providing essential context.
Role in Dimensional Modeling
Component entities become dimension tables in star schemas.
They provide the business context for slicing, dicing, and filtering fact data.
They answer fundamental analytical questions: who, what, when, where, how, and why for each transaction.
Customer | Product |
|---|---|
Who made the purchase or participated in the event | What was sold or what item was involved |
Location | Period |
Where the transaction occurred or was processed | When the transaction took place in time |
Important Note: Time is a critical component entity because historical analysis is a core requirement of data warehouses. Every fact table should include time dimensions for trend analysis.
🏷️ Classification Entities
Classification entities are related to component entities through chains of one-to-many relationships. They are functionally dependent on component entities, representing hierarchical structures.
Purpose
Represent hierarchies embedded within the data model.
Capture classification and grouping structures such as categories and levels.
Enable drill-down and roll-up analysis paths.
If data is suitable for a low-cardinality dropdown in a UI, it's likely a Classification entity.
Role in Dimensional Modeling
Collapse Hierarchies: Classification entities can be collapsed into component entities when creating dimension tables.
Support Analysis: They support hierarchical analysis such as roll-ups and drill-downs across levels.
Preserve Meaning: They simplify the analytical model while preserving business meaning and relationships.
🧐 Resolving Ambiguities
When an entity fits into multiple categories, a precedence hierarchy is used:
Transaction Entity (highest precedence)
Classification Entity
Component Entity (lowest precedence)
This hierarchy determines an entity's primary role in a star schema.
🌲 Hierarchies
Hierarchies are crucial in dimensional modeling, forming the primary basis for deriving dimensional models from ER models. Most dimension tables embed these hierarchies. An ER model hierarchy is a sequence of entities joined by one-to-many relationships, aligned in the same direction.
Parent/Child: State is the parent of Region; Region is the child of State.
Descendants: Sale Item, Sale, Sale Location, Region are all descendants of State.
Ancestors: Sale, Sale Location, Region, State are all ancestors of Sale Item.
Maximal Hierarchy
A hierarchy is maximal if it cannot be extended further upwards or downwards, capturing the complete scope of a business relationship.
Minimal Entity
An entity is minimal if it resides at the very bottom of a maximal hierarchy, possessing no further one-to-many relationships pointing away from it ("leaf" entities).
Maximal Entity
An entity is maximal if it is positioned at the top of a maximal hierarchy, meaning it has no further many-to-one relationships leading to it ("root" entities).
Entity Hierarchies Example
From an example data model, we can identify:
Minimal Entities: Sale Item and Sale Fee (2 entities).
Maximal Entities: Period, CustomerType, State, Location Type, Product Type, and Fee Type (6 entities).
These hierarchies define detailed relationships and lineage, providing a structured view for analysis.
Product Hierarchies: Product-type $\rightarrow$ Product $\rightarrow$ Sale-item; Fee-type $\rightarrow$ Sale-fee.
Customer Hierarchies: State $\rightarrow$ Region $\rightarrow$ Customer $\rightarrow$ Sale $\rightarrow$ Sale-fee (and Sale-item); Customer-type $\rightarrow$ Customer $\rightarrow$ Sale $\rightarrow$ Sale-fee (and Sale-item).
Location Hierarchies: Location-type $\rightarrow$ Location $\rightarrow$ Sale $\rightarrow$ Sale-fee (and Sale-item); State $\rightarrow$ Region $\rightarrow$ Location $\rightarrow$ Sale $\rightarrow$ Sale-fee (and Sale-item).
Time Hierarchies: Period(sale-date) $\rightarrow$ Sale $\rightarrow$ Sale-fee (and Sale-item); Period(posted-date) $\rightarrow$ Sale $\rightarrow$ Sale-fee (and Sale-item).
🔨 Producing Dimensional Models
Dimensional modeling employs specialized operators to transform ER models into star schemas, optimizing data for analytical queries.
Operator 1: Collapse Hierarchy
This foundational operator consolidates attributes from a higher-level entity into a lower-level entity within a one-to-many hierarchy.
Process: A deliberate act of denormalization, introducing redundancy to create flatter, more performant dimension tables.
Example: Attributes of a 'State' entity (State ID, State Name) are absorbed into the 'Region' entity, creating a single, broader dimension.
Operator 2: Aggregation
The aggregation operator is applied to a transaction entity to create a new entity containing summarized data.
Process: Involves selecting a subset of numerical attributes to aggregate and another subset of attributes to group by.
Example: Applying aggregation to a
Sale Itementity to create aProduct Summaryentity (e.g., total sales amount, average quantity per order, average price per item on a daily basis).Aggregation Attributes: Quantity, Price.
Grouping Attributes: Product ID, Sale Date.
Important: Aggregation inherently involves a loss of detailed information. Once data is summarized, it's not possible to reconstruct individual details.
Consequence: In ETL, one fact table is usually kept at the lowest granularity, and then other aggregate fact tables are created.
📊 Dimensional Modeling: Fact Tables
Fact tables are the core of a dimensional model, storing measurable business metrics and linking to dimension tables for context. They are typically narrow (few columns) but immense in terms of rows (billions+).
📝 Fact Table Structure
Consider a sales fact table named f_Sales as an example of good design practices:
CREATE TABLE f_Sales (
-- Dimension keys
OrderDate_Date_FK INT NOT NULL,
ShipDate_Date_FK INT NOT NULL,
Product_FK INT NOT NULL,
Salesperson_FK INT NOT NULL,
-- Attributes
SalesOrderNo INT NOT NULL,
SalesOrderLineNo SMALLINT NOT NULL,
-- Measures
Quantity INT NOT NULL,
-- Audit attributes
AuditMissing BIT NOT NULL,
AuditCreatedDate DATE NOT NULL,
AuditCreatedBy VARCHAR(15) NOT NULL,
AuditLastModifiedDate DATE NOT NULL,
AuditLastModifiedBy VARCHAR(15) NOT NULL
);Primary Key and Dimension Keys
Primary Key: Fact tables usually do not have an explicit primary key. It often doesn't serve a useful purpose and increases storage size unnecessarily. The primary key is often implied by the set of dimension keys and attributes.
Dimension Keys: References to the surrogate keys (or higher-level attributes) in related dimensions. An unusual fact table would not include at least one date dimension key.
Role-Playing Dimension: A fact table can reference a dimension multiple times. For example,
OrderDate_Date_FKandShipDate_Date_FKboth reference the same physical date dimension, each playing a distinct role.
Attributes, Measures, and Audit Attributes
Attributes: Provide additional information and set the granularity of fact data, but are neither dimension keys nor dimension attributes, nor measures. Examples include sales order numbers or tracking numbers. An attribute can form a degenerate dimension for analysis.
Measures: Typically numeric and commonly additive (can be summed and summarized). The
Quantitycolumn in the example is a measure.Audit Attributes: Optional attributes that track when and how fact records were created or modified, and can include diagnostic information from ETL processes.
📏 Fact Table Size and Design Concepts
Fact tables vary in size based on dimensionality, granularity, number of measures, and amount of history. They are typically narrow (fewer columns) but big or immense in terms of rows (exceeding billions).
Three Types of Fact Tables
Transaction fact tables
Periodic snapshot fact tables
Accumulating snapshot fact tables
Measure Types
Measures are typically numeric and commonly additive. However, some measures are semi-additive or non-additive.
Additive Measures: Can be summed across any dimension (e.g., order quantity, sales revenue in a single currency).
Semi-Additive Measures: Can be summed across certain dimensions only.
Any measure in a periodic snapshot fact table cannot be summed across other time periods.
A stock balance measure in an inventory fact table cannot be summed across other products.
Sales revenue with a currency dimension key cannot be summed across currencies.
Non-Additive Measures: Cannot be summed across any dimension (e.g., temperature reading, rates, ratios). These can still be aggregated using count, distinct count, minimum, maximum, or average.
🛒 Transaction Fact Tables
A transaction fact table stores business events or transactions. Each row stores facts in terms of dimension keys and measures, and optionally other attributes. All data is fully known when inserted and never changes (except for error correction).
Typically store facts at the lowest possible level of granularity.
Contain measures that are additive across all dimensions.
Example: A sales fact table storing every sales order line.
fact_salehas the granularity of the most granular table in the source database (e.g.,sales_order_details,sales_items).
📸 Periodic Snapshot Fact Tables
A periodic snapshot fact table stores measurements at a predefined time or specific intervals. It provides a summary of key metrics over time and is useful for trend analysis. Measures are always semi-additive.
Example: An inventory fact table loaded daily with the end-of-day stock balance of every product.
Use Case: Used when recording large volumes of transactions is expensive and does not support useful analytical requirements, focusing on trends rather than individual transactions.
🔄 Accumulating Snapshot Fact Tables
An accumulating snapshot fact table stores measurements that accumulate across a well-defined period or workflow. It often records the state of a business process at distinct stages or milestones, which might take days, weeks, or months to complete.
Initial Event: A fact row is loaded soon after the first event in a process.
Milestone Updates: The row is updated in a predictable sequence every time a milestone event occurs.
Process Completion: Updates continue until the process completes.
Have multiple date dimension keys, each representing a milestone event.
Some dimension keys might record an
N/Astate until a certain milestone is reached.Measures typically record durations between milestones, providing valuable insight into business workflows.
👻 Factless Fact Tables
A factless fact table does not contain any measure columns. It typically records events or occurrences (e.g., students attending class). From an analytics perspective, a measurement can be achieved by counting fact rows.
Purpose: Records the occurrence of an event or condition with no numeric measures.
Examples: "customer viewed product in store on date", "customer eligible for promotion in store on date", "customer-product-store had at least one touchpoint".
| dim_customer | | fact_customer_product_store_event | | dim_store | |
|----------------|--------------|-------------------------------------|--------------|-----------------|--------------|
| customer_skey | int | customer_skey | int | store_skey | int |
| customer_code | varchar(50) | product_skey | int | store_code | varchar(50) |
| full_name | varchar(200) | store_skey | int | store_name | varchar(200) |
| segment | varchar(50) | event_type_skey | int | city | varchar(100) |
| | | event_ts | datetime | region | varchar(100) |
| | | source system | varchar(50) | | |
| dim_product | | source_event_id | varchar(100) | dim_event_type | |
| product_skey | int | | | event_type_skey | int |
| sku | varchar(50) | | | event_type_code | varchar(50) |
| product_name | varchar(200) | | | event_type_name | varchar(200) |
| category | varchar(100) | | | event_group | varchar(100) |
| brand | varchar(100) | | | is_active | boolean |🌌 Fact Constellation Schema & Fact Galaxy Schema
Fact Constellation Schema
An aggregate fact table represents a rollup of a base fact table to a lower dimensionality and/or higher granularity. Its purpose is to accelerate query performance for commonly queried dimensions.
Consists of a set of star schemas with hierarchically linked fact tables.
Links between fact tables provide the ability to drill down between levels of detail (e.g., from Sale to Sale Item).
Fact Galaxy Schema
More generally, a set of star schemas or constellations can be combined to form a galaxy.
A galaxy is a collection of star schemas with shared dimensions.
Unlike a constellation schema, the fact tables in a galaxy do not need to be directly related.
📥 Dimensional Modeling: Loading Dimensions and Facts
Loading a dimensional model involves periodically running an Extract, Transform, and Load (ETL) process. This process orchestrates the staging of source data, synchronization of dimension data, insertion of rows into fact tables, and recording of auditing data and errors.
Pipelines: Build workflows to orchestrate the ETL process, executing SQL scripts, stored procedures, etc.
Dataflows: Develop low-code logic to ingest data from hundreds of sources using the Power Query experience.
ETL Development: Can be complex and challenging, often consuming 60-80% of a data warehouse development effort.
⚙️ Orchestration
The general workflow of an ETL process follows a structured sequence:
01 | 02 |
|---|---|
Load Staging Tables | Process Dimension Tables |
Optionally extract and stage source data for processing | Synchronize dimension data and handle historical changes |
03 | 04 |
Process Fact Tables | Post-Processing Tasks |
Load fact data with proper dimension key lookups | Trigger refreshes and send notifications |
Dimension tables should be processed first, especially when there are dependencies (e.g., an outrigger dimension like geography must be processed before customer and vendor dimensions).
Fact tables can be processed once all dimension tables are complete.
Post-processing includes triggering refreshes of semantic models and sending notifications about the ETL outcome.
staging data 🚧
Staging data is an intermediary step in the ETL process, where raw source data is temporarily stored before transformation and loading into the data warehouse.
Data Virtualization Alternatives
Consider data virtualization to reduce data movement and improve efficiency:
Mirroring: A low-cost, low-latency solution that creates a replica of your data in OneLake.
PolyBase in SQL Server: Allows T-SQL queries to join data from external sources to relational tables.
OneLake Shortcuts: Point to other storage locations containing source data, usable as tables in T-SQL queries.
Azure SQL Managed Instance: Execute T-SQL queries on files in Azure Data Lake Storage Gen2 or Azure Blob Storage and combine with locally stored relational data.
🔄 Transform Data
The structure of source data often differs from the destination structures of dimensional model tables. The ETL process must reshape and transform source data to ensure quality and consistency.
"Garbage in, garbage out" applies: avoid loading low-quality data into dimensional model tables.
Key Transformation Activities
Combine Data: Integrate data from different sources based on matching keys or append data sharing a common structure (e.g., merging product data using SKU, unioning sales data).
Detect Historical Change: Detect and appropriately store changes in dimension tables to maintain historical accuracy (SCD types).
Convert Data Types: Convert data types to align with the dimensional model tables.
Aggregate Data: Reduce fact table dimensionality or raise the granularity of facts (e.g., grouping sales by dimension keys without storing sales order numbers).
Calculations: Produce values for dimensional model tables (e.g., concatenate first and last names for full name, calculate gross sales revenue).
⬆️ Load Data
Various data ingestion options are available for loading tables in a Fabric Warehouse, each suited for different scenarios.
COPY INTO(T-SQL): Useful for Parquet or CSV files stored in external Azure storage accounts (ADLS Gen2, Azure Blob Storage).Dataflows: Provide a code-free experience to transform and clean data with an intuitive interface.
Pipelines: Can include activities that run T-SQL statements, perform lookups, or copy data from a source to a destination.
Cross-Warehouse Ingestion: When data is stored in the same workspace, allows joining different warehouse or lakehouse tables. Supports
INSERT...SELECT,SELECT INTO, andCREATE TABLE AS SELECT(CTAS) commands for efficient set-based operations.
📝 Logging
ETL processes require dedicated monitoring and maintenance. Logging the results of the ETL process to non-dimensional model tables in your warehouse is recommended.
ETL Process Logging
Unique ID for each execution.
Start time and end time.
Status (success or failure).
Any errors encountered.
Table-Level Logging
Start and end times.
Status indicators.
Rows inserted, updated, deleted.
Final table row count.
Error details.
A semantic model dedicated to monitoring ETL processes can help identify bottlenecks and predict future data warehouse size.
Operation Logging
Start and end times of semantic model refresh operations.
Performance metrics.
Resource utilization.
🌳 Process Dimension Tables
Processing a dimension table involves synchronizing the data warehouse data with source systems. Source data is transformed, prepared, and then matched with existing dimension table data by joining on business keys.
New Products: Rows are inserted into the dimension table.
Modified Products: Existing rows are either updated (SCD type 1) or a new version is inserted and the existing one expired (SCD type 2).
Historical Tracking: SCD type 2 expires current versions and inserts new versions.
📊 Process Fact Tables
Processing a fact table involves synchronizing data with source system facts. Source data is transformed, and for each dimension key, a lookup determines the surrogate key value to store in the fact row.
Dimension Key Lookups
When a dimension supports SCD type 2, the surrogate key for the current version of the dimension member should be retrieved.
For date and time dimensions, the surrogate key can often be computed (e.g., YYYYMMDD or HHMM format).
Integrity Check: Since many DW systems don't enforce foreign keys, the ETL process must check for integrity when loading fact tables.
Handling Lookup Failures
If a dimension key lookup fails (indicating a source system integrity issue), the fact row must still be inserted with a valid dimension key.
Approach 1: Store a special dimension member like
Unknown.Approach 2: If the natural key is valid, insert a new dimension member and store its surrogate key.
Incremental vs. Full Reload
Whenever possible, a fact table should be loaded incrementally (new facts detected and inserted). This is more scalable and reduces workload.
Truncating and reloading a large fact table should be a last resort due to cost, resources, and complexity with SCD type 2 dimensions.
Detecting New Facts
Source System Identifiers/Timestamps: Rely on these to efficiently detect new facts (e.g., storing the latest sales order number as a high watermark).
Change Data Capture (CDC): SQL Server and Azure SQL Managed Instance offer CDC to track row changes.
Create Date Columns: Can reliably detect new orders.
Database Triggers: Add triggers to relational tables that store keys of inserted, updated, or deleted records for efficient change detection.
Inferred Dimension Members
When a fact load process inserts a new dimension member, it's known as an inferred member.
Initial Creation: Only the natural key is known; the fact load process creates a new dimension member with
Unknownattribute values.Late Arriving Details: When details arrive later, the dimension load process updates the dimension row.
Flag as Inferred: Set an audit attribute like
IsInferredMembertoTRUE.
✏️ Fact Updates or Deletions
You might be required to update or delete fact data (e.g., canceled sales orders, changed order quantities).
Soft Deletes Strategy: For canceled orders, change the sales order status (e.g., from
OpentoCanceled) rather than deleting the row. This preserves history.Identification Attributes: Include attributes like sales order number and line number in the fact table to help identify rows for modification. Index these columns for efficient operations.
Quantity Updates: For quantity changes, an update of the fact row quantity measure is necessary.
Periodic Processing: If fact data was inserted using an
Unknowndimension member, run a periodic process to retrieve current source data and update dimension keys to valid values.
All reporting queries and Power BI semantic models should filter out soft-deleted records to ensure accurate reporting.
➡️ Pipeline Lineage
Transformations, Loading Dimensions, Facts, and Derived Facts
This diagram illustrates the flow of data through a pipeline, from raw (bronze) to refined (silver) tables, then into dimension (dim) tables, and finally into fact (fact) tables for analysis. This process transforms and loads data through different stages, preparing it for reporting and analytics.
📊 Архитектури на податочна платформа и димензионално моделирање
Архитектурите на податочните платформи се основни за управување и анализа на податоците на претпријатието. Едно складиште на податоци (DW) служи како централно складиште, оптимизирано за аналитички прашања, во остар контраст со оперативните бази на податоци дизајнирани за трансакциска обработка. Разбирањето на интеракцијата помеѓу различните архитектури на податоци, обрасците за обработка како OLTP и OLAP и современите концепти како што се езерата со податоци и езерските куќи е од клучно значење за ефективно инженерство на податоци.
🏛️ Основи за складиште на податоци
Складиштето за податоци е основна компонента на деловната интелигенција, обезбедувајќи структурирано складиште за историски податоци што може да се испита. Се однесува на ограничувањата на директното барање оперативни бази на податоци, кои обично се нормализираат за трансакциска ефикасност и на тој начин работат слабо на сложени аналитички прашања.
🚨 Предизвик без складиште на податоци
Директно барање оперативни бази на податоци (OLTP системи) за аналитички цели претставува неколку прашања:
Бавна изведба на барање: Нормализираните структури на податоци (3NF) не се оптимизирани за големи, сложени аналитички прашања.
Оптоварување на системот: Аналитичките прашања може прекумерно да ги оптоварат оперативните системи, што влијае на трансакциите во реално време.
Комплексни спојувања: Често се потребни обемни спојувања за да се добијат значајни аналитички сознанија од нормализирани податоци.
Складиштето на податоци ги надминува овие предизвици со складирање на податоци во формати оптимизирани за читање, собирање и барање големи количини историски податоци.
🔄 Шаблони за пристап OLTP наспроти OLAP
Обработка на онлајн трансакции (OLTP) и Онлајн аналитичка обработка (OLAP) претставуваат фундаментално различни обрасци за пристап до податоци, што бара посебни архитектонски пристапи.
OLTP системи:
Рачка чести, мали трансакции.
Оптимизиран за поединечни трансакции и операции со податоци на ниво на ред.
Фокусирајте се на модификации на податоци во реално време.
Примери: внесување нарачки, банкарски трансакции.
OLAP системи:
Процес сложени прашања низ огромни збирки на податоци.
Организирајте ги податоците во повеќедимензионални коцки (време, географија, категории на производи).
Овозможете интерактивни операции како пресечете, исечете на коцки, издупчете и свртете го.
Обезбедете брза агрегација и однапред пресметани резимеа за брзи одговори.
Поддршка сложени аналитички функции.
Магацините на податоци често користат димензионално моделирање, организирање на податоци во табели со факти (мерења) и табели со димензии (контекст) за интуитивна анализа.
📊 Кога OLTP системите паѓаат: потребата од OLAP
Оперативните бази на податоци, иако се одлични за трансакции, се борат со длабоки аналитички прашања кои бараат собирање огромни историски збирки на податоци низ повеќе димензии. Ова е местото каде складишта на податоци и OLAP системи се истакнуваат.
Примери на сложени аналитички прашања со кои OLTP системите се борат:
Комплексни трендови на продажба: Анализирање на растот на продажбата од година во година по линија на производи, регион и сегмент на клиенти за да се идентификуваат сезонските модели.
Предвидувачки инвентар: Прогнозирање на оптимални нивоа на залихи со комбинирање на историските продажби, времето на испорака на добавувачите и промотивните влијанија.
Метрика на вредноста на клиентите: Пресметување на вкупниот приход што се очекува да го генерира клиентот во текот на нивниот однос со бизнисот.
Финансиски перформанси: Споредување на буџетираните расходи и приходи со реалните бројки низ одделите за анализа на варијанса.
📝 Дефинирање на складиште на податоци
Истакнати фигури во управувањето со податоци дадоа основни дефиниции:
Овие дефиниции нагласуваат дека складиштето на податоци е структурирани податоци дизајнирани да поддржуваат одлучување и деловна интелигенција.
🏷️ Клучна терминологија за традиционално складирање податоци
Магацин на податоци: А централизирано складиште за интегрирани, историски податоци, дизајнирани за аналитичко известување и деловна интелигенција.
Нормализиран модел: Пристап за дизајнирање на базата на податоци што го намалува вишокот на податоци и го подобрува интегритетот, првенствено за трансакциски системи.
БИ (Бизнис интелигенција): Алатки и процеси кои ги трансформираат необработените податоци во значајни согледувања за информирано деловно одлучување.
Податоци Март: А фокусирано подмножество на складиште на податоци, кои ги опслужуваат аналитичките потреби на одреден оддел или деловна функција.
OLAP (Онлајн аналитичка обработка): Технологија која овозможува интерактивна, мултидимензионална анализа на големи количини на податоци за стратешки согледувања.
Модел за рударство податоци: Алгоритми и техники кои се користат за откривање обрасци, трендови и аномалии од големи збирки податоци за предвидувачка анализа.
Димензионален модел, Ѕвездена шема: Модел на логички податоци (факти + димензии) оптимизирање на складишта на податоци за брзо, интуитивно барање.
ETL (екстракција, трансформација, оптоварување): Основен процес на преместување податоци од изворни системи, нивно чистење/реструктуирање и вчитување во складиште на податоци.
Ад-хок барање: Прашање формулирано на лето за да се одговори a специфично, спонтано деловно прашање.
🚀 Развој на пејзаж на податоци: клучни концепти
Како што растат обемот и сортите на податоци, се појавија нови архитектонски парадигми за да ги задоволат современите аналитички барања.
Архитектура на Лејкхаус: А хибридна архитектура на податоци комбинирајќи ја флексибилноста и економичноста на податоци езера со робусните способности за управување со податоци и трансакциски ACID на складишта на податоци. Поддржува и структурирани и неструктурирани податоци за разновидна аналитика и машинско учење.
Податочна мрежа: А децентрализирана архитектура на податоци кој ги третира податоците како производ, организиран по деловни домени. Секој домен управува со сопствената инфраструктура на податоци, производи и стандарди, поттикнувајќи агилност, приспособливост и сопственост на податоци.
Езерото на податоци: А централизирано складиште за огромни количини на необработени, необработени податоци во нивниот мајчин формат. Тоа е шема-агностичка, идеален за аналитика на големи податоци и машинско учење.
Ткаенина за податоци: Архитектура која обезбедува а унифициран, интелигентен поглед на податоците на организацијата низ различни средини (во простории, облак, хибридни). Интегрира алатки за управување со податоци и користи AI/ML за автоматско откривање на податоци, управување и потрошувачка.
Стриминг на податоци: Генерирани податоци континуирано од бројни извори (IoT, веб-кликови, финансиски трансакции), кои бараат непосредна обработка за инстант увид.
⚙️ Процесот на инженерство на податоци
Процесот на инженеринг на податоци вклучува низа чекори за преместување, трансформирање и подготовка на податоците за анализа.
Проголтување податоци: Стекнување необработени податоци од различни извори.
Трансформација на податоци: Чистење, реструктуирање и збогатување на податоците.
Сервисирање на податоци: Ставање на обработените податоци достапни за анализа или апликации.
Оркестрација на проток на податоци: Управување со автоматизираните работни текови.
Дополнителни клучни концепти во инженерството на податоци:
Миграција на податоци: Преместување податоци помеѓу системи или околини, на пр., од просториите во облак.
Интеграција на податоци: Консолидирање на информации од различни системи и IoT уреди во унифицирани текови на податоци.
Расправија на податоци: Конвертирање на сурови, неструктурирани податоци во чисти, употребливи формати за аналитика и машинско учење.
Операции со бази на податоци: копирање табели, одржување на синхронизација и поддршка за резервна копија/обновување од катастрофи.
🏗️ Архитектура на податоци - слоеви
Современите архитектури на податоци, како Лејкхаус, често се слоевити за да управуваат со зрелоста и обработката на податоците.
Вообичаените слоеви вклучуваат:
Слој на извори на податоци: Повеќе извори на податоци (OLTP, ERP, CRM, IoT, веб, социјални медиуми) кои се хранат во архитектурата.
Слој за голтање: Стриминг во реално време (Kinesis, Event Hubs) и процеси на сериско внесување.
Слој за складирање: Складирање необработени податоци (Data Lake - S3, ADLS) и структурирано складирање (Data Warehouse - Redshift, Snowflake). Различни зони (Слетување, Суровини, Доверливи, Курирани) за зрелост на податоците.
Слој за обработка: Трансформација на податоци, чистење, цевководи ETL/ELT (Spark, Databricks), проверки на квалитетот, агрегација и збогатување.
Потрошувачки слој: BI алатки, API, Машинско учење, извештаи и наука за податоци.
🏛️ Архитектура на складишта на податоци со две нивоа
Вообичаена архитектонска шема за складишта на податоци вклучува повеќе нивоа за да се одделат грижите и да се оптимизираат перформансите.
Слој на изворни системи: Оперативни и надворешни извори на податоци.
Станирање на податоци/Слој на ETL:
Екстракт, трансформација, оптоварување процеси.
Чистење на податоците и валидација.
Примена на деловни правила.
Слој на складиште на податоци на претпријатијата (EDW):
Централизирано складиште за интегрирани, историски податоци.
Единствен извор на вистината за организацијата.
Вклучува метаподатоци за ETL процесите и каталогот на податоци.
Слој на Data Marts:
Специфично за одделот или субјектно-ориентирани подмножества.
Оптимизиран за специфични деловни функции (продажба, финансии).
Денормализиран за извршување на барањето.
Произлезено од централната EDW.
🌐 Целосен екосистем за инженерство на податоци: Технолошка перспектива
Современиот екосистем за инженерство на податоци интегрира различни компоненти за да создаде унифицирана, скалабилна архитектура.
Овој екосистем вклучува:
Извори: OLTP бази на податоци, ERP, оперативни апликации, собирачи на настани, дневници, API, складирање датотеки/објекти.
Голтање и транспорт: Репликација на податоци (Fivetran, Stitch), менаџери на работни текови (Airflow), пренос на настани (Kafka, Kinesis), Reverse ETL.
Складирање: Lakehouse, Data Lake (Delta, Iceberg, Hudi, S3), Data Warehouse (Snowflake, BigQuery, Redshift).
Барање и обработка: Spark платформи (Databricks, EMR), SQL пребарувачи (Presto, Trino), DS/ML платформи, бази на податоци за аналитика во реално време (Druid, ClickHouse), обработка на стримови.
Трансформација: Метрички слој (LookML, dbt), моделирање податоци, менаџери на работниот тек.
Анализа и излез: Контролни табли (Looker, Tableau), вградена аналитика, зголемена аналитика, работни простори за податоци, алатки за DS/ML, приспособени апликации.
Вкрстени грижи: Откривање на податоци (Amundsen, DataHub), управување со податоци (Collibra), набљудување на податоци (Монте Карло), права и безбедност (Privacera, Immuta).
ETL наспроти ELT Pipelines 🔄
Разбирање на разликата помеѓу ETL (Екстракт, трансформација, оптоварување) и ELT (Екстракт, оптоварување, трансформација) е од клучно значење за дизајнирање модерни цевководи за податоци.
📦 ETL Pipeline Architecture (Inmon & Kimball)
ETL е традиционалниот пристап каде што се случува трансформација на податоците пред вчитување во целниот систем. Ова обезбедува квалитет и конзистентност на податоците од самиот почеток.
Екстракт: Враќање необработени податоци од повеќе извори (бази на податоци, API, рамни датотеки, платформи за стриминг) во неговиот оригинален формат.
Трансформирајте: Стандардизирање и чистење на податоците за да се исполнат барањата за специфични формати. Ова вклучува примена на деловни правила, проверки на квалитетот на податоците и форматирање, значително подобрување откриеност на податоците и употребливост.
Вчитај: Зачувување на трансформираните податоци до нивната крајна дестинација, обично a складиште за податоци, подготвен за итна анализа и известување.
☁️ ELT Pipeline Architecture (Lakehouse)
ELT ја менува традиционалната ETL секвенца со вчитување необработени податоци прво и трансформирање подоцна. Овој модерен пристап ја користи процесорската моќ на складиштата за податоци во облакот и обезбедува поголема флексибилност.
Различен редослед за обработка: Екстракт, Вчитај, Трансформирај. Податоците се внесуваат во него сурова форма и се трансформира само кога е потребно за конкретни случаи на употреба.
Максимално внесување податоци: ELT цевководите се одлични во снимањето што е можно повеќе податоци однапред, овозможувајќи подоцна да се донесат одлуки за трансформација врз основа на новите деловни барања и аналитички потреби.
Флексибилност на шемата: Архитектурата ELT не бара однапред одлуки за типови на податоци, структури и формати. Ова шема-на-читање пристапот обезбедува максимална агилност за истражување и анализа на податоците.
🎯 Data Marts: Фокусирана аналитика
Податоците маршираат се помали, фокусирани складишта на податоци дизајнирани да ги опслужуваат специфичните аналитички потреби на одредена деловна единица, оддел или предметна област.
Одделенски складишта на податоци: Тие обезбедуваат побрзи перформанси на барањето и поедноставен пристап за крајните корисници со тоа што го содржат само релевантно подмножество на податоци за претпријатието.
⬆️ Пристап од дното нагоре (Кимбол)
Може да се комбинираат повеќе специјализирани податоци за да се создаде сеопфатен складиште за податоци на претпријатието. Овој пристап прво го нагласува градењето за специфични деловни процеси.
⬇️ Пристап од горе-надолу (Инмон)
Централното складиште на податоци може да се распадне на фокусирани податоци кои служат за одредени одделенија или функции. Овој пристап дава приоритет на единствен, интегриран извор на вистината.
⚖️ Пристапи на Инмон против Кимбол
Две истакнати методологии го водат дизајнот на складиштето за податоци: Пристапот од врвот надолу на Бил Инмон и Пристап од долу нагоре на Ралф Кимбал.
⬆️ Инмон - пристап од врвот надолу
Пристапот на Инмон нагласува градење а нормализиран Enterprise Data Warehouse (EDW) прво, што потоа ги храни специјализираните податоци за маркети.
Клучни принципи:
Гради а нормализиран EDW прво.
Податоците се влеваат од изворните системи во EDW, а потоа во специјализирани податочни маркети.
Се фокусира на сеопфатна интеграција на податоци да се создаде единствен извор на вистината.
Користи Трета нормална форма (3NF) за основната структура на складиштето, обезбедувајќи стабилна конзистентност на податоците и намален вишок.
Ограничувања:
Одзема време и скапо да се спроведе.
Бара значително однапред планирање и дизајн.
Комплексни ETL процеси поради нормализација.
Побавни перформанси на барањето во споредба со димензионалните модели.
Помалку агилни и тешко се прилагодуваат менување на деловните барања.
Потребно е длабоко разбирање на целиот пејзаж на податоци на претпријатието.
⬇️ Кимбол - пристап од долу нагоре
Пристапот на Кимбал го нагласува градењето димензионални податоци мартови прво, вообичаено користејќи шеми со ѕвезди, а потоа нивно интегрирање преку усогласени димензии.
Клучни принципи:
нагласува димензионално моделирање, првенствено користење шеми на ѕвезди за податоци мартови.
Најпрво се градат податочни маркети, фокусирајќи се на специфични деловни процеси (од долу-нагоре).
Се фокусира на леснотија на користење и брзи перформанси за пребарување за деловни корисници.
Користи денормализирани маси (факти и димензии) за намалување на спојувањата и подобрување на брзината.
Промовира „автобуска архитектура" за интегрирање на податочни маркети со усогласени димензии.
Дизајниран за агилен развој и брза испорака на аналитички решенија.
Ограничувања:
Потенцијал за вишок на податоци низ различни податочни маркети.
Може да доведе до недоследности ако усогласените димензии не се строго управувани.
Помалку погоден за сложени потреби за интеграција на податоци на ниво на целата компанија без внимателно планирање.
Не може да се справи со сите случаи на аналитичка употреба толку ефикасно како високо нормализираниот EDW.
Предизвици во одржувањето и развојот а голем број на различни податоци маркети.
Почетното поставување може да бара значителен напор во дефинирањето на димензиите и фактите.
🌊 Инженерство на големи податоци и езера на податоци
Дата езера претставуваат промена на парадигмата во начинот на кој организациите складираат и управуваат со податоците за претпријатијата, прифаќајќи ја целосната разновидност на современи извори на податоци во масивни размери.
За разлика од традиционалните магацини со крути шеми, езерата со податоци ја опфаќа целосната разновидност на современи извори на податоци, складирајќи сè во своите мајчин формат во масовни размери.
На врвот на езерото со податоци, може да се создадат повеќе слоеви за време на различни чекори на трансформација.
🆚 Магацин на податоци наспроти Езеро на податоци
Магацин на податоци: Податоците се обработуваат и организираат во една шема пред се става во магацинот. Анализата се прави на прочистени податоци.
Езерото на податоци: Сурови и неструктурирани податоци одат во езеро со податоци. Податоците се избираат и организираат како и кога е потребно (шема-на-читање).
🏗️ Архитектура на Дата Лејк
Податоците течат од различни извори преку слоевите за внесување, во зони за складирање со различни нивоа на префинетост и на крајот служат повеќекратни шеми на потрошувачка.
Што го дефинира езерото со податоци?
Езерото на податоци е а централизирано складиште што ги складира сите податоци на претпријатието во нивниот природен, необработен формат. Ова вклучува структурирани, полуструктурирани, неструктурирани и бинарни податоци.
Тоа е како природно езеро со повеќе притоки, кое прима континуирани текови на различни типови податоци во реално време.
Разбивање на силоси на податоци
Податочни езера се појавија за решавање фрагментација на податоците на претпријатието. Организациите ги консолидираат сите податоци во единствено складиште кое може да се скалира, обично изградено на дистрибуирани системи како Hadoop или складирање на објекти во облак.
Суровини изворни податоци: Немодифицирани копии на податоци од изворни системи, со зачувување на оригиналниот контекст.
Трансформирани податоци: Обработени податоци оптимизирани за известување, визуелизација, напредна аналитика и машинско учење.
Аналитички средства: Курирани збирки на податоци, модели и функции подготвени за непосредни деловни сознанија.
Типови на податоци во езерото со податоци
Податочните езера ги опфаќаат сите формати на податоци без дискриминација, дозволувајќи им на организациите да земат вредност од сите информациски средства.
Структурирани податоци: Табели за релациони бази на податоци со дефинирани шеми.
Неструктурирани податоци: Е-пошта, документи, PDF-датотеки, текстуални датотеки на кои им недостасуваат однапред дефинирани модели.
Полуструктурирани податоци: CSV, логови, XML, JSON со флексибилни шеми.
Бинарни податоци: Слики, аудио, видео содржини.
Неограничен размер: Податочните езера можат да складираат секаков вид податоци во неговиот мајчин формат без фиксни ограничувања, зголемувајќи ги аналитичките перформанси и овозможувајќи домашна интеграција.
🌟 Придобивки и принципи на езерата на податоци
Стратешки предности
Примарната цел на езерата со податоци е да понудат ан нерафиниран, сеопфатен преглед на податоците на научниците за податоци и аналитичарите, што доведува до а конкурентна предност.
Нови аналитички способности: Овозможете нови аналитички пристапи користејќи претходно недостапни извори на податоци.
Флексибилност на шемата: Нема потреба од однапред моделирање на податоци во крути шеми на ниво на претпријатие.
Подобрен квалитет: Пристапот до необработени податоци ја подобрува длабочината и точноста на анализите.
Деловна агилност: Брзо прилагодување на променливите деловни прашања и аналитичките потреби.
AI и ML Подготвени: Мајчин поддршка за машинско учење и оптоварување со вештачка интелигенција.
Конкурентна предност: Увидите базирани на податоци создаваат одржливи конкурентни предности.
Унифицирана архитектура: Елиминира силоси на податоци и фрагментирани системи за складирање.
Основни принципи
Прифатете ги сите податоци: Не се одбива внесување на податоци; сите податоци се вчитуваат од различни изворни системи и се задржуваат на неодредено време.
Зачувајте ја суровата состојба: Податоците се складираат во нетрансформирана или минимално трансформирана состојба, точно како што се примени.
Трансформирајте на барање: Податоците се трансформираат и структурираат врз основа на специфични барања за анализа, а не предодредени шеми.
🔑 Суштински концепти во управувањето со езерото со податоци
Ефективното управување со езерото со податоци бара робусни способности во неколку области.
Проголтување податоци: Конекторите повлекуваат податоци од различни извори и ги вчитуваат во езерото со податоци.
Складирање на податоци: Скалабилно, економично складирање со брз пристап.
Управување со податоци: Управување со достапноста, употребливоста, безбедноста и интегритетот на сите податочни средства.
Безбедност: Повеќеслојна заштита имплементирана на секој слој.
Квалитет на податоци: Обезбедување висококвалитетни податоци за генерирање точни деловни сознанија; лошиот квалитет води до лоши одлуки.
Откривање на податоци: Техники за означување и каталогизација за изразување и споделување на разбирањето на податоците.
Ревизија на податоци: Оценување на ризикот, усогласеноста и почитувањето на регулаторните барања.
Линија на податоци: Следење на потеклото на податоците, трансформациите и движењето низ екосистемот.
Истражување на податоци: Почетна фаза на анализа на податоците што овозможува формирање хипотеза.
📊 Магацин на податоци наспроти споредба на езерата на податоци
Организациите обично бараат и складиште на податоци и езеро со податоци, бидејќи тие служат фундаментално различни потреби и случаи на употреба.
Карактеристики на складиште на податоци
Предефинирана структура: Структурата на податоците и шемата се однапред дефинирани (шема-на-пишување).
Чисто и збогатено: Податоците се чистат, збогатуваат и трансформираат пред складирањето.
Единствен извор на вистината: Служи како авторитетен, конзистентен приказ на податоците на претпријатието.
Структуриран фокус: Примарно се справува со релациони податоци со добро дефинирани шеми.
Деловни корисници: Оптимизиран за SQL-базирани извештаи и алатки за деловна интелигенција.
Карактеристики на податочното езеро
Флексибилна структура: Структурата на податоците или шемата не се дефинирани во времето на внесување (шема-на-читање).
Складирање необработени податоци: Податоците се чуваат во својот мајчин формат без претходна трансформација.
Повеќекратни можности: Поддржува многу можни аналитички пристапи за истите податоци.
Различни типови на податоци: Сместува релациски и нерелациски податоци (мобилни апликации, IoT, социјални медиуми).
Научници за податоци: Овозможува напредна аналитика, машинско учење и истражувачка наука за податоци.
Детална споредбена матрица
Карактеристики | Магацин на податоци | Езерото на податоци |
|---|---|---|
Податоци | Релациски од трансакциски системи, оперативни бази на податоци и линија на деловни апликации | Нерелациски и релациски од IoT уреди, веб-страници, мобилни апликации, социјални медиуми и корпоративни апликации |
Шема | Дизајниран пред имплементацијата на DW (шема-на-пишување) | Напишано во времето на анализата (шема-на-читање) |
Цена/Перформанс | Најбрзи резултати на барањето со користење на складирање со повисоки трошоци | Резултатите од барањето стануваат побрзи со користење на евтино складирање |
Квалитет на податоци | Високо курирани податоци кои служат како централна верзија на вистината | Сите податоци што може или не може да се чуваат (т.е. необработени податоци) |
Корисници | Бизнис аналитичари | Научници за податоци, развивачи на податоци и деловни аналитичари (со помош на курирани податоци) |
Анализа | Сериско известување; БИ и визуелизации | Машинско учење, предвидлива аналитика, профилирање за откривање податоци и |
Параметри | Дата езера | Магацин на податоци |
|---|---|---|
Податоци | Податочните езера складираат сè: | Data Warehouse се фокусира само на деловните процеси. |
Обработка | Податоците главно се необработени | Високо обработени податоци. |
Вид на податоци | Може да биде неструктуриран, полуструктуриран и структуриран. | Претежно е во табеларна форма и структура. |
Задача | Споделете чување на податоци | Оптимизиран за пронаоѓање податоци |
Агилност | Високо агилен; конфигурирајте и реконфигурирајте по потреба. | Споредено со Data Lake, тоа е помалку агилно и има фиксна конфигурација. |
Корисници | Data Lake најмногу го користи Data Scientist | Деловните професионалци широко го користат складиштето на податоци |
Складирање | Дизајн на езера на податоци за складирање со ниска цена. | Се користи скапо складирање кое дава брзи времиња на одговор |
Безбедност | Нуди помала контрола. | Овозможува подобра контрола на податоците. |
Замена на EDW | Езерото на податоци може да биде извор за EDW | Дополнително на EDW (не замена) |
Шема | Шема за читање (без претходно дефинирани шеми) | Шема за пишување (предефинирани шеми) |
Обработка на податоци | Помага за брзо внесување на нови податоци. | Одзема време за воведување нова содржина. |
Грануларност на податоците | Податоци на ниско ниво на детали или грануларност. | Податоци на резиме или збирно ниво на детали. |
Алатки | Може да користи отворен код/алатки како Hadoop/ Map Reduce | Претежно комерцијални алатки. |
Одлука за архитектура: Модерните архитектури на податоци често ги имплементираат двата системи во a комплементарна мода, користејќи езера на податоци за складирање и истражување на необработени податоци, додека одржувате складишта на податоци за курирани, аналитички оптоварувања со високи перформанси.
🚫 Езеро на податоци наспроти мочуриште на податоци: избегнување замки
А податочно мочуриште е езеро на податоци кое се расипало во неуправувано складиште, каде што податоците стануваат недостапни или обезбедуваат минимална вредност поради несоодветното управување со податоците.
Ризикот од лошо владеење: Без соодветна каталогизација, контрола на квалитетот и управување со метаподатоци, езерото со податоци може да стане депонија наместо вредно средство.
Практични предизвици за пристапност: Додека езерата со податоци теоретски нудат пристап до сите податоци, тешкотиите во парсирањето на неструктурираните податоци создаваат бариери за генерирање на увид.
Организациите мора да инвестираат во каталогизација на податоци, рамки за управување и управување со квалитетот за да се спречи податочните езера да станат мочуришта.
Управување со метаподатоци: Сеопфатно каталогизирање и означување на сите податочни средства за откривање.
Политики за пристап: Јасни политики за управување кои дефинираат кој до кои податоци може да пристапи.
Контроли за квалитет на податоците: Автоматска валидација и проверки на квалитетот за да се обезбеди веродостојност на податоците.
Управување со животниот циклус: Дефинирани политики за задржување и архивски стратегии за да се спречи неограничена акумулација на податоци.
💎 Архитектура на Lakehouse (медалјон).
На Lakehouse (медалјон) архитектура е хибридна архитектура на податоци која ги комбинира флексибилноста и економичноста на езерата со податоци со робусното управување со податоци и способностите за трансакции со ACID на складиштата на податоци. Ги организира податоците во различни слоеви, прогресивно подобрувајќи го квалитетот на податоците.
Клучни принципи
Слоевит пристап: Организира податоци во три различни слоеви: Бронза (сурова), сребро (рафинирано) и злато (куриран), подобрување на квалитетот на податоците во секоја фаза.
Фондацијата на езерото Делта: Често се гради на езерото Делта, обезбедувајќи ACID трансакции, спроведување на шема, верзии на податоци и патување низ времето.
Бронзен слој (суров): Ги внесува изворните податоци како што се, обезбедувајќи историска непроменливост и целосна следливост.
Сребрен слој (рафиниран): Применува чистење, филтрирање и основни трансформации, често спојувајќи податоци од повеќе бронзени табели за конзистентен поглед.
Златен слој (куриран): Испорачува збирни, денормализирани и високо оптимизирани податоци за одредени апликации за деловна интелигенција, известување и машинско учење.
Прогресивен квалитет на податоци: Квалитетот и доверливоста на податоците се зголемуваат како што податоците се движат низ слоевите.
Унифицирана обработка за пренос и сериски податоци.
Ограничувања
Зголемена сложеност: Повеќе слоеви може да додадат сложеност на цевководите за податоци, особено за помали проекти.
Databricks/Специфично за делта на езерото: Целосната имплементација честопати користи специфични карактеристики на платформата Databricks Lakehouse, што потенцијално води до заклучување на продавачот.
Интензивни ресурси: Складирањето и обработката на податоци во три слоја може да потроши повеќе складирање и пресметување ресурси доколку не се оптимизираат.
🗺️ Архитектура на медалјони: Објаснети зони
Медалјонската архитектура ги категоризира податоците во различни зони, секоја со одредена цел.
Бронза (Сурови податоци): Почетната зона на фаза/слетување каде што сировите, непроменливи податоци се внесуваат како што се, служејќи како историски запис на изворните податоци. Овозможува различни начини на внесување податоци од различни извори.
Сребро (исчистено и рафинирано): доверливата зона каде што податоците се чистат, потврдуваат и трансформираат. Служи како а сигурен единствен извор на вистината за понатамошна обработка. Поддржува паралелно/независно голтање.
Злато (курирано и собрано): оваа зона обезбедува високо курирани, собрани и оптимизирани податоци за одредени апликации за деловна интелигенција, известување и машинско учење. Се одликува со консолидирани податоци и усогласени димензии.
Дополнителни специјализирани зони
Зона на песок: Флексибилна, изолирана средина за научниците за податоци и аналитичарите да експериментираат без да влијаат на производството.
Резервна зона: Посветен на складирање на копии од податоци за враќање од катастрофи, обезбедувајќи континуитет во работењето.
Архивска зона: За долгорочно (ладно) складирање на неактивни историски податоци, од клучно значење за усогласеноста и намалување на трошоците.
🔒 Медалјон: перспектива за заштита на PII
Од перспектива на приватност и безбедност, архитектурата на медалјонот имплементира прогресивна заштита на податоците и информации за лична идентификација (PII) ракување.
Бронза: PII шифрирана: Необработените податоци содржат шифрирани полиња за PII, со што се одржува усогласеноста додека се зачувува алатката за податоци за овластените корисници.
Сребрена: PII маскирани: Чувствителните полиња се маскирани или токенизирани, што овозможува поширок пристап додека ја штити индивидуалната приватност.
Злато: Агрегирани PII: Агрегираните метрики ги елиминираат податоците на индивидуално ниво, овозможувајќи широка дистрибуција без загриженост за приватноста.
Овој повеќеслоен пристап ги поддржува регулаторните барања како GDPR, CCPA и HIPAA.
🏷️ Медалјонски зони - Алтернативни имиња
Слетување | Суровини | Усогласен | Дестилирана | Истражувачки | Испорака | |
|---|---|---|---|---|---|---|
Грануларност (необработена збирка) | Суровини | Суровини | Суровини | Агрегирани | Било кој | Било кој |
Шема (било која консолидирана) | Било кој | Било кој | Консолидирани | Консолидирана, збогатена | Било кој | Било кој |
Синтакса (непроменета консолидирана) | Основни трансформации | Основни трансформации | Консолидирани | Консолидирани | Било кој | Било кој |
Семантика (Непроменето обработено) | Главно непроменет, освен ако не е потребно за усогласеност | Главно непроменет, освен ако не е потребно за усогласеност | Главно непроменет, освен ако не е потребно за усогласеност | Комплексна обработка | Било кој | Било кој |
Својства | Управуван, неисториски, неупорен; заштитен дел; независен употребен случај | Управуван, историски, упорен, заштитен дел; независен употребен случај | Управуван, историски, упорен, заштитен дел; независен употребен случај | Управуван, историизиран, упорен; заштитен дел; употреба зависни од случајот | Неуправен, не-упорен, заштитен дел; употреба зависни од случајот | Управен, упорен, заштитен дел; употреба зависни од случајот |
Кориснички групи | Системи, процеси | Научници за податоци, системи, процеси | Научници за податоци; системи, процеси | Научници за податоци; домен експерти, системи, процеси | Научници за податоци | човечки корисници, системи, процеси Секое |
Пристап за моделирање | Било кој | Било кој | Стандардизирани | Стандардизирани | Било кој | Било кој |
☁️ Архитектура на медалјони со повеќе облаци
А Архитектура на медалјони со повеќе облаци е дизајниран да ги обработува и рафинира податоците низ различни даватели на облак (AWS, Azure, GCP).
Интеграција и синхронизација: користи алатки за агностика на облак или директни врски од облак до облак за да се обезбеди беспрекорен проток на податоци и конзистентност.
Придобивки:
Избегнување на заклучување на продавачот: Флексибилност за избор на најдобри услуги од различни даватели.
Искористување на најдобрите услуги: Користете специјализирани услуги уникатни за секој облак.
Географска дистрибуција: Подобрете ја локацијата на податоците, враќањето при катастрофи и усогласеноста.
Минимизирање на движењето на податоците и усогласување со строгите прописи за престој на податоци.
Одржување на деловниот континуитет.
🏠 Хибриден на-прем пристап
Овој пристап ја проширува архитектурата на медалјонот на средини во просториите, дозволувајќи им на организациите да одржуваат контрола врз нивните податоци.
Медалјонските зони (бронзени, сребрени, златни) може да се имплементираат со користење на различни Технологии за складирање и обработка во просториите.
Опции за складирање: HDFS (Hadoop Distributed File System) и MinIO (складирање објекти компатибилен со S3).
Формати и обработка на табели: Hive, Kudu и Apache Iceberg.
Оваа флексибилност овозможува целосно искористување на постоечката on-prem инфраструктура без да се загрози интегритетот на моделот на медалјонот.
Клучно за обраќање строги барања за суверенитет на податоци и усогласеност.
🧠 Медалјон во системи базирани на RAG
Медалјонската архитектура може да се прилагоди за Враќање на зголемена генерација (RAG) базирани системи, обезбедувајќи структуриран пристап за обработка на податоци за оптимално пребарување на модел на голем јазик (LLM).
Бронзен слој: Внесува необработени, мултимодални податоци (текст, слики, аудио, видео) директно од различни извори, складирани во неговиот оригинален, непроменлив формат.
Сребрен слој: Обработува податоци од бронза, што вклучува чистење, нормализација, делење текст, генерирање вградувања, OCR за слики и транскрипција за аудио. Целта е да се рафинираат податоците во a конзистентен и употреблив формат.
Златен слој: Сместува оптимизирано векторска база на податоци кои содржат индексирани вградувања. Овие рафинирани податоци се подготвени за ефикасно пребарување од LLM, формирајќи висококвалитетна база на знаење за работните текови на RAG.
Овој систематски пристап обезбедува супериорен квалитет на податоците, целосна следливост на лозата на податоците и значително оптимизирани перформанси за пребарување за RAG апликации.
⏳ Сериска наспроти стриминг обработка на податоци
Разбирање на фундаменталните разлики помеѓу серија и стриминг обработка на податоци е од клучно значење за дизајнирање ефикасни и одговорни архитектури на податоци.
Сериска обработка
Процеси голем обем на податоци во закажани интервали.
Податоците се собираат со текот на времето и се обработуваат во парчиња.
Погоден за операции каде латентноста не е критична (на пример, дневни извештаи, месечен платен список).
Оптимизиран за висока пропусност и сложени трансформации.
Пример: Финансиски усогласувања на крајот на денот.
Обработка на стриминг
Обработува податоци континуирано како што пристигнува.
Податоците се обработуваат во мали зголемувања или настани.
Суштински за сценарија кои бараат увиди во реално време и непосредни дејства (на пр., откривање измама, контролни табли во живо).
Оптимизиран за ниска латентност и брза реакција.
Пример: Анализа на податоци од сензори во реално време.
Интегрирање на стриминг и сериски податоци: предизвици
Интегрирањето на двата типа на обработка воведува специфични предизвици:
Голтање во реално време: Фактите и димензиите од апликациите со кои се соочуваат клиентите се трошат речиси во реално време, се трансформираат и се складираат во делта табели.
Сериска обработка: Димензиите кои сè уште не се проследени се внесуваат во серија ETL од различни делови на системот.
Приклучи се на предизвикот: Некои стримувани факти може да упатуваат на копчиња од табела со димензии кои сè уште не постојат, што бара внимателно ракување.
📐 Димензионално моделирање: матрица со автобус
На Автобуска матрица е основна алатка во складирањето на податоци на претпријатијата, која го води развојот на аналитички решенија преку мапирање на деловните процеси до заеднички димензии. Пристапот на Ралф Кимбал нагласува дека димензионалното моделирање е изградено врз деловните процеси, при што Enterprise Data Warehouse е збир на усогласени и интегрирани модели.
🚌 Автобуска матрица: Основа за складирање на податоци на претпријатија
Автобуската матрица е модел на високо ниво на деловните процеси кои се извршуваат дневно, неделно или месечно. Ги наведува деловните процеси на едноставен деловен јазик, објаснувајќи што е секој процес, неговата фреквенција, што може да се мери и кои се клучните играчи.
Ги наведува табели со факти и табели со димензии потребни.
Може да се создаде и разбере со деловните чинители.
Ги складира сите информации што му се потребни на тимот за податоци.
❓ Дефинирајте деловен проблем
Димензионалното моделирање започнува со разбирање на деловниот проблем. Мапите на матрицата на автобусот споделени димензии (колони) против деловни процеси (редови), овозможувајќи зголемување на развојот на средствата за податоци.
Дизајнот одредува факт податоци зрно и потребните димензии за достапни, побрзи прашања.
🚶 Четирите чекори на димензионално моделирање
За димензионално моделирање на бизнис процес, следете ги овие четири чекори:
Идентификувајте го деловниот процес: Дефинирајте ја конкретната деловна активност што треба да се мери и анализира.
Идентификувајте ги димензиите: Дефинирајте ги објектите и контекстот што го опишуваат деловниот процес.
Идентификувајте го житото: Одредете го нивото на детали за секое мерење во процесот.
Идентификувајте ги фактите: Наведете ги мерливите метрики што го квантифицираат деловниот процес.
Овој процес обично трае 1-2 дена по деловен процес, што бара a експерт за предметни прашања и а архитект на податоци со знаење за димензионално моделирање.
🍋 Пример: Среќен штанд за лимонада
Размислете за бизнис со штанд за лимонади со неколку процеси:
Продавам лимонада: Трансакциски процес на продажба на очила ($1 за 33cl) или шишиња ($3 за 1 литар) на клиентите.
Карактеристики на процесот: Трансакциски, жито при индивидуална продажба, не сите клиенти имаат картички за лојалност.
Придобивки од програмата за лојалност: 5 чаши за 4 (наспроти 5 редовни), 2 шишиња за 5,50 (наспроти 6 редовни), бара следење на социјалните мрежи.
Купете состојки: Процес на набавка на лимони и други состојки.
Изработка на лимонада: Процес на производство (50-литарски галон секој викенд, од 10:00 до 14:00 часот).
Изградба на модел на матрица на автобуси
За процесот „Продавање лимонада“:
Чекор 3: Идентификувајте ги димензиите:
Датум и време: Кога продажбата се случила за време на викенд операции (10:00-14:00).
Производ: Стакло (33 cl) или шише (1 литар) со атрибути за волумен и цена.
Програма за лојалност: Изборна димензија (означена со О) за следење на попустот.
Чекор 4: Идентификувајте мерки:
Клучни мерки: продадени единици, цена, волумен на производот и дадени попусти.
Потребни димензии: Датум, време, програма за лојалност и производ.
Резултатот е модел на еден процес: а табела за трансакциски факти со 4 димензионални столбови и 4 мерки. Овој модел може да се тестира со поставување прашања и евалуација на потребите за известување без пишување никаков код.
📝 Модел на матрица на автобуси - Пример за процеси
Деловен процес | Жито | Мерки | Програма за лојалност | Производ | Датум | Време | Серија # | Состојка | Отпад Причина |
|---|---|---|---|---|---|---|---|---|---|
Продавам лимонада | Еден ред за секој продаден производ на клиент на дадена трансакција со датум | Продажни единици, продажна вредност, обем на продажба, вредност на попуст | О | О | О | О | X | X | X |
Изработка на лимонада | Еден ред за секоја направена серија лимонада, по производ | Волумен, единици | X | О | О | О | О | X | X |
Купете состојки | Еден ред за секој купен предмет за правење лимонада | Единици, трошок | X | X | О | X | X | О | X |
Отпад | Еден ред за секоја состојка или производ што се фрла во ѓубре како отпад | Единици | О | О | О | X | X | О | О |
📈 Комплетна автобуска матрица: Возна вредност
Со повторување на 4-те чекори за сите процеси, се создава сеопфатна магистрална матрица, поврзувајќи повеќе табели со факти преку споделени димензии.
Комплексна анализа: Изведете анализа на сите 4 процеси додека табелите со факти се поврзуваат преку споделени димензии.
Оптимизација на процесот: Идентификувајте серии со најмногу отпад и управувајте со нарачките за купување за да ја задоволите очекуваната побарувачка.
Агилен развој: Водете приоретизација и применувајте агилни методологии со деизбирање на делови без да пропуштите важни елементи.
Автобуската матрица овозможува градење на складиште на податоци на претпријатието постепено, обезбедувајќи процес на деловна вредност по процес, истовремено одржувајќи ја интеграцијата и флексибилноста за идниот раст.
🌟 Пристапи за моделирање податоци: Ѕвездена шема
Пристапите за моделирање податоци формираат хиерархиска пирамида, одразувајќи како податоците постепено се рафинираат и оптимизираат за различни цели во рамките на податочната платформа.
Оваа пирамида го илустрира протокот од високо структурирани оперативни податоци, преку слоеви на интеграција, до поедноставени аналитички модели за деловна потрошувачка.
🏛️ База на податоци за трансакции (3NF)
Цел: Управува со секојдневните операции на организацијата (на пр., продажба, инвентар, нарачки).
Тип на податоци: Складира тековни, во реално време и многу детални податоци.
Структура: Оптимизиран за CRUD (Креирај, читање, ажурирање, бришење) операции.
Дизајн: Високо нормализиран за да се минимизира вишокот и да се обезбеди интегритет на податоците (обично Трета нормална форма - 3NF).
Шема за барање: Првенствено се справува со едноставни, брзи прашања за обработка на трансакции.
Изведба: Фокусиран на брзо вметнување податоци и ажурирања наместо на сложени аналитички прашања.
Примери: ERP системи, CRM системи, системи за обработка на нарачки.
🌠 Димензионално моделирање: Ѕвездена шема
На ѕвездена шема е основен дизајн за складишта на податоци, оптимизирајќи ги податоците за аналитичка обработка.
Карактеристики на шема на ѕвезди
Цел: Организира податоци во факти и димензии за аналитичка обработка (OLAP).
Структура: Централна табела со факти поврзани со повеќе табели со димензии, што личи на форма на ѕвезда.
Табела со факти: чува квантитативни податоци (мерки), како што се продажбата, приходите или количините.
Табели со димензии: Содржи описни атрибути (контекст), на пр., клиент, време, производ.
Нормализација: Типично денормализиран за перформанси во превземање и известување податоци, намалувајќи ја потребата за приклучување.
Изведба на барање: Оптимизиран за брзо читање операции и агрегации.
Едноставен дизајн: Лесен за разбирање и барање, погоден за алатки за БИ.
❄️ Димензионално моделирање: Шема на снегулка
На шема на снегулки е понормализирана верзија на ѕвездената шема.
Цел: Понормализирана верзија на ѕвездената шема, која се користи за аналитичка обработка.
Структура: Fact table at the center, surrounded by normalized dimension tables, which may have further sub-dimensions. Ова создава структура на разгранување.
Нормализација: Повисоката нормализација (обично 3NF) го намалува вишокот, но може да ги комплицира барањата поради повеќе спојувања.
Табела со факти: Зачувува квантитативни податоци (на пр., приходи од продажба, продадени единици).
Табели со димензии: Покомплексен и поделен на повеќе поврзани табели.
Изведба на барање: Може да биде побавно од ѕвездената шема поради зголемените спојувања.
Ефикасност на складирање: Поефикасно за складирање поради намалениот вишок.
Пример за употреба: Комплексна аналитика каде што конзистентноста и складирањето на податоците се критични.
📊 Табела со факти
На табела со факти е централната компонента во шемата со ѕвезди или снегулки, која ги складира мерливите деловни метрики.
Цел: Содржи мерливи деловни метрики поврзани со настани (продажби, состаноци итн.).
Тип на податоци: Продавници нумерички податоци, како што се приходи од продажба, број на трансакции или добивка.
Грануларност: Дефинирано според нивото на детали на податоците (на пр., дневно, час).
Клучни колони: Содржи странски клучеви табели за димензии (на пр.
product_id,time_id,customer_id).Мерки: Содржи нумерички факти или збирни мерки (на пр., вкупна продажба).
Изведба: Оптимизиран за брзо собирање и анализа.
Табелата со факти за продажбата содржи странски клучеви за димензии плус нумерички метрики (на пр., приходи, продадени единици) кои ги мерат деловните перформанси.
🌳 Табела со димензии
Табели со димензии обезбеди описен контекст за фактите во складиште на податоци.
Цел: Го опишува контекстот на фактите во складиште на податоци или аналитички модел.
Тип на податоци: Продавници описни, категорични податоци (на пр., имиња на клиенти, категории на производи).
Структура: Содржи атрибути кои ги обезбедуваат „димензиите“ за анализа (на пр., време, локација).
Клучеви: Секоја димензионална табела обично се поврзува со табела со факти преку a странски клуч.
Денормализација: Често се денормализира за побрзо барање и едноставност.
Примери за колони: Име на клиент, тип на производ, регион, временски период.
Случај за употреба: Им овозможува на корисниците да парчиња и коцки факти според различни атрибути (на пр., продажба по регион, производ).
Димензиите производ, продавница, клиент и датум обезбедуваат контекстуални информации, спојувајќи се со фактите за да одговорат на конкретни деловни прашања како месечна популарност на производот или квартални перформанси на продавницата.
🛠️ Чекори на дизајнирање модел на шема на ѕвезди
Дизајнирањето на ефективен модел на шема на ѕвезди вклучува систематски пристап:
Идентификувајте го деловниот процес: Дефинирајте ги клучните деловни активности што треба да се анализираат, вклучувајќи ги релевантните метрики, KPI и почетните димензии.
Изберете Грануларност: Определете го најниското ниво на детали за вашите податоци. Start at the finest grain, considering trade-offs between storage costs and query performance.
Определете ги димензиите: Дизајнирајте табели со димензии со уникатни, не-нулти примарни клучеви. Овие табели даваат описен контекст за фактите.
Консолидирај ги фактите: Чувајте ги квантитативните метрики и табели за мерење, поврзувајќи ги со табели со димензии користејќи странски клучеви.
Изградете шема: Изберете го соодветниот тип на шема (ѕвезда, снегулка или соѕвездие факти) и имплементирајте го, идеално преку инфраструктура како код со автоматизирани тестови.
Оптимизирајте за перформансите на барањето: Индексирајте ја табелата со факти на клучните колони (странски клучеви) за ефикасно барање. Креирајте изведени табели со факти за агрегации на високо ниво.
✅ Придобивките од моделите на Star Schema
Едноставност: Лесен за разбирање и навигација поради неговата јасна, интуитивна структура. Барањата се јасни со минимални спојувања.
Изведба на барање: Оптимизиран за аналитички барања за читање, особено кога се собираат податоци. Денормализираната структура води до побрзи перформанси на барањето и намалена потреба за спојувања.
Поделба на факти и димензии: Лесно се одржуваат и ажурираат фактите и димензиите независно. Димензиите остануваат стабилни со текот на времето, додека фактите може да се променат или растат.
Приспособливост: Лесно може да се прошири бидејќи може да се додадат нови димензии или факти без значителен редизајн. Great for large datasets with many dimensions and measures.
Пристапност на деловни корисници: Едноставната, разбирлива структура е подостапна за деловните аналитичари и не-техничките корисници. Директна поддршка за алатките и контролните табли на Business Intelligence (BI).
❌ Недостатоци на моделите на Ѕвездена шема
Ограничена агилност за промени:
Додавањето нови извори или правењето структурни промени може да биде предизвик.
Промените во деловните барања често бараат редизајн или преструктуирање на шемата.
Промените на грануларноста на табелата со факти бараат повторно вчитување.
Историското полнење (насипување) може да биде предизвик.
Комплексност на одржување:
Управувањето со големи табели со факти со голем број записи може да стане тешко.
Обезбедувањето конзистентност меѓу табелите со факти со различни грануларности може да биде сложено.
Потенцијал за прашања за интегритетот на податоците: Ако табелите со димензии не се одржуваат правилно, може да се појават проблеми со интегритетот на податоците, особено со атрибути кои често се менуваат.
Вишок на податоци: Поради денормализација, табелите со димензии може да содржат непотребни податоци, што ќе доведе до повисоки трошоци за складирање. Ажурирањата на атрибутите на димензија бараат ажурирање на повеќе редови.
Не е идеален за обработка во реално време: Оптимизиран за анализа на историски податоци и може да не поддржува обработка на трансакции во реално време или ажурирања со мала латентност.
Недостаток на флексибилност за сложени односи: Покомплексните врски многу-до-многу може да биде потешко да се моделираат.
Кошмар за одржување: Напорот и трошоците за одржување на информативните маркети може значително да се зголемат со текот на времето.
🌳 Димензионално моделирање: табели со димензии
Табелите со димензии се клучни во димензионалното моделирање, обезбедувајќи описен контекст за фактите во складиштето на податоци. Тие претставуваат субјекти релевантни за деловните и аналитичките барања, како што се производи, луѓе, места, датуми и времиња.
📝 Разбирање на табелите со димензии
Во димензионален модел, а маса за димензии опишува ентитет релевантен за вашиот бизнис и барањата за аналитика. Овие табели обично претставуваат:
Што: Продадени или произведени артикли.
Каде: Локации, региони, продавници.
СЗО: Клиенти, вработени, продавачи.
Кога: датуми и временски печати на настани.
Табелите со димензии често се префикс со d_ или Dim_ за лесна идентификација.
Пример d_Salesperson структура на маса:
CREATE TABLE d_Salesperson (
Salesperson_SK INT NOT NULL, -- Surrogate key
EmployeeID VARCHAR(20) NOT NULL, -- Natural key(s)
FirstName VARCHAR(20) NOT NULL,
-- ... other dimension attributes
SalesRegion_FK INT NOT NULL, -- Foreign key(s) to other dimensions
-- Historical tracking attributes (SCD type 2)
RecChangeDate_FK INT NOT NULL,
RecValidFromKey INT NOT NULL,
RecValidToKey INT NOT NULL,
RecReason VARCHAR(15) NOT NULL,
RecIsCurrent BIT NOT NULL,
-- Audit attributes
AuditMissing BIT NOT NULL,
AuditIsInferred BIT NOT NULL,
AuditCreatedDate DATE NOT NULL,
AuditCreatedBy VARCHAR(15) NOT NULL,
AuditLastModifiedDate DATE NOT NULL,
AuditLastModifiedBy VARCHAR(15) NOT NULL
);🔑 Сурогат клучеви: Фондацијата
А сурогат клуч is a single-column unique identifier, generated and stored in the dimension table. Служи како примарен клуч за поврзување со други табели во димензионалниот модел.
Клучни придобивки
Консолидирај повеќе извори на податоци без судири на идентификаторите.
Претворете ги природните клучеви со повеќе колони во ефикасни копчиња со една колона.
Следете ја историјата на димензиите со SCD тип 2.
Оптимизирајте го складирањето на табелите со факти со најмал можен тип на податоци со цели броеви.
Најдобра практика
Колона за сурогат клуч се препорачува дури и кога природниот клуч изгледа прифатлив. Избегнувајте да им давате значење на клучните вредности, освен копчињата за димензија на датум и време.
Пристапи за генерирање на сурогат клучеви на димензија
Пристап | Добрите | Конс |
|---|---|---|
Хаш на деловниот (природен) клуч (на пр., SHA256 или MD5) | Може да се обработи во дистрибуирана средина; Повторните вчитувања ги генерираат истите вредности; Не бара спојување со димензии за да се идентификува сурогат клучот при вчитување табели со факти | Бара деловниот клуч да е стабилен; Не е толку лесен за користење како клучевите INT |
Генерирајте GUID (Глобално единствен идентификатор) | Може да се обработи во дистрибуирана средина | Потребни се спојувања со димензии за да се вчитаат табели со факти; Не е толку лесен за користење како клучевите INT; Повторното вчитување генерира нови вредности, па ако табела со димензии повторно се вчита, сите табели со факти што ја упатуваат треба да се вчитаат повторно. |
Генерирајте автоматско зголемена вредност (колона за идентитет) | Вредности погодни за корисникот (инженерот); Олеснува поефикасно нарачување со INT што одговара на секвенцата на вчитување | Потребни се спојувања со димензии за да се вчитаат табели со факти; Повторното вчитување генерира нови вредности, па ако табела со димензии повторно се вчита, сите табели со факти што ја упатуваат треба да се вчитаат повторно; Неефикасно да се генерира во дистрибуирани средини |
Внимание: Никогаш не вршете скратување и целосно повторно вчитување на табела со димензии со автоматски генерирани сурогат клучеви ако е референцирана од табели со факти или поддржува промени SCD тип 2.
🗑️ Бришење на членови со димензии
Избегнувајте синхронизирање бришења со табелата со димензии, освен ако членовите не се создадени по грешка и немаат поврзани записи со факти.
Пристап за меко бришење
Означете ги членовите на димензијата како повеќе неактивни или валидни со помош на Булова атрибут (на пр. IsDeleted или IsActive). Ажурирајте ја оваа колона на TRUE (1) за избришани членови.
Следење на тековната верзија
Тековната, најнова верзија на член на димензија може да се означи со Булова (bit) вредност во IsCurrent или IsActive колони.
Филтри за известување
Сите барања за известување и семантичките модели на Power BI треба да ги филтрираат меко избришаните записи за одржување интегритетот на податоците.
🕵️ Ревизорски атрибути
Ревизорски атрибути се опционални, но многу се препорачуваат. Тие следат кога и како се креирани или изменети записите за димензија и може да вклучуваат дијагностички информации од ETL процесите.
Временско следење: Следете кој (и каков процес) ажурирал ред и кога.
Дијагностика: Помогнете да се дијагностицираат предизвикувачки проблеми, како кога процесот на ETL неочекувано запира.
Знамиња за квалитет: Означете ги членовите на димензијата како грешки или заклучените членови за следење на квалитетот на податоците.
📏 Разгледувања за големината на табелата со димензии
Најкорисните и разноврсни димензии се често големи, широки димензии (милиони + редови, стотици атрибути). Големината не е примарна грижа; она што е важно е дека димензијата го поддржува потребното филтрирање, групирање и точна историска анализа.
Големи димензии: Извор од повеќе системи, кои бараат комбинирање, спојување, дедупликација и стандардизирање на податоци при доделување сурогат клучеви.
Мали димензии: Мали табели за пребарување со малку записи и атрибути, кои често ги складираат вредностите на категоријата.
Совет за консолидација: Кога постојат многу мали димензии, размислете да ги консолидирате во a ѓубре димензија да се намали нередот и да се подобри ефикасноста.
⚖️ Денормализација наспроти нормализација
Димензии на снегулка
А димензија на снегулка е нормализирана димензија, складирање податоци низ неколку поврзани табели.
Кога да се користат димензиите на снегулката
Димензијата е исклучително голема, и трошоците за складирање ги надминуваат потребите за изведба на барањето.
Ви требаат клучеви за да ја поврзете димензијата со фактите со повисоко зрно.
Треба да ги следите историските промени на повисоки нивоа на грануларност.
важно: Хиерархија во семантички модел може да се заснова само на колони од една табела. Димензијата на снегулка треба да даде a денормализиран резултат со користење на поглед што ги спојува масите со снегулки заедно.
Дијаграмот прикажува димензија на снегулка која содржи три поврзани табели со димензии: Производ, Поткатегорија и Категорија, секоја со специфични атрибути на ниво на грануларност.
🌲 Разбирање на хиерархиите
Колоните со димензии најчесто произведуваат хиерархии кои овозможуваат истражување на податоците на различни нивоа на сумирање (на пр., намалување од годишна на квартална продажба).
Единечна денормализирана димензија: Колони од една табела што ги чуваат сите нивоа на хиерархија.
Димензии на снегулка: Повеќе поврзани табели кои формираат нивоа на хиерархија.
Однос родител-дете: Самореференциска димензија за неурамнотежени хиерархии.
Хиерархиите можат да бидат избалансиран, неурамнотежен, или парталав.
Балансирани хиерархии
Балансирани хиерархии се најчести, имаат ист број на нивоа во сите гранки.
Вообичаени примери:
Календарска хиерархија: Година $\десно стрелка$ четвртина $\десно стрелка$ Месец $\десно стрелка$ Датум
Географија хиерархија: Земја $\десно стрелка$ држава $\десно стрелка$ Градот
Хиерархија на производи: Категорија $\десно стрелка$ Поткатегорија $\десно стрелка$ Производ
Нивоата се засноваат на колони од една, денормализирана димензија или од табели што формираат димензија на снегулка.
Фактите секогаш се однесуваат на едно ниво, обично најниското, што дозволува фактите да се соберат (навиваат) на највисоко ниво.
Неурамнотежени хиерархии
Неизбалансирани хиерархии се поретки и имаат нивоа засновани на односот родител-дете. Бројот на нивоа се одредува според редовите на димензиите, а не специфичните колони, што значи дека различните гранки можат да имаат различни длабочини.
Вообичаени примери: Хиерархии на вработени (структури за известување), сметка за материјали, модели на сопственост на компанијата, сметки во главната книга.
Препорака: Натурализирајте ја хиерархијата со трансформирање и складирање на нивоата на хиерархија како колони во димензијата.
Парталави хиерархии
Хиерархија е парталав кога родител на член постои на ниво кое не е веднаш над него. Вредностите на нивоата што недостасуваат ја повторуваат вредноста на родителот.
Пример: Во географската хиерархија, Нов Зеланд може да нема држави. Вредноста на земјата („Нов Зеланд“) ќе се повтори во
StateProvinceколона.
⏳ Управување со историските промени
Полека менување на димензиите (SCD) се механизми за управување со тоа како со текот на времето се постапува со промените на податоците за димензиите, зачувувајќи го историскиот контекст.
SCD Тип 1: Презапишете
Акција: Го препишува постојниот ред со димензии, бидејќи нема потреба да ги следите промените. Се користи и за поправање на грешки.
Влијание: Не е зачувана историја. Постојниот ред е ажуриран, што може да резултира со различни агрегации на повисоко ниво за историски податоци.
Пример: Ако некој продавач е назначен во нов регион, историските продажби ќе се наполнат во новиот регион како секогаш да биле назначени таму.
SCD Тип 2: Верзија
Акција: Вметнува нов ред што претставува временска верзија на член на димензија. Секогаш има актуелна верзија.
Клучни барања: Бара a сурогат клуч бидејќи ќе има дупликат природни клучеви. Историски атрибути за следење (на пр.
RecValidFromKey,RecValidToKey,RecIsCurrent) чувајте ги периодите на важност.Влијание: Зачувана е целосна историја. Грануларноста на табелите поврзани со факти е на ниво на верзија на член, што овозможува историските резултати правилно да се соберат, но аналитичарите мора да се движат во повеќе верзии на членови.
Детали за имплементација на SCD тип 2
Ажурирајте ја тековната верзија: Поставете го датумот на крајна важност на датумот на обработка на ETL и тековното знаменце на
FALSE.Вметнете нова верзија: Поставете го датумот на важење на почетокот на претходниот датум на завршување и тековното знаменце на
TRUE.Биланс: Избегнувајте премногу промени на SCD тип 2 на димензија, бидејќи тоа може да ја комплицира анализата. Наместо тоа, размислете за додавање атрибути кои често се менуваат во табелата со факти.
SCD Тип 3: Ограничена историја
Акција: Ја следи ограничената историја со зачувување на претходните и тековните вредности во посебни колони.
Случај за употреба: Корисно кога треба само да го зачувате најновата промена.
Механизам: Го препишува редот со димензија додека ја задржува претходната вредност во посветена колона (на пр.
PreviousSalesRegion).Ограничувања: Не е практично во реалниот живот поради честите промени на шемата потребни за следење на новите колони. Не се користи најчесто во семантички модели.
📅 Димензија на датум
На димензија на датум (или календар). е најчестата димензија, зачувува еден ред по датум и поддржува филтрирање/групирање по точки.
Природен клуч: Треба да го користите
DATEтип на податоци.Сурогат клуч: Треба да го зачувате датумот во YYYYMMDD формат користејќи го
INTтип на податоци, што го прави читлив за луѓе, ефикасен и усогласен со ISO 8601.Атрибути: Вклучува колони за година, квартал, месец, ден, седмица од годината, IsHoliday, HolidayText, IsWeekday, LastDayOfMonth и релативни поместувања (на пр.
RelativeMonthOffset).Население: Generated beforehand (e.g., 100 years * 365 = 36,500 rows) and rarely modified. Треба да вклучи идни датуми за цели или прогнози.
⏰ Временска димензија
Кога фактите бараат анализа во одреден момент во времето, посветено временска (или часовник) димензија е од суштинско значење.
Грануларност: Може да се движи од минути (1.440 редови) до секунди (86.400 редови).
Природен клуч: Треба да го користите
TIMEтип на податоци.Сурогат клуч: Размислете за човек читлив
INTформат какоHHMMилиHHMMSS.Атрибути: Вклучува час, половина час, четврт час, минута, етикети за временски период (наутро, попладне), имиња на работни смени и знаменца за врв/не шпиц.
важно: Димензијата на датум треба не вклучуваат зрно што се протега до време од денот. Користете и димензија на датум и посебна временска димензија.
🤝 Усогласени димензии
Усогласени димензии се споделуваат на повеќе табели со факти, обезбедувајќи конзистентност и намалувајќи го напорот за развој.
Вообичаени примери: Димензии на датумот (се користат во скоро сите табели со факти) и димензии на производот.
Придобивка: Обезбедува конзистентна анализа на податоци во различни деловни области.
Преклопување на ентитети: Кога ентитетите се преклопуваат (на пр., вработени кои се исто така корисници на системот), комбинирајте ги нивните атрибути во една усогласена димензија.
🌌 Соѕвездија на факти и шеми на галаксијата
Шема на констелација на факти
А шема на соѕвездија се состои од збир на шеми со ѕвезди со хиерархиски поврзани табели со факти.
Врските помеѓу различни табели со факти обезбедуваат можност за „дупчење“ помеѓу нивоата на детали (на пр., од продажба до ставка за продажба).
Факт Галакси Шема
Поопшто, збир на шеми на ѕвезди или соѕвездија може да се комбинираат за да формираат a галаксија.
Галаксија е збирка шеми на ѕвезди со заеднички димензии.
За разлика од шемата на соѕвездието, табелите со факти во галаксијата навистина го прават не треба да бидат директно поврзани.
🎭 Димензии на играње улоги
А димензија на играње улоги се случува кога димензијата се референцира повеќе пати во табела со факти, при што секоја референца претставува посебна улога.
Пример: Табела со факти за продажба може да има
OrderDate_Date_FK,ShipDate_Date_FK, иDeliveryDate_Date_FKклучеви, сите се однесуваат на истата физичка димензија на датум.Друг пример: Аеродромска димензија поврзана двапати со табела со факти за лет како
Departure AirportиArrival Airport.
🗑️ Несакани димензии
А ѓубре димензија консолидира многу мали димензии во една димензија. It is useful when there are many independent dimensions with few attributes and low cardinality.
Придобивки:
Намалете го бројот на димензии.
Намалете ги копчињата за табели со факти и големината на складирањето.
Намалете го нередот на окното за податоци.
Добри кандидати: Знамиња и индикатори, статус на нарачка, демографски состојби на клиентите (пол, возрасна група).
Табелата за димензија на ѓубре обично ги складира Декартов производ од сите вредности на атрибутот на димензијата со сурогат клуч.
👻 Дегенерирани димензии
А дегенерирана димензија occurs when the dimension is at the same grain as the related facts. Вообичаен пример е димензијата на бројот на нарачки за продажба која се однесува на табела со факти за продажба.
Typically, the invoice number is a single, non-hierarchical attribute in the fact table. Прифатена практика е да не се копираат овие податоци за да се создаде посебна табела со димензии.
Можете да креирате а поглед во магацин за ткаенини што ја прикажува дегенерираната димензија како димензија за цели на барање.
⛵ Димензии на аутригер
Кога табела со димензии се однесува на други табели со димензии, таа е позната како an димензија на потпора. Димензијата на потпора помага да се усогласат и повторно да се користат дефинициите во димензионалниот модел.
Пример: Географска димензија што ги складира географските локации за секој поштенски код, референцирани и според димензиите на купувачот и на продавачот. Ова обезбедува конзистентни географски локации за анализа.
Димензијата на датумот може да се користи како димензија на подигање кога другата табела со димензии ги атрибуира датумите на складирање (на пр., датум на раѓање во димензија на клиент користејќи го сурогат клучот на димензијата на датум).
↔️ Повеќевредни димензии
Кога атрибутот за димензија мора да складира повеќе вредности, имплементирате a повеќевредносна димензија користејќи а табела за мост (или спојна маса) за складирање на односот многу-на-многу помеѓу ентитетите.
Пример: Ако продавачите може да се доделат во повеќе продажни региони, табела за мост ја складира секоја врска продавач-регион, создавајќи логичен однос многу-на-многу.
Практичен пристап: Имајте директни странски клучеви (FK) од табелата со факти до сите комбинации кои произлегуваат од врската многу-на-многу (на пр., FKs и кон клиентот и кон сметката).
🎯 Димензии: Клучни производи за носење
Принципи за дизајн
Користете сурогат клучеви за сите димензии.
Денормализирај за перформанси и употребливост.
Вклучи ревизорски атрибути за следење.
Балансирајте ги потребите за историско следење со сложеноста.
Заеднички обрасци
Спроведување димензии на датумот и времето за временска анализа.
Користете усогласени димензии за конзистентност.
Пријавете се Видови SCD врз основа на деловните барања.
Левериџ димензии на играње улоги кога е соодветно.
Посебни размислувања
Консолидирајте мали димензии во ѓубре димензии.
Натурализирај неизбалансирани хиерархии кога е можно.
Користете мостови маси за многу-до-многу врски.
Креирај ставови за дегенерирани димензии.
🏷️ Димензионално моделирање: Класификација на ентитети
Класификацијата на ентитетите е првиот чекор во конвертирањето на моделот за односи со ентитетот (ER) во димензионален модел за аналитика и известување. Целта е да се разбере улогата што секој ентитет ја игра во анализата и да се подготви за градење табели за факти и димензии во шема со ѕвезди.
📈 Чекор 1: Преглед на класифицирани ентитети
Оваа почетна фаза помага да се трансформираат сложените оперативни модели во поедноставени аналитички модели.
Ентитетите се класифицирани во три различни категории врз основа на тоа како тие ја претставуваат деловната активност и контекстот: Трансакции, компоненти и ентитети за класификација.
Зошто е важна класификацијата
Идентификувајте деловни настани: Помага прецизно да се одреди кои деловни настани сакаат да ги анализираат и следат носителите на одлуки.
Структурни шеми на ѕвезди: Обезбедува јасна структура за организирање табели со факти и табели со димензии.
Поедноставување на модели: Ги поедноставува сложените оперативни модели за аналитичка употреба додека го зачувува суштинското деловно значење.
💰 Трансакциски субјекти
Трансакциски субјекти снимајте детали за конкретни деловни настани што се случуваат во одреден момент. Овие настани обично се примарен фокус на анализата и претставуваат мерливи активности.
Клучни карактеристики
Опишете настан што се случува на а конкретен момент во времето.
Содржи мерења или количини (на пр., износи во долари, тежини, волумени, брои).
Повеќето многу-до-многу врски претставуваат трансакциски субјекти.
Улога во димензионално моделирање
Трансакциски субјекти се најважно во складиште за податоци.
Тие ја формираат основата за изградба табели со факти во шеми на ѕвезди.
Потребен е внес на корисникот за да се идентификуваат кои трансакции се релевантни за аналитичките барања.
Вообичаени примери
Нарачки | Побарувања од осигурување |
|---|---|
Трансакции за купување од купувачи со ставки и збирки | Поднесени барања со износи и детали за обработка |
Исплати на плата | Резервации за хотели |
Трансакции на платен список со износи и датуми на плаќање | Настани за резервации со цени и времетраење на престојот |
Присуство | Најавете се |
Влегување во соба | Секој настан за најавување и одјавување во систем |
IoT мерења | Обид за испит |
Мерење на одредена вредност со сензор во кој било момент од времето | Секој обид за испит, дури и неуспешен, е настан |
🧩 Ентитети на компоненти
Компонентни ентитети се директно поврзани со трансакциските субјекти преку односи еден-на-многу. Тие ги опишуваат поединечните компоненти или димензии кои го дефинираат секој деловен настан, обезбедувајќи суштински контекст.
Улога во димензионално моделирање
Компонентните ентитети стануваат табели со димензии во шеми на ѕвезди.
Тие го обезбедуваат деловниот контекст за сечење, коцки и филтрирање на податоци за факти.
Тие одговараат на основните аналитички прашања: кој, што, кога, каде, како и зошто за секоја трансакција.
Клиент | Производ |
|---|---|
Кој го направил купувањето или учествувал на настанот | Што е продадено или за кој предмет станува збор |
Локација | Период |
Каде се случила или била обработена трансакцијата | Кога трансакцијата се случила навреме |
Важна забелешка: Времето е критична компонента ентитет бидејќи историската анализа е суштинско барање на складиштата на податоци. Секоја табела со факти треба да вклучува временски димензии за анализа на трендовите.
🏷️ Класификација субјекти
Класификација субјекти се поврзани со составни ентитети преку синџири на односи еден-на-многу. Тие се функционално зависни од составните ентитети, кои претставуваат хиерархиски структури.
Цел
Претставуваат хиерархии вградени во моделот на податоци.
Снимајте класификација и групирање структури како што се категории и нивоа.
Овозможи дупчење и ролна анализа патеки.
Ако податоците се погодни за паѓачкото мени со ниска кардиналност во корисничкиот интерфејс, тоа е веројатно ентитет на класификација.
Улога во димензионално моделирање
Колапс хиерархии: Ентитетите на класификацијата може да се склопат во составни ентитети кога се креираат табели со димензии.
Анализа за поддршка: Тие поддржуваат хиерархиска анализа, како што се собирање и вежбање на нивоа.
Зачувај го значењето: Тие го поедноставуваат аналитичкиот модел додека го зачувуваат деловното значење и односите.
🧐 Решавање на нејаснотии
Кога еден ентитет се вклопува во повеќе категории, а хиерархија на првенство се користи:
Трансакциски ентитет (највисока предност)
Класификација субјект
Компонентен ентитет (најниско првенство)
Оваа хиерархија ја одредува примарната улога на ентитетот во ѕвездената шема.
🌲 Хиерархии
Хиерархии се клучни во димензионалното моделирање, формирајќи ја примарна основа за изведување на димензионални модели од ER модели. Повеќето табели со димензии ги вградуваат овие хиерархии. Хиерархија на ER модел е низа од ентитети споени со врски еден-на-многу, порамнети во иста насока.
Родител/Дете: Државата е родител на Регионот; Регионот е дете на државата.
Потомци: Продажен артикл, Продажба, Локација за продажба, Регион се сите потомци на државата.
Предци: Продажба, Локација за продажба, Регион, држава се сите предци на артиклите за продажба.
Максимална хиерархија
Хиерархија е максимално ако не може да се прошири понатаму нагоре или надолу, опфаќајќи го целосниот опсег на деловниот однос.
Минимален ентитет
Ентитет е минимална ако се наоѓа на самото дно на максималната хиерархија, без дополнителни врски еден-на-многу кои се насочени подалеку од него (ентитети „лист“).
Максимален ентитет
Ентитет е максимално ако е позициониран на врвот на максималната хиерархија, што значи дека нема дополнителни врски многу-на-еден што водат до него (ентитети „корен“).
Пример за хиерархии на ентитети
Од примерен модел на податоци, можеме да идентификуваме:
Минимални ентитети: Продажен артикл и продажен надоместок (2 субјекти).
Максимални ентитети: Период, Тип на клиент, држава, Тип на локација, Тип на производ и Вид на надоместок (6 субјекти).
Овие хиерархии дефинираат детални односи и лоза, обезбедувајќи структуриран поглед за анализа.
Хиерархии на производи: Тип на производ $\десно стрелка$ Производ $\десно стрелка$ Продажба-артикал; Тип на надоместок $\десно стрелка$ Продажба-надоместок.
Хиерархии на клиенти: Држава $\десно стрелка$ Регионот $\десно стрелка$ Клиент $\десно стрелка$ Продажба $\десно стрелка$ Продажба-надоместок (и Продажба-артикал); Клиент-тип $\десно стрелка$ Клиент $\десно стрелка$ Продажба $\десно стрелка$ Продажба-надоместок (и Продажба-артикал).
Хиерархии на локација: Локација-тип $\десно стрелка$ Локација $\десно стрелка$ Продажба $\десно стрелка$ Продажба-надоместок (и Продажба-артикал); држава $\десно стрелка$ Регионот $\десно стрелка$ Локација $\десно стрелка$ Продажба $\десно стрелка$ Продажба-надоместок (и Продажба-артикал).
Временски хиерархии: Период (датум на продажба) $\десно стрелка$ Продажба $\десно стрелка$ Продажба-надоместок (и Продажба-артикал); Период (објавен-датум) $\десно стрелка$ Продажба $\десно стрелка$ Продажба-надоместок (и Продажба-артикал).
🔨 Производство на димензионални модели
Димензионалното моделирање вработува специјализирани оператори за да ги трансформираат ER моделите во шеми со ѕвезди, оптимизирајќи ги податоците за аналитички прашања.
Оператор 1: Колапс хиерархија
Овој основен оператор ги консолидира атрибутите од ентитет на повисоко ниво во ентитет од пониско ниво во хиерархија еден-на-многу.
Процес: Намерен чин на денормализација, воведувајќи вишок за да се создадат порамни, попрофесионални табели со димензии.
Пример: Атрибутите на ентитет „Држава“ (Државна ИД, Име на државата) се апсорбираат во ентитетот „Регион“, создавајќи единствена, поширока димензија.
Оператор 2: Агрегација
На оператор за агрегација се применува на трансакциски ентитет за да се создаде нов ентитет кој содржи сумирани податоци.
Процес: Вклучува избор на подмножество од нумерички атрибути да се соберат и уште една подмножество на атрибути да се групираат по.
Пример: Примена на агрегација на a
Sale Itemентитет да се создаде аProduct Summaryентитет (на пр., вкупен износ на продажба, просечна количина по нарачка, просечна цена по ставка на дневна основа).Агрегација атрибути: Количина, Цена.
Групирање на атрибути: ИД на производот, Датум на продажба.
важно: Агрегирањето инхерентно вклучува а губење на детални информации. Откако ќе се сумираат податоците, не е можно да се реконструираат поединечни детали.
Последица: Во ETL, една табела со факти обично се чува на најниска грануларност, а потоа се креираат други збирни табели со факти.
📊 Димензионално моделирање: табели со факти
Табелите со факти се јадрото на димензионалниот модел, складирање на мерливи деловни метрики и поврзување со табели со димензии за контекст. Тие се вообичаено тесни (неколку колони), но огромни во однос на редовите (милијарди +).
📝 Структура на табела со факти
Размислете за табела со факти за продажбата именувана f_Sales како пример за добри дизајн практики:
CREATE TABLE f_Sales (
-- Dimension keys
OrderDate_Date_FK INT NOT NULL,
ShipDate_Date_FK INT NOT NULL,
Product_FK INT NOT NULL,
Salesperson_FK INT NOT NULL,
-- Attributes
SalesOrderNo INT NOT NULL,
SalesOrderLineNo SMALLINT NOT NULL,
-- Measures
Quantity INT NOT NULL,
-- Audit attributes
AuditMissing BIT NOT NULL,
AuditCreatedDate DATE NOT NULL,
AuditCreatedBy VARCHAR(15) NOT NULL,
AuditLastModifiedDate DATE NOT NULL,
AuditLastModifiedBy VARCHAR(15) NOT NULL
);Примарен клуч и клучеви за димензии
Примарен клуч: Табели со факти обично немаат експлицитен примарен клуч. Честопати не служи за корисна цел и непотребно ја зголемува големината на складирањето. Примарниот клуч често се подразбира од множеството клучеви за димензија и атрибути.
Клучеви за димензии: Референци за сурогат клучеви (или атрибути на повисоко ниво) во сродни димензии. Табелата за необични факти нема да вклучува барем еден клуч за димензија на датум.
Димензија на играње улоги: Табелата со факти може да упатува на димензија повеќе пати. На пример,
OrderDate_Date_FKиShipDate_Date_FKи двете се однесуваат на истата физичка димензија на датумот, секој од нив игра посебна улога.
Атрибути, мерки и карактеристики на ревизија
Атрибути: Обезбедете дополнителни информации и поставете ја грануларноста на податоците за фактите, но не се ниту клучеви за димензија, ниту атрибути за димензија, ниту мерки. Примерите вклучуваат броеви на нарачки за продажба или броеви за следење. Еден атрибут може да формира а дегенерирана димензија за анализа.
Мерки: Типично нумерички и вообичаено адитив (може да се сумира и сумира). На
Quantityколоната во примерот е мерка.Ревизорски карактеристики: Изборни атрибути кои следат кога и како се креирани или модифицирани записите со факти и може да вклучуваат дијагностички информации од ETL процесите.
📏 Концепти за големина и дизајн на табела со факти
Табелите со факти се разликуваат по големина врз основа на димензионалноста, грануларноста, бројот на мерки и количината на историја. Тие се типично тесен (помалку колони) но голема или огромна во однос на редови (што надминува милијарди).
Три типа на табели со факти
Табели со факти за трансакции
Периодични табели со факти
Акумулира табели со факти за слики
Видови мерки
Мерките се типично нумерички и најчесто адитивни. Сепак, некои мерки се полу-адитив или не-адитив.
Адитивни мерки: Може да се сумира во која било димензија (на пр., количина на нарачка, приход од продажба во една валута).
Полуадитивни мерки: Може да се сумира само преку одредени димензии.
Секоја мерка во табела со факти за периодични слики не може да се сумира низ други временски периоди.
Мерката за билансот на акции во табела со факти за залихи не може да се сумира кај други производи.
Приходите од продажба со клуч за димензија на валута не може да се сумираат низ валути.
Мерки без додавање: Не може да се сумира низ која било димензија (на пр., отчитување на температурата, стапки, соодноси). Тие сè уште може да се соберат со користење на броење, различно броење, минимално, максимум или просек.
🛒 Табели со факти за трансакции
А табела со факти за трансакцијата складира деловни настани или трансакции. Секој ред ги складира фактите во однос на копчињата и мерките за димензија, како и опционално други атрибути. Сите податоци се целосно познати кога се вметнуваат и никогаш не се менуваат (освен за корекција на грешки).
Обично складирајте факти на најниско можно ниво на грануларност.
Содржи мерки кои се адитив низ сите димензии.
Пример: Табела со факти за продажба која ја складира секоја линија за нарачки за продажба.
fact_saleја има грануларноста на најгрануларна табела во изворната база на податоци (на пр.sales_order_details,sales_items).
📸 Периодични табели со факти
А периодична табела со факти складира мерења во однапред дефинирано време или специфични интервали. Обезбедува резиме на клучните метрики со текот на времето и е корисен за анализа на трендовите. Мерките се секогаш полу-адитив.
Пример: Табела со факти за залихи која секојдневно се вчитува со билансот на залихите на крајот на денот на секој производ.
Случај за употреба: Се користи при снимање на големи количини на трансакции е скапо и не поддржува корисни аналитички барања, фокусирајќи се на трендовите наместо на индивидуалните трансакции.
🔄 Акумулира табели со факти за слики
Ан акумулирачка табела со факти за слика складира мерења кои се акумулираат во добро дефиниран период или работен тек. Често ја евидентира состојбата на деловниот процес во различни фази или пресвртници, за кои може да бидат потребни денови, недели или месеци за да се завршат.
Почетен настан: Редот со факти се вчитува веднаш по првиот настан во процесот.
Ажурирања за пресвртница: Редот се ажурира во предвидлива секвенца секогаш кога ќе се случи настан од пресвртница.
Завршување на процесот: Ажурирањата продолжуваат додека не заврши процесот.
Имаат повеќекратни копчиња за димензија на датум, секој од нив претставува настан од пресвртница.
Некои копчиња за димензии може да снимаат
N/Aсостојба додека не се постигне одредена пресвртница.Мерките обично се снимаат времетраење помеѓу пресвртниците, обезбедувајќи вреден увид во деловните работни текови.
👻 Табели со факти без факти
А табела со факти без факти не содржи никакви колони со мерки. Обично снима настани или појави (на пр. студенти кои посетуваат настава). Од аналитичка перспектива, мерењето може да се постигне со броење на редови со факти.
Цел: Снима појава на настан или состојба без нумерички мерки.
Примери: "клиентот го видел производот во продавницата на датум", "клиентот кој ги исполнува условите за промоција во продавницата на датум", "клиент-производ-продавницата имала најмалку една допирна точка".
| dim_customer | | fact_customer_product_store_event | | dim_store | |
|----------------|--------------|-------------------------------------|--------------|-----------------|--------------|
| customer_skey | int | customer_skey | int | store_skey | int |
| customer_code | varchar(50) | product_skey | int | store_code | varchar(50) |
| full_name | varchar(200) | store_skey | int | store_name | varchar(200) |
| segment | varchar(50) | event_type_skey | int | city | varchar(100) |
| | | event_ts | datetime | region | varchar(100) |
| | | source system | varchar(50) | | |
| dim_product | | source_event_id | varchar(100) | dim_event_type | |
| product_skey | int | | | event_type_skey | int |
| sku | varchar(50) | | | event_type_code | varchar(50) |
| product_name | varchar(200) | | | event_type_name | varchar(200) |
| category | varchar(100) | | | event_group | varchar(100) |
| brand | varchar(100) | | | is_active | boolean |🌌 Шема на соѕвездија на факти и шема за галаксија на факти
Шема на констелација на факти
Збирната табела со факти претставува збир на основна табела со факти до помала димензионалност и/или поголема грануларност. Неговата цел е да ги забрза перформансите на барањето за димензиите кои најчесто се прашуваат.
Се состои од збир на шеми со ѕвезди со хиерархиски поврзани табели со факти.
Врските помеѓу табелите со факти обезбедуваат можност за дупчат помеѓу нивоата на детали (на пр., од Продажба до артикал за продажба).
Факт Галакси Шема
Поопшто, збир на шеми на ѕвезди или соѕвездија може да се комбинираат за да формираат a галаксија.
Галаксија е збирка шеми на ѕвезди со споделени димензии.
За разлика од шемата за соѕвездија, табелите на фактите во галаксијата не треба да бидат директно поврзани.
📥 Димензионално моделирање: Димензии и факти за вчитување
Вчитувањето на димензионален модел вклучува периодично водење на Процесот на екстракција, трансформација и вчитување (ETL).. Овој процес го оркестрира поставувањето на изворните податоци, синхронизацијата на податоците за димензиите, вметнувањето редови во табелите со факти и евидентирањето на ревизорските податоци и грешки.
Цевководи: Изградете работни текови за оркестрирање на ETL процесот, извршување на SQL скрипти, складирани процедури итн.
Текови на податоци: Развијте логика со низок код за да внесувате податоци од стотици извори користејќи го искуството со Power Query.
Развој на ETL: Може да биде комплексен и предизвикувачки, често троши 60-80% од напорите за развој на складиште за податоци.
⚙️ Оркестрација
Општиот работен тек на ETL процес следи структурирана низа:
01 | 02 |
|---|---|
Вчитај табели за поставување на сцена | Табели со димензии на процесот |
Изборно извлечете и исценирајте ги изворните податоци за обработка | Синхронизирајте ги податоците за димензијата и справувајте се со историските промени |
03 | 04 |
Табели со факти за процесот | Задачи по обработката |
Вчитајте податоци за фактите со соодветно пребарување на копчињата за димензија | Активирајте освежувања и испраќајте известувања |
Табели со димензии прво треба да се обработи, особено кога има зависности (на пр., димензијата за подигање како географијата мора да се обработи пред димензиите на купувачот и продавачот).
Табели со факти може да се обработи откако ќе се завршат сите табели со димензии.
Пост-обработката вклучува активирање на освежување на семантичките модели и испраќање известувања за исходот на ETL.
стационарни податоци 🚧
Станирањето на податоците е посреднички чекор во процесот на ETL, каде суровините изворни податоци привремено се складираат пред трансформацијата и вчитувањето во складиштето на податоци.
Алтернативи за виртуелизација на податоци
Размислете за виртуелизација на податоците за да го намалите движењето на податоците и да ја подобрите ефикасноста:
Пресликување: Ефтино решение со мала латентност што создава реплика на вашите податоци во OneLake.
PolyBase во SQL Server: Дозволува T-SQL барањата да ги спојат податоците од надворешни извори во релациони табели.
Кратенки на OneLake: Посочете кон други локации за складирање што содржат изворни податоци, што може да се користат како табели во T-SQL пребарувања.
Управуван примерок на Azure SQL: Извршете T-SQL барања за датотеки во Azure Data Lake Storage Gen2 или Azure Blob Storage и комбинирајте со локално складирани релациски податоци.
🔄 Трансформирајте податоци
Структурата на изворните податоци често се разликува од дестинационите структури на табелите со димензионални модели. Процесот на ETL мора да ги преобликува и трансформира изворните податоци за да обезбеди квалитет и конзистентност.
Важи „Garbage in, garbage out“: избегнувајте вчитување податоци со низок квалитет во табели со димензионални модели.
Клучни трансформациски активности
Комбинирајте податоци: Интегрирајте податоци од различни извори врз основа на соодветни клучеви или додајте податоци што споделуваат заедничка структура (на пр., спојување на податоците за производот со користење на SKU, обединување на податоци за продажба).
Откријте историска промена: Откријте и соодветно складирајте ги промените во табелите со димензии за да ја одржите историската точност (типови SCD).
Конвертирај типови на податоци: Конвертирајте ги типовите на податоци за да се усогласат со табелите со димензионални модели.
Збирни податоци: Намалете ја димензионалноста на табелата со факти или подигнете ја грануларноста на фактите (на пр., групирање на продажбата по клучеви за димензии без складирање на броеви на нарачки за продажба).
Пресметки: Направете вредности за табелите со димензионални модели (на пр., спојте ги имињата и презимето за целото име, пресметајте ги бруто приходите од продажба).
⬆️ Вчитај податоци
Достапни се различни опции за внесување податоци за вчитување табели во складиште за ткаенини, секоја погодна за различни сценарија.
COPY INTO(T-SQL): Корисно за паркет или CSV датотеки складирани во надворешни сметки за складирање на Azure (ADLS Gen2, складирање на Azure Blob).Текови на податоци: Обезбедете искуство без код за трансформирање и чистење на податоците со интуитивен интерфејс.
Цевководи: Може да вклучува активности што извршуваат T-SQL изјави, вршат пребарување или копирање податоци од извор до дестинација.
Вкрстено-магацинско голтање: Кога податоците се зачувани во истиот работен простор, овозможува спојување на различни магацински или езерски табели. Поддржува
INSERT...SELECT,SELECT INTO, иCREATE TABLE AS SELECT(CTAS) команди за ефикасни операции базирани на множества.
📝 Сеча
ETL процесите бараат посветен мониторинг и одржување. Се препорачува евидентирање на резултатите од процесот ETL во табели со недимензионални модели во вашиот магацин.
ETL Процес логирање
Единствен ID за секое извршување.
Време на почеток и крај.
Статус (успех или неуспех).
Сите наидени грешки.
Евиденција на ниво на табела
Време на почеток и крај.
Индикатори за статус.
Редовите се вметнати, ажурирани, избришани.
Конечно броење на редови на табелата.
Детали за грешка.
Семантички модел посветен на следење на процесите на ETL може да помогне да се идентификуваат тесните грла и да се предвиди идната големина на складиштето на податоци.
Операција логирање
Време на почеток и крај на операциите за освежување на семантичкиот модел.
Метрика на перформанси.
Искористување на ресурсите.
🌳 Табели со димензии на процесот
Обработката на табелата со димензии вклучува синхронизирање на податоците од складиштето на податоци со изворните системи. Изворните податоци се трансформираат, подготвуваат и потоа се совпаѓаат со постоечките податоци од табелата со димензии со приклучување деловни клучеви.
Нови производи: Редовите се вметнуваат во табелата со димензии.
Изменети производи: Постојните редови или се ажурираат (SCD тип 1) или се вметнува нова верзија и постоечката е истечена (SCD тип 2).
Историско следење: SCD тип 2 истекува на тековните верзии и вметнува нови верзии.
📊 Табели со факти за процесот
Обработката на табела со факти вклучува синхронизирање на податоците со фактите на изворниот систем. Изворните податоци се трансформираат и за секој клуч за димензија, пребарувањето ја одредува сурогатната вредност на клучот што треба да се складира во редот со факти.
Пребарувања на клучните димензии
Кога димензија поддржува SCD тип 2, сурогат клучот за тековна верзија на членот на димензијата треба да се извади.
За димензиите на датумот и времето, сурогатниот клуч често може да се пресмета (на пр., YYYYMMDD или HHMM формат).
Проверка на интегритет: Со оглед на тоа што многу DW системи не спроведуваат странски клучеви, процесот ETL мора да провери дали има интегритет при вчитување на табели со факти.
Ракување со неуспеси во пребарувањето
Ако пребарувањето на клучот за димензија не успее (што укажува на проблем со интегритетот на изворниот систем), редот со факти сепак мора да се вметне со важечки клуч за димензија.
Пристап 1: Чувајте посебен член на димензија како
Unknown.Пристап 2: Ако природниот клуч е валиден, вметнете нов член за димензија и зачувајте го неговиот сурогат клуч.
Инкрементално наспроти целосно повторно вчитување
Секогаш кога е можно, треба да се вчита табела со факти постепено (откриени и вметнати нови факти). Ова е поскалабилно и го намалува обемот на работа.
Скратувањето и повторното вчитување на голема табела со факти треба да биде a последно средство поради трошоците, ресурсите и сложеноста со димензиите на SCD тип 2.
Откривање на нови факти
Изворни системски идентификатори/временски печати: Потпрете се на нив за ефикасно откривање нови факти (на пр., зачувување на најновиот број на нарачка за продажба како висок воден печат).
Промена на снимање на податоци (CDC): SQL Server и Azure SQL Managed Instance нудат CDC за следење на промените во редовите.
Креирајте колони за датум: Може со сигурност да открие нови нарачки.
Активатори на база на податоци: Додајте предизвикувачи на релациони табели кои складираат клучеви од вметнати, ажурирани или избришани записи за ефикасно откривање промени.
Заклучени членови на димензија
Кога процесот на оптоварување факти вметнува нов член на димензија, тој е познат како an заклучен член.
Почетно создавање: Познат е само природниот клуч; процесот на оптоварување факти создава нова димензија член со
Unknownвредности на атрибутите.Детали за доцна пристигнување: Кога деталите ќе пристигнат подоцна, процесот на вчитување на димензијата го ажурира редот со димензии.
Означете како заклучено: Поставете атрибут за ревизија како
IsInferredMemberдоTRUE.
✏️ Ажурирања или бришења на факти
Можеби ќе треба да ги ажурирате или избришете податоците за факти (на пр., откажани нарачки за продажба, променети количини на нарачки).
Стратегија за меки бришења: за откажани нарачки, променете го статусот на нарачката за продажба (на пр., од
OpenдоCanceled) наместо да го избришете редот. Ова ја зачувува историјата.Атрибути за идентификација: Вклучете атрибути како број на нарачка за продажба и број на линија во табелата со факти за да помогнете да се идентификуваат редовите за измена. Индексирајте ги овие колони за ефикасни операции.
Ажурирања за количина: За промени во количината, неопходно е ажурирање на мерката количина на редот факти.
Периодична обработка: Ако податоците за фактите се вметнати со помош на
Unknownчлен на димензија, извршете периодичен процес за враќање на тековните изворни податоци и ажурирање на клучевите за димензија до валидни вредности.
Сите барања за известување и семантичките модели на Power BI треба да ги филтрираат меко избришаните записи за да обезбедат точно известување.
➡️ Линија на цевководи
Трансформации, димензии на вчитување, факти и изведени факти
Овој дијаграм го илустрира протокот на податоци низ цевковод, од сурови (бронзени) до рафинирани (сребрени) табели, потоа во димензија (dim) табели, и конечно во факт (fact) табели за анализа. Овој процес ги трансформира и вчитува податоците низ различни фази, подготвувајќи ги за известување и аналитика.