创建第一个数据仓库

Mubin M. Shaikh

4.93/5 (124投票s)

2013 年 9 月 11 日

CPOL

10分钟阅读

582180

18436

在本文中，我将向您展示数据仓库的重要性。

下载脚本 - 8.1 KB

引言

在本文中，我将向您展示数据仓库的重要性。为什么以及何时一个组织或公司需要计划设计数据仓库？我们将快速了解各种概念，然后通过一个小型场景，我们将设计我们的第一个数据仓库并用测试数据填充它。

如果您在思考什么是数据仓库，让我简单解释一下，数据仓库是数据的集成、非易失性、面向主题和时间变化的存储。当您的数据分布在各种数据库、应用程序或以不同格式存储在不同位置时，并且您希望通过集成并在单个位置为这些分布式数据创建唯一的存储，将这些数据转换为有用的信息时，您就需要开始考虑使用数据仓库。

在另一种情况下，如果您的数据库中每天的事务性数据录入量非常大，可能有数百万或数十亿条记录，那么您需要将这些数据归档到另一个存档数据库中，该数据库保存您的历史数据，以减轻实时数据库的负载。如果您在此存档数据库上创建二维报表，那么您的报表生成速度会非常慢，可能需要几分钟到几小时，或者可能会给您超时错误。在这种二维数据上，您甚至无法对数据进行任何类型的趋势分析，您无法将数据划分为一天中的各种时间段，也无法研究不同年份、季度、月份、周、日、工作日-周末组合之间的数据。在这种情况下，为了根据您的历史数据做出完美的决策，您必须考虑根据您的需求设计数据仓库，这样您就可以使用多个维度研究数据，并进行更好的分析以做出准确的决策。

数据仓库的设计有助于将数据转换为有用的信息，它提供了多个维度来研究您的数据，因此高层管理人员可以根据使用这些数据计算出的统计数据做出快速准确的决策。这些数据还可以用于数据挖掘、预测、预测性分析、更快的报表和信息丰富的仪表板创建，这也有助于管理层在日常生活中根据他们的需求解决各种复杂的查询。

如今，用户需要拥有自助式 BI（商业智能）功能，以便他们可以自己创建报表（即席报表），并且无需太多技术知识即可进行数据分析。数据仓库是业务分析师的梦想——所有关于组织活动的信息都集中在一个地方，可以使用一套分析工具。但如何将梦想变为现实？首先，您必须规划您的数据仓库系统。因此，数据仓库建模是朝着这个方向迈出的第一步。

场景

X-Mart 在我们城市有不同的商场，每天销售各种产品。高层管理人员在决策时面临问题，因为缺乏集成数据，他们无法根据自己的需求研究数据。因此，他们要求我们设计一个系统，可以帮助他们快速决策并提供投资回报 (ROI)。

让我们开始设计数据仓库，在开始数据仓库设计之前，我们需要遵循几个步骤。

开发数据仓库

下面列出的数据仓库项目阶段与大多数数据库项目类似，从识别需求开始，到执行 T-SQL 脚本以创建数据仓库结束。

识别并收集需求
设计维度模型
执行 T-SQL 查询以创建并填充您的维度表和事实表

识别并收集需求

我们需要采访关键决策者，了解哪些因素决定了业务的成功？管理层希望如何分析他们的数据？最重要的是哪些业务问题需要通过这个新系统来满足？

我们还需要与不同部门的人员合作，了解数据及其共同关系（如果有），并记录该系统需要满足的全部需求。

让我们首先确定管理层对他们的需求。

需要查看每个商店的每日、每周、每月、每季度利润。
比较不同时间段的销售额和利润。
比较一天中不同时间段的销售额。
需要了解哪些产品在哪个地点需求量更大？
需要研究一周、一月、一年中按一天时间段划分的销售趋势？
哪天的销售额更高？
本月的每个周日，销售额和利润是多少？
工作日和周末的销售趋势如何？
需要比较每周、每月和每年的销售额以了解增长和 KPI？

设计维度模型

我们需要设计维度模型以满足用户的需求，该模型必须解决业务需求并包含易于访问的信息。模型设计应易于根据未来需求进行扩展。此模型设计必须支持 OLAP 多维数据集，以便为分析师提供“即时”查询结果。

