Category Archives: Oracle

Oracle ACE: In Absentia

Leave a reply

So… A few years back I spent a LOT of time with Oracle ETL and BI products. I learned them inside and out, gave some user conference presentations, wrote a bunch of blogs, even Alpha tested a version of Oracle Warehouse Builder. Then I found “Open Source BI” and I’ve been heading breakneck into the world of MySQL, Pentaho, … A choice I do NOT regret – my consultancy is busier than ever and I love the Open Source BI play.

However – I miss seeing some of the old Oracle peeps at Open World. This year, I even registered for my free ACE pass to OOW but didn’t make it because I started two new projects this week. What I realized this year, was that I’m WAY out of touch with what’s going on in the land of Big Red O. The words and products for BI whiz past me – they don’t even look anything like they did just a couple of years back.

I hope everyone had a good time at OOW this year! I don’t see a path back to the land of Oracle anytime soon for me. 🙁

New open source project: OWBScripts

Leave a reply

I hadn’t had a chance to post yet, but Mark made mention of it on his blog so I figure it’s about time to post about it.

OMB is the TCL based scripting language that comes with Oracle Warehouse Builder that allows you to do OWB “things” programatically (ie, without the GUI).  It is very useful for doing ETL generation, mass updates, deploying mappings, etc.  Basically, anything that you are doing repetitively is a good candidate for making into an OMB script.  OMB is a cure for “tennis elbow” from clicking hours on end in the OWB GUI.

I’ve released a handful of OMB scripts that I used on consulting gigs, presentations, articles, etc.  There is nothing spectacular here, but hey, they’re not doing me any good!  If just one or two people find them useful it was worth the time to slap the Apache 2.0 license and upload them to http://sourceforge.net.

The release (initial and only unless someone else out there wishes to take on the management/augementation) includes scripts to:

a) Generate base SOURCE to STAGING Truncate/Staging mappings and tables.
b) Generate base STAGING to WAREHOUSE Insert/Update mappings, tables, and sequences.
c) Install repository and the standard CIF targets (Staging, Warehouse, AreaMart).

Let me know what you think and I do hope someone, somewhere finds it useful!
PS – I haven’t used OWB for nearly 9 months.  For something I used day in day out for YEARS that’s a long time to have not even touched it!

Reporting in 60 seconds with Oracle XE

13 Replies

Ok, well maybe not 60 seconds but more like 5 minutes.

Some great news today from Pentaho.  Pentaho has purchased proprietary software for visually building reports and donated it to the open source community.  The full text of the announcement is here:

 More on this in a later post… Let’s get to the bits.

Much of what I’ve been hearing in the marketplace is the perception that Open Source has hidden costs in terms of usability and the technical savvy needed of users. 

Let us try and challenge this assumption and see how "productive" we can be building a simple database driven report.

Download the Report Designer here and unzip it to a directory of your choosing.  There’s zero installation required (you’ll need Java on your computer, but that’s pretty prevalent these days).  Double click on the bat file (or .sh if you’re using linux).

When the product launches you’ll notice a panel that allows you drag and drop elements onto the canvas in an easy to use fashion.  I’m sure additional tutorials will come on this blog and Pentaho.org but suffice to say, this is an interface which is easy enough to just "figure out" by playing with it.

The "RED" circle is our palette and the "BLUE" area is our canvas.

Let’s get going and start our report!

  1. Launch the Report Wizard
  2. Select the option that indicates that you will use your own dataset, a JDBC dataset
  3. Enter in the connection information and driver location for your database.  I keep the latest Oracle Express edition (a Free version of the Oracle database) around.
    Note:  You don’t have to do anything special with the JDBC driver (put it in a lib directory or anything).  All you have to do is show the wizard where the .jar file is.

    I’m using the HR schema which comes with the Oracle database for testing/sample purposes.
  4. Navigate to find the "EMPLOYEES" and "DEPARTMENT" tables.  Enter in a simple SQL query that contains the data of interest and use the very handy Preview Option to make sure you’re getting the results desired.

    Feel free to just copy and paste if you are using XE as well:
    select
    d.department_name "Department Name",
    e.first_name "First Name",
    e.last_name "Last Name",
    e.phone_number "Phone Number"
    from
    employees e, departments d
    order by d.department_name
  5. You can just click Next on the visible feilds screen:
  6. Highlight "Department Name" and add it to the grouping and hit "Finish"
  7. It should return you to the original canvas where you can see the design of your report.  From here you can start to add images, lines, change fonts, etc.  Have fun exploring the richness of the options available here.
  8. Let’s see what it looks like!  Click Preview and page through your report!

