Page 40 - DCAP606_BUSINESS_INTELLIGENCE

Unit 3: Dimensional Data Warehouse

Temporal Table Notes

A temporal table is a table that records the period of time when a row is valid.
A table that is defined by the SQL statement CREATE GLOBAL TEMPORARY TABLE or DECLARE
GLOBAL TEMPORARY TABLE is temporary table. It is used to hold data temporarily.

XML Table

It is a special table that holds only XML data. When you create a table with an XML column,
database implicitly creates an XML table space and an XML table to store the XML data.

Self Assessment

State whether the following statements are true or false:
9. The surrogate key is derived from application data.
10. Surrogate keys change while the row exists.

11. In the case of natural keys, all tables that use the natural key will have to change.
12. When every table has a uniform surrogate key, some tasks can be easily automated by
composing the code in a table-independent way.

13. The values of surrogate keys have relationship with the real world meaning of the data
held in a row.
14. The most common type of table is base table.

15. A table with zero rows is an empty table.
16. A temporal table is a table that records the period of time when a row is valid.
17. XML table is used to hold data temporarily.

3.5 Multidimensional OLAP

OLAP stands for On-Line Analytical Processing. In computing, OLAP is an approach to answering
Multi-Dimensional Analytical (MDA) queries swiftly. OLAP is part of the broader category of
business intelligence, which also includes relational database, report writing and data mining.
Depending on the underlying technology used, OLAP can be broadly divided into MOLAP and
ROLAP.

Did u know? In the OLAP world, there are mainly two different types: Multidimensional
OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) is combination of
MOLAP and ROLAP.
3.5.1 MOLAP

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. The storage is not in the
relational database, but in proprietary formats. Figure 3.4 shows physical multi-dimensional
cubes.

LOVELY PROFESSIONAL UNIVERSITY 35