SQL Functions

SQL Functions provide the ability to import .net clr functions into the LemonEdge database for reference and use within the query tools. This provides access to system default algorithms, such as IRR calculations, live from the query tools whilst also providing a mechanism for custom algorithms as well.

SQL Function Features

SQL Functions allow the importing of .net clr functions into the LemonEdge database for use within the query tools.

LemonEdge uses SQL Server CLR Integration to provide functionality that can be integrated into all of the queries you create in LemonEdge. You can see examples of these custom calculations being used in standard sql from the Microsoft documentation here.

Caution

In most legacy systems reporting is often archaic involving proprietary scripting/programming languages, and datacubes (or similar) that are generated by process overnight that your "simpler" reports run against. This enables their solutions to calculate metrics not possible in sql, by calculating them offline, to be included in those datacubes, and reported on in your reports - such as IRR calculations.
This was necessary 20 years ago when these systems were developed, but is one of many reasons their reporting tools lag so far behind the expected modern day experience. No one wants to be charged thousands simply to calculate irr or twr metrics a different way - which for them involves updating their overnight processes to include that calculation, something you can't easily do yourself. Ultimately it also means reports like these are never running live - which is always a problem.

LemonEdge addresses this common problem by allowing the calculation of these metrics live in sql in any aggregate manner you like using our query tools. This offers many advantages including, but not limited to:

  • All reports are running in sql functions/procedures that anyone can use allowing easy 3rd party tool integration
  • No proprietary scripting/languages that process data in to proprietary datacubes or similar
  • All reports are running live on your data
  • The calculation is immediate with your data ensuring it is done in the fastest manner possible
  • You control the calculation so you can easily set it to run on any dates/cashflows (for irr calculation for example) you like using any grouping you like
  • You have the ability to customize and add your own metric calculations, such as twrr or similar, and integrate them into the query tool for your own use
Tip

By default LemonEdge includes a xirr calculation SQL Function ensuring you can calculate irr's anyway you like easily using our own query tools. Most importantly this means any query you design using our tools is backed by a single sql function being executed in the database - providing all your results (and custom calculations) in one go, as fast as possible, with no intermediary steps/calculations (such as datacubes), and returned immediately.

Types

Only Aggregate CLR User Defined SQL Functions are currently supported for automatic integration with LemonEdge via SQL Functions.

Tip

However you can still create scalar, and other, clr functions in SQL Server yourself and reference them in our query tools. The system just won't automatically handle that for you via SQL Functions just yet.

Enabling

Warning

By default this functionality is not enabled and requires the AllowCLRSQLFunctions GlobalEnv flag to be set to 1, along with enabling the CLR SQL Server Integration feature of SQL Server on your database. This SQL Server feature can be enabled against SQL Server, or SQL Azure Managed Instances, but not against SQL Azure.

Once you've set the flag to 1 the system will assume you have the correct database privileges and attempt to enable the CLR SQL Server Integration feature with the following script on the next upgrade:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
ALTER DATABASE YOURDATABSENAME SET TRUSTWORTHY ON;
RECONFIGURE WITH OVERRIDE;

You can find out more about these settings from here:

You can configure this feature yourself using the above script, or by following alternative options on CLR Integration - Enabling.

Important

In either case, once you have SQL Server CLR Integration configured against your database this feature in LemonEdge will allow you to configure the addition of SQL Server CLR User Defined Functions. The SQL Functions command will not be available in the main menu until all the above steps are complete, it will be hidden by default until the functionality is enabled.

Views

SQL Functions are managed through the following views: