asapBI: Visual Modeling of Calculation Views Without SQL
Developing complex SQL queries with subqueries and CTEs often turns into a readability and maintenance nightmare. Text spanning dozens of screens complicates debugging and understanding the logic over time. The solution is switching to visual Calculation Views, similar to SAP HANA, but adapted for various DBMS.
asapBI implements such a system: visual data selection building with parameters, export to database UDF functions. Support for ADB, Greenplum, PostgreSQL, ClickHouse allows integrating models into existing stacks.
Key Features of Calculation Views
The system generates native DBMS functions that can be called from any tools. Key features:
- Execution inside the database or on external clusters (Trino, CedrusData) — transformations in 10 minutes instead of 4 hours without loading production.
- Full transparency: view generated SQL with manual editing option.
- Vendor lock-in free: export to standard tools without losing work.
- Integration of CV parameters for dynamic queries.
Creation happens in project folders, like other Data Vault objects. A ready CV is a set of UDFs ready to call.
Advantages Over Manual SQL
Manual SQL is suitable for niche optimizations, but TTM (time-to-market) suffers. Most queries are optimized by the DB engine, and hardware performance growth reduces the need for assembly-like tweaks.
In asapBI, SQL becomes an option for final polishing, not the main tool. Intermediate CV blocks are exported as functions, simplifying modularity.
Example: instead of a monolithic query, a transformation graph with joins, filters, and aggregations is built visually. Output — optimized SQL or UDF.
Scaling on Clusters
Critical for ETL: closing periods without materialization. Execution on external clusters unloads the DWH. No need to maintain materialized views.
Trino support allows parallelizing tasks. For reports — direct UDF calls without intermediate tables.
What's Important
- Visual building accelerates development of complex analytical models.
- Export to UDF ensures integration with external systems.
- Execution on clusters reduces load on production DB by 20+ times.
- SQL code transparency + manual edits for senior developers.
- No vendor lock-in: migration to any tools.
Prospects with AI
Next step — automation: AI generates schemas, joins, models based on descriptions. The developer reviews and adjusts. This minimizes manual labor, keeping focus on business logic.
Such systems are relevant for mid/senior specialists: reduce cognitive load, speed up iterations without losing control.
— Editorial Team
No comments yet.