Operational OLAP Interview Questions & Answers:

1. What is OLAP?

OLAP is an acronym for Online Analytical Processing. OLAP performs multidimensional analysis of business data and provides the capability for complex calculations, trend analysis, and sophisticated data modeling.

2. What are Cubes in OLAP?

A data cube stores data in a summarized version which helps in a faster analysis of data. The data is stored in such a way that it allows reporting easily. E.g. using a data cube a user may want to analyze weekly, monthly performance of an employee. Here, month and week could be considered as the dimensions of the cube.

3. Where is Data Sources in OLAP?

Data source is where the data comes from in data warehousing. The data collected from various sources and is cleaned. The data source can be internal or external. Efficient Analysis and cleansing of source data is the key success to data warehousing.

4. What are Fact Tables?

Data in a warehouse comes from the transactions. Fact table in a data warehouse consists of facts and/or measures. The nature of data in a fact table is usually numerical. e.g. If I want to know the number of resources used for a task, my fact table will store the actual measure (of resources) while my Dimension table will store the task and resource details.

5. What are Database roles in OLAP?

Database level roles are used to manage the security of the database. The role can be either fixed or flexible. Fixed roles are predefined while flexible roles can be created. Examples of some fixed database level roles are db_owner, db_securityadmin, db_datawriter etc.

6. Data that can be modeled as dimension attributes and measure attributes are called _______ data.
a) Multidimensional
b) Singledimensional
c) Measured
d) Dimensional

a) Multidimensional
Explanation:
Given a relation used for data analysis, we can identify some of its attributes as measure attributes, since they measure some value, and can be aggregated upon.Dimension attribute define the dimensions on which measure attributes, and summaries of measure attributes, are viewed.

7. Which one of the following is the right syntax for DECODE ?

a) DECODE (search, expression, result [, search, result]… [, default])
b) DECODE (expression, result [, search, result]… [, default], search)
c) DECODE (search, result [, search, result]… [, default], expression)
d) DECODE (expression, search, result [, search, result]… [, default])

d) DECODE (expression, search, result [, search, result]… [, default])

8. Select item name, color, clothes size, sum(quantity)
from sales group by rollup(item name, color, clothes size);
How many grouping is possible in this rollup?
a) 8
b) 4
c) 2
d) 1

b) 4
Explanation:
{ (item name, color, clothes size), (item name, color), (item name), () }.

9. What do data warehouses support?
a) OLAP
b) OLTP
c) OLAP and OLTP
d) Operational databases

a) OLAP

10. { (item name, color, clothes size), (item name, color), (item name, clothes size), (color, clothes size), (item name), (color), (clothes size), () }
This can be achieved by using which of the following?

a) group by rollup
b) group by cubic
c) group by
d) None of the mentioned

d) None of the mentioned
Explanation:
'Group by cube' is used .