Views:

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 and MaturityDate columns from these providers, with DisplayName renamed to Instrument.
  • 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:

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:

@x = use Sys.Admin.SetupView
--help
enduse;


Alternatively, run the following query to retrieve the help content as a table:

select distinct CustomSyntax from Sys.Registration where Name = 'Sys.Admin.SetupView'

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:

@x = use Sys.Admin.SetupView [with @@scalar_var1 [, @@scalar_var2...]]
<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.
    OptionExplanation
    --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.

    --deleteProviderRemoves 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 @x

    Each 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 an ENDUSE 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.

MacroExplanation
#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 #SELECT macros in a view. Each <column> argument consists of a column name and an SQL statement that defines how that column is populated, separated by a colon. For example:

@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 Instrument column that is populated with values retrieved from the DisplayName column of the Lusid.Instrument provider. If the end user does not explicitly request the Instrument column in a particular query, the view does not retrieve the DisplayName column from the underlying provider.
 

You could omit the #SELECT macro and create this view as follows:

@x = use Sys.Admin.SetupView
----
select DisplayName as Instrument from Lusid.Instrument
enduse;

…but this would impair performance. The view always retrieves the DisplayName column from the underlying provider whether the end user requests the Instrument column or not, increasing bandwidth and round-trip time unnecessarily.

Note you can append one or more characters to a column name to distinguish that column in the view:

  • Append + to make the column a primary key.
  • Append ^ to make the column a 'main' field (one returned by a query that starts select ^ from...)
  • Append ~ followed by either Boolean, Int, BigInt, Double, Decimal, Text, Date or DateTime to explicitly set the data type.
  • Append |"<string>" to give the column a description in the catalog (only " may not appear in that description).

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_AGGUse 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 Date or DateTime must be assigned to an intermediate scalar variable; see the example section below. This is a temporary restriction that may be removed shortly.

#LIMITInserts 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.
#OFFSETFills in the OFFSET clause as required by the SQL dialect.
#DISTINCTInserts 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.
#GROUPBYInserts 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_BYAllows 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, MaturityDateand DaysToExpiry) and pass in one parameter (AsAt).
  • The Type column can either return a bond retrieved by Lusid.Instrument.Bond or an FxForward retrieved by Lusid.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:

select * from Views.MaturityDaysToExpiry where isin is not null limit 42


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:

select
  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:

select
  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:

@x = use Sys.Admin.SetupView
--provider=Views.MaturityDaysToExpiry
--deleteProvider
----
select 1 as deleting
enduse;
select * from @x
Keywords: view, luminesce, setupview