Database Requirements

Summary check list

The following list summarizes the database requirements:

You must have created a data warehouse with a star or snowflake schema.

Each dimension must have at least one level.

Attributes must be stored in the same dimension table as the level with which they are associated.

The Time dimension table must be fully populated and contain end-date and time-span columns, as described in "Time dimensions".

Facts must be stored in columns defined with a data type supported by OLAP Services.

All measures in a fact table must be dimensioned identically. You can store similarly dimensioned measures in different fact tables, however, each fact table must be referenced by a different cube. A cube references a single fact table.

All hierarchies for a dimension must have the same base level.

Time dimensions

OLAP metadata considers Time dimensions as distinct from other dimensions. When you specify a dimension in OLAP management, you must identify whether it is a Time dimension. A Time dimension has special attributes that support both regular and irregular time periods.

Regular time periods, such as weeks, months, and years, are evident on standard calendars. Typically, they neither overlap nor have gaps between them. Irregular time periods, such as promotional schedules and seasonal time periods, are not evident on standard calendars. They often overlap (even to the extent that one time period is a subset of another time period) or have gaps between them.

The Time dimension table must contain the following columns:

• Values for all dimension members, with a column for each level of summarization (such as weeks, quarters, and years).

• An end-date attribute for each level, such as WEEK_ENDDATE, QUARTER_ENDDATE, and YEAR_ENDDATE. These columns must have a DATE data type. Their values identify the last day in the time period.

• A time-span attribute for each level, such as WEEK_TIMESPAN, QUARTER_TIMESPAN, and YEAR_TIMESPAN. These columns must have a NUMBER data type. Their values identify the number of days in the period.

Example: Time dimension in a star schema

The following table describes an example dimension table in a star schema.

Column Name	Sample Value	Data Type	Comment
WEEK_ID	W12000	VARCHAR2	Level 1
WEEK_DESC	Week Ending January 8, 2000	VARCHAR2	Attribute
WEEK_ENDDATE	8-JAN-00	DATE	Attribute
WEEK_TIMESPAN	7	NUMBER	Attribute
QUARTER_ID	1QTR2000	VARCHAR2	Level 2
QUARTER_DESC	1st Quarter in Year 2000	VARCHAR2	Attribute
QUARTER_ENDDATE	31-MAR-00	DATE	Attribute
QUARTER_TIMESPAN	91	NUMBER	Attribute
YEAR_ID	YR2000	VARCHAR2	Level 3
YEAR_DESC	Year 2000	VARCHAR2	Attribute
YEAR_ENDDATE	31-DEC-00	DATE	Attribute
YEAR_TIMESPAN	366	NUMBER	Attribute

Example: Time dimension in a snowflake schema

The following tables describe example dimension tables in a snowflake schema. The first table defines weeks, which is the lowest level of time data.

Column Name	Sample Value	Data Type	Comment
WEEK_ID	W12000	VARCHAR2	Level 1
WEEK_DESC	Week Ending January 8, 2000	VARCHAR2	Attribute
WEEK_ENDDATE	8-JAN-00	DATE	Attribute
WEEK_TIMESPAN	7	NUMBER	Attribute

A second table defines quarters.

Column Name	Sample Value	Data Type	Comment
WEEK_ID	W12000	VARCHAR2	Foreign key
QUARTER_ID	1QTR2000	VARCHAR2	Level 2
QUARTER_DESC	1st Quarter in Year 2000	VARCHAR2	Attribute
QUARTER_ENDDATE	31-MAR-00	DATE	Attribute
QUARTER_TIMESPAN	91	NUMBER	Attribute

A third table defines years.

Column Name	Sample Value	Data Type	Comment
QUARTER_ID	1QTR2000	VARCHAR2	Foreign key
YEAR_ID	YR2000	VARCHAR2	Level 3
YEAR_DESC	Year 2000	VARCHAR2	Attribute
YEAR_ENDDATE	31-DEC-00	DATE	Attribute
YEAR_TIMESPAN	366	NUMBER	Attribute

Supported data types

The OLAP API supports native Java data types. It does not support the following Oracle data types: BLOB, CLOB, NCLOB, RAW, and LONG RAW. Do not create measures from facts with these unsupported data types.

Note: The OLAP DML supports CLOB and NCLOB data types. If your application requires this type of data, then you should investigate a MOLAP or HOLAP solution. Search for "SQL (FETCH)" in OLAP DML Help for additional information about supported data types.