How to deliver outstanding analytic performance and scalability on Exasol

Exasol is a scalable in-memory analytical database that can be run across multiple nodes. It provides an analytical solution for large databases that need to operate from memory. One of the key challenges is to work directly on the technology with self-service analytic capabilities without duplicating the data into another layer.

Pyramid can consume Exasol natively, enabling users to drive custom data models and subsequent analysis on Exasol itself, complementing all its attendant performance. In addition, Pyramid’s data flow and preparation tools can assist users wanting to create an Exasol database to perform a data mashup or any other write-back function.

What is a direct query?

A direct query is a query that connects directly to the data source without first copying the data into a proprietary data technology or cache. It does not consume extra memory and harnesses the power of the source environment hosting the source data while preserving and exposing the full depth and breadth of the analytical and mathematical operations needed. More importantly, the cardinal enterprise principles of security and governance are honored because the data is not duplicated, and the centrality of the business logic remains intact.

The problem

Direct querying of an Exasol database must be made using MDX or SQL. BI vendors like Power BI, Qlik, and Tableau can make basic MDX and SQL direct queries to the Exasol environment. However, their capabilities are limited to simplistic queries, and they do not support deep SQL or MDX logic or large queries. For commonly used queries involving member calculations and set/list formulations—like year-to-date totals, month-on-month percentage growth rate, totals, filters, sorts, etc.—these tools effectively force users to load the raw Exasol data into their internal data engines to resolve queries. This severely limits the scope of analytics that these tools can execute directly on Exasol and reduces the value of the investment made in Exasol’s high-scale technology.

Pyramid’s solution

In contrast, Pyramid offers real self-service BI that works directly on Exasol. Pyramid’s PYRANA query engine operates natively on Exasol and queries the database on the fly. This means it can fully expose the full depth and breadth of the Exasol engine by working directly with the technology—while extending deep analytical and calculation functions.

This is a game-changer. Users can fully leverage the speed and scaling capabilities of Exasol with Pyramid’s self-service functionality—without compromising governance and data security and without duplicating the data.

Business case

Phil is a BI Analyst for I&J Distributors. I&J are using an Exasol in-memory data warehouse and Pyramid for their self-service BI analytics. Pyramid operates directly on Exasol—which houses a 20 billion-record sales transaction database in memory. Phil wants to create a sales report and he wants to show the aggregated sales since inception to compare percentage product sales of the current year versus percentage product sales since inception. The calculation requires aggregation of all records, while only displaying data from the current year.

First, Phil uses current hierarchies or columns to create his sales report by simply dragging and dropping items and measures into the relevant Discover “drop zones.”

He then creates a net margin calculation using the graphical Formulate tools.

Then he adds it to his sales report by clicking on the calculation (Net Sales Margin) he has just created.

Phil then creates a calculation for all aggregated sales since inception—in this case 2008. Using simple point and click actions, Phil creates percentage of total columns for both the Sales and then the Accumulated Sales columns.

He then adds them to his report. Now has a sales versus accumulated sales report, with all queries on data and calculations being performed directly and almost entirely on Exasol.

Summary

Exasol’s scalable in-memory analytical database provides an analytical solution for large in-memory databases. Users, however, need a self-service BI tool to perform analysis and create content. While most BI vendors deliver varying degrees of self-service, most do not support deep and direct querying of databases like Exasol, severely limiting the scope of analytics that can run natively. This devalues the high-scale in-memory offering from Exasol, ultimately reducing its power and scalability.

In contrast, Pyramid’s query engine operates natively on Exasol. Users can fully leverage Exasol’s in-memory engine without duplicating the data, compromising governance and security, or lessening its high-performance analytical capabilities.