数据仓库入门知识

     

基本规范或优化

字段命名规范:

语言:

命名应该使用英文单词，避免使用拼音，最好不要使用拼音简写，如果使用拼音简写应该有规范。命名不允许使用中文或者特殊字符。

英文单词使用用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名。

当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的意义。

当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。

大小写:

名称一律小写，以方便不同数据库移植，以及避免程序调用问题。

单词分隔：

命名的各单词之间可以使用下划线进行分隔。

保留字:

命名不允许使用SQL保留字。 比如 name,date

字段名称:

同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。

不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。

 

Sql规范与优化相关:

1.尽量在最外层的查询的时候使用汉字别名，在子查询中使英文代替

错误示范:

Select b.id,a.`日期`

(Select date_time as `日期` from a ) a

Left join b on a.`日期`=b.date_time

改成

Select b.id,a.date_time as `日期`

(Select date_time as date_time from a ) a

Left join b on a.date_time=b.date_time

 

2.尽量查询只需要的字段

   Select *  首先会去元数据中遍历所有的列会增加数据库的负担

错误示范:

select * from b;

改正

Select id,name from b;

 

3.如果需要大数据量的情况下 先过滤不需要的数据再join

错误示范:

select a.name,b.name from

   A left join b  on a.id=b.id where a.dt=’2018-07-04’and b.dt=’2018-07-04’  

改正：

Select a.name,b.name from

(Select name from a where dt=’2018-07-04’)a

Left join

(select name from b where dt=’2018-07-04’) b  on a.id=b.id

 

注意:

select a.name,b.core from test a

left join test2 b on a.id= b.id

where b.core=3

 

select a.name,b.core from test a left join

(select id,core from test2 where core=3)b on a.id=b.id ;

 

以上这两sql的结果是不一样的 所以做优化要注意逻辑

 

数据库和数据仓库的区别

数据库：是一种逻辑概念，用来存放数据的仓库。传统的关系型数据库的主要应用OLTP（On-Line Transaction Processing 联机事务处理），主要是基本的、日常的事务处理，,主要采取3NF的实体关系模型存储数据。目前市面上流行的数据库都是二维数据库。如：Oracle、DB2、MySQL、Sybase、MS SQL Server等。

 

数据仓库：是数据库概念的升级。从逻辑上理解，数据库和数据仓库没有区别，都是通过数据库软件实现的存放数据的地方，只不过从数据量来说，数据仓库要比数据库更庞大得多。数据仓库主要用于数据挖掘和数据分析，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果,辅助领导做决策。OLAP（On-Line Analytical Processing 联机分析处理）

数据仓库的特点

1.数据仓库的数据是面向主题的

与传统数据库面向应用进行数据组织的特点相对应，数据仓库中的数据是面向主题进行 组织的。什么是主题呢？首先，主题是一个抽象的概念，是较高层次上企业信息系统中的数 据综合、归类并进行分析利用的抽象。通俗讲就是分析的对象，比如 商家，客户，流量， 财务。

2. 数据仓库的数据是集成的

数据仓库的数据来源有很多方面，关系型数据库，非关系型数据库，日志文件。

3.数据仓库的数据是不可更新的

数据仓库的数据主要供企业决策分析之用，所涉及的数据操作主要是数据查询，一般情况下并不进行修改操作。

 4. 数据仓库的数据是随时间不断变化的

  数据仓库中的数据不可更新是针对应用来说的，也就是说，数据仓库的用户进行分析处理时是不进行数据更新操作的。但并不是说，在从数据集成输入数据仓库开始到最终被删除的整个数据生存周期中，所有的数据仓库数据都是永远不变的

1. 数据仓库随时间变化不断增加新的数据内容。
2. 数据仓库随时间变化不断删去旧的数据内容
3. 数据仓库中包含有大量的综合数据，这些综合数据中很多跟时间有关，如数据经     常按照时间段进行综合，或隔一定的时间片进行抽样等等。

为什么需要数据仓库模型

首先我们需要了解整个数据仓库的建设的发展史。

数据仓库的发展大致经历了这样的三个过程：

