《BI项目笔记》历年的初烟水分均值变化分析Cube的建立

原文:《BI项目笔记》历年的初烟水分均值变化分析Cube的建立

主要维度：

班组

班次

检测项

质检日期（时间维度）

加工客户

加工类型

收购类型

生产线

产地

烟叶级别

主要指标：
慢速测定_平均值
快速测定_平均值
红外测定_平均值

ETL设计

需要抽取的维度表：

需要抽取的事实表：

建立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

 数据库关系图：

最终浏览器效果：

客户端展示效果：