Category Archives: Oracle

OWB Trace Data : ROWKEYs

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.

OWB Administration and Operations Training

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.

JDeveloper is "free"; hoooey!

There have been some posts about this recently, and there’s an official FAQ on OTN. Most Oracle employees are quite excited about the fact that JDeveloper is now “free” and tons of developers will jump on board to really put a lot of momentum behind the tool. The slight of hand that occurred (in the same breath I might add) is that Oracle has completely decimated the biggest selling point of JDeveloper: free runtime license for ADF and choice of Application Server/Database!

Yesterday you could pay Oracle the modest 1k for JDeveloper seats and you got “productivity with choice.” You were paying for features for productive development and this included ADF since that is how JDeveloper delivers most of it’s “productivity.”

Today you get JDeveloper for free and you get “productivity with hooks.” The “productivity” of JDeveloper (heavily based on ADF) is now runtime licensed. In essence, the FREE JDeveloper license is worth far less than the 1k developer seat since ADF now must be licensed in production.

I don’t use JDeveloper for projects; I’ve only fired it up occasionally to measure progress and to check in with my old hat (I used to be a Java developer). So, I suppose I’m not really qualified to rant but I find this a disturbing change that Oracle feels free to change it’s licensing at will after organizations have chosen it with it’s licensing in mind. I understand you can actually “choose” to deploy ADF to other J2EE/DB but now that there’s a runtime license for ADF you might as well run Oracle AS/DB. I see the business wisdom in this new “loss-lead” but it’s crap, I say. What happend to paying for what you want and getting fully functional products?

Perhaps this will be more salient to bayon blog readers: What if Oracle were to license the OWB runtime engine, out of the blue? You can have the OWB seat but now you have to pay per CPU to “run” the code. If someone had chosen OWB over Informatica because of licensing issues what a blow for their investment!

Perhaps those at Oracle can change my mind, and help me understand why JDeveloper customers are not actually getting the shaft in the guise of “a free lunch.” I don’t get it either, no one else has pointed this out yet. Am I way off base here? Send an email through to me to tell me if I’m bonkers.

Open Source BI – I like Pentaho

Business Intelligence software, databases, and their supporting hardware are expensive. I mean really, really expensive (hundreds of thousands to millions of dollars). Many people working in the Business Intelligence/Data Warehousing fields have seen their “operational application” colleagues adopting open source solutions (Linux, JBoss, Eclipse, Apache, etc.) but have seen little attention paid to the software required to build and deliver Business Intelligence. That is beginning to change.

I’ve blogged about this before, specifically my experiences with downloading and testing Mondrian, an open source ROLAP server written in Java. It appears as if there is some gaining momentum and maturity of projects suitable for BI in the Open Source(OS) world. I’ve felt for some time that the open source community had not embraced BI in quite the same way they have other applications of technology. It is, in earnest, a technology stack to make bigger companies bigger and smart companies smarter. While these precepts aren’t in opposition of open source ideals, they aren’t what typically motivates communities of developers to band together to make software for free (ie, change the world, provide a framework used by 10,000 websites, etc.).

The state of open source BI was relatively slim not too long ago. There were a variety of possible toll sets one could use for ETL (Clover, Enhydra Octopus), some initial OLAP components (Mondrian, JPivot), some portal frameworks for dashboards (JetSpeed, JBoss Portal), and some databases with maturity for DW situations with smaller volumes (MySQL, Postgres). Things have been heating up this past year, and we should review whats going on in the Open Source BI realm. The lead is buried, make sure you check out Pentaho at the bottom.

CA’s Open Source release of Ingres
Albeit a funny OSI approved license (there are many provisions which will scare away the OS purists, and make others at least think twice about including it in their products or service) Ingres is officially open source and free. Ingres has some pretty significant “enterprise” features including replication, partitioning, and “in the works” linux clustering (a la RAC). This is great news because Ingres is a rather mature database and is better suited for large DW volumes than MySQL and PostGres. It is noticeably (and perhaps critically) lacking the vibrant community required to increase uptake. At this point it feels like CA is still the only one “interested” in Ingres. This might change, but I believe the funny CATOSL has hindered acceptance from open source communities.

