Big Data Lecture 14 Data Cubes

Question 1

What is OLTP?

Answer 1

Online transaction processing, both read and write intensive system in real time, example is relational database.

Question 2

What is OLAP?

Answer 2

Online analytical processing, we want to agreggate data, can be not fully online, example are Data Cubes.

Question 3

What are the main differences of OLTP and OLAP in IO?

Answer 3

OLTP writes mostly, OLAP reads often.

Question 4

What is the difference of OLAP and OLTP in detail of information it provides about data?

Answer 4

OLTP has detailed individual records, OLAP historical summarized consolidated data.

Question 5

What is the difference of OLTP and OLAP in terms of size of transactions?

Answer 5

OLTP has small transactions, OLAP has few huge queries.

Question 6

What is the difference of OLAP and OLTP in execution time?

Answer 6

OLTP is interactive, OLAP takes its time.

Question 7

What is the difference of OLAP and OLTP in terms of duplication and denormalization?

Answer 7

OLTP is consistent, OLAP has redundant data.

Question 8

What 4 adjectives define OLAP?

Answer 8

<ul><li>Subject oriented: usually for one use case (web analytics, sales...),</li><li>time-variant: the data tends to be historical,</li><li>integrated: loads data from many different sources,</li><li>non-volatile: the data is locked once it is loaded.</li></ul>

Question 9

What is the main data shape in OLAP?

Answer 9

Data cube! Multidimensional object with many dimensions, which are indexed by members.<br></br>E.g. YEAR x PRODUCT x COUNTRY identifies a unique product sold.

Question 10

What is a fact table?

Answer 10

Table that stores for each coordinate combination a value.

Question 11

What is a measurement?

Answer 11

Value stored in the table.

Question 12

What is slicing?

Answer 12

Taking a slice of the cube over some dimensions.

Question 13

What is dicing?

Answer 13

Reformatting the table such that the axis of the table become the dimensions, this is called the cross-tabulated view.

Question 14

How is aggregation from the POV of the cube?

Answer 14

It is like squeezing a cube along one dimension.

Question 15

What is a roll-up?

Answer 15

We combine two categories in aggregation together, to create a lower dimensional view of the data.

Question 16

What is a drill-down?

Answer 16

We make the data more granular, by considering more subvariables in each category, making the view more precise, less aggregated.

Question 17

What is the difference of ROLAP and MOLAP?

Answer 17

Relational OLAP (built on top of RDBMS) and Multidimensional OLAP (natively deals with multiple dimensions).

Question 18

What is pivoting (and unpivoting)?

Answer 18

Pivoting takes dimensions and makes the associated values into a different measurements. Unpivoting reverses that.

Question 19

What is a star schema of tables?

Answer 19

We keep one main table with all dimensions, and then we have <i>satellite tables </i>which reference the remaining data.

Question 20

What is snowflake schema?

Answer 20

Within each table of the star schema, we further normalize the data into more tables.

Question 21

What is MDX?

Answer 21

Stands for Multi-Dimensional eXpressions. The query language for Data Cubes.

Question 22

How to roll up in SQL?

Answer 22

Use group by, and the dimensions that you want to keep insert into the groupings.

Question 23

How to cube in SQL?

Answer 23

You can ‘GROUP BY GROUPING SETS’ and then denote all the combinations, or ‘GROUP BY CUBE’ and just input the attributes, it will produce the 2^n different sets we need to group on.

Question 24

How to roll up in SQL?

Answer 24

You can use ‘GROUP BY ROLLUP’ that produces all the attributes for (n+1) types that we need.

Question 25

How do authorities deal with fancy reports from companies to make the readeable to the system?

Answer 25

They let them produce the reports in XHTML, and make them annotate the important attributes for parsing. Later, data cubes can be used for simple aggregation online.