ExecQBit is a Qualitas product that makes use of OLAP, an important tool for data mining, and used in financial reporting, marketing analysis, budget planning and more – but what the heck is OLAP?
OLAP – a definition
OLAP, or online analytical processing, is used to extract and view data (from different points of view) about your business. OLAP transforms data into information you can use to answer those critical business questions.
It can be used for data discovery, strategic planning, report viewing, analytical calculations and predictive what if scenarios for budget and forecast planning.
It is also referred to as business intelligence.
How does OLAP work?
Consider all the data that is created in your business – from sales, to marketing to financial data. That is a lot of data, but how useful is it to you in making strategic business decisions? How easy is it to read this data?
The databases used to store all this data are called online transaction processing (OLTP) databases. This kind of database is used for storage only, not analysis. These databases are two dimensional, and the data is stored in tables.
This is where OLAP steps in. OLAP databases can extract all your business data using three or more dimensions in your results. This data is required to help your business make informed decisions.
To extract this data, OLAP organizes it into cubes, dimensions and measures.
An OLAP cube is a data structure that allows fast analysis of data. Wikipedia refers to a cube as a multi-dimensional generalization of a two or three dimensional spreadsheet. For example, a company might want to summarise financial data by product, time-period, and city to compare actual and budget expenses. These categories – product, time, city, actual and budget – are the data’s dimensions.
Each cell of the cube holds a number that represents some measure of the business, such as sales, profits, expenses, budget and forecast. This enables rapid analysis of data.
Cube analytical operations
Cubes can display and sum large amounts of data while also providing you with searchable access to any data points. To view the data from any viewpoint, you need to roll up, slice and dice the data. You need to perform these operations so that you can get the widest variety of questions that are relevant to your area of interest from the data.
Here is more about these analytical operations used when gathering data from a cube.
Slice
This involves taking out a specific data set from the OLAP cube. For example, you would slice the sales figures of all sales regions and all product categories of the company in the year 2005 and 2006 out of the data cube.
Dice
Once it has been sliced, the data can then be viewed, or analysed from different perspectives. For example, marketing can view this data to evaluate a marketing campaign, while sales can view the sales per region.
Drill Down
The data can be delved in to get more details. You would start at the summary, then drill down to see the finer details. For example, the sales analyst will drill down on the sales data to view the individual products from each region.
Roll-up
This involves accumulating all the data in a dimension. For example, to identify sales patterns and anticipate future sales trends, sales data from all of the outlets of a retail chain are rolled up to the company’s main sales department for consolidation and computation.
Pivot
This rotates the data axes to provide an alternative view or presentation of data.
Types of OLAP
There are 3 main types of OLAP models.
- Multidimensional OLAP (MOLAP)
This is your standard OLAP model that uses a multidimensional database.The multidimensional cube stores the data, and is structured according to a client’s reporting requirements. You can easily reorganize or rotate the cube structure to view different aspects of data.
Because calculations are predefined on the cubes, complex calculations are achievable and analysis reporting quick. MOLAP is more optimized for fast query performance and retrieval of summarized information. - Relational OLAP (ROLAP)
This type of OLAP stores data in a relational database. The data and dimension tables are stored as relational tables.This model translates native OLAP queries, written in a language called multidimensional expressions (MDX) into the appropriate SQL statements. This prevents the need for another copy of the data. The data created directly by the online transaction processing (OLTP) applications are used. - Hybrid OLAP (HOLAP)
HOLAP stores the aggregated totals in a multidimensional database (in pre-calculated cubes), and the detailed data in the relational database.HOLAP attempts to incorporate the best features of MOLAP and ROLAP into a single architecture.
Some of the advantages of this system are better scalability, quick data processing and flexibility in accessing of data sources.
Advantages of using OLAP
You will find many advantages to using OLAP. These are some of them you will receive when using ExecuBit:
- Slice and dice your dimensions on the fly. You can drag and drop dimensions to a different position in the analysis giving you a different view of the data. For example, you could look at Customer/Stock Code which shows the Customers and what Stock Codes they have been buying from you. And then you can switch Stock Code/Customer around to get a view of Stock Codes and which Customers are buying them.
- Drill down on the displayed data to view the transactions that make up the figure.
- Create your own groups by taking a dimension and identifying specific elements of that dimension, which makes up your group. For example, you might want to analyse how a chain of customer stores is contributing, but each customer store is identified as a customer. Fine take each of those customers and make them part of one group allowing you to analyse how the chain store is doing from a group perspective.
- You can save your OLAP cube if doing the same analysis on a regular basis. This saved cube can be called back at any time the data will be refreshed with the current data from the Data Warehouse. Just like running saved reports.
- Export your data out of your analysis to Excel or a comma separated test file.
OLAP giving you data you can rely on
To make solid business decisions, you need data you can rely on.
A successful business is always planning and analysing their financial, operational and sales activities. This is so that they can remain profitable, and competitive.
To do this, you need to be able to turn that information into knowledge. And to do this, you need OLAP. OLAP is one of the most efficient ways to assist your business in making decisions to achieve and maintain success.
Sources
- https://technet.microsoft.com
- www.techrepublic.com
