A Better OLAP: Comparing Acterys with IBM Planning Analytics (TM1) and Jedox

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 the 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 of 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, which 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 is 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:

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 avoids maintaining another OLAP database layer in addition to the existing relational data warehouse.
4. 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 to model 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 is 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 set up 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 effort is 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 groundbreaking 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, which 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 an 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
Server
Storage	Proprietary	Relational Database
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 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.

Key Takeaways

1. OLAP, or Online Analytical Processing, allows effective analysis across various dimensions and hierarchies, such as customer, time, and sales representative, facilitating comprehensive insights into data. 
2. OLAP has evolved from client-server-based systems to cloud-based and integrated models that enable users to create their own analytical models with real-time write-back capabilities. 
3. Traditional OLAP systems often required a separate data storage layer, leading to complexities in data management and time-consuming ETL processes. 
4. Acterys offers a modern approach to OLAP, combining the benefits of the cloud, business user-driven modeling, real-time write-back, and seamless integration with leading analytics platforms.

FAQs

• What is OLAP, and why is it important in analytics and planning?

OLAP, or Online Analytical Processing, is a crucial concept in analytics and planning. It refers to database and analysis technologies that empower business users to build multi-dimensional data models, enabling effective analysis across various dimensions and hierarchies. OLAP is essential for gaining comprehensive insights into data, particularly in financial use cases.

• How has OLAP evolved over the years?

OLAP has evolved significantly over time. It started with client-server-based systems like Express, TM1, and Essbase, followed by Microsoft Analysis Services and Jedox. In recent years, OLAP has moved to the cloud, offering greater flexibility and real-time data processing capabilities.

• What challenges did traditional OLAP systems face?

Traditional OLAP systems often required a separate data storage layer, leading to complexities in data management and time-consuming ETL processes. Integrating data discovery and planning systems posed challenges, and many systems did not support real-time write-back capabilities.