黑马Hive+Spark离线数仓工业项目--数仓事实层DWB层构建（2）

旧城等待， 2024-03-30 13:03 24阅读 0赞

## 工单事实指标构建 ##

**目标**：实现DWB层工单事实指标表的构建

**实施**

**建表**

![844f1e6214e944b88391c1cc46f1446b.png][]

**抽取**

![d845251c41654d66a849a4ec9421bcd5.png][]  
![5248ad31eecc438fa4c453bc3ccfe3cb.png][]

![e672cc3cab0c4607b4a54d1bd4a8040e.png][]

## 安装事实指标需求分析 ##

**目标**：掌握DWB层安装事实指标表的需求分析

**路径**

\- step1：目标需求  
\- step2：数据来源

**实施**

目标需求：基于设备安装信息统计安装设备个数、收费安装个数、审核安装个数等指标

![a546e191db7b43c28321a5a3fbee7a0f.png][]

\- 全新安装数量：install\_type = 1  
\- 联调安装数量：install\_way = 2  
\- 产生维修数量：is\_repair = 1  
\- 额外收费数量：is\_pay = 1  
\- 安装设备数量：与服务单关联，统计设备的id个数  
\- 安装费用：通过工单id从报销单信息中关联得到报销金额  
\- 审核完成工单个数：

**数据来源**

ciss\_service\_install：安装单信息表

![462c2a99734f4640bb570b171e1dfe0a.png][]  
  
ciss\_service\_workorder\*\*：工单详情事实表  
![b0bbbdcc363d415ab76913e120ce1692.png][]  
  
ciss\_service\_order\*\*：服务单信息表  
![fe47a81092f84c6e89e7f1bbda76f7e5.png][]  
![a50b41df3e0b4d738eebb2a7279161c7.png][]

## 安装事实指标构建 ##

**目标**：实现DWB层安装事实指标表的构建

**实施**

**建表**

![408843c4206540148a5e6dc8b0eed7e7.png][]

**抽取**

![08c820a4c61441c38c767bcfb47c23f8.png][]

![1426f0f0932b413695041a144e59ef2d.png][]

## 维修事实指标需求分析 ##

**目标**：掌握DWB层维修事实指标表的构建需求分析

**路径**

\- step1：目标需求  
\- step2：数据来源

**实施**

目标需求：基于维修信息数据统计维修设备个数、维修、更换、升级配件数量、工时费用、配件费用等指标

![45fdd658fabc4431a684eb9c86634c56.png][]

**数据来源**

![59d9c457f6c7474f8c6ccd5f57688387.png][]

![54a68b952393436a92aa11e48290308e.png][]

## 维修事实指标构建 ##

**目标：**实现DWB层维修事实指标表的构建

**实施**

**建表**

![3d7a096269bb4f31944df7cf706f17cd.png][]

**抽取**

![28bb5f473c01428abdfd80ba657cd994.png][]

## ![3b4354b1fbfd42b299bc68ee25e8d591.png][]客户回访事实指标需求分析 ##

**目标**：掌握DWB层客户回访事实指标表的需求分析

**路径**

\- step1：目标需求  
\- step2：数据来源

**实施**

目标需求：基于客户回访数据统计工单满意数量、不满意数量、返修数量等指标

![067431ed58304b7b9b2d0bbc340dd8ce.png][]

数据来源

\- \*\*ciss\_service\_return\_visit\*\*：回访信息表

![4f71177a9a30430f94f5feb0ae0c9fbf.png][]

\- 1：满意  
\- 0：不满意

\- \*\*ciss\_service\_workorder\*\*：服务工单信息表

![4d84ab95fd04400aa44165330b111d53.png][]

## 客户回访事实指标 ##

**目标**：实现DWB层客户回访事实指标表的构建

**实施**

**建表**

![4b6cee4b8706454482fd9a81021bcf8f.png][]

**抽取**

![9c13fc9f18574b869fe101e99e51c4fa.png][]

![ace4de893177483eb20b373f9c8d9333.png][]

## 费用事实指标分析及实现 ##

**目标：**实现DWB层费用报销事实指标表的构建

**路径**

\- step1：目标需求  
\- step2：数据来源  
\- step3：目标实现

**实施**

目标需求：基于费用报销数据统计费用报销金额等指标

![b130781aae704877a09cfd6a62412bcc.png][]

**数据来源**

![447c99ed3a034c1eaabd9d4c52c92437.png][]

**目标实现**

**建表**

![208348e2a557451da7fdb2306414f7fd.png][]

**抽取** ![254bde1f346a49f0af9f34fa257f51f4.png][]

## 差旅事实指标分析及实现 ##

**目标**：实现DWB层差旅报销事实指标表的构建

**路径**

\- step1：目标需求  
\- step2：数据来源  
\- step3：目标实现

