首页 > 代码库 > 《BI项目笔记》基于雪花模型的维度设计
《BI项目笔记》基于雪花模型的维度设计
原文:《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_QualityModelAdminWHERE F_CustomerCode NOT IN ( SELECT C_CustCode FROM T_CustomInfo )DELETE FROM T_TIR_QualityModelAdminWHERE F_OriginID NOT IN ( SELECT I_OriginID FROM T_Origin ) UPDATE T_TIR_QualityModelAdminSET T_TIR_QualityModelAdmin.F_Tag = T_GBGradeCode.I_GBGradeIDFROM T_GBGradeCodeWHERE T_TIR_QualityModelAdmin.F_GradeCode = T_GBGradeCode.V_GBGradeCodeDELETE FROM T_TIR_QualityModelAdminWHERE T_TIR_QualityModelAdmin.F_Tag NOT IN ( SELECT I_GBGradeID FROM T_GBGradeCode )ALTER TABLE T_Origin ALTER COLUMN I_OriginPID INT NULLUPDATE [T_Origin]SET [I_OriginPID] = NULLWHERE [I_OriginPID] = 0
QualMoistureMiddleRawChemistry
QualMoistureMiddle
数据处理：
ALTER TABLE T_TeamOrder ALTER COLUMN V_TeamOrderCode VARCHAR(10) NOT NULLALTER TABLE T_QualMoisture_Middle ADD DeptID INT NULLALTER TABLE T_QualMoisture_Middle ADD TeamOrderCode VARCHAR(10) NULLUPDATE T_QualMoisture_MiddleSET DeptID = T_Department.I_DepIDFROM T_DepartmentWHERE T_QualMoisture_Middle.V_Team = T_Department.V_DepNameUPDATE T_QualMoisture_MiddleSET TeamOrderCode = T_TeamOrder.V_TeamOrderCodeFROM T_TeamOrderWHERE T_QualMoisture_Middle.V_Team_Order = T_TeamOrder.V_TeamOrder
RoastingPlan
数据处理：
ALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD ProdLineID VARCHAR(10) NULLALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD TLProcTypeID INT NULLALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD PurchaseID INT NULLALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD OriginID INT NULLALTER TABLE TB_MRP_ROASTING_PLAN_DETAIL2 ADD CustCode VARCHAR(5) NULL-- 生产线UPDATE TB_MRP_ROASTING_PLAN_DETAIL2SET TB_MRP_ROASTING_PLAN_DETAIL2.ProdLineID = T_ManuProductLine.V_LineCodeFROM T_ManuProductLineWHERE TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_ROASTING_DEVICE = T_ManuProductLine.V_ProdLineDELETE FROM TB_MRP_ROASTING_PLAN_DETAIL2WHERE ProdLineID IS NULL-- 加工类型UPDATE TB_MRP_ROASTING_PLAN_DETAIL2SET TB_MRP_ROASTING_PLAN_DETAIL2.TLProcTypeID = T_TLProcType.I_TLProcTypeCodeFROM T_TLProcTypeWHERE TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_MANUFACTURE_TYPE = T_TLProcType.V_TLProcTypeDELETE FROM TB_MRP_ROASTING_PLAN_DETAIL2WHERE TLProcTypeID IS NULL-- 收购类型UPDATE TB_MRP_ROASTING_PLAN_DETAIL2SET TB_MRP_ROASTING_PLAN_DETAIL2.PurchaseID = T_PurchaseType.I_PurchaseIDFROM T_PurchaseTypeWHERE TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_FT_PURCHASE_TYPE = T_PurchaseType.V_PurchaseType-- 成品产地 UPDATE TB_MRP_ROASTING_PLAN_DETAIL2SET TB_MRP_ROASTING_PLAN_DETAIL2.OriginID = T_Origin.I_OriginIDFROM T_OriginWHERE TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_RT_AREA = T_Origin.V_OriginDELETE FROM TB_MRP_ROASTING_PLAN_DETAIL2WHERE OriginID IS NULL-- 客户UPDATE TB_MRP_ROASTING_PLAN_DETAIL2SET TB_MRP_ROASTING_PLAN_DETAIL2.CustCode = T_CustomInfo.C_CustCodeFROM T_CustomInfoWHERE TB_MRP_ROASTING_PLAN_DETAIL2.COL_RPD_RT_OWNER = T_CustomInfo.V_CustomerDELETE FROM TB_MRP_ROASTING_PLAN_DETAIL2WHERE CustCode IS NULL
《BI项目笔记》基于雪花模型的维度设计
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。