Robert StackowiakJoseph RaymanRick Greenwald

Oracle®

Data Warehousing and

Business Intelligence Solutions

01_919216 ffirs.qxp 11/21/06 10:28 PM Page iii

File AttachmentC1.jpg

01_919216 ffirs.qxp 11/21/06 10:28 PM Page ii

Oracle®

Data Warehousing and

Business Intelligence Solutions

01_919216 ffirs.qxp 11/21/06 10:28 PM Page i

01_919216 ffirs.qxp 11/21/06 10:28 PM Page ii

Robert StackowiakJoseph RaymanRick Greenwald

Oracle®

Data Warehousing and

Business Intelligence Solutions

01_919216 ffirs.qxp 11/21/06 10:28 PM Page iii

Oracle® Data Warehousing and Business Intelligence SolutionsPublished byWiley Publishing, Inc.10475 Crosspoint BoulevardIndianapolis, IN 46256www.wiley.com

Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-471-91921-6ISBN-10: 0-471-91921-7

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1MA/RS/RS/QW/IN

No part of this publication may be reproduced, stored in a retrieval system or transmittedin any form or by any means, electronic, mechanical, photocopying, recording, scanning orotherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copy-right Act, without either the prior written permission of the Publisher, or authorizationthrough payment of the appropriate per-copy fee to the Copyright Clearance Center, 222Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to thePublisher for permission should be addressed to the Legal Department, Wiley Publishing,Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, oronline at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no repre-sentations or warranties with respect to the accuracy or completeness of the contents of thiswork and specifically disclaim all warranties, including without limitation warranties of fit-ness for a particular purpose. No warranty may be created or extended by sales or promo-tional materials. The advice and strategies contained herein may not be suitable for everysituation. This work is sold with the understanding that the publisher is not engaged in ren-dering legal, accounting, or other professional services. If professional assistance isrequired, the services of a competent professional person should be sought. Neither thepublisher nor the author shall be liable for damages arising herefrom. The fact that an orga-nization or Website is referred to in this work as a citation and/or a potential source of fur-ther information does not mean that the author or the publisher endorses the informationthe organization or Website may provide or recommendations it may make. Further, read-ers should be aware that Internet Websites listed in this work may have changed or disap-peared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support,please contact our Customer Care Department within the U.S. at (800) 762-2974, outside theU.S. at (317) 572-3993 or fax (317) 572-4002.

Library of Congress Cataloging-in-Publication Data: Available from publisher.

Trademarks: Wiley, the Wiley logo, and related trade dress are trademarks or registeredtrademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and othercountries, and may not be used without written permission. Oracle is a registered trademarkof Oracle Corporation. All other trademarks are the property of their respective owners.Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appearsin print may not be available in electronic books.

01_919216 ffirs.qxp 11/21/06 10:28 PM Page iv

www.wiley.com

Robert Stackowiak is Vice President of Business Intelligence in Oracle’sTechnology Business Unit. He has worked for over 20 years in businessintelligence, data warehousing, and IT-related roles at Oracle, IBM, HarrisCorporation, and the U.S. Army Corps of Engineers. His papers regardingbusiness intelligence and computer and software technology have appearedin publications such as President & CEO Magazine, Database Trends andApplications, and The Data Warehousing Institute’s publications. He alsoco-authored the books Oracle Essentials: Oracle Database 10g (currently in3rd Edition, February 2004, O’Reilly), Oracle Application Server 10g Essentials(1st Edition, August 2004, O’Reilly), and Professional Oracle Programming(1st Edition, June 2005, WROX).

Joseph Rayman leads the Oracle Consulting Business Intelligence Practicein North America with over 20 years of business experience in a vast arrayof industries, including financial services, manufacturing, retail, telecom-munications, healthcare, and federal government. His technical and busi-ness leadership spans enterprise architecture design, enterprise datamodeling, VLDB system tuning, data warehouse design, data mining, andquality assurance activities for data warehouse practices. Joe is a key con-tributor in defining and authoring Oracle Consulting’s Data WarehousingMethodology. Prior to joining Oracle, Joe designed and deployed businessintelligence and statistical analysis solutions for a large food manufacturerand provided real-time trading and analysis solutions for a major interna-tional financial organization.

About the Authors

v

