OLTP & OLAP

Question 1

3 Type of Subject-oriented schema

Answer 1

Star Scheme
Snowflake Scheme
Galaxy Scheme

Question 2

Workload of OLTP & OLAP

Answer 2

Heavy write, Low Read
Heavy Read, Low write

Question 3

Architectural of OLTP & OLAP

Answer 3

OLTP - 3-tier architecture.
OLAP - Semantic Model Architecture

Question 4

3 Type of OLAP

Answer 4

Multi-dimensional OLAP (MOLAP)
Relational OLAP (ROLAP)
Hybrid OLAP (HOLAP)

Question 5

3 Component of OLAP Architecture

Answer 5

OLAP Graphical User Interface (GUI)
OLAP Analytical Processing Logic (APL) - Placed on client side
OLAP Data Processing Logic (DPL) - Place on server side; Map data analysis request.

Question 6

Data Warehouse

Answer 6

A subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision-making process

Question 7

5 Reason of Using Data Warehouse

Answer 7

Reduces stress on transactional systems.
Keeps historical data
Provides single version of truth
Improves data quality
Enables complex analysis for business

Question 8

2 Type of Hierarchy

Answer 8

Total order
Partial order

Question 9

Aim of Big Data Architecture

Answer 9

Designed to handle the ingestion, processing, and analysis of data that is too large or complex for traditional database systems

Question 10

3 Situation to Use Big Data Architecture

Answer 10

Store and process data in volumes too large for a traditional database.
Transform unstructured data for analysis and reporting.
Capture, process, and analyse unbounded streams of data in real time, or with low latency

Question 11

Data cube

Answer 11

A metaphor for multidimensional data storage which allows data to be modelled and viewed in multiple dimensions defined by dimensions and facts.

Question 12

Lowest and Highest Point in Lattice of Cuboids cuboid

Answer 12

Lowest level - Base cuboid
Highest level - Apex cuboid

Question 13

4 Concept Hierarchies of Data Cube

Answer 13

Roll-up / Drill-up - Less dimension

Drill-down - More dimension

Slice and dice
Dice - Defines a subcubby performing a selection on two or more dimensions.
Slice - Performs a selection on one dimension of the given cube.

Pivot (rotate) - Show side view

Question 14

2 Type of Table in Star Scheme

Answer 14

Large central data (Fact table)
Attendant tables (Dimension tables)

Question 15

Pros & Cons of Snowflake Scheme

Answer 15

Pros
Dimension tables easy to maintain and saves storage

Cons
Reduce the effectiveness of browsing

Question 16

Data Warehouse Vs Data Mart

Answer 16

Data Warehouse
Multiple subject
Many data source
Enterprise-wide
Galaxy schema

Data Mart
Single subject
Few data source
Department-wide
Star / Snowflake schema

Question 17

3 Types of Data Mart

Answer 17

Dependent Data Marts - Draw data from a central data warehouse that has already been created.

Independent Data Marts - Draw data directly from operational or external sources of data or both.

Hybrid Data Marts - Draw data from operational systems or data warehouses