Queries & Summaries Overview

​

Key Features

• SQL Syntax Support: Use familiar SQL-like syntax for data queries
• High-Speed Execution: Optimized in-memory query processing
• Group By Operations: Create summaries and aggregations by multiple dimensions
• Filtering: Apply WHERE clauses with complex expressions
• Sorting: ORDER BY with ascending/descending support
• Sub-Queries: Nested SELECT statements for advanced filtering
• Distinct Values: Eliminate duplicates from result sets
• Pagination: TOP and OFFSET for result set limiting

​

Quick Example

uses BI.SQL;

var 
  Data, Result: TDataItem;
begin
  // Simple aggregation with grouping
  Result := TBISQL.From(Data, 
    'sum(Amount) where (Customer=123) group by Country, Year');
  
  // Filter with sub-select
  Result := TBISQL.From(Data, 
    'ProductName, UnitPrice where UnitPrice > select Average(UnitPrice)');
  
  // Pagination
  Result := TBISQL.From(Movies, 
    'top 100 offset 15000 year, length');
end;

​

Core Classes

​

TBISQL

• From(AData, SQL) - Parse and execute SQL query
• ProviderFrom(AData, SQL) - Parse SQL and return provider

​

TBIQuery Component

• Dimensions - Fields to select or group by
• Measures - Aggregation calculations
• Filter - WHERE clause filtering
• SortBy - ORDER BY sorting

​

TSummary

• By - Group by dimensions
• Measures - Aggregate functions
• Filter - Row-level filtering
• Having - Post-aggregation filtering

​

Query Workflow

1. Parse - SQL string is parsed into a provider (TDataSelect or TSummary)
2. Filter - WHERE clause is evaluated for each row
3. Group - Data is organized by GROUP BY dimensions
4. Aggregate - Measures are calculated (SUM, AVG, COUNT, etc.)
5. Sort - Results are ordered by ORDER BY clause
6. Limit - TOP/OFFSET are applied to result set

​

Next Steps