Invantive SQL offre la possibilità di definire e utilizzare viste di database (database-views) a partire dalla release 22.0.14. Le viste di database sono pensate per gli utenti finali di Invantive SQL e degli strumenti BI.
Questo articolo mostra come creare e utilizzare le viste di database e confronta le viste di database con le viste fornite da Invantive tramite i driver.
Creazione di una vista di database
Una vista di database può essere creata con l’istruzione create [or replace] [force] view.
La seguente vista di database crea una relazione tra le due tabelle journals in Exact Online, dove dalla tabella XML viene aggiunto il valore sì/no della corrispondenza a tutte le colonne dei journals nella tabella REST:
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
Recupero del contenuto della vista
Le nuove viste vengono memorizzate per la durata della sessione nel dizionario dati, sotto il catalogo DataDictionary e lo schema Custom. Il nome completo è quindi DataDictionary.Custom.TESTJOIN.
Quando si crea, modifica o elimina una vista, non è necessario specificare che la vista si trova nel dizionario dati; dopotutto, è l’unico posto in cui può trovarsi. Quando si effettua una query, invece, è necessario specificare l’origine tramite l’alias del driver DataDictionary.
Pertanto, il contenuto della vista può essere facilmente recuperato tramite una query, come ad esempio:
select *
from testjoin@DataDictionary
Attualmente non esiste un modo per memorizzare in modo permanente le definizioni delle viste nel database definito tramite la proprietà DataDictionaryConnectionString di un database Invantive. Tuttavia, è possibile ricaricare le definizioni delle viste all’apertura del database, ad esempio su Invantive Cloud tramite l’On Startup SQL.
La definizione di una vista può essere recuperata premendo il tasto F4 quando il cursore si trova sul nome della vista nell’Invantive Query Tool, ma la definizione della vista è sempre disponibile anche tramite la vista del dizionario dati SystemViews:
select PROVIDER_NAME
, Name
, QUERY
from SYSTEMVIEWS@DataDictionary
where PROVIDER_NAME = 'DataDictionary'
con il seguente risultato:
Vista per iterare su funzioni tabella
Molte piattaforme forniscono funzioni tabella per accedere ai propri dati. Queste funzioni tabella spesso richiedono uno o più parametri obbligatori, rendendo necessaria un’iterazione su un intervallo di valori per recuperare l’intero set di dati.
Le funzioni tabella con uno o più parametri obbligatori non sono rese disponibili a OData / Power BI.
Una vista può semplificare il processo di recupero di tutti i dati. Ad esempio, in Teamleader Focus, per recuperare gli indirizzi dei contatti è necessaria una valutazione della funzione tabella per gli indirizzi dei contatti. Questo può essere ottenuto utilizzando una vista, come dimostrato di seguito:
select d.*
, p.* prefix with 'user_'
from Teamleader.V2.Contacts p
join Teamleader.V2.ContactAddressesByContactId(p.id) d
Si noti che l’accesso a queste viste può generare un numero molto elevato di chiamate API. I filtri che utilizzano una clausola where vengono tipicamente valutati prima che venga eseguita la funzione tabella, quindi per motivi di prestazioni si consiglia di cercare di filtrare sui valori delle colonne della tabella guida (Contacts nell’esempio sopra).
Vista con più contenitori di dati
Le viste di database aggiungono davvero valore quando una query combina dati provenienti da più contenitori di dati. Questo fa risparmiare molto lavoro manuale. La query seguente confronta le aziende nelle amministrazioni di Exact Online con 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
con il risultato di un elenco di differenze in cui per ogni nome di azienda viene mostrato con quale numero è presente in Exact Online e/o in ActiveCampaign:
Vista con più paesi o sottoscrizioni di Exact Online
Exact Online ha un ambiente separato per ciascun paese; la divisione 123123 è un’azienda diversa in Belgio rispetto ai Paesi Bassi. Con una vista è possibile elaborare in un’unica operazione tutte le aziende di più paesi, ad esempio in Power BI. Crea un contenitore di dati per ogni sottoscrizione di Exact Online all’interno di un database, ad esempio con gli alias eolnl e eolbe. La query seguente è un esempio di unione dei clienti/fornitori dei Paesi Bassi con quelli del Belgio:
create or replace force view eolall_accounts_r
as
select *
from AccountsIncremental@eolnl
--
-- Union all è più veloce di union perché la deduplicazione
-- può essere omessa grazie ai valori univoci della colonna Id
-- con il tipo di dati GUID.
--
union all
select *
from AccountsIncremental@eolbe
select *
from eolall_accounts_r@DataDictionary
Questa vista può essere inserita nell’On Startup SQL.
Viste con errori
La vista viene controllata per verificarne la correttezza sintattica durante la creazione, ma non viene eseguita. Anche le istruzioni SQL con errori sintattici possono essere utilizzate per una vista passando l’opzione force. Al momento dell’esecuzione, sia la sintassi che la validità vengono verificate nel database attualmente attivo.
Utilizzo delle viste in Power BI
Le viste di database possono essere utilizzate - come le viste del driver - in Power BI, Power Query e Azure Data Factory. Si consiglia di includere la definizione della vista nell’On Startup SQL del database.
Dopo aver svuotato la cache come descritto di seguito, la vista appare quindi automaticamente nell’elenco delle tabelle.
Definire più di qualche decina di viste di database nell’On Startup SQL rallenta notevolmente l’apertura del database.
Nuove versioni delle viste in Power BI
Quando una vista viene sostituita con un’altra versione o estesa con nuove viste, la nuova versione in generale non sarà visibile, o lo sarà solo parzialmente, in Invantive Bridge Online e Invantive App Online. Potrebbe essere necessaria fino a una settimana prima che le nuove definizioni diventino visibili in Power BI, mentre sono immediatamente visibili nell’Editor SQL di Invantive Cloud.
Invantive Bridge Online e Invantive App Online utilizzano in modo intensivo il cosiddetto «caching» per mantenere le prestazioni il più possibile ottimali anche con grandi volumi di dati.
Questa cache può essere reimpostata per forzare il caricamento della nuova versione della vista. Quando si reimposta la cache, tutti gli elementi memorizzati nella cache verranno rimossi, incluse le vecchie versioni delle tabelle incrementali come le tabelle *Incremental su Exact Online. Durante il caricamento, questo causerà un ritardo evidente la prima volta; dopodiché, le prestazioni torneranno simili a prima.
Per reimpostare la cache di Invantive Bridge Online, seguire questi passaggi:
- Verificare quale utente Cloud viene utilizzato in Power BI o PowerBI.com nelle credenziali.
- Accedere a Bridge Online con questo utente Cloud.
- Aprire il menu in alto a destra.
- Scegliere “Reset Cache” come mostrato nell’immagine seguente.
- In Power BI, caricare l’elenco delle tabelle disponibili.
- La nuova definizione della vista non appare?
- In tal caso, verificare in Bridge Online Monitoring se manca il segno di spunta accanto a “From Cache” per la richiesta di caricamento. Se “From Cache” risulta selezionato, significa che il reset della cache non è riuscito. Provare a reimpostare nuovamente la cache.
Viste del driver
Le viste di database integrano le viste fisse (fornite) per uno specifico driver. Queste viste del driver possono combinare solo dati provenienti da tabelle appartenenti allo specifico driver. Le viste del driver semplificano l’utilizzo della piattaforma per cui il driver è destinato, ma non possono essere modificate.
Le differenze tra le viste di database e le viste del driver sono riportate nella tabella seguente:
| Vista database | Vista driver | |
|---|---|---|
| Disponibile su ogni sito | No | Sì |
| Personalizzabile dall’utente | Sì | No |
| Combina dati tra driver diversi | Sì | No |