**实施**

目标需求：基于差率报销信息统计交通费用、住宿费用、油费金额等报销费用指标

![0fd2daeca278464f8807d985ca6f08c7.png][]

**数据来源**

![76f0d6b755404a9783dbcdb359997f7a.png][]

![2c2d0ae2bea648afb21a550a3a511078.png][]

**目标实现**

**建表**

![d7867a4b580e4ee297a8059558a34ff6.png][]  
  
**抽取**  
![fa260b40681a4cce8badc252ad9a6a88.png][] ![615b6f8f0dbf41c992cd0c6818d85bfd.png][]

[844f1e6214e944b88391c1cc46f1446b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/1d22d244839044439f0b5eaaed759c74.png
[d845251c41654d66a849a4ec9421bcd5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/445235622d2b4043b9b9b4d6837c04b2.png
[5248ad31eecc438fa4c453bc3ccfe3cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d47c15c8ff1543ea9da11f88c45e3f41.png
[e672cc3cab0c4607b4a54d1bd4a8040e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b20d250a6a1e49cc91d8009f3f5782b6.png
[a546e191db7b43c28321a5a3fbee7a0f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c958471d29a04676b2b65dedf76dd4d9.png
[462c2a99734f4640bb570b171e1dfe0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/1cf8f20e6ce74df8aea1fecb51406136.png
[b0bbbdcc363d415ab76913e120ce1692.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/a4e8f6d441bd4c5a9ead9bada82ab31a.png
[fe47a81092f84c6e89e7f1bbda76f7e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/42e0d555602243ccb16c8d4eb70afe2a.png
[a50b41df3e0b4d738eebb2a7279161c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fd1d23c127bc45c497852d5db4009aaa.png
[408843c4206540148a5e6dc8b0eed7e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/39f797ca7b1649e89c29577ab9ec4e88.png
[08c820a4c61441c38c767bcfb47c23f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c55f967e75b14664bdac50794fd9a348.png
[1426f0f0932b413695041a144e59ef2d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/9d5d9b2c8af746cabd707ea6b67d3dbb.png
[45fdd658fabc4431a684eb9c86634c56.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/624fa215f1a34f86ade5e37a235421f2.png
[59d9c457f6c7474f8c6ccd5f57688387.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/2362deceb0304ff08c9f9ae4a9f748d6.png
[54a68b952393436a92aa11e48290308e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/09122fa39ed64daa928c117f18b1522d.png
[3d7a096269bb4f31944df7cf706f17cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/7c2e3130ecfb4fffa3c0e6d8d7e5d012.png
[28bb5f473c01428abdfd80ba657cd994.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/94fd49310b8a4f21b5de2a3d69e5a517.png
[3b4354b1fbfd42b299bc68ee25e8d591.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/dd0bb67353c54d3095ea4c03bb09ab26.png
[067431ed58304b7b9b2d0bbc340dd8ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/51aaad88a2ae460780cc5c41b3fc2ea5.png
[4f71177a9a30430f94f5feb0ae0c9fbf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/f7ea840d65ae45bbaf67414abd232310.png
[4d84ab95fd04400aa44165330b111d53.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c1f75971f4d5422b9a8b6323de0f5d97.png
[4b6cee4b8706454482fd9a81021bcf8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/feb6ed0a50cc437598fe6812d1332a58.png
[9c13fc9f18574b869fe101e99e51c4fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/996d887f736546d69ee37337ef457f0f.png
[ace4de893177483eb20b373f9c8d9333.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b2ff3adbdaa14b54878c98d2b3158ed6.png
[b130781aae704877a09cfd6a62412bcc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/46f25489cc144f73b539b302049e756d.png
[447c99ed3a034c1eaabd9d4c52c92437.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/55e4b442024a475596c8cc82496a231f.png
[208348e2a557451da7fdb2306414f7fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d60b1eb247e443b08a3361099a8f3ae2.png
[254bde1f346a49f0af9f34fa257f51f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bed05e7950ca404ab0fd9d3d423be80d.png
[0fd2daeca278464f8807d985ca6f08c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/3c59f4c4123b435fba2aa43cfd7ae444.png
[76f0d6b755404a9783dbcdb359997f7a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/1899493cb74d45169b4eb336a4b14662.png
[2c2d0ae2bea648afb21a550a3a511078.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ec1b6cd0ef054a84b165284d0775fbdf.png
[d7867a4b580e4ee297a8059558a34ff6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/05e770c8563a4b45af82d397c20d6841.png
[fa260b40681a4cce8badc252ad9a6a88.png]: https://img-blog.csdnimg.cn/fa260b40681a4cce8badc252ad9a6a88.png
[615b6f8f0dbf41c992cd0c6818d85bfd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8b88aa87d580417380173b2f5eb121df.png