OLAP: Online Analytical Processing

Intended Learning Outcomes

• Define OLAP
• Determine the types of OLAP
• Understand OLAP operations
• List OLAP Tools
• Define ETL and understand its process in data warehouse

Online Analytical Processing

• OLAP is a powerful technology behind many Business Intelligence (BI) applications
• OLAP discovers data, provides report viewing capabilities and complex analytical calculations
• OLAP supports predictive "what if" scenarios, budget planning, and forecast planning
• OLAP is for data analytics and enables analysis of information from multiple database systems simultaneously
• OLAP is a computing method that enables users to easily extract required data and query data for analysis from different perspectives
• OLAP is based on the large data in a data warehouse, collecting required data and performing business analysis for decisions to improve profit, sales, brand, and marketing

OLAP Architecture

• Three-tier data warehouse architecture including data sources, data storage, and front-end tools.
• Data flows from source databases through extraction, transformation, and loading into a central data warehouse.

How OLAP Works

• A data warehouse extracts information from multiple data sources and formats, such as text files, Excel sheets, and multimedia files
• The extracted data is cleaned, transformed and then loaded into an OLAP server (or OLAP cube)
• The information is pre-calculated in advance for further analysis in the OLAP server

OLAP Cube

• An OLAP Cube is a data structure that allows fast data analysis based on multiple dimensions which define a business problem.
• A multidimensional cube for reporting sales could be composed of 7 dimensions: Salesperson, Sales Amount, Region, Product, Region, Month, Year.

Basic Analytical Operations of OLAP

• Roll-up (drill-up/consolidation) summarizes operation data along a dimension
• Drill-down performs analysis in deeper dimensions of data. Drilling down from "time period" to "years", "months", and "days" plots sales growth
• Slice performs the analysis and takes one level of information for display, like "sales in 2019"
• Dice performs analysis by selecting data from multiple dimensions
• Pivot performs analysis to gain a new view of data by rotating the cube's data axes

Types of OLAP System

• Relational OLAP (ROLAP) uses a relational database management system to keep and control the data and the servers exist between the database and the user
• Multidimensional OLAP (MOLAP) utilizes a multi-dimensional Database (MDDB) for storing and analyzing information
• Hybrid OLAP (HOLAP) is a blend of MOLAP and ROLAP and offers the qualities of both techniques
• Desktop OLAP (DOLAP) is a single-tier, desktop-based OLAP technology
• Web OLAP (WOLAP) is accessible via a web browser and its architecture consists of a client, middleware, and a database server
• Mobile OLAP helps users access and analyze OLAP data using their mobile devices
• Spatial OLAP (SOLAP) manages spatial and non-spatial data in a Geographic Information System (GIS)

OLAP Tools

• Notable OLAP tools include IBM Cognos, Micro Strategy, Palo OLAP Server, Apache Kylin, Pentaho BI, Mondrian, Oracle Business Intelligence Enterprise Edition (OBIEE), JsHypercube and Jedox

Advantages of OLAP

• High speed of data processing
• Aggregated and detailed data
• Multidimensional data representation
• Using familiar business expressions
• "What-if" scenarios
• Flat learning curve

Disadvantages of OLAP

• High cost
• OLAP is relational
• Computation capability
• Some potential risk

Extract, Transform, and Load (ETL)

• ETL stands for Extract, Transform, and Load
• ETL is used in data warehousing to extract data from databases or source systems and after transforming, places the data into a data warehouse
• ETL is a combination of three database functions

ETL Process

• Step 1: Extraction - Preferred data are identified and extracted from various source systems like databases, applications, and flat files
• Data extraction can be done by running jobs during non-business hours
• Step 2: Transformation - Extracted data that cannot be directly loaded into the target system is transformed
• Based on business rules, transformations are done before loading the data, correcting it, removing incorrect data, and fixing errors
• Step 3: Loading - All gathered information is loaded into the target Data Warehouse tables

Data Extraction Strategies

• Full Extraction: Whole data gets loaded from sources into data warehouses
• Partial Extraction (with update notification): Known as delta, where only the data being changed is extracted and updates data ware houses
• Partial Extraction (without update notification): Specific required data is extracted from sources according to load in data warehouses instead of extracting whole data

Types of Loading

• Initial Load populates all the Data Warehouse tables
• Incremental Load applies ongoing changes as needed periodically
• Full Refresh erases the contents of one or more tables and reloads with fresh data

ETL Tools

• ETL tools include RightData, Informatica Data Validation, QuerySurge, ICEDQ, Datagaps ETL Validator, QualiDI, Talend Open Studio for Data Integration, Codoid's ETL Testing Services, and Data Centric Testing
• Other ETL tools are SSISTester, TestBench, GTL QAceGen, Zuzena Automated Testing Service, DbFit, AnyDbTest, and 99 Percentage ETL Testing