As a recent transplant to Seattle, I’m keen on getting connected with my local community of Linux, Open Source, BI, and Oracle brethren. I took in my first Puget Sound Oracle Users Group meeting last night. The group draws between 10-50 people per week, appears well organized, and based on my observations from my first meeting, provides substantial substance in a peer-to-peer setting.

Daniel Morgan, from the UW extension school, presented three mini-lectures on DBMS_RECTIFIER, Instead of Triggers, and ORA_HASH. I highly recommend you visit these pages on PSOUG, they have examples that demonstrate the following features quite nicely.

DBMS_RECTIFIER is a rather interesting package, especially for someone who spends a great deal of my Oracle time building CDC routines for Data Warehousing. At a basic level, it’s two procedures that provide a quick method to easily identify differences in data tables, and correct those changes. The rectify has an interesting behavior, and it’s not particularly well suited to CDC. However, the differences procedure quickly identifies differences (can be scoped to only a few columns as well) and places the different ROWIDs in a seperate table. In a pure Oracle environment, a BI professional could make use of this since ROWID provides a very quick way to access rows.

Instead of Triggers apparently have been availabe since Oracle 8. Some views are updateable, if they match a certain criteria. However, those that do not match this criteria, there is still a way to allow for updates/deletes/inserts on these views. Oracle allows you to make a special trigger, an Instead Of trigger that only applies to Views. They allow someone to specify exactly the DML to occur, and can include procedure and function calls, and DML on completely different tables. This could be used to :

The OWB runtime generates runtime audit data for every process, mapping, and file loading that it invokes. If you have intricate flows and load your warehouse/mart several times a day (every 5 minutes perhaps?) you’ll see these pile up quickly. The easiest user method for purging this audit data is through the OWB Runtime Browser that allows you to select (checkbox).

Sometimes you don’t want to manually do this, but rather have a way to do this programatically. Perhaps because you want to keep a clean house (always purge greater than 30 days old). Perhaps you generate a lot of audit data in a development environment that’s a hassle to delete from OWB RT Browser. Perhaps you rarely visit the OWB RT and don’t want to fuss with keeping it up.

Whatever your reason, please feel free to use the following little script to systematically purge old audit data. It must be run as your OWB Runtime Owner. I’ve not run this by Oracle, or seen them document this API so use it at your own risk. It works, though.

