5 - Data Analysis in SQL

Question 1

What are the main goals of OLAP?

Answer 1

• Decision support to knowledge worker (analyse company data to discover patterns/trends)
• based on integrated data warehouse [separate from OLTP systems -> periodic refresh)
  
  • > Organize and centralize corporate data (historical) from various heterogeneous sources (ETL)

Question 2

Compare OLAP applications with OLTP applications.

Answer 2

OLTP: many simple (point update) queries, small amounts of data, current data, fast update + high (transaction) throughput
OLAP: few complex queries (repeated), large data, current+historical data, fast calculation + load throughput + query tthroughput

Question 3

What is a Data Warehouse and its basic architecture?

Answer 3

Separate datastore modeled by multidimensional historical data from various sources, supporting OLAP.
SOURCES -> ETL -> WAREHOUSE -> TOOLS

Question 4

Explain the goals of multidimensional Data Modelling.

Answer 4

• > set of numerical measures associated to a combination of (hierarchical) dimensions
• > Dimensions group and qualify numerical fact data, aggregating measures on the groups

MOLAP (specific storage for multidimensional data) vs ROLAP (relies on RDBMS capabilities, enhanced SQL).

Question 5

What is the role of the multidimensional cube in OLAP and its supported operations?

Answer 5

• > way to arrange/interpret multidimensional data: each dimension in an axis
  • > points contain measures (cube cells)
• > rollup/drill-down: increase/decrease level of aggregation (day month, country city)
• > Slice/dice: selection and projection (fix one dimension/restrict (internal) one or more dim.)
• > Split/Merge: rem/add dimensions.

Question 6

What are the main concepts of Multidimensional Data Modelling?

Answer 6

MD Schema: set of dimensions + set of measures (of a cube)

**dimensions: partially ordered set of category attributes (primary(finest granule), classification (hierarchy), dimension(additional info))

Question 7

Explain and compare the terms Star Schema and Snowflake Schema.

Answer 7

Ways of modelling MD data in RDBMS
Star:
* fact table on the center has pointers to dimension tables and associated measures
* dimension attributes on dimension table -> more performance
Snowflake:
* Dimension tables are normalized, reflecting dimension hierarchy
* less redundant

Question 8

What is the idea of slice and dice operations?

Answer 8

cub: join fact table with dimension tables
- > retrieve portions of interest from the cube (in WHERE clause)
- > slice: fix one dimension -> dice: restrict values to an internal

• > choice of partitioning: GROUP BY
• > drill-down, rollup common in slice/dice queries

drill-down: finer partitions
rollup: coarser partitions

Question 9

What is the main functionality provided by the SQL OLAP extensions?

Answer 9

extesions to GROUP BY: ROLLUP, CUBE, GROUPING SETS (grouping by different sets of attributes)

Question 10

Give an overview over the ROLLUP operator.

Answer 10

Extends grouping semantics to combination of groups

GROUP BY ROLLUP (month, city, producer)

• > producer within city within month (normal GROUP BY)
• > City within month (producer = null)
• > month (city, producer=null)
• > grand total (all null)

Question 11

Give an overview over the CUBE operator.

Answer 11

Extends ROLLUP to produce multidimensional grouping (all group combinations)
GROUP BY CUBE (month, city, producer) = m,c,p+m,c+m,p+c,p+m+c+p+0

Question 12

What are grouping sets and how do they generalize other group operators?

Answer 12

Allow multiple groupings on a single scan (pass)
-> arbitrary definition of desired groups

ROLLUP (a,b,c) = GROUPING SETS ((a,b,c), (a,b), (a), ())
**() -> special syntax for grand total
CUBE(a,b,c) = GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b), (c), ())

Question 13

How does the analysis of functional dependencies provide more flexibility to expressing aggregations in SQL queries?

Answer 13

GROUP BY rule: columns projected must either be agg functions or grouped
-> functional dependency analysis may detect that column value is unique within group, allowing violation of the rule.