A Quick Way to Inspect Dimension Loading Behavior

Oracle Warehouse Builder has a powerful operator for Dimension in mapping that supports ROLAP/MOLAP/HOLAP modeling, Slowly Changing Dimension, Orphan management, etc. Although it is easy to use, we developers sometimes really need to know what the exactly behavior of the dimension operator is for our specific source data. Below I'll introduce a quick way to examine how the dimension operator will behave.

Prepare the scenario

We run the script as shown in Appendix A to set up objects used in our scenario. The script will also deploy the objects. Go to <ORACLE_HOME>/owb/bin/unix, then

./OMBPlus.sh /tmp/create.tcl

Start OWB Design Center, now we have several objects in DEMO_PROJECT that have already been deployed to the target schema.

Snap0

DIM_1 is a simple SCD type-2 dimension with one level L1 that has attributes below.

Attribute Data Type Key/SCD Role
ID NUMBER Surrogate key
NAME VARCHAR2 Business key
DESCRIPTION VARCHAR2 Trigger history
EFFECTIVE_DATE DATE Effective date
EXPIRATION_DATE DATE Expiration date

DEMO_MAP is a mapping we created for inspecting the dimension loading behavior. It has two operators, one is dimension operator DIM_1 that is bound to dimension DIM_1, and the other is a mapping input operator INPUT_1 that has two attributes connected to two corresponding attributes of DIM_1.

Snap1

Start the mapping

Before we execute the mapping, we have to make sure the preference "Prompt for Execution Parameters" is checked. Open the Preferences window by using the menu Tools->Preferences, select OWB->Deployment node on the preferences tree, then check the preference as below.

Snap10

Now we can start the mapping. In OWB Design Center, select DEMO_MAP in Projects tree, then click "Start..." button in toolbar, a window will then pop up asking for input parameters.

Snap2

We can set many parameters for the mapping here, but now we only focus on NAME and DESCRIPTION, they are the attributes in INPUT_1 operator.

Input 'desc1' (include single quote) for DESCRIPTION and 'name1' for NAME, leave other parameters as is, press "OK" button. Wait a few seconds for the mapping to finish running.

Snap11

Now we check the data in table DIM_1_TAB that is the implementation table for DIM_1. Right click DIM_1_TAB in Projects tree, then select "Data...", we see a new record.

Snap7

This means the mapping successfully loaded a record to DIM_1 and automatically set it's EFFECTIVE_DATE to the current date and time. This is the loading behavior of this dimensional mapping if the EFFECTIVE_DATE attribute in SCD type-2 dimension operator is unconnected.

We can do some more "What if"s. What if the description for 'name1' is changed? We can start the mapping again, input 'name1' for NAME and 'desc1_modified' for DESCRIPTION, and then we see the results as below.

Snap8

This means the mapping inserted a new record into dimension DIM_1 and automatically set EXPIRATION_DATE of the old record to the current date and time, indicating this record has been closed.

"Prompt for Execution Parameters" is not a new preference setting in OWB 11gr2, however it's very useful. It provides a means for you to input sample data to help debug your mapping.

As you can see, building this simple mapping utilizing the Mapping Input Parameter Operator will allow you to walk through the mapping and see how the dimension operator actually loads data. You can try it with SCD type-3, orphan management, etc.

Appendix A

create.tcl



