超全面试总结——数据仓库 超详细!!!带答案!!!持续更新中~

超全面试总结——数据仓库

• 如何认识数据仓库
• 为什么需要进行数据仓库的建模设计
• 数据库与数据仓库的区别在于其功能定位
• 数据分层方案的设计遵循哪些原则
• 数据集市与传统数据库的主要区别是什么
• 数据库与数据集市在应用场景上有哪些不同
• 维度建模中常见的三种模式有哪些 星型 雪花 组合型
• 什么是事实视图及其作用
• 事实表设计的基本原则是什么
• 慢变维度在实际应用中有哪些特点
• 缅因表的设计遵循哪些核心原则
• ETL的核心作用是什么 它主要用于何种操作？
• OLAP技术的主要优势体现在哪里 它如何提升数据分析效率？
• 元数据用于描述数据库中的哪些元信息内容？
• 如何构建有效的ER模型并实现规范化设计？

如何理解数仓

• 数据仓库即通过综合收集各领域历史数据实现细粒度多层次剖析 ，从而为高层管理者和业务分析人员提供决策支持。
• 数据仓库相当于一个主题导向、高度集成且动态更新的信息集合体 ，其信息本身较为稳定，并主要服务于管理层的战略规划。

为什么要数据仓库建模

• 性能：一个优秀的模型能够支持我们高效检索所需的数据 ，从而降低数据读取时的IO吞吐量。
• 成本：通过降低数据冗余程度以及使计算结果被多次利用（即结果被重复利用），我们可以有效降低存储和计算成本。
• 效率：通过改善用户使用数据的质量并提升处理速度，我们可以显著提高整体工作效率。
• 解决统计口径不统一的问题 ，从而减少因计算错误导致的风险。

为什么要设计数据分层

需要建立一套行之有效的 数据组织与管理 方法体系 以便使我们的 数据体系更加有序
将复杂的问题拆解为多个步骤来完成 每一层都集中解决特定的问题
明确各层级的数据结构作用 在使用数据库时能够更加便捷地定位和理解信息
通过追踪数据来源与影响关系 可以快速准确地定位问题并清楚其潜在的危害范围
规范建立分层架构以生成通用中间层数据库 这样便能有效避免重复计算 能够通过大量的预处理工作提升应用系统效率（从而提高应用系统的效率）
这种做法会导致系统中存在大量冗余的数据存储
通过合理设计各层级之间的接口 显著提升了系统的可维护性和可扩展性 并确保输出的一致性与规范性

通用的数据分层设计

数据运营层 ODS、数据仓库层 DW(DWD、DWM、DWS)、数据应用层 ADS、维表层

数据运营层 ODS （Operational Data Store） 面向主题的

• 采用的是未经加工的原始数据
• 基于Sqoop导入的数据类型构建模型，并采用Canal实时监控MySQL的Binlog事件作为采集源的同时记录实时监控日志
• 在ODS层中因各端开发团队的技术差异（如PC端、H访问、小程序等）或其他问题导致用户访问日志被分割为多张表上传至该层级
• ODS层级具备备份功能，在特殊行业可能保存长达一年以上；普通企业则根据数据规模及存储资源决定保存时长，默认为3至6个月

数据仓库层 DW （ Data Warehouse）

存放我们要重点设计的数据仓库中间层数据

数据明细层 DWD （ Data Warehouse Detail）

• 对ODS层数据进行一定的清洗和主题归纳 * DWD层构建了一张用户行为日志表 ，在此过程中我们将PC端网页、H5应用、小程序以及原生APP的行为日志整合到同一表中，并统一字段名称、将枚举类字段进行翻译处理、剔除异常记录、规范字段命名以及统一时间字段表示以确保数据质量。
• 通过多维度压缩策略 ，将商品一级二级三级分类信息表、SKU商品表、SPU商品表以及商品品牌信息表整合汇总为一张综合维度表！

