BIEE Client报表开发

发表于 | 分类于
字数统计: 5,703 字 | 阅读时长 ≈ 29 分钟

BIEE Client 报表开发

以开发”库存账龄报表”为例

SRC 层元数据

先打开 erp 的报表 package，找到所有需要的报表
1
2
3
4
5
mtl_system_items_b           msi,
mtl_safety_stocks            ms,
mtl_onhand_quantities_detail moq,
mtl_material_transactions    mmt,
mtl_transaction_lot_numbers  mtln
在 SRC 层复制相同的一份表结构，命名为 S_XXXXX。这里是在 BI_SRC 用户下创建如下表
1
2
3
4
5
BI_SRC.s_mtl_system_items_b           msi,
BI_SRC.s_mtl_safety_stocks            ms,
BI_SRC.s_mtl_onhand_quantities_detail moq,
BI_SRC.s_mtl_material_transactions    mmt,
BI_SRC.mtl_transaction_lot_numbers    mtln
创建方式

以 mtl_safety_stocks 为例，找到基表 inv.mtl_safety_stocks，右键-> 编辑-> 列-> 查看 SQL

image.png

复制 create table 语句image.png

对应的 SQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- 修改后的语句
-- 修改表名
create table BI_SRC.S_MTL_SAFETY_STOCKS
(
  inventory_item_id      NUMBER not null,
  organization_id        NUMBER not null,
  effectivity_date       DATE not null,
  last_update_date       DATE not null,
  last_updated_by        NUMBER not null,
  creation_date          DATE not null,
  created_by             NUMBER not null,
  last_update_login      NUMBER,
  safety_stock_code      NUMBER,
  safety_stock_quantity  NUMBER not null,
  safety_stock_percent   NUMBER,
  service_level          NUMBER,
  attribute_category     VARCHAR2(30),
  attribute1             VARCHAR2(150),
  attribute2             VARCHAR2(150),
  attribute3             VARCHAR2(150),
  attribute4             VARCHAR2(150),
  attribute5             VARCHAR2(150),
  attribute6             VARCHAR2(150),
  attribute7             VARCHAR2(150),
  attribute8             VARCHAR2(150),
  attribute9             VARCHAR2(150),
  attribute10            VARCHAR2(150),
  attribute11            VARCHAR2(150),
  attribute12            VARCHAR2(150),
  attribute13            VARCHAR2(150),
  attribute14            VARCHAR2(150),
  attribute15            VARCHAR2(150),
  request_id             NUMBER,
  program_application_id NUMBER,
  program_id             NUMBER,
  program_update_date    DATE,
  forecast_designator    VARCHAR2(10),
  project_id             NUMBER,
  task_id                NUMBER,
  --新增修改日期字段
  UPDATE_DT              DATE
)
tablespace BI_SRC_DAT   --修改表空间
  pctfree 10
  initrans 10
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- 修改索引名称
create unique index BI_SRC.S_MTL_SAFETY_STOCKS_U1 on BI_SRC.S_MTL_SAFETY_STOCKS (INVENTORY_ITEM_ID, ORGANIZATION_ID, EFFECTIVITY_DATE, PROJECT_ID, TASK_ID)
  tablespace BI_SRC_DIX  --修改索引空间
  pctfree 10
  initrans 11
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  )
  NOLOGGING;

打开 kettle，新建一个转换，配置好 EBS 数据库和 SRC 数据库信息

image.png

在”核心对象-> 输入”选好“表输入”，在”核心对象-> 输出”选好“表输出”。在表输入按住 shift 拖动到表输出，会有中间的蓝色线，把它们连起来

image.png

双击“表输出”配置表输出信息【注：这里勾选裁剪表，会导致全量同步】

image.png

右键原有的 ebs 基表-> 描述，如 INV.MTL_SAFETY_STOCKS，选择“逗号分隔复制”

image.png

整理以后如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT inventory_item_id,
       organization_id,
       effectivity_date,
       last_update_date,
       last_updated_by,
       creation_date,
       created_by,
       last_update_login,
       safety_stock_code,
       safety_stock_quantity,
       safety_stock_percent,
       service_level,
       attribute_category,
       attribute1,
       attribute2,
       attribute3,
       attribute4,
       attribute5,
       attribute6,
       attribute7,
       attribute8,
       attribute9,
       attribute10,
       attribute11,
       attribute12,
       attribute13,
       attribute14,
       attribute15,
       request_id,
       program_application_id,
       program_id,
       program_update_date,
       forecast_designator,
       project_id,
       task_id
  FROM INV.MTL_SAFETY_STOCKS

然后双击“表输入”配置表输入信息，把整理好的代码粘贴进去

image.png

保存该转换，命名为 S_MTL_SAFETY_STOCKS

运行该转换，如果报错查看”日志”，这里提示表或视图不存在，把表输出配置改一下即可

image.png

image.png

确认结果

image.png

同一个报表的其他基表按照此方法先同步数据到 BI_SRC 层即可

DIM 层维度表

这一层一般存放分析使用的维度表

新建库存组织、货位、子库存、物料的维度。这四张表要先同步基表到 BI_SRC 层，这里不再赘述

1
2
3
4
5
6
7
inv.mtl_secondary_inventories
inv.mtl_item_locations
Inv.mtl_system_items_b  
HR.HR_ALL_ORGANIZATION_UNITS  

--由于这个报表是库存账龄表，还需要一张账龄区间的维度表
BI_DIM.DIM_AGE

根据实际业务需求划分账龄区间

image.png

