Klant Query Server

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Inleiding

Op verzoek stelt ValueCare aan de instellingen een Query Server beschikbaar met daarop een Postgres database met alle source data en BI resultaat data. De door de instelling aangemerkte gebruikers kunnen een connectie maken met de database en er queries op los laten. Ook is het mogelijk om een tool, zoals bijvoorbeeld PowerBI, te connecten tot de database.

Implementatie

Het volgende is nodig vanuit de instelling:

Een server en het IP adres van de server, tenzij de server wordt gehost door ValueCare.
Een VPN verbinding van het klantnetwerk naar het ValueCare netwerk.
Een lijst met gebruikers.

De rest van de implementatie wordt door ValueCare verzorgd.

Hoe werkt het?

In het kort wordt het volgende dagelijks gedaan:

Er wordt een kopie van de source data en BI resultaat data op de ValueCare Productie server gemaakt.
Op de Query Server zijn er 2 database schema's: vcs en public. Het vcs schema wordt leeg gemaakt.
De kopie van de source data en BI resultaat data wordt in het vcs schema geplaatst.

De gebruiker kan vervolgens data in het vcs schema inzien, maar niet wijzigen. In het public schema kan de gebruiker tabellen aanmaken, wijzigen en verwijderen.

Toelichting data

De data

Er zijn twee schema's beschikbaar op de Query Server: public en vcs. In het vcs schema worden alle bi- en bron-data neergezet, zodat de ValueCare data beschikbaar is. Deze data worden elke dag ververst, zodat het elke dag overeen komt met de data op de Productie-omgeving. Als u op schema vcs een eigen tabel zou aanmaken, wordt deze verwijderd tijdens de verversing. Om met eigen tabellen te werken, is het public-schema beschikbaar. De tabellen die hierop staan, blijven beschikbaar op de Query Server. Hoe u data overzet van schema vcs naar schema public leest u hieronder.

Tabellen kopiëren van schema VCS naar schema Public

Er zijn verschillende manieren om een tabel van schema vcs over te zetten naar schema public. Hieronder is de query uitgeschreven voor één van die manieren. Als je dit vaak wil gebruiken, is het handig om hier een template van te maken (specifiek voor DBeaver). Hier vind je meer over in “Templates”.

CREATE TABLE public.table_name (LIKE vcs.table_name INCLUDING ALL);

INSERT INTO public.table_name

SELECT *

FROM vcs.table_name;

Van een dashboard of tegel naar de juiste tabel op de Query Server

Om te begrijpen welke tabellen op de Query Server gebruikt zijn om de tegels en dashboards in het portaal op te bouwen, is de tabel bi_queryserver_tegels_en_dashboards beschikbaar op de Query Server. Hierin kan je zien welke tabellen worden gebruikt in een tegel en/of dashboard. In onderstaand voorbeeld, ziet u dat voor de tegel ‘Waarde verblijfsdagen per maand’, die te vinden is in dataset ‘Verblijf ZPM’, de onderliggende tabel ‘bi_zis_verblijf_zpm’ is. Tabel bi_zis_verblijf_zpm is dan te vinden op de Query Server.

tegel_naam	dataset_naam	tabel_naam	tegel_beschrijving
Waarde verblijfsdagen per maand	Verblijf ZPM	bi_zis_verblijf_zpm	Deze rapportage toont waarde van de verblijfsdagen voor …

Daarnaast kunt u vanuit het portaal ook het filter 'Paginanaam' gebruiken. Als je deze toevoegt vanuit [+ nieuw filter] onderaan de linker balk met filters, dan ziet u ook welke tabellen er gebruikt worden voor het opstellen van de tegel en/of dashboard. Zoekt u bijvoorbeeld van een dashboard de bijbehorende dataset, dan kunt u ook het filter 'Paginanaam' gebruiken, om deze vervolgens als dataset_naam op de Query Server terug te vinden.

De logica achter de benaming van tabellen op de Query Server is hieronder weergegeven.

Voorvoegsel	Betekenis	Uitleg
src_	Source (bron)	Alle tabellen die wij direct vanuit een bron ophalen krijgen het voorvoegsel src_. Dit zijn dus tabellen vanuit bronnen, zoals XMCare, MijnQuarant, Beaufort, QuestManager, etc. Ook bestanden die we ophalen vanaf een SFTP server of andere bronnen, krijgen dit voorvoegsel.
bi_	Business intelligence	Alle tabellen die gebruikt worden voor het samenstellen van dashboards en tegels hebben het voorvoegsel bi.
bi_personeel	Business intelligence personeel	Deze data komen vanuit het personeelssysteem. Voor meer informatie, zie Stuurinformatie op Financiën.
bi_financieel	Business intelligence financieel	Deze data komen vanuit het financiële systeem. Voor meer informatie, zie Stuurinformatie op Personeel.
bi_zis	Business intelligence ziekenhuis informatie systeem	Deze data komen vanuit het zis (Ziekenhuis Informatie Systeem) ofwel het EPD (elektronisch patiëntendossier).

