OLAP: faster analyzing data
OLAP is an abbreviation for Online Analytical Processing and gives users the ability to analyze information thoroughly from multiple perspectives easily. OLAP is often provided by using information cubes but some Business Intelligence tools have OLAP functionality in their reports.
The Power of OLAP: boost performance
The power of Online Analytical Processing comes from the speed and flexibility compared with normal reporting directly from the database. It is fast because most of the user requests are pre-processed and the (aggregated) answers are stored in an OLAP-cube. The OLAP engine provides the answer almost instantly. It is flexible because users are able to choose easily between different dimensions, a filter or drill-down to lower levels of detail.
Differences: OLAP vs OLTP
OLAP systems are often compared with OLTP systems. OLTP is an abbreviation for Online Transaction Processing. The sole purpose of OLTP is to process transactions as fast as possible. What are the differences between OLAP and OLTP? The following table reveals the most important differences:
|Purpose||transaction processing||management information|
|Level of detail / scope||single records||aggregated datasets|
|Data model||normalized (3NF)||denormalized (star schema)|
|Data volumes per query||very small||large|
|Permissions||create, retrieve, update, delete||retrieve|
The 7 Steps to building an OLAP cube
- Decide which data you need for your analysis and write a SQL-query to retrieve the data. Often you need to combine the data from different tables. Then you have to join these tables.
- Define the measures / indicators you want to display in your report, analysis or dashboard
- Define the dimensions you want to use as perspectives for your measures, for example Time, Product, Region and Employee
- Model one or multiple hierarchies in your dimensions, for example Year, Month and Day, or Product Line, Product Group and Product
- Define the aggregates and how they should be processed, and define the complex measures and analytical functionality
- Process the OLAP cube, in other words retrieve the data and build the aggregates
- Optimize the performance of the cube based on usage. If users ask often for a specific combination of measures and dimensions which are not yet stored in an aggregate, define an extra aggregate for that combination.
Disadvantages of OLAP
This great technology comes not only with benefits. Two major problem areas can be identified:
- Inflexibility: once a cube is designed it is often difficult to add measures or dimensions quickly, especially when there is a big gap between IT and business.
- Sparsity: this happens when the cube contains too many dimensions / members. All the possible combinations are stored, but not all make sense. As a consequence the cube explodes and response times are getting even worse.
Alternatives to OLAP
Some people believe that OLAP technology is dead. We don’t think so because not many organization can afford an alternative. Aside from that, the alternatives have important shortcomings. The alternatives for Online Analytical Processing are:
- In-memory BI: all the detailed data is loaded into the internal memory of the computer. There is no need to retrieve data from (slow) disks anymore. This will definitely boost performance but still the computer has to process a lot of data because there are no aggregates to read the answers directly from. Some innovative OLAP tools now combine in-memory with OLAP technology.
- DWH appliances: these dedicated BI computers are intelligent and they learn from data structures itself and queries answered in the past and build automatically indexes or aggregates. Next time the same query has to be answered the appliance detects that and retrieves the data simply from the aggregate. The problem is that an appliance acts like a black box and no one is able to design aggregates upfront or to fix problems.
- Database Indexes: this is by far the cheapest solution. Just define the right indexes in the database so queries are optimized to access the data in the joined tables faster. Nonetheless, still the data has to be aggregated on the fly which might take some valuable time when it comes to analyzing the data.
See OLAP in action![iframe width=”600″ height=”325″ src=”//www.youtube.com/embed/2ryG3Jy6eIY”]
Interested in buying an OLAP tool or advice?
OLAP: The biggest innovation in BI
Interactive analysis and OLAP is often regarded as the most important innovation in the world of Business Intelligence. The eyes of analysts and managers often begin to sparkle when they see the possibilities of interactive analysis and OLAP for the first time: “This is what we always wanted to have!” Their enthusiasm is not without reason: this powerful tool enables us to quickly and casually ‘browse’ through large volumes of data, regardless of whether we search for specific information. This allows us to quickly approach data with a varying level of detail from different angles.
It often resulted in failure
The eagerness with which many organizations purchased this tool in the past, unfortunately often resulted in failure to apply interactive analysis. The tool proved mainly an instrument for specialists and not for managers. Furthermore, interactive analysis was mainly used to transfer data to a spreadsheet application.
Functionalities of OLAP technology
Anyhow, with this tool, we can create both simple and complex interactive analysis in very short time, through combining a variation of functionalities – either simultaneously or successively. Functionalities such as:
- Drilling: with a single mouse click, we can vary between levels of detail within a hierarchy, either downwards (drill-down) or upwards (drill-up). For example: we first look at the data by year, then by quarter and finally by month. The data are then updated (refreshed) pretty much automatically. We can also request all detail data of the relevant correction order in a particular context – a selection such as 20013, Order Type, ‘Correction’ – in one go (drill-through).
- Slice-and-dice: switching between angles from which we want to look at key performance indicators. With one single action, we can switch between for example the angles ‘location’ and ‘order delivery method’. The data will be renewed and displayed virtually automatically. This enables us to analyze and report both rapidly and flexible.
- Filter: zooming in on one or more specific values in a dimension. For example: we observe that the problem lies within a specific product group, which we then ‘secure’ as it were. Further analysis then takes place within that context, without taking up too much unnecessary screen space. The filter disappears to the background as it were. Beware of the pitfall though: sometimes we forget all a about the filter in which case we think we look at the overall data collection but we are not. The figures will then not match other reports, or our expectations.
- Nesting: this is the phenomenon of having two or more angles summed up and combined. For example: we already have an analysis of the revenue per product group, however now we would like to split up the revenue figures within the analysis by the different suppliers.
Detect easily trends, exceptions and deviations
The above-mentioned functionality forms an important basis for performing an interactive analysis. Obviously, interactive analysis tools offer much more functionality, which we have not mentioned here. Think for example of the 80/20-rule, emphasising exceptions, combining tables and charts in a single screen, and creating ‘the highest…’ or the ‘lowest…’ What matters is that we can both easily and interactively detect trends, exceptions and deviations, and more importantly, we can find out their cause.
Rapid response times
Interactive analysis at the front end is only properly possible if the Business Intelligence infrastructure supports this by way of available files containing much-needed summaries. The rapid response time we require for interactive analysis is primarily provided by a so-called OLAP-engine – either incorporated in a relational database or stand alone –, which is capable of summarising detail data automatically – to different angles – and which subsequently saves these summaries in a multidimensional format. All kinds of clever indexing techniques then ensure that the data can be looked up and retrieved at lightning speed when needed.
Key characteristic of interactive analysis and OLAP
- Flexible and explorative ‘browsing’ through a limited set of data
- The exact information need is not determined in advance, but is limited by a fixed number of angles, indicators and levels
- Rapid response times, also with big volumes
- Data appear to be renewed ‘on-the-fly’
- Standard design (interface)
- Usually uses cubes