Netezza/DATAllegro are using open source
These two providers of DW appliances are using open source databases as part of their solution. It’s a mixed technology stack, which means that unless you purchase the appliances you will benefit from none of the work that these two companies have put into their implementations. One uses Postgres, the other uses Ingres. There must be quite a bit of technology surrounding it to make it actually work for corporate DW environments. Netezza is actually doing rather well I believe, and some of the bigger vendors are starting to “see them on the radar” as a player in the space.

GreenPlum (aka Metapa) takes another shot
When Metapa wasn’t getting the traction with marketing their inexpensive proprietary Clustered DB implementation they figured they needed something to get more traction. Open Source is powerful enough that even a few years into the hype it still attracts attention. They relaunched themselves as an Open Source solution and are sponsoring the BizGres project (a few extensions to PostGres that are useful for BI environments) along with allowing the single instance version of their product to be used for free. I don’t think they’ll get the OS community embrace they desire because people are discerning these days; the only interesting work GreenPlum is doing is related to their MPP and shared nothing clustering technology which is very much NOT open source. I don’t think they’ll get the OS thrust they expected, because they are only opening their kimono an inch, not even a halfway mark.

Mondrian/JPivot releases
These two projects underwent new releases this year that provided the most visible part of an open source DW/BI system their legs. While not comparable to commercial OLAP interfaces they are certainly suited for ISV/Developers to embed in their application. These are great components for including in a project, and if your report consumers don’t really care to write their own reports (a la graphical report builder) and just want to pivot and page this could be an excellent, inexpensive solution.

BIRT and JasperReports are actually pretty good
Two commercially backed (one by Actuate, the other by JasperSoft) projects that are building the basis for business quality reports. Don’t turn off your Crystal installation yet because these both have a way to go, but they’re improving at a steady pace.

Pentaho Nation
This is truly the most exciting thing I’ve found in the Open Source BI space, and they’ve just begun their work so I’m running on faith at this point. Industry veterans who are passionate about BI and open source have pooled their minds and money (they’ve made $$ from previous entrepreneurial activities) to build a pure, 100% open source distribution for BI. They are collecting various open source projects, building their own components and releasing the whole thing as open source. A partial list of the projects they are planning (no official distro yet): Mondrian OLAP server, JPivot, Firebird RDBMS, Enhrydra ETL, Shark and JaWE, JBoss, Hibernate, JBoss Portal, Weka Data Mining, Eclipse, BIRT, JOSSO, Mozilla Rhino.
The company will follow in RedHat footsteps and make money on support, training, and consulting. Their plans are ambitious, but they are focused on assembling and configuring all these disparate projects into a comprehensive platform that will be at least comparable to the “big boys” at Hyperion, Cognos, Microstrategy, etc.


They are engaging the community, clearly understand the need in the space, and are committed to the ideals of getting paid for solutions instead of software. They are certainly strong in the presentation, dashboard, BPM/workflow, OLAP end of the spectrum but don’t appear to be including much in the ETL/DW end (there is some, but it appears to be for data movement and loading as opposed to building a DW). I’m not sure if it’s strategic or not, but it might makes sense. Most people adopting an open source BI platform for their reporting users will feel comfortable rolling their own ETL/DW for the backroom. It should also be noted that they haven’t made any releases yet, so what we’re seeing is all conceptual now but they’ll be rolling something out sometime in 2005. It appears as if the founders have a track record of “doing what they say they’ll do.”

