Mid Term

Question 1

Data Mart Advantages

Answer 1

Faster implementation

Earlier return on investment

Less risk of failure

Gives the project team time to learn and grow.

Question 2

Data Warehouse Advantages

Answer 2

A truly corporate effort, an enterprise view of data

Inherently architected, not a union of disparate data marts

Single, central storage of data about the content

Centralized rules and control

May see quick results if implemented with iterations

Question 3

Data Warehouse Disadvantages

Answer 3

Takes longer to build even with an iterative method

High exposure to risk of failure

Needs high level of cross-functional skills,

High outlay without proof of concept

Question 4

Data Mart/Bottom-Up Disadvantages

Answer 4

Each data mart has its own narrow view of data

Permeates redundant data in every data mart

Perpetuates inconsistent and irreconcilable data

Proliferates unmanageable interfaces (Not required)

Question 5

Phases of data warehouse development lifecycle.

Answer 5

Planning, Analysis, Design, Implementation

Question 6

What is the most costly lifecycle activity

Answer 6

ETL - You spend at least 75% of your time on that.

Question 7

What does ETL stand for?

Answer 7

Extract, Transform, Load

Question 8

Which Data Warehouse Lifecycle phase is analogous to logical design?

Answer 8

Dimensional modeling

Question 9

Which two data warehouse lifecycle phases have parallel levels of activities?

Answer 9

Design and implementation, which are the last two phases.

Question 10

The data warehouse architecture that does not rely on the creation of a new data structure is the what?

Answer 10

Federated.

Question 11

What is a data mart?

Answer 11

A data mart is a subset of a data warehouse focused on a particular line of business, department, or subject area.

Question 12

What is a data warehouse?

Answer 12

Central repositories of integrated data from one or more disparate sources.

Question 13

What activities are required in the analysis phase?

Answer 13

Analytic themes, which breaks requirements into categories;

bus matrix, which is the business processes mapped to the data needed;

Information packet development, maps which dimensions are needed for each fact table.

Question 14

What are the different types of data architectures, and what do they mean?

Answer 14

Federated: a collection of independent database systems are united into a loosely coupled federation in order to share and exchange information.

Hub and Spoke: The communication is not made between pairs of applications but between each application (spoke) and the central hub.

Data Mart Bus: A bus architecture is composed of a set of tightly integrated data marts that get their power from conformed dimensions and fact tables. A bus architecture uses top-down planning and a grid of business functions and dimensions to deliver a set of tightly integrated data marts.

Centralized Enterprise-Wide: A centralized data architecture means the data from each domain/subject (i.e. payroll, operations, finance) is copied to one location (i.e. a data lake under one storage account), and that the data from the multiple domains/subjects are combined to create centralized data models and unified views.

Independent Data Marts

Question 15

What are junk dimensions?

Answer 15

Junk dimensions are used for dimensions that only involve a few responses, and which don’t fit squarely in any other dimensions.

Question 16

What is a degenerate dimension?

Answer 16

When dimensions are stored in a fact table because there are so many.

Question 17

What are different options for slowly changing dimensions?

Answer 17

Zero option: do nothing, which is easiest, but you have inaccurate data.

One option: overwrite it.

Two: you retain the former value, but you create a new row. Problematic because you can’t duplicate a surrogate key. So instead, you have to identify the new information via a business key.

Three: instead of creating a new row, you create an additional column for “current” data.

Question 18

What are the basic dimensional model schemas?

Answer 18

Star schema and snowflake schema.

Question 19

What is Business Intelligence?

Answer 19

Business intelligence encompasses both the applications and methods used for the gathering, cleansing and storing of data, as well as the transformation of that data into usable knowledge capable of informing key business decisions and strategies.

Question 20

What is integrated data?

Answer 20

Integrated data refers to data that is pulled from many disparate data sources and organized to depict a useful, unified view of the business or parts of the business. The practice of data integration refers to the act of pulling data together to construct a unified view of the business data, and it can be done in multiple different ways.

Question 21

Who was Ralph Kimball?

Answer 21

Ralph Kimball is a top authority within the study of data warehousing, and a proponent of the bottom-up approach to data warehousing described above. He published the book “The Data Warehousing Toolkit” in 1996.

Question 22

What occurs in the Data Warehousing planning phase?