OMBCONN rep_owner/rep_owner@localhost:1521:orcl
OMBCREATE PROJECT 'DEMO_PROJECT'
OMBCC 'DEMO_PROJECT'
OMBCONNECT CONTROL_CENTER
OMBCREATE LOCATION 'DEMO_LOCATION' \
SET PROPERTIES (TYPE,VERSION) VALUES ('Oracle Database','11.2')
OMBALTER LOCATION 'DEMO_LOCATION' \
SET PROPERTIES (CONNECT_AS_USER,PASSWORD,HOST,PORT,SERVICE_NAME) \
VALUES ('rep_user','rep_user','localhost',1521,'orcl')
OMBCOMMIT
OMBREGISTER LOCATION 'DEMO_LOCATION'
OMBCOMMIT
OMBCREATE ORACLE_MODULE 'DEMO_MODULE' SET REF LOCATION 'DEMO_LOCATION'
OMBCC 'DEMO_MODULE'
OMBCREATE SEQUENCE 'SEQ_1'
OMBCREATE DIMENSION 'DIM_1' SET PROPERTIES (SCD_TYPE) VALUES (2) \
SET REF SEQUENCE 'SEQ_1' \
ADD DIMENSION_ATTRIBUTE 'ID' SET AS SURROGATE_KEY \
ADD DIMENSION_ATTRIBUTE 'NAME' SET AS BUSINESS_KEY \
SET PROPERTIES (DATATYPE,LENGTH) VALUES ('VARCHAR2',30) \
ADD DIMENSION_ATTRIBUTE 'DESCRIPTION' \
SET PROPERTIES (DATATYPE,LENGTH) VALUES ('VARCHAR2',100) \
ADD DIMENSION_ATTRIBUTE 'EFFECTIVE_DATE' \
SET PROPERTIES (DATATYPE) VALUES ('DATE') \
ADD DIMENSION_ATTRIBUTE 'EXPIRATION_DATE' \
SET PROPERTIES (DATATYPE) VALUES ('DATE') \
ADD LEVEL 'L1' \
ADD LEVEL_ATTRIBUTE 'ID' OF LEVEL 'L1' \
SET REF DIMENSION_ATTRIBUTE 'ID' \
ADD LEVEL_ATTRIBUTE 'NAME' OF LEVEL 'L1' \
SET REF DIMENSION_ATTRIBUTE 'NAME' \
ADD LEVEL_ATTRIBUTE 'DESCRIPTION' OF LEVEL 'L1' \
SET PROPERTIES (TYPE_TWO_SCD_TRIGGER) VALUES ('true') \
SET REF DIMENSION_ATTRIBUTE 'DESCRIPTION' \
ADD LEVEL_ATTRIBUTE 'EFFECTIVE_DATE' OF LEVEL 'L1' \
SET PROPERTIES (TYPE_TWO_SCD_EFFECTIVE_DATE) VALUES ('true') \
SET REF DIMENSION_ATTRIBUTE 'EFFECTIVE_DATE' \
ADD LEVEL_ATTRIBUTE 'EXPIRATION_DATE' OF LEVEL 'L1' \
SET PROPERTIES (TYPE_TWO_SCD_EXPIRATION_DATE) VALUES ('true') \
SET REF DIMENSION_ATTRIBUTE 'EXPIRATION_DATE' \
IMPLEMENTED BY SYSTEM STAR
OMBCREATE MAPPING 'DEMO_MAP' \
ADD DIMENSION OPERATOR 'DIM_1' BOUND TO DIMENSION 'DIM_1' \
ADD INPUT_PARAMETER OPERATOR 'INPUT_1' \
ADD ATTRIBUTE 'NAME' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUT_1' \
SET PROPERTIES (DATATYPE) VALUES ('VARCHAR') \
ADD ATTRIBUTE 'DESCRIPTION' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUT_1' \
SET PROPERTIES (DATATYPE) VALUES ('VARCHAR') \
ADD CONNECTION \
FROM ATTRIBUTE 'NAME' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUT_1' \
TO ATTRIBUTE 'NAME' OF GROUP 'L1' OF OPERATOR 'DIM_1' \
ADD CONNECTION \
FROM ATTRIBUTE 'DESCRIPTION' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUT_1' \
TO ATTRIBUTE 'DESCRIPTION' OF GROUP 'L1' OF OPERATOR 'DIM_1'
OMBCOMMIT
OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DAP_1' \
ADD ACTION 'A1' SET PROPERTIES (OPERATION) VALUES ('CREATE') \
SET REF SEQUENCE 'SEQ_1' \
ADD ACTION 'A2' SET PROPERTIES (OPERATION) VALUES ('CREATE') \
SET REF TABLE 'DIM_1_TAB' \
ADD ACTION 'A3' SET PROPERTIES (OPERATION) VALUES ('CREATE') \
SET REF DIMENSION 'DIM_1'
OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DAP_1'
OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DAP_2' \
ADD ACTION 'A1' SET PROPERTIES (OPERATION) VALUES ('CREATE') \
SET REF MAPPING 'DEMO_MAP'
OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DAP_2'
OMBDISC


Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today