让我们快速了解一些新术语，然后我们将为我们的需求识别/推导它们。

维度

维度是一个主表，由独立的、不重叠的数据元素组成。维度的主要功能是为您的数据提供过滤、分组和标签。维度表包含有关业务主题的文本描述。

让我向您简要介绍一下可用的不同类型的维度，例如确认维度、角色扮演维度、退化维度、垃圾维度。

缓慢变化维度（SCD）指定了存储随时间变化的维度值并保留历史的方式。有不同的方法/类型可用于存储这种变化的历史记录，**例如**SCD1、SCD2和SCD3，您可以根据需要使用。

让我们识别与上述案例研究相关的维度。

产品、客户、商店、日期、时间、销售人员

测量

度量表示包含可量化数据（通常是数字）的列，可以进行聚合。度量通常映射到事实表中的列。供您参考，有各种类型的度量。**例如**加性、半加性和非加性。

让我们定义在我们的案例中什么是度量。

实际成本、总销售额、数量、事实表记录数

事实表

事实表中的数据称为度量（或依赖属性），事实表提供按客户、销售人员、产品、时间段和商店维度细分的销售统计数据。事实表通常包含您的实时系统的历史事务条目，它主要由引用各种维度和将执行聚合的数字度量值的外键列组成。事实表有不同的类型，**例如**事务性、累积性和快照。

让我们确定我们的事实销售表应包含哪些属性。

外键列
销售日期键、销售时间键、发票号码、销售人员ID、商店ID、客户ID
Measures
实际成本、总销售额、数量、事实表记录数

设计关系数据库

我们已经做了一些基本工作来识别维度和度量，现在我们必须使用适当的模式来关联这些维度和事实表。

用于开发维度模型的几种流行模式如下：

例如：星型模式、雪花模式、星雪花模式、分布式星型模式等。

在另一篇文章中，我们将详细讨论所有这些模式、维度类型、度量类型等。

我个人会首先尝试使用星型模式，因为它提供了用于分析的分层属性模型以及查询数据的快速性能。

星型模式的图表类似于一个星星，从中心辐射出点。星星的中心是事实表，星星的点是维度表。

让我们创建我们的第一个星型模式，请参考下图

Using the Code

让我们一步步执行 T-SQL 脚本来创建表并用适当的测试值填充它们。

请按照以下步骤在 SSMS (SQL Server Management Studio) 中运行查询。

打开 SQL Server Management Studio
连接数据库引擎
打开新查询编辑器
在新查询编辑器窗口中逐一复制粘贴以下步骤中给出的脚本
要运行给定的 SQL 脚本，请按 F5

步骤 1

在 SQL Server 中为您的数据仓库创建数据库

Createdatabase Sales_DW
Go

Use Sales_DW
Go

第二步

在数据仓库中创建**客户维度**表，该表将保存客户个人详细信息。

Create table DimCustomer
(
CustomerID int primary key identity,
CustomerAltID varchar(10) not null,
CustomerName varchar(50),
Gender varchar(20)
)
go

用示例值填充**客户维度**

Insert into DimCustomer(CustomerAltID,CustomerName,Gender)values
('IMI-001','Henry Ford','M'),
('IMI-002','Bill Gates','M'),
('IMI-003','Muskan Shaikh','F'),
('IMI-004','Richard Thrubin','M'),
('IMI-005','Emma Wattson','F');
Go

步骤 3

创建基本级别的**产品维度**表，不考虑任何类别或子类别

Create table DimProduct
(
ProductKey int primary key identity,
ProductAltKey varchar(10)not null,
ProductName varchar(100),
ProductActualCost money,
ProductSalesCost money

)
Go

用示例值填充**产品维度**

Insert into DimProduct(ProductAltKey,ProductName, ProductActualCost, ProductSalesCost)values
('ITM-001','Wheat Floor 1kg',5.50,6.50),
('ITM-002','Rice Grains 1kg',22.50,24),
('ITM-003','SunFlower Oil 1 ltr',42,43.5),
('ITM-004','Nirma Soap',18,20),
('ITM-005','Arial Washing Powder 1kg',135,139);
GO

步骤 4