Answer 22

Define Initial project scope, Project Goals/Objectives, Project Success

Organize Team, Team roles, Team members

Organize Project, Project cost, Project schedule

Question 23

What are the requirements of Data Warehousing analysis?

Answer 23

Interview Summaries - Prose summarizing interviews

Analytics - Analysis requirements grouped into “categories”

DW Bus Matrix - Business processes mapped to data needed (dimensions used, eg time, student)

DM Information Package - Prioritized processes

Question 24

What is a degenerate dimension?

Answer 24

An attribute (dimension) stored in fact table

Attribute does NOT link to a dimension table

Typically a high-cardinality attribute

Typically groups records

Examples: PO#, Order#, Shipment#, …
Often used for drill-downs and/or data mining (e.g. Market Basket Analysis)

Question 25

What are the two basic dimensional model schemas?

Answer 25

Snowflake Schema and Star Schema

Question 26

What is a snowflake schema?

Answer 26

Fact table has direct relationship with some dimension tables, and indirect relationship with other(s)

Multi-table dimensions i.e., “Normalized” dimensions

Advantages: Good when large number of rows in Dims, Avoids data redundancy, Ease of maintaining

Question 27

What is a star schema?

Answer 27

Fact table has direct relationship with each of the dimension tables

“Single-table” dimensions i.e., denormalized

Advantages: Faster load/query/analysis performance, Less complexity; more intuitive to users, Good when relatively few rows in Dims

Question 28

What options exist for slowly changing dimensions?

Answer 28

0. Do Nothing
1. Overwrite Record
2. Retain All History (add new rows) – new SK
3. Retain Some History (add new columns)

Question 29

What are the key steps/components of the ETL process?

Answer 29

Extract: Copying and integrating data from OLTP and other data sources in preparation for cleansing and loading into the DW

Transform: Cleaning and converting data to prepare it for loading into the DW

Load: Putting cleansed and converted data into the DW

Question 30

What is the staging area and why is it important?

Answer 30

Information hub, facilitating the enriching stages that data goes through to populate a DW

Advantages: Separates source systems and DW, Minimizes ETL impact on source AND DW systems, Potential added support for operational reporting
e.g., ODS

Can consist of multiple “hubs”: “upload” area, “staging” area(s) , “DW load images”

Question 31

Describe common types of data transformations that occur during ETL

Answer 31

Format Revisions (Varchar (2) varchar (30))

Key Restructuring, Lookup (SK’s, AK’s)

Handling of Null Values (“unknown”; “-1” rows)

Decoding fields (m/f male/female)

Calculated, Derived values (FName + LName AS
Fullname)

Merging of Data (DimClass table = CLASS fields + COURSE fields)

Splitting of single fields (Addr Street, City, State, Zip)

Character set conversion (Ascii Unicode)

Units of measurement conversion (Meters Yards)

Date/time conversion (10/2/2022 02-OCT-2022)

Summarization (individualItemSale salesByProduct)

Deduplication (eliminating duplicated record)

Question 32

What is data profiling and why is it used?

Answer 32

Systematic analysis of the content of a data source
Profile each source table/dataset

Goals: Anticipate potential data quality issues upfront, Build quality corrections and controls into ETL process, Manual and/or Tool-assisted (SSIS Data Profiler)

Question 33

What are two ways to document ETL design?

Answer 33

Physical DW Design spreadsheet

ETL Map

Question 34

What are the two categories of extracting source data?

Answer 34

Static Data Capture - Point-in-time snapshot, Initial Loads and periodic refreshes

Revised Data Capture - Only data that has been added, updated, deleted since last load; Ongoing incremental loads - Updates existing data

Question 35

What are commonly used SQL commands for creating and transforming data?

Answer 35

INSERT…SELECT [DISTINCT]…
UPDATE…FROM…
CASE…WHEN… (can be used to expand values)s

Question 36

What is a multi-dimensional (OLAP) cube?

Answer 36

An OLAP (Online analytical processing) cube is a multi-dimensional array of data.

An OLAP Cube is a data structure that allows fast analysis of data according to the multiple Dimensions that define a business problem. A multidimensional cube for reporting sales might be, for example, composed of 7 Dimensions: Salesperson, Sales Amount, Region, Product, Region, Month, Year.