账龄维度表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Create table
create table BI_DIM.DIM_AGE
(
  age_wid  NUMBER(10),
  age_name VARCHAR2(100),
  age_low  NUMBER,
  age_high NUMBER
)
tablespace BI_DW_DAT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
nologging;
-- Add comments to the table 
comment on table BI_DIM.DIM_AGE
  is '账龄段;

创建维度存储过程，这里以货位为例

编写所需的字段 SQL

1
2
3
4
5
6
7
8
9
10
11
SELECT t.inventory_location_id,
           t.segment1 || '.' || t.segment2 || '.' || t.segment3,
           t.description,
           t.segment1,
           t.segment2,
           t.segment3,
           t.organization_id,
           t.inventory_item_id,
           t.subinventory_code
      FROM bi_src.s_mtl_item_locations t

根据 SQL，在 BI_DIM 层创建维度表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Create table
create table DIM_ITEM_LOCATION
(
  location_wid          NUMBER(10),
  inventory_location_id NUMBER,
  locator_name          VARCHAR2(255),
  locator_desc          VARCHAR2(500),
  segment1              VARCHAR2(255),
  segment2              VARCHAR2(255),
  segment3              VARCHAR2(255),
  organization_id       NUMBER,
  inventory_item_id     NUMBER,
  subinventory_code     VARCHAR2(255),
  effective_from_dt     DATE,
  effective_to_dt       DATE,
  delete_flg            CHAR(1),
  current_flg           CHAR(1),
  w_insert_dt           DATE,
  w_update_dt           DATE,
  datasource_num_id     NUMBER,
  etl_proc_id           VARCHAR2(50),
  etl_proc_name         VARCHAR2(50),
  integration_id        VARCHAR2(500),
  x_custom              VARCHAR2(50)
)
tablespace BI_DW_DAT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
nologging;
-- Add comments to the table 
comment on table DIM_ITEM_LOCATION
  is '物料货位维;

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
CREATE OR REPLACE PROCEDURE SP_DIM_ITEM_LOCATION(P_START_DAY IN NUMBER,
                                                 P_END_DAY   IN NUMBER,
                                                 P_PROC_ID   IN NUMBER) IS
  V_START_DAY  NUMBER(10) := P_START_DAY;
  V_END_DAY    NUMBER(10) := P_END_DAY;
  V_PROC_ID    NUMBER(8) := P_PROC_ID;
  V_MAX_ROWNUM NUMBER(10);
  V_START_TIME TIMESTAMP; --开始时间
  V_END_TIME   TIMESTAMP; --结束时间
  L_LOG        BI_DW.W_ETL_LOG%ROWTYPE; --日志表游标