Connectie opzetten

Vraag om de hostname en de databasename bij ValueCare
- Het kan ook zijn dat de Query Server lokaal gehost wordt, in dat geval is de hostname doorgaans te achterhalen bij uw ICT-afdeling
Vraag om de username en password bij uw ValueCare contactpersoon.
Maak de connectie vanuit de tool die u gebruikt, door de hostname en de databasename in te vullen en in te loggen met uw username en password.
- De Query Server beschikt over een postgres database, dus het poortnummer is de standaard postgres poort (5432)

Werken met DBeaver en de Query Server

Er zijn tal van goede programma's die u kunt gebruiken om te querieën op de Query Server. Mits u gebruik maakt of wilt maken van DBeaver om op de Query Server te querieën, dan vindt u hieronder een aantal instructies en een aantal tips & tricks die het gebruik van DBeaver nog fijner maken.

Installeren DBeaver

Via https://dbeaver.io/download/ kunt u DBeaver downloaden.
Met de standaard instellingen van de installatie kunt u prima op de Query Server komen, maar voelt u zich vooral vrij om de instellingen naar wens aan te passen.

Connectie opzetten met de database

Klik linksboven op het stekkertje om een nieuwe connectie op te zetten. (Dit kan eventueel ook via het menu: Database > New Database Connection
Selecteer PostgreSQL
Vul de juiste naam voor de Host in (het poortnummer staat default al goed: 5432)
Vul de juiste naam voor de Database in
Vul de juiste Username in (deze heb je ontvangen vanuit ValueCare. Heb je deze nog niet, dan kan je die aanvragen via jouw contactpersoon)
Ga naar het kopje “Driver properties” en zet hier het veld tcpKeepAlive op ‘True’
Klik op Finish
Nu verschijnt er links in de “Database Navigator” een connectie met de naam van de database. Dubbelklik hierop om de connectie te openen
Vul je wachtwoord in en voilà!

Tips & Tricks om snel met DBeaver te werken

Overzicht van de functietoetsen

Uitvoeren van een query: ctrl + enter

Openen van new script: F3

Geavanceerd kopiëren: ctrl + shift + c (uitgebreidere uitleg onder “Geavanceerd kopiëren”)

Templates

Templates zijn erg handig in DBeaver. Hiermee kunt u veelgebruikte queries opslaan en hoeft u die niet volledig te typen, als u ze de volgende keer wil gebruiken. Bijvoorbeeld: In plaats van ‘SELECT * FROM table_name;’, type je enkel ‘saf’ en druk je op tab.

Om een template aan te maken gaat u naar: Window > Preferences > SQL Editor > Templates > New.

Name: de combinatie aan woorden of letters die u wil typen in plaats van de query

Pattern: de query die moet verschijnen als u de Name typt en op Tab drukt.

Insert variable: Hiermee kunt u een bepaalde variabele aanroepen nadat u het template gebruikt. Bijvoorbeeld, SELECT * FROM ${table}; zorgt ervoor dat u makkelijk een bestaande tabel kunt selecteren als u het template gebruikt.

Geavanceerd kopiëren

Met ctrl + shift + c kunt u geavanceerd kopiëren. Dit is handig als u de geselecteerde rijen wil gebruiken, bijvoorbeeld in een volgende query. Er staan dan automatisch quotes omheen en een komma tussen elk item, zoals hieronder weergegeven.

'item1',

'item2',

...

Exporteren naar Excel (xlsx)

U kunt een extensie installeren waarmee u direct Excels kunt downloaden. Dus niet eerst csv-bestanden die nog met de hand geconverteerd moeten worden. Volg de stappen in de link 'extensie installeren'.

LET OP!: Sluit DBeaver eerst af en start het op als Administrator. Bijv. door Ctrl + Shift + Rechtermuisknop op de taakbalk en dan 'Als administrator uitvoeren'.

CSV-bestanden als database benaderen

Klik op het plusje met New Database Connection, zoek naar 'csv'.

Onder Path kiest u een map waarin u alle .csv-bestanden neer gaat zetten. Klik op Finish.

Alle csv's die u in de map (die je hierboven aangemaakt hebt) plaatst, zijn dan beschikbaar om op te queryen.

In de Driver Properties kunt u onder separator instellen wat het scheidingsteken is.

Regels afsterren:

Regels zijn op twee manieren als tekst neer te zetten.

-- door middel van twee streepjes voor

/*

Of door middel van /* en */

*/

Als u al tekst heeft geschreven kunt u gebruiken maken van ctrl + / om automatisch streepjes voor de tekst te laten zetten. Selecteer de betreffende regels en druk op CTRL + /

Databasenaam in export

Een export doet u door in een Resultset met de Rechtermuisknop op 'Export data ...' te klikken. Als u wil dat de file-name automatisch de databasenaam, tabelnaam, en/of een timestamp bevat, kunt u dat doen door het volgende te doen.

Bij Export data ... > Output (stap 4) > File name pattern, kunt u de naam van de te exporteren file bepalen. Als u daarin gebruik maakt van onderstaande variabelen, krijgt de export automatisch de naam van de betreffende variabele.

De databasenaam: ${datasource}

De tabelnaam: ${table}

De timestamp: ${timestamp}

Een combinatie hiervan is ook mogelijk. Bijvoorbeeld ${datasource}_${table}_${timestamp}.

Naar hoofdletters/kleine letters van selectie

Ctrl + Shift + X -> selectie naar hoofdletters

Ctrl + Shift + Y -> selectie naar kleine letters

Werken met PowerBI en de Query Server

Mocht u met PowerBI de data op de Query Server willen gebruiken, dan dient u de volgende stappen te volgen:

Installeer PowerBI
Open PowerBI
Klik op "Gegevens ophalen" en "Meer" in het lint of "Gegevens ophalen" in het startscherm.
Kies vervolgens in het menu "Gegevens ophalen" de optie "PostgreSQL-database" en klik op "Verbinding maken".

Voer vervolgens in het menu "PostgreSQL-database" waarden in voor "Server" en "Database".
- Uw ValueCare contactpersoon kan u helpen met de in te vullen waarden.

Voer in het volgende menu uw login en wachtwoord in.
- Uw ValueCare contactpersoon heeft u op een veilige manier voorzien van deze gegevens.
- Dit hoeft u maar 1 keer te doen voor een connectie. De volgende keer wordt uw login en wachtwoord niet meer gevraagd.
Kies vervolgens in het menu "Navigator" de gewenste tabel uit de lijst. Kies vervolgens op "Laden" als u de hele tabel wilt downloaden of kies "Gegevens transformeren" om data te limiteren en te bewerken.
U kunt nu aan de slag.

NB:

Via de knop "Recente bronnen" kunt u de verbinding met de Query Server selecteren en dan snel een extra tabel met data toevoegen

Werken met Excel en de Query Server

Mocht u met Excel de data op de Query Server willen gebruiken, dan dient u de onderstaande stappen te volgen. Het is noodzakelijk dat u een programma installeert op uw computer, namelijk een ODBC driver. Als u geen programma's op uw eigen computer kunt installeren, hebt u hulp nodig van uw ICT service desk.

Instructies:

Download Postgres ODBC driver via https://www.postgresql.org/ftp/odbc/versions/msi/
- Neem de laatste versie! Neem de 64 bit versie!
Installeer de ODBC driver op uw computer
- Start het programma "psqlodbc_x64" in de msi file.
- Klik op Next, vink aan "I accept the terms in the License Agreement", klik Next, klik Next, klik Install
- Klik Ja op vraag of programma wijzigingen aan laptop/PC mag aanbrengen
- Klik Finish
Configureer ODBC koppeling op uw computer
- Zoek via de Windows knop de app "ODBC-gegevensbronbeheer (64-bits)"
- Open deze app
- Klik op Toevoegen
- Klik op "PostgreSQL Unicode(x64)" en op "Voltooien"
- Vul de database gegevens en login gegevens in
  - Uw ValueCare contactpersoon kan u helpen met de in te vullen waarden.
  - Geef bij Data Source een begrijpelijke naam aan (laat de default naam niet staan). Bijvoorbeeld: Klant Qserver X
  - Klik op Test om te checken of de connectie werkt.

Open Excel
Zet de Excel connectie op
- Klik op Gegevens, Gegevens ophalen, Uit andere bronnen, Uit ODBC
- Klik in het scherm op de Data Source zoals net aangemaakt (bijvoorbeeld Bijvoorbeeld: Klant Qserver X) en klik op OK
- Vul in het volgende scherm wederom de gebruikersgegevens in en klik op OK
- Kies vervolgens in het scherm Navigator de gewenste tabellen en laadt deze in Excel
U kunt nu aan de slag.