DataViz Migration - SQL interoperability and semantic layer - {oa-lake}
Decision-support platforms often rely on several databases, several data warehouses / data lakes, and several reporting tools. Historically, they also relied on a semantic layer that let users work with business objects — “customers”, “contracts”, “products”, “revenue” — rather than tables or SQL queries.
{oa-lake} addresses both needs 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, which centralizes business objects, dimensions, measures, KPIs, calculation rules, and security, and exposes them to every consuming tool.
Key point: data sources and reporting tools become independent of one another; tools work with business objects while physical structures stay hidden.
Architecture
{oa-lake} connects simultaneously to multiple data systems: relational databases, data warehouses, lakehouses, files, or APIs.
Queries are federated by a distributed SQL engine that performs the necessary joins, calculations, and transformations.
The semantic layer centralizes dimensions, measures, hierarchies, KPIs, and calculation rules, defined once and then exposed to consuming tools. Unlike some semantic layers on the market, it requires no specific physical model, no pivot table, and no imposed navigation path: relationships are calculated dynamically from the existing model.
Key point: a single business model for all data and all consuming tools.
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.
The SQL Push-Down mechanism avoids any systematic copying of data: queries and user filters are propagated to the existing systems, respecting the architectures already in place.
Typical use case: build a dashboard or a KPI from data spread across several platforms, with no prior migration.
Business objects and semantic layer
The catalogs exposed by {oa-lake} group together dimensions, measures, KPIs, hierarchies, and business nomenclature.
Relationships between objects are defined in the semantic layer, not in the reporting tools; catalogs can be organized independently of the databases’ physical structures.
Business rules are no longer duplicated across every dataviz platform: the semantic layer becomes the single entry point for reporting tools.
Key point: a single definition of indicators for the entire decision-support system.
SQL generation
SQL queries are generated dynamically from the selected business objects, dimensions, measures, and filters.
Joins, aggregations, and calculation rules are applied by the engine.
The resulting SQL remains standard, portable, and executable by {oa-lake} or by any compatible SQL engine, which limits technology lock-in.
Key point: users work with business objects; the SQL is generated automatically.
Exploration interface
{oa-lake} comes with an interface for exploring its semantic layers.
The selected semantic layer (top left) exposes a tree of business objects; objects moved to the right become fields or filters; the generated SQL is shown in the side panel; the result can be sent to Superset, Dremio, or another data visualization tool.
- Choose the semantic layer: the drop-down list at the top left gives access to the available catalogs.
- Select business objects: browse the tree (folders, dimensions, measures), then move objects to the right to display them or use them as filters.
- Run: a limited preview is shown immediately; the full query runs on demand.
- Check the SQL: both the SQL sent to {oa-lake} and the generated physical SQL can be viewed and copied.
- Hand off the result: the result can be sent to a data visualization tool (Superset, Dremio, or other).
Key point: users build their queries from business objects, without writing SQL, while keeping full visibility on the SQL produced.
SQL engine and cache
{oa-lake} relies on a columnar, in-memory SQL engine. The queries generated by the semantic layer are executed by this 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, with no manual intervention and independently of consuming tools.
Typical use case: speed up the most expensive analytical processing without modifying the source systems.
Security
Security is managed at the semantic layer level.
Access restrictions, user profiles, business catalogs, and security filters are applied before queries are generated.
This approach makes it possible to reproduce the security mechanisms found in platforms such as SAP BusinessObjects or IBM Cognos.
Key point: security rules are defined once and then applied to all connected tools.
Reporting
The catalogs exposed by {oa-lake} can be consumed by any tool capable of querying a PostgreSQL or SQL source.
Power BI, Looker, Spotfire, Tableau, Qlik, SAP BusinessObjects, or other platforms access the same business objects, dimensions, measures, and KPIs.
Switching dataviz tools no longer requires rebuilding dimensions, measures, and calculation rules.
Result: several platforms can share the same business catalog with no duplication of calculation rules.
Progressive migration
Several tools can coexist during a transition phase: the same business model can be exposed simultaneously to several reporting platforms.
Result: progressive migration of usage with no change to the data systems.
Governance and data lineage
Business objects, KPIs, dimensions, measures, and calculation rules are centralized in a single repository. Functional changes are made in the semantic layer of {oa-lake} and are then immediately available to all connected tools.
The data lineage reconstructed by {openAudit} remains available across the whole chain, from the source systems all the way to the indicators exposed in the dashboards.
Key point: {oa-lake} is the central point for defining business objects and KPIs, and transformations remain traceable across the entire architecture.