BEGIN
  --记录程序开始时间
  SELECT CURRENT_TIMESTAMP INTO V_START_TIME FROM DUAL;

  --记录日志
  L_LOG.PROC_NAME       := 'SP_DIM_ITEM_LOCATION'; --过程名称
  L_LOG.TABLE_TYPE      := 'D'; --表类型
  L_LOG.TABLE_NAME      := 'DIM_ITEM_LOCATION'; --表名称
  L_LOG.MEASURE_ID      := NULL;
  L_LOG.ETL_INTERVAL    := V_START_DAY || '~' || V_END_DAY; --抽数区间
  L_LOG.PROC_ID         := V_PROC_ID; --过程D
  L_LOG.START_TIME      := V_START_TIME; --开始时间
  L_LOG.ETL_RECORD_LOAD := 0; --涉及记录数
  L_LOG.ETL_RECORD_INS  := 0; --实际插入的记录数
  L_LOG.ETL_RECORD_UPD  := 0; --更新的记录数，如果该存储过程有更新部分，则不等于零
  L_LOG.ETL_RECORD_DEL  := 0; --插入前清理目标时的删除记录数
  --加载记录数
  SELECT COUNT(1) INTO L_LOG.ETL_RECORD_LOAD FROM DIM_ITEM_LOCATION T;
  --取目前最大OW_WID
  SELECT NVL(MAX(T.Location_Wid), 1)
    INTO V_MAX_ROWNUM
    FROM DIM_ITEM_LOCATION T;

  --插入1记录数
  INSERT INTO DIM_ITEM_LOCATION
    (location_wid,
     inventory_location_id,
     locator_name,
     locator_desc,
     segment1,
     segment2,
     segment3,
     organization_id,
     inventory_item_id,
     subinventory_code,
     effective_from_dt,
     effective_to_dt,
     delete_flg,
     current_flg,
     w_insert_dt,
     w_update_dt,
     datasource_num_id,
     etl_proc_id,
     etl_proc_name,
     integration_id,
     x_custom)
    SELECT -1,
           -1,
           '未知,
           '未知,
           '未知,
           '未知,
           '未知,
           -1,
           -1,
           '未知,
           TO_DATE('19000101', 'YYYY-MM-DD'),
           TO_DATE('99990101', 'YYYY-MM-DD'),
           'N',
           'Y',
           SYSDATE,
           SYSDATE,
           9,
           V_PROC_ID,
           L_LOG.PROC_NAME,
           -1,
           -1
      FROM DUAL
     WHERE NOT EXISTS
     (SELECT 1 FROM DIM_ITEM_LOCATION T1 WHERE T1.Location_Wid = -1);
  COMMIT;
  --INTEGRATION_ID不一样的插入
  INSERT INTO DIM_ITEM_LOCATION
    (location_wid,
     inventory_location_id,
     locator_name,
     locator_desc,
     segment1,
     segment2,
     segment3,
     organization_id,
     inventory_item_id,
     subinventory_code,
     effective_from_dt,
     effective_to_dt,
     delete_flg,
     current_flg,
     w_insert_dt,
     w_update_dt,
     datasource_num_id,
     etl_proc_id,
     etl_proc_name,
     integration_id)
    SELECT ROWNUM + V_MAX_ROWNUM AS LOCATION_WID,
           t.inventory_location_id,
           t.segment1 || '.' || t.segment2 || '.' || t.segment3,
           t.description,
           t.segment1,
           t.segment2,
           t.segment3,
           t.organization_id,
           t.inventory_item_id,
           t.subinventory_code,
           TO_DATE('19000101', 'YYYY-MM-DD') AS EFFECTIVE_FROM_DT,
           TO_DATE('99990101', 'YYYY-MM-DD') AS EFFECTIVE_TO_DT,
           'N' AS DELETE_FLG,
           'T' AS CURRENT_FLG,
           SYSDATE AS W_INSERT_DT,
           SYSDATE AS W_UPDATE_DT,
           1 AS DATASOURCE_NUM_ID,
           V_PROC_ID AS ETL_PROC_ID,
           L_LOG.PROC_NAME ETL_PROC_NAME,
           t.inventory_location_id
      FROM bi_src.s_mtl_item_locations t
     WHERE NOT EXISTS (SELECT 1
              FROM DIM_ITEM_LOCATION T1
             WHERE T1.Location_Wid = t.inventory_location_id);

  --插入记录数
  L_LOG.ETL_RECORD_INS := SQL%ROWCOUNT + 1;
  COMMIT;
  --缓慢变化处理
  --更新旧记录
  UPDATE DIM_ITEM_LOCATION T
     SET T.EFFECTIVE_TO_DT = TO_DATE(V_END_DAY, 'YYYYMMDD'),
         T.CURRENT_FLG     = 'N'
   WHERE T.CURRENT_FLG = 'Y'
     AND EXISTS (SELECT 'X'
            FROM DIM_ITEM_LOCATION T1
           WHERE T1.Location_Wid = T.Location_Wid
             AND T1.CURRENT_FLG = 'T');
  COMMIT;
  --更新新记录
  UPDATE DIM_ITEM_LOCATION T
     SET T.EFFECTIVE_FROM_DT = TO_DATE(V_END_DAY, 'YYYYMMDD')
   WHERE T.CURRENT_FLG = 'T'
     AND EXISTS (SELECT 'X'
            FROM DIM_ITEM_LOCATION T1
           WHERE T1.Location_Wid = T.Location_Wid
             AND T1.CURRENT_FLG = 'N');
  COMMIT;
  --更新新记录
  UPDATE DIM_ITEM_LOCATION T
     SET T.CURRENT_FLG = 'Y'
   WHERE T.CURRENT_FLG = 'T';
  COMMIT;
  --程序结束时间
  SELECT CURRENT_TIMESTAMP INTO V_END_TIME FROM DUAL;
  --执行时间
  SELECT TRUNC(TO_NUMBER(TO_CHAR(V_END_TIME, 'YYYYMMDDHH24MISSXFF')), 2) -
         TRUNC(TO_NUMBER(TO_CHAR(V_START_TIME, 'YYYYMMDDHH24MISSXFF')), 2)
    INTO L_LOG.ETL_DURATION
    FROM DUAL;
  L_LOG.END_TIME   := V_END_TIME;
  L_LOG.ETL_STATUS := 'SUCCESS';
  --调用执行日志
  BI_DW.SP_W_ETL_LOG(L_LOG);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    L_LOG.ETL_STATUS := 'FAILURE';
    L_LOG.ERR_MSG    := SUBSTR(SQLERRM, 1, 2000);
    BI_DW.SP_W_ETL_LOG(L_LOG);
END SP_DIM_ITEM_LOCATION;

关于缓慢变化处理：

简单举个例子：张三 2020 年 5 月之前，在运营部做销售。到了 2020 年 6 月 1 日，张三到项目部做销售。那么体现在数据的变化上，就应该是张三，有两个成本中心。

1
2
3
姓名  启用日期从      启用日期至   成本中心  成本中心编码  角色id
张三  1973        - 2020-04-30  运营部    1013        1198
张三  2020-05-01  - Now         项目部    1015        1198

也就是说，在数据库里面应该有两条记录，并且项目部的这条记录是当前正在使用的记录。在核算张三业绩的时候，应该以 2020-05-01 为分界点，分属于不同的两个部门。从数据库角度来说，缓慢变化也可以理解为记录每一次的变动。

在 DIM 层启用缓慢变化，关键字段是 integration_id，这个字段必须是整个数据库中唯一的。在这个例子中，查询运营部的 integration_id 应该是 11981013，查询项目部的 integration_id 应该是 11981015。在这里 integration_id 是角色 id 和成本中心编码的组合，因为只有这两个字段的组合才能够保证记录的唯一性。

在启用了缓慢变化以后，下列的 Location_Wid 都要改成 integration_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--缓慢变化处理
  --更新旧记录的失效日期
  UPDATE DIM_ITEM_LOCATION T
     SET T.EFFECTIVE_TO_DT = TO_DATE(V_END_DAY, 'YYYYMMDD'),
         T.CURRENT_FLG     = 'N'
   WHERE T.CURRENT_FLG = 'Y'
     AND EXISTS (SELECT 'X'
            FROM DIM_ITEM_LOCATION T1
           WHERE T1.Location_Wid = T.Location_Wid  --integration_id
             AND T1.CURRENT_FLG = 'T');

  --更新新记录的启用日期
  UPDATE DIM_ITEM_LOCATION T
     SET T.EFFECTIVE_FROM_DT = TO_DATE(V_END_DAY, 'YYYYMMDD')
   WHERE T.CURRENT_FLG = 'T'
     AND EXISTS (SELECT 'X'
            FROM DIM_ITEM_LOCATION T1
           WHERE T1.Location_Wid = T.Location_Wid
             AND T1.CURRENT_FLG = 'N');

