Dear Readers, Welcome to OLAP Interview Questions and Answers have been designed specially to get you acquainted with the nature of questions you may encounter during your Job interview for the subject of OLAP. These OLAP Questions are very important for campus placement test and job interviews. As per my experience good interviewers hardly plan to ask any particular questions during your Job interview and these model questions are asked in the online technical test and interview of many IT companies.
OLAP is known as online analytical processing which provides answers to queries which are multi dimensional in nature. It composes relational reporting and data mining for providing solutions to business intelligence. This term OLAP is created from the term OLTP.
Hyper cube or multidimensional cube forms the core of OLAP system. This consists of measures which are arranged according to dimensions. Hyper cube Meta data is created by star or snow flake schema of tables in RDBMS. Dimensions are extracted from dimension table and measures from the fact table.
Classic form of OLAP is known as MOLAP and it is often called as OLAP. Simple database structures such as time period, product, location, etc are used. Functioning of each and every dimension or data structure is defined by one or more hierarchies.
Functioning of ROLAP occurs simultaneously with relational databases. Data and tables are stored as relational tables. To hold new information or data new tables are created. Functioning of ROLAP depends upon specialized schema design.
OLAP can process complex queries and give the output in less than 0.1 seconds, for it to achieve such a performance OLAP uses aggregations. Aggregations are built by aggregating and changing the data along the dimensions. Possible combination of aggregations can be determined by the combination possibilities of dimension granularities.
Often calculating all the data is not possible by aggregations for this reason some of the complex data problems are solved. In order to determine which data should be solved and calculated, developers use View selection application. This solution is often used to reduce calculation problem.
Bitmaps are very useful in start schema to join large databases to small databases. Answer queries and bit arrays are used to perform logical operations on the databases. Bit map indexes are very efficient in handling Gender differentiation; also repetitive tasks are performed with much larger efficiency.
Bitmaps commonly use one bitmap for every single distinct value. Number of bitmaps used can be reduced by opting for a different type of encoding. Space can be optimized but when a query is generated bitmaps have to be accessed.
Binning process is very useful to save space. Performance may vary depending upon the query generated sometimes solution to a query can come within few seconds and sometimes it may take longer time. Binning process holds multiple values in the same bin.
The process which is underlined during the check of base data is known as candidate check. When performing candidate check performance varies either towards the positive side or to the negative side. Performance of candidate check depends upon the user query and also they examine the base data.
When a database developer uses Hybrid OLAP it divides the data between relational and specialized storage. In some particular modifications a HOLAP database may store huge amounts of data in its relational tables. Specialized data storage is used to store data which is less detailed and more aggregate.
Microsoft in the late 1997 introduced a standard API known as OLE DB. After which XML was used for analysis specification and this specification was largely used by many vendors throughout the world as a standard specification. MDX is the standards specification for 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.
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.
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.
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.
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.
The following are the OLAP database objects:
1. Cubes: Data in cubes are persisted in a summarized version that helps to analyze data quickly. The data is persisted, through which reporting can be done easily.
2. Data Sources: The data source is location, from which data comes in data warehousing. Data is collected from different resources and cleaned. This data source could be internal or external. Cleansing of source data and efficient analysis is the prime process for data warehousing.
3. Fact Tables: Fact table consists of facts and / or measures in data warehousing. Usually the data is stored in numeric fashion. For example, the number of resources used for a task is stored as actual measure.
4. Database roles: The database security is managed by utilizing database level roles. These roles may be fixed or flexible. The fixed roles are predefined where as flexible roles can be created.
Online Analytical Processing is a powerful and popular data analytical method. Complex data structures are explored and provide the necessary information.
Multidimensional: OLAP provides services in a wide variety of possible views, which are multidimensional conceptual view of data by supporting multiple hierarchies or dimensional aggregation path are provided.
Easy to understand: The data designed for OLAP analysis will be handled by any business logic and statistical analysis which is relevant to the developer and / application user. Simultaneously, for the target user, it makes easy enough.
Interactive: OLAP supports the business information through comparative data to the user. Users are encouraged for defining new ad hoc calculations which is a part of the analysis.
Fast: OLAP services are implemented in a multi-user client / server architecture and provide rapid responses to queries consistently, irrespective of database complexity.
OLAP functionality is performed using SQL Anywhere by utilizing various extensions to SQL statements and window functions. Multidimensional data analysis, data mining, trend analysis, goal seeking, cost allocations, time series analyses and altering exceptions can be performed with a single SQL statement.
Extensions to SELECT statement: Grouping input rows, analyze the groups and including the findings in the final result, are the operations that could be done in SELECT statement. They include extensions to GROUP BY clause – GROUING SETS, CUBE and ROLLUP clauses and WINDOW clause.
WINDOW aggregate functions: Configurable sliding window concept is supported for using aggregate functions, which moves down the input rows as they are processed. Computing percentiles, moving averages and cumulative sums are performed in a single SQL statement, which reduces the complexity of using self-joins, correlated sub queries, temporary tables and at times, the combination of all these three.
Window ranking functions: These functions facilitates to form a single statement SQL queries , to obtain the information , like shipped top ten products in a given year by total sales.
MOLAP: A more traditional way of OLAP analysis. Data is persisted in a multidimensional cube in MOLAP. The storage is in proprietary formats but not in the relational database. MOLAP data cubes are built in such a way that data retrieval is faster and are optimal for dicing and slicing operations.
ROLAP: A methodology that is relied on manipulating the persisted data in the relational database, for providing an appearance of traditional OLAP’s dicing and slicing functionality. The actions of slicing and dicing are equivalent to adding the ‘WHERE’ clause in the SQL statement, is the essential part of ROLAP. The amount of data is not limited by ROLAP itself, thus able to handle large amounts of data..
Bitmaps are useful for starting schema for the purpose of joining large databases small databases. For performing logical operations on the databases, bit arrays and the answer queries are used. To handle Gender differentiation, bit map indexes are efficient. Bit map indexes also capable of performing repetitive tasks with much larger efficiency.