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.
- 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.
- 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.
47.Explain Difference between OLTP and OLAP?
Following Table shows difference between OLAP and OLTP system:
|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
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.
- 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.
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.
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.
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.
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.
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 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.
- 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 includes the following components −
- Database server.
- MOLAP server.
- Front-end tool.
- 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.
- MOLAP are not capable of containing detailed data.
- The storage utilization may be low if the data set is sparse.
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
|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.|