关于维度字段：一般是指用于分析的字段，或者说是 erp 过滤数据的参数

设置 kettle 运行存储过程

设置变量，这里的变量个数和类型要与存储过程一致

image.png

设置存储过程，这里的参数名称，要同“获取变量”

image.png

点击运行，到数据库查看结果即可。剩下的货位、物料维度也是如此，但是物料维度建议添加标准成本和实际成本。

DW 层事实表

写出报表的 SQL 脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT hou.organization_name,
       hou.organization_short_code,
       msi.segment1 item_number,
       msi.DESCRIPTION item_desc,
       msi.PRIMARY_UOM_CODE,
       moq.SUBINVENTORY_CODE,
       dil.locator_name,
       moq.LOT_NUMBER,
       NVL(ms.SAFETY_STOCK_QUANTITY, 0) safety_stock,
       nvl(moq.TRANSACTION_QUANTITY, 0) onhand_qty,
       hou.organization_wid,
       di.inventory_wid,
       dil.location_wid,
       dim.item_wid
  FROM BI_SRC.S_mtl_system_items_b           msi,
       BI_SRC.S_mtl_safety_stocks            ms,
       BI_SRC.S_mtl_onhand_quantities_detail moq,
       BI_DIM.DIM_ORGANIZATION_UNITS         hou,
       BI_DIM.DIM_INVENTORIES                di,
       BI_DIM.DIM_ITEM_LOCATION              dil,
       BI_DIM.DIM_ITEM                       dim
 WHERE 1 = 1
   AND ms.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
   AND ms.ORGANIZATION_ID(+) = msi.ORGANIZATION_ID
   AND moq.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
   AND moq.ORGANIZATION_ID(+) = msi.ORGANIZATION_ID
   AND hou.organization_id = msi.organization_id
   AND di.secondary_inventory_name = nvl(moq.SUBINVENTORY_CODE,-1)
   AND di.organization_id = nvl(moq.organization_id,-1)
   AND dil.inventory_location_id = nvl(moq.locator_id,-1)
   AND dim.item_id = msi.INVENTORY_ITEM_ID
   AND dim.organization_id = msi.organization_id
   AND moq.SUBINVENTORY_CODE NOT LIKE '%结算'

根据所需字段，创建 dw 事实表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- Create table
create table DW_INV_AGACCOUNT
(
  organization_name       VARCHAR2(100),
  organization_short_code VARCHAR2(50),
  item_number             VARCHAR2(50),
  item_desc               VARCHAR2(255),
  primary_uom_code        VARCHAR2(50),
  subinventory_code       VARCHAR2(50),
  locator_name            VARCHAR2(50),
  lot_number              VARCHAR2(50),
  safety_stock            NUMBER,
  onhand_qty              NUMBER,
  transaction_date        DATE,
  organization_wid        NUMBER,
  inventory_wid           NUMBER,
  location_wid            NUMBER,
  item_wid                NUMBER,
  w_insert_dt             DATE,
  w_update_dt             DATE,
  datasource_num_id       NUMBER,
  etl_proc_id             VARCHAR2(50),
  etl_proc_name           VARCHAR2(50),
  line_id                 VARCHAR2(100)
)
tablespace BI_DW_DAT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
nologging;
-- Add comments to the table 
comment on table DW_INV_AGACCOUNT
  is '库存物料明细;
-- Add comments to the columns 
comment on column DW_INV_AGACCOUNT.organization_name
  is '库存组织名称;
comment on column DW_INV_AGACCOUNT.organization_short_code
  is '库存组织代码;
comment on column DW_INV_AGACCOUNT.item_number
  is '物料编码;
comment on column DW_INV_AGACCOUNT.item_desc
  is '物料描述;
comment on column DW_INV_AGACCOUNT.primary_uom_code
  is '单位;
comment on column DW_INV_AGACCOUNT.subinventory_code
  is '子库存;
comment on column DW_INV_AGACCOUNT.locator_name
  is '货位名称;
comment on column DW_INV_AGACCOUNT.lot_number
  is '批次号;
comment on column DW_INV_AGACCOUNT.safety_stock
  is '安全库存;
comment on column DW_INV_AGACCOUNT.onhand_qty
  is '现有量;
comment on column DW_INV_AGACCOUNT.transaction_date
  is '事务处理日期;
comment on column DW_INV_AGACCOUNT.w_insert_dt
  is '插入时间;
comment on column DW_INV_AGACCOUNT.w_update_dt
  is '更新时间;
comment on column DW_INV_AGACCOUNT.datasource_num_id
  is '数据来源;
comment on column DW_INV_AGACCOUNT.etl_proc_name
  is 'ETL程序;

创建 dw 层存储过程，传输库存明细数据