• 简单报表阶段：这个阶段，系统的主要目标是解决一些日常的工作中业务人员需要的报表，以及生成一些简单的能够帮助领导进行决策所需要的汇总数据。这个阶段的大部分表现形式为数据库和前端报表工具。
• 数据集市阶段：这个阶段，主要是根据某个业务部门的需要，进行一定的数据的采集，整理，按照业务人员的需要，进行多维报表的展现，能够提供对特定业务指导的数据，并且能够提供特定的领导决策数据。
• 数据仓库阶段：这个阶段，主要是按照一定的数据模型，对整个企业的数据进行采集，整理，并且能够按照各个业务部门的需要，提供跨部门的，完全一致的业务报表数据，能够通过数据仓库生成对对业务具有指导性的数据，同时，为领导决策提供全面的数据支持。

一般来说，数据模型的建设主要能够帮助我们解决以下的一些问题

• 进行全面的业务梳理，改进业务流程。在业务模型建设的阶段，能够帮助我们的企业或者是管理机关对本单位的业务进行全面的梳理。通过业务模型的建设，我们应该能够全面了解该单位的业务架构图和整个业务的运行情况，能够将业务按照特定的规律进行分门别类和程序化，同时，帮助我们进一步的改进业务的流程，提高业务效率，指导我们的业务部门的生产。
• 建立全方位的数据视角，消灭信息孤岛和数据差异。通过数据仓库的模型建设，能够为企业提供一个整体的数据视角，不再是各个部门只是关注自己的数据，而且通过模型的建设，勾勒出了部门之间内在的联系，帮助消灭各个部门之间的信息孤岛的问题，更为重要的是，通过数据模型的建设，能够保证整个企业的数据的一致性，各个部门之间数据的差异将会得到有效解决。

数据仓库建模的好处：

性能和效率:良好的数据模型可以帮助我们快速的查询所需要的数据,减少数据的减少数据的I/O吞吐，可以改善用户使用数据的体验,提高使用数据的效率(比如 尽量好的维度表 尽量少的join 操作，宽表,汇总表)

成本:良好的数据模型能极大的减少不必要的数据冗余,也可以计算成本复用,降低存储和计算成本

质量:良好的数据模型能够改善数据统计口径的不一致,减少数据计算错误的可能性

数据仓库的架构分层：

数据仓库标准上可以分为四层：ODS（临时存储层 Operational Data Store）、DWD（数据明细层 Data Warehouse Detail ）、DWS（数据汇总层 Data Warehouse Summary）、APP（应用层 Application Data Store）。

 

 

数据仓库建模方法

1.范式建模法

范式建模法其实是我们在构建数据模型常用的一个方法，该方法的主要由 Inmon 所提倡，主要解决关系型数据库得数据存储，利用的一种技术层面上的方法。目前，我们在关系型数据库中的建模方法，大部分采用的是三范式建模法。

1NF:字段不可分; 
2NF:有主键，非主键字段依赖主键; 
3NF:非主键字段不能相互依赖; 

解释: 
1NF:原子性 字段不可再分 

2NF:唯一性 一个表只说明一个事物; 
3NF:每列都与主键有直接关系，不存在传递依赖; 

 

一个符合第三范式的关系必须具有以下三个条件 :

1NF(第一范式)：

数据库表中的字段都是单一属性的，不可再分，数据库表中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

比如 电话字段 包括手机电话和家庭电话的组合 或者一个字段再不同的情况下 内容含义不同。

我们先来看一张不符合1NF的表1-1

饭卡充值收费表：

card_no	student_no	student_name	sex	department	money
001	021101	小明	男	教育学院，心理系，1班	100

修改后：

card_no	student_no	student_name	sex	department	major	class	money
001	021101	小明	男	教育学院	心理系	1	100

 

2NF(第二范式):

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF),每个非主属性必须完全依赖于整个主键，而非主键的一部分

card_no	student_no	student_name	sex	department	major	class	money
001	021101	小明	男	教育学院	心理系	1	100

修改后:

 

card_no	money
001	100

student_no	student_name	sex	department	major	class	card_no
021101	小明	男	教育学院	心理系	1	001

 

