如何使用Star模式优化数据仓库？

Star模式是用于数据仓库和商业智能的高效数据库设计。它将数据组织到链接到周围尺寸表的中心事实表中。这种类似星形的结构简化了复杂的查询，提高了性能，并且是需要快速检索和简化连接的大型数据集的理想选择。

Star模式的主要优点是它可以最大程度地减少查询连接数量的能力，从而提高可读性和性能，尤其是对于数据聚合和报告。它的直接设计支持快速数据摘要，这对于生成业务见解至关重要。

Star模式还提供可扩展性，可以添加新的尺寸表，而不会破坏现有的结构。这支持持续的增长和适应性。将事实和维度表分开可最大程度地减少数据冗余并保持一致性。

在此博客中，我们将探索Star模式，展示其与模拟数据最佳查询性能的设置，并将其与雪花架构进行比较，并提供简化的数据管理和分析方法。

学习目标

• 了解Star模式的关键要素。
• 了解如何设计星形架构并了解其在提高查询性能方面的优势。
• 探索恒星模式如何简化分析查询。
• 了解Star模式如何促进数据聚合和报告。
• 了解恒星模式与雪花架构的比较以及如何选择合适的模式。

本文作为Thedata Science Blogathon的一部分发表。

目录

• 什么是明星模式？
• 示例：客户订单
  • 1。客户数据（尺寸表）
  • 2。产品数据（尺寸表）
  • 3。日期数据（尺寸表）
  • 4。订单数据（事实表）
• 设计星模架
• 查询Star模式是否有业务见解
  • 示例1：按产品类别按总销售
  • 示例2：按客户会员级别按平均订单价值
• Star模式与雪花架构
  • 1。什么是雪花架构？
  • 2。结构
  • 3。查询性能
  • 4。存储效率
  • 5。可伸缩性
• 为客户订单设计雪花模式
  • 雪花架构结构：
• 查询雪花模式示例
• 结论
• 常见问题

什么是明星模式？

Star模式是一个数据库架构，该模式由一个中央事实表组成，该表格被尺寸表包围。事实表存储可衡量的定量数据，例如销售交易和客户订单。相比之下，尺寸表存储描述性属性，例如客户详细信息，产品类别和时间信息。

恒星具有使星星相关的结构，可以通过使用Foreign键连接事实和尺寸表来创建。该设计非常优化用于重读的操作，尤其是在报告和分析环境中。

星形模式的关键组成部分：

• 事实表：事实表存储交易数据。在我们的客户订单示例中，此表将跟踪客户下的每项订单。
• 尺寸表：尺寸表是补充表，其中包含有关交易中涉及的实体的客户，产品和日期的描述性信息。

该结构可以通过简化表从数据中提取洞察力时简化连接并降低复杂性来实现快速查询。

另请阅读：数据仓库概念的简要介绍

示例：客户订单

为了说明Star模式的工作原理，我们将生成一个模拟数据集，代表在线商店中的客户订单。这些数据将填充我们的事实和尺寸表。

1。客户数据（尺寸表）

我们将创建一个模拟的客户数据集，包括关键信息，例如其ID，名称，位置和会员类型。客户数据维度表详细介绍了每个客户，并允许我们将订单链接到特定客户，以分析客户行为，偏好和人口趋势。

• Customer_ID：每个客户的唯一标识符。该ID将用作“订单”事实表中的外键，将每个交易与下订单的客户联系起来。
• first_name：客户的名字。这是客户识别信息的一部分。
• last_name：客户的姓氏。与名字一起，这提供了对客户的完整标识。
• 位置：此字段包含客户的地理位置（例如，国家或地区）。它可用于根据地理位置分析客户订单。
• 会员资格_level：指示客户是标准会员还是高级会员资格。这允许按会员类型进行客户行为分析（例如，高级客户花费更多吗？）。

导入大熊猫作为pd
导入numpy作为NP

