So, you’ve all heard about the Data Profiling features in the upcoming release of OWB 10gR2, right? If not, review articles here here here.

The bottom line is that Oracle pulls over the data from the source tables, and performs a bunch of “crunching” on them to deliver some valuable insight into the composition of the data. You can even see the exact records which fit certain circumstances. For example, 99.8% of values fit this dictionary, the .2% do not, and you can highlight that .2% value and see the ACTUAL rows that compose this .2%.

So, where is OWB doing this work? Which schema? It it doing this in the REPOSITORY, or is it using one of your TARGETs without your knowledge? Well, the answer is: you get to choose! Create a User that is used as a target right inside the OWB schema (DEV2_PROFILE) so that there is a target for OWB to use.

Then, in the OWB Preferences screen

Set the Profile User to your newly created target, DEV2_PROFILE

This blog is part of the OWB Paris Early Review series which reviews and comments on several new Paris features.

ODTUG accepted two of my presentation proposals on OWB. Unfortunately, the presentation on “Real Time Data Warehousing” had to be cut because the same happen to the feature in the Paris release.

However, I’ll still be giving a survey of new features.

Oracle Warehouse Builder 10gR2-Late, but PACKED with Features!
Oracle Warehouse Builder 10gR2 is packed with useful features and not just for the data warehouse professional. Improved ETL, expanded metadata capabilities, and advanced dimensional editors will mean a great deal to data warehouse developers. Features like model-based streams integration and the data profiling/cleansing features will even make DBAs sing Oracle Warehouse Builder praises.

If you’re interested in attending, register for the virtual conference here: https://www.odtug.com/ssl/Virtual_2006_registration.asp. You can still get early bird pricing until February 1st.

The folks at Oracle were kind enough to bestow their ACE award on little ‘ole me.

Muchas Gracias! I suppose I’ll add it to my sidebar now!

When I was on the BI and Reporting Tools panel at the UKOUG there was a rather interesting discussion that developed about the suitability of OWB to do anything useful. I recall there was one gent that politely but pointedly articulated what at least a few others in the room were privately thinking: OWB is rubbish, now isn’t it?

A thread, with some fervor, on OTN is not exactly kind to OWB. Some excerpts:

OWB gui looks like some undergraduate student learning JAVA has made this.

Every project which does more than data loading from an file into emp is not suitable for OWB. If time, budget and quality is relevant for an project, OWB is unsuitable for this project.

A very important word for OWB developer here is “workaround”. That’s the W in OWB, not Warehouse.

I’m always surprised at this because I’ve had such success using this tool and think it’s quite capable overall. It’s cheap, effective, efficient, and provides 90% of the functionality of it’s peers at 10% of the cost. I have some beefs with the product as well, but overall it’s a rapid way to respond to the information needs of business users for reporting.

So… in some ways, since the tool is quite powerful when used properly, I think it’s rubbish to say otherwise. OWB is an exceptional VALUE, full stop. I can’t invite everyone to comment, since I’ve closed comments… Let’s discuss on OTN!

Not earth shattering news, and who knows if there isn’t some “extra stuff” in it for the Beta releases, but I just realized that OWB is placing more than 1GB of it’s own bits on my virtual workstation. Beta4 would not fit on one CD (800 MB).

Of course the actual release might differ, and your mileage may vary, etc. That, and who really cares about disk space these days? Disk space is cheap! 🙂

I had organized an opportunity for Paris users to get together and swap stories, tips, etc. There were many different viewpoints represented and I’m glad that we had a chance to virtually meet up and discuss our experiences with the product. There is not another virtual user group meeting planned, but we are setting up an online forum to continue the peer to peer discussions.

If you are an OWB Beta program member (legal reasons) and would like to get on the forum, please email me.

PS – I’m happy to open it up to the community as soon as the developer preview release hits OTN.

Looks like 10.1.0.4 is out for just about every platform now…

A slight variation on some SQL I posted a few months back:

For MAPPING executions list the Start TIme, clock time in seconds, number of records selected, and the throughput.
select
ae.created_on start_time,
ae.object_name map_name,
ae.elapse_time time_in_seconds,
mr.number_records_selected total_num_records,
trunc(mr.number_records_selected / ae.elapse_time) records_per_second
from
all_rt_audit_executions ae,
all_rt_audit_map_runs mr
where
1 = 1
and ae.execution_audit_id = mr.execution_audit_id
and ae.elapse_time != 0
order by 1

WARNING: While I think it poses little risk, use at your own risk and ensure it’s right for your environment… Same thing everyone says about code/SQL posted on the internet.

I’m usually posting pictures about fancy new features in OWB Paris, and doling out little tidbits about how to this and that… I thought I’d change it up a bit and ask a question! Now that I’ve got comments working I can do fun things like that! I don’t want to influence any ideas so I won’t mention how I would go about this, so, here ’tis.

What is the most EFFECIENT way using Oracle SQL to calculate effective/expiration for denormalized tables? ie, if you have two tables with the SAME natural key with their own set of effective/expiration dates what is the most EFFICIENT SQL to generate a new denormalized table with accurate effective/expiration dates?

Consider the following example ERD:

What’s everyones favorite solution?

Not a long entry, but I did want to post some information on a useful little feature in the Paris release of OWB.

OMBPlus is the Swiss Army Knife for OWB developers. It allows you to do “OWB Stuff” without using the OWB GUI. If you’ve ever had to do many repeatable things in OWB you’ll be thankful for the hours that OMBPlus can save you! More details on OMBPlus can be found on OTN.

Currently you have to fire up OMBPlus seperate from the application and run it kind of like a text shell (interactive or fed a script). In the next release of OWB they’ve put this interactive shell environment as one of the panels in the design center. This is SOOOO very convenient when building OMB scripts.

Make a note though, some things won’t work in the context of the embedded OMBPlus window. Basically, things that require connecting to the OWB repository in SINGLE USER MODE you’ll still have to fire it up seperately (such as creating UDOs, etc).

This blog is part of the OWB Paris Early Review series which reviews and comments on several new Paris features.