01_919216 ffirs.qxp 11/21/06 10:28 PM Page v

Rick Greenwald has worked in the IT field for over 20 years for major ven-dors, including Oracle, Gupta Technologies, Cognos, and Data General. Hehas coauthored more than a dozen books, including Oracle Essentials: OracleDatabase 10g (currently in 3rd Edition, February 2004, O’Reilly), OracleApplication Server 10g Essentials (1st Edition, August 2004, O’Reilly), andProfessional Oracle Programming (1st Edition, June 2005, WROX). Mr. Green-wald currently works for Ingres Corporation.

vi About the Authors

01_919216 ffirs.qxp 11/21/06 10:28 PM Page vi

Executive EditorRobert Elliott

Senior Development EditorTom Dinse

Production EditorAngela Smith

Copy EditorMichael Koch

Editorial ManagerMary Beth Wakefield

Production ManagerTim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Project CoordinatorAdrienne Martinez

Graphics and Production Specialists

Carrie A. FosterStephanie D. JumperBarbara MooreHeather RyanAlicia B. South

Quality Control TechniciansJessica KramerBrian H. Walls

Book ProofreadingTechbooks

IndexingStephen Ingle

Anniversary Logo DesignRichard Pacifico

Credits

vii

01_919216 ffirs.qxp 11/21/06 10:28 PM Page vii

01_919216 ffirs.qxp 11/21/06 10:28 PM Page viii

Acknowledgments xv

Introduction xvii

Part I Oracle Business Intelligence Defined 1

Chapter 1 Oracle Business Intelligence 3Business Intelligence and Transactional Applications 4

Daily Business Intelligence 5Balanced Scorecard 6Enterprise Planning and Budgeting 8Activity-Based Management 9

Oracle Integration Components Enabling Business Intelligence 9Data Hubs 10Business Activity Monitoring 10BPEL Process Manager 11Enterprise Messaging Service 11

Custom Data Warehouse Solutions 12The Role of the Oracle Database 14Oracle Warehouse Builder 15Oracle Business Intelligence Standard Edition 16Oracle Business Intelligence Enterprise Edition 18BI (XML) Publisher 20Oracle Portal 20Spreadsheet Add-ins 21Building Custom Business Intelligence Applications 23

Emerging Trends 24

Contents

ix

02_919216 ftoc.qxp 11/21/06 10:28 PM Page ix

Chapter 2 Oracle’s Transactional Business Intelligence 27Transactional Business Intelligence 28

Business Terminology 28Oracle’s Daily Business Intelligence 30

How DBI Works 33Varieties of DBI 34

Balanced Scorecards 45Oracle Balanced Scorecard Structure 46OBSC Architecture 48Creating an Oracle Balanced Scorecard 48

Data Hubs 49The Oracle Customer Data Hub 49How Data Hubs Work 51Other Oracle Data Hubs 51

Is Transactional Business Intelligence Enough? 52

Chapter 3 Introduction to Oracle Data Warehousing 53Oracle Data Warehousing Basics 54

Oracle Database Analysis and Schema Considerations 55Managing an Oracle-based Data Warehouse 64

Where to Start? 70Oracle/PeopleSoft EPM 71Oracle/Siebel Business Analytics Applications 73Choosing Completely Custom 74

Chapter 4 Choosing a Platform 75Scaling Up Platforms Versus Scaling Out 76

Hardware Platforms 77Cost Considerations 80Availability Considerations 80Manageability Considerations 83

Sizing the Platform 84Information Needed for Warehouse Hardware Sizing 85Benchmarking 86Sizing Hardware for Business Intelligence Tools 89

Your Strategy 90

Part II Custom-Built Data Warehousing Solutions 91

Chapter 5 Designing for Usability 93Approaches for Design 94

Key Design Considerations 94Features for Design — Enhancing Performance 98Business Scenario 106Normalized Design 108Dimensional Design 110Hybrid Design 113

x Contents

02_919216 ftoc.qxp 11/21/06 10:28 PM Page x

Online Analytical Processing Design 116Other Considerations 120

Selecting the Best Approach 126

Chapter 6 Business Intelligence Tools 127Oracle Portal and Portal Products 128

Using Oracle Portal 129Building and Deploying Oracle Portal and Portlets 132

