主要维度:
班组
班次
检测项
质检日期(时间维度)
加工客户
加工类型
收购类型
生产线
产地
烟叶级别
主要指标:
慢速测定_平均值
快速测定_平均值
红外测定_平均值
ETL设计
需要抽取的维度表:
序号 | 表名 | 说明 | 备注 |
1 | T_Department | 班组 | YYERP库中对应表T_Department |
2 | T_TeamOrder | 班次 | YYERP库中对应表T_TeamOrder |
3 | T_DIC_QualTest | 检测项 | OnlineQualDB库中对应表T_DIC_QualTest |
4 | T_DIC_QualProcess | 质检工序 | OnlineQualDB库中对应表T_DIC_QualProcess |
5 | vRoastingPlan
TB_MRP_ROASTING_PLAN_DETAIL2 TB_MRP_ROASTING_PLAN2 | 生产信息 | vRoastingPlan 视图
TB_MRP_ROASTING_PLAN_DETAIL2 TB_MRP_ROASTING_PLAN2
|
5.1 | T_CustomInfo | 加工客户 | YYERP库中对应表T_CustomInfo |
5.2 | T_TLProcType | 加工类型 | YYERP库中对应表T_TLProcType |
5.3 | T_PurchaseType | 收购类型 | YYERP库中对应表T_PurchaseType |
5.4 | T_ManuProductLine | 生产线 | YYERP库中对应表T_ManuProductLine |
5.5 | T_Origin | 烟叶产地 | YYERP库中对应表T_Origin |
5.6 | T_GBGradeCode | 烟叶级别代码信息 | YYERP库中对应表T_GBGradeCode |
需要抽取的事实表:
序号 | 表名 | 说明 | 备注 |
1 | T_QualMoisture_Middle |
| OnlineQualDB库 水份检测中间表 |
2 | T_QualMoisture_Middle_Detail |
|
|
建立vRoastingPlan视图:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[vRoastingPlan] AS SELECT T2.COL_RP_CODE AS ProductionNo , T1.COL_RRD_CODE AS FormulaNo , T2.COL_RP_YEAR AS Year , T1.OriginID AS OriginID , T1.COL_RPD_RT_AREA AS Origin , T1.I_GBGradeID AS GBGradeID , T1.COL_RPD_RT_QUALITY AS GBGrade , t1.ProdLineID , T1.COL_RPD_ROASTING_DEVICE AS ProdLine , t1.PurchaseID , T1.COL_RPD_FT_PURCHASE_TYPE AS Purchase , t1.TLProcTypeID , T1.COL_RPD_MANUFACTURE_TYPE AS TLProcType , t1.CustCode , T1.COL_RPD_RT_OWNER AS Cust , T2.COL_RP_FINISH_FLAG AS FinishFlag , T1.COL_RPD_EXECUTE_TIME AS ExecuteTime FROM dbo.TB_MRP_ROASTING_PLAN_DETAIL2 AS T1 LEFT JOIN .dbo.TB_MRP_ROASTING_PLAN2 AS T2 ON T1.COL_RP_PK = T2.COL_RP_PK GO
ETL处理逻辑:
--新增字段 ALTER TABLE T_QualMoisture_Middle ADD OriginID INT ALTER TABLE T_QualMoisture_Middle ADD GBGradeID INT ALTER TABLE T_QualMoisture_Middle ADD ProdLineID VARCHAR(10) ALTER TABLE T_QualMoisture_Middle ADD PurchaseID INT ALTER TABLE T_QualMoisture_Middle ADD TLProcTypeID INT ALTER TABLE T_QualMoisture_Middle ADD CustCode VARCHAR(5) --同步数据 UPDATE T_QualMoisture_Middle SET T_QualMoisture_Middle.OriginID = vRoastingPlan.OriginID , T_QualMoisture_Middle.GBGradeID = vRoastingPlan.GBGradeID , T_QualMoisture_Middle.ProdLineID = vRoastingPlan.ProdLineID , T_QualMoisture_Middle.PurchaseID = vRoastingPlan.PurchaseID , T_QualMoisture_Middle.TLProcTypeID = vRoastingPlan.TLProcTypeID , T_QualMoisture_Middle.CustCode = vRoastingPlan.CustCode FROM vRoastingPlan WHERE T_QualMoisture_Middle.V_Produce_ID = vRoastingPlan.ProductionNo AND T_QualMoisture_Middle.V_Batch_ID = vRoastingPlan.FormulaNo SELECT * FROM T_QualMoisture_Middle
数据库关系图:
最终浏览器效果:
客户端展示效果: