原文:《BI项目笔记》报到信息分析Cube
数据源设置:
数据处理逻辑:
--处理丢失外键关系数据 SELECT * FROM T_ReportLeafGrade WHERE FSubFID NOT IN ( SELECT FID FROM T_RaceLeafReport ) DELETE FROM T_ReportLeafGrade WHERE FSubFID NOT IN ( SELECT FID FROM T_RaceLeafReport ) --处理供应商关系 SELECT * FROM T_RaceLeafReport WHERE V_Customer NOT IN ( SELECT C_CustCode FROM T_ProviderInfo ) --更改字段类型 ALTER TABLE T_RaceLeafReport ALTER COLUMN V_Customer INT --设置主键为NOT NULL ALTER TABLE T_ProviderInfo ALTER COLUMN C_CustCode INT NOT NULL --设置主键 ALTER TABLE T_ProviderInfo ADD CONSTRAINT PK_CustCode PRIMARY KEY(C_CustCode) --承运单位 SELECT * FROM T_RaceLeafReport WHERE I_CarryID NOT IN ( SELECT I_CarryID FROM T_RaceCarryUnitInfo ) --运输工具 SELECT * FROM T_RaceLeafReport WHERE I_ConveyanceID NOT IN ( SELECT I_ConveyanceID FROM T_RaceConveyance ) --收购类型 SELECT * FROM T_RaceLeafReport WHERE I_PurchaseTypeID NOT IN ( SELECT I_PurchaseID FROM T_PurchaseType ) --加工类型 --新增I_TLProcTypeCode字段 ALTER TABLE T_RaceLeafReport ADD I_TLProcTypeCode INT NULL UPDATE T_RaceLeafReport SET T_RaceLeafReport.I_TLProcTypeCode = T_TLProcType.I_TLProcTypeCode FROM T_TLProcType WHERE T_RaceLeafReport.V_TLProcType = T_TLProcType.V_TLProcType DELETE FROM T_RaceLeafReport WHERE I_TLProcTypeCode IS NULL --提交标志\删除标记 DELETE FROM T_RaceLeafReport WHERE V_IsSubmit = '未提交' DELETE FROM T_RaceLeafReport WHERE I_DeleteFlag IN ( 1, 2 ) --新增业务日期BusinessDate字段 ALTER TABLE T_RaceLeafReport ADD BusinessDate INT NULL --将datetime转为int UPDATE T_RaceLeafReport SET BusinessDate = CAST(REPLACE(CONVERT(CHAR(10), D_ReportDate, 120), '-', '') AS INT) --产地处理 DELETE FROM T_ReportLeafGrade WHERE I_ShapeCode NOT IN ( SELECT I_OriginID FROM T_Origin )
需要抽取的维度表:
序号 |
表名 |
说明 |
备注 |
1 |
T_ProviderInfo |
供货商 |
|
2 |
T_RaceCarryUnitInfo |
承运单位 |
|
3 |
T_RaceConveyance |
运输工具 |
|
4 |
T_GBGradeCode |
烟叶级别 |
|
5 |
T_Distinction |
烟叶等级 |
|
6 |
T_Origin |
产地 |
|
7 |
T_PurchaseType |
收购类型 |
|
8 |
T_TLProcType |
加工类型 |
|
需要抽取的事实表:
序号 |
表名 |
说明 |
备注 |
1 |
T_RaceLeafReport |
报到主表 |
|
2 |
T_ReportLeafGrade |
报到子表 |
|
主要指标:
I_PieceCount 件数_合计
I_Weight 重量_合计
项目结构:
浏览效果:
客户端效果:
时间: 2024-10-31 21:39:42