学习数据仓库概念

• 下载源代码 - 711 字节

引言

联机事务处理 (OLTP) 数据库包含大量的事务记录。有时，它有数百万甚至数十亿行。这些数据存储在关系数据库中。当业务需要分析这些事务时，处理这些数据需要大量时间。业务用户希望在短时间内分析这些数据。他们也不希望在模式上有复杂性。有时，业务用户也希望分析来自其他来源的数据。这些其他来源的数据可能来自不同的业务来源。因此，数据库设计者提出了实现业务数据仓库系统的解决方案。该系统独立于 OLTP 数据库。因此，分析过程比以前更容易。

背景

数据仓库系统由事实表、维度表和辅助表组成。数据仓库系统可以有多个 OLTP 系统作为数据源。它可以是 CRM 系统、ERP 系统、CMS 系统或平面文件数据。数据仓库系统中的表以特定模式组织。该模式可以是星型模式或雪花型模式。我们将在同一篇文章中介绍数据仓库系统的每个主题。

Using the Code

CREATE TABLE SalesRegion (
                SalesRegionId INTEGER NOT NULL,
                RegionName VARCHAR(50) NOT NULL,
                CONSTRAINT SalesRegion_pk PRIMARY KEY (SalesRegionId)
);

CREATE TABLE DimSalesMan (
                SalesRegionId INTEGER NOT NULL,
                SalesManId INTEGER NOT NULL,
                SallesmanName VARCHAR(50) NOT NULL,
                CONSTRAINT DimSalesMan_pk PRIMARY KEY (SalesRegionId, SalesManId)
);

CREATE TABLE DimDate (
                DateId INTEGER NOT NULL,
                DateValue DATE NOT NULL,
                MonthValue INTEGER NOT NULL,
                YearValue INTEGER NOT NULL,
                DateQuarter INTEGER NOT NULL,
                DateHalfYear INTEGER NOT NULL,
                CONSTRAINT DimDate_pk PRIMARY KEY (DateId)
);

CREATE TABLE Currency (
                CurrencyId INTEGER NOT NULL,
                CurrencyName VARCHAR(50) NOT NULL,
                CurrencyFxRate DECIMAL(18) NOT NULL,
                CONSTRAINT Currency_pk PRIMARY KEY (CurrencyId)
);

CREATE TABLE DimProduct (
                ProductId INTEGER NOT NULL,
                CurrencyID INTEGER NOT NULL,
                ProductName VARCHAR(50) NOT NULL,
                ProductCost NUMERIC(18,2) NOT NULL,
                CONSTRAINT DimProduct_pk PRIMARY KEY (ProductId, CurrencyID)
);

CREATE TABLE FactSales (
                SalesRegionId INTEGER NOT NULL,
                SalesManId INTEGER NOT NULL,
                ProductId INTEGER NOT NULL,
                CurrencyID INTEGER NOT NULL,
                DateId INTEGER NOT NULL,
                SalesId INTEGER NOT NULL,
                SalesDescription VARCHAR(100) NOT NULL,
                CONSTRAINT FactSales_pk PRIMARY KEY _
                (SalesRegionId, SalesManId, ProductId, CurrencyID, DateId)
);

ALTER TABLE DimSalesMan ADD CONSTRAINT SalesRegion_DimSalesMan_fk
FOREIGN KEY (SalesRegionId)
REFERENCES SalesRegion (SalesRegionId)

ALTER TABLE FactSales ADD CONSTRAINT FactSales_DimSalesMan_fk
FOREIGN KEY (SalesRegionId, SalesManId)
REFERENCES DimSalesMan (SalesRegionId, SalesManId)

ALTER TABLE FactSales ADD CONSTRAINT FactSales_DimDate_fk
FOREIGN KEY (DateId)
REFERENCES DimDate (DateId)

ALTER TABLE DimProduct ADD CONSTRAINT Currency_DimProduct_fk
FOREIGN KEY (CurrencyID)
REFERENCES Currency (CurrencyId)

ALTER TABLE FactSales ADD CONSTRAINT FactSales_DimProduct_fk
FOREIGN KEY (ProductId, CurrencyID)
REFERENCES DimProduct (ProductId, CurrencyID)    

事实表

包含事实信息的表称为事实表。这些信息可以在分析中衡量，例如总销售额、总订单数、总产品销售额等是业务的不同度量。这些度量信息存储在事实表中。通常，事实表包含业务事务。例如，FactSales 表是事实表。它包含业务事务。

