Data Relationships, Data Modelling Schemas, and Joins in Power BI

Power BI 中的数据关系、数据建模模式与连接

Data Modelling in Power BI

Power BI 中的数据建模

What Data Modelling Means in Power BI 什么是 Power BI 中的数据建模

Data modelling is the process of defining relationships between tables and organising data structures to support analysis. It determines how data is stored, related, and queried. The core responsibilities of a data model include: 数据建模是定义表间关系并组织数据结构以支持分析的过程。它决定了数据如何存储、关联和查询。数据模型的核心职责包括：

• Establishing relationships between tables: Connecting tables through one-to-many, many-to-many, or one-to-one relationships to enable cross-table filtering and aggregation. 建立表间关系： 通过一对多、多对多或一对一关系连接表，以实现跨表筛选和聚合。
• Enabling effective analysis: Allowing users to slice data by different attributes (date, geography, category). 实现有效分析： 允许用户按不同属性（日期、地理位置、类别）对数据进行切片。
• Improving performance and efficiency: Reducing data redundancy, minimising memory footprint, and accelerating report loading. 提升性能与效率： 减少数据冗余，最小化内存占用，并加速报表加载。
• Supporting reusability and maintainability: Building scalable structures so measures and calculations can be reused across reports. 支持可重用性与可维护性： 构建可扩展的结构，以便在不同报表中复用度量值和计算。

---

Why a Well-Designed Data Model Matters

为什么设计良好的数据模型至关重要

• Impact on DAX calculations: In a star schema, DAX measures are simpler and more reliable because dimension tables and fact tables have clearly separated responsibilities — dimensions filter, facts aggregate. 对 DAX 计算的影响： 在星型模型中，DAX 度量值更简单且更可靠，因为维度表和事实表职责分明——维度负责筛选，事实负责聚合。
• Impact on performance: Power BI’s storage engine and query engine are optimised for star schemas. The simpler the relationships (typically one-to-many from dimension to fact), the more efficient filter propagation and query optimization become. 对性能的影响： Power BI 的存储引擎和查询引擎针对星型模型进行了优化。关系越简单（通常是从维度到事实的一对多关系），筛选传播和查询优化的效率就越高。
• Impact on scalability: As data volumes grow and multiple business subjects or reusable dimensions are needed, flat tables quickly become a bottleneck, while star schemas maintain stable performance. 对可扩展性的影响： 随着数据量增长以及对多个业务主题或可重用维度的需求增加，扁平表很快会成为瓶颈，而星型模型则能保持稳定的性能。
• Impact on maintainability: A clear model structure reduces the risk of modelling errors, enables non-technical users to understand the model, and allows them to build their own reports. 对可维护性的影响： 清晰的模型结构降低了建模错误的风险，使非技术用户能够理解模型，并允许他们自行构建报表。

---

Comparison of Three Data Modelling Approaches

三种数据建模方法的比较

1. Flat Table (扁平表)

Definition: A flat table is a fully denormalized structure where all data is stored in a single table. Each row contains both fact measures and all related descriptive attributes. 定义： 扁平表是一种完全非规范化的结构，所有数据存储在单个表中。每一行既包含事实度量值，也包含所有相关的描述性属性。

• Table structure: One wide table containing both transactional data (e.g., sales amount, quantity) and descriptive data (e.g., product name, category, customer city). No relationships, no separate dimension or fact tables. 表结构： 一张宽表，同时包含交易数据（如销售额、数量）和描述性数据（如产品名称、类别、客户城市）。没有关系，没有独立的维度表或事实表。
• Advantages: Simplest structure, easy to understand; Fast to build for small, simple datasets; No relationships to manage. 优点： 结构最简单，易于理解；对于小型、简单的数据集构建速度快；无需管理关系。
• Disadvantages: Severe data redundancy; Performance problems; Difficult maintenance; Complicated DAX; Not scalable. 缺点： 严重的数据冗余；性能问题；维护困难；DAX 复杂；不可扩展。
• When appropriate: Extremely small and simple datasets, limited dimensions, no need to reuse dimensions across multiple fact tables, and low performance/scalability requirements. 适用场景： 极小且简单的数据集，维度有限，无需在多个事实表间复用维度，且对性能/可扩展性要求较低。

2. Star Schema (星型模型)

Definition: A star schema is a mature dimensional modelling approach that requires the modeller to classify tables into two categories: dimension tables and fact tables. The fact table sits at the centre, with dimension tables connected directly to it, forming a star pattern. 定义： 星型模型是一种成熟的维度建模方法，要求建模者将表分为两类：维度表和事实表。事实表位于中心，维度表直接连接到它，形成星状结构。

• Table structure: Dimension tables (describe business entities, unique keys, descriptive columns) and Fact tables (store measurable business events, dimension keys, numeric measures). 表结构： 维度表（描述业务实体，包含唯一键和描述性列）和事实表（存储可度量的业务事件，包含维度键和数值度量列）。
• Advantages: Optimal performance; Simplified DAX; Easy to understand; Highly scalable; High compression efficiency. 优点： 性能最优；DAX 简化；易于理解；高度可扩展；压缩效率高。
• Disadvantages: Dimension tables may have some redundancy; Highly complex hierarchies may require additional management. 缺点： 维度表可能存在一定冗余；高度复杂的层级结构可能需要额外管理。
• When appropriate: Almost all Power BI production models. When supporting multiple business subjects, reusable dimensions, large data volumes, or a good DAX experience is needed, the star schema is the first choice. 适用场景： 几乎所有的 Power BI 生产模型。当需要支持多个业务主题、可重用维度、大数据量或良好的 DAX 体验时，星型模型是首选。

3. Snowflake Schema (雪花模型)

Definition: A snowflake schema is an extension of the star schema in which dimension tables are further normalised and split into multiple related tables. Hierarchies (e.g., product → subcategory → category) are broken into separate tables. 定义： 雪花模型是星型模型的扩展，其中维度表被进一步规范化并拆分为多个相关表。层级结构（如：产品 → 子类别 → 类别）被拆分为独立的表。

• Table structure: The fact table remains at the centre, but dimensions no longer connect directly. Queries must traverse multiple relationship layers to reach the top-level descriptive attributes. 表结构： 事实表保持在中心，但维度不再直接连接。查询必须遍历多个关系层才能到达顶层描述性属性。
• Advantages: Reduced data redundancy; Suited to already-normalised source systems; More flexible hierarchy management. 优点： 减少数据冗余；适用于已规范化的源系统；层级管理更灵活。
• Disadvantages: Increased complexity (more relationships to manage); Potential performance degradation due to complex filter paths. 缺点： 复杂性增加（需要管理更多关系）；由于复杂的筛选路径，可能导致性能下降。