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.
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
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
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
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
select PROVIDER_NAME , Name , QUERY from SYSTEMVIEWS@DataDictionary where PROVIDER_NAME = 'DataDictionary'
with the result:
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:
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
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.
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.
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.
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.
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:
|Available on every site||No||Yes|
|Customizable by user||Yes||No|
|Combine data across drivers||Yes||No|