创建Cube操作手册.docx

1、创建Cube操作手册第1章 前言本操作手册主要用于在创建OLAP数据模型时，给用户一个指导性操作步骤。OLAP模型的建立主要有以下步骤和关键点：1） 需求调研此时主要是通过和用户进行沟通，明确用户有什么样需求，有哪些需求。2） 需求分析当明确用户的需求时，开发人员应该针对用户的需求进行分析。分析的目的主要是分析现有系统有没有相应的数据支持，能不能做等。并规划以后的开发过程。3） 系统设计详细的需求说明书确认后，此时就要整合所有的需求，进行归类，分析共性和异性。确认实现的整个架构，统一的规范等。4） 详细设计依据系统设计书，开发详细的程序5） 创建Cube模型建模型本操作手册以原奶收购情况分析作

2、为例子指导第2章 用户需求2.1 原始需求需求说明书中原奶收购情况分析的需求如下：原奶收购情况分析 分析目的分析牛奶收奶完成情况。 分析主体 伊利集团液态奶事业部领导。 数据来源指标名称指标解释指标计算公式取数来源实际收奶量工厂每月原奶实际收购量工厂在ERP系统录入的实际收奶量ERP供应链系统牛奶收购情况报表年计划收奶量指一年计划的用奶量BI系统月计划收奶量指一月计划的用奶量BI系统日计划收奶量指一日计划的用奶量BI系统 分析指标1. 基础指标：实际收奶量、年计划收奶量、月计划收奶量、日计划收奶量衍生指标：计划量2. 时间维度上的聚合关系：基础指标为加和汇总。 分析维度1、 时间：年-月-日品

3、种： A+有机奶、A+ 原料奶、A 原料奶、A- 原料奶机构：工厂区域：东北区域、中南区域、西北区域 单位：吨 分析案例1. 展示示例：液态奶事业部管理层：i. 时间为日（查询的日期需求为时间段，开始时间：年-月-日，结束日期：年-月-日）ii. 展示品种：A+有机奶、A+ 原料奶、A 原料奶、A- 原料奶iii. 展现工厂iv. 展现区域：东北区域、中南区域、西北区域v. 展示相应的分析指标。 信息展现需求1、 分析可以按照时间点或时间段进行查询；除提供本期查询分析外，并支持同比分析，环比分析，累计分析等及丰富灵活的图形化展示，包括：柱状图，饼图，折线图，地图形式，气泡图，堆积图等等2、 地

4、图展示：提供指标告警2.2 需求分析分析指标：在第2.1节的分析指标中我们可以看到总共有4个指标实际收奶量、年计划收奶量、月计划收奶量、日计划收奶量。通过分析我们知道，在ERP系统中，牛奶是通过杂项入库的方式维护到ERP当中的。但收奶计划量是业务部门通过制订计划，由外部数据Excel手工维护的。因此通过分析数据来源，我们可以清楚的知道，该需求的数据主要有两部分：ERP系统和外部数据。分析维度：1） 时间：在时间维度上，有三种选择，年、月、日2） 品种：在ERP系统中，对原奶来说，实际上就是批次表中相应的批次号3） 机构：原奶收购量在维护进ERP时，是入到具体的原料仓中，所以通过仓库代码就可以找

