July 18, 2018 Martin Kratky

OLAP: The King is Dead Long Live the King

What is OLAP?

Online Analytical Processing (or OLAP) is an important concept in the analytics and planning space. It 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 writeback 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.

The Evolution

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.

OLAP Challenges

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 suboptimal 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 we were looking for an approach that combined:

  1. 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
  2. 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
  3. Combining data storage in a single system avoiding maintaining another OLAP database layer in addition to the existing relational data warehouse.

Microsoft Azure - Microsoft Power Business intelligence Planning Software | Bi Consulting Services | Acterys

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.

Optionally, the users can use a semantic layer based on Analysis Services Tabular that is automatically maintained by the Acterys engine to manage calculation logic. This is done in “Direct Query” mode without an additional storage layer. All queries and data entries happen directly against the relational model. 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.

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 a 2-way Excel integration. 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

 

Legacy OLAP (e.g. Alea (Infor BI), Jedox,  TM1 ) Acterys Unified Dimensional Modelling 
Server
Storage Proprietary SQL
Multidimensional Analysis
Direct Handling of transactional records
Data Integration
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

API
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
Frontend
Integration with 3rd party frontends
Excel
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.
On Premise/Cloud
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
In-memory processing
IntelliSense (system suggests syntax and available model parameters while typing) for Excel Formulas
IntelliSense in calculation logic
Licensing
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 processed to the next level please contact us. We currently also offer migration programs 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.

, ,
×