package 定义：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE PACKAGE PKG_DW_INV_AGACCOUNT IS

  --获取事务处理日期
  FUNCTION get_transaction_date(p_lot_control_code IN NUMBER,
                                p_organization_id  IN NUMBER,
                                p_item_id          IN NUMBER,
                                p_lot_number       IN VARCHAR2) RETURN DATE;

  -- Author  : LYH
  -- Created : 2020-5-27 上午1:21:01
  -- Purpose : 库存明细
  PROCEDURE SP_DW_INV_AGACCOUNT(P_START_DAY IN NUMBER,
                                P_END_DAY   IN NUMBER,
                                P_PROC_ID   IN NUMBER);

END PKG_DW_INV_AGACCOUNT;

package 内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
CREATE OR REPLACE PACKAGE BODY PKG_DW_INV_AGACCOUNT IS
  --获取事务处理日期
  FUNCTION get_transaction_date(p_lot_control_code IN NUMBER,
                                p_organization_id  IN NUMBER,
                                p_item_id          IN NUMBER,
                                p_lot_number       IN VARCHAR2) RETURN DATE IS
    l_transaction_date DATE;
  BEGIN
    --不启用批次控制
    IF p_lot_control_code = 1 THEN
      SELECT t.TRANSACTION_DATE
        INTO l_transaction_date
        FROM (SELECT mmt.INVENTORY_ITEM_ID,
                     mmt.ORGANIZATION_ID,
                     mmt.TRANSACTION_DATE
                FROM BI_SRC.S_mtl_material_transactions mmt
               WHERE 1 = 1
                 AND p_item_id = mmt.INVENTORY_ITEM_ID
                 AND p_organization_id = mmt.ORGANIZATION_ID
               ORDER BY mmt.TRANSACTION_DATE ASC) t -- 批次控制来判断msi.Lot_Control_Code
       WHERE 1 = 1
         AND ROWNUM = 1;
    ELSE
      --启用批次控制
      SELECT t.TRANSACTION_DATE
        INTO l_transaction_date
        FROM (SELECT mmt.INVENTORY_ITEM_ID,
                     mmt.ORGANIZATION_ID,
                     mmt.TRANSACTION_DATE,
                     mtln.LOT_NUMBER
                FROM BI_SRC.S_mtl_material_transactions   mmt,
                     BI_SRC.S_mtl_transaction_lot_numbers mtln
               WHERE 1 = 1
                 AND mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
                 AND mtln.lot_number = p_lot_number
                 AND p_item_id = mmt.INVENTORY_ITEM_ID
                 AND p_organization_id = mmt.ORGANIZATION_ID
               ORDER BY mmt.TRANSACTION_DATE ASC) t -- 批次控制来判断msi.Lot_Control_Code
       WHERE 1 = 1
         AND ROWNUM = 1;
    END IF;
  
    RETURN l_transaction_date;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  
  END get_transaction_date;

  PROCEDURE SP_DW_INV_AGACCOUNT(P_START_DAY IN NUMBER,
                                P_END_DAY   IN NUMBER,
                                P_PROC_ID   IN NUMBER) IS
    V_START_DAY  NUMBER(10) := P_START_DAY;
    V_END_DAY    NUMBER(10) := P_END_DAY;
    V_PROC_ID    NUMBER(8) := P_PROC_ID;
    V_START_TIME TIMESTAMP; --开始时间
    V_END_TIME   TIMESTAMP; --结束时间
    L_LOG        BI_DW.W_ETL_LOG%ROWTYPE; --日志表游标
  BEGIN
    --记录程序开始时间
    SELECT CURRENT_TIMESTAMP INTO V_START_TIME FROM DUAL;
    --记录日志
    L_LOG.PROC_NAME       := 'SP_DW_INV_AGACCOUNT'; --过程名称
    L_LOG.TABLE_TYPE      := 'F'; --表类型
    L_LOG.TABLE_NAME      := 'DW_INV_AGACCOUNT'; --表名称
    L_LOG.MEASURE_ID      := NULL;
    L_LOG.ETL_INTERVAL    := V_START_DAY || '~' || V_END_DAY; --抽数区间
    L_LOG.PROC_ID         := V_PROC_ID; --过程D
    L_LOG.START_TIME      := V_START_TIME; --开始时间
    L_LOG.ETL_RECORD_LOAD := 0; --涉及记录数
    L_LOG.ETL_RECORD_INS  := 0; --实际插入的记录数
    L_LOG.ETL_RECORD_UPD  := 0; --更新的记录数，如果该存储过程有更新部分，则不等于零
    L_LOG.ETL_RECORD_DEL  := 0; --插入前清理目标时的删除记录数
    --加载记录数
    SELECT COUNT(1) INTO L_LOG.ETL_RECORD_LOAD FROM DW_INV_AGACCOUNT T;
    --删除记录
    /*DELETE DW_INV_AGACCOUNT F
     WHERE F.DAY_WID >= V_START_DAY
       AND F.DAY_WID <= V_END_DAY;
    COMMIT;*/
    EXECUTE IMMEDIATE 'TRUNCATE TABLE DW_INV_AGACCOUNT';
    --加载数据
    INSERT INTO DW_INV_AGACCOUNT
      (organization_name,
       organization_short_code,
       item_number,
       item_desc,
       primary_uom_code,
       subinventory_code,
       locator_name,
       lot_number,
       safety_stock,
       onhand_qty,
       transaction_date,
       organization_wid,
       inventory_wid,
       location_wid,
       item_wid,
       w_insert_dt,
       w_update_dt,
       datasource_num_id,
       etl_proc_id,
       etl_proc_name,
       line_id)
      SELECT organization_name,
             organization_short_code,
             item_number,
             item_desc,
             PRIMARY_UOM_CODE,
             SUBINVENTORY_CODE,
             locator_name,
             LOT_NUMBER,
             safety_stock,
             onhand_qty,
             transaction_date,
             organization_wid,
             inventory_wid,
             location_wid,
             item_wid,
             SYSDATE AS W_INSERT_DT,
             SYSDATE AS W_UPDATE_DT,
             1 AS DATASOURCE_NUM_ID,
             V_PROC_ID,
             L_LOG.PROC_NAME,
             inventory_item_id || organization_id || subinventory_code ||
             nvl(lot_number, -1)
        FROM (SELECT hou.organization_name,
                     hou.organization_short_code,
                     msi.segment1 item_number,
                     msi.DESCRIPTION item_desc,
                     msi.PRIMARY_UOM_CODE,
                     moq.SUBINVENTORY_CODE,
                     dil.locator_name,
                     moq.LOT_NUMBER,
                     NVL(ms.SAFETY_STOCK_QUANTITY, 0) safety_stock,
                     nvl(moq.TRANSACTION_QUANTITY, 0) onhand_qty,
                     hou.organization_wid,
                     di.inventory_wid,
                     dil.location_wid,
                     dim.item_wid,
                     msi.inventory_item_id,
                     msi.organization_id,
                     get_transaction_date(msi.lot_control_code,
                                          msi.organization_id,
                                          msi.inventory_item_id,
                                          moq.lot_number) transaction_date
                FROM BI_SRC.S_mtl_system_items_b           msi,
                     BI_SRC.S_mtl_safety_stocks            ms,
                     BI_SRC.S_mtl_onhand_quantities_detail moq,
                     BI_DIM.DIM_ORGANIZATION_UNITS         hou,
                     BI_DIM.DIM_INVENTORIES                di,
                     BI_DIM.DIM_ITEM_LOCATION              dil,
                     BI_DIM.DIM_ITEM                       dim
               WHERE 1 = 1
                 AND ms.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
                 AND ms.ORGANIZATION_ID(+) = msi.ORGANIZATION_ID
                 AND moq.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
                 AND moq.ORGANIZATION_ID(+) = msi.ORGANIZATION_ID
                 AND hou.organization_id = msi.organization_id
                 AND di.secondary_inventory_name =
                     nvl(moq.SUBINVENTORY_CODE, -1)
                 AND di.organization_id = nvl(moq.organization_id, -1)
                 AND dil.inventory_location_id = nvl(moq.locator_id, -1)
                 AND dim.item_id = msi.INVENTORY_ITEM_ID
                 AND dim.organization_id = msi.organization_id
                 AND moq.SUBINVENTORY_CODE NOT LIKE '%结算');
  
    L_LOG.ETL_RECORD_INS := L_LOG.ETL_RECORD_INS + SQL%ROWCOUNT;
    COMMIT;
    --程序结束时间
    SELECT CURRENT_TIMESTAMP INTO V_END_TIME FROM DUAL;
    --执行时间
    SELECT TRUNC(TO_NUMBER(TO_CHAR(V_END_TIME, 'YYYYMMDDHH24MISSXFF')), 2) -
           TRUNC(TO_NUMBER(TO_CHAR(V_START_TIME, 'YYYYMMDDHH24MISSXFF')), 2)
      INTO L_LOG.ETL_DURATION
      FROM DUAL;
    L_LOG.END_TIME   := V_END_TIME;
    L_LOG.ETL_STATUS := 'SUCCESS';
    --调用执行日志
    BI_DW.SP_W_ETL_LOG(L_LOG);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      L_LOG.ETL_STATUS := 'FAILURE';
      L_LOG.ERR_MSG    := SUBSTR(SQLERRM, 1, 2000);
      BI_DW.SP_W_ETL_LOG(L_LOG);
  END SP_DW_INV_AGACCOUNT;

