Eigen database-views gebruiken voor SQL en BI-tools

Go to English version

Invantive SQL biedt vanaf release 22.0.14 de mogelijkheid om database-views te definiëren en gebruiken. Database-views zijn gericht op eindgebruikers van Invantive SQL en BI-tools.

Dit artikel toont hoe database-views gemaakt en gebruikt kunnen worden, en vergelijkt ze met de views die Invantive meelevert op drivers.

Aanmaken Database-view

Een database-view kan aangemaakt worden met het create [or replace] [force] view-statement.

De volgende database-view legt een relatie tussen de twee dagboektabellen in Exact Online, waarbij uit de XML-tabel het wel/niet matchen toegevoegd wordt aan alle kolommen uit de dagboeken uit de REST-tabel:

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

Inhoud View Opvragen

De nieuwe view(s) worden opgeslagen voor de duur van de sessie in het data dictionary onder de catalogus DataDictionary en het schema Custom. De volledige naam is daarom DataDictionary.Custom.TESTJOIN.

Bij het maken, aanpassen en verwijderen van views hoeft niet opgegeven te worden dat de view in het data dictionary staat; er is tenslotte maar één plek waar hij kan staan. Bij het opvragen van een query is het wel nodig om de herkomst op te geven via de alias van de DataDictionary driver.

De inhoud van de view kan dus eenvoudig opgevraagd worden via een query, zoals:

select *
from   testjoin@DataDictionary

Er is op dit moment geen mogelijkheid om de viewdefinities blijvend vast te leggen in de database gedefinieerd via de DataDictionaryConnectionString eigenschap van een Invantive database.

De definitie van een view kan opgevraagd worden met de toets F4 als de cursor op de viewnaam staat in het Invantive Query Tool, maar is ook altijd beschikbaar via de data dictionary view SystemViews:

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

met als resultaat:

SystemViews@DataDictionary

View met meerdere datacontainers

Database-views voegen pas echt waarde toe als een query over meerdere datacontainers heen gegevens combineert. Dat bespaart veel handmatig werk. De volgende query vergelijkt de relaties in Exact Online administraties met ActiveCampaign:

create or replace force view compare_companies_across_data_containers_v
as
select coalesce(t1.name, t2.name) 
       name
       label 'Relatienaam'
,      t1.code 
       exact_online_code
       label 'Exact Online Relatienummer'
,      t2.id 
       active_campaign_id
       label '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

met als resultaat een verschillenlijst waarbij per relatienaam getoond wordt onder welk nummer die in Exact Online en/of ActiveCampaign staat:

View met meerdere landen of abonnementen van Exact Online

Exact Online heeft een aparte omgeving per land; de divisie 123123 is een andere administratie in België dan in Nederland. Met een view is het mogelijk om alle administraties van meerdere landen in 1x te verwerken, bijvoorbeeld in Power BI. Maak per Exact Online abonnement een datacontainer binnen een database, bijvoorbeeld met de alias eolnl en eolbe. De volgende query is een voorbeeld van het samenvoegen van de relaties in Nederland met België:

create or replace force view eolall_accounts_r
as
select *
from   AccountsIncremental@eolnl
--
-- Union all is sneller dan union omdat het ontdubbelen
-- achterwege kan blijven door unieke waardes voor de kolom Id
-- met het GUID data type.
--
union all
select *
from   AccountsIncremental@eolbe

select *
from   eolall_accounts_r@DataDictionary

Deze view kan in de startup SQL gezet worden.

Views met fouten

De view wordt tijdens het aanmaken gecontroleerd op syntactische juistheid, maar niet uitgevoerd. Zelfs SQL-statements met syntactische fouten kunnen aangemaakt worden door de force optie mee te geven. Op het moment van selectie worden zowel de syntax als de geldigheid in de huidig actieve database gecontroleerd.

Views gebruiken in Power BI

De database-views kunnen - net zoals de driver-views - gebruikt worden in Power BI, Power Query en Azure Data Factory. Het advies is om de viewdefinitie op te nemen in de On Startup SQL van de database.

Daarna verschijnt de view automatisch in de lijst van tabellen.

Het definiëren van meer dan enkele tientallen database-views in de On Startup SQL vertraagt het openen van de database meetbaar.

Driver-views

Database-views complementeren de vaste (meegeleverde) views voor een specifieke driver aan met nieuwe mogelijkheden. Deze driver-views kunnen alleen data combineren uit tabellen die horen bij de specifieke driver. Driver-views vereenvoudigen het gebruik van het platform waar de driver voor bedoeld is, maar kunnen niet gewijzigd worden.

De verschillen tussen database-views en driver-views zijn te vinden in onderstaande tabel:

Database-view Driver-view
Bij elke installatie beschikbaar Nee Ja
Aanpasbaar door gebruiker Ja Nee
Combineren tabellen meerdere datacontainers Ja Nee