Article - Multidimensional Database Technology

Question 1

When is a multidimensional data model better suitable than a relational data model?

Answer 1

• When the objective is to analyze data rather than to perform online transactions.
• Key factor in the interactive analysis of large amounts of data for decision-making purposes.

Question 2

How is data categorized in multidimensional models?

Answer 2

• As facts with associated numerical measures
  Fact: a purchase | Measure: purchase amount/price
• As textual dimensions that characterize the facts
  Fact: A puchase | Textual dimensions: Purchase time & location

Question 3

What three application areas are there for multidimensional data models?

*Means: In which processes are they used?

Answer 3

1. Data warehouses
   Large repositories that integrate data from several sources in an enterprise for analysis
2. Online analytical processing (OLAP)
   Finding overall trends by aggregating data search queries
3. Data mining applications
   To discover knowledge by searching for unknown patterns and relationships in multidimensional databases

Question 4

What is a pivot table?

Answer 4

A two-dimensional spreadsheet with associated subtotals and totals that supports viewing more complex data by nesting several dimensions on the x- or y-axis and displaying data on multiple pages.

Question 5

Why are spreadsheets not adequate for managing and storing multidimensional data?

Answer 5

They tie data storage to tightly to the presentation - they do not seperate the structural information from the desired views of the information.

Question 6

Why are relational databases and SQL databases not adequate for managing and storing multidimensional data?

Answer 6

• The use of aggregation functions can be difficult

- It is harder to combine all the dimensions you are interested in

Question 7

What is a multidimensional database/data warehouse built up?

Answer 7

A collection of related cubes

Question 8

Why can cubes easily manage the addition of new dimension values?

Answer 8

Because dimensions in a cube are first-class, built-in concepts with associated domains

Question 9

How many dimensions can a cube have?

Answer 9

Unlimited, but current tools start having performance problems with more than 10 dimensions.

Question 10

When do cubes become more sparse?

Answer 10

As there are more dimensions added to the cube and when the level of detail (granularity) becomes finer

Question 11

How many dimensions can you view of a cube?

Answer 11

Generally, only two or three. But it can show up to 4 low-cardinality dimensions by nesting one dimension within another on the axis.

Question 12

What is an important use of dimensions in a multidimensional database?

Answer 12

To use dimensions to provide as much context as possible for facts.

Question 13

Why are data redundancies sometimes okay in multidimensional databases?

Answer 13

It is okay if it increases the data’s information value.

-> Since the data is derived from other sources, and not born into the multidimensional database, redundancy problems related to updates can be managed more readily

Question 14

Where is in general redundancy in a multidimensional database?

Answer 14

In the dimensions, not the facts.

Question 15

What is the relation of hierarchy and dimension?

Answer 15

Dimensions are used for selecting and aggregating data at the desired level of detail. These levels of details are part of the hierarhy (time -> year, month, day).

-> Sometimes define multiple hierarchies for a dimension (fiscal year and calender year)

Question 16

How do you avoid duplicate definitions when defining multiple hierarchies for dimension?

Answer 16

The metadata of a cube defines the dimension hierarchy.

Question 17

How can you avoid adding dimensions to a cube if you want to add extra information?

Answer 17

Add a property that you initially wanted as a dimension as a level under an existing dimension:

Dimension: product –> you add the level of package size instead of making package-size an dimension.

Question 18

What kind of ordering is available in a multi-dimensional database?

Answer 18

• Typically no ordering
• Only ordering is by hierarchy
• Ordering can happen by cumalative information, such as total sales to date.

Question 19

What are facts?

Answer 19

The representation of the subject for a specific combination of dimensions

Question 20

How is the granularity of a fact determined?

Answer 20

By the levels from which its combination of dimension values is drawn.

Question 21

Which three types of facts are commonly included in data warehouses?

Answer 21

1. Events
   sales, clicks on a page, movement of goods
2. Snapshots
   An entity at a given point in time.
   Warehouse inventory levels at a time
3. Cumulative snapshots
   Information about an activity up to a certain moment.
   Total sales up to and including the current month

–> Because these 3 are complementary, a database often has them all.

Question 22

Of which two components does a measure consist?

