引言：
数据建模是一个用于定义和分析在组织的信息系统范围内支持商业流程所需的数据要求的过程。简单来说，数据建模是基于对业务数据的理解和数据分析的需要，将各类数据进行整合和关联，使得数据可以最终以可视化的方式呈现，让使用者能够快速地、高效地获取到数据中有价值的信息，从而做出准确有效的决策。
背景知识：
维度模型：
维度建模从分析决策的需求出发构建模型，为分析需求服务，因此它重点关注用户如何更快速地完成需求分析，同时具有较好的大规模复杂查询的响应性能。其典型的代表是星形模型，以及在一些特殊场景下使用的雪花模型。
建模步骤：
（1）选择业务过程。业务过程可以是单个业务事件，比如交易的支付、退款等；也可以是某个事件的状态，比如当前的账户余额等；还可以是一系列相关业务事件组成的业务流程，具体需要看我们分析的是某些事件发生情况，还是当前状态，或是事件流转效率。
（2）选择粒度。在事件分析中，要预判所有分析需要细分的程度，从而决定选择的粒度。粒度是维度的一个组合。
（3）识别维表。选择好粒度之后，就需要基于此粒度设计维表，包括维度属性，用于分析时进行分组和筛选。
（4）选择事实。确定分析需要衡量的指标。
建模意义：
（1）性能：良好的数据模型能帮助我们快速查询所需要的数据，减少数据的I/O吞吐。
（2）成本：良好的数据模型能极大地减少不必要的数据冗余，也能实现计算结果（3）复用，极大地降低大数据系统中的存储和计算成本。
（4）效率：良好的数据模型能极大地改善用户使用数据的体验，提高使用数据的效率。
（5）质量：良好的数据模型能改善数据统计口径的不一致性，减少数据计算错误的可能性。
实验目标：
完成基于sakila DVD租赁模型的维度建模操作，开发完整的代码逻辑。
实验过程：
1.数据初始化。执行如下两条命令，进入脚本目录，数据初始化。其中AKID AKSecret 项目名称在云产品资源中可以对应找到。
cd /root/init_table_task/
/usr/bin/python3.6 table_2_odps.py AKID AKSecret 项目名称
等待上云完毕。
2.分析业务过程。
业务过程可以概括为一个个不可拆分的行为事件。用户的业务系统中，通过埋点或日常积累，通常已经获取了充足的业务数据。为理清数据之间的逻辑关系和流向，首先需要理解用户的业务过程，了解过程中涉及到的数据系统。
您可以采用过程分析法，将整个业务过程涉及的每个环节一一列清楚，包括技术、数据、系统环境等。在分析企业的工作职责范围（部门）后，您也可以借助工具通过逆向工程抽取业务系统的真实模型。您可以参考业务规划设计文档以及业务运行（开发、设计、变更等）相关文档，全面分析数据仓库涉及的源系统及业务管理系统：
每个业务会生成哪些数据，存在于什么数据库中。
对业务过程进行分解，了解过程中的每一个环节会产生哪些数据，数据的内容是什么。
数据在什么情况下会更新，更新的逻辑是什么。
业务过程可以是单个业务事件，例如交易的支付、退款等；也可以是某个事件的状态，例如当前的账户余额等；还可以是一系列相关业务事件组成的业务流程。具体取决于您分析的是某些事件过去发生情况、当前状态还是事件流转效率。
选择粒度：在业务过程事件分析中，您需要预判所有分析需要细分的程度和范围，从而决定选择的粒度。 识别维表、选择好粒度之后，您需要基于此粒度设计维表，包括维度属性等，用于分析时进行分组和筛选。最后，您需要确定衡量的指标。
salika租赁模型中，业务过程还是比较简单的，经过简单梳理如下：
3.划分数据域。
数据仓库是面向主题（数据综合、归类并进行分析利用）的应用。数据仓库模型设计除横向的分层外，通常也需要根据业务情况纵向划分数据域。数据域是联系较为紧密的数据主题的集合，是业务对象高度概括的概念，目的是便于管理和应用数据。
通常，您需要阅读各源系统的设计文档、数据字典和数据模型，研究逆向导出的物理数据模型。进而，可以进行跨源的主题域合并，跨源梳理出整个企业的数据域。
数据域是指面向业务分析，将业务过程或者维度进行抽象的集合。为保障整个体系的生命力，数据域需要抽象提炼，并长期维护更新。在划分数据域时，既能涵盖当前所有的业务需求，又能让新业务在进入时可以被包含进已有的数据域或扩展新的数据域。数据域的划分工作可以在业务调研之后进行，需要分析各个业务模块中有哪些业务活动。
数据域可以按照用户企业的部门划分，也可以按照业务过程或者业务板块中的功能模块进行划分。例如A公司电商营销业务板块可以划分为如下数据域，数据域中每一部分都是实际业务过程经过归纳抽象之后得出的。
| 数据域 | 业务过程 |
|---|---|
| 会员店铺域 | 注册、登录、装修、开店、关店 |
| 商品域 | 发布、上架、下架、重发 |
| 日志域 | 曝光、浏览、单击 |
| 交易域 | 下单、支付、发货、确认收货 |
| 服务域 | 商品收藏、拜访、培训、优惠券领用 |
| 采购域 | 商品采购、供应链管理 |
本次只抽象一个数据域，定义为交易域，业务过程只分析影碟的租赁与归还。
| 数据域 | 业务过程 |
|---|---|
| 交易域 | 下单、支付、发货、确认收货 |
4.定义维度和确定粒度
结合对业务过程的分析定义维度。
在影碟租赁的业务过程中，有以下维度：
客户
店铺
日期
电影
店员
在影碟归还的业务过程中，有以下维度：
客户
店铺
日期
电影
作为维度建模的核心，在企业级数据仓库中必须保证维度的唯一性。以实验电影维度为例，有且只允许有一种维度定义。对于任何业务过程所传达的信息都是一致的。
事实表的粒度，我们一般选择最细粒度，这样可以得出几乎任何的指标。本例选择的粒度是一次租赁信息。
5.构建事实维度矩阵
明确每个数据域下有哪些业务过程后，即可构建事实维度矩阵。您需要明确业务过程与哪些维度相关，并定义每个数据域下的业务过程和维度。如下所示是A本例构建的事实维度矩阵，我们定义了客户、店铺、日期、电影、店员等维度。
6.MaxCompute中建表
复制下方地址，在Chromium网页浏览器打开新页签，粘贴并访问大数据开发质量平台DataWorks控制台。
https://dataworks.console.aliyun.com/overview
在工作空间列表页面，找到实验室分配的DataWorks资源，单击右侧操作列下的数据开发。
在临时查询区域，右键临时查询，选择新建节点>ODPS SQL。
在新建节点对话框中，名称输入为create_table，单击提交。
在create_table节点编辑页面，将如下模型的建表语句复制到临时查询的createtable节点中。
(说明：本次实验环境，只有一个项目空间，无法做到ods cdm ads的数仓分层，因此以表名前缀区分分层，比如ods开头的代表ods层的表，dim和dwd开头的表代表cdm层的表。)
set odps.sql.type.system.odps2=true;
set odps.sql.decimal.odps2=true;
-- 租赁事实表
CREATE TABLE IF NOT EXISTS dwd_fct_rental
(
rental_id BIGINT
,staff_key BIGINT
,customer_key BIGINT
,film_key BIGINT
,store_key BIGINT
,rental_date_key STRING
,rental_counts BIGINT
,inventory_id BIGINT
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
-- 归还事实表
CREATE TABLE IF NOT EXISTS dwd_fct_return
(
rental_id BIGINT
,customer_key BIGINT
,film_key BIGINT
,store_key BIGINT
,return_date_key STRING
,return_counts BIGINT
,inventory_id BIGINT
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
-- 累积快照事实表
CREATE TABLE IF NOT EXISTS dwd_fct_order
(
rental_id BIGINT
,staff_key BIGINT
,customer_key BIGINT
,film_key BIGINT
,store_key BIGINT
,rental_date_key STRING
,return_date_key STRING
,rental_counts BIGINT
,return_counts BIGINT
,inventory_id BIGINT
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
-- 电影维度表
CREATE TABLE IF NOT EXISTS dim_film
(
film_key BIGINT
,film_title STRING
,film_description STRING
,film_release_year BIGINT
,film_language STRING
,film_rental_duration BIGINT
,film_rental_rate DECIMAL(4,2)
,film_duration BIGINT
,film_replacement_cost DECIMAL(6,2)
,film_rating_text STRING
,film_category STRING
,film_last_update DATETIME
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
-- 日期维度表
CREATE TABLE IF NOT EXISTS dim_date
(
date_key STRING
,date_value DATE
,year_value STRING
,month_value STRING
,week_value STRING
,day_value STRING
,etl_date DATETIME
);
-- 客户维度表
CREATE TABLE IF NOT EXISTS dim_customer
(
customer_key BIGINT
,customer_first_name STRING
,customer_last_name STRING
,customer_active STRING
,customer_address STRING
,customer_district STRING
,customer_city STRING
,customer_country STRING
,customer_last_update DATETIME
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
-- 店铺维度表
CREATE TABLE IF NOT EXISTS dim_store
(
store_key BIGINT
,store_address STRING
,store_district STRING
,store_city STRING
,store_country STRING
,store_manager_staff_id BIGINT
,store_manager_first_name STRING
,store_manager_last_name STRING
,store_last_update DATETIME
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
-- 店员维度表
CREATE TABLE IF NOT EXISTS dim_staff
(
staff_key BIGINT
,staff_store_id BIGINT
,staff_first_name STRING
,staff_last_name STRING
,staff_active STRING
,staff_last_update DATETIME
,etl_date DATETIME
) PARTITIONED BY (ds STRING);
在create_table节点编辑页面，单击图标,并确定两次运行。
等一段时间，等返回结果如下且已不再刷新时，表示模型的建表已完成。
7.代码开发。
在数据开发页面左侧功能栏中，单击数据开发。
在数据开发区域，右键业务流程，单击新建业务流程。
在新建业务流程对话框中，业务名称输入事实表开发，单击新建。
按照以上步骤，新建维度表开发。
在数据开发区域，右键事实表开发，选择新建节点>ODPS SQL。
在新建节点对话框中，名称输入dwd_fct_rental，单击提交。
在dwd_fct_rental节点编辑页面，将如下插入数据语句复制到临时查询的dwd_fct_rental节点中。
在参数对话框中，单击确定，再点击运行。返回结果如下，表示模型的建表已完成。
重复上述步骤，在维度表开发流程中，创建名称为dwd_fct_return的ODPS SQL节点，复制如下插入数据语句，然后执行。
INSERT OVERWRITE TABLE dwd_fct_return PARTITION(ds = '${bizdate}')
SELECT
r.rental_id as rental_id
,r.customer_id as customer_key
,i.film_id as film_key
,i.store_id as store_key
,to_char(r.return_date, 'yyyymmdd') as return_date_key
,1 as return_counts
,r.inventory_id as inventory_id
,GETDATE() as etl_date
FROM ods_sakila_rental_df r
INNER JOIN ods_sakila_inventory_df i
ON r.inventory_id = i.inventory_id
AND i.ds = '${bizdate}'
AND r.return_date IS NOT NULL
WHERE r.ds = '${bizdate}'
;
重复上述步骤，在事实表开发流程中，创建名称为dwd_fct_order的ODPS SQL节点，复制如下插入数据语句，然后执行。
INSERT OVERWRITE TABLE dwd_fct_order PARTITION(ds = '${bizdate}')
SELECT
r.rental_id as rental_id
,r.staff_id as staff_key
,r.customer_id as customer_key
,i.film_id as film_key
,i.store_id as store_key
,to_char(r.rental_date, 'yyyymmdd') as rental_date_key
,to_char(r.return_date, 'yyyymmdd') as return_date_key
,1 as rental_counts
,case when i.inventory_id is not null
then 1 else 0
end as return_counts
,r.inventory_id as inventory_id
,GETDATE() as etl_date
FROM ods_sakila_rental_df r
LEFT JOIN ods_sakila_inventory_df i
ON r.inventory_id = i.inventory_id
AND i.ds = '${bizdate}'
WHERE r.ds = '${bizdate}'
;
到这里，我们的事实表就开发完成。
继续重复步骤，在维度表开发流程中，创建名称为dim_film的ODPS SQL节点，复制如下插入数据语句，然后执行。
set odps.sql.decimal.odps2=true;
INSERT OVERWRITE TABLE dim_film PARTITION(ds = '${bizdate}')
SELECT
f.film_id as film_key
,f.title as film_title
,f.description as film_description
,cast(f.release_year AS BIGINT ) as film_release_year
,l.name as film_language
,f.rental_duration as film_rental_duration
,cast(f.rental_rate AS DECIMAL(4,2)) as film_rental_rate
,f.length as film_duration
,cast(f.replacement_cost AS DECIMAL(6,2)) as film_replacement_cost
,f.rating as film_rating_text
,c.name as film_category
,f.last_update as film_last_update
,GETDATE() as etl_date
FROM ods_sakila_film_df f
LEFT JOIN ods_sakila_language_df l
ON f.language_id = l.language_id
AND l.ds = '${bizdate}'
LEFT JOIN ods_sakila_film_category_df fc
ON f.film_id = fc.film_id
AND fc.ds = '${bizdate}'
LEFT JOIN ods_sakila_category_df c
ON fc.category_id = c.category_id
AND c.ds = '${bizdate}'
WHERE f.ds = '${bizdate}'
;
在维度表开发流程中，创建名称为dim_date 的ODPS SQL节点，复制如下插入数据语句，然后执行。
set odps.sql.type.system.odps2=true;
INSERT OVERWRITE TABLE dim_date
SELECT
TO_CHAR(dt, 'yyyymmdd') as date_key
,dt as date_value
,SUBSTRING(dt,1,4) as year_value
,SUBSTRING(dt,6,2) as month_value
,WEEKOFYEAR(dt) as week_value
,SUBSTRING(dt,9,2) as day_value
,GETDATE() as etl_date
FROM (
SELECT DATE_ADD('2005-01-01', cast(rn AS BIGINT)) AS dt
FROM (
SELECT transform(
'for i in `seq 0 7000`; do echo $i; done'
) USING 'sh' AS rn
) a
) b
;
重复步骤，在维度表开发流程中，创建名称为dim_customer的ODPS SQL节点，复制如下插入数据语句，然后执行。
INSERT OVERWRITE TABLE dim_customer PARTITION(ds = '${bizdate}')
SELECT
c.customer_id as customer_key
,c.first_name as customer_first_name
,c.last_name as customer_last_name
,c.active as customer_active
,a.address as customer_address
,a.district as customer_district
,ct.city as customer_city
,cy.country as customer_country
,c.last_update as customer_last_update
,GETDATE() as etl_date
FROM ods_sakila_customer_df c
LEFT JOIN ods_sakila_address_df a
ON c.address_id = a.address_id
AND a.ds = '${bizdate}'
LEFT JOIN ods_sakila_city_df ct
ON a.city_id = ct.city_id
AND ct.ds = '${bizdate}'
LEFT JOIN ods_sakila_country_df cy
ON ct.country_id = cy.country_id
AND cy.ds = '${bizdate}'
WHERE c.ds = '${bizdate}'
;
重复步骤，在维度表开发流程中，创建名称为dim_staff的ODPS SQL节点，复制如下插入数据语句，然后执行。
INSERT OVERWRITE TABLE dim_staff PARTITION(ds = '${bizdate}')
SELECT s.staff_id as staff_key
,s.store_id as staff_store_id
,s.first_name as staff_first_name
,s.last_name as staff_last_name
,s.active as staff_active
,s.last_update as staff_last_update
,GETDATE() as etl_date
FROM ods_sakila_staff_df s
WHERE ds = '${bizdate}'
;
