Category Archives: Oracle

OWB 10gR2 "End to End" Metadata Management

Here are the slides for the presentation I gave at the UKOUG conference last week. There was some interest about this, and while it was rather late in the day I think that there might have been some light bulbs going off for those attending. There were a couple of questions about using Model Driven Architecture metadata (ie, UML to generate your application code) to integrate with the warehouse and OWB. Generated application code (Java) and Generated ETL code (OWB) are a good marraige so I’d I think there’s some interest.

Check out the slides to get the gist of the presentation, but basically what we’re talking about here is extending the OWB Metadata Repository (OMB) to include additional items. Above is pictured some example UDOs that I created that would be quite interesting. Integrating Business Objects, Crystal, Oracle Reports, Discoverer Metadata with OWB to get a true “end to end” picture of the data moving through your enterprise.

Mark Rittman posted a picture of me giving the presentation as well!

Comments, as always, are very welcome!

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

UKOUG : Days 1 and 2

I’m quite impressed by the UK user group that puts on quite a large conference in Birmingham, UK (aka Brum). While I believe there are many more regional groups in the US, I don’t believe there are any that are in the same “league” as the UKOUG. There must be several thousand participants and over 250 different sessions to attend… VERY, VERY impressive!

Some presentations of interest were those on the CBO, RFID, materialized view query rewrite, XMlQuery in Oracle, Oracle 10g OLAP and Discoverer, HTMLDB New Features. The session on XMLPublisher was full by the time I arrived and I’m bummed to have missed it.

One thing I’ve really enjoyed is the chance to meet some people that I’ve only had the chance to chat with, virtually, on email. Peter Scott, Jeff Moss, Jon Mead, and Julian Ford. I am well aware that all these people “know their stuff” because of their various blogs, articles, and emails. Now I know that they are genuinely nice people; it’s just as easy to chat about local beers as it is about the ins and outs of the Oracle database. Nice to catch up with Mark Rittman as well who put together a dinner of the bloggers that was well attended (and paid for by UKOUG, thanks!)

I was a bit taken aback by some of the Paris information being put forth at the conference… I’m uncertain if the beta information is not disseminated to Oracle employees properly or if it’s just a desire to keep upbeat about an overdue product. All the same, information announced publicly at Open World was not even covered (Paris released in CY 2006, officially). It’s clear that there is great customer interest in Paris and I think it’s a great leap forward for OWB. Paris is a great product, have no doubt! Just needs to get “finished up” and out the door!

I put on a presentation about new Metadata features of OWB Paris… I think some people found it useful, but it also might have been a bit of a firehose at 5pm at the end of conference day. I had this experience at another User Group before and I hereby resolve to refrain from submitting any more ‘highly focused’ presentations. Conference attendees I think would benefit more from something much more widely applicable… All the same, I’ll post the slides when I return for those that ARE interested and either did or did not attend the conference.

Blew off the social event to have a wonderful meal at the “bank” with my fiance. Excellent!

Oracle is free, btw. As if this hasn’t been blog covered, much to the dismay of Mr. Thomas Kyte. 🙂

OWB released for Windows

The latest “patch” (and you’ll see why this is in quotes shortly) to OWB 10g Release 1 has just been posted in Metalink (patch number 4703215).

It’s a slightly odd patch, seeing that it requires installation into a new Oracle home and the patch installation notes read more like a major upgrade. It’s a 528MB patch… That’s a full installation mind you! Paul Narth posted in the OTN forums that there is an in place repository upgrade so you can patch from 10x to for the repositories.

Other than just a bunch of bug fixes, why patch to

    Two reasons:

  • Paris is “wicked late” pushed out to Calendar Year 2006
  • It will run on Oracle DB 10gR2 which and prior DO NOT

Best Coverage of Oracle Streams?

I see there is a book on Streams from Rampant but I’ve heard mixed reviews of Rampant books… I will probably end up buying it (only $12 at Amazon right now) but wanted to ask if anyone has any book suggestions with great detail and examples on Oracle Streams… I’m sure I can sort through some of the OTN material and the manual, but I’d like another resource. Thanks for any recommendations readers have in this regard!

See some of you at UKOUG in just a few days!

OWB Paris : Virtual User Group Meeting

Bayon Technologies, Inc. is sponsoring an informal web conference for OWB Paris Beta members. Open ONLY to beta program participants, it’s an opportunity for OWB’ers around the world to both share and learn about current “in the field” experiences with Paris.

I’m limiting it to 10 participants to ensure the “informal” nature of the meeting will remain effective (any bigger it would need a little more formality). I’ll offer up my experience with a couple of “gotchas” and some experiences with the new dimensional operators.

If you’re interested, please fill out the survey below so I can select a time and send out an email with the virtual conference details. I look foward to hearing about everyones experience with Paris! I’ll email out the details when I return from UKOUG (approximately Nov 7th).

OWB Paris Virtual User Group Meeting Signup

Free OWB Tuning PDF

I’ve been barely able to keep up with my current OWB projects and getting ready for my UKOUG presentation that I haven’t had ANY time to work on the previously floated ideas for blogs. However, I didn’t want to pass up the chance to add to a thread started on

Jon Mead, a colleague a SolStonePlus, emailed Mark and I about his findings about the tuning method “in the real world.” Jon and Mark had sorted out many of the details for how to generate diagnostic data for OWB Mappings and Flows. I asked to include this exceptional work in my OWB Tuning Workshop (which is the final afternoon of my Administration/Operations course) as part of a method for tuning OWB DW solutions. The method I put forth in the workshop includes information on generating detailed information for tuning particular mappings, but I included it as part of an overal process for tuning OWB Data Warehouses.

