SQL to automate DataViz layer migration

 

SQL to automate DataViz layer migration

Many companies wish to decommission this or that data visualization technology to move towards more sustainable technologies, more in line with current Cloud platforms: Azur, AWS, GCP, etc.   These migrations are high risk given the complexity of the platforms. And even if the migration has taken place, the output can immediately present pathologies that are crippling for the professions and for IT: slowness, complexity, cumbersome maintenance. And the “divorce” with the source dataviz solution is expensive enough not to impose an ad vitam “marriage” with a new dataviz technology 😊.    We have developed an agnostic SQL generation engine which will enable "As Is" migration,  from the source dataviz layer, to SQL.   This SQL will be transposed into the source database of the target technology (Azure SQL, BigQuery, Redshift, etc.).     In short, it will be a question of generating in the target database, via new data pipelines in "flat" SQL, a very "business" and inherently multiplatform data layer.   The target dataviz technology will therefore only make simple queries on this data. The efficiency will only be better. And future migrations will only be simpler. These projects are offered as a fixed price.

The benefit of “pivoting” using  SQL

Improved performance By moving all the complexity inherent in the dataviz layer to simple SQL within the databases, we mechanically boost performance: intelligence can be factorized and data persisted. Thus, we are seeing a massive reduction in latency times. The user experience becomes smoother.

Ease of maintenance Centralized management of SQL queries simplifies maintenance of the dataviz layer. Updates and modifications can be carried out directly at the database level, thus avoiding complex interventions on several platforms, with different teams each time. As it will be simple SQL, maintenance will be within the reach of as many people as possible.

Resource Consolidation By migrating to simple SQL data pipelines which generate shared data sets, we avoid the proliferation of data visualization tools meeting this or that business requirement. This helps simplify license management, user training and performance monitoring. It is thus possible to consolidate resources and optimize infrastructures.

Increased scalability Integrating the complexity of the data visualization layer into the database improves the scalability of the system. A DWH at a hyperscaler is designed to more efficiently manage significant growth in data volume, while maintaining optimal performance, thus guaranteeing the sustainability of dataviz solutions.

Security “Flat” SQL (without subqueries) makes the code more readable and easier to maintain: it is possible to have direct control over the conditions and filters applied to the data without having to navigate between several levels of queries nested. This makes checking and modifying the selection criteria easier. Database security, which is often optimal, is shared for all dataviz solutions that feed from these pipelines.

Technically   Introspect the source technology in an ultra-granular way   {openAudit}  our software will retrieve the following information:

In terms of the intelligence of the source dashboards: The list of expressions and variables used, and their level of nesting;  The list of functions used in these expressions and variables.

In terms of sources:  SQL queries, and therefore the list of useful fields and tables; The list of joins to connect these tables; The definition of personalized SQL; The contexts used; The list of prompts used in dashboards.

Migrate   Based on this information, {openAudit} will generate SQL to build data pipelines in the target database, while factoring intelligence.   (Note: generally we suggest simplifying the source platform before migration).

Conclusion    Dataviz tool migrations are a headache. We offer "As Is" migrations while allowing us to "tame" complexity, by refocusing it where it perhaps should not have come from, the DWH.    Our very strong ability to introspect most data visualization technologies allows us to offer this translation engine from the data viz layer to SQL. This will allow us to start again on a healthy and lasting basis. We offer this type of project on a fixed price basis to allow client companies to precisely frame their projects.