Oracle Warehouse Builder (OWB) Weblog

A new video highlighting Oracle Warehouse Builder customers is now available.  In it, representatives from Absa Bank and ColorLine describe how Oracle Warehouse Builder has benefited their companies:

• Arthur Britz, Absa Bank: "Oracle Warehouse Builder allows us to consolidate data from a multitude of sources into a single repository..." He goes on to laud the auditability and traceability that OWB provides for the billions of records in Absa's data warehouse.
  
• Anders Langäs, ColorLine: "We're using Oracle Warehouse Builder in the whole flow from extracting information from data sources..."

Watch the video

It is summer again, and ODTUG is going to the beach! Just in case you are wondering if I've gone mad, nope (at least I don't think so). This year's ODTUG conference will be held in Daytona Beach.

Last year we launched Warehouse Builder 10gR2 (the now famous Paris release) at ODTUG, so this year there will be a little less hype around OWB, but I think it should still be good. If nothing else it is not the worst location ever to be for a conference and it is one of those conferences that are great fun.

As far as OWB goes, I'll be presenting 2 sessions, one of business rules and ETL which is a bit of the beaten track, the other one is a "preview" as to what OWB 11g will bring (and I think I'll bring 10.2.0.3 patch along as it has some really cool stuff - also in 11g, but 10.2.0.3 might be easier to get to). Just finished the PPT for this and we will also look at the roadmap for OWB. You all of course have read the public OWB roadmap doc, but this sessions will cover some questions and ansers and we might be able to show some of the software as it looks today in development. It will be a while till the release, but at least everyone has an idea about the execution on the roadmap.

While we are on the subject, I do get the occasional question as to whether ODI will replace OWB, and the answer is a definite no. That is another reason for showing the software in the lab. You will understand the direction for OWB much better...

Anyways, hope to see you at ODTUG and come and say hi at my sessions if you find them interesting!

Just noticed this script, which I think many people might not be aware of. Ok it is not something you would need every day, but in some cases you may find yourself in need of it...

In <OWB_HOME>/owb102/owb/misc you find 3 scripts:

• unregister_location.sql
• wb_env_util.plb
• wb_env_util.pks

Not something I use every day, since most  of my mappings are not overly complex, but something that may help in any real developers life are some of the following UI gimmicks:

• Customizing the operators in a mapping
• Horizontal scroll bars in operators
• Multi-Select in mapping
• Sorting in the operator itself
  

Well, this is it again, I did my one day ODTUG in Daytona Beach and now I'm stuck in the airport, so time to quickly write about the conference. As I wrote in an earlier post, we did 2 papers on OWB. One was on data rules and the other was on 11g (and 10.2.0.3) so I guess the cat is out of the bag now. Hence we will start discussing what is new in the releases in anticipation of the release.

Here are a couple of things that should make you all quite happy... the first one is the much asked about DML error logging. It is now in 10.2.0.3 which should be released July-August or so (don't quote me here!) as a patch on both 10.2.0.1 and 10.2.0.2. It will be a regular patch coming via metalink once released. Other cool things are some changes (or should we call them fixes) to the SCD dimension logic based on feedback from the forums and some users. We hope that will solve those issues. As we were working on it we added hierarchy versioning for the SCD dimensions.

Some other things we talked about at ODTUG in the 11g session was the new Siebel connector allowing us to use Siebel as a source or to for example use data profiling on the MDM solutions (Universal Customer Master), and on connectivity, one of the things to take a look at are the new gateways for mainframe solutions that are part of 11g (not OWB but DB of course). They allow a direct solution for Adabas, VSAM and other fun thing.

One of the things I intended to demo (and it worked just before) is the new performance tuning features where you can see the explain plan, statistics and get tuning advice directly in OWB. So rather than copying the SQL from OWB and run it in SQL Plus or SQL Developer you can now just run it from OWB. Gets you a quick first glance at the plan. That feature should be useful to detect big bottlenecks in the early stages of mapping development. To come back to the demo, that nicely failed of course (something always goes wrong in those darn live demonstrations).

While we are in mapping, some nifty thing we (finally) added is support for pseudo columns like rowid and rownum. You can now add these columns into mapping to ensure you can work with rowid's. Since we also worked on the aggregator and allow more cool things to be done in it we can now do some complex federation using views from OWB. I'll do a detailed one on this feature, as it is in the list of "10 things you did not know OWB did".

That is it for now, and as you can see, going to ODTUG next year will get you the best information earliest... the folks in the session did get to actually see all of this (and some even more cool stuff from the roadmap) live and as the first outside of Oracle. So next year, do go to ODTUG. It is a fun conference, it will have a beach (I hope) and you get lots of very useful information from all the folks at the conference.

PS. We will go into more detail on all of these, but for now the ODTUG folks have a leg up :-)

There are useful OMB APIs (see OMBDESCRIBE) to introspect the OWB model and query object definitions. They provide a useful insight when building accelerators. I've used them for building a few things including the configuration experts on the exchange (slice and dice object/properties/configurations).

So if you want to see the core properties of an object (for example TABLE) you can perform the following from within OMBPlus (or panel):

For all of those who missed the webcast this morning, you can find the recording and the slides I used on the BIWA website. Very good to see so much interest for OWB's future, which I hope you all agree looks very bright indeed... Lots of good things coming.

Anyways, for those who want the direct links:

• Presentation in PDF: http://ioug.itconvergence.com/pls/htmldb/DWBISIG.download_my_file?p_file=845
• Recording with my stuff and the all important demonstrations(!): http://ioug.itconvergence.com/pls/htmldb/DWBISIG.download_my_file?p_file=846

A number of the experts on the exchange use a series of UI extensions to provide shuttle and data entry controls. They are written in java but project a tcl interface that makes them fairly straightforward to use. You may find them useful or you can use the same technique to construct your own.

The UI extensions include:

• Capture data input in a table (oracle.owb.jexpert.DataEntryTable)
• Information selection and ordering (oracle.owb.jexpert.ShuttleObjects)
• Display info in table, action them (oracle.owb.jexpert.ActionTable)

In order to get a list of selections from a list and order those items, you can use the shuttle control. Shuttle from available to selected. The UI labels are configurable, the example below illustrates selecting columns from a table that represents levels (in a dimension to be created). It is a modal dialog.

Invoke this example as;
set selection [java::call oracle.owb.jexpert.ShuttleObjects getSelection "Select dimension levels" "Select columns to identify levels:" "Columns:" "Levels" {"PROD_ID" "PROD_NAME" "PROD_DESC" "CAT_ID" "SUBCAT_ID"}]

The select items will be returned in the variable 'selected', they will be in the order the user has defined. The above returns;

CAT_ID SUBCAT_ID PROD_ID

Data Entry Component

This dialog allows information to be collected from the user via a table. In the dialog below, dimension attributes plus the type to level attribute (applicability) to column binding is captured in one dialog (used in the create dimension from expert).

The tcl to invoke this dialog is;
set rowheader {"Attributes/Levels" "Usage" "CATEGORY" "SUBCATEGORY" "PRODUCT"}
set thedata {{"DESCRIPTION" "Standard" "CAT_DESC" "SUBCAT_DESCRIPTION" "PRODUCT_DESC"} {"" "Standard" "" "" ""}}
set avail_cols {"" "CAT_ID" "CAT_DESC" "CAT_INFO" "SUBCAT_ID" "SUBCAT_DESCRIPTION" "PRODUCT_ID" "PRODUCT_DESC"}
set selection_model {{} {"Standard" "Long Description" "Short Description"  "Effective Date" "Expiration Date"} }
set selection_model [linsert $selection_model 2 $avail_cols]
set selection_model [linsert $selection_model 3 $avail_cols]
set selection_model [linsert $selection_model 4 $avail_cols]

set data_result [java::call oracle.owb.jexpert.DataEntryTable {getdata java.lang.String java.lang.String tcl.lang.TclObject tcl.lang.TclObject  tcl.lang.TclObject} "Level Attribute-Column Details" "Bind attributes to columns:" $rowheader $thedata $selection_model]

The dialog returns {DESCRIPTION Standard CAT_DESC SUBCAT_DESCRIPTION PRODUCT_DESC} {{} Standard {} {} {}} into the variable data_result.

This control also supports copy-paste, so results can be copied from and to the table. So for example it is also possible to copy from a series of cells in Excel into this dialog to quickly construct objects.

Alternatively the construction of the dialog can be like the following where the columns listed in column 1 are all of the columns excluding the level identification columns, and there will be a default stating all columns will be attributes of the leaf (EMP for example).

set rowheader {"Columns/Levels" "Usage" "DEPT" "EMP"}
set thedata {{"GENDER" "Standard" "false" "true"} {"MARITAL_STATUS" "Standard" "false" "true"} }
set avail_cols {"false" "true"}
set selection_model {{} {"Standard" "Long Description" "Short Description"  "Effective Date" "Expiration Date"} }
set selection_model [linsert $selection_model 2 $avail_cols]
set selection_model [linsert $selection_model 3 $avail_cols]
set data_result [java::call oracle.owb.jexpert.DataEntryTable   {getdata java.lang.String java.lang.String tcl.lang.TclObject tcl.lang.TclObject  tcl.lang.TclObject}  "Level Attribute-Column Details" "Columns-Level applicability:" $rowheader $thedata $selection_model]

The result of the dialog is a TclList with the selections;

{GENDER Standard false true} {MARITAL_STATUS Standard false true}

Action Table

This control is for when you use OWB to get a list of objects (for example results of a search) and display this list and allow a set of actions to be performed on them.

set rowheader {"Name" "Description" "Path" "My Funky Info"}
set displaydata  { {"DATA_0" "Cube for DATA_0" "//OSA_PROJECT/OWBEXP_TGT/DATA_0" ""} {"LOAD_0" "Load Cube Map Sample" "//OSA_PROJECT/OWBEXP_TGT/LOAD_0" ""}}
set idref {{"CUBE" "//OSA_PROJECT/OWBEXP_TGT" "DATA_0"} {"MAPPING" "//OSA_PROJECT/OWBEXP_TGT" "LOAD_0"}}
set buttons {{"Edit" "OMUALTER"} {"Properties" "OMUPROPERTIES"} {"Lineage" "OMULINEAGE"} {"Cancel" java::null}}

java::call oracle.owb.jexpert.ActionTable displayTable "Search Results" "Object matches" $rowheader $idref $displaydata $buttons

In Summary

These are sample extensions that are supplied via the owb/lib/ext directory in the OWB client and are useful for providing controls for some fairly common UI actions.

