Brug af brugerdefinerede databasevisninger til SQL og BI-værktøjer

[forums]

Gå til den hollandske version

Invantive SQL tilbyder muligheden for at definere og bruge database-views fra og med version 22.0.14. Database-views er rettet mod slutbrugere af Invantive SQL og BI-værktøjer.

Denne artikel viser, hvordan man opretter og bruger database-views, og sammenligner database-views med de views, Invantive leverer med drivere.

Oprettelse af et database-view

Et database-view kan oprettes med create [or replace] [force] view-sætningen.

Følgende database-view opretter en relation mellem de to journal-tabeller i Exact Online, hvor ja/nej-matchet fra XML-tabellen tilføjes til alle kolonnerne fra journalerne i REST-tabellen:

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

Hent view-indhold

De nye view(s) gemmes i løbet af sessionen i datakataloget under kataloget DataDictionary og skemaet Custom. Det fuldt kvalificerede navn er derfor DataDictionary.Custom.TESTJOIN.

Når man opretter, ændrer og sletter views, er det ikke nødvendigt at angive, at viewet ligger i datakataloget; der er jo kun ét sted, det kan ligge. Ved forespørgsler er det nødvendigt at angive oprindelsen via aliaset for DataDictionary-driveren.

Således kan indholdet af viewet nemt hentes via en forespørgsel, f.eks.:

select *
from   testjoin@DataDictionary

Der er i øjeblikket ingen måde at gemme view-definitioner permanent i databasen, som er defineret via egenskaben DataDictionaryConnectionString for en Invantive-database. Det er dog muligt at genindlæse view-definitionerne, når databasen åbnes, f.eks. på Invantive Cloud med On Startup SQL.

Definitionen af et view kan hentes med tasten F4, når markøren står på view-navnet i Invantive Query Tool, men view-definitionen er også altid tilgængelig gennem datakatalog-viewet SystemViews:

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

med resultatet:

View til at iterere over tabelfunktioner

Mange platforme tilbyder tabelfunktioner til at tilgå deres data. Disse tabelfunktioner kræver ofte en eller flere obligatoriske parametre, hvilket gør det nødvendigt at iterere over en række værdier for at hente det komplette datasæt.

Tabelfunktioner med en eller flere obligatoriske parametre gøres ikke tilgængelige for OData/Power BI.

Et view kan forenkle processen med at hente alle data. I Teamleader Focus kræver det for eksempel én evaluering af tabelfunktionen for kontaktadresser at hente adresser til kontakter. Dette kan opnås ved hjælp af et view, som vist nedenfor:

select d.*
,      p.* prefix with 'user_'
from   Teamleader.V2.Contacts p
join   Teamleader.V2.ContactAddressesByContactId(p.id) d

Bemærk venligst, at adgang til sådanne views kan udløse et meget stort antal API-kald. Filtre, der bruger en where-klausul, evalueres typisk, før tabelfunktionen tilgås, så det anbefales af præstationsmæssige årsager at forsøge at filtrere på kolonneværdier fra den styrende tabel (Contacts i eksemplet ovenfor).

View med flere datacontainere

Database-views giver virkelig værdi, når en forespørgsel kombinerer data på tværs af flere datacontainere. Dette sparer en masse manuelt arbejde. Følgende forespørgsel sammenligner virksomhederne i Exact Online-administrationer med 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

hvilket resulterer i en liste over forskelle, hvor hvert virksomhedsnavn vises under det nummer, det har i Exact Online og/eller ActiveCampaign:

View med flere lande eller abonnementer af Exact Online

Exact Online har et separat miljø pr. land; afdeling 123123 er et andet selskab i Belgien end i Holland. Med et view er det muligt at behandle alle selskaber fra flere lande i ét hug, f.eks. i Power BI. Opret en datacontainer pr. Exact Online-abonnement inden for en database, f.eks. med aliasserne eolnl og eolbe. Følgende forespørgsel er et eksempel på sammenfletning af kunder/leverandører i Holland med Belgien:

create or replace force view eolall_accounts_r
as
select *
from AccountsIncremental@eolnl
--
-- Union all er hurtigere end union, fordi dedupliceringen
-- kan undgås på grund af unikke værdier for kolonnen Id
-- med datatypen GUID.
--
union all
select *
from AccountsIncremental@eolbe

select *
from eolall_accounts_r@DataDictionary

Dette view kan lægges i startup-SQL’en.

Views med fejl

Viewet kontrolleres for syntaktisk korrekthed under oprettelsen, men udføres ikke. Selv SQL-sætninger med syntaksfejl kan bruges til et view ved at angive force-indstillingen. På udførelsestidspunktet kontrolleres både syntaks og gyldighed i den aktuelt aktive database.

Brug af views i Power BI

Database-views kan – ligesom driver-views – bruges i Power BI, Power Query og Azure Data Factory. Vi anbefaler at inkludere view-definitionen i databasens On Startup SQL.

Efter rensning af cachen som beskrevet nedenfor vises viewet automatisk i listen over tabeller.

At definere mere end nogle få dusin database-views i On Startup SQL gør det markant langsommere at åbne databasen.

Nye view-versioner i Power BI

Når et view erstattes med en anden version eller udvides med nye views, vil den nye version generelt ikke eller kun delvist være synlig i Invantive Bridge Online og Invantive App Online. Det kan tage op til en uge, før de nye definitioner bliver synlige i Power BI, mens de straks er synlige i SQL Editor i Invantive Cloud.

Invantive Bridge Online og Invantive App Online bruger aggressivt såkaldt „caching“ for at holde ydeevnen så god som muligt, selv ved store datamængder.

Denne cache kan nulstilles for at tvinge indlæsningen af den nye version af viewet. Når cacherne nulstilles, fjernes alle cachelagrede elementer, herunder de gamle versioner af inkrementelle tabeller såsom *Incremental-tabellerne i Exact Online. Ved indlæsning vil dette medføre en mærkbar forsinkelse første gang; derefter vil ydeevnen være som før.

Nulstilling af Invantive Bridge Online-cachen gøres ved at udføre følgende trin:

• Tjek, hvilken Cloud-bruger der bruges i Power BI eller PowerBI.com i legitimationsoplysningerne.
• Log på Bridge Online med denne Cloud-bruger.
• Åbn menuen øverst til højre.
• Vælg „Reset Cache“, som vist på billedet nedenfor.
• Indlæs listen over tilgængelige tabeller i Power BI.
• Vises den nye view-definition ikke?
• Hvis ikke, så tjek i Bridge Online Monitoring, om afkrydsningen mangler ved „From Cache“ for indlæsningsanmodningen. Hvis „From Cache“ er markeret, er cache-nulstillingen tilsyneladende mislykkedes. Prøv at nulstille cachen igen.

Driver-views

Database-views supplerer de faste (medfølgende) views for en bestemt driver. Disse driver-views kan kun kombinere data fra tabeller, der tilhører den pågældende driver. Driver-views forenkler brugen af den platform, driveren er beregnet til, men kan ikke ændres.

Forskellene mellem database-views og driver-views kan ses i tabellen nedenfor:

	Database-view	Driver-view
Tilgængelig på alle sites	Nej	Ja
Kan tilpasses af brugeren	Ja	Nej
Kombiner data på tværs af drivere	Ja	Nej