Software-as-a-service (SaaS) offers many benefits, including but not limited to elasticity: the ability to shrink and grow storage and compute resources on demand. Clients of most leading enterprise business intelligence (BI) platforms enjoy this cloud elasticity benefit but at a cost. Ultimately, elasticity requires both application and data components (compute and store) to be elastic, and therefore, cloud-native BI platforms require that on-premises data be ingested into the cloud platform before it can be analyzed. But not all organizations are ready to let go of their data from inside their firewalls, and they are not ready to commit to a single cloud provider — most are opting for a hybrid on-premises and multicloud environment.
So how do such organizations balance the need for a hybrid environment with all the benefits they can get from an elastic, modern cloud-based BI platform? The answer is: a push-down query capability — pushing an SQL query to an on-premises (or a different cloud platform) database management system (DBMS) and only moving the result set (the result of the query, not the entire database [DB]) to the cloud platform. But some data — the result set — still needs to move to the cloud in order to populate reports and dashboards.
It’s 9 a.m. Do You Know Where Your Data Is?
To understand exactly how much on-premises data moves to the cloud and when, even in a push-down query scenario, you need to consider the following components of your BI platform, where data can reside at any moment during BI operations (SQL, OLAP): remote (on-premises or on a different public cloud) DBMS, the platform’s DBMS (disk or in-memory), the platform’s app server cache, and the platform’s full or browser-based client. Then go through the following Q&A with your tech experts and your cloud BI platform provider and map data locations throughout the push-down query data journey.
-
Describe your data journey through the four components during the initial SQL or MDX query. This will help you map physical locations of the data set (or subsets) when you first run a report or pull up a dashboard.
-
Describe the data journey through the four components when a drill-through to detail or a drill-across to a different dimension require an update to the result set. This will help you map physical locations of the data set (or subsets) during every click as you are analyzing the results — slice and dice.
-
Once a connection between a cloud BI platform and a remote DB is made and an initial push-down query is executed, do all subsequent queries still need to go through the BI app server on the cloud, or is there now a direct connection between your BI full client/browser and the DB? In the latter scenario, if both your PC and the DB are on-premises but the BI platform is on the cloud, the subsequent data movement can all happen inside your firewall. This will help you get an extra level of comfort that no data leaves your firewall.
-
Are all remote DBMSes supported in the same manner for push-down query capability? Modern cloud DBMSes and legacy on-premises DBMSes have different levels of APIs, customization and tuning — make sure you map the push-down query data journey for each of the DBMSes that are relevant to your environment.
-
Does the remote DBMS platform require an installation of a component from the BI platform? The nirvana of push-down query capability is to minimize data movement as much as possible. This requires bidirectional “smart” communication between the BI platform and a remote DBMS beyond just processing a query and returning the result set — usually achieved by installing a BI platform component (an agent program or a stored procedure) in the target DBMS.
Use the data mapping exercise to optimize your data and BI architecture, minimize data movement, and satisfy internal data audits and regulatory compliance.
To understand the business and technology trends critical to 2021, download Forrester’s complimentary 2021 Predictions Guide here.
This post was written by VP and Principal Analyst Boris Evelson, and it originally appeared here.