Reporting 136BI/XML Publisher 137Oracle Reports 140Oracle BI Reporting Workbench (Actuate) 141

Ad hoc Query and Analysis 142Business Intelligence Enterprise Edition 143Discoverer and Business Intelligence Standard Edition 153Oracle Spreadsheet Add-ins 162

Building Business Intelligence Applications 165JDeveloper and BI Beans 166Data Miner 166

Other Tools 169

Chapter 7 Data Loading 175Oracle Database Data Loading Features 176

Embedded ETL in the Oracle Database 177SQL*Loader 178Change Data Capture 179Transportable Tablespaces 180Data Pump 180

Oracle Warehouse Builder 181OWB Packaging 181Typical Steps when using OWB 182ETL Design in OWB 184OWB and Dimensional Models 189The OWB Process Editor 191

Balancing Data Loading Choices 192

Chapter 8 Managing the Oracle Data Warehouse 195Oracle Enterprise Manager Grid Control 196

Database Performance Monitoring 200Database Administration 202Database Maintenance 218Database Topology 223

Management and Management Options 224

Chapter 9 Data Warehouse Performance Tuning and Monitoring 227Understanding Performance Challenges 228

Who Is Responsible? 228Causes of Poor Performance 231

Contents xi

02_919216 ftoc.qxp 11/21/06 10:28 PM Page xi

Successful Approaches to Performance Tuning 238Critical Tasks for Performance Tuning Lifecycle 239Hardware Configuration 244Software Configuration 247Database Application Design 256

Business Scenario: Tuning Our Sample Solution 257Where To Start 257Enterprise Manager Advisory Framework 258

Approaches for Success 273

Part III Best Practices 275

Chapter 10 Scoping the Effort and an Approach for Success 277Uncovering Key Business Initiatives 278

Where to Start 278Information Sources 280What is Important 282

Accountability and Securing Business Sponsorship 282Establish a Steering Committee 283Project Review Board 283

Endorsing a Methodology 284Choices of Methodologies 284A Business-focused Approach 285

Staffing the Project 293Organization Structure 293End-User Experience 299Engaging the Business: Education and Training 301

Managing Risk 301Communication — Managing Expectations 301Contingency Allocation 302Financial Risk 303Technology Risk 304

No Place to Shortcut 305

Chapter 11 Understanding Business Needs 307How Bad Deployment Choices Impact the Business 308

Independent Data Marts 308Limited and Inflexible Reporting 310Sources of Information Limited to Internal Data 311Limited Data History 311Lack of Current High-Quality Data 312Limited Growth Flexibility 313

Project Drivers and Business Types 314Financial Companies 314Healthcare 316Manufacturing 318Media and Entertainment 319

xii Contents

02_919216 ftoc.qxp 11/21/06 10:28 PM Page xii

Retail 319Telecommunications 320Other Business Types: Transportation and Utilities 321Educational Institutions 321Government Agencies 323

Developing Scope and Gaining Business Support 326

Chapter 12 Justifying Projects and Claiming Success 329Concept to Project 330

Business Constraints 330Where to Start Justification 330

Measuring Value 331Common Metrics to Measure 332Common Budgeting Techniques 349

Total Cost of Ownership 356Modeling Total Cost of Ownership 357

Return on Investment 360Modeling Return on Investment 360

Claiming Success 363

Index 365

Contents xiii

02_919216 ftoc.qxp 11/21/06 10:28 PM Page xiii

02_919216 ftoc.qxp 11/21/06 10:28 PM Page xiv

We begin by acknowledging the support of our families, especially ourwives who realize that authors sometimes get a bit cranky and difficult asdeadlines approach. Although they probably hope there is not another bookcoming from any one of us soon, we realize such an undertaking would notbe possible without the support of Jodie Stackowiak, Donna Rayman, andLuAnn Greenwald.

Special thanks to the folks at Wiley Publishing who worked their magicto turn the documents and screen-captured images from our laptops intothe book you have in front of you. We would especially like to thank TomDinse, our Senior Development Editor, and Bob Elliott, Wiley’s ExecutiveEditor who understood the need for such a book.

