第8章 综合案例—构建DVD租赁商店数据仓库

案例背景结束：
对于在线DVD租赁商店的决策者来说，他们需要从多个不同的商业角度观察数据，例如时间、电影、演员、用户等角度观察数据，并进行相关的分析得出决策，但是数据库中的数据不适合从多个角度进行分析，无法得出战略决策。然而，数据仓库支持复杂的分析操作，侧重于决策支持，并且还提供直观易懂的查询结果，因此我们需要基于数据库sakila创建一个DVD租赁商店数据仓库，并将sakila数据库中的数据加载到数据仓库中，便于在线DVD租赁商店的决策者对数据进行分析得出商业决策。

数据下载：
我们可以从MySQL的官网下载数据库sakila的建库脚本，若是在Windows环境下安装数据库sakila，则下载名称为sakila-db.zip的压缩包文件；若是在Linux环境下安装数据库sakila，需要下载名称为sakila-db.tar.gz的压缩包文件。
本书下载的是名称为sakila-db.zip的压缩包文件，该压缩包文件中包含三个文件，分别是文件sakila.mwb、文件sakila-data.sql和文件sakila-schema.sql。其中，文件sakila.mwb是一个MySQL Workbench数据模型，用于查看数据库结构；文件sakila-data.sql是用于创建数据库sakila的数据；文件sakila-schema.sql是用于创建数据库sakila的数据结构。

数据库介绍
数据库sakila中一共含有十六张数据表，分别是actor（演员）表、address（地址）表、category（类别）表、city（城市）表、country（国家）表、customer（顾客）表、film（电影）表、film_actor（演员所属电影）表、film_category（电影所属的类别）表、film_text（电影描述）表、inventory（库存）表、language（语言）表、payment（付款）表、rental（租赁）表、staff（工作人员）表以及store（商店）表。
数据表关系：

数据表字段介绍
数据表film：
用于储存电影基本信息及相关介绍的数据，该数据表各个字段的含义如表。

数据表file_category：
用于储存定义电影id所属电影类别id的数据，该数据表各个字段的含义如表。

数据表category
用于储存电影类别名称所属类别id的数据，该数据表各个字段的含义如表。

数据表film_actor
用于储存定义演员id所属电影id的数据，该数据表各个字段的含义如表。

数据表actor
用于储存演员id对应的姓和名字数据，该数据表各个字段的含义如表。

数据表language
用于储存电影语言id对应的语言名称数据，该数据表各个字段的含义如表。

数据表film_text
用于储存电影id对应的电影名及描述的数据，该数据表各个字段的含义如表。

数据表store
用于储存商店id对应管理人员id和商店地址id数据，该数据表各个字段含义如表。

数据表staff
用于储存员工的基本信息及员工所属商店的数据，该数据表各个字段的含义如表。


数据表inventory
用于储存库存编号对应的电影id和商店id数据，该数据表各个字段的含义如表。

数据表customer
用于储存顾客的基本信息数据，该数据表各个字段的含义如表。


数据表rental
用于储存租借相关信息数据，该数据表各个字段的含义如表。

数据表payment
用于储存租赁时付款的相关信息，该数据表各个字段的含义如表。

数据表country
用于储存国家id对应的国家名称数据，该数据表各个字段的含义如表。

数据表city
用于储存城市id对应的城市名称以及所属国家id这一类数据，该数据表各个
字段的含义如表。

数据表address
用于储存城市地址及地址邮编、所属区域等相关信息，该数据表各字段含义如表。

构建数据仓库
我们基于数据库sakila构建一个星型模型的DVD租赁商店数据仓库，并命名为sakila_dw。数据仓库sakila_dw中的事实表fact_rental是根据数据库sakila中的数据表rental创建的；维度表是根据数据表sakila中数据表的分类创建的，即从人员、时间、地点以及事件四个角度进行创建数据仓库sakila_dw的维度表，具体如下：
1、从人员角度角度创建维度表dim_customer和维度表dim_staff，分别表示租赁业务中的客户和员工；
2、从时间角度创建维度表dim_date和维度表dim_time，用于记录所有DVD的租赁时间和归还时间；
3、从地点角度创建维度表dim_store，用于记录DVD光盘是从哪个商店租赁的；
4、从事件角度创建维度表dim_actor和维度表dim_film，其中维度表dim_actor用于记录演员的基本信息，维度表dim_film用于记录电影的基本信息。由于电影是租赁和归还的实际对象，因此维度表dim_film应与事实表fact_rental关联。一部电影是由多位演员出演，所以会有桥接表dim_film_actor_bridge，该表将电影与演员相关联。

一、通过Kettle工具加载日期数据至dim_date日期维度表。
组件准备




表输出的数据库字段是关键，选择获取字段，然后删去一些数据库中没有的字段，这时候可能还有错，按照日记提示，修改掉匹配错误的字段

结果：

二、通过Kettle工具加载时间数据至日期维度表dim_time。

选择组件，构成转换

在生成记录中，将限制设为24，表示生成24小时的记录

增加序列中，将值的名称改为hours24 每一列增加1

在JavaScript代码中输入以下代码将24小时的12小时制表示出来，并选择获取变量

//Script here
//生成12小时格式
var hours12=hours24.getInteger()%12;
//生成AM。PM格式
var am_pm=hours24.getInteger()>12?"PM":"AM";

生成记录二中，生成的是代表分钟的序列：

增加序列二中，把分钟0~59分钟表示了出来：

