2020ok  Directory of FREE Online Books and FREE eBooks

Free eBooks > Computers & Internet > Databases > Data Storage & Management > Data Warehousing > Oracle DBA Guide to Data Warehousing and Star Schemas

Oracle DBA Guide to Data Warehousing and Star Schemas

by Bert Scalzo

Download Book

If you are the author or the publisher, and would like to link to your site here, please contact us.

About Book

Book Description

This book addresses all aspects of constructing star schemas within Oracle data warehouses, from modeling and design through high-speed loads and lightning fast queries. The book delivers meaningful examples complemented by empirical samples and benchmarks, such that readers will learn more than just the mechanics. This book transforms readers into subject matter experts for dimensional modeling, star schemas and data warehousing in general for the Oracle database environment. This book is based on research conducted for the multi-terabyte data warehouse for the 7-Eleven Corporation. Star schema: a data warehouse design that enhances the performance of multidimensional queries on traditional relational databases. One fact table is surrounded by a series of related tables. Data is joined from one of the points to the center, providing a so called "star query." Previously announced in 2/2003 PTG catalog.

From the Back Cover

  • The definitive, real-world guide to Oracle data warehousing
  • Maximizing performance, flexibility, and manageability in production environments
  • Hardware/software architectures, star schema design, partitioning, and more
  • Industrial strength data loading and query optimization techniques
  • By the world-renowned architect of 7-Eleven's multi-terabyte datawarehouse
Maximize Oracle data warehouse performance, flexibility, and manageability

Oracle DBAs finally have a definitive guide to every aspect of designing, constructing, tuning, and maintaining star schema data warehouses with Oracle 8i and 9i. Bert Scalzo, one of the world's leading Oracle data warehousing experts, offers practical, hard-won lessons and breakthrough techniques for maximizing performance, flexibility, and manageability in any production environment. Coverage includes:

  • Data warehousing fundamentals for DBAs--including what a data warehouse isn't
  • Planning software architecture: business intelligence, user interfaces, Oracle versions, OS platforms, and more
  • Planning hardware architecture: CPUs, memory, disk space, and configuration
  • Radically different star schema design for radically improved performance
  • Tuning ad-hoc queries for lightning speed Industrial-strength data loading techniques
  • Aggregate tables: maximizing performance benefits, minimizing complexity tradeoffs
  • Improving manageability: The right ways to partition
  • Data warehouse administration: Backup/recovery, space and extent management, updates, patches, and more

About the Author

BERT SCALZO is a product architect for Quest Software. As a member ofthe TOAD development team, he designed many of the features in the TOADDBA module. Scalzo has presented numerous papers on data warehousing andled or served on data warehouse special interest groups at Oracle eventsthroughout the past five years. He has worked for both Oracle Educationand Oracle Consulting, holds several Oracle Masters, a Ph.D. in ComputerScience, an MBA, and several insurance industry designations. Scalzodesigned 7-Eleven's multi-terabyte, star-schema data warehouse. He isauthor of The TOAD Handbook.

Excerpt. © Reprinted by permission. All rights reserved.

I've written this book with the hope that it will serve as mylifetime technical contribution to my database administrator (DBA)brethren. It contains the sum knowledge and wisdom I've gathered thispast decade, both working on and speaking about data warehousing. Itdoes so purely from the DBA's perspective, solely for the DBA's needsand benefit.

While I've worked on many data warehousing projects, my three yearsat Electronic Data Systems (EDS) as the lead DBA for 7-ElevenCorporation's enterprise data warehouse provided my greatest learningexperience. 7-Eleven is a world leader in convenience retailing, withover 21,000 stores worldwide. The 7-Eleven enterprise data warehouse:

  • Is multi-terabyte in size, with tables having hundreds of millionsor billions of rows.
  • Is a true star schema design based on accurate business criteria andrequirements.
  • Has average and maximum report runtimes of seven minutes and fourhours, respectively.
  • Is operational 16X6 (i.e. the database is available 16 hours perday, 6 days per week).
  • Has base data and aggregations that are no more than 24 hours old(i.e., updated daily).

