Article: Introduction to Data warehousing

Question 1

Decision Support System

Answer 1

A set of expandable, interactive IT techniques and tools designed for processing and analyzing data and for supporting managers in decision making. In practice the system provided value estimates for each alternative.
Different forms of DSS: 
- Passive 
- Active
- Collaborative
- Model-driven
- Etc.

Question 2

Data warehouse

Answer 2

A collection of data that supports decision-making processes. A Data-warehouse is:
- Subject-oriented
- Integrated
- Consistent
- Shows evolution over time
- Not vollatile
IT systems can be divided into transactional (OLTP) and analytical (OLAP). The type of query required in data warehouses is OLAP.

Question 3

Data warehousing architectures

Answer 3

• Single layer architecture
• Two-layer architecture (Primary DW or Corporate DW)
• Three-layer architecture

Question 4

Single layer architecture

Answer 4

Not often used in practice. Goal is to minimize the amount of data stored by removing data redundancies. Composed of a source layer, data warehouse and analytics.

Question 5

Two-layer architecture

Answer 5

Composed of a source layer, data staging, data warehouse (with meta-data and data marts), and analysis layer.

Question 6

Three-layer architecture

Answer 6

The third layer is reconciled data layer (or operational data store). This materalizes operational data obtained after integrating and cleansing source data. Main advantage: creates a common reference data model for the whole enterprise. Simultaneously, it sharply separates the problems of source data extraction and integration from those of data warehouse population. However, reconciled data leads to more redundancy of operational source data. This architecture is composed of a source layer, data staging, reconciled layer, loading, DW layer and analysis.

Question 7

Central architecture vs Federated architecture

Answer 7

• The amount of interdependent information exchanged between business units. When there is a lot of interdependent data, people tend to implement an Enterprise-wide architecture.
• Time available for the implementation. When there is little time, people tend to use a Federated architecture.
• Minor role of data-warehouse played in enterprise strategies. When the role is small, people tend to choose independent data marts.
• The frequent need for integrating pre-existing data-warehouses can require a federated architecture type.

Question 8

Extract, Transform, Load (ETL)

Answer 8

The ETL process takes place in the staging layer. Process takes place when a data-warehouse is populated for the first time, after that it occurs every time the data-warehouse is regularly updated. 4 different steps:

1. Extract
2. Cleanse - rectifying data values with rectification and homogenization.
3. Transform - managing data formats.
4. Load

Question 9

Multi-dimensional modelling

Answer 9

This concept looks at the measures and dimensions of values of a ‘fact’, and is the basis for the OLAP cube. Measure is a value that is numeric and can be used for calculating. Dimension is associated with hierarchy of aggregation levels, such as date. Using these dimensions and measures, you can perform restrictions (selections) and aggregations.

Question 10

Meta-data

Answer 10

Data to define other data. Can be classified in two categories:

• Internal meta-data
• External meta-data

Question 11

Internal meta-data

Answer 11

The users that are interested in internal meta-data are system administrators, because it contains information about data sources, transformation processes, population policies, logical and physical schemata, constraints and user profiles.

Question 12

External meta-data

Answer 12

The users that are interested in external meta-data are end-users.

Question 13

After data-warehouse

Answer 13

• Reports
• OLAP
• Dashboards

Question 14

Reports

Answer 14

A query and a layout -> a query generally implies a restriction and an aggregation of multidimensional data. Data-warehouses add value to reports, because they have one version of the truth (aggregated values are reliable and correct), and performance is improved by separating transaction processes and analysis.

Question 15

OLAP

Answer 15

A way to explore and analyze the data in the data-warehouse by means of multi-dimensional modelling. The most common operators are roll-up, drill-down, slice-and-dice, pivot, drill-across and drill-through.

Question 16

Dashboards

Answer 16

GUI that displays a limited amount of relevant data in a brief and easy-to-read format.

Question 17

ROLAP

Answer 17

Relational OLAP, an implementation based on relational DBMS’s.
Advantages:
- Can handle large amounts of data.
- Can leverage functionalities inherent in the relational database.
Disadvantages:
- Performance can be slow.
- Limited by SQL functionalities.

Question 18

MOLAP

Answer 18

Multi-dimensional OLAP, an implementation on multi-dimensional DBMS’s.
Advantages:
- Excellent performance
- Can perform complex calculations
Disadvantages:
- Limited in the amount of data it can handle
- Requires additional investment.

Question 19

HOLAP

Answer 19

Hybrid OLAP, an implementation using both relational and multi-dimensional DBMS’s.

Question 20

Other data-warehouse issues

Answer 20

• Quality: data in the warehouse should be accurate, up-to-date, complete, consistent, available, traceable, and clear. Also documentation is an important topic, because most documentation is not standardized yet.
• Security
• Evolution