Data Management - What I missed from his slides

Question 1

What is business intelligence?

Answer 1

• Data driven decision making

- Transforming data into meaningful information/knowledge to support business decision making

Question 2

What is knowledge?

Answer 2

Processed data or information that is applicabale to a business decision problem

Question 3

What are descriptive analytics, predictive analytics and prescriptive analytics?

Answer 3

1. Using data to understand past and present
2. Predict future behaviour based on past performance
3. Make decisions or recommendations to achieve the best performance

Question 4

How is business intelligence a product?

Answer 4

It provides information and knowledge that enables decision making

Question 5

How is business intelligence a process?

Answer 5

You have to extract information and knowledge from data. This requires a series of actions.

Question 6

What is a database management system (DBMS)?

Answer 6

The software that controls the data.

Question 7

What is the difference between a super key and a candidate key?

Answer 7

A superkey is any combination of attributes that identifies each row.

A candidate key is a superkey without any unnecessary attributes

Question 8

What is cardinality?

Answer 8

The way tables are related. Their relationship.

Question 9

What is the difference in terms of time between a data warehouse and an operational system?

Answer 9

Operational: Current value data

Data warehouse: More historical perspective.
In a data warehouse, every key structure contains an element of time (whether you know it or not).

Question 10

What are 4 conceptual characteristics of a data warehouse?

Answer 10

1. Subject-oriented
2. Integrated
3. time-variant
4. nonvolatile (no updates)

Question 11

What is an independent data mart architecture?

Answer 11

Different data marts are seperately designed and built in a nonintegrated fashion. They are loaded with data from the source layer.

Question 12

Wat is a bus architecture?

Answer 12

Similar to an independent data mart system, except that the data marts have conformed dimensions.

Question 13

What is a hub-and-bespoke architecture?

Answer 13

You have datamarts that are loaded with data from the reconciled layer.

One of the most used architectures. Much attention to scalability and extensibility.

Reconciled layer: already integrated, consistent and correct data.

+ creates a common reference model
- leads to more redundancy

Question 14

What is a centralized architecture?

Answer 14

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

Question 15

What is a federated architecture?

Answer 15

You have datamarts filled by the operational data sources. BUT the data marts are integrated (virtually or physically) after that.

Happens often when there is a merger or acquisition and some datawarehouse needs to continue to exist.

Question 16

What is the difference between the grouped name ‘Data Mart Approach’ (bottom-up) and ‘Enterprise data warehouse approach’ (top-down?

Answer 16

• Data mart approach means you have a collection of data marts. Uses dimensional modelling.
• Enterprise warehouse means that (even though you might have data marts) they are integrated. Uses Entity-relational modeling.

Question 17

What is a data lake?

Answer 17

Similar to a datawarehouse, except that the data is stored in its natural/raw form untill it is needed.

Question 18

What two types of loads are their in the ETL-loading stage?

Answer 18

1. Integral load
   All records are taken and processed by comparing them with the records in the datawarehouse
2. Delta/incremental load
   Only the changed records are taken and processed systematically in the datawarehouse.

Question 19

What are the different generations of ETL tools?

Answer 19

1. Generation 0 - do it yourself (SQL procedures)
2. Generation 1 - code generators
3. Generation 2 - ETL engines

Question 20

What is the difference between ‘Query & reporting’ , ‘OLAP’ and ‘Data Mining Models’?

Answer 20

Query and reporting gives static reports through SQL.

OLAP gives interactive analysis of multidimensional data

Data mining discovers new valuable knowledge
(which customer is at risk of leaving?)

Question 21

What is a lattice of cubes?

Answer 21

If you roll up the base cube over all its associated dimensions and dimension hierarchies.

Bottom most cube = base cub Cb

Topmost cube = apex = Ct = contains only one cell

Question 22

How do you calculate how many cubes there are in a multidimensional database if there are no hierarchies?

Answer 22

You have n dimensions.

2^n

Question 23

How do you calculate how many cubes there are in a multidimensional database if there are hierarchies?

Answer 23

You have n dimensions and each dimension has L levels.

(L + 1)^n

Question 24

What is the purpose of a star schema?

Answer 24

To limit the number of joins in a multi-dimensional model to increase query performance.

Question 25

What is given in a star schema?

Answer 25

You have the fact table in the middle. This table has a list of all the dimension keys. These dimension keys are linked to other tables that are the dimension table. These have lists of their layers.

The fact table also include the measure keys.

Question 26

What is a snowflake schema?

Answer 26

Similar to a star schema, except that data is more normalized. This means that some dimension tables are linked to a secondary dimension table further specifying a specific layer in the dimension.

In the first dimension table that means that there is an extra ‘dimension key’ to link to the secondary table.

Question 27

How do you calculate density in a cube?

Answer 27

Number of actual rows / number of potential rows.

If the Fact sales has dimension Time, Product and Store and you set the dimension on Time(day), products and Stores.

There are 4 days, 5 products and 3 stores.

Potential rows: 4 * 5 * 3

Actual rows = given = 35

Densitity = 35 / 65 = 53.33%

Question 28

How can you deal with slow changes in dimension attributes?

Answer 28

1. Overwrite old values
2. Add version number field as part of the key
3. Add new columns to the dimension table.