Within Oracle, we have had the great fortune to work with many peopleskilled in this area. From Oracle Development, we would like to acknowl-edge the following who have provided us with guidance over the yearsthat was especially relevant for this book: George Lumpkin, Robert Ash,Hermann Baer, Andrew Holdsworth, Paul Narth, Jean-Pierre Dijcks, PaulRodwick, Chris Leone, and Ray Roccaforte. We would also like to acknowl-edge the contributions of business intelligence specialists in the TechnologyBusiness Unit, especially Louis Nagode, Gayl Czaplicki, Derrick Cameron,Jim Bienski, Alan Manewitz, Joan Maiorana, and the Enterprise Technol-ogy Center under Jim Olsen where we were able to illustrate some of thelarge-scale management capabilities.

Acknowledgments

xv

03_919216 flast.qxp 11/21/06 10:01 PM Page xv

Oracle also has a great many business intelligence specialists within sales,consulting, and other organizations around the world who remind us ofthe day-to-day challenges that their customers face when building thesesolutions. Some of the key individuals who influenced the content in thisbook include David Pryor, Susan Cook, Steve Illingworth, Nick Whitehead,Jon Ainsworth, Kevin Lancaster, Craig Terry, Joe Thomas, Rob Reynolds,Rich Solari, Nuge Ajouz, Ken McMullen, Brian MacDonald, and PatrickViau. There are many more, of course.

Lastly, much of the content in this book is based on the experience of theauthors. Some of the descriptions of what to avoid are based on observa-tions we made of less successful techniques used by Oracle’s customers.But many of Oracle’s customers and partners provide innovation and tech-niques that take product features and turn them into useful solutions. Wehave had the fortune of dealing with both types of customers, and thisbook is much stronger and realistic because of what these customers andthe Oracle partners have shared with us. So, thank you to all of you whomwe have worked with over the years. We especially hope you find thisbook to be of value as you build and develop your own solutions.

xvi Acknowledgments

03_919216 flast.qxp 11/21/06 10:01 PM Page xvi

We are now decades into deploying decision support systems, data ware-houses, and business intelligence solutions. Today, there are many booksthat describe data warehousing and design approaches. There are manybooks that describe business intelligence. There are many books thatdescribe the Oracle database. So you may be asking, why did the authorsdecide to write this book?

The fact is, the authors of this book still hear comments from many ofyou that business intelligence and data warehousing projects are problem-atic. This seems to be true regardless of database technologies or businessintelligence tools selected and deployed. While the wealth of Oracle skillsand resources that exist might make this less true where Oracle technologyis part of the solution, the number of implementations that face significantissues and the repetition of mistakes convinced the authors that too fewprojects are approached holistically. Not many of the books that are avail-able as resources look at Oracle business intelligence and data warehous-ing in such a manner.

This book attempts to give you a single reference that covers a diverserange of relevant topics in providing a holistic approach. It covers the data-base and platform technology, of course. But it also covers business intelli-gence tools, emerging business intelligence applications, architecture choices,schema selection, management and performance tuning, requirementsgathering, and justifying the project. Tips are included throughout the bookbased on real experience and implementations.

Introduction

xvii

03_919216 flast.qxp 11/21/06 10:01 PM Page xvii

Your tendency might be to jump to sections you know something aboutor suspect as being a possible solution to a problem in order to furtherdevelop your knowledge of a specific topic. Although you should findvalue in using this book that way, keep in mind that the reason many imple-mentations struggle is due to ignoring areas that should be understood andare outside the core competencies of those engaged in the project.

To sum it up, the goal of this book is more than about gaining academicknowledge. If this book attains its objective, you will gain knowledge thatyou can apply to your own project such that your deployed solution will beviewed as successful technically within Information Technology (IT), butalso successful because it delivers the business value that your businesscommunity recognizes.

Who This Book Is For

This book should appeal to a wide audience. Although those in IT will findit particularly useful, more technically inclined business analysts and man-agers should also find value in topics such as justifying projects and evalu-ating deployment choices.

Within IT, the day-to-day management and modification of such aninfrastructure often falls on database administrators, programmers, andsystems managers. Certainly, we cover topics of interest to this group.Unfortunately, the value of architects and project managers in deployingand updating such solutions is often overlooked. There is plenty in thisbook that should also appeal to that audience.

How This Book Is Organized

This book is divided into three parts:

Oracle Business Intelligence Defined

Custom Built Data Warehousing Solutions