That’s it!  You’re done!  You’ve created a report that can be parameterized, hosted, bursted, distributed in just a few minutes.  In a follow on blog I’ll show you how to publish this into the Pentaho server (it’s only about 2 more steps).

Happy Trails!

OLAP Survey

Leave a reply

I noticed some other bloggers posted but I’ll encourage people as well. The more people that respond the more accurate the survey represents reality!
“We would very much welcome your participation in The OLAP Survey 6. This is the largest independent survey of business intelligence users worldwide. The Survey will obtain input from a large number of users to better understand their buying decisions, the implementation cycle and the business success achieved. Both business and technical respondents are welcome.

The OLAP Survey is strictly independent. While Oracle, Microsoft, Cognos and other vendors assist by inviting users to participate in the Survey, the vendors do not sponsor the survey, nor influence the questionnaire design or survey results. As a participant, you will not only have the opportunity to ensure your experiences are included in the analyses, but you will also receive a summary of the results from the full survey. You will also have a chance of winning one of ten $50 Amazon vouchers. Click here to complete the survey on-line.”

Mark Rittman on Oracle BI Suite EE

3 Replies

Mark Rittman has worked up an excellent article on the new Oracle BI Landscape. He basically covers the whole range of products and technologies and how they’re being repackaged and rebranded into a new product set. Great read.
I thought the following was of interest:

The Analytic Server is effectively a ROLAP (Relational OLAP) engine, that connects to data sources but does not have any local storage in the traditional, database sense, although it does have a cache which holds copies of data previously retrieved, so that subsequent requests for the same data are returned faster.

What struck me about this statement is that from a technology perspective this is nearly identical to Mondrian‘s (Open Source OLAP engine) architecture. While proprietary vendors (Oracle included) had been touting the benefits of a true MOLAP solution instead of just ROLAP. However, with the processing headed to mid tier servers (instead of OLAP crunching happening on a Pentium I desktop) one can leverage the mid tier for caching, and analytical processing (MOLAP-esque stuff). In other words, you can get MOST of the benefits of MOLAP with a well designed and performant ROLAP server proactively caching/crunching on your behalf! Cool stuff!

Oracle: We'll always have Paris

Leave a reply

Nothing like running down a good movie quote with techno-babble.

Some people have inquired if I’m heading to Pentaho because I dislike the Oracle tools; specifically if the significant delays (2+ yrs) on Paris have worn on me. I can emphatically state this is NOT the case! I’m moving to Pentaho because Open Source BI is REALLY REALLY cool not because I was unhappy with Oracle Business Intelligence technologies.

I think Paris is a significant improvement for Oracle customers and still believe it provides significant VALUE for customers. That’s one of the reasons I like Pentaho Data Integration (aka Kettle); it provides VALUE in different but very compelling ways.

So, Oracle Warehouse Builder remains a good value/choice in my book. I just think that Open Source BI/ETL/OLAP/DB shines in the “value” equation and I’m thrilled to help make that happen for real customers needing real solutions.

Lengthy Emailed Questions

1 Reply

Like other bloggers who post their email, I regularly get emails from people asking about topics which I blog about… I’m not really all that much of an expert on it all, just publish what I know. Usually it’s a brief:

Obscure question here: How can I do this little thing X with OWB because it’s not readily apparent in the product and the docs/forums/web searches haven’t yielded anything.

5 minute answer, I’ll email straight away if I know and I’m happy to help.

Ocassionally, people email with a whole BUNCH of question(s), that would, if answered properly, chew up a many hours of time. While I enjoy solving technology problems I do so in the context of charging people money to do it. I’d like to for free, but that would leave my paying customers behind schedule. So, when it becomes a sizable chunk of time I usually don’t answer. I’ve received three such emails in the past couple of weeks, and I figured what the heck… why not post them to the blog and see if the community as a whole wants to have a go at them. So… if you know the answer and feel like posting it please do!

Here goes:
EMAIL 1:

hello,

I am looking at upgrading our current out-dated Oracle 8.0.5 database to 10G (eventually),
but for now upgrading to 8.0.6 before the next phase. I want to be able to test the 8.0.6
database migration first, I have a few questions…

Can I upgrade a test database first for testing purposes, and once satisfied upgrade the production database ?
Is this just done by setting the correct Oracle_SID and running the upgrade process?
Do I have to run multiple listeners (8.0.5 and 8.0.6 )?
To upgrade each instance do I have to run the upgrade procedure for each instance?

