The Online Analytical Data Processing: a Review
Are you using all the data you have? Do you have that data integrated and ready for integrated analysis? Are you using analytics tools on your data?
We have more than two decades of availability of technology for data analysis. We went to the business intelligence space from reporting, where it was not a good idea to run all large data queries in the operational systems. By that time, it was started to talk about online analytical processing tools (OLAP tools), when hardware and software were not as powerful as of today. And during this period, the amount of data generation has not stopped increasing, and it will continue.
Many companies had a journey using these technologies and migrating to the latest ones. However, today, when we are talking about data as the new source of energy, the new oil or the new gold, are all companies harnessing the power of data?
Even if you are a medium or small business, you cannot wait to unlock the power of the data that you have in your company and the possibilities future and aggregated data will have. If you don’t do it right now, your competitors will be taking advantage.
Let’s review from where we are coming to position yourself in that journey and take action on the data you are not harnessing yet. OLAP or Online Analytical Processing is a concept where you can produce a multidimensional analysis of your business data. It also can provide you the capability for complex calculations, data modeling, and trend analysis.
Online Analytical Processing tools give the insight and understanding that end-users need for better decision-making by performing specific analyses of multidimensional data interactively from multiple perspectives.
OLAP tools involve relational databases, report writing, data mining. Online Analytical Processing tools performed on a Cube consist of five basic analytical operations:
- Drill down
- Roll up
- Dice
- Slice
- Pivot
Online Analytical Processing tools are the basis for many business applications for Business Performance Management, Planning, Analysis, Budgeting, Simulation Models, and Data Warehouse Reporting.
OLAP databases allow for complex analytical and specific queries with a fast execution time using a multidimensional data model. An OLAP system consists of an OLAP cube, which is also known as a multidimensional cube or a hypercube.
Main Online Analytical Processing tools are classified as:
- MOLAP: Multidimensional Online Analytical Processing tools
- ROLAP: Relational Online Analytical Processing tools
- HOLAP: Hybrid Online Analytical Processing tools
Why OLAP Tools?
As knowledge is the foundation of all successful decisions, businesses need to continuously plan, analyze, and report sales and operational activities to maximize efficiency and become successful businesses.
An important thing to do to improve your business activity is to collect as much data as possible. As businesses collect data using many different systems, you have the challenge to get all the data together to create reliable and accurate information about your business.
Your business will be better positioned to make better decisions and win the competition if you turn data into shared knowledge quickly and accurately.
OLAP tools technology can be defined as the capacity to deliver “fast access to shared multidimensional information.” Thereby, OLAP tools technology can help you make better and quicker decisions based on reliable and accurate information.
Benefits of OLAP Multidimensional Databases
The main difference between Relational Databases and Online Analytical Processing tools is that OLAP tools do not store individual transaction records in a row-by-column format like a worksheet.
Online Analytical Processing tools use some multidimensional database structures called Cubes to store arrays of consolidated information. The data is stored in an optimized multidimensional database.
Your business is a multidimensional activity that needs to track its actions by considering many variables. If you follow these variables on a spreadsheet, you will use X and Y axes where each axis represents a group of logical variables or categories.
You can track your sales in units in time using these types of schemes—for example, sealed units in rows and months in columns. Sometimes, a business needs to track many variable groups or parameters, which may be beyond the scope of any number of linked spreadsheets.
In an Online Analytical Processing (OLAP) environment, these variable groups or parameters are called Dimensions. Instead of storing individual transaction records in a two-dimensional format (row-by-column) like a worksheet, OLAP tools use Cubes that are multidimensional database structures.
Users can view a Cube on demand and produce a worksheet-like view of the data stored in the optimized multidimensional database.
Companies have many dimensions to track. A business that distributes goods from several facilities will have many Dimensions to consider as Products, Accounts, Locations, periods, Salespeople, among others.
These Dimensions represent the “whole” business knowledge, providing the basis for all business activities (planning, analysis, and reporting).
Companies need OLAP technology to own the capability to perform multidimensional analysis so that users can view and manipulate data along the multiple dimensions required.
How do OLAP Tools work?
Online Analytical Processing tools are used to extract or retrieve data to be analyzed from different viewpoints. These tools work by extracting data from multiple sources and various formats and storing them in Data warehouses. After that, the extracted data is cleaned and transformed.
Data is stored in OLAP Cubes, where information is pre-calculated in advance for further analysis.
Then users can get the information they need by running queries.
The OLAP Cube consists of numerical facts called measures which are categorized by dimensions. The Cube can store and analyze multidimensional data in a logical and orderly manner.
Classification of OLAP Tools
There are mainly two types of Online Analytical Processing tools: Multidimensional OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and ROLAP.
MOLAP
MO00LAP or Multidimensional Online Analytical Processing uses a multidimensional cube that accesses stored data through various combinations.
MOLAP products use a “Multi-cube” approach where a series of pre-calculated cubes make up a hypercube. Data is first summarized and then stored in contrast to ROLAP, where queries are served on-demand.
It is easy to use even for inexperienced users thanks to its simple interface. It turns out to be the best tool for “slicing and dicing” operations due to its speedy data retrieval.
MOLAP is the more traditional way of OLAP analysis. It has excellent performance and can perform complex calculations. The downside is that MOLAP can only handle a limited amount of data and is less scalable than ROLAP.
Among the most known products that emerged running off MOLAP:
- Oracle Essbase
- IBM Cognos
- Apache Kylin
ROLAP
ROLAP or Relational Online Analytical Processing stores data in relational tables using columns and rows. It retrieves the information on demand through user queries.
A ROLAP database can be accessed to calculate information by complex SQL queries. Besides, it can handle large data volumes, but the more data it processes, the slower the processing time.
It does not require the storage and pre-computation of information because queries are made on-demand.
ROLAP relies on manipulating the relational database’s data to give the appearance of traditional OLAP’s slicing and dicing functionality. Each action of slicing and dicing is like adding a “WHERE” clause in the SQL statement.
ROLAP can handle large amounts of data, and it doesn’t impose limitations on data amounts.
Besides, it can leverage functionalities inherent in the relational database. The downside with ROLAP is that it has potential performance constraints and scalability limitations.
Examples of popular ROLAP products included Metacube by Stanford Technology Group, Red Brick Warehouse by Red Brick Systems, AXSYS Suite by Information Advantage, several of them acquired by IBM and incorporated into the business intelligence data suites.
HOLAP
HOLAP or Hybrid Online Analytical Processing connects attributes of both MOLAP and ROLAP.
These technologies try to combine the advantages of MOLAP and ROLAP.
Users get the benefits of both since HOLAP involves storing part of data in a ROLAP store and another part in a MOLAP store. With this use of the two OLAP tools, the data is stored in relational databases and multidimensional databases.
The choice to access one of the databases depends on which is most appropriate for the requested processing application. This hybrid approach provides much more flexibility for handling data.
The data is stored in a relational database for heavy processing and in a multidimensional database for theoretical processing.
Some products emerged running HOLAP with Microsoft Analysis Services and SAP BI Accelerator. The Analysis Services technology is now available in Azure, Power BI, and on-premises with SQL Server.
The SAP BI Accelerator product went out to the free market, and all functionalities have been incorporated and very much improved into the new SAP HANA InMemory Database technology.
What Are OLAP Cubes?
An OLAP Cube is a data structure that enables fast data analysis according to the multiple Dimensions that define a business problem.
The multidimensional cube for reporting services sales of an IT Business might be composed of many Dimensions like Salesperson, Amount, Region, Services, Month, Year, among others.
Cube is a shorthand for a “multidimensional dataset” where data can have an uncertain number of dimensions. A cube is not a “cube” in the strict mathematical sense. But this term is used widely, although all the sides are not necessarily equal.
The term cube here refers to a multi-dimensional dataset, which is sometimes called a hypercube if the number of dimensions is higher than three.
A determined number of cells forms a cube. Every cell of the cube holds a number representing some measure of the business, such as profits, expenses, budget, or sales.
Another essential term when we talk about Online Analytical Processing tools and Cubes is a “Slice.” It is a term for a subset of the data generated by picking a value for one dimension and only showing the data for that value.
OLAP Cube Advantages
The organization of data into Cubes appears due to the limitations of relational databases that were not considered for the analysis and display large amounts of data.
OLAP Cubes are better suited than relational databases when a whole database must be summarized. They are the best option when users wish to re-orient reports or analyses according to different, multidimensional perspectives called Slices.
The use of OLAP Cubes facilitates a fast end-user interaction with data. It can be thought of as an extension of a spreadsheet’s modeling structure (that only accommodates data in rows and columns).
OLAP Cubes’ designers can build models that balance user needs and logical model limitations due to Cubes’ capacity to include many arrays or Dimensions.
OLAP Tools Analytical Operations
(Images: https://www.geeksforgeeks.org/olap-operations-in-dbms)
OLAP is a business intelligence unit (BI) that answers multi-dimensional analytical queries more smoothly and faster.
Online Analytical Processing tools hold the relational database and encompass RDBMS and data mining & reporting. OLAP tools give users the capacity to analyze multidimensional data from multiple perspectives.
Five basic analytical operations can be performed on an OLAP cube:
- Drill down
- Roll up
- Dice
- Slice
- Pivot
#1. Drill down
The less detailed data is converted into highly detailed data in drill-down operations. Two different kinds of functions can do it:
- Moving down in the concept hierarchy
- Adding a new dimension
The example below shows the drill-down operation by moving down in the Time dimension concept hierarchy (Quarter -> Month).
Images: https://www.geeksforgeeks.org/olap-operations-in-dbms/
#2. Roll up
A Roll up operation is the opposite of the drill-down operation. It performs aggregation on the OLAP cube.
It can be done by:
- Climbing up in the concept hierarchy
- Reducing the dimensions
The cube below shows the roll-up operation performed by climbing up in the Location dimension concept hierarchy (City -> Country).
#3. Dice
The Dice operation selects a sub-cube from the OLAP cube by selecting two or more dimensions.
The cube below shows a sub-cube selection following dimensions with this criteria:
- Location = “Delhi” or “Kolkata”
- Time = “Q1” or “Q2”
- Item = “Car” or “Bus”
#4. Slice
The Slice operation selects a single dimension from the OLAP cube, which results in a new sub-cube creation.
The cube below shows how a Slice operation is done on the dimension Time = “Q1”.
#5. Pivot
Pivot, also called rotation operation, rotates the current view to get a new representation view.
In the sub-cube obtained after the slice operation, performing pivot operation gives a new view of it.
Some OLAP Tools from where we are coming
SAP NetWeaver Business Warehouse
The SAP NetWeaver Business Warehouse (SAP NetWeaver BW) is one of the most known and used OLAP tools. It has the purpose of reporting, analysis, and interpretation of business data.
These actions are essential to enhance companies’ competitiveness by optimizing processes and enabling them to react instantly and in line with market needs.
SAP NetWeaver BW provides tools and functions that facilitate companies to achieve these goals. If you get SAP NetWeaver BW, you can integrate and consolidate appropriate business information from productive SAP applications and external data sources.
This group of tools also gives you a high-performance infrastructure that helps you evaluate and interpret data. Due to this, you can make well-founded decisions and identify target-orientated activities based on the analyzed data.
Integration with In-Memory Technologies
One key point of this group of SOAP tools is that you can enhance SAP NetWeaver BW’s performance using in-memory technologies.
This feature allowed you to efficiently process demanding scenarios with high data volumes, unpredictable query types, high query frequency, and complicated calculations.
You can use a database to access the data from a BW object faster by storing it as an index in SAP NetWeaver Business Warehouse Accelerator.
SAP HANA database for data persistence offers further benefits when executing analysis and planning scenarios besides the performance benefits.
IBM Cognos
IBM Cognos is an integrated and analytical web-based processing system from IBM. It contains numerous inbuilt components to meet various information requirements in an organization. Due to its toolkit, it can also perform analysis for reporting and score carding and the provision to monitor metrics.
IBM Cognos id formed by several windows based components named as IBM Cognos Framework Manager; cube designer; IBM Cognos Transformer; map manager; IBM Cognos Connection; and IBM Cognos Report Studio.
For instance, IBM Cognos Report Studio is used to create reports that are shared with knowledge processing departments. This element allows creating any type of report, including charts, lists, maps, and repeat function.
Analysis Studio is also used to search for background information about an action/ event and prepare the analysis of large data sources. It includes some essential OLAP tools like roll up and drills down. Those are used to get a better understanding of information.
Cognos Analytics Features
Cognos allows you to perform automated data preparation, data discovery, and visualizations to make more confident decisions. To achieve it, you need to complete the following steps:
- Get connected: You need to connect your data effortlessly from your CSV files and spreadsheets, from the cloud or on-premises data sources, including SQL databases, Google BigQuery, Amazon, or Redshift.
- Prepare your data: Then, you can prepare and connect your data automatically with AI-assisted data preparation. Clean and prep data from multiple sources, add calculated fields, join data, and create new tables.
- Build visualizations: Cognos allows you to create dynamic dashboards easily. Drag and drop data to create auto-generated visualizations, drill down for more detail, and share using email or Slack.
- Identify patterns: You can ask the AI assistant a question in plain language and see the answer in a visualization. Use time-series modeling to predict seasonal trends.
- Generate personalized reports: You can also keep your stakeholders up-to-date, automatically. Create and share dynamic, personalized, multi-page reports in the formats your stakeholders want.
- Gain insights: Cognos lets you make confident data decisions. Validate what you know, identify what you don’t with statistically accurate time-series forecasting, and pinpoint patterns to consider.
- Stay connected: You can stay connected 24/7 on the go with the new mobile app. Access data and get alerts will always be right from your phone.
Micro Strategy
MicroStra00tegy is a Washington-based enterprise business intelligence (BI) application software vendor that provides services on BI and mobile software worldwide.
The MicroStrategy platform supports interactive dashboards, scorecards, highly formatted reports, ad hoc queries, thresholds and alerts, and automated report distribution.
MicroStrategy Analytics allows companies/organizations to analyze large volumes of data and distribute business-specific insight.
It delivers reports and dashboards to the users and allows them to conduct and share analysis via mobile devices. It is a secure and scalable software with excellent governance features of enterprise-level BI.
MicroStrategy’s platform uses single standard metadata for consistency and streamlined maintenance. MicroStrategy’s 64-bit architecture supports in-memory analytics with “Intelligent Cubes” that are OLAP reports cached in memory as data sets.
Metrics and attributes are created once and used across different types of reports. When a change is made in one place, all related reports are automatically updated. In the same way, security permissions are given in only one place to reduce administration costs.
MicroStrategy helps make more reliable decisions and build a more innovative enterprise. It is available on-premises software and host-based service in MicroStrategy Cloud.
PowerBI
Power BI is a Microsoft business analytics service that provides interactive visualizations and business intelligence capabilities. It features an interface simple enough for end-users to create their dashboards and reports.
Power BI is formed by a collection of apps, software services, and connectors that work together to turn your business’ unrelated sources of data into logical, visually immersive, and interactive insights.
Power BI can get data from a single Excel spreadsheet or collect cloud-based and on-premises hybrid data warehouses. It allows you to easily connect to your data sources, visualize and discover what’s important.
This application was conceived originally by Thierry D’Hers and Amir Netz of the SQL Server Reporting Services Team at Microsoft. After several evolutions, it was renamed as “Power BI,” and Microsoft then revealed it in September 2013 as Power BI for Office 365.
With PowerBI, you can visualize and connect to any data using the unified, scalable platform for enterprise business intelligence that’s easy to use and helps you gain deeper data insight.