CASUS eenvoudige CBS datamart
De CASUS is het ophalen van opendata uit het CBS register om vervolgens daarop enkele ETL's te definieeren voor de dimensies en vervolgens een FACT-ETL maken met lookup's naar deze dimensies.
Inleiding:
Welk proces gaan we volgen, welke stappen en deze hieronder markeren.
STAP 1 : CBS opendata
CBS is de bron van veel opendata. GRIP heeft het speciale commando CBS_OPENDATA ontwikkeld voor het ophalen de inhoudsopgave van wat CBS aan data te bieden heeft. Deze inhoudsopgave wordt in de gelijkgenaamde tabel 'CBS_OPENDATA' opgeslagen welke eenvoudige met een sql-query uit te vragen is. Iedere CBS-opendata-set kan opgehaald worden via zijn IDENTIFIER. In onderstaande figuur zie je de data-sets van query
**NB: de sql-syntax die hieronder weergegeven is, is voor een sqlserverdatabase.
Voer het onderstaand command uit:
CBS_OPENDATA
Wanneer het commando klaar is, kan met onderstaande de inhoudsopgave van wat CBS aan opendata geschikbaar heeft, weergegeven worden:
select top 100 Identifier, title, updated from xxx.CBS_OPENDATA where lower(title) like '%ziek%'
Wanneer je geinteresseerd bent in de dataset 'Ziekenhuisopnamen ... ' met identifier 84067NED, dan kan deze dataset geladen worden met de GRIP-call 'CBS_OPENDATA_READ 84067NED'. Er wordt een tabel CBS_OPENDATA_84067NED gecreeerd waar deze dataset in opgeslagen wordt.
Voer het onderstaande commando uit:
CBS_OPENDATA_READ 84466NED
Wanneer dit commando is uitgevoerd, is er een tabel gecreeerd op basis van deze data, met als tabelnaam CBS_OPENDATA_84067NED. Deze tabel kan als bron dienen voor het datawarehouse.
select top 100 * from xxx.CBS_OPENDATA_84466NED ( sqlserver )
select * from xxx.CBS_OPENDATA_84466NED limit 100 ( postgresql )
- xxx. staat voor currurrent-schema
STAP 2 : de bron
Voor onze demo gaan we opendata ophalen van CBS met als identifier : 84466NED.. Het betreft data over "Zelfstandigen; inkomen, vermogen, kenmerken Zelfstandigen;"
select * from xxx.CBS_OPENDATA where identifier = '84466NED'
Busy...
[SS_boss,grip_parse,INFO] tonen resultaat van query: select * from xxx.CBS_OPENDATA where identifier = '84466NED'
-------------------------------------------------------------------- dataframe from SS_boss
select * from grip.CBS_OPENDATA where identifier = '84466NED'
( ** rownorow : voor aan en uitzetten van regelnummers , clipboard : voor data via het clipboard beschikbaar stellen )
---------------------------------------------------------------------
UPDATED ID IDENTIFIER TITLE SHORTTITLE SHORTDESCRIPTION SUMMARY MODIFIED METADATAMODIFIED REASONDELIVERY EXPLANATORYTEXT OUTPUTSTATUS SOURCE LANGUAGE CATALOG FREQUENCY PERIOD SUMMARYANDLINKS APIURL FEEDURL DEFAULTPRESENTATION DEFAULTSELECTION GRAPHTYPES RECORDCOUNT COLUMNCOUNT SEARCHPRIORITY
0 2020-11-03T02:00:00 741 84466NED Zelfstandigen; inkomen, vermogen, kenmerken Zelfstandigen; welvaart \nDeze tabel bevat gegevens over het inkomen en vermogen van zelfstandigen. Er wordt onderscheid gemaakt tussen enerzijds de personen voor wie het voornaamste inkomen wordt gegenereerd uit werkzaamheden als zelfstandige, en anderzijds alle personen met inkomen uit werkzaamheden als zelfstandige. De cijfers in deze tabel zijn uitgesplitst naar type zelfstandige, economische activiteit, geslacht, leeftijd, migratieachtergrond, positie in het huishouden, en naar inkomens- en vermogensgroepen.\n\nAlle statistieken in deze tabel zijn op persoonsniveau, ook de vermogens; (bedrijfs)vermogens zijn bepaald per huishouden, en worden toegekend aan alle personen in het huishouden als een maat voor de welvaart. Peildatum van doelpopulatie en vermogen is 1 januari van het verslagjaar. Voor de oudere jaargangen 2007 t/m 2010 is de peildatum van vermogen 1 januari van het jaar volgend op het verslagjaar.\n\nVoor het bepalen van de SBI van zelfstandigen wordt gebruik gemaakt van het Algemeen Bedrijvenregister (ABR). Het ABR heeft in de periode 2007-2011 met diverse trendbreuken te maken gehad. Dit leidt in deze tabel tot een forse daling van het aantal zelfstandigen in de financiële dienstverlening (K) in 2010. Bij het vergelijken van de cijfers naar SBI is daarom voorzichtigheid geboden.\n\nGegevens beschikbaar vanaf: 2007. \n\nStatus van de cijfers: \nDe cijfers in deze tabel zijn voorlopig. \n\nWijzigingen per 3 november 2020:\nDe cijfers over 2018 zijn bijgewerkt, en de cijfers over 2019 zijn toegevoegd.\nAlle jaargangen zijn nog voorlopig, omdat er wordt gewerkt aan een verbetering van het aanmerkelijk belang. \n\nWanneer komen er nieuwe cijfers?\nDe nieuwe cijfers over 2020 komen in december 2021 beschikbaar.\nDe gereviseerde cijfers over de periode 2006 t/m 2019 komen in het eerste kwartaal van 2021 beschikbaar.\n Inkomen en vermogen van zelfstandigen\nType zelfstandige, bedrijfstak, kenmerken van zelfstandigen 2020-11-03T02:00:00 2020-11-03T02:00:00 Actualisering Regulier CBS. nl CBS Perjaar 2007 t/m 2019 Inkomen en vermogen van zelfstandigen<br />Type zelfstandige, bedrijfstak, kenmerken van zelfstandigen<br /><a href="http://opendata.cbs.nl/ODataApi/OData/84466NED">http://opendata.cbs.nl/ODataApi/OData/84466NED</a><br /><a href="http://opendata.cbs.nl/ODataFeed/OData/84466NED">http://opendata.cbs.nl/ODataFeed/OData/84466NED</a> https://opendata.cbs.nl/ODataApi/OData/84466NED https://opendata.cbs.nl/ODataFeed/OData/84466NED ts=1603282320817&graphtype=Table&r=TypeZelfstandige&k=Topics&t=Geslacht,BedrijfstakkenBranchesSBI2008,Kenmerken,Perioden $filter=((TypeZelfstandige eq '2021380') or (TypeZelfstandige eq '2021430') or (TypeZelfstandige eq '2021420') or (TypeZelfstandige eq '2021400') or (TypeZelfstandige eq '2021390') or (TypeZelfstandige eq '2021393') or (TypeZelfstandige eq '2021410')) and ((Geslacht eq 'T001038')) and ((BedrijfstakkenBranchesSBI2008 eq 'T001081')) and ((Kenmerken eq 'T009002')) and ((Perioden eq '2019JJ00'))&$select=TypeZelfstandige, Geslacht, BedrijfstakkenBranchesSBI2008, Kenmerken, Perioden, Zelfstandigen_1, MediaanInkomenAlsZelfstandige_5, MediaanGestandaardiseerdInkomen_7, MediaanVermogen_9, Zelfstandigen_11, MediaanInkomenAlsZelfstandige_15, MediaanGestandaardiseerdInkomen_17, MediaanVermogen_19 Table,Bar,Line 597246 25 2
--------------------------------------------------------------------
Ready ......(2.7)...........
Inlezen van de dataset kan met onderstaand commando:
CBS_OPENDATA_READ 84466NED
na bovenstaande CALL is een tabel gecreerd, genaamd CBS_OPENDATA_84466NED
de identifier van de dataset wordt de prefix van de tabel
select top 100 * from xxx.CBS_OPENDATA_84466NED
Deze dataset gaan we gebruiken als bron voor een datamart met dimensies en een fact-tabel.
In het navolgende laten we zien hoe eenvoudig we een datamart van deze bron maken, compleet met FLOW.
STAP 3 : de dimensies
Doel: Enkele DIMENSIE-VIEWS maken voor het stermodel
- de xxx. staat voor de current-login-schema zoals in de connectie-file grip_con.dat is opgegeven.
Deze xxx. behoeft niet in de ETL-call gebruikt te worden, wel in de view.
- SOURCE : de naam van de bron-view of bron-tabel ( zonder xxx. )
- TARGET : de naam van de target-tabel. Indien de target tabel niet bestaat, wordt hij gecreeerd
op basis van de structuur van de SOURCE
- MERGE_KEYS : de unique key van de SOURCE/TABLE. Indien een van de attributen een null-value bevat
of wanneer de keys duplicaten kent, genereert GRIP een error en stagneert de ETL / FLOW.
We gebruiken hier de actualize_t2 functie waarmee het databeeld van de source-view DIM_TYPEZELFSTANDIGE_V type-2 geactualiseerd wordt. Dat betekent dat deze dimensie met historie wordt opgebouwd. Middels de attributen date_created en date_deleted, kan een selectie op pijldatum, terug in de tijd, genomen worden: het zgn. tijdreizen.
Selecteer 1 of meerdere regels en click op {execute}-button .. De geselecteerde regels worden uitgevoerd. De meeste commando's staan op 1 regel en middels {ctrl-enter} worden de regels, waar de cursor op knippert, uitgevoerd.
drop view xxx.DIM_TYPEZELFSTANDIGE_V
create view xxx.DIM_TYPEZELFSTANDIGE_V
as
select TYPEZELFSTANDIGE, sum(1) aantal
from xxx.CBS_OPENDATA_84466NED
group by TYPEZELFSTANDIGE
actualize_t2('| SOURCE DIM_TYPEZELFSTANDIGE_V | TARGET DIM_TYPEZELFSTANDIGE| MERGE_KEYS TYPEZELFSTANDIGE | ')
de inhoud van de tabel kan bekenen worden met
- select * from xxx.DIM_TYPEZELFSTANDIGE
- browse DIM_TYPEZELFSTANDIGE
drop view xxx.DIM_GESLACHT_V
create view xxx.DIM_GESLACHT_V
as
select GESLACHT , sum(1) aantal
from xxx.CBS_OPENDATA_84466NED
group by GESLACHT
actualize_t2('| SOURCE DIM_GESLACHT_V | TARGET DIM_GESLACHT | MERGE_KEYS GESLACHT | ')
drop view xxx.DIM_KENMERKEN_V
create view xxx.DIM_KENMERKEN_V
as
select KENMERKEN , sum(1) aantal
from xxx.CBS_OPENDATA_84466NED
group by KENMERKEN
actualize_t2('| SOURCE DIM_KENMERKEN_V | TARGET DIM_KENMERKEN | MERGE_KEYS KENMERKEN | ')
drop view xxx.DIM_PERIODEN_V
create view xxx.DIM_PERIODEN_V
as
select PERIODEN , sum(1) aantal
from xxx.CBS_OPENDATA_84466NED
group by PERIODEN
actualize_t2('| SOURCE DIM_PERIODEN_V | TARGET DIM_PERIODEN | MERGE_KEYS PERIODEN | ')
drop view xxx.DIM_INKOMEN_V
create view xxx.DIM_INKOMEN_V
as
select GEMIDDELDPERSOONLIJKINKOMEN_2 INKOMEN, sum(1) aantal
from xxx.CBS_OPENDATA_84466NED
where GEMIDDELDPERSOONLIJKINKOMEN_2 is not null
group by GEMIDDELDPERSOONLIJKINKOMEN_2
actualize_t2('| SOURCE DIM_INKOMEN_V | TARGET DIM_INKOMEN | MERGE_KEYS INKOMEN | ')
STAP 4 : de fact
Creatie van de FACT-view voor koppeling met de Dimensies.
desgewenst kunnen nog indexen en foreignkeys aangebracht worden ...
drop view xxx.FACT_84466NED_V
create view xxx.FACT_84466NED_V
as
select distinct
coalesce (a.id,-1) DIM_TYPEZELFSTANDIGE_id
, coalesce (b.id,-1) DIM_GESLACHT_id
, coalesce (c.id,-1) DIM_KENMERKEN_id
, coalesce (d.id,-1) DIM_PERIODEN_id
, coalesce (e.id,-1) DIM_INKOMEN_id
from ( select * from xxx.CBS_OPENDATA_84466NED ) x
left join xxx.DIM_TYPEZELFSTANDIGE a on ( a.TYPEZELFSTANDIGE = x.TYPEZELFSTANDIGE )
left join xxx.DIM_GESLACHT b on ( b.GESLACHT = x.GESLACHT )
left join xxx.DIM_KENMERKEN c on ( c.KENMERKEN = x.KENMERKEN )
left join xxx.DIM_PERIODEN d on ( d.PERIODEN = x.PERIODEN )
left join xxx.DIM_INKOMEN e on ( e.INKOMEN = x.GEMIDDELDPERSOONLIJKINKOMEN_2 )
actualize_t2('| SOURCE FACT_84466NED_V | TARGET FACT_84466NED | MERGE_KEYS DIM_TYPEZELFSTANDIGE_id,DIM_GESLACHT_id,DIM_KENMERKEN_id,DIM_PERIODEN_id,DIM_INKOMEN_id | ')
Middels onderstaande commando kan de fact-table bekeken worden. NB, indien je de view uitbreidt met extra kolommen zal de ETL-sql die GRIP genereert, fout gaan. Door SHOWRES
- browse FACT_84466NED
Extra parameters SHOWRES en SHOWCODE kunnen gebruikt worden :
bv: actualize_t2('| SOURCE DIM_INKOMEN_V | TARGET DIM_INKOMEN | SHOWRES | SHOWCODE | MERGE_KEYS INKOMEN | ')
- SHOWRES : de log-gegevens worden getoond.
- SHOWCODE : alle gegenereerde code wordt getoond.
STAP 5 : de flow
Alle losse componenten nu verzameld in de FLOW. De FLOW eenvoudig gestart worden waarmee alle ETL's achterelkaar uitgevoerd worden. De flow kan vervolgens in de scheduler opgenomen worden om vervolgens iedere dag om 5 uur uitgevoerd te worden.
record_on('| FLOW PF_84466NED |')
actualize_t2('| SOURCE DIM_TYPEZELFSTANDIGE_V | TARGET DIM_TYPEZELFSTANDIGE | MERGE_KEYS TYPEZELFSTANDIGE | ')
actualize_t2('| SOURCE DIM_GESLACHT_V | TARGET DIM_GESLACHT | MERGE_KEYS GESLACHT | ')
actualize_t2('| SOURCE DIM_KENMERKEN_V | TARGET DIM_KENMERKEN | MERGE_KEYS KENMERKEN | ')
actualize_t2('| SOURCE DIM_PERIODEN_V | TARGET DIM_PERIODEN | MERGE_KEYS PERIODEN | ')
actualize_t2('| SOURCE DIM_INKOMEN_V | TARGET DIM_INKOMEN | MERGE_KEYS INKOMEN | ')
actualize_t2('| SOURCE FACT_84466NED_V | TARGET FACT_84466NED | MERGE_KEYS DIM_TYPEZELFSTANDIGE_id,DIM_GESLACHT_id,DIM_KENMERKEN_id,DIM_PERIODEN_id,DIM_INKOMEN_id | ')
record_off()
flow_run('| FLOW PF_84466NED | ')
STAP 6 : de scheduler
Wanneer we het datawarehouse per dag willen verversen kan dat middels de scheduler geregeld worden. We kunnen als frequentie 'DAY' gebruiken maar in ons voorbeeld gebruiken we ook SECONDS, om niet te lang te hoeven wachten. Wanneer je scheduler_start ( WAIT | ITERATIE ) aanroept, zet de een oneindige lus op, met een frequentie van wait - seconds. Indien je geen ITERATIE meegeeft, dan is de lus oneindig, en anders eindigt de lus na X- ITERATIES. Middels CALL en BCALL kan de scheduler aangeroepen worden. Met BCALL wordt de scheduler parrallel opgestart.
CALL PF_SCHEDULER_84466NED --
of
BCALL PF_SCHEDULER_84466NED -- parrallel
BEGIN PF_SCHEDULER_84466NED
SET CONN SS_boss
DISP de scheduler wordt gestart, tijdstip van starten: <NOW>.
schedule_add ("| FREQ DAY | TIME 5:00 | COMMAND flow_run('| FLOW PF_84466NED | ') ")
schedule_add ("| FREQ SECONDS | VALUE 1 | COMMAND flow_run('| FLOW PF_84466NED | ') ")
schedule_start( '| WAIT 2 | ITERATIE 4 |' )
DISP de scheduler is gestopped, tijdstip van stoppen: <NOW>.
RETURN