SQL Wrappers

SQL Wrappers form the core of our integrated reporting technology. SQL Wrappers allow the system to understand the parameters of a piece of sql along with the structure of the resulting set of data. LemonEdge allows this to be fully utilised throughout the platform in grids, charts, pivots, reports, etc.

SQL Wrapper Features

SQL Wrappers wrap any sql and provide a mechanism for the platform to understand its structure and parameters. The sql can be custom sql you've hand written to retrieve any dataset you care about, or sql the system has auto-generated for you from our dataset designers, or other reporting tools.

When you design a query using our Datasets tool [see here for more info] the system automatically generates the associated sql for that query for you. It does this to increase transparency, provide better insights into how to create queries in our system, is in line with our Auto-Code Designer approach, and ultimately provide you with the mechanisms to copy and tweak the sql to your bespoke requirements.

SQL Wrappers contain the following features:

  • Can wrap the following types of SQL
    • InLine Functions
    • Table Value Functions
    • Stored Procedures
  • Can automatically load the schema of the parameters and columns from functions into the SQL Wrapper
  • Can specify each individual parameter and map them to fields in the system for user selection
  • Can specify each individual column returned and map them to fields in the system
  • Versioning for consistency across systems
  • Can run algorithms (if using stored procedures) processing and updating data in the system, and simply return a success or failure result.

With the parameters and results precisely known to the system, you can run any SQL Wrapper provide any parameters, and see the results in a grid or excel immediately.

With the stored procedure option you can always code algorithms to run in sql and bulk update data directly on the database should you so require. See our CreateCapitalAccountData period end process for an example of that being used in the system itself.

SQL Wrapper Integration

SQL Wrappers are more than just a method of executing any sql in the system though. They provide powerful integration into the platform itself through 3 core mechanisms:

  • API
    You always have the ability to query, filter and otherwise interrogate all you data through our API.SQL Wrappers also provide additional capability through the API:
    • You also have the ability to hand craft any specific SQL you want, wrap it in a SQL Wrapper, and call it using any parameters you like from our API.
      This ensures you can always achieve custom performance and data integration through any SQL database tools you like.
    • System queries [See here for more info] are written using SQL Wrappers so they are exposed normally in the system. This means when designing you own system through our API you can override system SQL Wrappers to hook into your own proprietary processes.
      An example of this is being able to tweak the GetInstrumentSets system SQLWrapper to allow you to create arbitrary groups of instruments using your own matching mechanisms. This is then automatically integrated into the financial services engine because it uses that SQL Wrapper.
  • UI Integration
    SQL Wrappers are fully integrated into our LemonEdge platform. Providing the following integration:
    • Results can be displayed as a grid with all our standard grid features [see here for more info] such as filtering, reporting, paging, grouping and exporting.
    • They can also be run as server tasks and have the results exported into excel or csv formats.
    • All grids can be dragged and dropped into custom layouts with context sensitive parameters so users can see important KPI's throughout the application right where they are needed
  • Reporting Integration
    SQL Wrappers can be re-used throughout all of our reporting technologies including the following:
    • Datasets can query all entities in the system, all their history and any SQL Wrapper you have created. In other words they allow you to re-query queries
    • Any designed Dataset query actually writes a SQL Wrapper for you allowing full integration into the platform and for you to have the ability to manually tweak the SQL as desired.
    • Pivots are based off SQL Wrappers as data sources
    • Charts are based off SQL Wrappers (and Pivots) as data sources
    • Grouped Queries use multiple SQL Wrappers as data sources
    • Venn Sets allow multiple SQL Wrappers to be merged together into one Dataset
    • Our Excel integration allows exporting data from SQL Wrappers into Excel, but also for you to use our Excel AddIn and call SQL Wrappers from Excel Formulas.
    • Professional Reporting integration (such as SSRS) runs from SQL Wrapper data sources

Essentially with SQL Wrappers you can always integrate any queries into the LemonEdge application and display them in interactive grids, charts, pivots and our other reporting technologies.

You always have the ability to see any queries created by our own reporting tools, and have the ability to copy those and tweak them to your own requirements - ensuring you always have the technical granular control you require. 

Some of our system processes run through SQL Wrappers. This provides transparency on system calculations, but also the ability to easily tailor these processes as you build out your own solution on the platform.
These system SQL Wrappers are versioned and will be upgraded by any new LemonEdge release to our latest version. If you have modified the behaviour you need to make sure your version is always ahead of the system version when upgrading.
[See here for more info]

Using SQL Wrappers

You can always use SQL Wrappers by clicking on the results tab and running the query immediately.

The system will prompt you for any parameters you've setup in the SQL Wrapper before executing your SQL Wrapper and displaying the results in the grid. This grid automatically applies paging to result set larger than a thousand rows (or 20 rows on limited devices like web pages). You can filter, sort and group the results however you like.

