What is a SQL view?

A SQL View is a SELECT statement which can be registered as a view. 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, avaraged 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

When the views are registered in xMart, they appear just like tables in the interface and can be accessed via the front end or via the API. If a view on a table is made public, the whole table is not made public so both confidential and public data can be stored on the same table and the view can be made to only read the public fields.

Enable WIMS account to connect to the custom view schema

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

How to 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

• UAT SSDB103.who.int
• PROD SSDB234.who.int

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.

How to create a view

Create the SQL

User defined views can only be created in the _c schema of the mart. So in this case, views could only be created in training_cf_c

The next step is to write some SQL

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 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 when the data is viewed in xMart, the default sort is done by Sys_PK if it is there. As this is the order in which the data is stored, it means that for large views, the data is displayed more quickly. The optimize custom views article has more tips on improving custom view performance.

Create the view

If the SQL is generating the required results and runs suitably quickly (normally the top 10 rows should return in under 5 seconds) then the next step is to create a view.

To do this, 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
	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

***************************************************************************/
CREATE OR ALTER VIEW training_cf_c.GetCountryCurrency
AS
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
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

Register the view in xMart

If views are registered in xMart, they are treated the same way as tables. This means that 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 a view can be registered by going to the Admin Catalog and clicking the + next to the Custom views

To a view can be registered by going to the Custom Views page

clicking on the unregistered view

and pressing register

or by going to the Admin Catalog and clicking the + next to the Custom views

Both of these methods will give you the registation pop-up.