OLTP vs OLAP

OLTP vs OLAP

42.What are two examples of an OLTP environment?

On Line Banking On Line Reservation(Transportation like Rail, Air etc.)

 1- ATM. 2- POS.

43.What is the difference between ODS and OLTP?
ODS:- It is nothing but a collection of tables created in the Datawarehouse that maintains only current data.
ODS has broad enterprise wide scope ,but unlike the real enterprise datawarehouse ,data is refreshed in near real time and used for routine business activity
ODS: Operational Data Source/Store, The source from where we will get the data for DWH is called ODS
OLTP maintains the data only for transactions, these are designed for recording daily operations and transactions of a business.
OLTP is online transaction process which comes across daily bases data .
OLTP: We have content(Front End) associated with the back end.a fact table without facts.i,e there is no key measure to analyse the business.
44.Why are OLTP database designs not generally a good idea for a Data Warehouse?
  •  Tables are normalized and hence query response will be slow for end user
  • and OLTP doesn’t contain years of data and hence cannot be analysed.
45.Why should you put your data warehouse on a different system than your OLTP 
  •  A OLTP system is basically ” data oriented ” (ER model) and not “Subject oriented “(Dimensional Model) .
  • That is why we design a separate system that will have a subject oriented OLAP system.Moreover if a complex query is fired on a OLTP system will cause a heavy overhead on the OLTP server that will affect the day today business directly.
  • The loading of a warehouse will likely consume a lot of machine resources. Additionally, users may create queries or reports that are very resource intensive because of the potentially large amount of data available. Such loads and resource needs will conflict with the needs of the OLTP systems for resources and will negatively impact those production systems.
46.What is the difference between OLTP and OLAP?
Data Source
OLTP: Operational data is from original data source of the data
OLAP: Consolidation data is from various sources.
Process Goal 
OLTP: Snapshot of business processes which does fundamental business tasks 
OLAP: Multi-dimensional views of business activities of planning and decision making
Queries and Process Scripts
OLTP: Simple quick running queries ran by users.
OLAP: Complex long running queries by system to update the aggregated data.
Database Design
OLTP: Normalized small database. Speed will be not an issue due to smaller database and normalization will not degrade performance.
This adopts entity relationship(ER) model and an application-oriented database design.
OLAP: De-normalized large database. Speed is issue due to larger database and de-normalizing will improve performance as there will be lesser tables to scan while performing tasks.
This adopts star, snowflake or fact constellation mode of subject-oriented database design.
 User and System Orientation
OLTP: customer-oriented, used for data analysis and querying by clerks, clients and IT professionals.
OLAP: market-oriented, used for data analysis by knowledge workers( managers, executives,analysis)
Data Contents 
OLTP: manages current data, very detail-oriented.
OLAP: manages large amounts of historical data, provides facilities for stigmatization and aggregation, stores information at different levels of granularity to support decision making process.

47.Explain Difference between OLTP and OLAP?

Following Table shows difference between OLAP and OLTP system:

OLAP OLTP
Data Storage:It stores only historical data and historical data processing is done. Data Storage:It involves daily processing of data
Users Of System: OLAP System is used by higher management like managers , analysts , executives,CEO, CFOs Users Of System: OLTP system used by DBAs, Database Professionals ,Programmers for applying business logic.
Key use: OLAP is used to analyse the business Key use: OLTP is used to run the business
Optimization Techniques: OLAP is very huge database so lot of indexes are used for fast data processing Optimization Techniques: OLTP uses less indexing as data is less here
Database Schema: OLAP uses Star-Schema,Snowflakes schema or Fact-Dimensions Database Schema: OLTP uses Entity Relations
Data Information: OLAP contains historical data Data Information: OLTP contains Current data
Join Data: OLAP has less joins and in de-normalized form database Join Data: OLTP has large no of joins and in normalized form
Aggregated Data: OLAP system has aggregated multidimensional data Aggregated Data: OLTP has not aggregated data.
Summarized data: OLAP system gives Summarized consolidated data Detailed Data: OLTP system gives data in detailed format
Data Size: OLAP database size is 100 GB to 100 TB Data Size: OLTP database size is 100 MB to 100 GB

48.What is the difference between OLAP and datawarehosue?

  •  Data warehouse is the place where the data is stored for analyzing 
  • OLAP is the process of analyzing the data,managing aggregations,partitioning information into cubes for in depth visualization.

49.What does OLAP stand for?

OLAP stands for On Line Analytical Processing. It is a system which collects, manages, and processes multi-dimensional data for analysis and management.

 50.List the types of OLAP servers.

  • Relational OLAP
  • Multidimensional OLAP
  • Hybrid OLAP
  • Specialized SQL Servers

51.List some of the functions performed by OLAP.

Some of the major functions performed by OLAP include “roll-up”, “drill-down”, “slice”, “dice”, and “pivot”.

52.Integrating Heterogeneous Databases

To integrate heterogeneous databases, we have two approaches −

  • Query-driven Approach
  • Update-driven Approach

Query-Driven Approach

This is the traditional approach to integrate heterogeneous databases. This approach was used to build wrappers and integrators on top of multiple heterogeneous databases. These integrators are also known as mediators.

