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 .

11. In SQL the cross-tabs are created using:
a) Slice
b) Dice
c) Pivot
d) All of the mentioned

a) Slice
Explanation:
pivot (sum(quantity) for color in ('dark','pastel','white')).

12. The operation of moving from finer-granularity data to a coarser granularity (by means of aggregation) is called a ________.
a) Roll-up
b) Drill down
c) Dicing
d) Pivoting

a) Roll-up
Explanation:
The opposite operation-that of moving from coarser-granularity data to finer-granularity data-is called a drill down.

13. The process of viewing the cross-tab (Single dimensional) with a fixed value of one attribute is.
a) Slicing
b) Dicing
c) Pivoting
d) Both a and b

d) Both a and b
Explanation:
For eg., The item name and colour is viewed for a fixed size.

14. The generalization of cross-tab which is represented visually is ____________ which is also called as data cube.
a) Two dimensional cube
b) Multidimensional cube
c) N-dimensional cube
d) Cuboid

a) Two dimensional cube
Explanation:
Each cell in the cube is identified for the values for the three dimensional attributes.

15. OLAP stands for:
a) Online analytical processing
b) Online analysis processing
c) Online transaction processing
d) Online aggregate processing

a) Online analytical processing
Explanation:
OLAP is the manipulation of information to support decision making.

16. It is because of __________ that enables OLAP to achieve great performance for a query.
a) Aggregation
b) Composition
c) Dice
d) Hybrid

a) Aggregation

17. Aggregations are built from the _________________ by changing the granularity on specific dimensions.
a) Both c and d below
b) schema
c) Dimension table
d) Fact table

d) Fact table

18. _____operation of OLAP provides alternate presentation of data by rotating it.
a) Slice
b) Roll up
c) Dice
d) Pivot

d) Pivot

19. What is the source of the OLAP cube's metadata?
a) Star schema
b) Snow flake schema
c) Both a and b
d) Database

c) Both a and b

20. __________operation of OLAP involves computing all of the data relationships for one or more dimensions.
a) Slice
b) Roll up
c) Dice
d) Pivot

b) Roll up

21. Can the user perform data-entry or editing tasks on OLAP data?
a) Yes
b) No

b) No

22. Following are three steps in random order for creating a data cube.
1. Chose a data source.
Create the query that extracts data from the database.
Create the cube from the extracted data.

a) 1,2,3
b) 2,1,3
c) 3,2,1
d) 3,1,2

a) 1,2,3

23. Dice operation is also known as rotate.
a) True
b) False

b) False

24. The output of an OLAP query is displayed as a:
a) Matrix
b) Pivot
c) Both a and b
d) excel

c) Both a and b

25. Which type of data models are used by databases configured for OLAP?
a) Multidimensional
b) Single dimensional
c) Two dimensional
d) Three dimensional

a) Multidimensional

26. MOLAP databases generally give better performance.
a) True
b) False

a) True

27. What are the applications of OLAP?
a) business reporting for sales, marketing
b) Budgeting
c) Forecasting
d) All of the above

d) All of the above

28. What is sparsity?
a) A condition when each cell of the cube is filled with data and that leads to more processing time.
b) A condition when each cell of the cube is not filled with data and that leads to less processing time.
c) A condition when each cell of the cube is not filled with data and that leads to more processing time.
d) A condition when each cell of the cube is not filled with data.

a) A condition when each cell of the cube is filled with data and that leads to more processing time.

29. What is the other name for OLAP cube?
a) Multidimensional cube
b) Hyper cube
c) Cube
d) Both 1 and 2

d) Both 1 and 2

30. Which of the following below is/are OLAP vendors?
a) Cognos
b) Infor
c) Oracle corporation
d) All of the above

d) All of the above

31. What are the different industries which use this marketing tool?

Many different companies can use this tool for developing their business strategy but it is often three major industries which use this tool more. Those three industries are Consumer goods industries, Retail industries, and financial services industry. These industry`s have huge amount of data in their disposal which makes then to use these tools to determine their exact customer.

32. Explain about the database marketing application of OLAP?

Database marketing tool or application helps a user or marketing professional in determining the right tool or strategy for his valuable add campaign. This tool collects data from all sources and gives relevant information the specialist with their add campaign. It gives a complete picture to the developer.

33. Explain about multidimensional features present in OLAP?

Multidimensional support is very essential if we are to include multiple hierarchies in our data analysis. Multidimensional feature allows a user to analyze business and organization. OLAP efficiently handles support for multidimensional features.

34. Explain about analysis?

Analysis defines about the logical and statistical analysis required for an efficient output. This involves writing of code and performing calculations, but most part of these languages does not require complex programming language knowledge. There are many specific features which are included such as time analysis, currency translation, etc.

35. Explain about shared features of OLAP?

Shared implements most of the security features into OLAP. If multiple accesses are required admin can make necessary changes. The default security level for all OLAP products is read only. For multiple updates it is predominant to make necessary security changes.