生成记录三，生成秒，生成60秒的记录

增加序列三，把秒对应的0~59秒表示出来

记录关联：不需要配置，它会自动将1小时对应60分钟，1分钟对应60秒关联对应起来
会得到：

JavaScript代码2：输入代码获取time字段和timekey：

//Script here
//生成time
var time = hours24.getInteger() + ":" + minutes.getInteger() + ":" + seconds.getInteger();
//time_key
var time_key = (hours24.getInteger()<10?"0":"")+hours24.getInteger()+(minutes.getInteger()<10?"0":"")+minutes.getInteger()+(seconds.getInteger()<10?"0":"")+seconds.getInteger();

配置表输出：

结果：

三、通过Kettle工具加载员工数据至员工维度表dim_staff。
选择好组件，配置转换

在SQL框中编写SQL语句，用于获取字段staff_last_update中的最大值，将该值替换为”1970-01-01 00:00:00”并赋值给临时字段max_dim_staff_last_update；单击“预览”按钮，查看临时字段max_dim_staff_last_update是否将默认值设置为“1970-01-01 00:00:00”，如图所示。

表输入二，获取staff中的数据

字段选择中，修改active的类型

值映射中，将Y对应yes，N对应NO

维度查询/更新：
单击目标表右侧的【浏览】按钮，选择输出的目标表，即维度表dim_staff；在“关键字”选项卡处添加关键字字段staff_id，用于指定维度表字段和值映射控件流中字段的比较条件，若维度表中的数据有更新，则通过字段staff_id进行更新操作；在“字段”选项卡处添加查询/更新字段，用于更新目标维度表中的字段数据；在“代理关键字段”处的下拉框中选择staff_key为代理关键字段，并指定“创建代理键”是使用自增字段；在“Version字段”处的下拉框中选择staff_version_number；在“Stream日期字段”处的下拉框中选择last_update；在“开始日期字段”处的下拉框中选择staff_valid_from；在“截止日期字段”处的下拉框中选择staff_valid_through，如图所示（见下页）。

结果：

四、通过Kettle工具加载用户数据至用户维度表dim_customer。
配置组件，构成转换：

在SQL框中编写用于获取字段customer_last_update中的最大值，将该值替换为”1970-01-01 00:00:00”并赋值给临时字段max_dim_customer_last_update；单击“预览”按钮，查看临时字段max_dim_customer_last_update是否将默认值设置为“1970-01-01 00:00:00”，如图所示。
在表输入2中获取customer的数据
按如下所示配置：

创建一个转换fetch_address：

在load_dim_customer中的映射（子转换）中，将fetch_address浏览进去

修改active的字段类型

改值映射

更新：

结果：


五、通过Kettle工具加载商店数据至商店维度表dim_store。
获取组件，构成转换：

配置表输入，在创建了数据库连接之后，在SQL框中编写SQL语句，用于获取字段store_last_update中的最大值，将该值替换为”1970-01-01 00:00:00”并赋值给临时字段max_dim_store_last_update；单击“预览”按钮，查看临时字段max_dim_store_last_update是否将默认值设置为“1970-01-01 00:00:00” ，如图所示。

表输入2，获取store数据

映射（子转化），使用的子转换还是fetch_address

数据库查询：
单击表名右侧的【浏览】按钮，添加staff数据表，用于查询商店员工的信息；在“查询所需的关键字”框中，添加查询所需的关键字staff_id，用于指定表字段和流字段的比较条件；在“查询表返回的值”框中，添加查询表返回的值，即员工姓名，如图所示。

维度更新：
单击【浏览】按钮，选择输出的目标表，即维度表dim_store；在“关键字”选项卡处添加关键字字段store_id，用于指定维度表字段和流字段的比较条件；在“字段”选项卡处添加查询/更新字段，用于指定维度表字段store_id和流字段store_id数据一致需要更新的字段，；在“代理关键字段”处的下拉框中选择store_key为代理关键字段，并指定“创建代理键”是使用自增字段；在“Version字段”处的下拉框中选择store_version_number；在“Stream日期字段”处的下拉框中选择last_update；在“开始日期字段”处的下拉框中选择store_valid_from；在“截止日期字段”处的下拉框中选择store_valid_through，如图所示。

结果：


六、通过Kettle工具加载演员数据至演员维度表dim_actor。
获取组件，构建转换：

表输入：在创建了数据库连接之后
在SQL框中编写SQL语句，用于获取字段actor_last_update中的最大值，将该值替换为”1970-01-01 00:00:00”并赋值给临时字段max_dim_actor_last_update；单击“预览”按钮，查看临时字段max_dim_actor_last_update是否将默认值设置为“1970-01-01 00:00:00”，如图所示。

表输入2中获取表actor中的数据：

插入/更新：单击【获取字段】按钮，用来指定查询数据所需要的关键字，这里选择的是dim_actor数据表中的actor_id字段和输入流里面的actor_id字段；单击【获取和更新字段】按钮，用来指定需要更新的字段，如图所示。

结果：


七、通过Kettle工具加载电影数据至电影维度表dim_film。
获取组件，创建转换

组件配置如下：



















结果：


八、通过Kettle工具加载租赁数据至租赁事实表fact_rental。
获取组件，构建转换：

组件配置如下













结果：


创建一个作业load_rentals，并添加Start控件、转换控件、发送邮件控件、中止作业控件以及Hop作业项连接线，如图所示