MS SQL Data Mining Interview Questions And Answers

MOLAP Multidimensional Online Analytical processing
In MOLAP data is stored in form of multidimensional cubes and not in relational databases.

Advantage
Excellent query performance as the cubes have all calculations pre-generated during creation of the cube.

Disadvantages
It can handle only a limited amount of data. Since all calculations have been pre-generated, the cube cannot be created from a large amount of data.

It requires huge investment as cube technology is proprietary and the knowledge base may not exist in the organization.

ROLAP Relational Online Analytical processing
The data is stored in relational databases.

Advantages
It can handle a large amount of data and
It provides all the functionalities of the relational database.

Disadvantages
It is slow.
The limitations of the SQL apply to the ROLAP too.

HOLAP Hybrid Online Analytical processing
HOLAP is a combination of the above two models. It combines the advantages in the following manner:

For summarized information it makes use of the cube.
For drill down operations, it uses ROLAP.

Deleting data from data warehouse is known as data purging. While loading data into staging or in the target table fresh data loading may be needed every time. In this scenario, data purging is needed to stage or target table prior to loading fresh data. Usually junk data like rows with null values or spaces are cleaned up. Data purging is the process of cleaning this kind of junk values.

Data mining can be used in a variety of fields/industries like marketing, advertising of goods, products, services, AI, government intelligence.

The US Federal Bureau of Investigation uses data mining for screening security and intelligence for identifying illegal and incriminating e-information distributed over internet.

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.

Custom rollup operators provide a simple way of controlling the process of rolling up a member to its parents values.The rollup uses the contents of the column as custom rollup operator for each member and is used to evaluate the value of the member’s parents.

If a cube has multiple custom rollup formulas and custom rollup members, then the formulas are resolved in the order in which the dimensions have been added to the cube.

OLTP: Online Transaction and Processing helps and manages applications based on transactions involving high volume of data. Typical example of a transaction is commonly observed in Banks, Air tickets etc. Because OLTP uses client server architecture, it supports transactions to run cross a network.

OLAP: Online analytical processing performs analysis of business data and provides the ability to perform complex calculations on usually low volumes of data. OLAP helps the user gain an insight on the data coming from different sources (multi dimensional).

Data warehousing is merely extracting data from different sources, cleaning the data and storing it in the warehouse. Where as data mining aims to examine or explore the data using queries. These queries can be fired on the data warehouse. Explore the data in data mining helps in reporting, planning strategies, finding meaningful patterns etc.

E.g. a data warehouse of a company stores all the relevant information of projects and employees. Using Data mining, one can use this data to generate different reports like profits generated etc.

Exploration: This stage involves preparation and collection of data. it also involves data cleaning, transformation. Based on size of data, different tools to analyze the data may be required. This stage helps to determine different variables of the data to determine their behavior.

Model building and validation: This stage involves choosing the best model based on their predictive performance. The model is then applied on the different data sets and compared for best performance. This stage is also called as pattern identification. This stage is a little complex because it involves choosing the best pattern to allow easy predictions.

Deployment: Based on model selected in previous stage, it is applied to the data sets. This is to generate predictions or estimates of the expected outcome.

Data warehousing can be used for analyzing the business needs by storing data in a meaningful form. Using Data mining, one can forecast the business needs. Data warehouse can act as a source of this forecasting.

Models in Data mining help the different algorithms in decision making or pattern matching. The second stage of data mining involves considering various models and choosing the best one based on their predictive performance.

Discreet data can be considered as defined or finite data. E.g. Mobile numbers, gender. Continuous data can be considered as data which changes continuously and in an ordered fashion. E.g. age.