begin

  for purge_ids in (select distinct top_level_execution_audit_id id

            from ALL_RT_AUDIT_EXECUTIONS

            where updated_on

					

The OWB team has clearly heard the masses and responded to their difficulty in installing and getting “up and running” with OWB. I have to think back to those first few days of head scratching over the installation and “quick” start guides to remember how tough it was to just get to a point where you’re building tables and composing mappings. Thinking back it seems painless but as I’ve observed at customers and conferences alike there are great difficulties and a learning curve to installing OWB. Smart, intelligent DBAs and BI architects had to spend some amount of time to understand the concepts to get going.

Previously there were numerous steps (I believe the OWB team mentioned a total of 60 steps, but those included “double click on this” and the like). They’ve been simplified by the consolidation of the two repositories. OWB veterans are aware there were previously two primary repositories that one interacts with to get things done. There was the DESIGN repository which held the metadata, ETL definitions, table structures, etc Then there was the RUNTIME repository which invoked and audited the acutal flows, mappings, and deployments being executed. Beyond that, every schema that was going to execute any mappings, or have any data objects also required the installation of a TARGET runtime components so the runtime repository could execute and monitor ETL in that schema. In a stage/warehouse/multiple mart setting, this sometimes meant stepping through the Repository Assistant many times (24 times at one of my customers for a set of development/stage/production environments).

OWB10g Paris consolidates the two primary repositories (DESIGN and RUNTIME) into one unified repository. This greatly eases the burden of installation, which can be done from a simple “New User” wizard on the client. During my testing of the product, we were working with a single database so I was not able to test a multiple database installation. The OWB product team indicates that the original topology is still available for customers wishing to use only portions of certain repositories (ie, use one design, with development/testing/production runtimes).

There is also an augmented OWB user security, permissions, and role based system that supports another great feature of OWB installation. Target schemas (which often make up the bulk of installation time in new environments) allow for new target schemas to be added as a simple afterthought with on the last screen of an add User wizard. OWB objects now have permissions and can be assigned throttled permissions (full ctl, edit, read) by both user and role.

It was noted by OWB product team that the security provided in OWB is enforced for access to the metadata, not the actual deployed objects. If one restricts the “Customer” table in OWB to only those with “INTERNAL_EMPLOYEES” this means that only those users in INTERNAL_EMPLOYEES are allowed to utilized, examine, and otherwise edit the CUSTOMER OWB table. When that table is generated and deployed, it is then subject to the normal Oracle permissions and OWB does not have a method for managing that.

My fellow testers echoed the sentiment that if OWB is really positioning itself to be an inclusive, central location for BI work in an enterprise it will also need to figure out how to capture security and access rules. OWB team product team acknowledged need… We’ll see if it ends up in a major revision or two out.

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

I’ve been using regular expressions for years. A few handy cut, sorts, egreps, and uniqs work wonders on datafiles for quick easy troubleshooting. I spent an hour or so tracking down what I think is a funny implementation of regular expression matching. Thought I would quickly post it, and hope google picks it up so that perhaps I can save some poor soul the couple hours I just lost to it.

There is limited information about owa_pattern available; one of the better resources I’ve found is this page on stormloader.com. One of the bits of advice on that page is to:

Or use the second function in owa_pattern, amatch. This function has an IN parameter that dictates where you want to start to match. For our purpose, we don’t care so set it to 0. What we want from it is the type of the return value, integer.

Examine the next two commands and their results, and perhaps you can glean what the issue is with setting the offset to 0
select DATE_UTC, owa_pattern.amatch(date_utc, 0, ‘2004’) from xxxx yields

select DATE_UTC, owa_pattern.amatch(date_utc, 9, ‘2004’) from xxxx yields

The offset, or where to start, doesn’t necessarily mean that that is where the pattern matching will begin to scan for the pattern. It most literally means that the pattern must start at that offset. I’m not a huge expert on regex, but I wonder why a function that is supposed to tell YOU where the regular expression exists in the string requires that it start on an offset specified. Anyone have a perspective or a different experience (perhaps I’m looking at this improperly)?

Of course software vendor product teams products shots always show their UI configured in the most efficient way possible leading you to below that the development tool their pitching will be the greatest thing for your efficiency since Venti quadruple non-fat lattes. Many times, the actual product usually falls short and requires a significant curve to “grock” how the product designers thought that YOU would be going about your job. The OWB product team has experience with doing a significant rewrite of their GUI (9.0.3 to 9.2 was significantly different). If those OWB verterans thought that was significant, prepare to be shocked.

The OWB10gR2 GUI will delight those that have not used OWB before as it has embraced some UI paradigms of other IDEs. Those familiar with JDeveloper, JBuilder, NetBeans, etc. will have a much easier time learning OWB. The OWB product team has accomplished this by making two significant UI changes:

• Smaller Fonts are pervasive through the product. : I dread having to return the OWB 10gR1 with it’s monstrous fonts that make displaying even the most simple of mappings difficult. This smaller fonts on dialogs, wizards, editors, and navigation are a great enhancement to the product. In the short list of features I would have submitted for the 10gR2 version of the product this would not have been on it. I am very thankful that I was not the one composing the shortlist; the smaller fonts make the entire product much more usable on the whole.
• Dockable Panels throughout : The editors and OWB navigator have created a much improved display to support the real work that occurs in OWB. They have created a set of screens that allow the focus to be on the primary work at hand (process flow, mapping, table definition, etc) but allow for the peripheral, oft utilized parts of the definition available in a fast refreshing set of panels surrounding the main event. One panel display the properties for the objects highlighted thus eliminating MANY MANY redudant clicks and scrolls, and highlights, and drills to the attribute. Another panel displays a palette of objects that can be added to the process or mapping. The panels are dockable (make them float), and collapsable (running out of space quickly expand and collapse them).

While most of the benefit of their UI efforts are captured in the two major changes there are other beneficial UI changes. Overall the OWB feels like it is quicker, and more attuned to the work at hand. Birds Eye Views of your edited object, automatic layout buttons all add a certain refinement and really lead me to believe that OWB is really maturing into a robust comprehensive product. Of course, it must be for us BI consultants, ETL developers, architects, DBAs to spend entire days with our noses plugged to the screen.

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