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 rittman.net.

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.

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!

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!

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!

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.

I was diving into some details of the Runtime Audit Browser for the course I just created about Administration and Operation. As I was digging into some of the trace data displayed in the RAB I noticed that I had never actually used the “ROWKEY” here to lookup the actual database rows in question.


Example, some row has had an error during processing and an error message is generated here along with it’s ROWKEY.

When I saw the ROWKEY I realized I had not idea how to use it to find the actual row. The ROWKEY appears to be the record number within the cursor, which as most readers know means that you can’t actually use it to get to the physical row. I perused the OWB public runtime views and did not find anything useful there, as the only information provided was still this ROWKEY. I did some digging and found an undocumented VIEW that helps track down and find the Oracle ROWID that can be used to find the row in question.

Login to the OWB Runtime Repository owner (or some other user that has access to these views). Track down the execution audit id, which you can use the following query with your mapping name.

Next, use the following query (or some derivative) to generate a report with the cursor keys and the actual oracle ROWIDs. Note: This uses a view that is not part of the public OWB API so you should know that this might break at any point since Oracle has not made them an actual public interface.

You now have your ROWID for the trace rows… You can query for the actual row by using ROWID in Oracle by using a query like this:

Did you notice above, that in the trace data it was only the DML cursors that had ROW_IDENTs? What a bummer! If the DML succeeded and the row was inserted then there would probably not be a need to track it down using this method. Clearly this is of limited use since OWB appears not to track the ROWID of the SELECT cursors. If anyone has had any better luck on tracking down the SELECT ROWID (or other method to identify the errored rows) I’d love to hear from you, and I’d be happy to post it here.

I put togther a three day training for a large US wireless company to help their Operations, System Administrators, and DBAs understand and administer an OWB Data Warehouse solution. These topics are covered only partially in the Oracle University course, and rightfully so. OWB is primarily a development tool and most people who use it are “developers;” some however are charged primarily with administering, testing, and managing OWB solutions. This course, offered only for private onsite trainings, is perfect for these individuals.

Here’s a PDF with the course outline, and sample slides. The training manual is hundreds of pages worth of slides, notes, and hands on exercises. If your company is interested in developing knowledge about OWB in your DBA, Operations, and System Administrators please email me to discuss bringing this course to your company.

This course is for Operators, Administrators, and Quality Assurance staff for organizations that utilize OWB. Students will learn the fundamental architecture of OWB, its repository structures, and how mappings are built (logically and physically). Students learn how to import, export, and sample metadata along with view metadata reports through the web. Students learn how to execute mappings from within OWB, and also from SQL scripts and Enterprise Manager.

Students will learn how to generate common trace files/plans for OWB mappings so they can leverage their existing knowledge and Oracle tuning skills. Students will understand the security features provided in OWB and what steps should be considered to help them secure their OWB installation. Students will also learn how to build powerful process flows, email notifications, error dispatching etc.