OLAP: ON-LINE ANALYTICAL PROCESSING E-COMMERCE CONCEPTS

The term OLAP (On-Line Analytical Processing) was coined by E.F. Codd in 1993 to refer a type of application that allows a user to interactively analyze data. An OLAP system is often contrasted to an OLTP (On-Line Transaction Processing) system that focuses on processing transactions such as orders, invoices or general ledger transactions.

OLAP is now acknowledged as a key technology for successful management in the 90’s. It describes a class of applications that require multidimensional analysis of business data. OLAP systems enable managers and analysts to rapidly and easily examine key performance data and perform powerful comparison and trend analyses, even on very large data volumes. They can be used in a wide variety of business areas, including sales and marketing analysis, financial reporting, quality tracking, profitability analysis, manpower and pricing applications and many others.

OLAP technology is being used in an increasingly wide range of applications. The most common are sales and marketing analysis; financial reporting and consolidation; and budgeting and planning. OLAP is being used for applications such as product profitability and pricing analysis, activity based costing, manpower planning; quality analysis, in fact for any management system that requires a flexible top down view of an organization.

What is OLAP?

On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user. OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities including:

• calculations and modeling applied across dimensions, through hierarchies and/or across members
• trend analysis over sequential time periods
• slicing subsets for on-screen viewing
• drill-down to deeper levels of consolidation
• reach-through to underlying detail data
• rotation to new dimensional comparisons in the viewing area

OLAP is implemented in a multi-user client/server mode and offers consistently rapid response to queries, regardless of database size and complexity. OLAP helps the user synthesize enterprise information through comparative, personalized viewing, as well as through analysis of historical and projected data in various “what-if” data model scenarios.

This is achieved through use of an OLAP Server.

OLAP allows business users to slice and dice data at will. Normally data in an organization is distributed in multiple data sources and are incompatible with each other. A retail example: Point-of-sales data and sales made via call-center or the Web are stored in different location and formats. It would a time consuming process for an executive to obtain OLAP reports such as - What are the most popular products purchased by customers between the ages 15 to 30?

Part of the OLAP implementation process involves extracting data from the various data repositories and making them compatible. Making data compatible involves ensuring that the meaning of the data in one repository matches all other repositories. An example of incompatible data: Customer ages can be stored as birth date for purchases made over the web and stored as age categories (i.e. between 15 and 30) for in store sales. It is not always necessary to create a data warehouse for OLAP analysis. Data stored by operational systems, such as point-of-sales, are in types of databases called OLTPs. OLTP, Online Transaction Process, databases do not have any difference from a structural perspective from any other databases. The main difference, and only, difference is the way in which data is stored.

Examples of OLTPs can include ERP, CRM, SCM, Point-of-Sale applications, Call Center. OLTPs are designed for optimal transaction speed. When a consumer makes a purchase online, they expect the transactions to occur instantaneously. With a database design, call data modeling, optimized for transactions the record ‘Consumer name, Address, Telephone, Order Number, Order Name, Price, Payment Method’ is created quickly on the database and the results can be recalled by managers equally quickly if needed.

Data are not typically stored for an extended period on OLTPs for storage cost and transaction speed reasons. OLAPs have a different mandate from OLTPs. OLAPs are designed to give an overview analysis of what happened. Hence the data storage (i.e. data modeling) has to be set up differently. The most common method is called the star design.

The central table in an OLAP start data model is called the fact table. The surrounding tables are called the dimensions. Using the above data model, it is possible to build reports that answer questions such as:

• The supervisor that gave the most discounts.
• The quantity shipped on a particular date, month, year or quarter.
• In which zip code did product A sell the most.

To obtain answers, such as the ones above, from a data model OLAP cubes are created. OLAP cubes are not strictly cuboids - it is the name given to the process of linking data from the different dimensions. The cubes can be developed along business units such as sales or marketing. Or a giant cube can be formed with all the dimensions.

OLAP can be a valuable and rewarding business tool. Aside from producing reports, OLAP analysis can aid an organization evaluate balanced scorecard targets.

OLAP Server

An OLAP server is a high-capacity, multi-user data manipulation engine specifically designed to support and operate on multi-dimensional data structures. A multi-dimensional structure is arranged so that every data item is located and accessed based on the intersection of the dimension members which define that item. The design of the server and the structure of the data are optimized for rapid ad-hoc information retrieval in any orientation, as well as for fast, flexible calculation and transformation of raw data based on formulaic relationships. The OLAP Server may either physically stage the processed multi-dimensional information to deliver consistent and rapid response times to end users, or it may populate its data structures in real-time from relational or other databases, or offer a choice of both.

Given the current state of technology and the end user requirement for consistent and rapid response times, staging the multi-dimensional data in the OLAP Server is often the preferred method.

Uses Of Olap

OLAP applications span a variety of organizational functions. Finance departments use OLAP for applications such as budgeting, activity – based costing (allocations), financial performance analysis, and financial modelling. Sales analysis and forecasting are two of the OLAP applications found in sales departments. Among other applications, marketing departments use OLAP for market research analysis, sales forecasting, promotions analysis, customer analysis, and market / customer segmentation. Typical manufacturing OLAP applications include production planning and defect analysis. For all the above applications is the ability to provide managers with the information they need to make effective decisions about an organization’s strategic directions. The key indicator of a successful OLAP application is its ability to provide information, as needed, i.e., its ability to provide “just – in - time” information for effective decision- making. This requires more than a base level of detailed date.

Analyzing and modelling complex relationships are practical only if response times are consistently short. In addition, because the nature of data relationships may not be known in advance, the data model must be flexible. A truly flexible data model ensures that OLAP systems can respond to changing business requirements as needed for effective decision making.

OLAP Benefits:

OLAP applications increase the productivity of business managers, developers, and whole organizations. The inherent flexibility of OLAP systems means business users of OLAP applications can become more self sufficient. Managers are no longer dependent on IT to make schema changes, to create joins or worse. Perhaps more importantly, OLAP enables managers to model problems that would be impossible using less flexible systems with lengthy and inconsistent response times. More control and timely access to strategic information equal more effective decision-making. IT developers also benefit from using the right OLAP software. Although it is possible to build an OLAP system using software designed for transaction processing or data collection, it is certainly not a very efficient use of developer time. By using software specifically designed for OLAP, developers can deliver applications to business users faster, providing better service. Faster delivery of applications also reduces the applications backlog

OLAP reduces the applications backlog still further by making business users self sufficient enough to build their own models. However, unlike standalone departmental applications running on PC networks, OLAP applications are dependent on data warehouses and transaction processing systems to refresh their source level data. As a result, IT gains more self-sufficient users without relinquishing control over the integrity of the data. IT also realizes more efficient operations through OLAP. By using software designed for OLAP, IT reduces the query drag and network traffic on transaction systems or the data warehouse.