Oracle data warehousing and business intelligence guide

The goal of business intelligence (BI) technologies is to turn data into knowledge in order to improve business performance. Easier said than done! Whether you're looking for in-depth BI and data warehousing (DW) advice and analysis, or just need to learn the basics, you've come to the right place. This learning guide compiles all the news, columns and expert advice from SearchOracle.com's archives, from broad overviews of BI and DW to tips for those who are already working with Oracle's tools.

TABLE OF CONTENTS
   Data warehousing basics
   Business intelligence basics
   Extract/Transfer/Load
   Design issues
   For more information

Data warehousing basics

• Do people really understand data warehousing?: A data warehousing initiative shouldn't result in multiple data silos, but too often that's just what happens. The problem, says guest columnist Rick Sherman, is that data warehousing is misunderstood.
  
• Peering into Bill Inmon's data warehousing crystal ball: "Data warehousing is just beginning as an industry," according to W. H. Inmon, the father of the data warehouse. So what lies ahead?
  
• Data warehouse woes? Blame your CEO: If you're disappointed with your data warehouse performance, find out whether your CEO is doing a good job integrating disparate business units.
  
• Top 10 new data warehousing features in Oracle Database 10g: Plus Consultancy's Mark Rittman shares what he believes are the 10 most exciting new data warehousing features in 10g.
  
• Deciding what hardware to purchase for a data warehouse: Data warehousing expert Ian Abramson addresses the question, "Are there formulas for deciding what hardware to purchase for a data warehouse?"
  
• Data warehousing possible on a disaster recovery hot site?: Our expert Brian Peasland responds to a user's question: "We have been granted approval to put together a disaster recovery hot site. Our CIO would like to do some data warehousing on the hot site. Is this possible when using DataGuard in a standby DR replication scenario?"
  
• Resources for Oracle DBA learning about DW principles: This expert response provides guidance for Oracle DBAs looking to learn more about data warehousing.
  
• Using export/import to clone a data warehouse: This detailed expert response addresses the pros and cons of using an export/import process for DW cloning.
  
• MetaBase scripting for the Oracle data warehouse DBA: This tip describes the merits of Oracle's dbms_scheduler package and the Oracle Warehouse Builder MetaBase Plus (OMB) scripting language.
  
• More data warehousing advice: Browse the archives of Ian Abramson's expert advice on DW implementation and ETL.
  
  

Business intelligence basics

• Analyst views: Business intelligence: This article provides analysts' views of what BI really is, some of the reasons behind the increased interest in BI and the pitfalls to which users should be alerted, as well as recommendations for how best to proceed with BI.
  
• Implementing business intelligence with Oracle's E-Business Suite, part 1: This tip covers the merits of using Oracle's Embedded Data Warehouse, Business Intelligence System and Daily Business Intelligence features.
  
• Implementing business intelligence with Oracle's E-Business Suite, part 2: This tip covers the merits of using Oracle's Sales Analyzer, Financial Analyzer and Enterprise Planning and Budgeting features for data analysis.
  
• Implementing business intelligence with Oracle's E-Business Suite, part 3: This tip covers the merits of using Oracle's Discoverer and Reports features.
  
• Areas of impact while migrating database from 8i to 9i/10g: Read our expert's response to this reader question: "We are supporting a BI/data warehouse application. Now the backend database is migrating from Oracle 8i to Oracle 9i/10g. We are using Oracle Express Analyzer 6.3.2 as the OLAP tool for report generation. We would like to know the impact and areas of concern."
  
• The BI application consolidation challenge: This tip addresses the necessity and difficulty of consolidating business intelligence applications gained through acquisitions.
  
• Saving lives with Oracle BI: In the area of medical informatics, powerful tools such as Oracle Discoverer and Oracle Data Mining are improving the quality of health care and helping scientists to save lives.
  
• OLAP defined: What is OLAP, and what are its uses in the present day business environment?
  
• Oracle9i OLAP uncovered: This article outlines some of the key features of the Oracle9i OLAP option and explains how multidimensional OLAP data can now be incorporated into Oracle databases and made available to standard SQL-based Oracle tools and applications.
  
• Expanding business intelligence with enterprise information integration: In this Q&A the chief architect for ING Worldwide discusses the need for data integration in BI.
  
• One year later, Oracle touts progress of Fusion: This article details the progress of Oracle's year-old Fusion initiative as of early 2006.
  
• Legal auditing firm retains customers with Oracle BI: Read why one firm moved business intelligence to the top of its IT priority list.
  
• Getting BI with a little help from your friends: An interview with the VP of a firm that chose to implement Oracle Business Intelligence to solve its customer retention problems.
  
• Reducing time to process a query: Read expert Mike Lampa's response to the question, "We are using Oracle Business Intelligence 10g. We have a problem: when we click on a worksheet it takes too much time to process the query. Is there any possibility that we can reduce this elapsed time of querying?"
  
• Testing BI applications: This expert response addresses the types of tests that are typically used in testing BI and ETL applications.
  
• More business intelligence advice: Browse the archives of Mike Lampa's expert advice on data warehousing, metadata and BI.

Extract/Transfer/Load

• Rating ETL tools: Data warehousing expert Ian Abramson provides weighted descriptions of criteria for evaluating ETL tools.
  
• When managing database environments, integration is key: This tip describes the importance of ETL tools to integration, a key process for managing complex database environments.
  
• Questions about ETL and business intelligence tools: A reader asks what ETL tool should be used to ETL data from Oracle to Sybase IQ, and what tool should be used for business intelligence.
  
• Can ETL tools handle complicated business logic?: Ian Abramson answers the question, "We extract/transform/load into our data warehouse using SQR programs. One of the main reasons why we have not purchased an ETL tool is that we have complicated business logic embedded in many of our tables. Can ETL tools handle this?"
  
• Custom development versus ETL tool: What are the pros and cons of custom development versus an ETL tool?
  
• Engine-based versus code-generating ETL tools: Ian Abramson addresses the following remarks: "Why would you choose an engine-based ETL tool only? Code-generating tools (like Oracle Warehouse Builder) are supposed to be faster. You may not want a tool that generates legacy code like COBOL for some obvious reasons, but code-generating tools can achieve a much faster load throughout, as the data does not have to go through the engine bottleneck."
• Can you use ETL successfully as part of the process to migrate from OS/390 to Unix or NT?: This expert response discusses the merits of using an ETL tool to migrate data from one server to another.

Design issues

• Design recommendations for fact tables and dimensions: This expert response provides recommendations and restrictions for fact tables and dimensions in data warehouse design.
  
• Composite keys in fact tables: Ian Abramson answers the question, "Is it a good approach to use a composite key in a fact table linking to a dimensional table?"
  
• Producing a nested output: Ian Abramson explains how to produce formatted output from a database.
  
• External tables explained: A reader asks, "What are some of the pros and cons I should consider and what are some of the general rules of thumb for deciding whether or not I should use external tables?"
  
• A simple way to reorganize a table and release the unused space: This tip offers a simple solution to better manage table space and performance during ETL operations.
  
• Queries to retrieve table metadata: This utility provides a quick view of tables, including their relationships (parent and children tables/columns), indexes and grants.
  

For more information

• Special Report: Getting down to business intelligence
  
• Check out our sister site, SearchDataManagement.com.
  
• Have a BI or DW question? Ask one of our experts: