“Oracle 10g Data Warehousing”
May 28th, 2005 by Mark Rittman
I’ve
been meaning to post a plug for this one for a while.
"Oracle 10g Data Warehousing" by Susan Hillson, Shilpa Lawande, Pete Smith
and Lilian Hobbs came out a while ago and is the update to the 9iR2 Data
Warehousing book that I talked about on my Books page. Although the 9iR2 book
was good, I thought it was a bit thin and didn’t really cover much that wasn’t
already in the manuals, however the 10g book is far more substantial and covers
a lot of ground that you don’t normally come across when going through the OWB,
Discoverer and Oracle OLAP documentation. Like the 9iR2 book it covers all of
the products that make up the 10g BI&W product stack, but what’s good about this
version is the degree to which it covers some of the stuff that you tend to
gloss over when working with the tools – stuff like taking backups, using
Enterprise Manager, using the new 10g features such as ASM – and as well has a
big section on using the new analytic and aggregating functions (cube, grouping
sets, rollup) that you get with 9i and 10g.
I’m usually fairly sceptical about printed books as there’s not much you
can’t already get on docs.oracle.com and asktom.oracle.com; however in practice
we’ve found this book invaluable and it’s become standard issue on a number of
big 10g data warehousing projects that we’re working on. The only criticism I
would say is that it only covers the first 10g release of the various tools -
Discoverer, OWB etc – and doesn’t therefore have anything on Discoverer "Drake"
or of course OWB "Paris". Also, like every Oracle BI&W book, the coverage of
Oracle OLAP is fairly sketchy, with nothing on the new 10.1.0.3 or 10.1.0.4
features, but of course that therefore leaves things open for my forthcoming
magnum opus :-)
Funnily enough I was one of the technical reviewers for this book, as I
stepped in at the last minute to tech review a couple of Pete Smith’s chapters.
Pete and I happened to be working together at the same client (for different
companies), with Pete as the DBA (dropping and recreating tables) and me as the
OWB developer (trying to keep OWB going with all my tables appearing and
disappearing). One thing that struck me at the time was the amount of work that
goes into writing a book – a typical blog article is for example an afternoon’s
work, say a day with the research and putting together articles – whilst a book
chapter, at around 20-30 pages, and where you have to get everything just
right, is a lot more effort. The other issue around book writing is the fact
that the technical content has to be completely correct – I’ve experienced this
now with writing for OTN, where articles are technically reviewed by the Oracle
product team – and this review and revision process, whilst ensuring that
content is absolutely spot on, certainly takes up a lot of time – as much time
actually as the time it took to write the article in the first place.
Anyway, I’d thoroughly recommend the book, an excellent grounding in the
technology and processes around building a 10g data warehouse, and an excellent
introduction to 10g new features for those DW developers and DBAs up until now
working with 8i and 9i.
May 31st, 2005 at 5:41 pm
I browsed it at the bookstore, so my comments cannot be construed as encompassing. However, I looked at the ETL section–I believe they called it “Loading the Oracle Database”–and I was disturbed at the techniques they discussed. It discussed transportable tablespaces as a method to load a warehouse. I cannot understand why some one would use this technique… unless the model of the warehouse is indentical to the model of one source system, which isn’t warehousing at all. Perhaps it can be used as a means to bring the data from the source system over to the staging area, but with all the high speed ETL-as-you-go options out there, it seems like a useless step to me. I would love to hear from others who have found it a meaningful step in the process.
The real issue I found unforgiveable was there was no mention of using partition-exchanging to load fact tables. This is too large an oversight… it is *THE* Oracle best-practise for loading a highly-available warehouse.
If some one was a more comprehensive review, please let me know, because the subject matter of Oracle warehousing is in need of a best practises manual… something as essential to our bookshelves as Tom Kyte and Ralph Kimball.
May 31st, 2005 at 9:06 pm
Stewart, I find the use of transportable tablespaces for dataload somewhat contrived; they only seem to have utility in circumstances where there is a single data source and it is running Oracle. This might be OK for datamart loading but for multi-data source or otherwise heterogeneous systems I am not convinced. As you say, there are many ETL alternatives out there that would give speed and flexibility.
Partition exchange loading in data warehouses is relatively new; I am of the opinion that it would only be found in use in the more recently developed DW systems (say no older than 18 months old) and then only that small subset whose partitioning scheme can be aligned to the dataload process. At the time the book was written partition exchange was a minority interest and little by way of real-life examples existed.
Mark, Sorry if I am hijacking your blog… Pete
May 31st, 2005 at 11:23 pm
As a consultant, I move from project to project quite a bit, and in the last year, partition-exchanging is either being used or on the radar. When working with tools such as Informatica, whose only bulk loading option is SQL-Loader, it is a necessity. Plus, with the rising necessity of bitmap indexes (especially for Star Transformation), it seems to me to be a requirement, not a “nice-to-have”.
This is a book on 10g Warehousing, so the fact that partition-exchanging is not a staple yet is a bit lost on me, I must admit. Every client I’ve interviewed with in the last year has asked about my experience with it.
May 31st, 2005 at 11:27 pm
Also… I’ve seen a lot of warehouses where partition-exchanging is being used… but zero where transportable tablespaces are being used. Once again, I only browsed the book, but it seemed a thinly-veiled rehashing of the Oracle Data Warehousing manual to me.
Just my two cents. I’m hoping some one who has looked at the book more extensively can demonstrate that my first impression is flawed, because I really think this space *NEEDS* an up-to-date bible.
June 4th, 2005 at 1:17 pm
Stewart,
thanks for the comments (and Pete as well). As far as I remember (my copy is back in the office at the moment) the book does carry quite a big sections (several pages at least) on Partition Exchange, in fact I seem to remember this was part of the section I tech reviewed at the time (Peter Smith wrote this section). I remember at the time I questioned whether it should in fact be called “Partition Exchange Loading” (as it’s referred to in the OWB documentation) or “Partition Exchange” as Pete called it. Turns out Pete was correct as this is the official Oracle term.
Having said all this, yes the book is more or less an “enhanced” version of the docs – it tells you what you could do, and every variation of what you could do, but it doesn’t offer an opinion or tell you what works and what doesn’t work. By the nature of the authors (all Oracle Corp staff) it’s not going to be controversial (as with say the Bert Scalzo book) or warn you off features that are less than effective. Having said that, many people look to get beginners/belt-and-braces books like this, and as far as that market goes, it’s pretty good. Like you though I’d look forward to reading something that offered best practices, viewpoints on the various Oracle BIW technologies, something for experienced users rather than just beginners. Doesn’t diminish this book though which I guess serves a different market.