How SPL Transforms Report Data Preparation and Cuts Development Time

Reducing Report Development Effort

Report development consists of data preparation and presentation; tools handle presentation, but data preparation remains manual, often involving hundreds of lines of SQL, stored procedures, and Java code, leading to low efficiency.

Why SPL Helps

SPL (Structured Process Language) is an open‑source structured data computation engine offering rich libraries, step‑wise processing, multi‑source support, hot‑swap, and a standard JDBC interface for seamless integration with reporting tools.

Lowering Development Workload

Data preparation accounts for about 80% of report development effort. Using SPL’s concise syntax and libraries can replace most SQL/Java code, dramatically reducing workload.

Example of SPL’s step‑wise calculation versus complex nested SQL is shown below.

select code, max(risenum)-1 maxRiseDays
from (
  select code, count(1) risenum
  from (
    select code, changeSign, sum(changeSign) over (partition by code order by ddate) unRiseDays
    from (
      select code, ddate,
        case when price>=lag(price) over (partition by code order by ddate)
        then 0 else 1 end changeSign
      from stock_record
    )
  )
  group by code, unRiseDays
)
group by code
having max(risenum) > 5

Equivalent SPL script:

=connect@l("orcl").query@x("select * from stock_record order by ddate")
=A1.group(code)
=A2.new(code,~.group@i(price<price[-1]).max(~.len())-1:maxrisedays)
=A3.select(maxrisedays>=5)

Improving Stored Procedure and Java Drawbacks

Stored procedures are hard to debug, non‑portable, and tightly couple reports to databases, while Java lacks structured computation libraries and hampers hot‑swap. SPL provides database‑independent “library‑outside” procedures, decoupling reports from databases and enabling hot‑swap.

Eliminating Database Intermediate Tables

Intermediate tables consume space and resources; SPL can store intermediate results in external files, reducing database load and simplifying management.

Enabling Hot‑Swap

SPL’s interpreted execution naturally supports hot‑swap, allowing data‑preparation logic to be changed without restarting applications.

Handling Diverse Data Sources

SPL supports dozens of sources (RDB, NoSQL, CSV, Excel, HDFS, REST, Kafka) and provides libraries for heterogeneous‑source calculations, avoiding cumbersome ETL or Java hard‑coding.

Boosting Report Performance

Since most performance issues stem from data preparation, moving heavy calculations to SPL—especially with parallel data fetching and file‑based processing—can significantly speed up reports.

Low‑Cost Strategy for Endless Report Changes

1. Use a reporting tool for presentation. 2. Adopt SPL for data preparation. 3. Decouple the report module from the main application, sharing only data sources.

These steps fully tool‑ify report development, improve architecture, and lower maintenance cost.

Join the SPL community for support and updates.