《BI项目笔记》基于雪花模型的维度设计

GBGradeCode

外键关系：

1	烟叶等级	T_GBGradeCode.I_DistinctionID=T_Distinction.I_DistinctionID	烟叶等级分为：上等烟、中等烟、下等烟、末等烟、低等烟、低次等烟、其它、下低等烟
2	分级标准	T_GBGradeCode.I_GradStanCode=T_GradeStandard.I_GradStanCode	取值为：四十二级、二十八级、自定义、其他、工艺级别
3	烟叶烤型	T_GBGradeCode.C_Type=T_TobaccoLeafType.C_Type	取值为：白肋烟、烤烟

QualityModel

外键关系：

T_TIR_QualityModelAdmin.F_OriginID = T_Origin.I_OriginID
T_TIR_QualityModelAdmin.F_CustomerCode = T_CustomInfo.T_CustomInfo
T_TIR_QualityModelAdmin.F_Tag = T_GBGradeCode.I_GBGradeID

数据处理：

ALTER TABLE T_TIR_QualityModelAdmin ALTER COLUMN F_CustomerCode VARCHAR(5)

DELETE  FROM T_TIR_QualityModelAdmin
WHERE   F_CustomerCode NOT IN ( SELECT  C_CustCode
                                FROM    T_CustomInfo )

DELETE  FROM T_TIR_QualityModelAdmin
WHERE   F_OriginID NOT IN ( SELECT  I_OriginID
                            FROM    T_Origin )              

UPDATE  T_TIR_QualityModelAdmin
SET     T_TIR_QualityModelAdmin.F_Tag = T_GBGradeCode.I_GBGradeID
FROM    T_GBGradeCode
WHERE   T_TIR_QualityModelAdmin.F_GradeCode = T_GBGradeCode.V_GBGradeCode

DELETE  FROM T_TIR_QualityModelAdmin
WHERE   T_TIR_QualityModelAdmin.F_Tag NOT IN ( SELECT   I_GBGradeID
                                               FROM     T_GBGradeCode )

ALTER TABLE T_Origin ALTER COLUMN I_OriginPID INT NULL

UPDATE  [T_Origin]
SET     [I_OriginPID] = NULL
WHERE   [I_OriginPID] = 0

QualMoistureMiddleRawChemistry

QualMoistureMiddle

数据处理：

ALTER TABLE T_TeamOrder ALTER COLUMN V_TeamOrderCode VARCHAR(10) NOT NULL
ALTER TABLE T_QualMoisture_Middle ADD DeptID INT NULL
ALTER TABLE T_QualMoisture_Middle ADD TeamOrderCode VARCHAR(10) NULL

UPDATE  T_QualMoisture_Middle
SET     DeptID = T_Department.I_DepID
FROM    T_Department
WHERE   T_QualMoisture_Middle.V_Team = T_Department.V_DepName

UPDATE  T_QualMoisture_Middle
SET     TeamOrderCode = T_TeamOrder.V_TeamOrderCode
FROM    T_TeamOrder
WHERE   T_QualMoisture_Middle.V_Team_Order = T_TeamOrder.V_TeamOrder

RoastingPlan

数据处理：

ALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD ProdLineID VARCHAR(10) NULL
ALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD TLProcTypeID INT NULL
ALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD PurchaseID INT NULL
ALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD OriginID INT NULL
ALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD CustCode VARCHAR(5) NULL

-- 生产线
UPDATE  TB_MRP_ROASTING_PLAN_DETAIL2
SET     TB_MRP_ROASTING_PLAN_DETAIL2.ProdLineID = T_ManuProductLine.V_LineCode
FROM    T_ManuProductLine
WHERE   TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_ROASTING_DEVICE = T_ManuProductLine.V_ProdLine

DELETE  FROM TB_MRP_ROASTING_PLAN_DETAIL2
WHERE   ProdLineID IS NULL

-- 加工类型
UPDATE  TB_MRP_ROASTING_PLAN_DETAIL2
SET     TB_MRP_ROASTING_PLAN_DETAIL2.TLProcTypeID = T_TLProcType.I_TLProcTypeCode
FROM    T_TLProcType
WHERE   TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_MANUFACTURE_TYPE = T_TLProcType.V_TLProcType

DELETE  FROM TB_MRP_ROASTING_PLAN_DETAIL2
WHERE   TLProcTypeID IS NULL

-- 收购类型
UPDATE  TB_MRP_ROASTING_PLAN_DETAIL2
SET     TB_MRP_ROASTING_PLAN_DETAIL2.PurchaseID = T_PurchaseType.I_PurchaseID
FROM    T_PurchaseType
WHERE   TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_FT_PURCHASE_TYPE = T_PurchaseType.V_PurchaseType

-- 成品产地 
UPDATE  TB_MRP_ROASTING_PLAN_DETAIL2
SET     TB_MRP_ROASTING_PLAN_DETAIL2.OriginID = T_Origin.I_OriginID
FROM    T_Origin
WHERE   TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_RT_AREA = T_Origin.V_Origin

DELETE  FROM TB_MRP_ROASTING_PLAN_DETAIL2
WHERE   OriginID IS NULL

-- 客户
UPDATE  TB_MRP_ROASTING_PLAN_DETAIL2
SET     TB_MRP_ROASTING_PLAN_DETAIL2.CustCode = T_CustomInfo.C_CustCode
FROM    T_CustomInfo
WHERE   TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_RT_OWNER = T_CustomInfo.V_Customer

DELETE  FROM TB_MRP_ROASTING_PLAN_DETAIL2
WHERE   CustCode IS NULL