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. However, it is possible to reload the view definitions when opening the database, for example on Invantive Cloud with the On Startup SQL.
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:
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.
New view versions in Power BI
When a view is replaced with another version or extended with new views, the new version in general will not or only partially be visible in Invantive Bridge Online and Invantive App Online. It may take one week before the new definitions become visible in Power BI, while they are immediately visible in the SQL Editor of Invantive Cloud.
Invantive Bridge Online and Invantive App Online aggressively use so-called “caching” to keep performance as good as possible even with large volumes.
This caching can be reset to force the loading of the new version of the view. When resetting the caches, all cached elements will be removed, including the old versions of incremental tables such as the *Incremental tables on Exact Online. When loading, this will cause noticeable delay the first time; after that, performance will be similar to before.
Resetting the Invantive Bridge Online cache is done by performing the following steps:
- Check which Cloud user is being used in Power BI or PowerBI.com in the credentials.
- Log on to Bridge Online with this Cloud user.
- Open the menu at the top right.
- Choose “Reset Cache” as visible in the image below.
- In Power BI, load the list of available tables.
- Does the new view definition not appear?
- If so, check in Bridge Online Monitoring if the check mark is missing from “From Cache” for the load request. If “From Cache” is checked, then apparently cache resetting failed. Try resetting cache again.
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 |