创建**商店维度**表，该表将保存与各地可用商店相关的详细信息。

Create table DimStores
(
StoreID int primary key identity,
StoreAltID varchar(10)not null,
StoreName varchar(100),
StoreLocation varchar(100),
City varchar(100),
State varchar(100),
Country varchar(100)
)
Go

用示例值填充**商店维度**

Insert into DimStores(StoreAltID,StoreName,StoreLocation,City,State,Country )values
('LOC-A1','X-Mart','S.P. RingRoad','Ahmedabad','Guj','India'),
('LOC-A2','X-Mart','Maninagar','Ahmedabad','Guj','India'),
('LOC-A3','X-Mart','Sivranjani','Ahmedabad','Guj','India');
Go

步骤 5

创建**维度销售人员**表，该表将保存与各地可用商店相关的详细信息。

Create table DimSalesPerson
(
SalesPersonID int primary key identity,
SalesPersonAltID varchar(10)not null,
SalesPersonName varchar(100),
StoreID int,
City varchar(100),
State varchar(100),
Country varchar(100)
)
Go

用示例值填充维度**销售人员**

Insert into DimSalesPerson(SalesPersonAltID,SalesPersonName,StoreID,City,State,Country )values
('SP-DMSPR1','Ashish',1,'Ahmedabad','Guj','India'),
('SP-DMSPR2','Ketan',1,'Ahmedabad','Guj','India'),
('SP-DMNGR1','Srinivas',2,'Ahmedabad','Guj','India'),
('SP-DMNGR2','Saad',2,'Ahmedabad','Guj','India'),
('SP-DMSVR1','Jasmin',3,'Ahmedabad','Guj','India'),
('SP-DMSVR2','Jacob',3,'Ahmedabad','Guj','India');
Go

步骤 6

创建**日期维度**表，该表将创建并填充按各种级别划分的日期数据。

为此，您必须参考我在 CodeProject 上的文章创建和填充日期维度。

下载脚本并在该数据库中运行它，以创建和填充日期维度值。

步骤 7

创建**时间维度**表，该表将创建并填充全天的时间数据，并包含各种时间段。

为此，您必须参考我在 Code Project 上的文章，创建和填充包含 24 小时以上值的时间维度

下载脚本并在该数据库中运行它，以创建和填充时间维度值。

步骤 8

创建**事实表**以保存前一天销售的所有事务性条目，其中包含引用维度主键列的适当外键列；在填充事实表时，您必须注意引用适当维度的主键值。

例如：

客户亨利·福特于2013年1月1日，在Sivranjani的D-mart购买了2件商品（1公斤葵花籽油和2块Nirma香皂），发票编号为一张，销售员是雅各布，记录的开票时间是13:00，那么我们来定义如何引用每个维度的主键值。

在填充事实表之前，您必须根据给定示例识别并查找维度中的主键列值，并用适当的键值填充事实表的外键列。

属性名称	维度表	主键列/值
日期（2013年1月1日），销售日期键（20130101）	日期维度	日期键：20130101
时间 (13:00:00) 销售时间备用键 (130000)	时间维度	时间键：46800
组合键（销售人员备用ID+姓名）用于（'SP-DMSVR1'+'Jacob'）	销售人员维度	销售人员ID：6
产品备用键（葵花籽油 1kg）'ITM-003'	产品维度	产品ID：3
产品备用键（Nirma 香皂）'ITM-004'	产品维度	产品ID：4
Sivranjani 商店的商店备用 ID 'LOC-A3'	商店维度	商店ID：3
客户亨利·福特的备用 ID 为 'IMI-001'	客户维度	客户ID：1

Create Table FactProductSales
(
TransactionId bigint primary key identity,
SalesInvoiceNumber int not null,
SalesDateKey int,
SalesTimeKey int,
SalesTimeAltKey int,
StoreID int not null,
CustomerID int not null,
ProductID int not null,
SalesPersonID int not null,
Quantity float,
SalesTotalCost money,
ProductActualCost money,
Deviation float
)
Go

添加事实表与维度表之间的关系