3NF(第三范式)

每个非主属性不能依赖于其他关系中的属性，因为这样的话，这种属性应该归到其他关系中去

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

第三范式

不存在非主属性对码的传递性依赖以及部分性依赖 ，
StudyNo   |   Name   |   Sex   |      Email         |      bounsLevel   |   bouns

20040901      john         Male       kkkk@ee.net   优秀                    $1000

20040902     mary         famale    kkk@fff.net       良                         $600

这个完全满足了第二范式,但是bounsLevel和bouns存在传递依赖

更改为：

StudyNo   |   Name   |   Sex   |      Email         |      bouunsNo

20040901      john         Male       kkkk@ee.net   1

20040902     mary         famale    kkk@fff.net       2

bounsNo   |   bounsLevel   |   bouns

1                   优秀                $1000

 2                 良                   $600

 

Inmon 的范式建模法的最大优点就是从关系型数据库的角度出发，结合了业务系统的数据模型，能够比较方便的实现数据仓库的建模。但其缺点也是明显的，由于建模方法限定在关系型数据库之上，在某些时候反而限制了整个数据仓库模型的灵活性，性能等，特别是考虑到数据仓库的底层数据向数据集市的数据进行汇总时，需要进行一定的变通才能满足相应的需求。

 

2.维度建模法

维度建模法，Kimball 最先提出这一概念。其最简单的描述就是，按照事实表，维表来构建数据仓库，数据集市。

根据事实表和维度表的关系，又可将常见的模型分为星型模型和雪花型模型。

星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，不存在渐变维度，所以数据有一定的冗余，如在地域维度表中，存在国家 A 省 B 的城市 C 以及国家 A 省 B 的城市 D 两条记录，那么国家 A 和省 B 的信息分别存储了两次，即存在冗余。

图1. 销售数据仓库中的星型模型

 

图 2. 销售数据仓库中的雪花型模型

比较：

1.数据优化

雪花模型使用的是规范化数据，也就是说数据在数据库内部是组织好的，以便消除冗余，因此它能够有效地减少数据量。通过引用完整性，其业务层级和维度都将存储在数据模型之中。

相比较而言，星形模型实用的是反规范化数据。在星形模型中，维度直接指的是事实表，业务层级不会通过维度之间的参照完整性来部署。

2.性能

雪花模型在维度表、事实表之间的连接很多，因此性能方面会比较低。

3.ETL

雪花模型加载数据集市，因此ETL操作在设计上更加复杂，而且由于附属模型的限制，不能并行化。

星形模型加载维度表，不需要再维度之间添加附属模型，因此ETL就相对简单，而且可以实现高度的并行化

星型模型因为数据的冗余所以很多统计查询不需要做外部的连接，因此一般情况下效率比雪花型模型要高。星型结构不用考虑很多正规化的因素，设计与实现都比较简单。雪花型模型由于去除了冗余，有些统计就需要通过表的联接才能产生，所以效率不一定有星型模型高。正规化也是一种比较复杂的过程，相应的数据库结构设计、数据的 ETL、以及后期的维护都要复杂一些。因此在冗余可以接受的前提下，实际运用中星型模型使用更多，也更有效率。

维度建模:

基本概念：

事实表:事实是各个维度的的交点,是对某个特定事件的度量。若干个一致的事实能够被组合到一个公共的结构中就是事实表。

维度表:维表是用户分析决策的角度。

 

四步维度建模方法:

第一步:选择业务过程

业务过程通常使用行为动词表示业务执行的活动,通俗讲就是确定要分析的业务流程

第二步:声明粒度

粒度的声明是事实表建模非常重要的一步，意味着精确定义事实表的每一行所表示的业务含义，粒度传递的是与事实表度量有关的细节层次。明确的粒度能确保对事实表中行的意思的理解不会产生混淆，保证所有的事实按照同样的细节层次记录。
应该尽量选择最细级别的原子粒度，以确保事实表的应用具有最大的灵活性。

第三步:确定维度

完成粒度声明以后，也就意味着确定了主键，对应的维度组合以及相关的维度字段就可以确定了，应该选择能够描述清楚业务过程所处的环境的维度信息。

