DataViz Migration - SQL interoperability and semantic layer - {oa-lake}

Decision-support platforms often combine several databases, data warehouses / data lakes, and reporting tools. They also rely on a semantic layer: users work with business objects (“customers”, “contracts”, “revenue”) rather than tables or SQL.

{oa-lake} brings both together in a single middleware:

• a SQL interoperability layer between data sources and dataviz tools, which federates queries and runs the processing (mini SQL pipelines);
• a built-in semantic layer: business objects, dimensions, measures, KPIs, calculation rules, and security, defined once for all tools.

Key point: sources and reporting tools become independent; physical structures stay hidden.

Architecture

{oa-lake} connects simultaneously to relational databases, data warehouses, lakehouses, files, or APIs. A distributed SQL engine federates queries and performs the joins, calculations, and transformations.

Unlike some semantic layers on the market, the {oa-lake} semantic layer requires no specific physical model, no pivot table, and no imposed navigation path: relationships are calculated dynamically from the existing model.

Data sources

The data stays in its original systems. Oracle, SQL Server, PostgreSQL, DB2, BigQuery, Snowflake, Redshift, Azure SQL (and others), or files can be queried within a single request, and a single business object can combine several sources.

SQL Push-Down propagates queries and user filters to the existing systems, with no systematic copying of data.

Typical use case: build a dashboard or a KPI from data spread across several platforms, with no prior migration.

Business objects and semantic layer

Catalogs group dimensions, measures, KPIs, hierarchies, and business nomenclature, organized independently of the databases’ physical structures. Relationships between objects are defined in the semantic layer, which becomes the single entry point for reporting tools: business rules are no longer duplicated across every platform.

SQL generation

SQL is generated dynamically from the selected business objects, dimensions, measures, and filters; joins, aggregations, and calculation rules are applied by the engine. This SQL remains standard, portable, and executable by any compatible SQL engine, which limits technology lock-in.

Exploration interface

{oa-lake} comes with an interface for exploring its semantic layers.

1. Choose the semantic layer: the drop-down list at the top left gives access to the available catalogs.
2. Select business objects: browse the tree (folders, dimensions, measures), then move objects to the right to display them or use them as filters.
3. Run: a limited preview is shown immediately; the full query runs on demand.
4. Check the SQL: both the SQL sent to {oa-lake} and the generated physical SQL can be viewed and copied.
5. Hand off the result: the result can be sent to Superset, Dremio, or another data visualization tool.

Semantic layer carried over, multi-tool

Legacy business objects (Spotfire Information Links, SAP BO universes, Cognos Framework Manager…) are carried over as they are, and the generated query feeds the tool of your choice directly (Looker, Apache Superset, Power BI, Tableau, Qlik…).

SQL engine and cache

Queries are executed by a columnar, in-memory SQL engine, or propagated to the source systems depending on the context. The most heavily used datasets can be materialized in Parquet format; the Cache Manager drives this cache automatically based on observed usage.

Typical use case: speed up the most expensive analytical processing without modifying the source systems.

Security

Access restrictions, user profiles, business catalogs, and security filters are managed in the semantic layer and applied before queries are generated, for all connected tools. The security mechanisms of SAP BusinessObjects or IBM Cognos can thus be reproduced.

Reporting and progressive migration

Any tool capable of querying a PostgreSQL or SQL source (Power BI, Looker, Spotfire, Tableau, Qlik, SAP BusinessObjects…) accesses the same business objects. Switching tools no longer requires rebuilding dimensions, measures, and calculation rules, and several tools can coexist during a transition phase, with no change to the data systems.

Governance and data lineage

Functional changes are made in the semantic layer and are then immediately available to connected tools. The data lineage reconstructed by {openAudit} covers the whole chain, from the source systems to the indicators in the dashboards.

Frequently asked questions