-- Add relation between fact table foreign keys to Primary keys of Dimensions
AlTER TABLE FactProductSales ADD CONSTRAINT _
FK_StoreID FOREIGN KEY (StoreID)REFERENCES DimStores(StoreID);
AlTER TABLE FactProductSales ADD CONSTRAINT _
FK_CustomerID FOREIGN KEY (CustomerID)REFERENCES Dimcustomer(CustomerID);
AlTER TABLE FactProductSales ADD CONSTRAINT _
FK_ProductKey FOREIGN KEY (ProductID)REFERENCES Dimproduct(ProductKey);
AlTER TABLE FactProductSales ADD CONSTRAINT _
FK_SalesPersonID FOREIGN KEY (SalesPersonID)REFERENCES Dimsalesperson(SalesPersonID);
Go
AlTER TABLE FactProductSales ADD CONSTRAINT _
FK_SalesDateKey FOREIGN KEY (SalesDateKey)REFERENCES DimDate(DateKey);
Go
AlTER TABLE FactProductSales ADD CONSTRAINT _
FK_SalesTimeKey FOREIGN KEY (SalesTimeKey)REFERENCES DimDate(TimeKey);
Go

用前一天销售的历史交易值填充您的**事实表**，并带有适当的维度键值。

Insert into FactProductSales(SalesInvoiceNumber,SalesDateKey,_
SalesTimeKey,SalesTimeAltKey,StoreID,CustomerID,ProductID ,_
SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)values
--1-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,_
StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,_
ProductActualCost,SalesTotalCost,Deviation)
(1,20130101,44347,121907,1,1,1,1,2,11,13,2),
(1,20130101,44347,121907,1,1,2,1,1,22.50,24,1.5),
(1,20130101,44347,121907,1,1,3,1,1,42,43.5,1.5),

(2,20130101,44519,122159,1,2,3,1,1,42,43.5,1.5),
(2,20130101,44519,122159,1,2,4,1,3,54,60,6),

(3,20130101,52415,143335,1,3,2,2,2,11,13,2),
(3,20130101,52415,143335,1,3,3,2,1,42,43.5,1.5),
(3,20130101,52415,143335,1,3,4,2,3,54,60,6),
(3,20130101,52415,143335,1,3,5,2,1,135,139,4),
--2-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,_
StoreID,CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(4,20130102,44347,121907,1,1,1,1,2,11,13,2),
(4,20130102,44347,121907,1,1,2,1,1,22.50,24,1.5),

(5,20130102,44519,122159,1,2,3,1,1,42,43.5,1.5),
(5,20130102,44519,122159,1,2,4,1,3,54,60,6),

(6,20130102,52415,143335,1,3,2,2,2,11,13,2),
(6,20130102,52415,143335,1,3,5,2,1,135,139,4),

(7,20130102,44347,121907,2,1,4,3,3,54,60,6),
(7,20130102,44347,121907,2,1,5,3,1,135,139,4),

--3-jan-2013
--SalesInvoiceNumber,SalesDateKey,SalesTimeKey,SalesTimeAltKey,StoreID,_
CustomerID,ProductID ,SalesPersonID,Quantity,ProductActualCost,SalesTotalCost,Deviation)
(8,20130103,59326,162846,1,1,3,1,2,84,87,3),
(8,20130103,59326,162846,1,1,4,1,3,54,60,3),


(9,20130103,59349,162909,1,2,1,1,1,5.5,6.5,1),
(9,20130103,59349,162909,1,2,2,1,1,22.50,24,1.5),

(10,20130103,67390,184310,1,3,1,2,2,11,13,2),
(10,20130103,67390,184310,1,3,4,2,3,54,60,6),

(11,20130103,74877,204757,2,1,2,3,1,5.5,6.5,1),
(11,20130103,74877,204757,2,1,3,3,1,42,43.5,1.5)
Go

执行上述 T-SQL 脚本后，您的销售样本数据仓库就准备好了，现在您可以在此数据仓库的基础上创建 OLAP 多维数据集。我将很快发布一篇文章，展示如何使用此数据仓库创建 OLAP 多维数据集。

在现实生活中，我们需要设计 SSIS ETL 包，以适当的值填充数据仓库的维度表和事实表，我们可以安排该包每天执行，并每天处理和填充前一天的数据到维度表和事实表中，这样我们的数据就可以为分析和报告做好准备了。

享受 SQL 智能。