5、到对应的机构。机构肯定在某个固定的区域内。4） 区域：对于区域的划分，在ERP中并没有做明确的维护。因此需要手工定义并维护。ERP系统中手工维护区域和仓库的关系，因此区域机构仓库可以建立对应关系。2.3 结论分析指标：实际收奶量 ERP系统 计划量 外部数据分析维度：时间 系统产生品种 手工维护机构 系统产生区域 手工维护第3章 系统设计命名规范：1）维度：DW_DIM_XXXX2）事实表：DW_FACT_XXXX3.1 维度模型 1）时间create table DW.DW_DIM_TIME_DAY( YEAR_ID VARCHAR2(8) not null, YEAR_NAME VARCH

6、AR2(20) not null, YEAR_ORDER VARCHAR2(8) not null, HALFYEAR_ID VARCHAR2(8) not null, HALFYEAR_NAME VARCHAR2(20) not null, HALFYEAR_ORDER VARCHAR2(8) not null, QUARTER_ID VARCHAR2(8) not null, QUARTER_NAME VARCHAR2(20) not null, QUARTER_ORDER VARCHAR2(8) not null, MONTH_ID VARCHAR2(8) not null, MONTH

7、_NAME VARCHAR2(20) not null, MONTH_ORDER VARCHAR2(8) not null, TENDAYS_ID VARCHAR2(8) not null, TENDAYS_NAME VARCHAR2(20) not null, TENDAYS_ORDER VARCHAR2(8) not null, FIVEDAYS_ID VARCHAR2(8), FIVEDAYS_NAME VARCHAR2(50), FIVEDAYS_ORDER VARCHAR2(8), DAY_ID VARCHAR2(8) not null, DAY_NAME VARCHAR2(20)

8、not null, DAY_ORDER VARCHAR2(49) not null, DUMMY_LEVEL NUMBER)tablespace DW 2）等级奶维度手工维护create table DW.DW_DIM_MILKGRADE( GRADE_ID NUMBER(5), LOT_NO VARCHAR2(32), LOT_DESC VARCHAR2(40), PARENT_ID NUMBER(15), SHOW_ORDER VARCHAR2(32), ROLLUP_METHOD VARCHAR2(1) default + not null)tablespace DW 3）区域机构维度手

9、工维护create table DW.DW_DIM_MILK_DEPOT( WHSE_ID NUMBER(15), WHSE_CODE VARCHAR2(20), WHSE_NAME VARCHAR2(200), PARENT_ID NUMBER(15), SHOW_ORDER VARCHAR2(32), ROLLUP_METHOD VARCHAR2(1) default + not null)tablespace DW 4）指标维度指标表dw_dim_measure_all与dw_dim_msr_olap_info配置如下：将配置表中数据插入到dw_dim_measure_all与dw_di

10、m_msr_olap_info中。指标维DW.VW_DIM_MEASURE_MILK视图如下：CREATE OR REPLACE VIEW DW.VW_DIM_MEASURE_MILK ASSELECT t.MSR_ID FACT_VALUE_ID, t.MSR_OLAP_NAME FACT_VALUE_NAME, t.ROLLUP_METHOD CALL_METHOD, t.PARENT_MSR_ID PARENT_ID, t.OLAP_ATTRIBUTE1 MEASURE_FLAG, t.CUBE_ID,t.PARENT_MSR_ID,t.MSR_ID,t.MSR_NAME,t.CALC_

11、ENGINE,t.CALC_TYPE,t.TIME_TYPE,t.CALC_LEVEL,t.DEFAULT_JD,t.MSR_CATEGORY,t.MSR_SUB_CATEGORY,t.LINK_MSR1_TYPE,t.LINK_MSR1_ID,t.LINK_MSR1_NULLABLE,t.LINK_MSR2_TYPE,t.LINK_MSR2_ID,t.LINK_MSR2_NULLABLE,t.EXTERNAL_MSR_FLAG,t.MSR_ATTRIBUTE1,t.MSR_ATTRIBUTE2,t.MSR_ATTRIBUTE3,t.MSR_ATTRIBUTE4,t.MSR_ATTRIBUTE