维度表

包含用于数据分析的维度的表称为维度表。它可以是日期维度、产品维度、区域维度、货币维度等。事实表的分析通过维度表进行。可以按时期、区域、产品等对业务事务进行分析。维度表中的数据通常是固定的。这些数据通常不修改，或者在需要业务更新集的情况下进行修改。它可能在一个月或一年中修改一次，例如 DimDate、DimProduct、DimRegion 是维度表。

维度表的属性

维度表的属性可以是产品名称、产品成本、产品颜色、产品类别、产品重量等。

自然键

分配给属性的默认键称为自然键，例如 ProductCode 是任何产品的自然键。

代理键

用作连接维度表和事实表的键的数值称为代理键，例如，ProductId 是产品维度表的代理键。它与 FactSales 事实表连接以获取产品销售信息。

数据库架构

数据仓库是一个关系数据库，其中事实表和维度表相互关联。在定义数据仓库系统之前，必须定义其结构。数据仓库中的所有表都以特定模式构建。可以是星型模式或雪花型模式。

星型模式

维度表直接与事实表连接的数据库模式称为星型模式。它是设计数据仓库系统中最流行的模式。它易于实现和关联维度表与事实表。

雪花型模式

维度表与事实表连接，但维度表本身与其他维度表相关以获取数据的数据库模式称为雪花型模式。雪花型模式使数据仓库设计复杂。如果可能，我们应该避免雪花型模式，而使用视图，其中多个维度表连接在一起并用作简单的维度表。

垃圾维度

数据仓库系统中一些值的行数非常少。我们不能将它们包含为维度表，也不能在数据仓库系统的全局设置值等系统中避免它们。我们可以将所有需要的全局设置放在单独的设置表中，但这不被认为是维度表。它被称为垃圾维度。

退化维度

数据仓库系统中的某些表包含事实表的属性。这些属性用于业务分析，例如事实表中的事务号被维护为退化维度。

缓慢变化维度

这是管理维度表中数据变化的过程。当维度表中的数据发生修改时，需要维护数据的变化历史。当分析依赖于不同时间段时，它为业务提供了准确的视图，例如，全球业务销售额取决于汇率。每个国家都有不同的货币，汇率会不时更新。如果某些销售发生在 2014 年 1 月，当时的汇率为 XXX，而在 2014 年 7 月进行了修改，只有在我们可以根据该时间段的汇率准确评估销售额时，才能进行准确的分析。如果我们根据当前的汇率计算销售额，那么销售数据将不准确。

SCD 类型 1

在 SCD 类型 1 中，维度表会用新值覆盖维度属性的现有值。因此，不对任何已更改的属性值维护更改历史。维度表将仅包含最后更新的值，例如，在 dimcustomer 表中，客户地址可以作为 SCD 类型 1 来维护，以便它只包含客户的当前地址。

SCD 类型 2

在 SCD 类型 2 中，会维护完整的更改历史记录。因此，对于属性值的每次更改，都会在维度表中插入新记录。历史记录通过记录中的日期范围进行跟踪，例如 DimCurrencyFxRate 应为 SCD 类型 2，因为货币汇率仅在特定日期到某个日期范围内有效。

SCD 类型 3

在 SCD 类型 3 中，仅维护最近的一些更改历史。更改跟踪的数量基于业务需求。因此，此类维度表包含同一属性的多个列，例如，DimProduct 表可能包含 Productname 的三个列名，分别为 Productname1、Productname2、Productname3。当业务团队要求更改任何产品的名称时，这将有所帮助。Productname3 将包含最新的更改名称，因此 Productname2 和 Productname1 可以包含产品的前 2 个名称。

桥接表或无事实事实表

它是一个中间表，允许将一个维度表连接到另一个维度表。它不包含事务详细信息，而是包含键属性以及其他列。

快照事实表

包含事实表事务聚合的表称为快照事实表。当业务事务量很大时，事实表中的条目也会很大。这将使分析过程耗时，因此事实表事务被聚合并存储在单独的快照表中。

事务事实表

包含事务完整详细信息的表称为事务事实表。事务事实表中的属性包含事务的每个详细信息，例如，事务号、产品 ID、事务日期、事务用户等。

关注点

本文包含有关数据仓库中使用的术语的信息。这将帮助用户在设计新的数据仓库系统之前理解概念。

历史

• 2014 年 7 月 22 日：初始版本