维度提供围绕某一业务过程所涉及的“谁、什么、何处、何时、为什么、如何”等背景。维度表包含BI应用所需要的用于过滤及分类事实的描述属性。

 

第四步:确定事实

事实可以通过回答“过程的度量是什么”来确定。事实涉及来自业务过程时间的度量，基本上都是以数量值表示。应该选择与业务过程有关的所有事实，且

事实的粒度要与所声明的事实表的粒度一致

 

事实表基础:

若干个一致的事实能够被组合到一个公共的结构中就是事实表。

发生在现实世界中的操作型事件,其所产生的可度量值，存储在事实表中。除了数字度量外，事实表总是包含外键,用于关联与之相关的维度。

事实表中的一条记录所表达的业务细节程度称为粒度。通常粒度可以听过两种方式来表达:一种是维度属性组合所表示的细节程度；一种是所表示的具体业务含义。

 

事实的类型:可加、半可加、不可加事实

事实表中的数值数字度量可划分为三类。

可加事实:可以按照与事实表关联的任意维度进行汇总。

半可加事实:只能按照特定的维度汇总，比如库存可以按照地点和商品进行汇总，而按照时间维度把一年中每个月的库存累加起来则毫无意义。

不可加事实:比如比率

 

事实表类型：

事物事实表：事务事实表用来描述业务过程，眼踪空间或时间上某点的度量事件，保存的是最原子的数据 比如下单,发货,退款,提供最明细的数据，用来定义业务过程的原子粒度的行为。通俗的讲,比如就是订单表的每一行记录。

周期快照事实表：周期快照事实表中的每行汇总了发生在某一标准时间，如某一天，某一周，某一月的度量时间。粒度是周期性的，不是个体的事务。

比如:商家汇总表,指标有如,距离今天一共服务多少单,距离今天一共赚多少钱，截止一共服务多少客户

累积快照事实表: 累积快照事实表的行汇总了发生在过程开始和结束之间可预测步骤内的度量时间。如订单服务为例,可以有这几个字段 订单id,下单时间,支付时间,服务时间,完成时间,取消时间

 

维度表：

维度是维度建模的基础和灵魂。在维度建模中。将度量称为’事实’，将环境描述’维度’，

维度是用于分析事实所需要的多样环境。

 

第一步：选择维度或者新建维度。作为维度建模的核心，在企业及数据仓库中必须保证维度的唯一性。

第二步:确定主维表，一般是ods表，与业务系统直接同步

第三步：确定相关相关维表。数据仓库是业务源系统的数据整合，不同业务系统或者同一月系统中的表之前存在关联性。根据对业务的梳理，确定哪些表和主维表存在关联关系，并选择其中的某些表用于生维度属性。

第四步：确定维度属性。分两个阶段：一个阶段从主维度表中选择维度属性或者生成新的维度属性。第二个阶段从相关维表中选择维度属性或者生成新的维度属性

 

无事实的事实表：

比如 浏览日志表 或者 记录 维度和维度的关系表

数据治理：

元数据管理：

技术元数据：

1、建表对每个字段必须有注释。

2、库名，表名，数据加载策略，数据更新周期，源表，字段名，字段类型，字段含义

3、码值类：有可供查询的码值集合，用来整理归纳，所有表的所有字典字段的类型和描述信息。

4、数据表新建，变更，删除等。
5、Mapping文档
（业务->BI）
系统名，表名，数据加载策略，数据更新周期，接口实现方式，源表清单，字段名，字段类型，主键，
非空，默认值，举例，ETL  计算口径，备注（数据字典相关定义等，比如枚举值，取值范围，业务说明等）

业务元数据：

1.业务知识整理

业务系统相关业务知识库整理，按照系统业务域整理，业务系统说明，操作流程说明等。

比如:数据来源 清洗的 生命周期

比如:SDK埋点规范

比如:各个类型日志的含义，开发流程

比如:业务线具体业务含义

2、业务表的指标含义，统计的口径，业务的含义，维度的说明

3、数据开发规范

   数据开发流程 业务评审 研发设计评审 开发 测试