Process of Query-Driven Approach

  • When a query is issued to a client side, a metadata dictionary translates the query into an appropriate form for individual heterogeneous sites involved.
  • Now these queries are mapped and sent to the local query processor.
  • The results from heterogeneous sites are integrated into a global answer set.

Disadvantages

  • Query-driven approach needs complex integration and filtering processes.
  • This approach is very inefficient.
  • It is very expensive for frequent queries.
  • This approach is also very expensive for queries that require aggregations.

Update-Driven Approach

This is an alternative to the traditional approach. Today’s data warehouse systems follow update-driven approach rather than the traditional approach discussed earlier. In update-driven approach, the information from multiple heterogeneous sources are integrated in advance and are stored in a warehouse. This information is available for direct querying and analysis.

Advantages

This approach has the following advantages −

  • This approach provide high performance.
  • The data is copied, processed, integrated, annotated, summarized and restructured in semantic data store in advance.
  • Query processing does not require an interface to process data at local sources.

53.Relational OLAP

ROLAP servers are placed between relational back-end server and client front-end tools. To store and manage warehouse data, ROLAP uses relational or extended-relational DBMS.

ROLAP includes the following −

  • Implementation of aggregation navigation logic.
  • Optimization for each DBMS back end.
  • Additional tools and services.

54.Multidimensional OLAP

MOLAP uses array-based multidimensional storage engines for multidimensional views of data. With multidimensional data stores, the storage utilization may be low if the data set is sparse. Therefore, many MOLAP server use two levels of data storage representation to handle dense and sparse data sets.

55.Hybrid OLAP

Hybrid OLAP is a combination of both ROLAP and MOLAP. It offers higher scalability of ROLAP and faster computation of MOLAP. HOLAP servers allows to store the large data volumes of detailed information. The aggregations are stored separately in MOLAP store.

Specialized SQL Servers

Specialized SQL servers provide advanced query language and query processing support for SQL queries over star and snowflake schemas in a read-only environment.

Data Warehousing – Relational OLAP

Relational OLAP servers are placed between relational back-end server and client front-end tools. To store and manage the warehouse data, the relational OLAP uses relational or extended-relational DBMS.

ROLAP includes the following −

  • Implementation of aggregation navigation logic
  • Optimization for each DBMS back-end
  • Additional tools and services

Points to Remember

  • ROLAP servers are highly scalable.
  • ROLAP tools analyze large volumes of data across multiple dimensions.
  • ROLAP tools store and analyze highly volatile and changeable data.

Relational OLAP Architecture

ROLAP includes the following components −

  • Database server
  • ROLAP server
  • Front-end tool.

Rolap Architecture

Advantages

  • ROLAP servers can be easily used with existing RDBMS.
  • Data can be stored efficiently, since no zero facts can be stored.
  • ROLAP tools do not use pre-calculated data cubes.
  • DSS server of micro-strategy adopts the ROLAP approach.

Disadvantages

  • Poor query performance.
  • Some limitations of scalability depending on the technology architecture that is utilized.

Data Warehousing – Multidimensional OLAP

Multidimensional OLAP (MOLAP) uses array-based multidimensional storage engines for multidimensional views of data. With multidimensional data stores, the storage utilization may be low if the dataset is sparse. Therefore, many MOLAP servers use two levels of data storage representation to handle dense and sparse datasets.

Points to Remember −

  • MOLAP tools process information with consistent response time regardless of level of summarizing or calculations selected.
  • MOLAP tools need to avoid many of the complexities of creating a relational database to store data for analysis.
  • MOLAP tools need fastest possible performance.
  • MOLAP server adopts two level of storage representation to handle dense and sparse data sets.
  • Denser sub-cubes are identified and stored as array structure.
  • Sparse sub-cubes employ compression technology.

MOLAP Architecture

MOLAP includes the following components −

  • Database server.
  • MOLAP server.
  • Front-end tool.

Molap Architecture

Advantages

  • MOLAP allows fastest indexing to the pre-computed summarized data.
  • Helps the users connected to a network who need to analyze larger, less-defined data.
  • Easier to use, therefore MOLAP is suitable for inexperienced users.

Disadvantages

  • MOLAP are not capable of containing detailed data.
  • The storage utilization may be low if the data set is sparse.
56.Which one is faster, Multidimensional OLAP or Relational OLAP?

Multidimensional OLAP is faster than Relational OLAP.

  • MOLAP: Multi-dimensional OLAP Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats (one example is Power OLAP’s .olp file).  products can be compatible with Excel, which can make data interactions easy to learn.
  • ROLAP: Relational OLAP ROLAP products access a relational database by using SQL (structured query language), which is the standard language that is used to define and manipulate data in an RDBMS. Subsequent processing may occur in the RDBMS or within a mid-tier server, which accepts requests from clients, translates them into SQL statements, and passes them on to the RDBMS.

57. MOLAP vs ROLAP

MOLAP ROLAP
1 Information retrieval is fast. Information retrieval is comparatively slow.
2 Uses sparse array to store data-sets. Uses relational table.
3 MOLAP is best suited for inexperienced users, since it is very easy to use. ROLAP is best suited for experienced users.
4 Maintains a separate database for data cubes. It may not require space other than available in the Data warehouse.
5 DBMS facility is weak. DBMS facility is strong.