Using custom database views for SQL and BI tools

Go to Dutch version

Invantive SQL offers the possibility to define and use database-views starting release 22.0.14. Database views are aimed at end users of Invantive SQL and BI tools.

This article shows how to create and use database views and compares database views with the views Invantive delivers with drivers.

Creating a database view

A database view can be created with the create [or replace] [force] view-statement.

The following database view creates a relationship between the two journals tables in Exact Online, where from the XML table the yes/no match is added to all the columns from the journals in the REST table:

create or replace force view testjoin
as
select coalesce(t1.code, t2.code_attr) code
,      t2.OWNBANKACCOUNT_GLPAYMENTINTRANSIT_MATCHING_ATTR
,      t1.Description
from.  ExactOnlineREST..journals@eol t1
full
outer
join   ExactOnlineXML..journals@eol t2
on     t1.code = t2.code_attr

Retrieve View Contents

The new view(s) are stored for the duration of the session in the data dictionary under the catalog DataDictionary and the schema Custom. The full qualified name is therefor DataDictionary.Custom.TESTJOIN.

When creating, modifying and deleting views, it is not necessary to specify that the view is in the data dictionary; after all, there is only one place it can be. When querying, it is necessary to specify the origin via the alias of the DataDictionary driver.

Thus, the contents of the view can easily be retrieved via a query, such as:

select *
from   testjoin@DataDictionary

There is currently no way to permanently store view definitions in the database defined via the DataDictionaryConnectionString property of an Invantive database.

The definition of a view can be retrieved with the F4 key when the cursor is on the view name in the Invantive Query Tool, but the view definition is also always available through the data dictionary view SystemViews:

select PROVIDER_NAME
,      Name
,      QUERY
from   SYSTEMVIEWS@DataDictionary
where  PROVIDER_NAME = 'DataDictionary'

with the result:

SystemViews@DataDictionary

View with multiple data containers

Database views really add value when a query combines data across multiple data containers. This saves a lot of manual work. The following query compares the companies in Exact Online administrations with ActiveCampaign:

create or replace force view compare_companies_across_data_containers_v
as
select coalesce(t1.name, t2.name) 
       name
       label 'Relationship name
,      t1.code 
       exact_online_code
       label 'Exact Online Relation Number
,      t2.id 
       active_campaign_id
       Tag 'ActiveCampaign ID
from   AccountsIncremental@eol t1
full
outer
join   Accounts@ac t2
on     t2.name = t1.name

select *
from COMPARE_COMPANIES_ACROSS_DATA_CONTAINERS_V@DataDictionary

resulting in a list of differences where each company name is shown under which number it is in Exact Online and/or ActiveCampaign:

View with multiple countries or subscriptions of Exact Online

Exact Online has a separate environment per country; the division 123123 is a different company in Belgium than in the Netherlands. With a view it is possible to process all companies of multiple countries in one go, for example in Power BI. Create a data container per Exact Online subscription within a database, for example with the aliases eolnl and eolbe. The following query is an example of merging the customers/suppliers in the Netherlands with Belgium:

create or replace force view eolall_accounts_r
as
select *
from AccountsIncremental@eolnl
--
-- Union all is faster than union because the deduplication
-- can be omitted because of unique values for the column Id
-- with the GUID data type.
--
union all
select *
from AccountsIncremental@eolbe

select *
from eolall_accounts_r@DataDictionary

This view can be put into the startup SQL.

Views with errors

The view is checked for syntactic correctness during creation, but not executed. Even SQL statements with syntactic errors can be used for a view by passing the force option. At the time of execution, both syntax and validity are checked in the currently active database.

Using Views in Power BI

Database views can - like the driver views - be used in Power BI, Power Query and Azure Data Factory. We recommend to include the view definition in the On Startup SQL of the database.

The view then automatically appears in the list of tables.

Defining more than a few dozen database views in the On Startup SQL slows down opening the database significantly.

Driver Views

Database views complement the fixed (supplied) views for a specific driver. These driver-views can only combine data from tables belonging to the specific driver. Driver views simplify the use of the platform the driver is intended for, but cannot be modified.

The differences between database-views and driver-views can be found in the table below:

Database-view Driver-view
Available on every site No Yes
Customizable by user Yes No
Combine data across drivers Yes No