Creating custom views (Sys.Admin.SetupView)

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;

Getting help

You can access the latest documentation 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 itself a direct provider, and therefore follows these syntactic conventions.

The precise syntax is as follows:

@x = use Sys.Admin.SetupView [with @@scalar_var, @table_var]
<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:

  • You can optionally pass scalar variables and/or table variables into the view. Scalar variables can be used directly in the <sql-section> encapsulated in {}, for example {@@scalar_var}. Table variables, on the other hand, must be declared as parameters.

  • 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.

      Option

      Explanation

      --provider=<name>

      Creates a custom 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.

      --documentationLink=<text><url>[;<text><url>...]

      Displays one or more hyperlinks in the summary dialog for the view (available from the information icon next to the view name in the Luminesce catalog) that enable an end user to visit documentation web pages. For example:

      --documentationLink=[Doc1](https://mydocs/myview/doc1);[Doc2](https://mydocs/myview/doc2)

      --useDryRun

      Equivalent to setting pragma DryRun = True when creating the view. More information.

      This option should never be used until you have created the view without it and tested it fully. It is intended for CI/CD pipelines deploying views, especially those with data side effects that should not be regularly triggered.

      Note you may need to add a pragma such as PRAGMA [DryRunMock_ProviderX.Xyz] = 'select 123 as X, ''abc'' as Y' into your SQL - where you give an example of what the data should be - in order for this to behave correctly.

      --parameters
      <name>,<type>,<value>

      ,true/false[,description]

      Declares parameters. Each parameter declaration must be on a separate line after --parameters, so if specified this option should always be last in the list.

      Note: To insert a parameter value at an appropriate point in the view, use the #PARAMETERVALUE macro.

      A parameter declaration defines either:

      • A scalar variable that can be passed into the view by an end user. This is not the same as a scalar variable passed into the view in code using the WITH keyword; these do not need to be declared. Each parameter declaration on behalf of an end user:

        • Must have a unique name.

        • Must have a type of either Boolean, Int, BigInt, Double, Decimal, Text, Date, DateTime.

        • Must have an initial value appropriate to the data type. To include quote or comma characters in a Text value, encapsulate it in double quote marks (see the example below). Specify true as the fourth argument to make the initial value also the default value; specify false to use the initial value only when setting up the view, and not when end users query the published version.

        • Can have a description that appears as a tooltip for end users in the Luminesce catalog. To include quote characters in the tooltip, encapsulate them in double quote marks:

        @x = use Sys.Admin.SetupView
--provider=Views.MyView
--description="This is a tooltip for the view as a whole"
--parameters
IsinToSearch,Text,GB124892739,true
BondType,Text,"Vanilla, Complex and Inflation-Linked",true,"This is a parameter tooltip"
AsAt,DateTime,0001-01-01,false,"This will ""appear"" as a parameter tooltip"
----

      • A table variable that can either be passed into the view by an end user or in code. In either case, the table itself must be defined using the WITH keyword. Each parameter declaration:

        • Must have a unique name.

        • Must have a type of Table.

        • Must have a value that is the same as the table declared using the WITH keyword.

        • Should be set to true to mandate that an end user passes in a suitable table of data when using the view, or false for this to be optional. The default is true.

        • Can have a description that appears as a tooltip for end users in the Luminesce catalog. To include quote characters in the tooltip, encapsulate them in double quote marks:

        @table_in_code = select 123 as x, 'abc' as y;
@table_end_user = select 456 as x, 'def' as y;
@x = use Sys.Admin.SetupView with @table_in_code, @table_end_user
--provider=Views.MyView
--description="This is a tooltip for the view as a whole"
--parameters
CodeTable,Table,@table_in_code,false,"The end user may or may not pass in a table of data"
EndUserTable,Table,@table_end_user,true,"The end user must pass in a table of data"
----

      --variableShape

      Creates a view with 'direct provider' behavior instead of the default 'data provider'. Data vs direct providers.

      This is useful if data returned is likely to vary in shape between queries, for example a view that reads from a file in Drive storing different information each day.

      Note the #LIMIT and #PARAMETERVALUE macros can be used in the <sql-section> but that most other macros have no effect. Note also that scalar as well as table variables passed into the view using the WITH keyword must have parameter declarations with matching names, for example:

      @@my_scalar_var = select 123;
@my_table_var = select 456 as x, 'def' as y;
@x = use Sys.Admin.SetupView with @@my_scalar_var, @my_table_var
--provider=Views.MyView
--variableShape
--parameters
Scalar,Text,@@my_scalar_var,false
Table,Table,@my_table_var,false

      This is different to 'data provider' views, where only table variables passed into the view must have parameter declarations.

      --allowExecuteIndirectly
      (Additional privilegesrequired)

      Allows end users to query providers within the view even if they are not entitled to use those providers directly.

      Note to specify this option you (the view author) must have access to:

      • All providers used in the view.

      • AdditionalAccess entitlements to Sys.Admin.SetUpView. To get this additional access, create a policy consisting of the following JSON, assign it to a suitable role, and assign that role to your LUSID user (or any LUSID user wishing to create permissioned custom views):

      {
    "description": "Grants access to create allowExecuteIndirectly views",
    "applications": [
        "Honeycomb"
    ],
    "grant": "Allow",
    "selectors": [
        {
            "idSelectorDefinition": {
                "identifier": {
                    "code": "*",
                    "scope": "Honeycomb"
                },
                "actions": [
                    {
                        "scope": "Honeycomb",
                        "activity": "AdditionalAccess",
                        "entity": "Feature"
                    }
                ],
                "name": "AdditionalAccess Sys.Admin.SetupView",
                "description": "Sys.Admin.SetupView may create views needing only ExecuteIndirectly"
            }
        }
    ],
    "when": {
        "activate": "2019-09-19T09:47:23.4550399+00:00",
        "deactivate": "9999-12-31T23:59:59.9999999+00:00"
    }
}

      Note end users must be given sufficient access to data and the underlying LUSID features used within the permissioned custom view. See how to set up access control for different users.

      --deleteProvider

      Removes a published custom view 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.

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 #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_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 at an appropriate point in the view. For example:

@table_end_user = select 456 as x, 'def' as y;
@x = use Sys.Admin.SetupView with @table_end_user
--parameters
IsinToSearch,Text,GB124892739,true,"The end user must pass in an ISIN"
EndUserTable,Table,@table_end_user,true,"The end user must pass in a suitable table of data"
----
@@isin = select #PARAMETERVALUE(IsinToSearch);
@data = select * from #PARAMETERVALUE(EndUserTable);
select * from Lusid.Instrument where Type is @@isin
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.

#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 query creates a Views.MaturityDaysToExpiry custom view 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 custom view 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. Note you must also grant the creator of the custom view the necessary permissions to be able to query it as an end 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 custom view in the same way as any other view or provider in the Luminesce catalog.

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 custom view 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 custom view 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