You can also run any sql wrapper as a task on the task server. You can do that by simply exporting to excel. The system will prompt for any parameters, and then also prompt for any filters you want applied and which actual columns you want returned. The system will then build a result file of just those results allowing you to download them and display in csv, excel, or predefined excel templates.

By allowing you to pre-filter the query and choose the columns this provides enormous performance benefits on large complex queries, along the following lines:

  • You can filter the query as much as you want first, effectively providing an easy query functionality to the end user before they run it. The more filters you apply the less results returned and the faster the performance of the query.
  • The less columns you choose the less data returned, and if using inline functions the faster the performance of the query overall (depending on your query itself).

This can be of enormous benefit when you have queries that return millions of rows and you want to investigate certain parts of it. You don't want 200mb+ excel files, and you don't want so many rows they blow past excels row limit. By filtering your results, and choosing only the columns you need it becomes very easy for users to use one SQL Wrapper to meet all their use cases.

SQL Wrapper Performance

SQL Wrappers are designed with performance in mind. By default you should always try to wrap InLine SQL Functions so the system can take advantage of proper paging, filtering, sorting when displaying large data result sets in grids.

The results displayed in the grid (or any of our presentation tools) take advantage of a few dedicated performance functions to ensure queries that return millions of rows of data still perform optimally. these are as follows:

  • Wherever possible ensure your SQL Wrapper uses Inline SQL Functions for enhanced performance. The Dataset tools will always produce Inline SQL Functions by default
    • This ensures when selecting a subset of your query data (say first thousand rows) the database engine can do that more efficiently, whereas with Table Value Functions, or stored procedures it has to gather your million plus row result set first, before allowing you to filter that to the first thousand rows if that's all you want.
  • The grids automatically have paging/sorting and filtering built in
    • When you apply paging, sorting or filtering the system actually alters the sql statement to reflect that dynamically to the database. This means instead of a million rows being returned to your server, and then being filtered to the first thousand and sent back to you - only the first thousand are requested from the database to begin with and sent to the server back to you.

These enhancements built into all functionality in the LemonEdge platform ensure results are fetched from the database, to the server, to you as efficiently and as quickly as possible without you having to do any extra work to achieve it.

By providing the ability to tailor the SQL by hand you always have the ability to improve performance based on bespoke knowledge you have of the architecture you've built on the LemonEdge platform and how based to query it.

Any SQL Wrapper created by the system (such as system SQL Wrappers or those automatically created through reporting tools such as Datasets) will always be compatible with the following set of functionalities throughout the platform:

  • Multi-Tenanted Accounts
  • Canvases
  • Team Permissions
  • As Of Date reporting

As such every SQL Wrapper should always have the following parameters that the system will handle automatically passing the correct values to:

  • @accountID
  • @canvasID
  • @teamID
  • @lastUpdated

These parameters should always be the first set of parameters in any sql wrapper, and should maintain this specified order.

Whenever you create a new SQL Wrapper the system will always create the above 4 parameters by default for your new blank SQLWrapper.

You can then directly edit the sql for this function through the

Edit Core SQL command. By default a blank SQL Wrapper will have this example sql which gives you an idea of the structure:

ALTER Function dbo.NewQuery
(
	@accountID BigInt, 
	@canvasID UniqueIdentifier = null, 
	@teamID UniqueIdentifier, 
	@lastUpdated DateTimeOffset = null
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
    -- Example Users
	Select  Name, EmailLogin, ID 
    From    fn_Users(@accountID, @canvasID, @teamID, @lastUpdated)
)

Canvas/As Of Dating Options

For performance reasons it is not always the case that you need to be passing a canvas id or lastupdated parameter around if the user isn't running any query in a canvas, or as of a prior date in the past.

In those scenarios to avoid the associated overhead, the system can call queries without those parameters at all. The system has 4 main types of functions you can call to reference entity records in sql, those are illustrated here with Users:

  • fn_Users(@accountID, @canvasID, @teamID, @lastUpdated)
    This function will deal with permissions, accounts as standard. It will also cater for canvas and lastupdated values even if they are null.
  • fn_Users_Main(@accountID, @teamID)
    This function works exclusively on the main system (no canvas) and current data (no as of date)
  • fn_Users_Canvas(@accountID, @canvasID, @teamID)
    This function allows the passing of a canvas as well, and deals with it containing null
  • fn_Users_AsOf(@accountID, @teamID, @lastUpdated)
    This function allows the passing of an as of date as well, and deals with it containing null

When you write your Core SQL always use the first type of function that uses all standard parameters - @accountID, @canvasID, @teamID, and @lastUpdated.

The system will automatically translate this and create appropriate _Main, _Canvas, and _AsOf overloads of your function. The system will automatically use them as appropriate.

You do however have the ability to override this behaviour. You can individually specify the SQL to be user for the Canvas, Main, and AsOf overloads. If you do this the system will use those instead, if they are blank it will automatically translate you core SQL as appropriate to generate the high overloads for higher performance.

Views

SQL Wrappers are managed through the following views:

 

© 2020 LemonTree Software.