What does this all mean?
There are three things that will happen as the Open Source and BI worlds start dating.

  1. Hardly anything for your current BI project and technologies. It is still emerging and is just now being utilized by early adopters.
  2. Cost pressure on the “big boys” will occur as the maturity of these components provide at least comparable options. Currently the small number of vendors along with their constantly increasing prices will show up as an area to be trimmed (ironic enough probably in a financial report provided inside the software in question). I don’t believe that it will have a significant impact, but will have a small impact over the next 3-5 years. It will also affect prices of BI OEM and inclusion of BI capabilities in vertical applications (more BI in existing products).
  3. Increased adoption of BI at small and mid sized business who can now afford to enter into the BI space. Previously inhibited by the exorbitant software costs business can now spend a few thousand dollars to start their foray into BI.

HTMLDB HOSTING REVIEW

Most people don’t need to read this review as they host HTMLDB at their own location with their own Oracle DB, operations staff, etc. I would venture to say that most applications of the technology, which runs on the Oracle database, are meant for data centric internal application building and also extranet applications. It would be incorrect to suggest that there are a plethora of consumer websites built with the technology.

There are some circumstances where hosting of an HTMLDB application is beneficial. For security, cost, or as a 3rd party HTMLDB developer, having a hosted HTMLDB for production purposes can be a good solution. A company could build an application as an extranet applicaiton, and rather than expose their own machines to the internet one could host the application at a service provider, and use database links to view the remote database as it were part of their local Oracle installation. Perhaps someone develops an extremely simple application using HTMLDB, and wants to provide it to customers as a service, but doesn’t want to get into the hosting business. Or consider furhter, a very simple applicaiton, investing in seperate machines for Apache and Oracle (RedHat/Windows) could be more expensive than required when someone else can do the whole thing for $49.95/mo.

That’s just what the folks at revion.com have done. $49.95/mo for an HTMLDB account that you can use for production purposes (remember, the HTMLDB environment at OTN can only be used for development!). I found their service excellent, the hosting reliable (admittedly I only used their service for a couple of months). They were using the newest HTMLDB version, provided access via SQL*Net (big plus for me). Overall, I was very pleased with their service, including my cancellation. Some hosts are downright difficult in cancelling but Revion was graceful, thankful, and inviting for any future HTMLDB hosting needs.

Based on my (limited) experience with them, I’d recommend them. They are much less expensive than their competitors (does not allow SQL*Net access either) so I think they are a bargain. Check them out if you require some “external” HTMLDB hosting!

OWB PARIS CLICK, CLICK, CLICK

For those of you using Oracle Warehouse Builder currently you are aware of the involved nature of installing the product and getting to a point you can do “real ETL.” It was multiple phases (product/design rep/runtime rep/target schema) and multiple steps in each. Once you’ve done it, and know what you’re doing one can do it in a few hours if you’ve already planned out your release. If you’re new, or aren’t quite familiar with OWB it can take days or even weeks.

I downloaded the latest beta release to install today, and I’m still amazed at how much the new version has improved this process! Download, Install Wizard(3 steps), Getting Started Wizard(3 steps), BAMMM! You’re ready to start building your data warehouse (provided of course, that you have experience in ETL and data warehousing).

Kudos to the OWB product team for simplifying this process!

ORACLE WAREHOUSE BUILDER : WORKSHOP II

UPDATE : The next workshop will be held end of May! Classes are limited to 8 seats (very hands on) Email me to get registered…

Many of you know that I’ve been developing Workshop II, the follow on workshop to OWB : Workshop I. As always, it’s an intensive hands on workshop ensuring you come away with both SKILLS and knowledge. If any blog readers are in the greater Seattle area (or have always needed an excuse for a weekend here) consider taking this course. Email me directly if you’re interested, and I can help get you registered for the workshop
ORACLE WAREHOUSE BUILDER : WORKSHOP II

This course builds upon the basic skills in Workshop I, and students leave with enough knowledge to begin building their warehouse. Users progress beyond basic mappings and learn additional DML techniques. Additional ETL skills are developed and students learn how to use Set Operators, Custom PL/SQL, and leave with most of the logical operators to build their warehouse. OWB Execution is explored and students learn how to build Process Flows to sequence and monitor the execution of their ETL Mappings. Students learn how to deliver web interfaces to display their Runtime (DBAs) and Design (End Users) data. Students learn techniques for scheduling and executing OWB from SQLPlus (EM).

Course Objectives
After completing the course, students should:

  • Be able to build process flows, ensure that errors are properly detected and managed
  • Be able to deliver the Web Browser based applications (Runtime Audit Browser for DBAs, and Design Browser for End Users)
  • Understand and be able to Import and Export data from OWB (OWB Metdata and Discoverer Integration)
  • Understand the applicability of, and know how to use the following ETL operators (Splitter, SetOp, View, Transformations, Expressions)
  • Be able to perform common DML processing (Trunc/Ins, Update, Delete, Merge) in mappings
  • Understand how to, and be able to deploy custom procedures and
    functions
  • Know the composition and the applicability of the OWB provided
    transformations
  • Be able to create views, and materialized views within OWB
  • Know how to execute and monitor OWB mappings from SQL Plus/Enterprise Manager

Text and Materials
All materials are supplied to students at the beginning of each class.

Class Format
Classes consist of approximately 1.5 hours of lecture supported by slides and whiteboarding. The balance of the day, approximately 6.5 hours, is hands-on. Each students works on their own Linux server.

Prerequisites
To be successful students must be comfortable with basic Oracle skills. The following is a short list of the suggested but not required skills:

  • Be able to accomplish all the objectives in Workshop I. Attendance at Workshop I is not required, but suggested.
  • Understand what schemas are, how they relate to users
  • Understand basic DDL (create table, alter table, create sequence, create or replace package) and DML (select, insert, update)
  • Know how to connect to Oracle using both Localnames (tnsnames.ora) and JDBC (host/port/SID)
  • Students will benefit most when they have a basic understanding of dimensional modeling (Cubes/Facts/StarSchemas)

Nice URLs for HTMLDB? Fishing for help!

I think HTMLDB is quite a gem in the Oracle Database offering. It is a great data centric GUI building tool; if you need a quick GUI for an Oracle based application consider HTMLDB. I got on to HTMLDB when I used it to build an administrative tool for an OWB Data Warehouse for a large publisher in Boston. I did so because it was free and easy, but quickly became addicted!

Asktom runs on HTMLDB; obviously it scales! So, assuming you don’t mind vendor lock in (will only run on Oracle) for your database, HTMLDB can be used as a GUI for custom applications, intranet applications, and extranet applications. Rapid, productive, and FREE with the database! Excellent! It’s powerful enough to even build publicly accessible websites, like AskTom…

That’s where I’m getting into a pickle! Publicly accessible sites have some additional requirements. Scalability, check. Professional looking templates, check. URLs acceptable for bookmarking and search engine ranking, big screeching crashing halt!

For those unfamiliar with search engine indexing in general, here’s the summary:
Search Engines generally rank lower pages with identical content, if their content is determined by parameters to the right of the “?” In other words (and entirely without proof) the following AskTom page on “Fast Wild Card Searching” (http://asktom.oracle.com)

/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:37336026927381
would be ranked higher for some google searches if it’s URL was more like this:
/pls/ask/4950/8/fast_wc_srchs.html?p=::sess:NO

There’s an entire industry built around how to ensure that your valuable content is mated with people searching for it. It would be tough to sell a company on using a solution that will almost certainly decrease their ability to reach customers (google users) with the content they put into their application.

I’ve used mod_rewrite before on simple point solutions to help make a site google friendly. However there is some significant work to be done(maps of page IDs to page names, maps of application IDs to names, complicated regex, etc) to create a mod_rewrite solution to make HTML DB urls easier on the eyes, bookmarks, and better indexed by search engines.

Anyone facing similar issues? Anyone have any prior art they wouldn’t mind sharing with the community? Please email me (boy, I really need to get my comments working on my blog) and I’ll be happy to post any information on it here.