Method 1: OWB Runtime Data Analysis

This involves examination in greater detail the runtime audit information provided by the OWB runtime engine. The web based interface provides only per-run reporting, and there is currently not any Oracle provided interface for analyzing the data in the Runtime Repository. We’ll examine some of the data available as Oracle views, and understand what information is available to us. It provides performance data on a macro level, and does not provide detailed diagnostic information (an explain plan for instance).

Method 2: Mapping/Process Flow Analysis

We’ll learn how to set our mappings and process flows up to generate very detailed information about how Oracle is processing our logic, and an entire wealth of information used for common Oracle tuning practices. This includes being able to explain plan, receiving information on wait events, etc. We’ll learn how to implement these diagnostics in our mappings and process flows along with how to collect and assemble them from our Oracle server

Jon echoes the same reasons why this tracing should be used as part of an overal process: too much data and diagnostic data to sort through. Make sure your dollars are well spent and tune mappings one at a time. Tune the ones that will provide the most direct benefit (decrease in load time), or will not scale well (hockey stick anyone?). I have in my “OWB Toolbox” a little OWB Performance Data Mart that transforms the OWB Runtime View data into a ROLAP mart (with Disco). It’s pretty cool, actually! I’ll see if I can’t take some screenshots from a non-client instance (when I get the time, right?).

It might not be good business sense to publish parts of a course that I charge money for, but hitherto I’ve been a better techie than businessman so what do I care!? 🙂

Without further adieu here are the first 13 workbook pages (there’s 90 pages total) from the afternoon workshop that enumerates a method for tuning OWB solutions. Feel free to contact me with any feedback or if you think you could use some help tuning your OWB solution.

UPDATE: A reader sent me an email noting that there is a rather amusing metaphor mixup in the paper… Blog visitors from “.ca” should recognize it straight away. 🙂 I’ll revise it eventually.

OWB 10gR2 slips to 2006

Originally reported by Mark Rittman:

Apart from Report Center though, there were no significant new products launched at Open World that affect BI and data warehousing – indeed, we were told this week that OWB Paris won’t be out until calendar year 2006 (that’s over two years late by my estimation) so there probably won’t be much new going on for a while now.

I emailed the OWB product managers in Redwood Shores and they confirmed that Paris will not be out until calendar year 2006.

I have mixed feelings about this development. OWB 10gR2 is such a marked improvement on OWB capabilities that I am dissapointed that most customers are unable to use it on their DW projects. I’m building a new DW using Paris for a customer in Houston and parts of the development are SCREAMING by because of some of the new Dimensional modeling features. That being said, using the beta product I’ve been only at 80% effectiveness. Simple things still tend to blow up and take some time to sort out… This is not a bad product, it’s a beta product. In that regard, I’m happy they’re waiting. OWB is a solid tool… Releasing it too early with issues would not help the uptake and long term reputation of the product.

I’m just hopeful it’s not Paris in 2012!

Oracle Open World from afar

I couldn’t make Open World this year… too busy on my project to skip out for the week. Quite unfortunate! However, all the bloggers from the conference deserve our thanks for their coverage of OW. I’ve been listening to Keynotes, reading through presentations of interest, and otherwise getting much more of the “scoop” than in years past.

Thanks to all the OW bloggers on behalf of the world Oracle community!

Using a different schema than username in OWB

It’s not uncommon to set up a special Oracle account to access data in another schema. This is common practice and a good practice, especially when it comes to reporting users.

You want to give a reporting user a limited view of the system; only SELECT access on known interface tables or only SELECT on tables in exactly one other schema. This helps protect the usually powerful schema owner (ACTUAL_SCHEMA) from being widely disseminated. This won’t be new to any bayon blog readers.

The question is:

How does one set up an OWB Oracle Data Source Module (ACTUAL_SCHEMA) that uses one username and password (LOW_PRIV_USER) for authentication but access the objects needed (ACTUAL_SCHEMA)?

The answer is:

You can’t in OWB 10gR1, but you can in OWB 10gR2 (in beta). Note: OWB Product Management emailed a solution in 10gR1 (see below)

I’d love to be wrong, so if anyone would like to send along a solution I’d be happy to post it here.

I reviewed the link area (for importing metdata), nothing! Ok, so perhaps it’s just for metadata. It’s not the end of the world as long as we can register when a different username/schema pair.

However, you can’t register using a different schema than the user either.

Last thought was to try and “Configure” a location, but you can’t do that.

I’m starting to sound like an Oracle employee, but the next version is great. This whole process was a snap with Paris.

If anyone has figured out how to do it in 10gR1 please email me and let me know.

UPDATE: The way to accomplish this in OWB 10gR1 is to configure the mapping to not use the LOCATION but to use a custom DBLink or schema prefix instead. I’ll try it and add a screenshot of this process, but basically this allows you to override the location for every mapping. Thanks Nikolai!

RSS and Paris Project

Did anyone notice that with the upgrades to the forums on OTN, there are now RSS feeds for forums and threads? What a welcome addition to the forums! Add the RSS for the Warehouse Builder forum to your RSS reader.

I’m head deep in a project building a new Data Warehouse with Paris. I’m getting to use even more areas than I had when I was just “kicking the tires.” I’m hoping to post some more findings over the next few weeks on volume, scalability, configuration, deployment, etc.