Answer 22

1. A facts numerical property (sales price, profit)

2. A formula that can combine several measure values into one (aggregation function sum)

Question 23

What is a measure?

Answer 23

A representation of the properties of the fact that the user wants to optimize.

-> Takes on different values for various dimension combinations.

Question 24

Which three classes of measures behave differently in computations?

Answer 24

1. Additive
2. Semiadditive
3. Non-additive

Question 25

How do additive measures behave in computations?

Answer 25

They can be meaningfully combined along any dimension, as it does not cause an overlap among the real-world phenomena.
(to add total sales for product, location and time)

Question 26

How do semiadditive measures behave in computations?

Answer 26

They can not be combined along one or more dimensions.

• > You can sum up inventory across products and warehouses.
• > you can not sum up inventory levels across time, cause you will count inventory several times then.

Question 27

How do nonadditive measures behave in computates?

Answer 27

They can not be combined along any dimension, usually because the chosen formula prevents combining lower-level averages into higher-level averages.

Question 28

Which measure(s) can occur for any kind of fact?

Answer 28

Additive and nonadditive facts

Question 29

Which measure(s) can occur only for snapshot or cumalative snapshot facts?

Answer 29

Semi-additive facts

Question 30

Which 5 types of querying are there in a multidimensional database?

Answer 30

1. Slice-and-dice
2. Drill-down and roll-up
3. Drill-across
4. Ranking
5. Rotating

Question 31

What is the slice and dice query?

Answer 31

Making selections to reduce a cube.

-> slice the cube to only consider those sells concering bread

Question 32

What is the drill-down and roll-up query?

Answer 32

Inverse operations that use dimension hierarcies and measures to perform aggregations.

Select country instead of cities. If there are two cities in a country, the numbers of these cities are added up to get to the country number.

Question 33

What is the drill-across query?

Answer 33

Combining cubes that share one or more dimensions (joining)

Question 34

What is the ranking query?

Answer 34

Returning the cells that appear at the top or bottom of a specified order.

Question 35

What is the rotating query?

Answer 35

Rotating to see the data grouped by another dimension

Question 36

Which two basic forms are there for multidimensional database implementation?

Answer 36

• MOLAP systems
  They include provisions for handling sparse arrays and apply advanced indexing and hashing to locate the data when performing queries
• ROLAP systems
  Using relational database technology for storing data, employing specialized index structures to achieve good query performance

Question 37

What are the benefits of a MOLAP implementation?

Answer 37

• More space-efficient storage;

- Faster query response times;

Question 38

What are the benefits of a ROLAP implementation?

Answer 38

• Better scalability in the number of facts they can store.
• More flexible with cube redefinitions
• Provide better support for frequent updates

Question 39

What does a ROLAP implementation typically need to employ?

Answer 39

A star or a snowflake model.

Question 40

Which two assumptions do traditional multidimensional data models and implemenation techniques have?

Answer 40

1. All facts map directly to the lowest level dimension values and only to one value in each dimension
2. Dimension hierarchies are balanced trees

Question 41

Why is complex multidimensional data problematic?

Answer 41

It is not summarizable.

Question 42

When is a dimension hierarchy considered strict?

Answer 42

If no dimension value has more than one direct parent

Question 43

When is a dimension hierarchy considered onto?

Answer 43

If the hierarchy is balanced

Question 44

When is a dimension hierarchy considered covering?

Answer 44

If no containment paths skips a level.

Question 45

When is a dimension hierarchy irregular?

Answer 45

If a lower level is not connected to the middle level for all data points (Country - State - City) –> Denmark does not have states.

Or if a lower level is connected to two middle levels.

(see figure 5)

Question 46

How can you solve irregular dimensions?

Answer 46

By normalizing the hierarchies –> adding in dummy variables.

Question 47

What are trends in the multidimensional database world?

Answer 47

1. Data that must be analyzed is getting increasingly distributed. –> need for more integrated data warehouses
2. It is being applied to data that current technology can not adequately analyze yet, such as data from sensors of moving objects (GPS)
3. Multidimensional database technology will increasingly be applied where analysis results are fed directly into other systems, thereby eliminating humans from the loop.