Mapping generation for Data Vault demo: part 2 (setting up OWB)

Now that the source data set has been created and is available it is time to initially set up the Oracle Warehouse Builder environment. As mentioned earlier, I’ve chosen OWB for this demo because of the powerful scripting capabilities (TCL / OMB). For the demo environment to work with the supplied scripts you have to place them in the C\:TCL directory because some scripts call other scripts as some sort of include statement. To create your own demo environment you simply have to install Oracle Warehouse Builder and create the aforementioned directory. Also, you have to create a schema to insert the source example data and setup the location (physical connections) but for the rest the scripts will do the hard work. The Integration Layer is the persistent layer which will be available in every architecture (the history layer is optional) so all housekeeping and metadata elements are places in this layer.

Since I’m not much of a programmer there are without doubt very many improvements that can be made in these scripts. Many things have been copied and pasted when things could have been solved more elegantly and most likely more effective statements can be used here and there. But creating a flawless code is not the purpose of this exercise: demonstrating the mapping generation possibilities for Data Vault is!

For this purpose I have created a number of scripts. Don’t forget to rename them to .tcl!

  • settings.tcl (download: settings). This script contains all generic properties for the demo including connectivity information and prefixes.
  • 99_setup_demo.tcl (download: 99_setup_demo). This script removes and re-creates the LOCAL_DATAWAREHOUSE project in Oracle Warehouse Builder. The script call a few other scripts which are listed below.
  • housekeeping.tcl (download: housekeeping). This script is called by the setup demo script and sets up any defined housekeeping procedures. Currently the script is a placeholder but it does limit the housekeeping to the Integration Layer.
  • metadata.tcl (download: metadata). This script created some metadata mappings and tables such as a process ID. This also serves as an example.

From an Oracle Warehouse Builder point of view you need to create your ‘schema’s / locations first. This is done using the Oracle Warehouse Builder repository management tool. These locations have to be configured in the client tool as well. I have used the following location names:

  • 00_SOURCE_SYSTEM_LOCATION
  • 10_STAGING_AREA_LOCATION
  • 20_HISTORY_AREA_LOCATION
  • 30_INTEGRATION_AREA_LOCATION
  • 40_DATAMART_AREA_LOCATION

These locations will be automatically linked to the demo environment folders but you’re free to create other ones. Remember to link the locations to the created folders / directories in OWB if you want to run your mappings!

Only the 99_setup_demo.tcl script has to be run in order to setup the environment completely. After that it’s time for the real work.

 
Roelant Vos

Roelant Vos

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.