Ok, you all watched the launch of database 11g (or did you tune in to the OWB webcast on the same day?), so lets write down what is coming in handy new things. To clarify the title, I will be using mostly 11.1 software to show the features, but they should work exactly the same in 10.2.0.3 unless stated otherwise...
So the first one that has been in OWB for a while, but that was broken as well, is how do you create Views and MViews while still keeping lineage and impact in tact? Well the simple answer is that you use mappings. In essence what we are doing here is working on Federation of data rather than consolidation (e.g. ETL). So we will leave the data in place but make it look like it is in a single place for any query.
In order to really do federation and use OWB there are some other pieces of the puzzle as far as infrastructure go that should be in place. In its briefest form a federated system with its Oracle technology in place could look like this:
Now the cool bit here is that, while you can just do this with the OWB piece, that piece makes this really work as you need it to because you get:
- Full metadata transparency delivering impact and lineage analysis
- A graphical way of managing your data flows using the OWB GUI
- A graphical debugger for your views (which are mappings at the end of the day)
- Simplified maintenance and better documentation
- Version management of all objects
- A one button choice to either Federate or Consolidate without changing any logical design
The piece I want to show here, is how you can use mappings to actually create the views and how easy it is to change modes between Federation and Consolidation. Last we'll quickly look at the metadata story with a quick look at Lineage and change propagation.
A mapping for federation would look like this:
where the most notable thing is that the target in this case is a non-existing view. I simply added an empty view operator into the mapping and mapped the approriate columns from the joiner over to the view, which creates the attributes in the operator.
Next I do a Create and Bind on the view (right mouse click for the menu option) and I place it in a module. The result is as follows:
As you can see the entire view is resolved, the code is generated (the select essentially) including any location information (e.g. dblinks, schema references etc). This is where the maintenance becomes a lot simpler, connection informatino, dblinks etc are decoupled from the actual query and can be changed based on the system. Simply reuse a new configuration and regenerate...
Now if you want to change the strategy (for performance, or for other reasons), what you do is go back to the mapping and replace the view with a table operator. Lets say you created the table in OWB (whichever which way you want to really), you simply do a synchronize in the mapping to replace the view with a table and your system is now an ETL rather than a Federated system and after you deploy the mapping and the table you can run your jobs into the consolidated system.
Obviously if you would want to do this manually, you would create the table (as in OWB) and then move the select into a PL/SQL package and run that. Sounds simple enough, but if you have hundreds of these views in place the automation you can get from OWB will make this task so much faster that it cannot be beaten by hand coding.
Last but not least, changes can be made much easier and faster in OWB using the lineage and impact analysis functionality (not new in 10.2.0.3 nor 11g, but very relevant for this particular feature). If a source column has changed, you can propagate that directly to the view definition.
There is no need to synchronize anything as this is all been taken care off (this includes the view definition)!