12、5,t.SHOW_ORDER,t.ROLLUP_METHOD,t.ALT_MSR_NAME,t.MSR_OLAP_NAME,t.OLAP_ATTRIBUTE1,t.OLAP_ATTRIBUTE2,t.OLAP_ATTRIBUTE3,t.OLAP_ATTRIBUTE4,t.OLAP_ATTRIBUTE5FROM dw.vw_dw_dim_measure_all tWHERE t.CUBE_ID=MILK3.2 事实表模型 通过分析知道，原奶收购情况分析涉及到时间、机构、等级奶、指标维，因此事实表结构如下：create table DW.DW_FACT_MILK( TIME_ID VARCHAR2

13、(8), WHSE_ID NUMBER(15), ITEM_ID NUMBER(10), ITEM_NO VARCHAR2(32), GRADE_ID VARCHAR2(10), FACT_VALUE_ID VARCHAR2(10), FACT_VALUE NUMBER)tablespace DW3.3 数据处理过程（ERP）该过程主要统计原奶收购量。/* * Author : tyc * Created : 2009-8-9 * Purpose : 原奶收购情况统计*/procedure ld_f_milk(p_begin_time auto_etl_inc_control_dw.begin

14、_time%type, p_end_time auto_etl_inc_control_dw.end_time%type, p_etl_all varchar2 default N, p_etl_auto varchar2 default N)is -记录数据处理用时 v_use_time date;begin v_use_time := sysdate; if p_etl_all = Y then pkg_fbi_common.Truncate_Table(DW, dw_fact_milk); pkg_fbi_etl_ctl.write_log(一般, 【DW】_TRUNCATE原奶收购数据

15、表_ | p_begin_time | | p_end_time | | sql%rowcount, 3, sysdate - v_use_time); commit; else delete dw.dw_fact_milk t where t.fact_value_id in (G_MEASURE50_ID,G_MEASURE51_ID,G_MEASURE55_ID) and t.time_id between p_begin_time and p_end_time; pkg_fbi_etl_ctl.write_log(一般, 【DW】_删除原奶收购数据表_ | p_begin_time |

16、 | p_end_time | | sql%rowcount, 3, sysdate - v_use_time); commit; end if; -统计原奶收购情况 insert into dw.dw_fact_milk ( time_id, -时间 orgn_id, -机构ID whse_id, -仓库ID whse_code, -仓库编码 item_id, -品项ID item_no, -品项编码 grade_id, -等级奶 fact_value_id, -指标ID fact_value -指标值 ) -原奶收购量第一计量单位 select to_char(tran.trans_dat

17、e,yyyymmdd) day_id, -事务日期 nvl(hr.operating_unit,-99999999) orgn_id, -机构ID nvl(whse.mtl_organization_id,-99999999) whse_id, -仓库ID tran.whse_code whse_code, -仓库代码 tran.item_id item_id, -品项ID item.item_no item_no, -品项编码 decode(lots.lot_no,A,A+, A,A-, A2,A+2, A,A+2, A+,A+2,lots.lot_no) grade_id, -等级ID G

18、_MEASURE50_ID fact_value_id, -指标ID nvl(sum(tran.trans_qty),0)/1000 fact_value -收购量 from ods.ic_tran_vw1 tran, -库存事务表 ods.ic_lots_mst lots, -原辅料批次表 ods.ic_item_mst_b item, -品项表 ods.ic_whse_mst whse, -仓库表 ods.inv_organization_info_v hr where tran.item_id = lots.item_id and tran.lot_id = lots.lot_id an

19、d tran.item_id = item.item_id and (tran.doc_type = CREI or tran.doc_type = ADJI) and (tran.line_type = 0 or tran.line_type = -1) and (tran.item_id = 2996 or tran.item_id = 56671) and pleted_ind = 1 and tran.whse_code = whse.whse_code and hr.organization_code(+) = tran.whse_code and tran.REASON_CODE

20、in (/*液奶*/1163 ,1263 ,1300 ,1301 ,1942,1060,/*奶粉*/2601) and lots.lot_no in (A,A+,A-,A+2,A,A,A2,A+,A) and tran.TRANS_DATE = to_date(p_begin_time,yyyy-mm-dd) and tran.TRANS_DATE to_date(p_end_time,yyyy-mm-dd) + 1 group by to_char(tran.trans_date,yyyymmdd), -事务日期 nvl(hr.operating_unit,-99999999), nvl(w

21、hse.mtl_organization_id,-99999999), tran.whse_code, -仓库代码 tran.item_id, item.item_no, lots.lot_no having nvl(sum(tran.trans_qty),0) 0 union all -原奶收购量第2计量单位 select to_char(tran.trans_date,yyyymmdd) day_id, -事务日期 nvl(hr.operating_unit,-99999999) orgn_id, -机构ID nvl(whse.mtl_organization_id,-99999999)