The jexpert.jar file is included in the OWB 10.2 release under owb/lib/ext, so you will be up and running. In 11g, it was omitted (a bug) but you can download and copy it to owb/lib/ext from here.

Yes, it is that time of year again. Oracle will take over San Francisco as of next week and you'll see those Oracle folks in every corner of our city by the bay. If you are one of 40,000 plus people coming (even just for the demogrounds) here are some of the things you may want to do when at Open World:

Demogrounds:

OWB is at the Moscone West Database demogrounds (booths O47 and O48), so come meet the folks who actually write this blog!!

Then we have quite some interesting sessions:

Keynote: Monday 6:00p.m. � 7:00p.m. Oracle Database 11g � Innovate Faster Andy Mendelsohn

Monday 3:15 p.m. Using Oracle Warehouse Builder for Business Intelligence - Vlamis  Hilton - Franciscan Room D

Tuesday 12:15 p.m. Advanced ETL and Warehousing at Xerox - Xerox/Wipro - Westin SF -Market St., City Room

Today we released OWB 10.2.0.3 for Solaris SPARC 64 to metalink. Please go to metalink and search for patch ID: 6264457

As of now, here are the platforms OWB 10.2.0.3 patch set is available on:

Solaris Sparc64
Linux x86-64 (AMD64/EM64T)
AIX5L Based Systems (64-bit)
HP-UX PA-RISC (64-bit)
Windows-32 bit
Linux-32 bit
Vista-32 bit (only on OTN and not on Metalink)

There is now a public Oracle Wiki at http://wiki.oracle.com/. We have created a wiki page for Oracle Warehouse Builder here:

http://wiki.oracle.com/page/Oracle+Warehouse+Builder

Like any wiki page, this page will be permanently under construction, and subject to reorganization or cleanup as the development of the content warrants.

Wikis-- especially corporate wikis-- are a tricky business. On the one hand, users and corporate staff can share information and let the wisdom of crowds emerge. On the other hand, a company can pitch up a wiki, call that their support strategy, and in essence tell their customers "Our product documents and supports itself. Isn't Web 2.0 fun?" This is certainly not Oracle's intention.

The OWB section of the wiki is intended to be a useful adjunct to traditional avenues of support for and communication with customers, a space in which Oracle and our customers can learn from each other, and compile the best of what has been thought, said and done about using and extending Warehouse Builder. But the existing support forums and other resources will continue to be available as well, no worries.

Oracle employees and customers can interact on the Wiki by posting new pages, or by contributing to discussion threads attached to each page. As an example of the cooperation that the Wiki is meant to facilitate, we're setting up a collection of user-contributed OWB utilities and experts, at:

http://wiki.oracle.com/page/OWB+Utilities+and+Experts

Over time, this may take the place of the existing OWB Utility Exchange. If you have OWB-related tools and utilities to share, you can publish them there, or host them on your own web sites and simply add links to the OWB Wiki.

We also encourage OWB users to add other types of content to the wiki -- edit the main OWB wiki page, create new pages, or to suggest in the threads at the bottom of the page possible areas for new content to be added. If you have links to other OWB-related material on the Web, feel free to add them.

Note: So far, Wiki visitors/contributors from outside Oracle seem to be mostly using threads attached to pages, much as they used the OWB support forums on OTN. The OTN forums aren't going away, and users should continue using them. The OTN forums are in many cases monitored by Oracle PM, support and developers, among others. The wiki page discussion threads may not be as closely tracked, and should really be reserved for discussion of what's on (or should be on) the wiki page.

BIWA WEBCAST 
March 19, 11:45 am Eastern Time
Oracle Development's tips and tricks for effectively using Oracle Warehouse Builder

It is ODTUG time again. No beach this time, but hey it will be a crawl
away from the Frensh Quarter, so get ready to have some fun. Of course
we have serious stuff going on as well...

I'm of course quite interested in OWB sessions or in topics that touch on it as like data warehousing, ETL and data quality. So here are some sessions that might be interesting:


A BI and Advanced Analytics Case Study (by Shyan Vran Nath, Citco Technology Management)

This presentation will look at the framework for developing a
data-driven planning application using business intelligence and
advanced analytics. Often the planning, budgeting, and forecasting
involves quantitative details that make the job of the approving
authorities harder. Here we take the domain of K-12 education and look
at how advanced analytics can be used to predict the students who are
"at-risk" of graduation versus those who are likely to succeed. This is
a major U.S. national challenge and the government's "No-Child Left
Behind" initiative addresses that. We look at how K-12 cross-subject
education related data such as attendance, enrollment, achievement,
discipline, etc. can be used to create an education datawarehouse and
can be used for data mining. We will look at the use of Oracle
Warehouse Builder, Oracle Data Mining, OBIEE, other BI tools, and the
development environment in the process. The outcome of the data mining
provides the "actionable" data to the education administrators and
decision-makers via the education dashboards. These in turn help to
initiate data-driven planning that can use the results of predictive
analytics to plan for current and future school years. This
presentation was developed using a real Department of Education BI
project from 2007.


A Next-Generation Oracle BI Architecture (by Mark Rittman, Rittman Mead Consulting)