def generate_customer_data（n_customers = 1000）：
    np.random.seed（42）
    customer_ids = np.arange（1，n_customers 1）
    first_names = np.random.choice（['thato'，'jane'，'alice'，'bob']，size = n_customers）
    last_names = np.random.Choice（['Smith'，'Mkhize'，'Brown'，'Johnson']，size = n_customers）
    位置= np.random.Choice（[['南非'，'加拿大'，'uk'，'dermany']，size = n_customers）
    会员资本_levels = np.random.Choice（['标准'，'premium']，size = n_customers）
    客户= pd.dataframe（{{
        'customer_id'：customer_ids，
        'first_name'：first_names，
        'last_name'：last_names，
        “位置”：位置，
        '会员资助_level'：会员资助_levels
    }））
    返回客户

customers_df = generate_customer_data（）
客户_df.head（）

输出：

另请阅读：2024年数据仓库的完整指南

2。产品数据（尺寸表）

接下来，我们将创建一个用于购买产品的数据集。该数据将包括产品ID，产品名称，类别和价格等字段。

• product_id：每个产品的唯一标识符。该ID将用作订单的事实表中的外键，以连接每次交易中购买的产品。
• product_name：产品名称（例如，笔记本电脑，电话，耳机）。该领域提供了有关用于分析和报告的产品的描述性信息。
• 类别：产品类别（例如，电子，配件）。类别有助于按产品类型进行分组和分析销售业绩。
• 价格：产品的价格。产品的单位价格将用于计算事实表中的总价格（乘以数量时）。

 def generate_product_data（n_products = 500）：
    product_ids = np.Arange（1，N_Products 1）
    product_names = np.random.Choice（['laptop'，'phone'，'tablet'，'耳机']，size = n_products）
    类别= np.random.Choice（['electronics'，'附件']，size = n_products）
    价格= np.random.统一（50，1000，尺寸= n_products）
    产品= pd.dataframe（{{
        'product_id'：product_ids，
        'product_name'：product_names，
        “类别”：类别，
        “价格”：价格
    }））
    返回产品

products_df = generate_product_data（）
products_df.head（）

输出：

3。日期数据（尺寸表）

日期尺寸表对于任何数据仓库或商业智能方案中的基于时间的分析至关重要。它使您可以根据特定时期（例如年，月，日或四分之一）进行汇总和分析数据。该表将引用交易的时间，使我们可以将每个订单链接到其相应日期。

• order_date：订单的实际日期，订单的事实表将引用该订单。
• 年：下订单的一年。
• 月份：订单月（从1到12）。
• 一天：一个月的一天。
• 周：一年的一周（基于ISO日历）。
• 季度：年度季度（1月至3月1日，4月至6月的2个）。

导入大熊猫作为pd

def generate_dates_data（start_date ='2023-01-01'，end_date ='2024-02-21'）：
    ＃创建一个日期范围
    date_range = pd.date_range（start = start_date，end = end_date，freq ='d'）
    
    ＃创建一个带日期零件的数据框
    dates_df = pd.dataframe（{
        'order_date'：date_range，
        '年'：date_range.year，
        “月”：date_range.month，
        “天”：date_range.day，
        “周”：date_range.isocalendar（）。周，
        “四分之一”：date_range.quarter
    }））
    
    返回dates_df

＃生成日期尺寸表
dates_df = generate_dates_data（）
dates_df.head（）

输出：

另请阅读：什么是数据仓库？

4。订单数据（事实表）

最后，我们将生成充当事实表的订单数据。该数据集将跟踪客户订单，包括订单日期，总价和产品信息。订单中的cow fact表中的行代表客户放置的唯一订单，并且它直接链接到相关的尺寸表（客户，产品和日期）通过外键。这允许进行详细的分析，例如跟踪每个客户花费的数量，哪些产品最受欢迎以及订单活动随时间变化。

• order_id ：每个顺序的唯一标识符。这是事实表的主要关键。
• Customer_ID：将每个订单与客户维度表中的客户链接到客户的外键。这允许根据客户属性（例如位置或会员级别）对订单进行分析。
• product_id：将每个订单与产品尺寸表中的产品联系起来的外键。这可以分析产品销售，趋势和性能。
• order_date：将每个订单链接到日期尺寸表中的特定日期的外键。该字段可实现基于时间的分析，例如按月或季度按销售进行销售。
• 数量：订购产品的单位数量。这对于计算订单的总价格和理解购买模式至关重要。
• Total_price：订单的总价格是通过将产品价格乘以订购数量的数量来计算的。这是分析收入的主要指标。

 def generate_order_data（n_orders = 10000）：
    order_ids = np.arange（1，n_orders 1）
    customer_ids = np.random.randint（1，1000，size = n_orders）
    product_ids = np.random.randint（1，500，size = n_orders）
    order_dates = pd.date_range（'2023-01-01'，oferes = n_orders，freq ='h'）
    数量= np.random.randint（1，5，size = n_orders）
    total_prices =数量 * np.random.uniform（50，1000，size = n_orders）
    orders = pd.dataframe（{{
        'order_id'：order_ids，
        'customer_id'：customer_ids，
        'product_id'：product_ids，
        'order_date'：order_dates，
        “数量”：数量，
        'total_price'：total_prices
    }））
    返回订单

orders_df = generate_order_data（）
orders_df.head（）

输出：

设计星模架

现在，我们可以使用模拟的客户订单数据构建S​​tar模式。主要的事实表将由订单组成，而关联的尺寸表将包括客户，产品和日期。

Star模式设计：

• 事实表：
  • 订单：包含交易数据，包括order_id，customer_id，product_id，order_date，wittity和total_price。
• 尺寸表：
  • 客户：包含有关客户的描述性数据，包括customer_id，first_name，last_name，location和membership_level。
  • 产品：包含产品详细信息，包括product_id，product_name，类别和价格。
  • 日期：跟踪每个订单的日期，包括订单_date，Year，“月和日”等字段。

随着每个维度表直接与事实表关联，STAR模式设计简化了查询，从而降低了SQL连接的复杂性。

另请阅读：了解数据仓库及其结构的基础知识

查询Star模式是否有业务见解

现在，我们的架构已经到位，假设已经创建并存储了这4个表（订单，客户，产品，日期），并将其存储在SQL数据库中，该数据库具有与每个相应表生成的上述数据范围相同的架构。通过此设置，我们可以运行SQL查询，以从数据中获得宝贵的业务见解。

示例1：按产品类别按总销售

我们可以使用“订单”事实表和产品维度表轻松地按产品类别检索总销售。此查询总结了订单表中的total_price，并根据产品表按产品类别分组结果：

选择
  P.类，
  sum（o.total_price）作为total_sales
从
  订单o
加入
  产品p
在
  o.product_id = p.product_id
组
  P.类
订购
  total_sales desc;

示例2：按客户会员级别按平均订单价值

我们可以加入订单和客户表，以了解不同的会员水平如何影响订单价值。此查询表明，高级会员的平均支出是否比标准成员多。

选择
  C.Membership_level，
  AVG（o.total_price）为avg_order_value
从
  订单o
加入
  客户c
在
  o.customer_id = c.customer_id
组
  C.Membership_level
订购
  avg_order_value desc;

Star模式与雪花架构

在尺寸表的组织中发现了Star模式和雪花架构之间的主要区别，特别是关于这些表中实现的归一化程度。

1。什么是雪花架构？

雪花架构是一种数据库模式，该模式通过标准化将尺寸表组织到几个互连表中。与具有不可缩小的尺寸表的Star模式不同，Snowflake模式将尺寸表进一步将尺寸表划分为子量度。例如，代表位置的维度表可以进一步分为城市和国家的不同表。这种布置导致一个更复杂的层次结构，类似于雪花，这是其名称的起源。

以下是概述何时使用每个模式的比较：

2。结构

这是结构：

Star Schema：

• 尺寸表是不可正的，这意味着它们是平坦的，并且包含所有必要的细节。该结构将尺寸表直接链接到中心事实表，从而导致查询连接较少。
• 例如，在与我们的客户订单示例有关的Star模式中，客户尺寸表包含单个表中的所有客户信息（例如，Customer_ID，first_name，last_name和Location）。

雪花架构：

• 将尺寸表归一化，并分解为多个相关表。每个维度表都根据层次结构分为子维度（例如，将位置分解为城市和乡村桌）。
• 示例：在雪花架构中，客户表可以进一步分解为单独的位置表，该表将Customer_ID与不同的地理数据（例如城市和国家 /地区）链接。

3。查询性能

这是Star Schema和Snowflake模式的查询性能：

Star Schema：

• 尺度表的表尺寸表会导致连接较少，从而提高了对读取操作的查询性能，尤其是在分析查询和报告中。

雪花架构：

• 需要更多的连接来连接归一化表，从而导致查询性能较慢，尤其是在复杂的查询中。

4。存储效率

这是Star模式和雪花模式的存储效率：

Star Schema：

• 由于尺寸表是不合同的，因此通常会有一些数据冗余，需要更多的存储空间。但是，查询简单性和性能改进通常超过此存储成本。

雪花架构：

• 雪花架构通过标准化尺寸表可降低冗余，从而使其更具存储效率。这对于避免冗余的大规模数据集是有益的。

5。可伸缩性

这是Star模式和雪花模式的可扩展性：

Star Schema：

• Star模式的简单，不合同的结构使扩展和维护更加容易。添加新属性或尺寸表很简单，不需要重新加工模式。

雪花架构：

• 尽管雪花架构可以处理更复杂的关系，但由于尺寸表的归一化程度，可能需要更多的努力来扩展和维护。

为客户订单设计雪花模式

让我们将客户订单的数据示例扩展到雪花模式。我们将不将所有客户信息存储在一个客户表中，而是将其分解为标准化数据并减少冗余。

雪花架构结构：

在相同客户订单数据的雪花架构中，我们将有以下内容：

• 事实表：使用order_id，customer_id，product_id，order_date，wittity和total_price订购表。
• 尺寸表：我们不保留尺寸的尺寸表，而是将它们分解为其他相关表。例如：
• 客户表：
  • customer_id，first_name，last_name，location_id，会员资本_level
• 位置表：
  • location_id，city_id，country_id
• 城市表：
  • city_id，city_name
• 国家表：
  • country_id，country_name
• 产品表：
  • product_id，product_name，category_id，价格
• 类别表：
  • category_id，category_name

订单事实表仍然包含交易数据，但是在多个表中将客户和产品信息归一化（例如，客户位置将链接到不同级别的地理数据）。

查询雪花模式示例

为了在雪花模式中检索产品类别的总销售，您将加入几张桌子以获得最终结果。这是一个示例SQL查询：

选择
  c.category_name，
  sum（o.total_price）作为total_sales
从
  订单o
加入
  产品p
在
  o.product_id = p.product_id
加入
  类别c
在
  p.Category_id = c.Category_id
组
  C.Category_name
订购
  total_sales desc;

如您所见，由于归一化尺寸表，与Star模式相比，Snowflake模式需要额外的连接。这会产生更复杂的查询，但可以最大程度地减少存储中的冗余。

结论

总而言之，为快速查询性能和分析查询的简单性而进行了优化，而雪花架构旨在通过标准化尺寸表来减少冗余。两者之间的选择取决于数据集的特定需求和组织的优先级，无论是查询性能还是存储效率。

在本文中，我们说明了使用模拟的客户订单数据集构建恒星和雪花架构。我们针对客户，产品，订单和日期的事实和尺寸表，证明了每个表在组织数据中的基本功能，以进行有效的查询和分析。该模式允许通过外国密钥（例如product_id and Customer_id）将事实表（订单）（订单）连接到尺寸表（客户，产品和日期），从而简化了数据检索并促进了广泛的查询。

我们还强调了Star模式的关键好处：

• 简化查询：实施星模架已经说明了如何使SQL查询更加简单，这是我们查询对按产品类型分类的总销售的查询。
• 查询性能： STAR模式设计通过减少必要的连接数量并有效地汇总数据来促进更快的查询执行。
• 可伸缩性和灵活性：我们演示了如何使用新属性或行扩展每个维度表，以及随着业务数据的增长或需求的变化，Star模式如何轻松扩展。
• 数据汇总和报告：我们证明了执行数据汇总和报告任务的易用性，例如根据Star模式的结构，计算按产品类别计算总销售额或每月趋势。

雪花架构通过标准化尺寸表，提高存储效率，但需要更复杂的查询来降低数据冗余。它是管理分层关系或优化存储空间的理想选择。相比之下，Star模式简化了数据管理并加快了查询性能，从而可以更好地进行洞察力和有效的分析。两者之间的选择取决于您是否优先考虑查询性能还是存储效率。

关键要点

1. Star模式增强了数据组织，并通过将交易数据分类为事实和尺寸表来提高查询性能。
2. 模式设计支持快速查询，从而更容易获得对销售趋势，客户行为和产品性能的见解。
3. Star模式设计用于可扩展性，可以随着数据集的增加而直接扩展。可以添加新的尺寸表或额外属性，而不会影响当前的架构，从而确保适应不断变化的业务需求。
4. 雪花架构通过标准化尺寸表可最大程度地减少数据冗余，从而使其更具存储效率。但是，需要额外的连接可能会导致更复杂的查询。

探索GitHub上本文背后的代码。

本文所示的媒体不由Analytics Vidhya拥有，并由作者酌情使用。

常见问题

Q1。什么是明星模式？

Ans。 Star模式是一种数据库架构设计，通常用于数据仓库和商业智能应用程序。它由一个包含交易或可测量数据的中央事实表组成，该数据被包含包含描述性信息的尺寸表所包围。这种类似星的结构优化了查询性能，并通过最大程度地减少复杂的连接并使查询更直观，从而简化了数据检索。 “星”这个名字来自架构的形状，事实表位于中间，尺寸表像星的点一样向外辐射。

Q2。是什么区别了事实表与维度表？

Ans。事实表的特征是它包含交易或可量化数据，例如销售数据，订单计数或收入指标。尺寸表提供了描述性属性，例如客户名称，人口统计，产品分类或日期。事实表包含定量数据，而尺寸表则提供了上下文。

Q3。 Star模式以什么方式增强查询性能？

Ans。 STAR模式通过减少所需的连接数来优化查询性能，因为事实表直接连接到每个维度表。这简化了查询并降低计算成本，从而导致查询执行时间更快，尤其是对于大型数据集。

Q4。是否可以将新的尺寸表添加到星模中，而不会破坏现有结构？

Ans。实际上，恒星模式既可扩展又灵活。可以将新的尺寸表或其他属性集成到现有架构中而不会引起任何中断。这种适应性使Star模式可以满足扩展的数据集和不断发展的业务需求。

Q5。我该如何在星形模式和雪花架构之间进行选择？

Ans。如果您的优先事项是查询性能和简单性，请选择星模架。如果您的目标是最大程度地减少数据冗余并优化存储效率，尤其是对于具有分层关系的大型数据集，请选择雪花架构。

以上是如何使用Star模式优化数据仓库？的详细内容。更多信息请关注PHP中文网其他相关文章！