You can use the Sys.Admin.SetupView
provider supplied by FINBOURNE to create a custom ‘view’ that can access any number of other Luminesce providers (or existing views).
The goal is to enable end users to write simplified or more performant queries using this view instead of the underlying providers directly, obscuring complexity, increasing productivity and promoting adoption.
Note: If you are the LUSID domain owner, you are automatically assigned the built-in lusid-administrator
role, which has all the access control permissions necessary to perform the operations in this article. If you are informed you require a license for Sys.Admin.SetupView
, please contact us.
Consider the example of a Views.MaturityDaysToExpiry
view you create that:
- Queries the
Lusid.Instrument.Bond
reader provider to retrieve information about bond instruments mastered in LUSID. - Queries the
Lusid.Instrument.FxForward
reader provider to retrieve information about FxForward instruments mastered in LUSID. - Returns the
Type
,DisplayName
,Isin
andMaturityDate
columns from these providers, withDisplayName
renamed toInstrument
. - Calculates and returns a
DaysToExpiry
column with the number of days until each instrument expires, enabling the end user to perform aggregation operations such as min, max, count and average efficiently. - Enables an end user to pass an
AsAt
parameter into the view to optionally roll back LUSID's bitemporal timeline.
Rather than engage with multiple providers and complex SQL, an end user can then quickly extract business intelligence about bonds and FxForwards by writing a simple Luminesce SQL query like this:
select * from Views.MaturityDaysToExpiry where DaysToExpiry < 7;
Contents:
- Understanding the Sys.Admin.SetupView provider syntax
- Writing a Sys.Admin.SetupView query to create your custom view
- Publishing your custom view
- Using your live custom view
- Managing your live custom view
- Deleting your custom view
Getting help
You can access the latest documentation in the help page for the Sys.Admin.SetupView
provider at any time by running this query:
--help
enduse;
Alternatively, run the following query to retrieve the help content as a table:
Understanding the Sys.Admin.SetupView provider syntax
Sys.Admin.SetupView
is a direct provider, and therefore follows these syntactic conventions.
The precise syntax is as follows:
<options-section>
----
<sql-section>
enduse;
-- The following statement is optional but useful while creating the view to test it returns meaningful data
select * from @x
Note the following:
- The
<options-section>
and the<sql-section>
must be separated by a line containing at least four hyphens (and no other characters). <options-section>
An option takes the form--<option>=<value>
, for example--provider=Views.MyView
. Note no spaces are allowed either side of the=
operator. If an option takes a boolean value, then specifying that option (for example--allDomains
) sets it to True; omitting the option specifies False.
To list all the options, access the help page and examine the table at the bottom. The options in the following table are particularly relevant to this use case.Option Explanation --provider=<name>
Creates a provider from the view with the specified name and makes it available in the Luminesce catalog for suitably-permissioned end users to discover and query. Note it may be more performant to omit this option while creating and testing your view. --parameters
<name>,<type>,<value>,[false]
Defines scalar variables that can be passed into the view. Each definition must consist of a name, data type, and an initial value that is also the default value (unless you specify false as the final argument, in which case the value is only used when setting up the view, and not when an end user queries the view later). The name is case insensitive but camel case with initial capitals is recommended. Available data types are:
Boolean
,Int
,BigInt
,Double
,Decimal
,Text
,Date
,DateTime
.
Note each definition must be on a separate line after--parameters
, so if specified this option must always be last in the list. For example:@x = use Sys.Admin.SetupView
--provider=Views.MyView
--distinct
--parameters
AssetClass,Text,Equity,false
Isin,Text,GB124892739
----See also the
#PARAMETERVALUE
macro in the section below for inserting variables into the view.--deleteProvider
Removes a published provider from the Luminesce catalog. More information. --limit=<n>
--offset=<n>--groupBy=<pipe-delimited(|)-set-of-expressions-to-group-by>
--select=<pipe-delimited(|)-set-of-expressions-to-filter-by>
--filter=<filter-expression>
--distinct
Specifying these options optimises performance in conjunction with the appropriate macros (see the section below) while you are creating and testing a view. Consider the following example: @x = use Sys.Admin.SetupView
--distinct
--filter=Isin is not null
--limit=42
----
select
#DISTINCT
#SELECT { { Isin : Isin } }
from Lusid.Instrument where
#RESTRICT { { Isin : Isin } }
#LIMIT
enduse;
select * from @xEach time you run this query, a maximum of 42 different bonds with ISIN identifiers is returned, enabling you to test the view returns meaningful data in a reasonable timeframe.
Note these options have no effect on a published view, so you can either retain or omit them when you are ready to add the--provider
option and go live.<sql-section>
This can be any valid Luminesce SQL query; see the section below. It must be terminated by anENDUSE
statement.
Writing a Sys.Admin.SetupView query to create your custom view
You must write a valid Luminesce SQL query in the <sql-section>
that undertakes the operations you want the view to perform. Note the following:
- You can query any existing Luminesce provider providing your end users have access control permissions to use it.
- You can nest other views providing they already exist.
We recommend including the macros in the following table in your SQL query to optimise performance.
Note: For more information on each macro, and for available macros that are not included below (such as #CHECKACCESS
, #USERID
and #CLIENTDOMAIN
), access the help page.
Macro | Explanation |
#SELECT{{<column>}, [{<column>}…]} | Optimises the performance of selecting and populating columns in the view that an end user can return and query. You can have multiple @x = use Sys.Admin.SetupView
---- select #SELECT { { Instrument: i.DisplayName }, } from Lusid.Instrument i enduse; …specifies that an end user can write a query returning an You could omit the @x = use Sys.Admin.SetupView
---- select DisplayName as Instrument from Lusid.Instrument enduse; …but this would impair performance. The view always retrieves the
For example: select #SELECT { { some-column-name+^~Int|"This is an important column" : sql-for-special-column }, { next-column-name : sql-for-ordinary-column }, } |
#SELECT_AGG{{<column>}, [{<column>}…]} | Use this macro once in a view instead of #SELECT in order to pass any aggregation operations that an end user performs (such as count, min, max or average) down to the underlying SQL implementation, further improving performance. |
#RESTRICT{{<column>}, [{<column>}…]} | Optimises the performance of any WHERE clause filters specified by an end user on the named set of columns, passing the filters down to the underlying provider. |
#RESTRICT_AGG | Use this macro once in a view instead of #RESTRICT and in conjunction with #SELECT_AGG to optimise the performance of filters specified by the end user on the same set of columns as #SELECT_AGG (which is why this macro has no <column> arguments). |
#PARAMETERVALUE(<NAME>) | Inserts the named --parameter value specified by an end user at an appropriate point in the view. For example:@x = use Sys.Admin.SetupView
--parameter AssetClass,Text,Equity ---- select * from Lusid.Instrument where Type is #PARAMETERVALUE(AssetClass) enduse; Note parameters with a data type of |
#LIMIT | Inserts a LIMIT clause specified by an end user at an appropriate point in the view, optimising performance. Since this macro has no effect if the end user does not specify a LIMIT clause, it is typically always useful to include this macro. |
#OFFSET | Fills in the OFFSET clause as required by the SQL dialect. |
#DISTINCT | Inserts a SELECT DISTINCT statement specified by an end user at an appropriate point in the view, optimising performance. Since this macro has no effect if the end user does not specify a SELECT DISTINCT statement, it is typically always useful to include this macro. |
#GROUPBY | Inserts a GROUP BY statement specified by an end user at an appropriate point in the view, optimising performance. Since this macro has no effect if the end user does not specify a GROUP BY statement, it is typically always useful to include this macro. |
#ORDER_BY | Allows more limits to be applied, and in the case where they exist can significantly optimise performance. Requires the presence of both #SELECT_AGG and #RESTRICT_AGG in the query. |
Creating our example Views.MaturityDaysToExpiry view
Consider the following example (notes underneath):
@x = use Sys.Admin.SetupView
--provider=Views.MaturityDaysToExpiry
--parameters
AsAt,DateTime,9999-01-01
----
-- For now, Date and DateTime parameters must be assigned to an intermediate scalar variable, and that variable
-- inserted into the view. This restriction may be removed in future. Other parameter types can be
-- inserted at any point using the #PARAMETERVALUE macro directly, as intended.
@@asAt = select #PARAMETERVALUE(AsAt);
select
#DISTINCT
-- Using the #SELECT_AGG macro here (as opposed to #SELECT):
-- 1. Avoids duplicating the work of the #RESTRICT macro
-- 2. Improves performance by passing aggregate queries down to the underlying SQL implementation.
-- Note the underlying providers return a DisplayName column that we wish to rename to Instrument. See the
-- #SELECT and #RESTRICT macros in the sub-query for the actual mapping of DisplayName to Instrument.
#SELECT_AGG
{
{ Type: Type },
{ Isin: Isin },
{ Instrument: Instrument},
{ MaturityDate: MaturityDate },
{ DaysToExpiry: cast(julianday(MaturityDate) - julianday('now', 'start of day') as Integer) },
}
from
(
select
'Bond' as Type,
MaturityDate,
-- Using the #SELECT macro here improves performance by only returning the Isin and DisplayName columns from the
-- underlying provider if the end user explicitly requests them. MaturityDate, on the other hand, is always returned
-- as it is required to calculate the synthetic DaysToExpiry column. Note also DisplayName is mapped to Instrument.
#SELECT
{
{ Isin: Isin },
{ Instrument: DisplayName },
}
from
Lusid.Instrument.Bond
where
-- Passes the AsAt parameter value down to the underlying provider using the intermediate scalar variable.
AsAt = @@asAt
and
-- Using the #RESTRICT macro here improves performance by passing any WHERE clause filters specified
-- by the end user for either the Isin, DisplayName or MaturityDate columns down to the underlying provider.
-- Note also DisplayName is mapped to Instrument.
#RESTRICT
{
{ Isin: Isin },
{ Instrument: DisplayName },
{ MaturityDate: MaturityDate },
}
union all
select
'FxForward' as Type,
MaturityDate,
#SELECT
{
{ Isin: Isin },
{ Instrument: DisplayName },
}
from
Lusid.Instrument.FxForward
where
AsAt = @@asAt
and
#RESTRICT
{
{ Isin: Isin },
{ Instrument: DisplayName },
{ MaturityDate: MaturityDate },
}
)
where
-- Enables filtering (against the sub-query) on all the columns specified in the #SELECT_AGG macro.
#RESTRICT_AGG
#GROUP_BY
#LIMIT
enduse;
select * from @x
Note the following:
- When run, this view creates a
Views.MaturityDaysToExpiry
provider and makes it available to end users in the Luminesce catalog. - An end user can return or query five columns (
Type
,Isin
,Instrument
,MaturityDate
andDaysToExpiry)
and pass in one parameter (AsAt
). - The
Type
column can either return a bond retrieved byLusid.Instrument.Bond
or an FxForward retrieved byLusid.Instrument.FxForward
.
Publishing your custom view
Each time you run a view with the --provider
option, a provider with the specified name is automatically created and made available in the Luminesce catalog (note you may need to click the highlighted Refresh button to see it):
Note: It is more performant to omit the --provider
option while you are creating and testing your view.
To grant end users the necessary permissions to query Views.MaturityDaysToExpiry
, create a suitable access control policy and assign it to each LUSID user.
Using your live custom view
Once published, and providing suitably permissioned, an end user can write a Luminesce SQL query using the Views.MaturityDaysToExpiry
provider in the same way as any other provider.
For example, the following query retrieves the first 42 bond and FxForward instruments with ISIN identifiers:
The following query counts the number of bonds and FxForwards with ISIN identifiers and calculates the minimum, maximum and average number of days until instruments expire in each asset class:
Type, count(*) cnt, min(DaysToExpiry) minD, max(DaysToExpiry) maxD, Avg(DaysToExpiry) avgD
from
Views.MaturityDaysToExpiry
where
isin is not null
group by
Type
The following query counts the number of bond and FxForward instruments with ISIN identifiers and in total. It also calculates the average number of days to expiry for all instruments, for those instruments with ISIN identifiers, and for US instruments:
Type,
count(*) as countAll,
count(*) filter (where Isin is not null) as countWithIsin,
Avg(DaysToExpiry) avgDAll,
Avg(DaysToExpiry) filter (where Isin is not null) avgDWithIsin,
Avg(DaysToExpiry) filter (where Isin like 'US%') avgDFromUS
from
Views.MaturityDaysToExpiry
group by
Type
Managing your live custom view
Once published, you can manage the Views.MaturityDaysToExpiry
provider by running queries. For example, to:
- Retrieve the original query used to create the view:select Sql, At
from sys.logs.hcquery
where Sql like '%SetupView%' and Sql like '%MaturityDaysToExpiry%' and Sql not like '%HcQuery%' and ShowAll = true
order by At desc limit 1 - Examine the current content of the view:select Content from Sys.File where Name like '%MaturityDaysToExpiry%' and Extension = '.sql'
Deleting your custom view
To remove the Views.MaturityDaysToExpiry
provider from the Luminesce catalog (which means it will no longer be available for end users to query), run your view with the --deleteProvider
option. For example:
--provider=Views.MaturityDaysToExpiry
--deleteProvider
----
select 1 as deleting
enduse;
select * from @x