目录
第1章 业务背景及项目需求
1.1 广告业务术语
1）广告主
广告主是指为推销商品或者提供服务，自行或者委托他人设计、制作、发布广告的组织或者个人。
2）广告媒体
广告媒体是用于向公众发布广告的传播载体。例如大家所熟悉的今日头条、抖音等。
3）广告受众
各个媒体的普通用户，用户在享受这些媒体的免费服务的同时，会被动的接受广告。
注：在本项目中我们的身份一家小型电商企业，是一个向广告媒体投放广告的广告主。
1.2 广告业务系统架构
1.2.1 互联网广告发展历程
自20世纪90年代初期第一条互联网广告诞生以来，互联网广告，尤其是广告的交易模式，不断发展变化，下面简要介绍几个重要的发展阶段。
1）传统销售模式
起初的交易模式非常简单，广告媒体直接向广告主进行广告位的售卖。这时的广告计价模式通常为CPT（Cost Per Time），即广告主按照广告的展示时长向广告媒体支付费用。
这种模式下，广告媒体需要自己接洽多家广告主，管理广告主的排期；而广告主需要和多家广告媒体谈判。这对于双方都是比较低效的。
2）广告联盟模式（Ad Network）
随着互联网行业的发展，涌现出了大量的中小媒体。这些小媒体同样掌握着一定的流量和变现的需求。
但是传统的销售模式，在此时就不太适用了，因为小媒体数量众多，让广告主与这些小媒体分别进行谈判是很不现实的。因此广告联盟（Ad Network）就诞生了。
广告联盟负责联络多家中小媒体，形成统一的定价，向广告主进行销售。
3）广告交易平台模式（Ad Exchange）
广告联盟在一定程度解决了广告主和众多中小媒体的交易问题。但是随着市场的发展，出现了很多的广告联盟，这样一来新的问题又出现了，这其中非常重要的一个就是定价问题。
首先，每个广告联盟都可能会有自己的定价策略。其次，广告联盟之间还存在转卖现象（例如：广告联盟A有一个小媒体资源一直没卖出去，而广告联盟B有一个广告主，但手头没有合适的媒体资源，这时广告联盟A与B之间就会出现转卖行为）。这样一来广告的价格体系就会愈发混乱，愈发不透明。
正是在这样的背景下，广告交易平台（Ad Exchange）出现了。Ad Exchange相当于一个拍卖市场，其联合了流量需求方（广告主）和流量供给方（广告媒体）。流量供给方将广告位放到交易平台进行拍卖，流量需求方在交易平台根据自身需求进行购买。流量定价靠实时竞价（RTB，Real Time Bidding）获得。这样一来定价就会更加公开透明。
大致的竞价过程如下：
需要注意的是，广告位的竞价过程要在极短的时间内完成，所以各广告主不可能像真正的拍卖市场那样人为的参与竞价。真实的竞价过程如下：
其中DSP（Demand Side Platform，需求方平台）的服务对象是广告主。其作用主要有两个，其一就是上图所提到的辅助广告主参与实时竞价，帮助广告主以最合理的价格购买到广告位；其二就是帮助广告主选择广告的投放对象，实现广告的精准投放。
1.2.2 广告业务系统架构
下面是我们广告相关的系统架构图，其中DSP、AD Exchange等均由广告商提供；广告管理平台是公司内部用于管理广告投放信息的一个平台，其具体作用为管理广告信息，并对接各家广告投放平台，辅助我们完成广告投放工作。
1.3 项目需求
本项目的需求是对我们投放到各广告平台的每个广告的曝光和点击行为进行统计，并识别异常流量，以达到监测广告投放效果的目的。以下是该项目的具体统计指标。
| 指标编号 | 指标说明 | 报表交互要求 |
| 1 | 各省市的点击次数和曝光次数统计（包括正常流量以及异常流量） |
|
| 2 | 各广告平台的点击次数和曝光次数（包括正常流量以及异常流量） |
|
| 3 | 各操作系统的点击次数和曝光次数（包括正常流量以及异常流量） |
|
| 4 | 各广告在每个小时的点击次数和曝光次数（包括正常流量和异常流量） |
|
最终的报表展示效果如下：
第2章 数据仓库概述
2.1 数据仓库概念
数据仓库是一个为数据分析而设计的企业级数据管理系统。数据仓库可集中、整合多个信息源的大量数据，借助数据仓库的分析能力，企业可从数据中获得宝贵的信息进而改进决策。同时，随着时间的推移，数据仓库中积累的大量历史数据对于数据科学家和业务分析师也是十分宝贵的。
数据仓库的典型架构如下：
2.2 数据仓库模型
数据模型就是数据组织和存储方法，它强调从业务、数据存取和使用角度合理存储数据。数据仓库中，常用的模型为维度模型。
2.2.1 维度模型概述
维度模型由数据仓库大师Ralph Kimball提出，其核心思想是将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程，而维度通常对应业务过程发生时所处的环境。
注：业务过程可以概括为一个个不可拆分的行为事件，例如电商交易中的下单，取消订单，付款，退单等，都是业务过程。
下图为一个典型的维度模型，其中位于中心的SalesOrder为事实表，其中保存的是下单这个业务过程的所有记录。位于周围每张表都是维度表，包括Date（日期），Customer（顾客），Product（产品），Location（地区）等，这些维度表就组成了每个订单发生时所处的环境，即何人、何时、在何地下单了何种产品。从图中可以看出，模型十分清晰、简洁。
维度建模以数据分析作为出发点，为数据分析服务，因此它关注的重点的用户如何更快的完成需求分析以及如何实现较好的大规模复杂查询的响应性能。
2.2.2 维度模型之事实表
2.2.2.1 事实表概述
事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程来设计。其包含与该业务过程有关的维度引用（维度表外键）以及该业务过程的度量（通常是可累加的数字类型字段）。
事实表通常比较“细长”，即列较少，但行较多，且行的增速快。
事实表有三种类型：分别是事务型事实表、周期型快照事实表和累积型快照事实表，每种事实表都具有不同的特点和适用场景，下面逐个介绍。
2.2.2.2 事务型事实表
1）事务事实表概述
事务事实表用来记录各业务过程，它保存的是各业务过程的原子操作事件，即最细粒度的操作事件。粒度是指事实表中一行数据所表达的业务细节程度。
事务型事实表可用于分析与各业务过程相关的各项统计指标，由于其保存了最细粒度的记录，可以提供最大限度的灵活性，可以支持无法预期的各种细节层次的统计需求。
2）设计流程
设计事务事实表时一般可遵循以下四个步骤：
选择业务过程→声明粒度→确认维度→确认事实
（1）选择业务过程
在业务系统中，挑选我们感兴趣的业务过程，业务过程可以概括为一个个不可拆分的行为事件，例如电商交易中的下单，取消订单，付款，退单等，都是业务过程。通常情况下，一个业务过程对应一张事务型事实表。
（2）声明粒度
业务过程确定后，需要为每个业务过程声明粒度。即精确定义每张事务型事实表的每行数据表示什么，应该尽可能选择最细粒度，以此来应各种细节程度的需求。
典型的粒度声明如下：
订单事实表中一行数据表示的是一个订单中的一个商品项。
（3）确定维度
确定维度具体是指，确定与每张事务型事实表相关的维度有哪些。
确定维度时应尽量多的选择与业务过程相关的环境信息。因为维度的丰富程度就决定了维度模型能够支持的指标丰富程度。
（4）确定事实
此处的“事实”一词，指的是每个业务过程的度量值（通常是可累加的数字类型的值，例如：次数、个数、件数、金额等）。
经过上述四个步骤，事务型事实表就基本设计完成了。第一步选择业务过程可以确定有哪些事务型事实表，第二步可以确定每张事务型事实表的每行数据是什么，第三步可以确定每张事务型事实表的维度外键，第四步可以确定每张事务型事实表的度量值字段。
3）事务事实表的不足之处
事务型事实表可以保存所有业务过程的最细粒度的操作事件，故理论上其可以支撑与各业务过程相关的各种统计粒度的需求。但对于某些特定类型的需求，其逻辑可能会比较复杂，或者效率会比较低下。例如：
（1）存量型指标
例如商品库存，账户余额等。此处以电商中的虚拟货币为例，虚拟货币业务包含的业务过程主要包括获取货币和使用货币，两个业务过程各自对应一张事务型事实表，一张存储所有的获取货币的原子操作事件，另一张存储所有使用货币的原子操作事件。
假定现有一个需求，要求统计截至当日的各用户虚拟货币余额。由于获取货币和使用货币均会影响到余额，故需要对两张事务型事实表进行聚合，且需要区分两者对余额的影响（加或减），另外需要对两张表的全表数据聚合才能得到统计结果。
可以看到，不论是从逻辑上还是效率上考虑，这都不是一个好的方案。
（2）多事务关联统计
例如，现需要统计最近30天，用户下单到支付的时间间隔的平均值。统计思路应该是找到下单事务事实表和支付事务事实表，过滤出最近30天的记录，然后按照订单id对两张事实表进行关联，之后用支付时间减去下单时间，然后再求平均值。
逻辑上虽然并不复杂，但是其效率较低，应为下单事务事实表和支付事务事实表均为大表，大表join大表的操作应尽量避免。
可以看到，在上述两种场景下事务型事实表的表现并不理想。下面要介绍的另外两种类型的事实表就是为了弥补事务型事实表的不足的。
2.2.2.3 周期型快照事实表
周期快照事实表以具有规律性的、可预见的时间间隔来记录事实，主要用于分析一些存量型（例如商品库存，账户余额）或者状态型（空气温度，行驶速度）指标。
对于商品库存、账户余额这些存量型指标，业务系统中通常就会计算并保存最新结果，所以定期同步一份全量数据到数据仓库，构建周期型快照事实表，就能轻松应对此类统计需求，而无需再对事务型事实表中大量的历史记录进行聚合了。
对于空气温度、行驶速度这些状态型指标，由于它们的值往往是连续的，我们无法捕获其变动的原子事务操作，所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样，构建周期型快照事实表。
2.2.2.4 累积型快照事实表
累计快照事实表是基于一个业务流程中的多个关键业务过程联合处理而构建的事实表，如交易流程中的下单、支付、发货、确认收货业务过程。
累积型快照事实表通常具有多个日期字段，每个日期对应业务流程中的一个关键业务过程（里程碑）。
| 订单id | 用户id | 下单日期 | 支付日期 | 发货日期 | 确认收货日期 | 订单金额 | 支付金额 |
| 1001 | 1234 | 2020-06-14 | 2020-06-15 | 2020-06-16 | 2020-06-17 | 1000 | 1000 |
累积型快照事实表主要用于分析业务过程（里程碑）之间的时间间隔等需求。例如前文提到的用户下单到支付的平均时间间隔，使用累积型快照事实表进行统计，就能避免两个事务事实表的关联操作，从而变得十分简单高效。
2.2.3 维度模型之维度表
2.2.3.1 维度表概述
维度表是维度建模的基础和灵魂。前文提到，事实表紧紧围绕业务过程进行设计，而维度表则围绕业务过程所处的环境进行设计。维度表主要包含一个主键和各种维度字段，维度字段称为维度属性。
2.2.3.2 维度表设计步骤
1）确定维度（表）
在设计事实表时，已经确定了与每个事实表相关的维度，理论上每个相关维度均需对应一张维度表。需要注意到，可能存在多个事实表与同一个维度都相关的情况，这种情况需保证维度的唯一性，即只创建一张维度表。另外，如果某些维度表的维度属性很少，例如只有一个**名称，则可不创建该维度表，而把该表的维度属性直接增加到与之相关的事实表中，这个操作称为维度退化。
2）确定主维表和相关维表
此处的主维表和相关维表均指业务系统中与某维度相关的表。例如业务系统中与商品相关的表有sku_info，spu_info，base_trademark，base_category3，base_category2，base_category1等，其中sku_info就称为商品维度的主维表，其余表称为商品维度的相关维表。维度表的粒度通常与主维表相同。
3）确定维度属性
确定维度属性即确定维度表字段。维度属性主要来自于业务系统中与该维度对应的主维表和相关维表。维度属性可直接从主维表或相关维表中选择，也可通过进一步加工得到。
确定维度属性时，需要遵循以下要求：
（1）尽可能生成丰富的维度属性
维度属性是后续做分析统计时的查询约束条件、分组字段的基本来源，是数据易用性的关键。维度属性的丰富程度直接影响到数据模型能够支持的指标的丰富程度。
（2）尽量不使用编码，而使用明确的文字说明，一般可以编码和文字共存。
（3）尽量沉淀出通用的维度属性
有些维度属性的获取需要进行比较复杂的逻辑处理，例如需要通过多个字段拼接得到。为避免后续每次使用时的重复处理，可将这些维度属性沉淀到维度表中。
2.2.3.3 维度设计要点
1）规范化和范规范化
规范化是指使用一系列范式设计数据库的过程，其目的是减少数据冗余，增强数据的一致性。通常情况下，规范化之后，一张表的字段会拆分到多张表。
反规范化是指将多张表的数据冗余到一张表，其目的是减少join操作，提高查询性能。
在设计维度表时，如果对其进行规范化，得到的维度模型称为雪花模型，如果对其进行反规范化，得到的模型称为星型模型。
数据仓库系统的主要目的是用于数据分析和统计，所以是否方便用户进行统计分析决定了模型的优劣。采用雪花模型，用户在统计分析的过程中需要大量的关联操作，使用复杂度高，同时查询性能很差，而采用星型模型，则方便、易用且性能好。所以出于易用性和性能的考虑，维度表一般是很不规范化的。
2）维度变化
维度属性通常不是静态的，而是会随时间变化的，数据仓库的一个重要特点就是反映历史的变化，所以如何保存维度的历史状态是维度设计的重要工作之一。保存维度数据的历史状态最常用的做法就是全量快照表。
离线数据仓库的计算周期通常为每天一次，所以可以每天从业务系统同步并保存一份全量的维度数据。
优点是简单而有效，开发和维护成本低，且方便理解和使用。
缺点是浪费存储空间，尤其是当数据的变化比例比较低时。
2.3 数据仓库分层概述
数据仓库分层是一种组织数据仓库结构的方法，它将数据仓库划分为多个层次，每个层次负责不同的数据处理任务和数据访问需求。以下是常见的数仓分层规划：
第3章 数据仓库系统设计流程
以下是构建数据仓库的完整流程：
下面结合本项目的具体业务，为大家介绍每个步骤应做的具体工作。
3.1 数据调研
3.1.1 概述
数据调研重点要做两项工作，分别是业务调研和需求分析。这两项工作做的是否充分，直接影响着数据仓库的质量。
1）业务调研
做业务调研时，要明确每个业务的具体流程，将该业务所包含的每个业务过程一一列举出来。
2）需求分析
分析需求时，需要明确需求所需的业务过程及维度。
3）总结
做完业务分析和需求分析之后，要保证每个需求都能找到与之对应的业务过程及维度的数据。
3.1.2 实操
广告监测相关的业务相对比较简单，主要就包括广告的点击和曝光。
分析第1章的需求，可以看出所有统计指标均围绕着广告的曝光和点击行为，故为支撑上述指标，我们首先需要获取各广告的曝光和点击的操作记录。又因为上述指标要求能够按照省市、投放平台、操作系统、小时等进行分组统计，并且需要按照日期、事件类型、广告名称等进行过滤筛选，故我们还需要获取这些维度信息。
在数据调研阶段，我们应确定上述事实和维度的数据的来源。
1）广告曝光点击操作记录
目前大多数的广告投放平台都支持广告监测数据上报功能。下图为某平台广告推广设置界面，可以看到，广告主可在展示（曝光）监控和点击监控处填入第三方监测机构的服务器地址或者自己的监测服务器地址。这样一来，广告在被点击或者曝光后，广告媒体就会向我们填入的地址发送HTTP请求上报数据，我们就能拿到所需的曝光和点击记录了。
由于我们需要接收和分析广告媒体通过HTTP请求上报的数据，所以我们需要了解HTTP请求和媒体上报的数据格式。
（1）HTTP请求
参考资料：
（2）某平台监测数据上报格式
参考资料：
2）维度信息
部分维度信息可从媒体上报的数据中直接获取或者经过解析后获取，部分维度信息可从前文提到的广告管理平台的数据库中获取。
例如省份信息，可通过解析IP地址获取；操作系统信息，可通过解析User Agent信息获取；投放平台信息，则可从广告管理平台的数据库获取。
参考资料：
（1）IP地址解析
（2）User Agent
User Agent 在线分析，UA 用户代理检测工具 - dute.org
3.2 明确数据域
数据仓库模型设计除横向的分层外，通常也需要根据业务情况进行纵向划分数据域。
划分数据域的意义是便于数据的管理和应用。
数据域的划分在业务过程繁多的项目中才能体现出其意义。由于本项目设计到的业务过程相对简单，故可不做划分。
3.3 维度模型设计
3.3.1 概述
维度模型以业务总线矩阵作为设计蓝图。业务总线矩阵中包含维度模型所需的所有事实（业务过程）以及维度，以及各业务过程与各维度的关系。矩阵的行是一个个业务过程，矩阵的列是一个个的维度，行列的交点表示业务过程与维度的关系。
3.3.2 实操
以下是本项目的业务总线矩阵。
| 业务过程 | 维度 | ||||||||
| 时间 | 广告 | 投放平台 | 地理位置 | 操作系统 | ip | user_agent | 是否异常 | ||
| 曝光 | √ | √ | √ | √ | √ | √ | √ | √ | |
| 点击 | √ | √ | √ | √ | √ | √ | √ | √ | |
3.4 汇总模型设计
汇总模型中保存的是一些公共粒度的汇总表。所谓公共粒度的汇总表，就是在多个数据分析的需求中，都会用到的汇总表。
由于本项目的各个指标所需汇总数据的粒度各不相同，且最终的报表需要具备各种交互功能，例如下钻、筛选等，所以为保证交互的最大灵活性，我们直接使用明细数据出报表。所以汇总表不做设计。
1227
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
