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. Het is wel mogelijk om bij het openen van de database de viewdefinities opnieuw te laden, bijvoorbeeld op Invantive Cloud met de On Startup SQL.
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:
View om over tabelfuncties te itereren
Veel platformen bieden tabelfuncties om toegang te krijgen tot hun gegevens. Deze tabelfuncties vereisen vaak een of meer vereiste parameters, waardoor iteratie over een reeks waarden nodig is om de volledige gegevensset op te halen.
Tabelfuncties met een of meer vereiste parameters zijn niet beschikbaar voor OData / Power BI.
Een view kan het proces van het ophalen van alle gegevens vereenvoudigen. In Teamleader Focus bijvoorbeeld vereist het ophalen van adressen voor contactpersonen één evaluatie van de tabelfunctie voor contactadressen. Dit kan worden bereikt met behulp van een view, zoals hieronder wordt gedemonstreerd:
select d.*
, p.* prefix with 'user_'
from Teamleader.V2.Contacts p
join Teamleader.V2.ContactAddressesByContactId(p.id) d
Hou er rekening mee dat het uitlezen van dergelijke views kan leiden tot zeer grote aantallen API-oproepen. Filters die gebruik maken van een where clausule worden meestal geëvalueerd voordat de tabelfunctie wordt benaderd, dus het is om prestatieredenen aan te raden om te filteren op kolomwaarden uit de sturende tabel (Contacten in het bovenstaande voorbeeld).
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.
Na het schonen van de cache zoals hieronder beschreven 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.
Nieuwe viewversies in Power BI
Als een view vervangen wordt door een andere versie of uitgebreid met nieuwe views, zal via Invantive Bridge Online en Invantive App Online in het algemeen de nieuwe versie niet of maar deels zichtbaar zijn. Het kan een week duren vooraleer de nieuwe definities zichtbaar worden in Power BI, terwijl ze in de SQL Editor van Invantive Cloud meteen zichtbaar zijn.
Invantive Bridge Online en Invantive App Online werken bijzonder agressief met zogenaamde “caching” om de prestaties ook bij grote volumes zo goed mogelijk te houden.
Deze caching kan gereset worden zodat het laden van de nieuwe versie van de view geforceerd wordt. Bij het opnieuw instellen van de caches worden alle cache-elementen verwijderd, inclusief de oude versies van incrementele tabellen zoals de *Incremental-tabellen op Exact Online. Bij het laden zal dit de eerste keer leiden tot merkbare vertraging; daarna zullen de prestaties weer vergelijkbaar met de situatie voorheen zijn.
Het opnieuw instellen van de Invantive Bridge Online cache gebeurt door de volgende stappen uit te voeren:
- Kijk welke Cloud-gebruiker gebruikt wordt in Power BI of PowerBI.com bij de credentials.
- Meld met deze Cloud-gebruiker aan op Bridge Online.
- Open het menu rechtsboven.
- Kies “Cache resetten” zoals zichtbaar in onderstaande afbeelding.
- Laad in Power BI de lijst van beschikbare tabellen.
- Verschijnt de nieuwe definitie niet?
- Controleer dan in Bridge Online Monitoring of het vinkje ontbreekt bij “Uit Cache” voor het laadverzoek. Als “Uit Cache” aangevinkt is, dan is blijkbaar cache resetten niet goed gelukt. Probeer nogmaals de cache te resetten.
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 |