http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/concept.htm#i10064091 Data Warehousing Concepts This chapter provides an overview of the Oracle data warehousing implementation. It includes:•What is a Data Warehouse?•Data Warehouse Architectures•Extracting Information from a Data WarehouseNote that this book is meant as a supplement to standard texts about data warehousing. This book focuses on Oracle-specific material and does not reproduce in detail material of a general nature. Two standard texts are:•The Data Warehouse Toolkit by Ralph Kimball (John Wiley and Sons, 1996)•Building the Data Warehouse by William Inmon (John Wiley and Sons, 1996)What is a Data Warehouse? A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but can include data from other sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources. This helps in:•Maintaining historical records•Analyzing the data to gain a better understanding of the business and to improve the business.In addition to a relational database, a data warehouse environment can include an extraction, transportation, transformation, and loading (ETL) solution, statistical analysis, reporting, data mining capabilities, client analysis tools, and other applications that manage the process of gathering data, transforming it into useful, actionable information, and delivering it to business users.See Also:Chapter 12, "Overview of Extraction, Transformation, and Loading" A common way of introducing data warehousing is to refer to the characteristics of a data warehouse as set forth by William Inmon:•Subject Oriented•Integrated•Nonvolatile•Time VariantSubject Oriented Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a data warehouse that concentrates on sales. Using this data warehouse, you can answer questions such as "Who was our best customer for this item last year?" or "Who is likely to be our best customer next year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented.Integrated Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.Nonvolatile Nonvolatile means that, once entered into the data warehouse, data should not change. This is logical because the purpose of a data warehouse is to enable you to analyze what has occurred.Time Variant A data warehouse's focus on change over time is what is meant by the term time variant. In order to discover trends and identify hidden patterns and relationships in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive.
Contrasting OLTP and Data Warehousing Environments Figure 1-1 illustrates key differences between an OLTP system and a data warehouse.Figure 1-1 Contrasting OLTP and Data Warehousing Environments Description of "Figure 1-1 Contrasting OLTP and Data Warehousing Environments" One major difference between the types of system is that data warehouses are not usually in third normal form (3NF), a type of data normalization common in OLTP environments.Data warehouses and OLTP systems have very different requirements. Here are some examples of differences between typical data warehouses and OLTP systems:•WorkloadData warehouses are designed to accommodate ad hoc queries and data analysis. You might not know the workload of your data warehouse in advance, so a data warehouse should be optimized to perform well for a wide variety of possible query and analytical operations.OLTP systems support only predefined operations. Your applications might be specifically tuned or designed to support only these operations.•Data modificationsA data warehouse is updated on a regular basis by the ETL process (run nightly or weekly) using bulk data modification techniques. The end users of a data warehouse do not directly update the data warehouse except when using analytical tools, such as data mining, to make predictions with associated probabilities, assign customers to et segments, and develop customer profiles.In OLTP systems, end users routinely issue individual data modification statements to the database. The OLTP database is always up to date, and reflects the current state of each business transaction.•Schema designData warehouses often use denormalized or partially denormalized schemas (such as a star schema) to optimize query and analytical performance.OLTP systems often use fully normalized schemas to optimize update/insert/delete performance, and to guarantee data consistency.•Typical operationsA typical data warehouse query scans thousands or millions of rows. For example, "Find the total sales for all customers last month."A typical OLTP operation accesses only a handful of records. For example, "Retrieve the current order for this customer."•Historical dataData warehouses usually store many months or years of data. This is to support historical analysis and reporting.OLTP systems usually store data from only a few weeks or months. The OLTP system stores only historical data as needed to successfully meet the requirements of the current transaction.Data Warehouse Architectures Data warehouses and their architectures vary depending upon the specifics of an organization's situation. Three common architectures are:•Data Warehouse Architecture: Basic•Data Warehouse Architecture: with a Staging Area•Data Warehouse Architecture: with a Staging Area and Data MartsData Warehouse Architecture: Basic Figure 1-2 shows a simple architecture for a data warehouse. End users directly access data derived from several source systems through the data warehouse.Figure 1-2 Architecture of a Data Warehouse Description of "Figure 1-2 Architecture of a Data Warehouse" In Figure 1-2, the metadata and raw data of a traditional OLTP system is present, as is an additional type of data, summary data. Summaries are very valuable in data warehouses because they pre-compute long operations in advance. For example, a typical data warehouse query is to retrieve something such as August sales. A summary in an Oracle database is called a materialized view.Data Warehouse Architecture: with a Staging Area You must clean and process your operational data before putting it into the warehouse, as shown in Figure 1-3. You can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management. Figure 1-3 illustrates this typical architecture.Figure 1-3 Architecture of a Data Warehouse with a Staging Area
Description of "Figure 1-3 Architecture of a Data Warehouse with a Staging Area" Data Warehouse Architecture: with a Staging Area and Data Marts Although the architecture in Figure 1-3 is quite common, you may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. Figure 1-4 illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales or mine historical data to make predictions about customer behavior.Figure 1-4 Architecture of a Data Warehouse with a Staging Area and Data Marts Description of "Figure 1-4 Architecture of a Data Warehouse with a Staging Area and Data Marts" Note:Data marts are an important part of many data warehouses, but they are not the focus of this book. Extracting Information from a Data Warehouse You can extract information from the masses of data stored in a data warehouse by analyzing the data. The Oracle Database provides several ways to analyze data:•A wide array of statistical functions, including descriptive statistics, hypothesis testing, correlations analysis, test for distribution fit, cross tabs with Chi-square statistics, and analysis of variance (ANOVA); these functions are described in the Oracle Database SQL Language Reference.•OLAP•Data Mining
This chapter provides an overview of the Oracle data warehousing implementation. It includes:•What is a Data Warehouse?•Data Warehouse Architectures•Extracting Information from a Data WarehouseNote that this book is meant as a supplement to standard texts about data warehousing. This book focuses on Oracle-specific material and does not reproduce in detail material of a general nature. Two standard texts are:•The Data Warehouse Toolkit by Ralph Kimball (John Wiley and Sons, 1996)•Building the Data Warehouse by William Inmon (John Wiley and Sons, 1996)What is a Data Warehouse?
A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but can include data from other sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources. This helps in:•Maintaining historical records•Analyzing the data to gain a better understanding of the business and to improve the business.In addition to a relational database, a data warehouse environment can include an extraction, transportation, transformation, and loading (ETL) solution, statistical analysis, reporting, data mining capabilities, client analysis tools, and other applications that manage the process of gathering data, transforming it into useful, actionable information, and delivering it to business users.See Also:Chapter 12, "Overview of Extraction, Transformation, and Loading"
A common way of introducing data warehousing is to refer to the characteristics of a data warehouse as set forth by William Inmon:•Subject Oriented•Integrated•Nonvolatile•Time VariantSubject Oriented
Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a data warehouse that concentrates on sales. Using this data warehouse, you can answer questions such as "Who was our best customer for this item last year?" or "Who is likely to be our best customer next year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented.Integrated
Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.Nonvolatile
Nonvolatile means that, once entered into the data warehouse, data should not change. This is logical because the purpose of a data warehouse is to enable you to analyze what has occurred.Time Variant
A data warehouse's focus on change over time is what is meant by the term time variant. In order to discover trends and identify hidden patterns and relationships in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive.
Figure 1-1 illustrates key differences between an OLTP system and a data warehouse.Figure 1-1 Contrasting OLTP and Data Warehousing Environments
Description of "Figure 1-1 Contrasting OLTP and Data Warehousing Environments"
One major difference between the types of system is that data warehouses are not usually in third normal form (3NF), a type of data normalization common in OLTP environments.Data warehouses and OLTP systems have very different requirements. Here are some examples of differences between typical data warehouses and OLTP systems:•WorkloadData warehouses are designed to accommodate ad hoc queries and data analysis. You might not know the workload of your data warehouse in advance, so a data warehouse should be optimized to perform well for a wide variety of possible query and analytical operations.OLTP systems support only predefined operations. Your applications might be specifically tuned or designed to support only these operations.•Data modificationsA data warehouse is updated on a regular basis by the ETL process (run nightly or weekly) using bulk data modification techniques. The end users of a data warehouse do not directly update the data warehouse except when using analytical tools, such as data mining, to make predictions with associated probabilities, assign customers to et segments, and develop customer profiles.In OLTP systems, end users routinely issue individual data modification statements to the database. The OLTP database is always up to date, and reflects the current state of each business transaction.•Schema designData warehouses often use denormalized or partially denormalized schemas (such as a star schema) to optimize query and analytical performance.OLTP systems often use fully normalized schemas to optimize update/insert/delete performance, and to guarantee data consistency.•Typical operationsA typical data warehouse query scans thousands or millions of rows. For example, "Find the total sales for all customers last month."A typical OLTP operation accesses only a handful of records. For example, "Retrieve the current order for this customer."•Historical dataData warehouses usually store many months or years of data. This is to support historical analysis and reporting.OLTP systems usually store data from only a few weeks or months. The OLTP system stores only historical data as needed to successfully meet the requirements of the current transaction.Data Warehouse Architectures
Data warehouses and their architectures vary depending upon the specifics of an organization's situation. Three common architectures are:•Data Warehouse Architecture: Basic•Data Warehouse Architecture: with a Staging Area•Data Warehouse Architecture: with a Staging Area and Data MartsData Warehouse Architecture: Basic
Figure 1-2 shows a simple architecture for a data warehouse. End users directly access data derived from several source systems through the data warehouse.Figure 1-2 Architecture of a Data Warehouse
Description of "Figure 1-2 Architecture of a Data Warehouse"
In Figure 1-2, the metadata and raw data of a traditional OLTP system is present, as is an additional type of data, summary data. Summaries are very valuable in data warehouses because they pre-compute long operations in advance. For example, a typical data warehouse query is to retrieve something such as August sales. A summary in an Oracle database is called a materialized view.Data Warehouse Architecture: with a Staging Area
You must clean and process your operational data before putting it into the warehouse, as shown in Figure 1-3. You can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management. Figure 1-3 illustrates this typical architecture.Figure 1-3 Architecture of a Data Warehouse with a Staging Area
Data Warehouse Architecture: with a Staging Area and Data Marts
Although the architecture in Figure 1-3 is quite common, you may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. Figure 1-4 illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales or mine historical data to make predictions about customer behavior.Figure 1-4 Architecture of a Data Warehouse with a Staging Area and Data Marts
Description of "Figure 1-4 Architecture of a Data Warehouse with a Staging Area and Data Marts"
Note:Data marts are an important part of many data warehouses, but they are not the focus of this book.
Extracting Information from a Data Warehouse
You can extract information from the masses of data stored in a data warehouse by analyzing the data. The Oracle Database provides several ways to analyze data:•A wide array of statistical functions, including descriptive statistics, hypothesis testing, correlations analysis, test for distribution fit, cross tabs with Chi-square statistics, and analysis of variance (ANOVA); these functions are described in the Oracle Database SQL Language Reference.•OLAP•Data Mining
最近在使用Oracle Warehouse Builder的时候，发现其基本资料库的安装配置过程比较繁琐和笨拙，下面首先说一下安装过程中出现的问题，然后再比较详细的说明一下正确安装配置的过程。由于全部用文本方式说明，很多图形就略去了，看完本文还有不清楚的可以联系我。我的OWB安装环境：
IBM 本本，P1.3G/40G/512M
Oracle 10G Express Edition
Express 的安装路径为 E:\oraclexe
OWB的安装路径为：D:\OraHome_1 本机的Oracle Express默认服务为xe，数据库SID为xe安装过程中经常出现的问题（错误）包括：
1、在刚开始初始化OWB资料库的时候，系统报监听器在协议栈中找不到对应的处理句柄错误，然后退出安装。
2、在重新安装的时候，不停的报某某对象已经被使用，然后安装程序退出。问题分析：第一个问题可能是监听器配置不正确，所以系统会报错。但是当查看日志文件（在D:\OraHome_1\owb\reposasst\log.txt.0中）可以发现出这个错误的时候，系统已经将不少资料库对象安装到Express数据库中去了，也就是前面的对象安装的时候系统是可以正确找到监听器信息的，到了某个点上就找不到了。由此，可以进一步推测，OWB资料库的初始化程序的编写团队中存在某些配合方面的问题，可能同一个功能（比如这里的向监听器发出连接请求）由不同的人实现时采用了完全不同的处理方法，结果前者是正确的，后者可能错也可能对，因此是不确定的，程序的鲁棒性比较差。第二个问题的原因很明显，那就是OWB资料库重新安装的时候，系统并没有将原有由于没有成功安装而留在Express数据库中的对象清除掉。真搞不清楚为什么OWB的开发团队会连这个问题都留给用户手工去解决。虽然在owb的目录下有很多sql脚本（D:\OraHome_1\owb\reposasst下有几乎所有的安装sql脚本) 也有清除脚本，但这么多脚本放在一起，估计不是OWB的开发人员亲自上马，一般用户很难在短时间之内轻松的清除这些残留对象，更别说再次安装了。解决方法：
对第一个问题只好手工去修改监听器的配置信息了。
找到监听器的配置文件（我的机器上是E:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora，这里千万当心不要把OWB安装目录下的那个listener.ora当成是监听器的配置文件，因为我使用的是Oracle Express，所以当然监听程序当然使用Express安装目录下的listener.ora作为配置文件），这个文件中原来的内容为：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE) 由上面的内容可以看出与TCP协议对应的那个SID_NAME是CLRExtProc，所以OWB安装程序就会根据这个配置信息在机器上找名为CLRExtProc的实例，当然会失败，因为我的默认实例名是xe! 于是手工把上面内容修改为：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =xe)
(ORACLE_HOME = E:\oraclexe\app\oracle\product\10.2.0\server)
(SID_NAME = xe)
)
)LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)DEFAULT_SERVICE_LISTENER = (XE)这里的xe是oracle express 10g安装时生成的默认数据库实例。修改完了之后，别忘记重新启动监听程序！针对第二个问题，除了手工删除数据库对象之外似乎没有更好的解决方法了（如果你不想重新安装Oracle 10g Express的话）
这里有两种方法，当然都是基于10g express数据库的数据字典的。
1、从dba_sequences，dba_tables，dba_views等系统表中找出安装用户所拥有的sequence,表，视图等数据库对象，然后drop掉。这个方法比较麻烦，并且需要考虑所有的数据库对象，忘记一个的话，下次就得重新来过。
2、从DBA_OBJECTS表中找出安装用户所拥有的所有数据库对象并根据对象类型的不同产生不同的drop语句，然后将这些语句Paste到PL/SQL Developer或者SQL Plus中去执行。
该语句的格式类似于：
SELECT 'DROP '||OBJECT_TYPE||' Ojbect_owner.'||OBJECT_NAME||';' FROM DBA_OBJECTS WHERE OWNER='Ojbect_owner';
其中的Object_owner是OWB资料库的所有者。
使用以上两个方法其实并没有把全部的Owb残留对象从数据库中清除掉，还要执行以下一条sql语句： drop role owb_Ojbect_owner (再次提醒Object_owner是OWB资料库的所有者，你可以替换为你自己的有意义的名字）这个角色是OWB安装程序在资料库中自动创建的，所以重新安装的时候一定要清楚，不然你就要被那些讨厌的报错信息折磨得发狂了 :)总结：从以上发现问题，分析问题，解决问题的过程中可以发现OWB在很多细节方面做得还很不好，真让人难以相信这样的产品是出自Oracle之手（其它方面暂且不论），另外，开发团队内部的沟通协作是多么的重要，如果没有必要标新立异就尽量不要重新去发明轮子吧！我想，如果Oracle 的OWB团队再多花点精力在这些细节上面，可以避免很多人为这些讨厌的错误而抓狂了也符合Oracle 的王者风范；反之，那就是搬起石头砸自己的脚了，这样的产品还是不要退出的好，免得把自己的名气做坏了。