data warehousing 2/3

Question 1

What is metadata in the context of data warehousing?

Answer 1

Metadata describes the structure, lineage, and definitions of data stored in the warehouse.

Question 2

What are the three types of metadata in a data warehouse?

Answer 2

Business Metadata: Data ownership, business definitions.
Technical Metadata: Table/column names, data types, keys.
Operational Metadata: Data state (active, archived), lineage.

Question 3

What is the difference between ETL and ELT?

Answer 3

ETL (Extract, Transform, Load): Data is transformed before loading into the warehouse.
ELT (Extract, Load, Transform): Data is loaded first, then transformed within the warehouse.

Question 4

What are the strengths of ETL processes?

Answer 4

Shorter development time.
Targeted data extraction.
Wide availability of tools.

Question 5

What is the purpose of data staging in a warehouse?

Answer 5

Acts as a temporary space for data transformation and integration before loading into the warehouse.

Question 6

What is a data mart, and how does it differ from a data warehouse?

Answer 6

A data mart is a subset of a data warehouse tailored for specific departments or functions.
It is smaller, faster to build, and focused on departmental needs.

Question 7

What are the types of data marts?

Answer 7

Independent: Stand-alone, specific to one department.
Dependent: Derived from the central data warehouse.

Question 8

What are the key index structures in data warehousing?

Answer 8

Inverted Index: Maps terms to their data locations for quick lookup.
Bitmap Index: Efficient for columns with few distinct values.
Join Index: Pre-computed joins between fact and dimension tables.

Question 9

What is the difference between inverted and bitmap indexes?

Answer 9

Inverted Index: Links terms to data records.
Bitmap Index: Uses binary vectors for efficient intersections of query results.

Question 10

What are the three key security aspects of data warehousing?

Answer 10

Confidentiality, integrity, and availability.

Question 11

How can users be classified in a data warehouse security framework?

Answer 11

By role, department, job type, or grade.

Question 12

What are common reasons for ‘dirty’ data in a data warehouse?

Answer 12

Dummy values, missing data, cryptic data, non-unique identifiers, and violation of business rules.

Question 13

What is the purpose of parsing in data cleaning?

Answer 13

Identifying and isolating data components, such as splitting names or addresses into parts.

Question 14

How is data standardized during cleaning?

Answer 14

By applying consistent formats using business rules, such as replacing nicknames or adding prefixes.

Question 15

What is data enrichment?

Answer 15

Enhancing existing data by incorporating external sources to provide additional context.

Question 16

What is the role of scoring in a data warehouse?

Answer 16

Assigning probabilities to events like customer churn or likelihood of purchase.

Question 17

What are refresh techniques in data warehousing?

Answer 17

Full Extract: Reloads the entire source table (expensive).
Incremental Extract: Updates based on changes since the last extract.

Question 18

What is an inverted index used for?

Answer 18

To associate index terms with data locations, enabling fast query lookups.

Question 19

When is a bitmap index most effective?

Answer 19

For columns with a small number of distinct values.

Question 20

What are join indexes, and how do they improve performance?

Answer 20

Pre-computed joins that link fact and dimension tables, reducing query execution time.

Question 21

What are the steps in managing security for a data warehouse?

Answer 21

Define access rights based on roles and sensitivity levels.
Use encryption and filtration for data transfer.
Implement auditing to monitor access and changes.

Question 22

What is the purpose of a security audit in data warehousing?

Answer 22

To review and test the adequacy of system controls and recommend changes to policies.

Question 23

What are the three types of security classifications for data objects?

Answer 23

Public, confidential, and top secret.

Question 24

What legal requirements should be considered in data warehousing?

Answer 24

Handling sensitive data, limiting third-party sharing, and adhering to privacy regulations.

Question 25

What is volume analysis in the context of data warehousing?

Answer 25

Estimating storage needs, including current usage and future growth over time.

Question 26

What is usage analysis, and why is it important?

Answer 26

Analyzing transaction types and frequencies to optimize system performance and identify constraints.

Question 27

What are the three main types of OLAP architecture?

Answer 27

Relational OLAP (ROLAP).
Multidimensional OLAP (MOLAP).
Hybrid OLAP (HOLAP).

Question 28

What is the difference between ROLAP and MOLAP?

Answer 28

ROLAP uses relational databases and is suitable for large datasets.
MOLAP uses pre-aggregated arrays and is faster for smaller, predefined queries.