创建Cube操作手册
第1章前言
本操作手册主要用于在创建OLAP数据模型时，给用户一个指导性操作步骤。
OLAP模型的建立主要有以下步骤和关键点：
1）需求调研
此时主要是通过和用户进行沟通，明确用户有什么样需求，有哪些需求。
2）需求分析
当明确用户的需求时，开发人员应该针对用户的需求进行分析。分析的目的主要是
分析现有系统有没有相应的数据支持，能不能做等。并规划以后的开发过程。
3）系统设计
详细的需求说明书确认后，此时就要整合所有的需求，进行归类，分析共性和异性。
确认实现的整个架构，统一的规范等。
4）详细设计
依据系统设计书，开发详细的程序
5）创建Cube模型
建模型
本操作手册以《原奶收购情况分析》作为例子指导
第2章用户需求
2.1 原始需求
需求说明书中原奶收购情况分析的需求如下：
原奶收购情况分析
?分析目的
分析牛奶收奶完成情况。
?分析主体
伊利集团液态奶事业部领导。
?数据来源
?分析指标
1.基础指标：实际收奶量、年计划收奶量、月计划收奶量、日计划收奶量
衍生指标：计划量
2.时间维度上的聚合关系：基础指标为加和汇总。
?分析维度
1、时间：年-月-日
品种： A++有机奶、A+ 原料奶、A 原料奶、A- 原料奶
机构：工厂
区域：东北区域、中南区域、西北区域
单位：吨
?分析案例
1.展示示例：
液态奶事业部管理层：
i.时间为日（查询的日期需求为时间段，开始时间：年-月-日，结束日期：年-
月-日）
ii.展示品种：A++有机奶、A+ 原料奶、A 原料奶、A- 原料奶
iii.展现工厂
iv.展现区域：东北区域、中南区域、西北区域
v.展示相应的分析指标。
?信息展现需求
1、分析可以按照时间点或时间段进行查询；除提供本期查询分析外，并支持同比分析，环
比分析，累计分析等及丰富灵活的图形化展示，包括：柱状图，饼图，折线图，地图形式，气泡图，堆积图等等
2、地图展示：提供指标告警
2.2需求分析
分析指标：
在第2.1节的分析指标中我们可以看到总共有4个指标实际收奶量、年计划收奶量、月计划收奶量、日计划收奶量。
通过分析我们知道，在ERP系统中，牛奶是通过杂项入库的方式维护到ERP当中的。但收奶计划量是业务部门通过制订计划，由外部数据Excel手工维护的。因此通过分析数据来源，我们可以清楚的知道，该需求的数据主要有两部分：ERP系统和外部数据。
分析维度：
1）时间：在时间维度上，有三种选择，年、月、日
2）品种：在ERP系统中，对原奶来说，实际上就是批次表中相应的批次号
3）机构：原奶收购量在维护进ERP时，是入到具体的原料仓中，所以通过仓库代码就可以找到对应的机构。机构肯定在某个固定的区域内。
4）区域：对于区域的划分，在ERP中并没有做明确的维护。因此需要手工定义并维护。
ERP系统中手工维护区域和仓库的关系，因此区域—机构—仓库可以建立对应关系。
2.3结论
分析指标：
实际收奶量 ERP系统
计划量外部数据
分析维度：
时间系统产生
品种手工维护
机构系统产生
区域手工维护
第3章系统设计
命名规范：
1）维度：DW_DIM_XXXX
2）事实表：DW_FACT_XXXX
3.1 维度模型
1）时间
create table DW.DW_DIM_TIME_DAY
(
YEAR_ID VARCHAR2(8) not null,
YEAR_NAME VARCHAR2(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_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) 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）区域机构维度
手工维护
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配置如下：
C:\Documents and
Settings\Administrat
将配置表中数据插入到dw_dim_measure_all与dw_dim_msr_olap_info中。
指标维DW.VW_DIM_MEASURE_MILK视图如下：
CREATE OR REPLACE VIEW DW.VW_DIM_MEASURE_MILK AS
SELECT
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_ENGINE",t."CALC_T YPE",t."TIME_TYPE",t."CALC_LEVEL",t."DEFAULT_JD",t."MSR_CATEGORY",t."MSR_SUB_CA TEGORY",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_ATT RIBUTE1",t."MSR_ATTRIBUTE2",t."MSR_ATTRIBUTE3",t."MSR_ATTRIBUTE4",t."MSR_ATTRIB UTE5",t."SHOW_ORDER",t."ROLLUP_METHOD",t."ALT_MSR_NAME",t."MSR_OLAP_NAME",t."OL AP_ATTRIBUTE1",t."OLAP_ATTRIBUTE2",t."OLAP_ATTRIBUTE3",t."OLAP_ATTRIBUTE4",t."O LAP_ATTRIBUTE5"
FROM dw.vw_dw_dim_measure_all t
WHERE t.CUBE_ID='MILK'
3.2 事实表模型
通过分析知道，原奶收购情况分析涉及到时间、机构、等级奶、指标维，因此事实表结构如下：
create table DW.DW_FACT_MILK
(
TIME_ID VARCHAR2(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 DW
3.3 数据处理过程（ERP）
该过程主要统计原奶收购量。
/*
* Author : tyc
* Created : 2009-8-9
* Purpose : 原奶收购情况统计
*/
procedure ld_f_milk(p_begin_time auto_etl_inc_control_dw.begin_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原奶收购数据表_[' || 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 || '~' ||
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_date,'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-',
'A＋2','A+2',
'A＋＋','A+2',
'A++','A+2',lots.lot_no) grade_id, --等级ID
G_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
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_type = -1)
and (tran.item_id = 2996 or tran.item_id = 56671)
and https://www.360docs.net/doc/9114053821.html,pleted_ind = 1
and tran.whse_code = whse.whse_code
and https://www.360docs.net/doc/9114053821.html,anization_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－','A＋2','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_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) 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-',
'A＋2','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, --库存事务表
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_type = -1)
--是否可在配置表中配置
and (tran.item_id = 2996 or tran.item_id = 56671)
--是否可在配置表中配置
and https://www.360docs.net/doc/9114053821.html,pleted_ind = 1
and tran.whse_code = whse.whse_code
and https://www.360docs.net/doc/9114053821.html,anization_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－','A＋2','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_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_no,'A＋','A+',
'A－','A-',
'A＋2','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, --品项表
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 = https://www.360docs.net/doc/9114053821.html,anization_code(+)
and lots.lot_no in ('A','A+','A-','A+2','A＋','A－','A＋2','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】_原奶收购数据表_[' || p_begin_time || '~' || p_end_time || ']' || sql%rowcount,
3,
sysdate - v_use_time);
commit;
exception
when others then
rollback;
pkg_fbi_etl_ctl.write_log('异常',
'【DW】_原奶收购数据表_' || sqlcode || sqlerrm, 2);
commit;
if p_etl_auto = 'Y' then
raise;
end if;
end;
3.4 数据处理过程（外部）
该过程统计外部数据
/*
* Author : tyc
* Created : 2009-8-9
* Purpose : 原奶收购计划情况统计
*/
procedure ld_f_ex_milkplan(p_begin_time auto_etl_inc_control_dw.begin_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;
v_date 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原奶收购情况统计表_[' || p_begin_time || '~' ||
p_end_time || ']' || sql%rowcount,
3,
sysdate - v_use_time);
commit;
else
--删除年计划
delete dw.dw_fact_milk t
where substr(t.time_id,1,4) between substr(p_begin_time,1,4) and substr(p_end_time,1,4)
and t.fact_value_id = G_MEASURE52_ID;
--删除月计划
delete dw.dw_fact_milk t
where substr(t.time_id,1,6) between substr(p_begin_time,1,6) and substr(p_end_time,1,6)
and t.fact_value_id = G_MEASURE53_ID;
--删除日计划
delete dw.dw_fact_milk t
where t.time_id between p_begin_time and p_end_time
and t.fact_value_id = G_MEASURE54_ID;
pkg_fbi_etl_ctl.write_log('一般',
'【DW】_删除原奶收购情况统计表(原奶计划)_[' || p_begin_time || '~' ||
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
a.day_id,
https://www.360docs.net/doc/9114053821.html,n_id,
https://www.360docs.net/doc/9114053821.html,n_id whse_id,
'-1' whse_code,
-1 item_id,
'-1' item_no,
decode(t.grade_id,'A++','A+2','A++','A+2',t.grade_id) grade_id, G_MEASURE52_ID fact_value_id,
(
case when t.day_id = a.year_id and a.year_id < substr(p_end_time,1,4) then
nvl2((add_months(to_date(t.day_id||'0101','yyyymmdd'),12)-to_date(t.day_id||'01 01','yyyymmdd')),t.year_plan/(add_months(to_date(t.day_id||'0101','yyyymmdd'),1
2)-to_date(t.day_id||'0101','yyyymmdd')),0)
else
nvl2((to_date(p_end_time,'yyyymmdd')-to_date(t.day_id||'0101','yyyymmdd')+1),t. year_plan/(to_date(p_end_time,'yyyymmdd')-to_date(t.day_id||'0101','yyyymmdd')+ 1),0)
end
) fact_value
from ods.excel_milkplan_mst t,
ods.i_time_day a
where t.day_id = a.year_id
and t.group_name <> '原奶'
and a.day_id between t.day_id||'0101' and a.day_id
and a.day_id between substr(p_begin_time,1,4)||'0101' and p_end_time union all
--月计划
--平均到每天
select a.day_id,
https://www.360docs.net/doc/9114053821.html,n_id,
https://www.360docs.net/doc/9114053821.html,n_id whse_id,
'-1' whse_code,
-1 item_id,
'-1' item_no,
decode(t.grade_id,'A++','A+2','A++','A+2',t.grade_id) grade_id, G_MEASURE53_ID fact_value_id,
(
case when t.day_id = a.month_id and a.month_id < substr(p_end_time,1,6) then
nvl2((last_day(to_date(t.day_id||'01','yyyymmdd'))-to_date(t.day_id||'01','yyyy mmdd')+1),t.month_plan/(last_day(to_date(t.day_id||'01','yyyymmdd'))-to_date(t. day_id||'01','yyyymmdd')+1),0)
else
nvl2((to_date(p_end_time,'yyyymmdd')-to_date(t.day_id||'01','yyyymmdd')+1),t.mo nth_plan/(to_date(p_end_time,'yyyymmdd')-to_date(t.day_id||'01','yyyymmdd')+1), 0)
end
) fact_value
from ods.excel_milkplan_mst t,
ods.i_time_day a
where t.day_id = a.month_id
and t.group_name <> '原奶'
and a.day_id between t.day_id||'01' and a.day_id
and a.day_id between substr(p_begin_time,1,6)||'01' and p_end_time
union all
--日计划
select
t.day_id,
https://www.360docs.net/doc/9114053821.html,n_id,
https://www.360docs.net/doc/9114053821.html,n_id whse_id,
'-1' whse_code,
-1 item_id,
'-1' item_no,
decode(t.grade_id,'A++','A+2','A++','A+2',t.grade_id) grade_id,
G_MEASURE54_ID fact_value_id, t.day_plan
from ods.excel_milkplan_mst t
where t.group_name <> '原奶'
and length(t.day_id) = 8
and t.day_id between p_begin_time and p_end_time
;
pkg_fbi_etl_ctl.write_log('一般',
'【DW】_原奶收购情况统计表(原奶计划)_[' ||
p_begin_time || '~' ||
p_end_time || ']' || sql%rowcount,
3,
sysdate - v_use_time);
commit;
exception
when others then
rollback;
pkg_fbi_etl_ctl.write_log('异常',
'【DW】_原奶收购情况统计表(原奶计划)_' ||
sqlcode || sqlerrm,
2);
commit;
if p_etl_auto = 'Y' then
raise;
end if;
end;
第4章创建Cube模型
由于每个Cube当中都有共同的计算成员和计算，因此我们在建立Cube的时候，可以通
过复制粘贴的方式。
4.1 打开Analysis Manager
4.2 新建“原奶收购分析”
1）选择任一已有的Cube，如产品库存分析，右键单击，如下图：
2）选择“复制”，单击，如下图：
3）在“多维数据集”上右键单击，并选择“粘贴”，如下图：
4）在选择“粘贴”后，出现如下图所示，重新命名“原奶收购分析”，点击“确定”
5）在“原奶收购分析”上右键单击，选择编辑，如下图所示
6）在“原奶收购分析”编辑界面，如下图所示
7）替换事实表，如下图所示
选择DW.DW_FACT_MILK事实表，如下图：
点确定后，事实表已经替换为DW.DW_FACT_MILK，如下图所示：