Self Configuring Warehouses

One of the reasons people don’t like the DAC is that it takes control over the ETL.
One of the reasons I like the DAC so much is the fact that it manages the ETL process with ease!

Not only does it run the Informatica tasks in the right order (providing you tell it the correct order!), but the DAC also runs other tasks, like SQL jobs.

Up to now the preferred method of running SQL in the warehouse or OLTP has been either directly (i.e. via TOAD or SQLPlus) or via stored procedures. But with the DAC you can run SQL directly that you keep in txt files in C:\SiebelAnalytics\DAC\CustomSQLs.

The usual application for these files is for simple update tasks, in the Oracle supplied example script it performs an insert.
insert into test_trgt (id) values (999)

One of the script files is used by the DAC on most tasks - customsql.xml. An example of the use of this file is the TRUNCATE statement that is run for a task. (On the task Edit page you have the choice of ‘Truncate Every Time’ and ‘Truncate on Full Load’)
The script looks like this


TRUNCATE TABLE %1


Where %1 is substituted for the table name(s) being truncated on that task.

Another useful script in customsql.xml is the Analyse statement


DBMS_STATS.GATHER_TABLE_STATS(ownname => '@TABLEOWNER', tabname => '%1', estimate_percent => 30, method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => true )


This one is more detailed but should not be ignored in any Warehouse project. As an analyse runs every table (if you have set the correct parameters) then this job needs to run as quickly as possible. Look at some of the values e.g. estimate_percent => 30 part. You may want to adjust this!!


A custom warehouse

Now here’s the really useful bit.

So you have a new warehouse and you’ve been creating new tables (prefixed with WC_ of course), custom fields (prefixed X_ of course!) and new indexes. This work was done is a development database and needs to be populated into Test, Training, Production, Support, etc environments. You also have version 1, 1.1, 1.2, etc.

Take the DDL that manipulates the Database structure and place this into a sql script file in C:\SiebelAnalytics\DAC\CustomSQLs.
Create a task that runs the script.
Create an Execution Plan that has the above task as a precedent. I normally call this Execution Plan ‘Initial Warehouse Set-up’
You can now keep all of your environments up to date easily by distributing the latest version of the SQL script and running the execution plan.
Tip. Make sure that your SQL is very robust, i.e. checking for existing tables before trying to create them again.


DAC Attacked


There are rumours that the DAC is going to be replaced by Oracle with something else. Whatever they do I hope it has all the useful features of the current DAC, plus more.

Next topic – Should Oracle keep developing OWB or just try to buy Informatica??