What is a custom SQL view?

A SQL View is a virtual table or query that returns data from 1 or more tables in a format which you define. This enables extra data manipulation to the data in the tables, for example:

• Extra data to be extracted from reference tables
• Columns to be summed up, averaged, etc.
• Dates to be returned in a certain format or codes interpreted
• Data to be filtered
• Only certain columns to be returned to the user in the case of data sensitivity
• Sequencing the columns in a special way
• Hiding certain columns

Registered vs unregistered view?

An unregistered view is a view created in the database that is not exposed via the frontend nor via the OData API. Unregistered views can be used by other custom views and are still available to 3rd party tools which can directly access the xMart database.

A registered view is a view that is also a view created in the database but is available via the frontend and via the OData API.

How to create a view

As of version 4.28, there are 2 possible SQL editors to create a custom SQL view. The recommended way is to use the SQL editor in the xMart UI. The second way, which is more complicated to setup, not available to everyone and may not always be supported, is using SQL Server Management Studio (SSMS).

But before we desribe these different editors, these are the basics of creating SQL regardless of which editor you use.

The basics

Schemas

When querying the tables or views in your mart, it is necessary to prefix them by their database schema. A “schema” is effectively a folder that contains your objects. Every mart has 2 schemas/folders:

• {mart code}: Contains the storage tables created in the xMart UI. The schema is the same as the mart code.
• {mart code}_c: Contains the custom SQL _views. The schema is the mart code plus the suffix “_c” to indicate “custom” views. Custom SQL views created using the xMart SQL editor are automatically put into this schema but if you are using SSMS, you must manually indicate it in the CREATE or ALTER statement.

For example, if your mart code is “OUR_MART” and you want to make a custom SQL view of a table with code “DATA_TABLE”, the SQL would be:

SELECT field_1, field_2 FROM OUR_MART.DATA_TABLE

If you save your new custom view with the code “V_DATA_TABLE” and then create a second custom view that retrieves data from V_DATA_TABLE, the schema would be OUR_MART_C in that case:

SELECT field_1, field_2 FROM OUR_MART_C.V_DATA_TABLE

Recommendations

We suggest using WITH (NOLOCK) because it prevents the view being held up if the table is being updated or holding up an update which is waiting.

We also recommend returning the Sys_PK column because it significantly speeds up display of the custom view in the UI and access via the API. This is especially true for large views, which must page the data. As this is the order in which the data is stored, it means that for large views, the data is paged and therefore displayed more quickly. The optimize custom views article has more tips on improving custom view performance.

Your custom SQL view should generate the required results suitably quickly. Normally the top 10 rows should return in under 5 seconds. If not, the the view will also have poor performance when accessed via the UI and via the API.

An example of some sql for mart code “training_cf” using “WITH (NOLOCK)”

SELECT
	cntry.CODE_ISO_3 AS Country_ISO3,
	cntry.NAME_SHORT_EN AS Country_Name,
	curr.CODE_ISO_3 AS Currency_ISO3,
	curr.TITLE AS Currency_Name,
	cntry.Sys_PK
FROM
	training_cf.REF_COUNTRY cntry WITH (NOLOCK)
INNER JOIN
	training_cf.REF_COUNTRY_CURRENCY rcc WITH (NOLOCK) 
ON
	rcc.COUNTRY_FK = cntry.Sys_RowTitle
INNER JOIN
	training_cf.REF_CURRENCY curr WITH (NOLOCK)
ON
	curr.Sys_RowTitle = rcc.CURRENCY_FK

We would also recommend putting a comment block at the top so that the view history is easy to track as well as making it easier to check if the same version of a view has been deployed in UAT and PROD.

/***************************************************************************
	Title : training_cf_c.GetCountryCurrency

	Description:
		Provides Currency Names and ISO3 Codes by Country

	Change History:
		Date		Author          Version	Description
		----------	--------------- -------	------------------------------
		2024-03-25	Chris Faulkner	1.00	Created

***************************************************************************/
SELECT .....

Limitations

SELECT statements can be arbitarily complex but only the mart’s and REFMART’s schemas can be queried (use “REFMART” as the database schema).

All of the following are supported: joins, top, order by, group by, aggregations, common table expressions, subqueries, date and math functions, etc.

No “dangerous” or other type of SQL is supported.

Using the xMart SQL editor

To access the xMart SQL editor, navigate to Model/Custom Views in your mart and either create a new view using the “+ New” button or open an existing SQL view. Existing SQL views are listed on the left and grouped by whether they are registered or not registered.

You can type your SQL into the editor.

The editor can be opened in full-screen mode using the Maximize button (use the same button to restore the normal view).

• Press Run to test/run the entire query or a selected part of the query.
• Press Format to format the query.
• Use the comment/uncomment button to toggle commenting on selected lines.
• Use the Save button to save the query, even if incomplete or invalid. Saving a query does not create a new version of the query and does not affect the current version used by the data catalog and the OData API.
• Press Publish to apply the query to the database and create a new version. The new version becomes immediately available in the data catalog and OData API.
• As before, use Register View to make the view avaialble on the Data page and via API. If not registered, the view is still created in the database and is available from 3rd party tools like Power BI or Site Finity. Unregistered views can also be used in pipelines using the GetDb command (see below).
• Use Drop View to permanetnly hard delete the view.

For very large views, a miniaturized view of the query appearing on the right can be used to quickly navigate between sections.

The CUSTOM_VIEW_MANAGE permission is required to be able to create and edit custom SQL views. During the 4.28 upgrade process, the CUSTOM_VIEW_MANAGE permission was added to every role which had MART_MANAGE. Those with MART_MANAGE can further assign the CUSTOM_VIEW_MANAGE to those who need it.

Currently, CUSTOM_VIEW_MANAGE permission gives the user the ability to create and edit any custom view in the mart and also to use any table or view inside the custom view. This is the same permissions as currently exists when editing views directly in the database but this all-or-nothing security model may change in future versions.

Using SSMS

Obtain permission for WIMS account

To connect to the database you will need a WIMS account. This will need to be added to a group to enable access to the xMart database. In order to do this, you will need to raise a request using the following form

Request direct xMart DB access

You will need to fill out which Mart you would like access to and the DB rolw should be set to Admins (create custom views).

Connect to the database

Connect to the server via SQL Server Management Studio (SSMS) or a similar tool. A link to download SSMS can be found here.

The server names are

From here, you should be able to see the system views and and custom views which have been created.

The system views are the ones which xMart creates to read the data on the table. The reason it does this is because the system views perform lookups on the Foreign Key tables and so return whatever is set to the Title when the table was created. These are what the view will be reading. The user does not have the rights to edit or change these views.

### In SSMS it is necessary to insert a CREATE OR ALTER VIEW AS statement before the start of the SELECT. The reason to use CREATE OR ALTER is because if the view already exists, it will be overwritten and the statement will not give an error.

CREATE OR ALTER VIEW training_cf_c.GetCountryCurrency
AS
SELECT * from training_cf.REF_COUNTRY cntry WITH (NOLOCK)

Register the view in xMart

If views are registered in xMart, they are available from the front end, can be read via the API and can also be read to feed into pipelines using GetMart.

To register a view click the “Register View button” (to unregister click “Unregister”)