Artyom Keydunov, CEO, Cube.
The popularity of Microsoft Excel has never waned. Today, a whopping 1.1 billion users globally rely on it for multidimensional analysis in areas such as “scenario planning, supply chain optimization, logistics and various financial processes.” Multidimensional analysis differs from other types of data analysis because it uses Online Analytical Processing (OLAP) “cubes,” or multidimensional structures containing information for slicing and dicing.
Traditional multidimensional OLAP comes with several issues in the modern day. Because cubes need to be pre-calculated and refreshed, traditional OLAP technologies limit the size, complexity and scale of data while creating governance headaches, operational complexity and duplicative data silos.
Adding to the challenge, multidimensional analyses, like pivot tables and reporting, operate using Multidimensional Expressions (MDX), a language designed for querying and managing multidimensional data structures.
The MDX Breakdown In The Modern Data Stack
The issue: While these OLAP cube technologies provide powerful analysis capabilities, they are tied to legacy relational database technologies such as Microsoft SQL Server Analysis Services (SSAS), Oracle Essbase or SAP HANA as data sources. These legacy platforms use OLAP engines to maintain multidimensional data models and aggregated measures for analysis.
When enterprises begin to migrate their data to modern cloud data platforms, Excel users lose their live analysis capabilities because cloud-based data platforms don’t natively speak MDX as a language. Without native MDX support, Excel cannot perform multidimensional analysis in a direct connection; instead, it forces users to deal with exported snapshots of data that are manual and out-of-date as quickly as they are created.
Current Workarounds To Meeting Excel Users’ Needs
To keep spreadsheet-loving users happy, data teams spend an inordinate amount of time trying to help users export data instead of being able to connect them directly to a data platform. Many organizations have also created “Frankenstacks” with one foot in the past supporting legacy OLAP systems just for spreadsheets and one foot in the present with cloud-based data platforms, creating complexity, scalability challenges, new data silos and inconsistencies.
Of course, business users tasked with FP&A and other critical processes could care less about the intricacies that make analysis possible. They want to use Excel’s familiar interface to get the job done, and data teams are responsible for helping them do so.
Data teams needing to solve the problem are gravitating toward a new solution called modern cloud OLAP. It allows them to connect Excel and other compatible applications directly to cloud data platforms using standard XML for Analysis (XMLA) protocols. Modern cloud OLAP provides native spreadsheet integration, so users get a familiar query experience without reliance on legacy OLAP systems.
How To Know If Modern Cloud OLAP Is Right For You
You need modern cloud OLAP if users across your organization frequently ask for help connecting to data in a data platform to enable live pivot tables and reporting in Excel. Another indicator is exporting data from governed BI platforms to ungoverned flat files that can be imported into Excel. Without a direct connection, they are stuck dealing with exported snapshots of data—which are manual and quickly out-of-date. Modern cloud OLAP is also vital if your organization wastes resources supporting legacy OLAP systems for spreadsheet users and cloud-based data platforms.
Best Practices For Implementation
Deploying modern cloud OLAP is a multistep process. The first step is implementing a universal semantic layer, or a layer of abstraction, that provides a consistent way of interpreting data. It maps data such as tables and columns in a cloud-based data source to familiar business terms stored centrally, making data more valuable to the business and simplifying querying for users. The universal semantic layer also provides data modeling, access controls and caching, removing much of the heavy lifting from data teams.
The universal semantic layer should support native spreadsheet integration so the organization can migrate from legacy OLAP platforms, such as Microsoft SSAS, Oracle Essbase and SAP HANA. With a universal semantic layer that supports native MDX integration, anyone can connect to and reuse trusted data assets and perform multidimensional analysis on governed data using a live connection—no exports are needed. The data remains in the data platform to avoid creating data silos, and the universal semantic layer enables the live multidimensional connection so Excel can query the data in real time.
Legacy OLAP systems were developed over decades, and now modern cloud OLAP offers the best of both legacy and modern approaches, but as with any change, organizations should approach the new data strategy with care. Implementing a universal semantic layer involves onboarding a new technology, which inevitably comes with resource and time commitments. However, instead of having staff maintain legacy systems, they can focus on moving toward modern cloud OLAP. Be sure to take a phased approach, prioritize departments and datasets, define a change management strategy and train users to connect to and find data for analysis in the universal semantic layer.
Moving To The Modern Data Stack
Organizations depend on Microsoft Excel for so much of their strategic planning and decision making. It is essential not to leave Excel users behind when transitioning to the modern data stack. With a universal semantic layer that supports multidimensional analysis and direct spreadsheet connectivity, organizations can enable modern cloud OLAP. Then, all users can execute sophisticated queries that include slicing, dicing, drilling down and rolling up of data in pivot tables and pivot charts—all without the hassle of data exports and maintaining outdated systems.
Forbes Technology Council is an invitation-only community for world-class CIOs, CTOs and technology executives. Do I qualify?
Read the full article here