With the recent acquisitions of Hyperion and Siebel, Oracle now has a
world-class set of BI tools to add to their own Oracle Database, Oracle
Discoverer and Oracle Warehouse Builder. For customers though, the
question now is how to build an effective BI architecture out of these
tools, built around the core of an Oracle database and using the
features of Oracle OLAP, Oracle BI Server, OWB and ODI, Oracle
Dashboards, Oracle Answers, and the performance management tools
provided by Hyperion. This presentation answers the questions around
Oracle's BI strategy, provides a BI development roadmap and sets out an
architecture that leverages the best elements of these tools,
explaining how best to use Oracle's BI and data warehousing for a
variety of customer scenarios.

Oracle BI, Oracle OLAP, Essbase: The Benefits and Cost of Openness (by Dan Vlamis Vlamis Software Solutions)

Companies with heterogeneous environments need to analyze data from
multiple sources, through multiple application servers, in multiple
presentation environments. The "hot pluggable" products work well for
this need: Oracle Data Integrator, Essbase, Real-time Decisions, Oracle
Business Intelligence Enterprise Edition, and BI Applications. But what
if these needs are not paramount in your organization? You can use
Oracle's traditional products: Oracle Warehouse Builder, the OLAP, Data
Mining options to the Oracle Database, Oracle Business Intelligence
Standard Edition, and Daily Business Intelligence. Come and learn which
products are appropriate for your situation, how Oracle's acquisition
strategy led to this situation, and how Oracle's product management
structure propagates this situation. The session will especially focus
on the differences between Essbase and the Oracle OLAP option�both very
capable multi-dimensional databases, but with different goals.

For OWB we will be doing 4 development sessions on the following topics:

Development's Bag of Tricks for Oracle Warehouse Builder

You are using Oracle Warehouse Builder and you see many things you
could do or could do better. See how you can solve some common problems
by using the functionality that OWB provides in 11g.
We will be discussing a set of topics with hands-on examples which will
include, for example, using analytical SQL in OWB (hierarchical
queries, advanced aggregations), dealing with XML data sources and
targets, matching and merging information from different systems, and
extending the product using Experts. This session is intended to deepen
your knowledge about Warehouse Builder and make you, as a developer,
more productive.

Best Practices in Implementing Data Quality Process with Warehouse Builder

We all know it is there, we all know we need to do it, but how do we
apply this data quality stuff in real projects? In this session we go
through a set of examples showing how to implement data quality
policies in an organization using OWB. Topics to expect include
determining survivorship of your master records, householding, and
linking of records. In addition, we will discuss and show how to create
smart auditing and testing of data in a so-called data quality
firewall. This firewall prevents bad data from even entering the
systems being targeted. Last but not least, we will discuss how to
implement a quality assessment and reporting structure on your existing
systems.

Warehouse Builder Goes Heterogeneous: What That Means for You

With Warehouse Builder 11g
Release 2 you will get the best of both the OWB and the ODI world in
one tool. Yes, OWB is integrating the ODI heterogeneous concepts and
templates within the familiar OWB functionality. So you now have full
heterogeneous support for both sources and targets included with the
data profiling, data quality, and regular Oracle PL/SQL mappings. On
top of that you will see a unification of the metadata layer in the
database and a unification of the client tool user interfaces (OWB goes
JDev IDE!). In short, if you are interested in ETL and OWB, this
session shows the newest of the newest in OWB land, straight out of
development. See it all in action and ask questions about our direction
in ETL land.

Since we get this question quite often, lets go back into this a little more. What is the story with OWB (or better the database) and web services? Well, you have all the tools and mechanisms around to simply hook it all together. What is all? Anything that speaks web services can speak to the database, and you can hook your DB processes (e.g. OWB mappings, OWB process flows etc) together with the Middleware frameworks like BPEL. You don't need any other tools to anything, just leverage what you have in the database.

How? Here are the details, excellently written out by Kuassi Mensah who is an expert in this at Oracle. There have been enhancements in Oracle Database 11g, but if you are on 10g this should be extremely helpful.

Oracle BIWA Summit - December
2nd & 3rd

Please pass this
information to customers and partners

Sometimes OWB PM gets questions about ways to use Warehouse Builder in consulting situations, sales-related proof of concepts implementations, and so on. Some that came up recently include:

• How OWB can be used to create or consume Web Services (a subject of a recent whitepaper);
  
• How OWB can send email with attachments (the process flow task for sending email makes no provisions for attachments);
  
• How OWB can make updates to an Active Directory or other LDAP server

In the second case, we recommended the PL/SQL package UTL_MAIL.  Incorporating a short bit of PL/SQL code that called the needed functions (make the attachment, create the mail message with the attachment, send it out) into a process flow would cover the customer's/developer's need.

The third case was more interesting, because of the thinking behind it, and the questions it raised.

The original question that reached PM was, "Can OWB write to ODBC targets? (Or do I need a different ETL product?)" The line of thinking was something like:

• I'm using an ETL tool
  
• An ETL tool can update data in database targets
  
• Therefore, if I want to update something that isn't a database, I should make it look like a database target
  
• Therefore, the customer needs to license a third-party ODBC driver for LDAP, and choose an ETL tool that can write to ODBC
  
• Can OWB write to ODBC? ("Yes, we can", by the way-- the core product can use the gateway for ODBC with any target, no cost.)