END PKG_DW_INV_AGACCOUNT;

kettle 定义、运行

image.png

DM 层分析表

编写 SQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT t.organization_wid,
       t.inventory_wid,
       t.location_wid,
       t.item_wid,
       round(SUM(CASE
             WHEN SYSDATE - TRUNC(t.transaction_date) > DA.AGE_LOW AND
                  SYSDATE - TRUNC(t.transaction_date) <= DA.AGE_HIGH THEN
              SYSDATE - TRUNC(t.transaction_date)
             ELSE
              0
           END),2) 账龄
  FROM bi_dw.DW_INV_AGACCOUNT t, BI_DIM.DIM_AGE DA
 GROUP BY t.organization_wid, t.inventory_wid, t.location_wid, t.item_wid

创建 dm 层数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Create table
create table BI_DM.DM_INV_AGACCOUNT
(
  day_wid            NUMBER(10),
  organization_wid   NUMBER,
  inventory_wid      NUMBER,
  location_wid       NUMBER,
  item_wid           NUMBER,
  age_wid            NUMBER(15),
  age_account        NUMBER,
  w_insert_dt        DATE,
  w_update_dt        DATE,
  datasource_num_id  NUMBER,
  etl_proc_id        VARCHAR2(50),
  etl_proc_name      VARCHAR2(50)
)
tablespace BI_DM_DAT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
nologging;
-- Add comments to the table 
comment on table BI_DM.DM_INV_AGACCOUNT
  is '库存账龄;
-- Add comments to the columns 
comment on column BI_DM.DM_INV_AGACCOUNT.day_wid
  is '日期;
comment on column BI_DM.DM_INV_AGACCOUNT.organization_wid
  is '库存组织;
comment on column BI_DM.DM_INV_AGACCOUNT.inventory_wid
  is '子库存;
