BCA Introduction to OLAP and OLTP Notes Study Material

Semester Wise BCA Introduction to OLAP and OLTP Notes Study Material: If you choose to do a Bachelor of Computer Application, it is BCA. BCA is a three-year program in most universities. After getting enrolled for BCA, There are certain things you require the most to get better grades/marks in BCA. Out of those, there are BCA Notes and BCA Semester Wise Study Material. BCA Question Answers along with BCA Previous Year Papers. At gurujistudy.com you can easily get all these study materials and notes for free.

If you are a BCA Student there is a single-stop destination as far as preparation for BCA Examination is concerned. Here in this post, we are happy to provide you with Topic Wise & Chapter Wise BCA Introduction to OLAP and OLTP Notes Study Material.

BCA Introduction to OLAP and OLTP Notes Study Material

Basic OLAP & OLTP

OLTP. OLAP, meta data and data warehouse are essential elements of supports system, which has increasingly become a focus of the database industry. Many commercial products and services are now available, and all of principal database management system providers now offerings in these areas. Decision support places some different requirements on database technology compared to traditional online transaction processing applications.

The above diagram shows in the integration system in which we use different tools but database management systems also need the basic requirements depends on data Warehouse, meta data, OLTP and OLAP.

1. Online Transaction Processing (OLTP)

The online operational database system that performs online transaction and query processing is called “Online Transaction Processing” (OLTP) systems. Ex. “Day to day operations of organizations, such as purchasing, inventory, manufacturing, banking payroll registration and accounting.

2. Online Analytical Processing (OLAP)

Data warehouse serves users and knowledge in the role of data analysis and decision-making. Such systems can organize & present data in various formats as per needs of different users. These systems are known as online analytical processing (OLAP) systems.

Online Analytical Processing (OLAP) server is based on multidimensional data model. It allows the managers, analysts to get insight the information through fast, consistent, interactive access to information.

3. The Major Features of OLTP & OLAP

OLTP:

1. User and system orientation: Clerks, clients and information technology professionals use OLTP systems and it is customer oriented whereas the OLAP is market-oriented used by knowledge workers, analysis and managers.
2. Data contents: An OLTP system manages current data and is not used for decision making purposes. An OLAP manages large amounts of historical data with facilities for Summerton and aggregation.
3. Database design: An OLTP system usually adopts an entity relationship model and an application oriented database design. An OLAP system uses a star or a snowflake model.
4. View: An OLTP system works on the current data within organization, without using historical data in different organizations. An OLAP systems deal with information that originates from different organizations, adding information from many data stores. Because of their huge volume, OLAP data are stored on multiple storage media.
5. Access patterns: The access patterns of an OLTP system consist of short, atomic transactions. Such a system requires concurrency control and recovery mechanisms. Accesses to OLAP systems are read only operations.

OLAP:

1. Logically, OLAP servers present business users with multidimensional data from DW or data marts, without concerns regarding how or where the data are stored. (BCA Introduction to OLAP and OLTP Notes Study Material)
2. So an OLAP Server is a high capacity, multi user data manipulation engine specifically designed to support and operate on multi-dimensional data structure. (BCA Introduction to OLAP and OLTP Notes Study Material)
3. However, physical architecture and implementation of OLAP servers must consider data storage issues.
4. OLAP stands for Online Analytical Processing.
5. The key feature is “Multidimensional“, the ability to analyze metrics in different dimensions such as time, geography, gender, product, etc.
6. Implementations of a warehouse server for OLAP processing include the following: MOLAP (Multidimensional OLAP, ROLAP (Relational OLAP) and Hybrid OLAP (HOLAP).

Types of OLAP Servers

We have four types of OLAP servers that are listed below.

1. Relational OLAP(ROLAP)
2. Multidimensional OLAP (MOLAP)
3. Hybrid OLAP (HOLAP)
4. Specialized SQL servers

1. Relational OLAP (ROLAP)

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

ROLAP includes the following:

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

Points to remember:

1. The ROLAP tools need to analyze large volume of data across multiple dimensions.
2. The ROLAP tools need to store and analyze highly volatile and changeable data. (BCA Introduction to OLAP and OLTP Notes Study Material)

1. Relational OLAP Architecture

The ROLAP includes the following.

1. Database server
2. ROLAP server
3. Front end tool

Advantages:

1. Can handle large amounts of data: ROLAP technology’s data size limitation depends on the underlying RDBMS data size. So, ROLAP itself places no limitation on data amount.
2. Can leverage functionalities inherent in the relational database: RDRA already comes with a lot of functionalities. So ROLAP technologies, (works on of the RDBMS) can control these functionalities.
3. The ROLAP servers are highly scalable.
4. They can be easily used with the existing RDBMS.
5. Data Can be stored efficiently since no zero facts can be stored.
6. ROLAP tools do not use pre-calculated data cubes.
7. DSS server of microstrategy adopts the ROLAP approach.

Disadvantages:

1. Performance can be slow: Each ROLAP report is a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
2. Limited by SQL functionalities: ROLAP technology relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs. (BCA Introduction to OLAP and OLTP Notes Study Material)
3. ROLAP technologies are limited by what SQL can do. ROLAP vendors moderate this risk by building the tool with ability to allow users to define their own functions. (BCA Introduction to OLAP and OLTP Notes Study Material)

2. Multidimensional OLAP (MOLAP)

Multidimensional OLAP (MOLAP) uses the 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 uses the two levels of data storage representation to handle dense and sparse data sets.

Points to remember:

1. MOLAP tools need to process information with consistent response time regardless of level of summarizing or calculations selected.
2. The MOLAP tools need to avoid many of the complexities of creating a relational database to store data for analysis.
3. The MOLAP tools need fastest possible performance.
4. MOLAP server adopts two level of storage representation to handle dense and sparse data sets.
5. Denser subcubes are identified and stored as array structure.
6. Sparse subcubes employs compression technology.

1. MOLAP Architecture

MOLAP includes the following components:

1. Database server
2. MOLAP server
3. Front end tool

Advantages:

1. MOLAP allows fastest indexing to the precomputed summarized data,
2. Helps the user who are connected to a network and need to analyze larger, less defined data.
3. Easier to use therefore MOLAP is best suitable for inexperienced user.
4. Excellent performance: A MOLAP cube is built for fast data retrieval, and is optimal for Slicing and Dicing operations.
5. Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only feasible, but they return quickly.

Disadvantages:

1. Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself.
2. The data in cube cannot be derived from a large amount of data. Indeed, this is possible. (BCA Introduction to OLAP and OLTP Notes Study Material)
3. Only summary-level information will be included in the cube itself.
4. Requires additional investment: Cube technology is often proprietary and does not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.

2. MOLAP VS ROLAP

3. Hybrid OLAP (HOLAP)

The hybrid OLAP technique combination of ROLAP and MOLAP both. It has both the higher scalability of ROLAP and faster computation of MOLAP. HOLAP server allows to store the large data volumes of detail data. The aggregations are stored separated in MOLAP store.

4. Specialized SQL Servers

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

5. OLAP Operations

Here is the list of OLAP operations.

1. Roll-up
2. Drill-down
3. Slice and dice
4. Pivot (rotate)

1. ROLL-UP:

This operation performs aggregation on a data cube in any of the following way:

(i) By climbing up a concept hierarchy for a dimension

(ii) By dimension reduction.

Consider the following diagram showing the roll-up operation.

(i) The roll-up operation is performed by climbing up a concept hierarchy for the dimension location.

(ii) Initially the concept hierarchy was “street < city < province < country”.

(iii) On rolling up the data is aggregated by ascending the location hierarchy from the level of city to level of country.

(iv) The data is grouped into cities rather than countries.

(v) When roll-up operation is performed then one or more dimensions from the data cube are removed.

2. DRILL-DOWN:

Drill-down operation is reverse of the roll-up. This operation is performed by either of the following way:

(i) By stepping down a concept hierarchy for a dimension.

(ii) By introducing new dimension.

Consider the following diagram showing the drill-down operation:

(i) The drill-down operation is performed by stepping down a concept hierarchy for the dimension time. (BCA Introduction to OLAP and OLTP Notes Study Material)

(ii) Initially the concept hierarchy was “day < month < quarter < year.”

(iii) On drill-up the time dimension is descended from the level quarter to the level of month.

(iv) When drill-down operation is performed then one or more dimensions from the data cube are added.

(v) It navigates the data from less detailed data to highly detailed data.

3. SLICE:

The slice operation performs selection of one dimension on a given cube and give us a new sub cube. Consider the following diagram showing the slice operation.

(i) The slice operation is performed for the dimension time using the criterion time = “Q1”. (BCA Introduction to OLAP and OLTP Notes Study Material)

(ii) It will form a new sub cube by selecting one or more dimensions.

4. DICE:

The Dice operation performs selection of two or more dimension on a given cube and give us a new subcube. Consider the following diagram showing the dice operation:

The dice operation on the cube based on the following selection criteria that involve three dimensions.

(i) (location = “Toronto” or “Vanconver”)

(ii) (time = “Q1” or “Q2”)

(iii) (time = “Mobile” or “Modem”).

5. PIVOT:

The pivot operation is also known as rotation. It rotates the data axis in view in order to provide an alternative presentation of data. Consider the follow diagram showing the pivot operation.

In this the term and location axis in 2-D slice are rotated.

OLAP Vs OLTP

Difference between Data Warehouse and OLTP

Ralph Kimball the co-founder of the data warehousing concept has defined the data warehouse as “a copy of transaction data specifically structured for query and analysis“.

Data warehousing is closely linked to OLAP-Online Analytical Processing – which relates to complex analysis of (usually aggregated, or denormalized) data in a data warehouse. (BCA Introduction to OLAP and OLTP Notes Study Material)

In contrast, OLTP refers to Online Transaction Processing, An OLTP database is used for day-to-day business operations and is therefore well-normalized.

BCA Introduction to OLAP and OLTP Notes Study Material