Data Warehouses

Question 1

OLTP

Answer 1

Online Transaction Processing
- ACID properties required
- Details about every transaction

Question 2

OLAP

Answer 2

Online Analytical Processing
- Data redundancy allowed
- Historical data
- Consistency before and after loads is required

Question 3

Decision Support Systems (DSS)

Answer 3

Huge operational database

Question 4

Operational Database vs. Data Warehouse

Answer 4

Operational stores every transaction while data warehouse stores aggregations of events through ETL

Question 5

ETL

Answer 5

Extraction: Acquires data from sources
(Cleaning): Improves data quality
Transformation: Transforms data to DW format
Loading: Propagates changes
(Loads data to DW)

Question 6

Data Mart

Answer 6

Small warehouse that focuses on one specific target (ex: one single store)

Question 7

Dimensional Fact Model

Answer 7

Graphical model supporting conceptual design.
Fact schema modelling:
- Dimensions
- Hierarchies
- Measures
DRAW IT

Question 8

DFM: Dimension

Answer 8

Attributes of a fact, typically categorical.

Question 9

DFM: Fact

Answer 9

Event of interest.
Evolves with time.

Question 10

DFM: Measure

Answer 10

A numerical property of a fact, typically aggregated.

Question 11

Additive vs. Non Additive

Answer 11

Non additive cannot be aggregated along a given hierarchy by means of the SUM operator

Question 12

Distributive vs. Non DIstributive (Algebraic)

Answer 12

Algebraic can compute higher level aggregations from more detailed data only when supplementary support measures are available, such as AVG.

Question 13

Star Schema

Answer 13

Draw it.

Question 14

Snowflake Schema vs. Star Schema

Answer 14

• Snowflake usually not recommended
• Partitions dimension tables in multiple extra-tables.

Question 15

SQL: Range vs. Rows

Answer 15

Range counts on the past record, the rows past values (not ideal for data with gaps)

Question 16

ROWS option1 option2

Answer 16

Option 1:
- BETWEEN
- UNBOUNDED

Option 2:
N PRECEDING
M FOLLOWING

Question 17

ROWS N PRECEDING = how many rows?

Answer 17

N + current row = N+1

Question 18

Materialized View

Answer 18

Precomputed summary of the fact table

Question 19

SQL: Divide a partition in N subgroups

Answer 19

NTILE(N)

Question 20

SQL: Add index to a partition

Answer 20

ROW_NUMBER

Question 21

SQL: Add 0-1 indexes

Answer 21

CUME_DIST

Question 22

SQL: Group by all possible combinations

Answer 22

CUBE

Question 23

SQL: Group by -> (C, M, P) (C, M) (C)

Answer 23

ROLLUP(C, M ,P)

Question 24

SQL: Group by using different (specified) combinations

Answer 24

GROUPING SETS

Question 25

ETL Techniques (types of triggering)

Answer 25

• Trigger: New or modified data
• Periodically
• Application Assisted

Question 26

Create a materialized view

Answer 26

CREATE MATERIALIZED VIEW MyView
BUILD ________
REFRESH ________
AS
SQL QUERY

Question 27

Create Trigger

Answer 27

CREATE TRIGGER TriggerName Mode Event ON TargetTable [ REFERENCING ReferenceName ]
FOR EACH Level
WHEN Predicate
DECLARE
BEGIN
Procedural SQL Statements
END

Question 28

Create materializer view log

Answer 28

CREATE MATERIALIZED VIEW LOG ON ________
WITH SEQUENCE, ROWID
(ForeignKeys, Measures)
INCLUDING NEW VALUES;

Question 29

SQL: BUILD _________

Answer 29

• Immediate
• Differed

Question 30

SQL: REFRESH _________

Answer 30

• COMPLETE: All data
• FAST: Only changes
• FORCE: Try FAST, else COMPLETE
• NEVER ON COMMIT: “ “
• ON COMMIT: When SQL ops affect content
• ON DEMAND: When asked

Question 31

SQL: Delete DB

Answer 31

DROP