Data Integration and Modelling

Question 1

What are the components of a Conceptual Model in a Data Warehouse?

Answer 1

Facts, Measures and Dimensions

Question 2

Define Facts, Measures and Dimensions

Answer 2

Facts: Focus of interest for decision making.
E.g Sales, Shipments

Measures: Attributes that describe facts from different points of view.
E.g. Each sale is measured by its revenue.

Dimensions: Discrete attributes which determine the granularity adopted to represent facts
E.g. Product, Store, Date

Question 3

What is the purpose of a Multidimensional E/R Model?

Answer 3

Its purpose is to create an intuitive representation
of the multidimensional data that is optimized for
high-performance access

Question 4

How are dimension hierarchies organised?

Answer 4

Dependencies between the classification levels are described by the classification schema through functional dependencies.

Question 5

What are the advantages of using a Star Schema?

Answer 5

Improved Query performance for often-used data
Less tables and simple structure
Efficient query processing with regard to dimensions.

Question 6

What is a data cube?

Answer 6

Cubes consist of data cells with one or more dimensions.
The coordinates of a cell are the classification nodes.

Question 7

What are some of the properties of dimension tables?

Answer 7

1. Contain many descriptive columns: dimension tables are wide.
2. Generally don’t have too many rows.
3. Contents are relatively static: almost like a lookup table.

Question 8

What are some of the uses of dimension tables?

Answer 8

Some of the uses of dimension tables include:
1. Filters are based on dimension attributes.
2. Grouping columns are dimension attributes.
3. Fact tables are references through dimensions.

Question 9

What are the advantages and disadvantages of using a snowflake schema?

Answer 9

The main advantage of using a snowflake schema is that the queries can run faster, as the size of the dimension tables can be reduced.

Disadvantages:
Fact tables are responsible for 90% of the storage requirements. Thus, normalising the dimensions usually lead to insignificant improvements.

Normalisation of the dimension-tables can reduce the performance of the DW because it leads to a large number of tables.

Question 9

What are the advantages and disadvantages of using a star schema?

Answer 9

The main advantages of using a star schema are:
- Improved query performance for often-used data
- Less tables and simple structure
- Efficient query processing with regard to dimensions.

The main disadvantage is:
- High overhead of redundant data.

Question 10

What is a star schema and what are the advantages/disadvantages of using this model?