Regards,

EMAIL 2

Hi Nicholas.

I’m a big fan of your blog and read it a lot for getting information regarding the BI. I have a few questions regarding OLAP cubes in Oracle and am hoping that you would be able to help out. We want to use MOLAP cubes in Oracle to run against historical data compromising more than 50M records. With 1M records our cubes (comprising of 6-8 dimensions) get refreshed in about 4 minutes with limited aggregation. Using incremental refresh the next 1M records take about 10 minutes while the next 1M takes around 30 minutes. This times keeps on increasing with additional refreshes. At this rate we fear that in a production environment with 50M+ records the incremental refresh time would be way too much. We want to do a one time build of the cube (with about 40M records) and then a weekly/monthly incremental refresh (of about 1M records).

We are using Oracle 10.1.0.4 on Windows 2000 on a HP Proliant DL380 (3.4GHz*2 and 6GB RAM). SGA Target is 890Mb and PGA Target is 370MB.

My questions are:

What is your opinion/experience about running OLAP cubes against high volume data (50M+ records). Please comment on both the cube maintenance time and the drill time at runtime in the reports. Do you know of any production systems that use cubes with this volume or higher? Are MOLAP cubes meant to operate against such a data volume or should we switch to ROLAP? Do you have any white paper that would help us in designing our cubes and configuring our hardware/oracle?

If we want to keep our daily incremental refresh time (for 1M records) for one cube to say 30 minutes then what do you think should be the minimum recommended machine specs for our environment? How many processors? How much RAM?

Thanks in advance.

EMAIL 3


Hi Nicholas,

My name is XXXX and I’m working as Product Analyst with XXXXXX Inc. XXXXXX is providing software solutions to XXXXX and XXXXXX industries.

I have been reading your blogs and I like them very much.

I have a real quick question. We want to develop a data warehousing solution that requires almost real-time warehousing. Is there a way we can do it with Oracle’s Asynchronous Change Data Capture and Oracle Warehouse Builder R1?

I’m not sure when OWB R2 will be out and how long it is going to take to stabilize. Is there a way to get hold of a beta version?

Your help is greatly appreciated.

Regards,
XXX

How about a Pete Finnigan Loop-du-jour

Leave a reply

Apparently there are two classes of bloggers: those in the grace of the big O and those that are not. I like the big O (what puts food on my table so to speak) but find it silly to exclude bloggers based on content. How many bloggers post total CRAP that appears to be well intentioned tuning advice that could end up TOTALLY messing up an Oracle solution? Is blogs.oracle.com going to peer review and ban these bloggers as well? It’s not that it isn’t a good idea to keep GOOD content on the official blog site, it’s just HOW are you going to decide what’s good?

Just like a recent “Best Blonde Joke Ever” loop, I suggest the remaining bloggers offer up a Loop-du-jour. It might get me banned as well, but oh well! 🙂

I’ll start off… I rather like Pete Finnigans web site. Check it out here!

ODTUG Desktop Conference

Leave a reply

I’m looking forward to popping my virtual head into a few of the ODTUG Conference 2006 sessions. In particular the following look to be well worth the registration fee:

Mark Rittman
SolStonePlus
Oracle XML Publisher-What’s It All About?
Oracle XML Publisher is the hot new Oracle BI and reporting tool that lets you build production-quality reports using a Microsoft Word add-in. This presentation looks at what XML Publisher does, how it works, how you use it, and whether it’s a replacement for Oracle Reports.

Irene Chen
Solbourne
DBI: The Good, Bad, and Ugly—Comparing a Successful to a Not-So-Successful Implementation
Daily Business Intelligence (DBI) is an integrated out-of-the-box reporting and analysis application that enables managers and executives to see relevant, accurate, and timely information using self-service dashboards. However, depending on how DBI is implemented, a company can get great results that help improve various aspects of its business. We will compare two DBI implementations, and discuss best practices for key portions of the implementation process. This session is intended for functional and technical users who are responsible for planning the DBI implementation and who need a more in depth understanding of Daily Business Intelligence reporting.

Rene De Vleeschauwer
Ikan Software
Common Warehouse Metamodel (CWM) Compliant, Multi-Dimensional Modeling and Oracle Analytical Workspaces (AW)
This presentation will explain how to model an Oracle AW application using multi-dimensional techniques which are OMG/CWM-compliant and using an MDA architecture to generate the analytical workspaces.

Nicholas Goodman
Bayon Technologies, Inc.
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.

Had to throw in the shameless plug for my own presentation!