Your Data Warehouse

What is a data warehouse?

The term data warehouse is used to distinguish a relational database that is used for business analysis (OLAP) rather than transaction processing (OLTP). While an OLTP database contains current low-level data and is typically optimized for the selection and retrieval of records, a data warehouse typically contains aggregated historical data and is optimized for particular types of analyses, depending upon the client applications.

Creating a data warehouse

This guide assumes that you have generated a data warehouse using an Extraction Transformation Transport (ETT) tool such as Oracle Warehouse Builder. If you do not currently have a data warehouse and wish to build one, then refer to the Oracle9i Data Warehousing Guide. It provides in-depth discussions about all of the database objects and concepts related to data warehouse design.

This chapter highlights some of the most important concepts developed in the Oracle9i Data Warehousing Guide. It also provides information that is specific to a data warehouse that will be used with OLAP Services. However, this chapter does not provide sufficient information for you to build a data warehouse of your own, or even to fully understand the issues involved in creating and maintaining a data warehouse.

Contents of a data warehouse

The contents of your data warehouse depends on the requirements of your users. They should be able to tell you what type of data they want to view and at what levels of aggregation they want to be able to view it.

Your data warehouse will store these types of data:

• Historical data

• Derived data

• Metadata

Historical data

A data warehouse typically contains several years of historical data. The amount of data that you decide to make available depends on available disk space and the types of analysis that you want to support. This data can come from your transactional database archives or other sources.

The lowest level of data is stored in fact tables in your data warehouse. The lowest level in a data warehouse is typically at a much higher level than in the transactional database. The transactional data should be aggregated to a base level where patterns and trends can emerge and analysis is meaningful, before being stored in the data warehouse. For example, individual purchase orders might be aggregated by sales representative, zip code, or some other demographic feature.

Some applications might perform analyses that require data at lower levels than users typically view it. You will need to check with the application builder or the application's documentation for those types of data requirements.

Derived data

Derived data is generated from existing data using a mathematical operation or a data transformation. OLAP Services uses SQL ROLLUP to generate aggregate data in the data warehouse. Dimension tables, also called lookup tables, are used to store the dimension members for all levels in the hierarchy. A dimension object in the database stores additional information about the hierarchy so that it can be aggregated further within the data warehouse.

By using dimension hierarchies, applications allow users to recognize trends at one level of aggregation, drill down to lower levels to identify reasons for these trends, and roll up to higher levels to see what affect these trends have on a larger sector of the business. For example, in a time dimension, days might roll up into months, and months into years.

Oracle9i provides materialized views for storing precomputed data derived from fact tables. Materialized views significantly improve querying times because the aggregates are computed for everyone's use, as a database administration task when the data is refreshed, rather than recomputed repeatedly whenever the aggregates are needed.

Metadata

Metadata is data that describes the data and schema objects, and is used by applications to fetch and compute the data correctly. The metadata informs the OLAP API about the data that is available within the database so that it can define multidimensional objects in OLAP Services. When an application runs, it instantiates these objects and populates them with data fetched from the database.

You must use Oracle Enterprise Manager to create the metadata required by the OLAP API, as described in "Creating Metadata".

About schemas

A schema is a collection of relational database objects. Two types of schemas are characteristic of a data warehouse:

• Star schema. Consists of one or more fact tables related to one or more dimension tables. The relationships are defined through foreign keys and metadata.

• Snowflake schema. A star schema that has been partially or fully normalized to reduce the number of duplicate values in the dimension tables.

For example, a star schema might have a single Geography dimension table with four columns: City, State, Region, and Country. Only the City column has predominately unique values, while the other columns have increasing numbers of duplicate values.

A snowflake schema might have three related geography dimension tables: One table with two columns (City and State) that define the relationship between cities and states, a second table with two columns (State and Country) that define the relationship between states and countries, and a third table with two columns (State and Country) that define the relationship between states and countries.

Your data warehouse can have either a star or a snowflake schema.