Page 45 - DCAP606_BUSINESS_INTELLIGENCE

Business Intelligence

Notes 3.6 Summary

Dimensional model comprises of a fact table and numerous dimensional tables and is
used for assessing summarized data.

Fact table generally represent a process or reporting environment that is of value to the
organization.

A fact table typically corresponds to an associative entity in the E-R model.
Various types of measure in a fact table are: Additive, Semi Additive, Non-Additive.
There are basically three types of fact tables: Transactional, Periodic snapshots and
accumulating snapshots.

Dimension tables consist of attributes that describe fact records in the fact table.
A surrogate key in a database is a unique identifier for either an entity in the modelled
world or an object in the database.

Attributes that uniquely recognize an entity might change over the time, which might
lead to invalidation of the suitability of the compound keys.

But surrogate keys also come with some disadvantages. The values of surrogate keys have
no relationship with the real world meaning of the data held in a row.
Referential integrity must be maintained between all dimension tables and the fact table.

The most common type of table is base table. You can create a base table with the SQL
CREATE TABLE statement.
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.
OLAP stands for On-Line Analytical Processing. In computing, OLAP is an approach to
answering multi-dimensional analytical (MDA) queries swiftly.
In MOLAP, data is stored in a multidimensional cube. It fulfils the requirements for an
analytic application, where you require to access only summarized level of data.

HOLAP technologies combine the advantages of MOLAP and ROLAP.
3.7 Keywords

Accumulating Snapshots: In this type of fact table the activity of a process is shown such that it
has a well-defined beginning and end.

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.
Dimension Tables: Dimension tables consist of attributes that describe fact records in the fact
table.
Dimensional Model: Dimensional Modelling (DM) is the name of a set of techniques and concepts
used in data warehouse design. It is considered to be different from entity-relationship modelling
(ER).
Empty Table: It is a table with zero rows is an empty table.
E-R Model: In software engineering, an Entity-relationship model (ER model) is a data model
for describing a database in an abstract way.

40 LOVELY PROFESSIONAL UNIVERSITY