22、whse_id, -仓库ID tran.whse_code whse_code, -仓库代码 tran.item_id item_id, -品项ID item.item_no item_no, -品项编码 decode(lots.lot_no,A,A+, A,A-, A2,A+2, A,A+2, A+,A+2,lots.lot_no) grade_id, -等级ID G_MEASURE51_ID fact_value_id, -指标ID nvl(sum(tran.trans_qty2),0)/1000 fact_value -指标值 from ods.ic_tran_vw1 tran, -库存

23、事务表 ods.ic_lots_mst lots, -原辅料批次表 ods.ic_item_mst_b item, -品项表 ods.ic_whse_mst whse, ods.inv_organization_info_v hr where tran.item_id = lots.item_id and tran.lot_id = lots.lot_id and tran.item_id = item.item_id and (tran.doc_type = CREI or tran.doc_type = ADJI) and (tran.line_type = 0 or tran.line_

24、type = -1) -是否可在配置表中配置 and (tran.item_id = 2996 or tran.item_id = 56671) -是否可在配置表中配置 and pleted_ind = 1 and tran.whse_code = whse.whse_code and hr.organization_code(+) = tran.whse_code and tran.REASON_CODE in (/*液奶*/1163 ,1263 ,1300 ,1301 ,1942,1060,/*奶粉*/2601) and lots.lot_no in (A,A+,A-,A+2,A,A,A2

25、,A+,A) and tran.TRANS_DATE = to_date(p_begin_time,yyyy-mm-dd) and tran.TRANS_DATE to_date(p_end_time,yyyy-mm-dd) + 1 group by to_char(tran.trans_date,yyyymmdd), -事务日期 nvl(hr.operating_unit,-99999999), nvl(whse.mtl_organization_id,-99999999), tran.whse_code, -仓库代码 tran.item_id, item.item_no, lots.lot

26、_no having nvl(sum(tran.trans_qty2),0)/1000 0 union all -原奶库存量 select a.day_id day_id, -事务日期 nvl(hr.operating_unit,-99999999) orgn_id, -机构ID nvl(whse.mtl_organization_id,-99999999) whse_id, -仓库ID tran.whse_code whse_code, -仓库代码 tran.item_id item_id, -品项ID item.item_no item_no, -品项编码 decode(lots.lot_

27、no,A,A+, A,A-, A2,A+2, A,A+2, A+,A+2,lots.lot_no) grade_id, -等级ID G_MEASURE55_ID fact_value_id, -指标ID sum(case when to_char(tran.trans_date,yyyymmdd) = a.day_id then nvl(tran.trans_qty,0) end)/1000 fact_value -指标值 from ods.ic_tran_vw1 tran, -库存事务表 ods.ic_lots_mst lots, -原辅料批次表 ods.ic_item_mst_b item

28、, -品项表 ods.ic_whse_mst whse, ods.inv_organization_info_v hr, ods.i_time_day a where tran.item_id = lots.item_id and tran.lot_id = lots.lot_id and tran.item_id = item.item_id and (tran.item_id = 2996 or tran.item_id = 56671) and tran.whse_code = whse.whse_code and tran.whse_code = hr.organization_cod

29、e(+) and lots.lot_no in (A,A+,A-,A+2,A,A,A2,A+,A) and a.day_id between to_char(tran.trans_date,yyyymmdd) and a.day_id and a.day_id between p_begin_time and p_end_time group by a.day_id, -事务日期 nvl(hr.operating_unit,-99999999), nvl(whse.mtl_organization_id,-99999999), tran.whse_code, -仓库代码 tran.item_id, item.item_no, lots.lot_no having sum(case when to_char(tran.trans_date,yyyymmdd) = a.day_id then nvl(tran.trans_qty,0) end)/1000 0 ; pkg_fbi_etl_ctl.write_log(一般, 【DW】_原奶收购