What really is OLAP?
Online Analytical Processing (or OLAP) is an important concept in the analytics and planning space that was introduced by solutions like Planning Analytics (TM1), Jedox, Infor Alea and others. In this article we cover a comparison between different approaches.
There isn’t really a concise technical specification but refers to database and analysis technologies that enable business users to build multi-dimensional data models and use them to effectively analyze and navigate across all dimensions and hierarchies in the model. For example, analyzing sales data across dimensions like customer, time, sales representative and hierarchies like customer and regional groupings.
The most widely used “OLAP tool” is likely the Excel Pivot Table that supports all the 12 OLAP Rules particular when it is used in conjunction with an OLAP server (e.g. Analysis Services). In the beginning OLAP functionality was an integral part of client-server-based systems like Express, TM1, Essbase followed by Microsoft Analysis Services and later Jedox (that started as an open source project under the name of Palo).
What’s the Point of OLAP?
OLAP solutions gained high popularity with business users as they allowed them to build their required analytical models (most often for financial use cases) without in-depth IT expertise themselves.
Particularly successful were solutions that could process write back transactions in real time i.e. enabling the user to see the results of a change in the data immediately. A requirement that is an integral part for planning and forecasting processes.
In 2005 Microsoft introduced the unified dimensional model that combined benefits of OLTP (“Online Transactional Processing”) typically relational database systems and “OLAP” databases. For example, users could build their own hierarchies on the fly during the analysis as opposed to having to rigidly define them in the model beforehand.
In 2010 initially with Power Pivot and from 2012 in Analysis Services Tabular the new Vertipaq engine was released, that for the first time included columnar data storage and in memory processing. This new technology enabled – until then- unparalleled query performance with sub second query times in models with tens of millions of records on a laptop. The only drawback here was that write back is not supported.
One challenge with all OLAP systems was that a separate data storage layer was required. This meant that in addition to a corporate data warehouse -typically based on a relational database- IT departments had to manage a separate “OLAP database”. The only exception here are ROLAP (relational OLAP) systems these though, were typically sub-optimal from a performance perspective and mostly didn’t support write back. Worse than managing two databases is the fact that this typically required complex and time-consuming extraction transformation and loading (ETL) processes to e.g. get the planning data in the data warehouse and vice versa the actuals in the planning system.
Next Level OLAP
Based on these challenges with Acterys we were looking for an approach that combined:
- The benefits of the cloud where the necessary services can be provisioned in minutes without the need for expensive in house infrastructure CAPEX and service teams
- Business user driven modelling as well as top down/bottom up write back with the option to see results immediately without the need for time consuming processing
- Combining data storage in a single system avoiding maintaining another OLAP database layer in addition to the existing relational data warehouse.
- Seamless Integration with leading analytics platforms (Power BI) and enterprise collaboration standards (Office, MS Teams, MS Flow, PowerApps)
A Unified Backend Approach
The Acterys approach utilizes an engine that manages modelling and write back on a proven, standard relational database on disk or in-memory. A platform that is often already licensed and in use for corporate data warehouses. A web-based design environment enables business users to manage and edit the models and all related admin aspects without any knowledge of data warehousing or relational databases. The system automatically creates the necessary optimal data warehouse star schema that are optimized for analytics and planning. The use of new relational technologies, multi-threading and in-memory deployment guarantees extremely fast processing times. In tests we were able to process 20m write back transactions in 20 seconds on standard server infrastructure.
Users can setup the required logic in DAX (by now a very widely used standard used in Excel and Power BI), SQL or the new Power Platform components MS Flow and PowerApps. All data is stored in a standard SQL database in the cloud or on premise with a “Direct Query” connection to the frontends enabling super fast, real time response without an additional storage layer. All client interactions from any supported fronted are restricted by flexible security rights (read AND write!) down to the single cell level and recorded in audit trails to ensure comprehensive governance. Again as opposed to re-inventing the wheel users are managed with the existing Windows, Active Directory or Microsoft accounts.
Leverage Proven Frontends & Seamlessly Integrate Analytics
In addition to the unnecessary duplication on the data storage side we also saw room for improvement on the frontend side.
In many cases these days users use a data discovery solution and a separate planning system. With this approach again, a lot of efforts are necessary to integrate the two parts. In our view avoidable by seamlessly extending the market leading data discovery solution with comprehensive planning features. For that reason, Acterys includes an add-on for Power BI.
Power BI’s ground breaking business user orientated, “self-service” approach enables the planning manager to create data entry sheets in minutes with all advanced visualization and analytics features available alongside and profit from seamless insight and simulation options with actual AND planning data. An approach that enables organizations to deploy planning and forecast in less than a day: KMG
The dramatically shorted planning cycles facilitates running forecast in shorter time intervals, that in turn increase quality and ensure up to date information that enables organizations to be much more responsive to relevant changes in conditions.
With the process becoming so much more effective and easy to handle, the contributing participants -that typically dread this process- are not only enjoying it more but really see the benefits from the additional insights they gain through the state of the art analytics power alongside.
Power BI is great, but some requirements will still require spreadsheet flexibility. For that reason Acterys also includes and Excel Add-on that allows comprehensive read AND write directly on the source data warehouse tables. This completely removes spreadsheet maintenance efforts as reports and data entry forms are automatically updated based on the single version of the truth data model.
In the following Table we have listed the differences between legacy OLAP and the Acterys approach
Comparison of OLAP Solutions
|Legacy OLAP (e.g. Alea (Infor BI), Jedox, TM1 )||Acterys Unified Dimensional Modelling|
|Direct Handling of transactional records||ü||ü|
|One Click Connectors to Accounting Systems||Some connectors available (e.g. SAP) but these require extensive customization and have a heavy price tag >$20k||ü
Fully automated connectors that generate entire model with a click
|Cloud Approach||Varying. Often not natively supported or with a “pseudo cloud approach” using a virtual machine that still requires unnecessary machine and software maintenance efforts that a full cloud solution avoids.||Native Cloud App, virtual machine / on premise deployment on request|
|Integration with 3rd party frontends|
|Power BI: Power BI context aware integration e.g. planning form updates based on click in other visuals or slicers in Power BI and vice versa||ü|
|Calculation Language||Proprietary||Standard Microsoft Excel DAX, MDX|
|Scalability Web Reporting||Limited (Jedox: Single thread core)||Power BI near infinite scalability|
|Web Based Model Management||ü||ü|
|Time Intelligence||Limited||All Time Intelligence that DAX, MDX offer|
|Zero Suppression||Resource intensive query in a multidimensional data space||Simple query on existing records|
|Interactive dashboard elements that are interconnected and refresh on click of an object||ü|
|SQL AND Multidimensional Access||ü|
|User can build hierarchies on the fly without the need to predefine them in model.||ü|
|Microsoft Account Integration. Users can use existing Active Directory/Microsoft Accounts without the need to maintain separate security layer||ü|
|Workflows||Custom development requiring coding and proprietary macro language knowledge||Built in standard (mostly without coding knowledge) Microsoft solutions: Microsoft Flow, PowerApps|
|Cell based security||ü||ü|
|IntelliSense (system suggests syntax and available model parameters while typing) for Excel Formulas||ü|
|IntelliSense in calculation logic||ü|
|Minimum Users||5 (Jedox)||1|
|Average Cost Per User Per Month||> USD $200||<USD $100|
For further information on how Acterys can take your planning and analytics processes to the next level please contact us. We currently also offer migration incentives to legacy OLAP users (TM1, Jedox, Alea (now part of Infor), etc.) where we credit users with existing maintenance agreements and offer discounted services for migration.