Oracle 9iR2 Data Warehousing

Oracle 9iR2 Data WarehousingAbout a year an a half ago I was right in the middle of researching data warehouse technologies and starting to get quite discouraged on the lack of good technical books on the topic. Then I came across the book “Oracle9iR2 Data Warehousing” by Hobbs, Hillson and Lawande. Here’s the review I put on Amazon on this deep technical book.

In this book, the authors take you swiftly and thoroughly through the entire process of creating a data warehouse. Several other books (I purchased 4 others before this) over-generalize the topic, teaching the vocabulary and business reasons for data warehousing. This book teaches how to implement a warehouse in Oracle 9i Revision 2, while teaching the major concepts through practical application.

It would be easy to get bogged down in the technical details of this book if one were not familiar with the Oracle environment. Those who are familiar with Oracle will find it is much like the courses offered by Oracle. The book consistently, clearly presents the concepts (dimensions, fact tables, summaries, ETL) then delves into such depth it leaves the reader with a complete understanding of not only how to implement each concept, but when, and why to implement them.

The major concepts covered include dimensional modeling, data partitioning, query optimization, materialized views, dimensions, the extract-transform-load process, warehousing tools, ongoing warehouse maintenance, and many more. Furthermore, SQL for the examples used in the book are available from one of the authors websites, affording the reader a hands-on environment in which to observe these concepts.

Overall, I would recommend this book to anyone looking to work with warehousing who already has a firm Oracle background (strong knowledge of schemas, data dictionary, storage conventions, terminology.) This is simply the best book I have found on data warehousing.

I see that there is now a new version of this book out. “Oracle Database 10g Data Warehousing” by the same authors, plus Pete Smith, will go on my short list for tech books to buy.

oracle, data warehouse, database, dba, database administration, data warehousing

Top Oracle Blogs

OracleIn no particular order, here are some of the other folks blogging good stuff about Oracle. Most are more up on current topics than I am since I mostly write how-to stuff so I can find it when I need it. – Mark Rittman’s Oracle Weblog is a fantastic resource with a focus on data warehousing and business intelligence. One of the best sites by an individual. – Pete Finnigan has a lot of security related info. A great resource! – While Ask Tom is not really a blog, there is a wealth of information here on most Oracle topics. – Doug has a bit more casual site and, like me, talks about tech and non-tech topics. – Not a frequent poster, but some great nuts-and-bolts SQL and PL/SQL stuff. – Another blog with a potpouri of Oracle topics. – The Tom Kyte Blog. Tom works for Oracle (see above) and some of his Oracle stuff and peripheral interests and opinions spill over into here.

More are out there. Feel free to comment with your favorites and also check out some of my favorite Oracle web resources

oracle, dba, database, database administrator, rdbms, database security, data warehouse

Oracle Change Tables – An Example

Here is an example of how to create and use Oracle Change tables. This is part of what Oracle refers to as “change data capture” or CDC.

Oracle change tables allow you to capture what has changed in a specific table over a period of time. This can be useful if you are attempting to keep data updated with a source. Typically CDC is used as part of the “extract” process in an extract-transform-load procedure for loading a data warehouse.

There are many sties and articles on this topic that go into much more depth than I will here, however I have never found a fully functional demonstration of an entire CDC cycle.

Change table example code

For more information on change data capture I recommend Oracle’s Data Warehousing Guide. More on data warehousing in general can be found at Mark Rittman’s Oracle Weblog.

A new feature in Oracle 10g called ‘streams’ offers an alternative to this type of CDC, however I (and I’m sure many others) are still dealing with change tables on a regular basis.

Technorati tags: , , , , , ,