Best Practices

Part I: Oracle Business Intelligence DefinedPart I provides a broad background as to possible Oracle-based solutionsand how you might deploy them. The database and business intelligencetools are introduced here, but other related topics are covered as well in thefollowing four chapters.

xviii Introduction

03_919216 flast.qxp 11/21/06 10:01 PM Page xviii

Chapter 1: Oracle Business Intelligence

A broad introduction of Oracle business intelligence is provided. Topicsintroduced include Oracle’s transactional business intelligence, integrationcomponents, and components in custom-built data warehouses and busi-ness intelligence solutions.

Chapter 2: Oracle’s Transactional Business Intelligence

Sometimes called operational business intelligence, this chapter describesOracle’s Daily Business Intelligence modules, Balanced Scorecard solution,and Data Hubs (used in master data management). You are provided withguidance as to where such solutions might be particularly useful and whya data warehouse might also be deployed to augment such a solution.

Chapter 3: Introduction to Oracle Data Warehousing

The introduction of this topic covers the wide array of features in the Ora-cle database that are relevant in data warehousing. More detailed explana-tions are provided in Part II of this book. In addition, the chapter covers thedata models that Oracle provides for its applications as pre-built datawarehousing solutions.

Chapter 4: Choosing a Platform

The basics of choosing a hardware platform are covered including scalingup versus scaling out and how to size your choice. Specifics addressedunder these broad topics include high availability considerations, manage-ability considerations, and approaches to benchmarking.

Part II: Custom-Built Data Warehousing SolutionsMost business intelligence solutions today are custom built. Part IIdescribes design approaches and deploying and managing business intel-ligence tools and an Oracle data warehousing database. These areexplained in the following five chapters.

Chapter 5: Designing for Usability

Covering approaches to design, topics in this chapter include how to leverageOracle features and an illustration of how these features can be used to pro-vide solutions to needs driven by a business scenario. Schema approachesare described including third normal form, star schema, hybrids, andOnline Analytical Processing (OLAP).

Introduction xix

03_919216 flast.qxp 11/21/06 10:01 PM Page xix

Chapter 6: Business Intelligence Tools

This chapter introduces using and deploying Oracle’s wide array of busi-ness intelligence tools, including portals, reporting, and ad hoc query andanalysis tools. The Oracle Business Intelligence Suites (Standard Editionand Enterprise Edition) are covered. In addition, the Oracle database sup-port provided by business intelligence tools available from other vendorsis described.

Chapter 7: Data Loading

Embedded extraction, transformation, and loading (ETL) features providedby the Oracle database are described in this chapter. Oracle WarehouseBuilder’s role in ETL, target data warehouse design, data quality analyses,and metadata management is also described.

Chapter 8: Managing the Oracle Data Warehouse

Oracle Enterprise Manager provides a useful interface often used in man-aging Oracle data warehouses as described in this chapter. The Grid Controlinterface for managing clusters is illustrated, as are interfaces for basic per-formance monitoring, administration, and maintenance.

Chapter 9: Data Warehouse Performance Tuning and Monitoring

Typical performance challenges are described and proven approaches tosolving such challenges are presented. We then illustrate using suchapproaches to tune the data warehouse first described in the business sce-nario presented in Chapter 5.

Part III: Best PracticesUnderstanding the technology is great, but is no guarantee of success. PartIII will help you identify potential risk and best approaches for mitigatingrisk as you develop and deploy your solution. These best practices aredescribed in the following three chapters:

Chapter 10: Scoping the Effort and an Approach for Success

This chapter describes how to uncover initiatives by your business com-munity, securing business sponsorship, endorsing a methodology, projectstaffing, and managing risk.

xx Introduction

03_919216 flast.qxp 11/21/06 10:01 PM Page xx

Chapter 11: Understanding Business Needs

Business needs for better business intelligence might be driven by a poorlydesigned solution or by a new business requirement. Examples of less opti-mal solutions and how they can impact the business are first described inthis chapter. Typical project drivers driven by business requirements are thenpresented followed by suggestions on how to build support for a project.

Chapter 12: Justifying Projects and Claiming Success

Getting the go-ahead to build a solution often requires financial justifica-tion. This chapter identifies the potential costs you should consider andwhere business benefits might come from. Financial benefits are computedfor a variety of scenarios and computing return on investment (ROI) isdescribed.

