A Quick Way to Inspect Dimension Loading Behavior
By Dawei Sun on Dec 10, 2009
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
Start OWB Design Center, now we have several objects in DEMO_PROJECT that have already been deployed to the target schema.
DIM_1 is a simple SCD type-2 dimension with one level L1 that has attributes below.
|Attribute||Data Type||Key/SCD Role|
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.
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.
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.
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.
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.
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.
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.