comment on column BI_DM.DM_INV_AGACCOUNT.location_wid
  is '货位;
comment on column BI_DM.DM_INV_AGACCOUNT.item_wid
  is '物料;
comment on column BI_DM.DM_INV_AGACCOUNT.age_wid
  is '账龄段;
comment on column BI_DM.DM_INV_AGACCOUNT.age_account
  is '账龄;
-- Create/Recreate indexes 
create index BI_DM.DM_INV_AGACCOUNT_N1 on BI_DM.DM_INV_AGACCOUNT (DAY_WID)
  tablespace BI_DM_DIX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;

创建 dm 层 package

package 定义：

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE PACKAGE PKG_DM_INV_AGACCOUNT IS

  -- Author  : KALOSORA
  -- Created : 2020-5-29 上午:42:34
  -- Purpose : 客户余额账龄
  PROCEDURE SP_DM_INV_AGACCOUNT(P_START_DAY IN NUMBER,
                                P_END_DAY   IN NUMBER,
                                P_PROC_ID   IN NUMBER);

END PKG_DM_INV_AGACCOUNT;

package 内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
CREATE OR REPLACE PACKAGE BODY PKG_DM_INV_AGACCOUNT IS

  -- Author  : KALOSORA
  -- Created : 2020-5-29 上午:42:34
  -- Purpose : 客户余额账龄
  PROCEDURE SP_DM_INV_AGACCOUNT(P_START_DAY IN NUMBER,
                                P_END_DAY   IN NUMBER,
                                P_PROC_ID   IN NUMBER) IS
    V_START_DAY  NUMBER(10) := P_START_DAY;
    V_END_DAY    NUMBER(10) := P_END_DAY;
    V_PROC_ID    NUMBER(8) := P_PROC_ID;
    V_START_TIME TIMESTAMP; --开始时间
    V_END_TIME   TIMESTAMP; --结束时间
    L_LOG        BI_DW.W_ETL_LOG%ROWTYPE; --日志表游标
  BEGIN
    --记录程序开始时间
    SELECT CURRENT_TIMESTAMP INTO V_START_TIME FROM DUAL;
    --记录日志
    L_LOG.PROC_NAME       := 'SP_DM_INV_AGACCOUNT'; --过程名称
    L_LOG.TABLE_TYPE      := 'F'; --表类型
    L_LOG.TABLE_NAME      := 'DM_INV_AGACCOUNT'; --表名称
    L_LOG.MEASURE_ID      := NULL;
    L_LOG.ETL_INTERVAL    := V_START_DAY || '~' || V_END_DAY; --抽数区间
    L_LOG.PROC_ID         := V_PROC_ID; --过程D
    L_LOG.START_TIME      := V_START_TIME; --开始时间
    L_LOG.ETL_RECORD_LOAD := 0; --涉及记录数
    L_LOG.ETL_RECORD_INS  := 0; --实际插入的记录数
    L_LOG.ETL_RECORD_UPD  := 0; --更新的记录数，如果该存储过程有更新部分，则不等于零
    L_LOG.ETL_RECORD_DEL  := 0; --插入前清理目标时的删除记录数
  
    FOR C IN (SELECT DAY_WID
                FROM BI_DIM.DIM_DAY
               WHERE DAY_WID >=
                     TO_CHAR(ADD_MONTHS(TO_DATE(V_START_DAY, 'YYYYMMDD'), -1),
                             'YYYYMMDD')
                 AND DAY_WID <= V_END_DAY
               ORDER BY DAY_WID) LOOP
      --删除记录
      DELETE DM_INV_AGACCOUNT F WHERE F.DAY_WID = C.DAY_WID;
      COMMIT;
      --加载数据
      --账龄
      INSERT INTO DM_INV_AGACCOUNT
        (day_wid,
         organization_wid,
         inventory_wid,
         location_wid,
         item_wid,
         age_wid,
         age_account,
         w_insert_dt,
         w_update_dt,
         datasource_num_id,
         etl_proc_id,
         etl_proc_name)
        SELECT C.DAY_WID,
               t.organization_wid,
               t.inventory_wid,
               t.location_wid,
               t.item_wid,
               DA.AGE_WID,
               round(SUM(CASE
                           WHEN SYSDATE - TRUNC(t.transaction_date) > DA.AGE_LOW AND
                                SYSDATE - TRUNC(t.transaction_date) <= DA.AGE_HIGH THEN
                            SYSDATE - TRUNC(t.transaction_date)
                           ELSE
                            0
                         END),
                     2),
               SYSDATE,
               SYSDATE,
               1,
               V_PROC_ID,
               L_LOG.PROC_NAME
          FROM bi_dw.DW_INV_AGACCOUNT t, BI_DIM.DIM_AGE DA
         WHERE trunc(t.transaction_date) <=
               TO_DATE(C.DAY_WID, 'YYYYMMDD')
           AND DA.AGE_WID <> 0
         GROUP BY t.organization_wid,
                  t.inventory_wid,
                  t.location_wid,
                  t.item_wid,
                  DA.AGE_WID
        --HAVING SUM(F.AMOUNT) <> 0
        ;
      L_LOG.ETL_RECORD_INS := L_LOG.ETL_RECORD_INS + SQL%ROWCOUNT;
      COMMIT;
    END LOOP;
    --程序结束时间
    SELECT CURRENT_TIMESTAMP INTO V_END_TIME FROM DUAL;
    --执行时间
    SELECT TRUNC(TO_NUMBER(TO_CHAR(V_END_TIME, 'YYYYMMDDHH24MISSXFF')), 2) -
           TRUNC(TO_NUMBER(TO_CHAR(V_START_TIME, 'YYYYMMDDHH24MISSXFF')), 2)
      INTO L_LOG.ETL_DURATION
      FROM DUAL;
    L_LOG.END_TIME   := V_END_TIME;
    L_LOG.ETL_STATUS := 'SUCCESS';
    --调用执行日志
    BI_DW.SP_W_ETL_LOG(L_LOG);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      L_LOG.ETL_STATUS := 'FAILURE';
      L_LOG.ERR_MSG    := SUBSTR(SQLERRM, 1, 2000);
      BI_DW.SP_W_ETL_LOG(L_LOG);
  END SP_DM_INV_AGACCOUNT;

