Getting the most of SQL
In Java there is builtin processors for ResultSet while in .NET there are processors for DataSet, IDataReader and DataTable. Templater supports registering custom plugins for processing such data types, but the built-in ones are sufficiently good for most use cases.
Templater was designed in a way that it receives data from the application.
Thus it does not have advanced manipulation features such as grouping, ordering and filtering of the data once received.
This way Templater focuses on presenting the provided data, while the application should focus on gathering, filtering
and preparing the data which is sent into Templater.
Only at the time when SQL results are passed into Process method, Templater will try to reconcile template structure from the analyzed tags
with the provided data/metadata, which in this case means mapping tags to columns/aliases from the SQL query.
This way Templater does not need to know in advance what kind of query was used, as it will do its best to match provided data with the template:
- this means resizing matching tags with the number of rows in the SQL result - although it will do this in a streaming way, chunk by chunk when appropriate
- tags which it cannot match will be left in the output document
- multiple query results can be passed into processing - each query being responsible only for part of the document
- .NET version will inspect relationship between results which can be used for more advanced scenarios
- all standard metadata and low level handlers will be invoked which can adjust the expected output
Using Templater with advanced SQL patterns
Since Templater only process the query results its up to the application to take care of complex queries or advanced SQL patterns.
Some of the common examples of such patterns are:
- selecting only the relevant columns from the database - instead of selecting many columns and then only using a subset of it
- selecting only relevant rows from the database - aggregated representation of the data, instead of passing in raw data and building pivots in Excel
- building dynamic queries with query builders
- applying three basic analytical operations: consolidation, drill down and slicing/dicing
Implementing Real-Time OnLine Analytical Processing queries (RTOLAP)
While OLAP usually is used as reference to
OLAP cubes prepared in advance, they can be created rather easily by
applying three basic analytical operations on the SQL query and building the query dynamically.
When building RTOLAP snowflake schema is usually used.
In practice queries consist from several parts:
- fact table from which the query starts
- joins over relationships to other tables to project dimension information
- dimension selection and drill-down
- fact aggregation
- filtering of data to relevant subset
This simple pattern scales very far on relational databases and will allow for deep insights into data from within the application which can be sent into Templater as just one more data source to process.