数据中间层 DWM (Data WareHouse Middle）

• 对明细数据进行常规维度上的初步汇总。 * 选择业务核心关注的关键字段来进行聚合运算（例如仅保留 '人' '商品' '设备'以及 '页面区域'这几个关键字段）。 * 统计出常见指标如访问次数与持续时长。 用户访问信息存储于天表中。
• 通过轻量级聚合运算生成中间表格来增强共性指标的可复用性，并降低重复处理程度。

数据服务层 DWS （Data WareHouse Servce）

• 亦称数据集市或宽表 * 将一个人在网站上的各项行为记录集中存储于一张表格中，并以此为基础搭建商品信息（如商品分类）、商铺运营情况以及地理位置信息（如地址编码）等主题类型的宽表框架；同时记录与用户相关的各项指标信息：包括每日登录频率（即登录次数）、访问频率（即访问次数）、使用购物车频率（即添加购物车次数）、下单频率（即购买商品数）以及多品类下单频率（即购买不同商品数），并按时间维度统计出每天和每月范围内的基础交易金额等关键数据。
  • 基于业务分类搭建字段较为繁多的宽表 ，以便支持后续业务需求查询分析以及数据分发等操作。

在实际应用中，在仅限于使用数据仓库的数据源（如DWD或ODS）进行宽表统计指标计算时会面临"计算负担过重"且"维度不足"的问题。通常采取的方法是在数据仓库管理（DWM）层先构建若干个小型中间视图，并通过整合这些视图最终生成一个综合性的数据仓库模型（DWS）。

dws和dwd是并行的

数据应用层 APP （Application）

• 为满足个性化服务需求而设计的应用数据
• 主要用于为数据分析提供支持的数据存储在报表系统的具体位置中。

• 层次维度（Hierarchy）
• 高频项（High-Frequency Items）: 通常涉及如用户信息、商品信息等这类典型场景。其数据规模可能达到千万级别或以上。
• 低频项（Low-Frequency Items）: 一般对应参数设置类的数据结构。例如：枚举映射关系的数据结构或日期时间格式的时间轴集合。

分层的原则是什么？

• 从对应用的支持来讲，我们希望越靠上层次，越对应用友好 。比如APP层，基本是完全为应用来设计的，很易懂，DWS层的话，相对来讲就会有一点点理解成本，然后DWM和DWD层就比较难理解了，因为它的维度可能会比较多，而且一个需求可能要多张表经过很复杂的计算才能完成。
• 从能力范围来讲，我们希望80%需求由20%的表来支持。直接点讲，就是大部分（80%以上）的需求，都用DWS的表来支持就行， DWS支持不了的，就用DWM和DWD的表来支持，这些都支持不了的极少一部分数据需要从原始日志中捞取。结合第一点来讲的话就是：80%的需求，我们都希望以对应用很友好的方式来支持，而不是直接暴露给应用方原始日志。
• 从数据聚合程度来讲，我们希望，越上层数据的聚合程度越高，看上面的例子即可，ODS和DWD的数据基本是原始日志的粒度，不做任何聚合操作，DWM做了轻度的聚合操作只 保留了通用的维度，DWS做了更高的聚合操作，可能只保留一到两个能表征当前描述主体的维度。从这个角度来看，我们又可以理解为我们是按照数据的聚合程度来划分数据层次的。

数据集市和数据仓库的区别

• 数据仓库 被视为大型化的基础设施，在此框架下各企业部门都能获得相应的决策支持能力；
  数据集市 则是一种小型化存储平台，在资源受限的情况下能够实现快速响应和高效运作，
  它主要服务于特定领域内的业务需求。

数据库和数据仓库有什么区别？

• 面向主题特性 ，操作型数据库旨在支持各类业务需求并满足完整性等约束与规范化的系统设计要求；而分析型数据库则致力于从复杂多样的业务中提取关键分析主题进行深入分析。
• 集成性 ，数据仓库通过整合来自不同来源的数据资源来构建其庞大的数据体系；
• 范围性 ，与部门级的数据资源不同，数据仓库的数据覆盖范围更为广泛；
• 历史性 ，相较于其他类型的存储系统而言，数据仓库的历史维度覆盖时间较长。

维度建模三种模式 星形、雪花、星座

包含一个事实表以及一组维（Dimension）表格组成的结构体系，在数据仓库架构中被广泛采用

分层结构 * 各个维度表能够向外延伸并包含多个子维度表 * 雪花模型相当于将星形模式的大维度表分解为较小的维度表 ，这种设计符合规范化原则 ，但在实际应用中较为少见

星型模式下实现多对多关系

能够实现共享关键维度

三种模式对比 * 细分星形模式的维表为更细致的部分，并以此实现其符合规范化的设计要求

• 提供星形模型支持包含多个独立的事实表的能力

事实表

• 在现实世界中发生的业务过程 ，会生成一系列可量化数据并记录于fact table中。

• 从最小单位来看，在这个表格中每一行对应一个特定的业务事件测量值。

• fact table的作用在于反映对分析主题的影响程度。例如，在这个案例中200元就是关键的事实数据信息。

• fact table通过外键机制与维度表建立关联关系，并采用join方式进行关联查询。

• fact table的数据类型通常为数值型且数量持续增长导致规模迅速扩大。

• 关于fact table有三种基本类型：

  • 事务fact table: 类似于MySQL的binlog日志机制，在每一次相关的业务变更都会被记录下来生成新的数据行
  • periodic snapshot fact table: 针对某个具体业务流程如订单收货进行数据切分划分时间粒度，默认为按天或按月划分时间段
  • cumulative snapshot fact table: 针对整个生命周期内多个相关业务流程进行综合反映例如创建订单→买家付款→卖家发货→买家确认收货等环节均会被单独记录到一张订单的事实数据行中

• 事实表设计8大原则

1. 尽量涵盖所有与业务流程相关的事实信息。
2. 仅包含与业务流程相关的事实数据，在订单下单这一业务流程中不应存储支付金额这一非相关性数据。
3. 将不可分割的事实数据分解为可独立存储的部分。
4. 在设计fact表时需明确粒度层次。
5. 同一张fact表中不应同时存在不同粒度的数据项。
6. 确保所有字段的数据类型保持一致。
7. 处理null值的方法通常是用0代替null。
8. 谨慎采用降维技术以提高表格的易用性，并通过降维实现对fact表的过滤查询、层次控制、排序操作以及主从关系管理等功能。

事实表设计方法

• 选择业务过程及确定事实表类型、声明粒度、确定维度、确定事实、冗余维度 *

思路：首先对需求进行深入分析，并从整体上对业务流程进行系统性评估；在此基础上识别核心操作环节；最终确定与目标相关的具体业务流程；通常采用行为动词来描述执行的具体活动

该订单流转的业务过程有 4 个 ：创建订单 → 买家付款 → 卖家发货 → 买家确认收货；

在涉及维度建模的业务流程中，请问您是希望专注于'买家付款'这一单一流程吗？或者是同时考虑'创建订单'以及'买家付款'这两个流程呢？

明确确定事实表类型需依据选定的业务流程。例如，在采用"买家付款"这一特定场景时，则应建立仅涵盖该单一交易环节的事实模型——即所谓的"单事务的事实模型"（用粗体表示）。若综合考虑全部四个核心环节，并要求共享各环节之间的执行时间间隔参数，则可构建包含全部四个环节的历史快照累积型的事实模型（用粗体表示）。

• 粒度定义 * 意味着精确地明确事实表每一行所代表的业务含义；清晰明确的粒度能够避免对实表中行的意思产生歧义，并保证所有事实按照统一的细节层次进行记录；

• 尽量选择最细级原子粒度（即最小化粒度过高带来的复杂性），从而确保应用灵活性最大化；

• 确定维度 * 完成之后实现了粒度声明这一目标，则意味着已经锁定了主键及其相关联的维度组合与相应字段。

• 在选择维度时应当遵循以下原则：应优先选取那些能够清晰反映业务过程所处环境的字段信息；例如，在淘宝订单 "付款事务事实表" 中，默认设置下若粒度设置为 "子订单"则涉及的相关字段包括：买家信息、卖家信息、商品详细信息、收货人基础信息等具体细节；此外还需考虑订单类型以及下单时间等因素。

识别所有相关事实 并筛选出与其相关的所有细节信息，并确保这些信息与所声明的事实表保持一致性

• 冗余维度

维度表

每个单目视图都有一个独立的功能模块。该功能模块可以与其他视图实现关联性调用。

表示对业务场景的关键要素进行分类。例如，在一次购物行为中

这样可以从这一事件中提取三个核心要素：时间、地点和商品

这些关键要素通常是固定的且数量有限

• 维度表设计原则 *

1. 尽量丰富维数 打造全面的数据模型

• 

什么是缓慢变化的维度

• 当业务数据库中的某些数据发生变化时（例如顾客的联系方式发生变动），如何将这些变化也反映到数据仓库中呢？一些基本信息的更改可能会导致数据分析出现相关问题。在现有架构下，

  • 缓慢渐变类型一：不记录历史信息而直接覆盖旧的数据
  • 缓慢渐变类型二：保存多条历史记录并引入代理键
  • 在代理键的设计过程中，
    • 解决这种缓慢渐变维度的同时保留历史信息记录，
    • 由于业务键通常较长，
      • 使用代理键可以实现高效管理，
      • 同时解决不同系统间可能出现相同业务键的问题。

• 第三种缓慢渐变类型 ：通过引入历史记录列，并采用不同字段存储变化的历史信息。该类型仅支持最多两次的变化记录存储。适用于分析涉及变化次数不超过两次的维度

维度表设计方法

• 构建数据仓库中的度量标准 。在企业级数据仓库设计过程中，在构建度量标准时需要确保每个度量项具有唯一性标识。
• 选定基准视图 。在此过程中, 选定基准视图时应优先考虑与业务系统直接同步的数据源. 例如, 在电商领域, 可以选择如淘宝商品这一维表作为基准视图。
• 识别关联数据源 。在实际应用中, 数据仓库通常由多个业务系统构成, 各个系统的数据之间可能存在一定的关联性. 因此, 需要依据业务流程梳理分析, 识别出与基准视图存在关联关系的具体数据源。
• 定义度量项组成要素 。根据需求选择来自基准视图或自关联数据源中的字段作为新度量项的关键构建要素.

如何维度建模

地区 - 商店 - 交易记录 - 顾客 - 产品 - 种类 - 卖主

哪些维度对于主题分析（涉及销售额）具有帮助？ 产品方面、客户层面、门店范围以及时间维度均有助于对销售额进行分析。

• 如何利用现有数据构建维表 * 产品属性可通过产品关系、供应商信息以及分类数据获取
  • 顾客特征可源自于客户资料
  • 零售店数量则基于门店布局与区域划分确定
  • 交易时间字段直接提取自订单记录的时间字段

如何衡量主题？案例中，核心目标为销售。销售与销售额更能体现销售表现。

• 如何用现有数据生成事实表 * 销售和销售额信息可以通过交易记录得到

维表不符合三层范式（3NF）的要求，各事实表均不符合第一层范式（1NF）的要求，在各个维表中将主键字段由xxID转换为xxKey字段。将此类字段称为代理码（surrogate key），它是通过自动生成的方式得到的一个自增主键，并且其仅用于解决'缓慢变化的维度'问题。该类字段主要用于数据仓库中的分析需求并解决数据随时间缓慢演变的问题

最常见的三种数据仓库建模体系

• 规范化数据仓库：基于标准化构建的分析型数据库系统中，在ETL流程完成后需完成ER图示与关系模式构建工作以形成规范化的数据库架构模式。各业务部门通常会从统一的数据集中心获取所需信息，并规定中心数据库不得直接被外部系统访问。
• 维度建模数据仓库：通过交错维度构建的数据存储架构能够整合多维视角下的分析信息，在整体上形成一个复杂的星座模型来表示各类分析型数据特征。
• 独立数据集市：公司内部各个组织实体均独立开展ETL流程并自主维护本地化数据中心集，并未形成统一的数据共享机制导致信息孤岛现象严重影响整体工作效率。
• 数据库架构对比：
  • 规范化数据中心：需全面实施标准化架构规划前期投入较大使用周期较长后期易出现维护问题
  • 维度数据中心：具备较强的灵活性适合应对业务环境快速变换需求开发门槛相对较低

什么是ETL？

ETL即为Extract-Transform-Load之简称，在实际应用中被用来描述从源端抽取、转换和加载数据至目标过程，在数据仓库体系中扮演着核心角色。

提取（Extract）主要针对各业务系统及不同服务器上的分散数据 ，在深入理解数据定义之后规划所需的数据源及其相关的数据规范，并明确了可操作化的具体数据源。随后制定了包括增量抽取策略和缓慢渐变处理规则在内的具体操作规范。

转换（Transform）作为一种关键的数据处理技术，在构建数据分析架构时发挥着重要作用 ，它通过一系列的操作将数据从业务建模映射到分析建模的过程中。借助ETL工具的可视化拖拽界面，并无需编写额外代码即可集成预定义数据转换功能模块、自定义处理逻辑模块以及第三方扩展组件。能够实现从业务建模到数据分析模型的关键转变，并提供详细的执行日志追踪功能以辅助优化数据分析架构。

装载（Load）主要是负责接收经过转换的数据并将其存入数据仓库中 ，采用直连至数据库的方式实现这一功能，并能充分展现其高效性。在实际应用过程中，可根据需求随时调整数据提取方式，并能够便捷地整合至其他管理系统中。

在传统环境下运行时的数据量较小且计算逻辑较为简单，在这种情况下可以直接通过ETL工具进行数据转换操作（T），将处理后的结果导入目标数据库系统。然而，在大数据环境下的规模不断扩大导致计算复杂度显著提升，在这种情况下必须将数据清洗作业置于运算能力更为强大的分布式计算引擎中进行处理；而当扩展至大数据环境时，则演变为ELT流程（Extract, Transform, Load），即ETL扩展为ELT流程

ETL加载策略采用以下三种方式

常用的ETL工具

• Sqoop 是 Apache 开源项目中一款专门用于在 Hadoop 和各种关系型数据库服务器之间传输数据的应用工具。

• 它支持从 MySQL 或 Oracle 等关系型数据库向 Hadoop 的 HDFS 导入数据，并且也能将存储在 HDFS 中的数据导出回各种关系型数据库。

• Sqoop 命令的核心功能是将其执行过程转化为 MapReduce 程序。该工具主要分为两种基本操作：导入操作（import）和导出操作（export）。此外，在这两种基本操作下又分别支持基于表的查询策略（table-based queries）和基于查询的策略（query-based strategies）。

• Sqoop 的数据传输模式可分为增量复制模式（incremental replication）和全量复制模式（full replication）。

• Kettle * 是一款国外免费开源的可视化ETL工具软件包，在Windows系统中运行时提供良好的用户体验，在Linux和Unix环境下同样表现出色。它基于Java开发，并支持高效的稳定的数据抽取流程。

canal是一款全Java开发的开源工具。它基于增量日志解析机制实现增量数据的实时订阅与消费功能，并现主要支持MySQL。

联机分析处理 OLAP

OLAP需要以丰富的历史数据作为基础，并结合不同时间段的变化来深入分析多个维度的数据以及经过汇总整理的数据。

Online Analytical Processing（OLAP），其主要功能是通过多维度的角度对数据进行分析，并能够灵活支持汇总分析（Aggregation）、深入分析（Drill-down）以及横向汇总（Pivot Analysis）等多种操作模式；其核心作用即是呈现集成性决策信息，在商业领域中广泛应用于商业智能（Business Intelligence）、数据分析（Data Analysis）以及数据库管理（Database Management）等领域

OLAP分类 * MOLAP，基于多维数组的存储模型 ，也是OLAP最初的形态，特点是对数据进行预计算，以空间换效率，明细和聚合数据都保存在cube中 。但生成cube需要大量时间和空间。
* ROLAP，基于关系模型进行存储数据 ，不需要预计算，按需即时查询。明细和汇总数据都保存在关系型数据库事实表中 。其特点是与事务实体对应，关系清晰；但一般需要较为复杂的数据准备。在响应前端需求时，一般较快，但取决于计算引擎能力。
* HOLAP，混合模型，细节数据以ROLAP存放，聚合数据以MOLAP存放 。这种方式相对灵活，且更加高效。可按企业业务场景和数据粒度进行取舍，没有最好，只有最适合。

OLAP核心操作 * drilling：层次的变化是从粗粒度向细粒度推进，在汇总的基础上深入细化至具体细节。例如，在季度销售数据分析中可深入至每月的数据。
* 上升聚合：drilling 的逆向操作是从具体细节上升至宏观层面。
* 切块：涉及其余维度中的三个维度的数据。
* 切片：仅涉及其中两个维度的数据。
* 交换位置：将某些维度的位置或行列位置进行交换或置换。

OLAP选型： kylin 和 druid

OLAP和OLTP的区别

在线事务处理系统（OLTP），该系统的在线事务处理能力主要关注日常运营所需的数据操作，并涉及数据增删改查等核心功能。

OLTP	OLAP
对象	业务开发人员	分析决策人员
功能	日常事务处理	面向分析决策
模型	关系模型	多维模型
数据量	几条或几十条记录	＞百万于万条记录
操作类型	增、删、查、改(CRUD)	查询为主
总体概括	联机事务处理	在线分析处理

元数据（Metadata）

一个管理元数据信息的系统，能够提供方便的元数据的操作和查询操作

metadata 即所谓的元数据。它涵盖 database、table、column names、partitions 信息以及 bucketing 信息等相关的元数据内容。 元数据通常建议存放在 Derby数据库中 ，如果需要的话也可以选择将它们存储在 关系型数据库 中以增强管理效能。

ER建模

• 实体-联系图(Entity Relationship Diagram)，采用表示实体类型、属性和关系的方式 ，用于构建现实世界的概念模型。

• 实体：矩形；属性：椭圆；联系：菱形；基数约束共有四种情况

  • 一个矩形对应多个椭圆
  • 一个矩形对应零个或多个椭圆
  • 一个矩形对应一个椭圆
  • 一个矩形对应零个或仅一个椭圆

• 地址 attribute 包含省份、城市和街道。

• 多值 attribute 表示为双层椭圆的 attribute 存在多值情况：一个职工可能存在多个电话号码。

• 派生 attribute 由其他 attribute 或其他数据（如当前日期）表示。

• 可选 attribute 通常用在具有可选项的情况中：例如职工奖金作为分 attribute 可能出现也可能不存在数值。

• 在基数约束中可以加入最大值和最小值限制以及联系角色信息。