END PKG_DM_INV_AGACCOUNT;

BIEE 视图层开发

登录到 biee 服务器，下载 rpd 文件用离线模式打开(加快运行速度)，找到文件 GJ_BI.rpd

1
/u01/biee/Oracle/Middleware/Config/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/customizations/

新建数据库，新建链接池

12c 数据库属性设置

image.png

链接池属性设置

image.png

切换到 BI_QUERY 用户，先为数据表创建同义词

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE SYNONYM DIM_INVENTORIESFOR FOR BI_DIM.DIM_INVENTORIES;

CREATE OR REPLACE SYNONYM DIM_ITEM  FOR BI_DIM.DIM_ITEM;

CREATE OR REPLACE SYNONYM DIM_ITEM_LOCATION FOR BI_DIM.DIM_ITEM_LOCATION;

CREATE OR REPLACE SYNONYM DIM_ORGANIZATION_UNITS FOR BI_DIM.DIM_ORGANIZATION_UNITS;

CREATE SYNONYM dw_inv_agaccount FOR bi_dw.dw_inv_agaccount;

连接池，右键，导入数据表

image.png

image.png

创建物理层事实表

对需要创建的表，右键-》新建对象-》别名

image.png

输入名称，保持一致

image.png

物理层创建完毕

image.png

同理，dw 表也要创建别名

image.png

选中维度和事实表，右键，物理图表-》仅所选对象。然后关联主键 id

image.png

image.png

image.png

修改事实表/维度表的表名称

image.png

修改字段名称，删除维度和事实表中不需要的字段

image.png

维度修改后

image.png

事实表修改后

image.png

5.3 创建逻辑维

image.png

修改维度的名称和字段名，并且删除不需要的字段

image.png

双击维度 detail 层，设置显示的主键

image.png

image.png

注意：时间维度要做如下操作

image.png

以上，其他维度也是如此

拖动数据到展示层

image.png

上传 rpd 文件

1
/u01/biee/Oracle/Middleware/Config/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/customizations

上传命令：

1
[oracle@OBIEE ~]$ cd /u01/biee/Oracle/Middleware/Config/user_projects/domains/bi/bidata/service_instances/ssi/metadata/datamodel/customizations[oracle@OBIEE customizations]$ /u01/biee/Oracle/Middleware/Config/user_projects/domains/bi/bitools/bin/datamodel.sh uploadrpd -u weblogic -p bieetest2019 -i liverpd.rpd -w Admin123 -S biee-test -SI ssi

BIEE 前端设置

新建分析 -》 右上角新建，分析

image.png

image.png

选择相应的主题后，把事实表中的列拖动到“所选列中”，然后选择对应的主题区域进行保存

image.png

新建仪表盘提示，并选择主题区域

image.png

选择列提示

image.png

image.png

新建仪表盘

image.png

把仪表盘提示和分析，分别拖入到仪表盘中

image.png

回到 编辑分析 页面，切换到 标准 选项卡，添加筛选器，使得仪表盘提示能够正常过滤数据

image.png

选择要过滤的列

注意：仪表盘中，提示的数据源要和筛选器中的数据源相同。

如果仪表盘提示使用的是 维表 那么筛选器也要使用 维表

如果仪表盘提示使用的是 事实表 那么筛选器也要使用 事实表

image.png

image.png

完成效果如下，注意关键字段的来源，有的来源于维表，用于过滤数据

image.png

提示也必须来源于维表

image.png

注意：如果报表无法充满整个屏幕，要编辑分析，选择内容分页即可

image.png

注意：设置字段相同也显示

image.png

image.png

image.png

注意：设置打印

image.png

image.png

设置 biee 的数据显示为最大行数-配置文件

1
/u01/biee/Oracle/Middleware/Config/user_projects/domains/bi/config/fmwconfig/biconfig/OBIPS/instanceconfig.xml

修改完后要重启 biee 应用

同时还要修改 Oracle EM 上的最大行数

image.png

BIEE 添加用户 -> 登陆到 Oracle WEBLOGIC CONSOLE

image.png

数据仓库设计

数据仓库设计

  • ODS 元数据 （BI_SRC 层）
  • DIM 维度层（BI_DIM 层，贯穿 DW 和 DM 层）
  • DW 数据仓库层（BI_DW 层），展示报表明细数据
  • DM 数据集市层（BI_DM 层）基于 BI_DW 层的数据，生成新的表，主要用于计算帐龄、余额的数据，根据不同的指标来展现聚合的数据

命名规范

书目

  • 数据仓库工具箱-——维度建模权威指南
  • 《Hadoop 构建数据仓库实践》

数据层——ETL 工具的 kettle

新建 kettle 转换

选择表输入和表输出

根据需要设置增量同步

新建 job，把多个 kettle 文件连在一起；job 设置定时同步

0%