Illustrations in the Text

Oracle product illustrations in the text are captured from recent Oraclesoftware versions. You should be able to leverage similar capabilitiesregardless of your Oracle software version provided you have Oracle Data-base 10g, Oracle Business Intelligence 10g, or newer releases of these prod-ucts. We frequently indicate when key features were introduced in theseproducts so that if you have older releases deployed, you can understandlimitations you might face.

From Here

To become an expert on this topic usually requires years of practice andlearning, implementations for a variety of companies and organizations,and the uncommon ability to feel equally at home discussing needs anddetails among both IT and business co-workers. For those that grow insuch expertise, there is great opportunity and potential reward.

This book is intended to help lay that foundation. Of course, your suc-cess will depend not only on what you read in the following chapters, butalso on how you put what you learn here into practice in the solutions youwork on. As you now start reading this book, it is our hope that it will helpyou avoid many common pitfalls and that you will gain a better perspec-tive on how to attain professional success in building and deploying suchprojects.

Introduction xxi

03_919216 flast.qxp 11/21/06 10:01 PM Page xxi

03_919216 flast.qxp 11/21/06 10:01 PM Page xxii

PA R T

I

Oracle BusinessIntelligence Defined

In This PartChapter 1: Oracle Business IntelligenceChapter 2: Oracle’s Transactional Business IntelligenceChapter 3: Introduction to Oracle Data WarehousingChapter 4: Choosing a Platform

04_919216 pt01.qxp 11/21/06 10:02 PM Page 1

04_919216 pt01.qxp 11/21/06 10:02 PM Page 2

3

Business intelligence can be defined as having the right access to the rightdata or information needed to make the right business decisions at theright time. The data might be raw or might have been analyzed in someway. Having access to such information enables management of the busi-ness by fact instead of by primarily relying on intuition.

This is a broad definition of business intelligence and is not limited todata warehousing alone. Although a data warehouse is often used to pro-vide such a solution and is the primary focus of most of this book, we’llbroaden the discussion to also include business intelligence gained fromon-line transaction processing solutions. Business analysts and users ofbusiness intelligence don’t really care about — or want to understandwhere their information comes from. They simply want access to suchsources. So the solution you choose to deploy will depend on the kind ofinformation that is needed.

This chapter provides a broad discussion of Oracle’s business intelli-gence offerings and should help you better understand all of the solutiontypes available for deployment. We conclude this chapter by discussingsome of the emerging business needs that will lead to a further blending ofdata warehousing and transactional systems. In subsequent chapters inthis section of the book, we provide more details as to how and why you’d

Oracle Business Intelligence

C H A P T E R

1

05_919216 ch01.qxp 11/21/06 9:59 PM Page 3

deploy transactional business intelligence and data warehousing solu-tions. We also discuss some of the platform strategies for deployment.

After the introductory first section of this book, we describe in muchgreater detail the area of business intelligence that you are probably mostinterested in: custom-built data warehousing solutions using Oracle databases. We provide examples of how you can design, use, and managevarious capabilities of the Oracle database and Oracle business intelligencetools. In the final section of this book, we discuss best practices and strate-gies for deployment of such solutions.

Although the primary audience of the book is information technology(IT) professionals, we begin this book with the following warning: build-ing a business intelligence solution as an IT project without sponsorship ofor buy-in by the lines of business is likely to end in very limited success orcareer-limiting failure. For many of you, the non-technical portions of thisbook in the best practices section might initially be of the least interest,because your interest is centered in IT and implementing technology plat-form solutions. However, applying techniques described in that sectioncould determine whether your project is viewed as successful.

Business Intelligence and Transactional Applications

Transactional applications generally provide business intelligence to busi-ness users through reports that reveal current data in transactional tables.Oracle’s E-Business Suite of applications, PeopleSoft applications, JDEdwards applications, and Siebel Customer Relationship Managementapplications all provide this level of business intelligence. Reporting isselected and deployed based on key business requirements (KBRs) andmost commonly displayed as key performance indicators (KPIs) in a dash-board using portal technology.