This chain of reasoning is quite understandable, but it misses an important point: OWB is much more than a tool for talking to data sources and targets. OWB is now and has always been a great tool to maximize productivity for developers of PL/SQL-based data integration solutions on top of the Oracle database. (Future mechanisms for heterogeneous data integration in our roadmap don't obsolete any of the strengths of OWB when used with Oracle databases.)

The PL/SQL part is crucial here. From OWB you have access to all the functionality Oracle builds into the PL/SQL packages that come with the Oracle database. (This, for example, is how we have incorporated Web Services/SOA functionality into OWB since our 9.2 release. PL/SQL is generated for OWB mappings, and all PL/SQL packages can be accessed as web services, therefore OWB mappings can be exposed as web services. Likewise, PL/SQL can consume Web services, OWB generated code is PL/SQL code, therefore OWB can consume web services.)

In this instance, OWB access to PL/SQL enables a much more direct solution, with no third-party component and no need to abandon OWB. We proposed a little PL/SQL code to call the DBMS_LDAP and DBMS_LDAP_UTL packages from PL/SQL to update the LDAP server directly, and incorporate that code into a process flow.

Can we do more over time to package up capabilities like the ones above? Yes, we can-- whenever the market demands it. (Tell us what you want-- you, after all, are the market.)

But the next time you look in your palette and you can't find the right icon to drag out to do exactly what you want, don't give up, reframe the question: "Can the Oracle Database platform do this? Are there PL/SQL packages that can do this?" You won't want to or need to do this often-- but when you choose to, yes, you can.

I would guess many people are using user defined activities in process flow. As do we when we do partner work. Wouldn't it be nice if you could change the icons for the user defined activities to better show what is being done in that activity? In some cases it might be, and it is quite simple to do.

First step is to create an iconset. You do this in the Global Explorer under icon sets. Here is an example of the completed dialog.



Next is to actually associate this iconset with the process flow activity. For mappings this can be done within the UI, but for process flow activities this is done via scripting:

OMB+> OMBALTER PROCESS_FLOW 'LOAD_ORCL' MODIFY ACTIVITY 'RUN_SIS_JOB' SET REFERENCE ICONSET 'STARANALYTICS'
Process Flow Activity RUN_SIS_JOB altered.
Process Flow LOAD_ORCL altered.
OMB+>

After this you get a wonderful icon reflecting the actual external job you are calling.



If you want to show separate icons on the mappings, here is how you do that. It is on the properties panel and it will change the icon in the design center.



Now of course you are wondering what Star Analytics is. Well, this is a company / product that allows you to re-integrate your Essbase data back into the relational data warehouse. It handles the extraction from Essbase, in this case into Oracle base tables. You then run the OWB mappings to load the data into the Oracle data warehouse. Quite cool if you need to integrate for example finance data back into the EDW.

Warehouse Builder depends on infrastructure in the Oracle Database for most of what it does... including calling external Web Services. Briefly, this requires that you install JPublisher in your database, which at this point means that you need to install SQLJ from the Oracle Database Client CD, as well as the Web Services Callout Client.

A lucid and concise description of how you configure an Oracle 10gR2 Database to consume Web services was posted in this Oracle By Example (OBE):

http://www.oracle.com/technology/obe/10gr2_db_single/develop/java/java_otn.htm

It takes you through installing all the required components and then calling an example web service from PL/SQL.

Once you have this working you are ready to start using calls to external web services from OWB (of which I shall write more in a future post).

Hi all, well we are back on line after the content migration. I did notice some screenshots that didn't look too good. Not sure if we can fix all of those.

Expect new postings to start appearing over the next days again. So stay tuned.

So what happened during the radio silence in OWB land. Well, not that much, it is after all summer vacation in most of the world (or winter if you are on the other hemisphere)...

We did ODTUG, which was my 5th conference in a row, and as always, great fun and a very good conference. I especially liked the new Hyperion track at ODTUG. With the Oracle family growing I think there is a lot of value provided by the conferences in introducing everyone to the new technologies in Oracle's portfolio. I had the privilege to be on a panel and discuss technology together with Mark Rittman (you can read more on his blog) and some other folks. Good fun, quite interesting to discuss end-user vs. IT perspectives.

One of the things I took away from that is that we need to bridge that gap in the near future. We need to enable end users to get their information when they need it, but I think we need to not forget architecture and infrastructure. Yes I know I'm one of the old timers who think that is important.

End users should have access to data, but in a controlled manner. At least that is what I think. The Hyperion tools allow the finance guys to set up their own systems, manage their own hierarchies, which is great. But it does not mean that we can't build this on top of a scalable and secure infrastructure.

So for Warehouse Builder I think we will look more and more into enabling more business users to get to their data. Let's call it end user data integration. That way we can allow for data quality to be embedded in everything that is done, we can centralize metadata without being obnoxious about it, and we can secure the base data.

Anyways these are some of my thoughts around direction for OWB in the long run. In the mean time, ODTUG is done and we are already looking at OpenWorld in September. Bigger, better and more involved as always.

Openworld will showcase at least 5 sessions on OWB, a handson session and of course demo stations for data integration and data quality. We will have product management and development around, so if you are a current user, this is the event to discuss your problems, solutions and ideas with the guys and girls who build the product. And of course, San Francisco in September is a nice place to be... So see you in San Francisco!

As of now the following ports are available for OWB 10.2.0.4:

Windows-32
Linux-32
HP-Itanium
Linux x86-64 (AMD64/EM64T)
HP-UX PA-RISC (64-bit)
Sparc64

All of these can be downloaded from metalink (https://metalink.oracle.com/) under patch id 7005587. You will need to log into metalink to download the patch.

The following platforms will follow shortly:

AIX5L Based Systems (64-bit)
Vista-32 bit (and Windows2008-32, Windows2008-64)
Windows-64 Itanium

More on those platforms once available.

Tickets are booked, hotels are reserved, excitement is building... yes it is time for Oracle OpenWorld! As data warehouse and OWB enthusiasts we all want to know the best sessions to go to. Here is a useful PDF printable booklet that you can bring to the show to see the times for the OWB and DW related sessions. Anything from Data Mining, through partitioning, via OWB and to OLAP is in here.

Get it here.

Oh and don't miss Andy Mendelsohn's session... should be good. Don't miss Larry's session this year, it promises to be good fun. Not sure if we have the cute penguins from 2 years ago again, but there will be sufficient stuff to talk about after that.

When going through the blog I noticed some comments actually are questions. Not sure if everyone knows, but there is a very active product forum on OTN. The link has changed over the past couple of weeks, so make sure this is the link you use...

http://forums.oracle.com/forums/forum.jspa?forumID=57

So, make sure to post questions there, not at the blog (unless it is about specific blog posts of course).

There is now an Oracle Warehouse Builder LinkedIn Group:

http://www.linkedin.com/groups?gid=140609

This group was not created to draw attention from the Oracle support forum, which is still the best venue for detailed technical questions-- it's just meant to give OWB users an opportunity to network with each other, commnuicate about what they're doing in their OWB projects, and share work opportunities. All are invited to join.

While a lot of what we do on the OWB blog is about teaching OWB users to get more out of the tool, we also want to stimulate Oracle database customers' thinking about their data in general. (We think OWB data quality and its integration with ETL compare favorably with anything else out there, so the more people think about the problem, the more they will gravitate towards OWB as a solution.)

Dylan Jones over at dataqualitypro.com is clearly thinking about some of the same things we've been thinking about in Warehouse Builder for some time. He's introduced a free tool for doing basic pattern analysis of data in an Oracle database, which in some ways provides similar insights into your data as basic profiling with Oracle Warehouse Builde. You can find out more about his freeware tool here.

If you start getting interesting insights using a tool like this, you will find more value in trying out the OWB data profiling and data quality features against your database.

Many OWB customers actually use OWB as a standalone data quality tool, mostly ignoring the ETL and data integration features when they get started. Then, once they have insight into their data, they can create data rules to actually enforce what they discover, and introduce data cleansing and data auditing on their sources and targets with OWB to catch and resolve any bad data that's coming in.

In general, you can use OWB for data quality measurement and enforcement and even data cleansing without disturbing your existing ETL logic: data profiling and data quality auditing are non-intrusive by nature, and for data cleansing you can create OWB mappings that either cleanse your data in place or copy cleansed data to a temporary location from which you can re-load it with your existing ETL method.

Anyway, we wanted to share this thought with you, and give a tip of the hat to Dylan's community, which is growing fast and generating a lot of fresh, thought-provoking content.

Looking for OWB information but not sure where to find things? Now you can search all of the OWB documentation, collateral, web logs, and discussion forums in one place!

Found an outside blogger who covers OWB from time to time:

http://www.business-intelligence-quotient.com/?s=owb

"Maybe someone from OWB product management reads this," he says. I guess he got his wish. :)

Oracle Warehouse Builder Application Connectors provide simplified access to the underlying data from Oracle's ERP applications-- E-Business Suite, PeopleSoft, Siebel, and MDM applications.

The benefits of application connectors include:

• The database objects exposed to the OWB user can be limited to those that correspond to the logical-level business objects managed by the application.


• Those objects can be managed and used in OWB under meaningful business names instead of cryptic physical-level names, and grouped into business areas if the application provides such metadata.

Oracle makes available application connectors for many Oracle ERP applications such as Oracle E-Business Suite. However, Oracle customers using applications other than those supported by official Oracle OWB connectors have similar requirements.

To help application vendors better serve these customers, we are opening the interfaces used to build OWB connectors for use by creators of Oracle-based applications. A new whitepaper provides all of the needed information to create your own connectors.

Well, not sure if anyone missed my posts on this blog, but recently I moved myself onto a new blog. We have started a more generic data warehousing blog, which focusses on the actual database platform, rather than on tools. You will see items on new functionality, on best practices and other hopefully interesting things. It is also the home of the DW team's ideas and thoughts on the HP Oracle Database Machine.

You can find us at: http://blogs.oracle.com/datawarehousing/

While you are reading this, if you are interested in HP Oracle Database Machine, google Kevin Closson, he writes a nice blog on storage and architecture. Very interesting and very detailed stuff.

Sometimes this blog is very into nitty-gritty, tactical technical suggestions, but sometimes (like when you’re coming up on the end of the week and tired of your to-do list) it’s useful to raise your head and look a few years out at changes in the industry that will probably eventually impact your day-to-day concerns. I tend to look at cloud computing at times like this and try to see how it will affect data integration and data quality.

Recently I ran across this blog post on cloud computing, BI and DI from the Open Group Cloud Computing summit:

A few observations around BI and cloud computing from the show in attending other talks, and just in the hallways over coffee.

First, cloud computing can't not progress forward without a clear data integration and business intelligence strategy, if you ask those in charge.   The core questions are:  How will my information get back into my enterprise when I need it, and better yet, how can I consider data stuck in the cloud in the context of my BI requirements?

Second, security.   Enough said.   

Finally, cultural issues around leveraging platforms we don't own.

Cloud computing is not evil, indeed it's an opportunity to leverage databases-as-a-service, and even information-as-service, at a price point unheard of.   However, you need to place all of this in the context of a cloud computing strategy that specially addresses data security, BI, MDM, and data integration.   

I would like to know how the OWB user community is thinking about such issues as cloud and SAAS become inescapable parts of our environment. I’d also like to shamelessly promote the LinkedIn group the OWB team started on data integration and data quality offerings from Oracle, and foster discussion there.

So:

• if you use OWB or just follow Oracle data integration offerings, come join the OWB LinkedIn group. It will put you in contact with hundreds of Oracle and outside people who work with or work on Oracle Warehouse Builder and our other data integration offerings. (There are also job listings and recruiters in the group, which may or may not interest you.)
• If you’re interested in these topics, come join our discussion over at LinkedIn.

Hope to see you there…

The Oracle Data Integration Linkedin Group (originally just an OWB group, now open to all users of Data Integration technologies with the Oracle database) is closing in on 500 members now.

Come join us at LinkedIn.

Group members include product management, development and marketing from the OWB and ODI teams, as well as users of each product, and recruiters and hiring managers looking for candidates with OWB skills. People post job leads, questions, and questions and discussions about the future of data integration at Oracle.

Will you be the one to put us over the top? (I sure hope someone is...)

Just got a note on this new book on OWB 11g. Might be a great resource for those working on OWB to augment the user documentation and see someone else's take on the OWB world.

Book is here: http://www.packtpub.com/getting-started-with-oracle-warehouse-builder-11g/book

Enjoy!

Here we see how to to construct a parallel unload mapping leveraging the Oracle Database parallel table function capabilities to write the results from a PLSQL Ref Cursor to file. The example also illustrates how the table function arguments are constructed within OWB. The format of the file is determined by the user in the mapping by an expression defining the data to be written.

The example is a simple table to file scenario just to illustrate the basic mechanics, but the query/operator set can obviously be more complex. Below we see an example writing the results of a SALES table to a file, the target is a PLSQL table function which has a refcursor parameter; a ref cursor which produces the record to be written to the file (the filename and directory name are embedded in the function definition).

The record itself is a string and is a concatenation of the columns, below we see the fields delimited by commas, if you wanted any specific locale expressions or wrapped in quotes for example you can do this here.

The table function itself has details of the underlying PLSQL function used and an indicator that it is being used as a target in the mapping (you must set this, its not smart enough to figure it out).

Now you see that the table function operator has 2 groups, 1 input group and one output group. There is a property which defines the type for the group and if you have a mixture of ref cursor inputs and scalars then you will need more than one group. So I have one group (INGRP1) for the ref cursor which produces the record to be written, it is defined with a type of ref cursor (so the operators sourcing the attribute in this group will be input via a PLSQL refcursor for use in the function).

The output group is simply returning a table of scalars so I made sure the group has that property set.

That's about it, so when we generate the code we see a call to the table function ParallelUnload wrapped in a TABLE call, and the parameter passed in; a ref cursor containing the query representing the operators sourcing the attribute (the 'select blah from SALES' statement). Note also the PARALLEL hint which I configured on the SALES table and defined 5 as the number of concurrent servers.

Running this will allow parallel unload to file. The function

create or replace type NumSet as table of number;

/

create or replace package RefCur_Unload as
    type Line is record (vc varchar2(32767));
    type MultiLine_cur is ref cursor return Line;
end;

/

create or replace function ParallelUnload
       (r RefCur_Unload.MultiLine_cur) return NumSet
   PIPELINED  PARALLEL_ENABLE (PARTITION r BY ANY) is
   i binary_integer := 0;
   rec2 varchar2(32767);
   out utl_file.file_type;
   filename varchar2(256) := 'dbunload';
   directoryname varchar2(256) := 'MY_DIR';
   vsid varchar2(120);
begin
   select sid into vsid from v$mystat where rownum=1;
   filename := filename || vsid || '.dat';
   out := utl_file.fopen (directoryname, filename , 'w');

   loop
     fetch r into rec2;
     exit when r%notfound;

     utl_file.put_line(out, rec2);
     i := i + 1;

   end loop;

   close r;

   utl_file.fclose(out);
   PIPE ROW(i);
   return ;

end;
/

The function uses the PIPELINED AND PARALLEL_ENABLE keywords to indicate the table function and parallel capabilities, the Oracle Database Data Warehousing Guide has more details on this in the 'Loading and Transformation' chapter if you want to dive into more detail. Also each parallel session of the function’s execution will write a data file (so data file will be named dbunload<sessionid>.dat). There was an earlier post on table functions (here) as source and target operators which is also worth checking out, they are a useful way of adding some custom transformation code into the mapping design, and all leveraging Oracle PLSQL.

The Oracle database has a range of powerful regular expression based pattern matching functions that can be used to filter and match data, in OWB for example conditions can be used in many places such as filters and joins. This post illustrates how to incorporate pattern matching conditions into OWB today in mappings you design, the data rules within OWB utilize these routines for when they match data (identification numbers, telephone numbers etc.). As the condition types evolved through the releases of the database as did support in OWB, but there is a little hoop to jump which was pointed out from Peter at Rittman Mead Consulting.

The OWB validation for condition varies, so the expression may work in a filter but might be slightly different in a join clause. The REGEXP_LIKE condition is not handled fantastically in the join condition (putting it politely), so we have to do a wee (Scottish accent coming out there) workaround.

So let's look at a basic example from the manuals, the following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph), simple right:

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$')
ORDER BY first_name, last_name;

This uses the EMPLOYEES table in the HR demo schema, it results in the following;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

Now building the same mapping with the same condition in OWB works fine;

We get the map validated fine and code generated, great so far.

Now we try using the JOIN operator and have a fabricated example where the regular expressions to be matched are in a table MATCHING_TAB and we will join this with the EMPLOYEES and use the REGEXP_LIKE function to match;

When you try generating the code for this map design you will get an error stating the join condition is not valid and that a join condition must be a boolean condition. The OWB code generator is parsing your conditions here and has support for a fixed number of condition types, to use REGEXP_LIKE we have to be a little creative.

So you can't just do REGEXP_LIKE(...) =1 or anything, one approach is to use the CASE statement, to test the pattern matching condition and return 1 if matched and 0 otherwise, then we can test for 1=1...phew! Here is a working design that provides OWB with a valid boolean condition;

You can see the join condition uses the FIRST_NAME column from EMPLOYEES and the EXPR column holding the regular expressions from the matching tab in the REGEXP_LIKE function. A quick run through a query that others will hopefully find useful.

Great news, today Oracle released the Linux x86 and x86-64 versions of 11gR2 for the Oracle Database which includes Warehouse Builder (see Oracle Database 11gR2 here). Nice to finally see all the hard work eventually come to fruition. This has been an interesting few years, through acquisitions, the tough economic times, the release went on sure and steadfast, hats off to all the development group. The OWB 11gR2 release has some substantial changes all round - from the user interface to the open mapping framework to the OBIEE integration to name a few. I love pushing the envelope (common idiom, see here) with tools and seeing what can be done with them and this release of OWB lets you do LOTS!

So where is the collateral?

Keep an eye open for new collateral as it is completed there is a lot of really useful functionality from cool new stuff to basic mapping functionality people have wanted for years! For a brief write up of new OWB functionality see the new Concepts manual here the OWB OTN page has some updates already here.

Where is the software?

Oracle database download (includes OWB):
http://www.oracle.com/technology/software/products/database/index.html

OWB standalone download:
http://www.oracle.com/technology/software/products/warehouse/index.html

Watch out for new posts, fun training and active forums!

Open World 2009 is almost upon us. For those thinking about attending, there's a schedule of events and product demos around data integration on OTN, here:

http://www.oracle.com/technology/products/oracle-data-integrator/events/data_integration_openworld-2009.html

This page will be updated on an ongoing basis as we get closer to the date of the conference. If you're planning to attend, join the Warehouse Builder/Data Integrator Linkedin Group and contact me, the group manager.

While the formal sessions at Oracle Open World provide a lot of valuable information, sometimes the choicest nuggets turn up at the Unconference sessions, where Oracle employees, customers and partners can delve into the topics that interest them most in an interactive forum.

For OWB customers the best chance to see OWB 11.2 at OpenWorld will be an Unconference session conducted jointly by Oracle DW and BI guru Mark Rittman and data integration product manager Antonio Romero.

The real treat, though, will be the special guest appearance by OWB architect David Allan! Through this blog, David has become one of the public faces of OWB and his knowledge of how to get the most out of the tool has been invaluable to internal and external users.

Planned topics include:

• ODI-based code template mapping capabilities
• Integration of Warehouse Builder with OBI-EE
• Right-time Data Warehousing: CDC, Trickle-feed mappings

...but since we're in Unconference mode, we can do a little off-roading, time permitting, as the audience demands it.

Schedule details: Wednesday, October 14, Moscone West, Third Floor, Overlook II.

While this is a bit unstructured, it helps to have a preliminary headcount. To register, you'll need to take a second to join our LinkedIn Group if you haven't already. Click here to register!

There are a number of OBEs available for the OWB 11gR2 release which range from an overview of all the cool new usability improvements in the mapping editor (auto-mapping improvements, copy-paste expressions, operators, operator groups, group/ungroup operators, spotlighting to name a few!), through basics of using the operators in a mapping for joining, filtering, pivoting and so on, then diving into building pluggable mappings (aiding reusability and good software design practice)  and last but not least an OBE on data profiling covering profiling, deriving rules and even cleansing the data!

The entire Oracle OBE 11gR2 release OBE's are here, and the OWB set are under 'Business Intelligence and Data Warehousing' then 'Warehouse Builder':

• Improved User Interface, Usability, and Productivity With OWB 11g R2
• Handling Flat File and COBOL Copybook Sources in Mappings
• Using Data Transformation Operators with Source and Target Operators
• Working with Pluggable Mappings
• Examining Source Data Using Data Profiling

There are some interesting examples here, worth checking out to see some of the cool new features and existing ones that illustrate the capabilities! In the 11gR2 set there are other interesting posts well worth checking out such as the High Speed Data Loading and Rolling Window Operations with Partitioning post, all essential information for leveraging the database to the max in your ETL loads.