While the 7-Eleven enterprise data warehouse may sound impressive,it was not that way from Day One. We started with Oracle 7.2 and a smallHewlett–Packard (HP) K-class server. We felt like genuine explorers aswe charted new territory for both EDS and 7-Eleven. There were fewreference books or white papers at that time with any detailed datawarehousing techniques. Plus, there were few DBAs who had alreadysuccessfully built multi-terabyte data warehouses with whom to network.Fortunately, EDS and 7-Eleven recognized this fact and embraced thetruly iterative nature of data warehousing development.

Since you are reading this book, it's safe to assume we can agreethat data warehousing is radically different than traditional onlinetransaction processing (OLTP) applications. Whereas OLTP database andapplication development is generally well-defined and thus easy tocontrol via policies and procedures, data warehousing is more iterativeand experimental. You need the freedom, support, and longevity tointelligently experiment ad-infinitum. With few universal golden rulesto apply, often the method of finding what works best for a given datawarehouse is to:

  • Brainstorm for design or tuning ideas.
  • Add those ideas to a persistent list of ideas.
  • Try whichever ideas currently look promising.
  • Record a history of ideas attempted and their results.
  • Keep one good idea out of 10-20 tried per iteration.
  • Repeat the cycle with an ever growing list of new ideas

As Thomas Peters states, "Life is pretty simple: You do some stuff.Most fails. Some works. You do more of what works." That's some of thebest advice I can recommend for successfully building a data warehouseas well.


There are numerous data warehousing books out there, so why is thisone different? Simply put: its DBA focus on implementation details. Infact, the mission statement for this book is:

To serve as the DBA's definitive and detailed reference regardingthe successful design, construction, tuning, and maintenance of starschema data warehouses in Oracle 8i and 9i.

So how is this different from what's already out there? In general,I've found that most data warehousing books fall into one of threecategories:

  • Conceptual--Primarily educational about theories andpractices, with very high-level information
  • Overview--Catalogs of hardware, software, and databaseoptions, with few specific recommendations
  • Cookbook--Detailed, DBA-oriented advice for all the datawarehouse development lifecycle stages

Respectively, "best-of-breed" examples for these three categoriesare:

  • Data Warehouse Tool Kit: Practical Techniques for BuildingDimensional Data Warehouses by Ralph Kimball
  • Oracle8 Data Warehousing by Gary Dodge and Tim Gorman

This book, primarily since no other book exists with this kind ofdetailed DBA advice

I mean no disrespect to these other categories or their books. Ihighly recommend Kimball's book to anyone new to data warehousing. Anduntil such time as this books debuts, I also highly recommend Dodge'sbook for DBAs.


This book is intended for physical DBAs--period, end of story. Thisbook assumes an extensive and detailed working knowledge of Oracletechnologies. Moreover, it presumes a keen awareness of hardware andsoftware options--often a skill possessed only by DBAs who also serve asat least the backup operating system (OS) administrator as well. Thatsaid, there are chapters that will be both applicable and beneficial toother members of the data warehousing team.

The sections on data modeling define how a DBA should interpret andextrapolate an entity relationship diagram (ERD) into a physicaldatabase design. So, this chapter would assist data modelers andapplication architects to understand how a DBA uses their input tocreate the underlying database structure.

Likewise, the sections on staging, promoting, and aggregating datadefine how a DBA should manage objects and processes to mostexpeditiously load massive amounts of data. So, this chapter would beboth educational and inspirational to extract, transform, and load (ETL)programmers tasked with loading a data warehouse.

And finally, the chapter on querying the data defines the indices,statistics, and plans necessary to deliver the best possible ad-hocquery runtimes. So, this chapter would assist business intelligencefront-end designers, who can appreciate how the database handles theircomplex, ad-hoc queries.


very good book


PLEASE READ: All comments must be approved before appearing in the thread; time and space constraints prevent all comments from appearing. We will only approve comments that are directly related to the article, use appropriate language and are not attacking the comments of others.

Message (please, no HTML tags. Web addresses will be hyperlinked):

Related Free eBooks

Related Tags

DIGG This story   Save To Google   Save To Windows Live   Save To Del.icio.us   diigo it   Save To blinklist
Save To Furl   Save To Yahoo! My Web 2.0   Save To Blogmarks   Save To Shadows   Save To stumbleupon   Save To Reddit