Most companies also deploy business intelligence solutions that rely on acomplementary data warehousing strategy when reporting and analysisbecomes more complex and summary level information is appropriate.Oracle’s PeopleSoft and JD Edwards’ EnterpriseOne applications are oftensurrounded by the PeopleSoft Enterprise Performance Management (EPM)data warehouse to enable such reporting through analytical applications.Oracle’s Siebel Customer Relationship Management (CRM) applicationsare similarly often surrounded by Business Analytics Applications builtupon a relationship management warehouse model. Other application ven-dors (such as SAP with their Business Warehouse) have such data ware-house models that often are deployed on Oracle databases.

4 Part I ■ Oracle Business Intelligence Defined

05_919216 ch01.qxp 11/21/06 9:59 PM Page 4

The Oracle E-Business Suite leverages more of a blended approach todelivering business intelligence applications as many of these applicationsrely on data in summary levels of transactional tables. The Enterprise Plan-ning and Budgeting application, a more complex analytical application,leverages Oracle OLAP technology in a separate multi-dimensional cube.

At the time of publication of this book, Oracle has described manyaspects of Project Fusion, Oracle’s future single set of transactional appli-cations that provide a migration path for current deployments of the E-Business Suite, PeopleSoft, JD Edwards, and Siebel CRM applications.The business intelligence solutions provided for this next generation ofapplications will continue to provide a blending of transactional businessintelligence and incorporate data warehousing concepts.

Among Oracle E-Business Suite and PeopleSoft EPM offerings, a num-ber of common business intelligence applications are provided including abalanced scorecard, activity-based management, and enterprise planningand budgeting applications. In addition, the Oracle E-Business Suite hasDaily Business Intelligence. We’ll briefly describe what these applicationsdo in this chapter, and describe them in more detail in Chapter 2.

Where multiple transaction processing vendors’ data models are present,a variety of integration approaches are also possible. We include a discus-sion of some of those in this chapter.

Daily Business IntelligenceTo speed deployment of management reporting showing real-time transaction-level data, the Oracle E-Business Suite features Daily BusinessIntelligence. Many key management roles are pre-defined, including rolesof Chief Executive Officer, vice president of operations, vice president ofprocurement, vice president of service contracts, project executive, market-ing manager, sales manager, manager of e-mail, profit center manager, andcost center manager.

A performance management framework is provided to define KPIs (ormeasures) and dimensions, set targets, and subscribe to alerts. Out-of-the-box, over 250 key measures are predefined, including revenue, expenses,costs of revenue, contribution margin, gross margin, percentage margin,total headcount and average salary per employee, lead activity, lead con-version, purchase order purchases, contract leakage, inventory turns, andproject revenue. Common dimensions are supported across the E-BusinessSuite modules, including time, geography, customer, supplier, item, ware-house, currency, manager, organization, project organization, sales group,and operating group.

Chapter 1 ■ Oracle Business Intelligence 5

05_919216 ch01.qxp 11/21/06 9:59 PM Page 5

Reports are typically at the day level with period-to-date calculationsavailable for any day. Data is aggregated at multiple levels of the timedimension, including day, week, month, quarter, and year. Report pages areprovided out-of-the-box for profit and loss, expense management, compli-ance management, HR management, operations management, order man-agement, fulfillment management, project profitability management,product lifecycle management, profit operations management, quote man-agement, marketing management, leads management, sales management,sales comparative performance, opportunity management, procure-ment management, procure-to-pay management, and service contractsmanagement.

Figure 1-1 shows a dashboard view provided by Daily Business Intelli-gence for sales management of forecasts with KPIs available for salesgroup and direct reports forecasts, pipeline and weighted pipeline, andwon to period.

Balanced ScorecardExecutives have long sought a strategic management tool based on mea-surements of financial status, customer feedback and other outcomes, andinternal process flows that illustrate the state of the business and exposeareas where improvement might be desirable. In 1992, Drs. David Nortonand Robert Kaplan developed such a tool and named it the Balanced Scorecard. This tool is often used at companies focused on Total QualityManagement (TQM), where the goals are measurement-based manage-ment and feedback, employee empowerment, continuous improvement,and customer-defined quality.

Figure 1-1: A sales manager’s view within Daily Business Intelligence

6 Part I ■ Oracle Business Intelligence Defined

05_919216 ch01.qxp 11/21/06 9:59 PM Page 6