[Database/Hackerrank] OLAP Performance

OLAP Performance

Which of these helps OLAP speed up queries, in terms of performance?

=> 2. Aggregation

 

OLAP Operations - 1

This OLAP operation involves computing all of the data relationships for one or more dimensions.

=> 4. roll-up

 

OLAP Operations - 2

This OLAP Operation rotates the data, and delivers an alternative to the original presentation. 

=> 1. pivot 

 

OLAP Cube Metadata

What is the source of the cube metadata for OLAP?

=> 4. Both star and snowflake schema(s)

 

star schema - dw 스키마 중 가장 단순한 종류의 스키마로 한 개의 테이블과 primary key, dimension으로 이루어짐. 복잡도가 낮아서 이해하기 쉽고 쿼리 작성이 용이하다. 조인 테이블의 개수가 적다. 비정규화로 인해 데이터 적재 소요 시간이 길다.

snowflake schema - dimension 테이블을 제3규형으로 정규화한 상태로 데이터 중복이 제거되어 데이터 적재 소요 시간은 짧으나 복잡도가 증가하여 조인 테이블이 많아지고 쿼리가 복잡해 진다.

 

OLAP Name(s)

Which of these are alternate names for an OLAP Cube? The options in the top row are a and b respecitvely and those in the bottom row are c and d.

=> 4. Both (B) and (C)

 

The Total View

Which of these provides a total view of the organization?

=> 3. Data Warehousing

OLAP Operation Types

Consider a fact table DataPoints(D1,D2,D3,x), and the following three queries:

 

Q1: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3
Q2: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH CUBE
Q3: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH ROLLUP

 

Suppose attributes D1, D2, and D3 have n1, n2, and n3 different values respectively, and assume that each possible combination of values appears at least once in the table DataPoints. The number of tuples in the result of each of the three queries above can be specified as an arithmetic formula involving n1, n2, and n3. Pick the one tuple (a,b,c,d,e,f) in the list below such that when n1=a, n2=b, and n3=c, then the result sizes of queries Q1, Q2, and Q3 are d, e, and f respectively.

=> 4. (4, 7, 3, 84, 160, 117) 

-- ROLLUP: a*b*c + (a*b)             + (a)     + 1
-- CUBE:   a*b*c + (a*b + a*c + b*c) + (a+b+c) + 1