Informatica使用操作流程--聚合、表达式转换、查找、排序组件的使用 案例3

一、需求：

将oracle数据库的scott用户源表items   直抽至  EDW层   EDW_items。数据存储到oracle的edw01用户下

数据抽取至EDW层做聚合转换

• 求各供应商  min(price),avg(price),max(price),sum(price), 2*avg(price)

• 根据供应商id去供应商表查找供应商名称

• 查验数据结果后，再修改Informatica,按照平均价格和求和价格降序

数据说明：

-- SCOTT.ITEMS definition
 
CREATE TABLE "SCOTT"."ITEMS" 
   (	"ITEM_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"ITEM_NAME" VARCHAR2(72) NOT NULL ENABLE, 
	"ITEM_DESC" VARCHAR2(72), 
	"PRICE" NUMBER(10,2) NOT NULL ENABLE, 
	"WHOLESALE_COST" NUMBER(10,2) NOT NULL ENABLE, 
	"DISCONTINUED_FLAG" NUMBER(*,0), 
	"MANUFACTUSER_ID" NUMBER(*,0), 
	"DISTRIBUTOR_ID" NUMBER(*,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
 
COMMENT ON COLUMN SCOTT.ITEMS.ITEM_ID IS '商品编号';
COMMENT ON COLUMN SCOTT.ITEMS.ITEM_NAME IS '商品名称';
COMMENT ON COLUMN SCOTT.ITEMS.ITEM_DESC IS '商品描述';
COMMENT ON COLUMN SCOTT.ITEMS.PRICE IS '售价';
COMMENT ON COLUMN SCOTT.ITEMS.WHOLESALE_COST IS '批发价';
COMMENT ON COLUMN SCOTT.ITEMS.DISCONTINUED_FLAG IS '是否断货';
COMMENT ON COLUMN SCOTT.ITEMS.MANUFACTUSER_ID IS '供应商id';
COMMENT ON COLUMN SCOTT.ITEMS.DISTRIBUTOR_ID IS '经销商id';

 
SELECT ITEM_ID, ITEM_NAME, ITEM_DESC, PRICE, WHOLESALE_COST, DISCONTINUED_FLAG, MANUFACTUSER_ID, DISTRIBUTOR_ID
FROM SCOTT.ITEMS;
 
 
 
INSERT INTO SCOTT.ITEMS
(ITEM_ID, ITEM_NAME, ITEM_DESC, PRICE, WHOLESALE_COST, DISCONTINUED_FLAG, MANUFACTUSER_ID, DISTRIBUTOR_ID)
VALUES(1313, 'Regulator System', 'Air Regulator ', 250.00, 150.00, 0, 100, 2012);
 

--先连接R端创建文件夹BI_ETL02

--切换到D端打开BI_ETL02

--创建连接源：点击源图标，点击源--->从数据库导入-->【点击ODBC数据源（D）后边的‘···’--->用户DSN--->添加--->选择：DataDirect 6.1 Oracle Wire Protocol--->自定义Data Source Name:odbc01_oracle--->Host:127.0.0.1--->Port Number:1521--->SID:orcl--->确定 --->选择 odbc01_oracle --->确定。