Data Warehouse Design - Chapter 1.3

Question 1

What are 5 architecture properties that are essential for a data warehouse system?

Answer 1

1. Separation
2. Scalability
3. Extensibility
4. Security
5. Administerability

Question 2

What is meant by seperation?

Answer 2

Analytical and transactional processing should be kept apart as much as possible

Question 3

What is meant by scalability?

Answer 3

Hardware and software architectures should be easy to upgrade as the data volume and number of users requirements increase

Question 4

What is meant by extensibility?

Answer 4

It should be able to host new appliations and technologies without redesiging the whole system

Question 5

What is meant by security?

Answer 5

Monitoring access is essential because of the strategic data stored in data warehouses

Question 6

What is meant by administerability?

Answer 6

Management of the system should not be overly difficult

Question 7

What are the characteristics of a single-layer data warehouse?

Answer 7

• There is only one physical layer of data, which is the source layer of operational data
• The data warehouse is virtual, created by middleware.
• It does not meet the requirement ‘seperation’
• Rarely used in real life

Question 8

When can a single layer data warehouse be successful?

Answer 8

If analysis needs are particularly restricted and the data volume to analyze is huge.

Question 9

What are the stages in the two-layer architecture?

Answer 9

1. Source layer
2. Data staging
3. Data warehouse layer
4. Analysis

–> So it has four stages, but two physical layers, namely the source layer and the data warehouse layer

Question 10

What is the source layer?

Answer 10

Data that is originally stored to corporate relational databases or legacy databases

Question 11

What is meant by data staging?

Answer 11

Here data that is stored in the physical layer is extracted, cleansed and integrated into one common scheme.
For this, ETL tools are used.

Question 12

What are ETL Tools?

Answer 12

Extraction, Transformation and Loading tools - to merge, extract, transform, cleanse, alidate, filter and load source data into a data warehouse.

Question 13

What is the data warehouse layer?

Answer 13

The place where the information is stored to one logically centralized repository.
This data warehouse can sometimes automatically be accesed or sometimes data marts are used.

Question 14

What is a data mart?

Answer 14

A subset or an aggregation of the data stored to a primary data warehouse. It includes a set of information pieces relevant to a specific business area, deparment or group of users

Question 15

What happes in the analysis stage?

Answer 15

Integrated data is efficiently and flexibly accessed to issue reports, analyze information and simulate business scenarios

Question 16

What are three reasons data marts are useful?

Answer 16

1. They can be used as building blocks while incrementally developing data warehouses;
2. They mark out the information required by a specific group of users to solve queries;
3. They can deliver better performance because they are smaller than primary data warehouses

Question 17

Which two types of data marts are there?

Answer 17

1. Dependent data marts - populated from a primary data warehouse
2. Independent data marts - populated by the sources
   (streamlines the design, but prone to inconsistencies between the different marts)

Question 18

How can you avoid data inconsistencies between independent data marts?

Answer 18

By creating a primary data warehouse that is populated by the individual marts (instead of the other way around).

Question 19

What are five advantages of the two-layer architecture?

Answer 19

1. Good quality information is always available, even if access to the sources is unavailable.
2. Analysis queries do not affect the management of transactions
3. Data warehouses are logically structured according to the multi dimensional model
4. OLAP system have historical and summarized data as well - so you avoid a mismatch in terms of time and level of detail
5. Data warehouses can be designed specifically to optimize performance

Question 20

What is the key characteristic of the three-layer architecture?

Answer 20

It has all the stages from the two-layer architecture, but in between the data staging and the data warehouse there is another layer called ‘reconciled data’.

-> So the data warehouse is populated by the reconciled data, not by the operational sources

Question 21

What are the advantages of the added ‘reconciled data’ layer?

Answer 21

1. It creates a common reference data model for a whole enterprise.
2. Sometimes the reconciled layer can be used to better accomplish operational tasks (instead of using the operational sources)

Question 22

Which main architectual principles are used for data warehouse systems?

Answer 22

1. One layer system
2. Two layer system
3. Three layer system

Question 23

Which main five types of system are distinguished in scientific literature that also include the beforementioned layers?

Answer 23

1. Independent data marts architecture
2. Bus architecture
3. Hub-and-Spoke architecture
4. Centralized architecture
5. Federated architecture

Question 24

What is the independent data mart architecture?

Answer 24

Different data marts are separately designed and build in a non-integrated fashion.

• > used when there is no desire for an enterprise-wide warehousing project or when divisions are only loosely related
• > Not the best data integration and cross-reporting

Question 25

What is the bus architecture?

Answer 25

Similar to the independent data mart architecture, but the data marts are all designed according the same conformed dimensions to ensure logical integration and an enterprise-wide view of information.

Question 26

What is the hub-and-spoke architecture?

Answer 26

The data marts are feeded by the ‘reconciled data’ layer. Users access the data marts, but sometimes also the reconciled layer.

• > Attention to scalability, extensibility & an enterprise-wide view.
• > Most used in medium to large companies.

Question 27

What is the centralized architecture?

Answer 27

It is an implementation of the hub-and-spoke architecture, in which the reconciled layer and the data marts are collapsed into a single physical repository.

Question 28

What is the federated architecture?

Answer 28

There are multiple data marts that are supplied by the operational data sources. These data marts are then either physically or virtually integrated with the others iin the logical-phyiscal integration phase.

-> Used in dynamix context where preexisting data warehouses / data marts need to be integrated. (think with a merger)

Question 29

Which 4 factors are particularly influential when choosing one of these architectures?

Answer 29

• The amount of interdependent information exchanged between organizational units
• Restrictions on (human) capital resources could lead to a ‘quick’ architecture
• Enterprise strategy
• The need for integration of pre-existing data warehouses can lead to a federal system.