Page 39 - DCAP606_BUSINESS_INTELLIGENCE

Business Intelligence

Notes
!
Caution Every fact record must have a related record in every dimension table used with
that particular fact table.

 Shared Dimensions: To maintain consistency dimension tables that are shared are created.
These tables are used by all components and data marts in the data warehouse.

3.4.3 Alternative Tables used in Data Warehousing

Auxiliary Table

This table is created with the SQL statements CREATE AUXILIARY TABLE and is used to hold
the data for a column that is defined in a base table.

Base Table

The most common type of table is base table. You can create a base table with the SQL CREATE
TABLE statement. All programs and users that refer to this type of table refer to the same
description of the table and to the same instance of the table.

Clone Table

A table that is structurally identical to a base table is known as clone table. You can create a clone
table by using an ALTER TABLE statement for the base table that includes an ADD CLONE
clause.

Example: In the DB2 catalogue, SYSTABLESPACE.CLONE indicates that a clone table
exists.

Empty Table

A table with zero rows is an empty table.

History Table

A history table is used by Database to store historical versions of rows from the associated
system period temporal table.
Materialized Query Table

Materialized query tables are useful for complex queries that run on large amounts of data.

Notes They are commonly used in data warehousing and business intelligence applications.
Result Table

A table that contains a set of rows that a database selects or generates, directly or indirectly, from
one or more base tables in response to an SQL statement is known as result table. A result table
is not an object that you can define using a CREATE statement.

34 LOVELY PROFESSIONAL UNIVERSITY