handleiding-power-pivot-met-cbs-open-data-v2.pdf

CBS |Excel en open data services van StatLine 13

Nu wordt de query Perioden toegevoegd aan de query TypedDataSet. Het is de laatste kolom, die de naam Perioden_1 gekregen heeft. In deze kolom zijn alle gegevens van de query Perioden samengevouwen. Door te klikken op het icoon met de pijltjes kun je kiezen welke kolommen uit de query Perioden je over wil nemen.

Klikken op het icoon met de pijltjes geeft een keuzemenu voor de kolommen uit de tabel Perioden. In dit geval willen we de query uitvouwen en alleen de kolom Title overhouden, die het label van de perioden bevat. Bij de andere kolommen is het vinkje daarom uitgezet. Klik vervolgens op OK.

CBS |Excel en open data services van StatLine 14

De kolom ‘Perioden.1.Title’ is nu toegevoegd aan de query TypedDataSet. Deze bevat de labels van de perioden. In de kolom rechts is deze stap genoteerd onder Toegepaste stappen.

Hierna kun je eventueel nog andere aanpassingen aan deze query uitvoeren, zoals de nieuwe kolom een andere naam geven of de kolom opschuiven naar links. Ook kun je op dezelfde manier de labels van Geslacht en Leeftijd aan de query toevoegen.

CBS |Excel en open data services van StatLine 15

2.6 Opslaan van query’s

Wanneer je klaar bent met de query editor kun je de gegevens opslaan in de werkbladen van je Excel file. Kies voor de optie ‘Sluiten en laden’ om dit te doen. De query editor wordt dan afgesloten en je keert terug naar de werkmap waarin je begonnen bent.

De optie ‘Sluiten en laden naar’ is handig wanneer je gegevens uit meerdere StatLine tabellen wil combineren. In het vervolgmenu ‘Laden naar’ kies je dan voor ‘Alleen verbinding maken’. De gegevens worden dan nog niet opgeslagen in je werkmap.

De gegevens uit de query’s worden nu ieder als een apart werkblad in de Excel map opgenomen. In dit voorbeeld zijn er vijf query’s, die in de werkbladen Blad 4 tot en met Blad 8 neergezet worden. Ze worden Werkmapquery’s genoemd. Blad 8 bevat de TypedDataSet.

CBS |Excel en open data services van StatLine 16

2.7 Tabellen en grafieken maken

Deze gegevens uit de query’s zijn nu te gebruiken om draaitabellen en -grafieken te maken. Kies daarvoor in het menu ‘Invoegen’ en vervolgens ‘Draaitabel’ of ‘Draaigrafiek’. Er verschijnt dan een scherm ‘Draaitabel maken’ waarin een paar opties aangeboden worden voor het ontwerp van de draaitabel of -grafiek. In dit voorbeeld gebruiken we een draaitabel.

Wanneer je het invoegen van een draaitabel opstart vanuit het werkblad met de TypedDataSet wordt deze automatisch aangeboden als bron van de gegevens voor de draaitabel. De standaard voor een draaitabel is om deze in een nieuw werkblad neer te zetten.

Klik daarna op OK. Vervolgens wordt het werkblad voor de draaitabel toegevoegd aan de werkmap.

CBS |Excel en open data services van StatLine 17

Het nieuwe werkblad toont links de dummy van de draaitabel. In de middelste kolom staan de werkmapquery’s die beschikbaar zijn voor de draaitabel. In dit geval is de TypedDataSet actief, omdat we die willen gebruiken voor een tabel.

In de kolom rechts staan de velden die beschikbaar zijn voor deze draaitabel. Deze kunnen gesleept worden naar de vier gebieden van de draaitabel: filters, kolommen, rijen en waarden.

Hoe je draaitabellen in Excel ontwerpt en aanpast valt buiten het kader van deze handleiding. Meer informatie en uitleg over draaitabellen is op het internet te vinden, bijvoorbeeld op de website van Microsoft en bij YouTube.

CBS |Excel en open data services van StatLine 18

2.8 Gegevens updaten

Tabellen en grafieken op basis van odata feeds uit StatLine kunnen snel van een update voorzien worden. In sommige tabellen worden iedere maand of kwartaal nieuwe gegevens toegevoegd.

In het werkblad van een draaitabel of –grafiek kun je hiervoor de menu-optie ‘Vernieuwen’ gebruiken. Door vervolgens te kiezen voor ‘Alles vernieuwen’ worden de query’s opnieuw uitgevoerd. Excel gaat dan de meest recente gegevens ophalen.

Aan de werkmapquery’s kun je zien dat de query’s opnieuw geladen worden. Het draaiende cirkeltje geeft aan dat de gegevens ingelezen worden. Wanneer het laden gereed is verandert het cirkeltje weer in het tabel-icoon.

Deze manier van de query’s verversen gaat uit van de menu-opties voor een draaitabel of –grafiek. Het is echter altijd mogelijk om in Excel de gegevens die opgehaald zijn via een query opnieuw in te lezen. Kies hiervoor de menu- optie ‘Gegevens’ en klik op ‘Alles vernieuwen’. Daarmee worden de query’s opnieuw gestart en de data vernieuwd.

CBS |Excel en open data services van StatLine 19

2.9 Query’s later veranderen

Eenmaal gemaakte query’s kunnen later nog bijgewerkt of veranderd worden. Ga daarvoor naar het werkblad met de tabel van de query, in dit geval de TypedDataSet. Kies vervolgens in het menu voor de optie ‘Query’. Je krijgt in het menu daaronder links een optie ‘Bewerken’.

Wanneer je op ‘Bewerken’ klikt, wordt de query editor gestart. Je kunt dan de query en de gegevens opnieuw bewerken.

CBS |Excel en open data services van StatLine 20

2.10 Inlezen van ontbrekende DataProperties

In deze paragraaf wordt beschreven hoe je de eenheid en andere ontbrekende DataProperties kunt toevoegen. Daarvoor is een iets andere manier van gegevens inlezen nodig.

Ga naar een leeg werkblad. Klik op Gegevens en kies voor ‘Van andere bronnen’. Uit de aangeboden opties neem je ‘Van Odata gegevensfeed’.

Er wordt gevraagd om een gegevensfeed. Hier wordt weer dezelfde feed opgegeven als eerder, https://opendata.cbs.nl/ODataFeed/odata/82914NED.

CBS |Excel en open data services van StatLine 21

Vervolgens selecteer je alleen de DataProperties. Deze worden nu opnieuw opgehaald. In deze feed komen alle kolommen van de DataProperties mee. Klik nu op Voltooien.

De DataProperties worden nu ingelezen in het werkblad, inclusief de kolommen die eerder nog ontbraken:  Release policy  Data Type  Unit  Decimals  Default

Door in de kolom Type te selecteren op type ‘Topic’, krijg je een lijst van de onderwerpen en hun bijbehorende eenheid. De unit (of eenheid) is essentiële informatie om te zien wat de waarden in de dataset betekenen. Zie paragraaf 3.5 voor een uitgebreidere beschrijving van de DataProperties.

De query editor neemt deze kolommen niet mee, omdat de odata 3 services niet voldoen aan de eisen van de query editor en komen niet mee. Het CBS werkt aan een volgende versie van de open data services (odata 4), waarbij het mogelijk is om automatisch een koppeling te leggen tussen de onderwerpen en de eenheden.

CBS |Excel en open data services van StatLine 22

Excel PowerPivot voor StatLine tabellen 3.1 Wat is PowerPivot?

PowerPivot is een (gratis) uitbreiding op Excel waarmee

• grote hoeveelheden gegevens
• uit praktisch alle denkbare bronnen
• snel en eenvoudig gecombineerd, geanalyseerd, gevisualiseerd en gedeeld kunnen worden.

PowerPivot is beschikbaar vanaf Excel 2010. Binnen dit document wordt uitgegaan van Excel 2010 met Microsoft SQL Server 2012 PowerPivot for Microsoft Excel.

Met slechts een klein beetje meer kennis dan voor normaal Excel, kunnen hier professionele (management) dashboards mee worden gemaakt. Iedere gebruiker die Excel op redelijk niveau beheerst, heeft nu deze extra rekenkracht ter beschikking heeft. Dit wordt selfservice BI genoemd. PowerPivot is een standaard onderdeel van de licentie voor Excel 2010, en kan gratis gedownload worden. PowerPivot heeft een eigen set aan functies. Dit noemen ze Data Analysing Expression (DAX) functies.

3.2 Hoe een StatLinetabel te openen in PowerPivot

PowerPivot is een in Excel is geïntegreerd applicatie. Door op de knop PowerPivot te klikken in Excel wordt het PowerPivot venster geopend. Gebruik de knop: ‘Uit een gegevensfeed’ om een verbinding te leggen met de CBS open data API-service.

De afbeelding hierboven toont de knop waarmee het PowerPivot venster wordt geopend.

De afbeelding boven toont de locatie van de knop: Uit een gegevensfeed.

CBS |Excel en open data services van StatLine 23

3.3 Opzetten van de verbinding

PowerPivot opent een Wizard waarmee de verbinding met de RSS Feed kan worden geconfigureerd. Als URL voor de gegevensfeed wordt de URL overgenomen van de bulk service zoals deze wordt getoond in de CBS Open Data Table Catalog. De bulk service heeft XML als standaard formaat waardoor de data direct geschikt is voor RSS readers zoals PowerPivot. Een URL uit de Catalog is: http://opendata.cbs.nl/ODataFeed/OData/81885NED

Na invoer kan de verbinding getest worden en kan de volgende stap in de wizard worden gevolgd. PowerPivot laat de aanwezige feeds zien die voor de geselecteerde tabel zijn gevonden. Door deze te selecteren gaat PowerPivot ze allen overbrengen:

CBS |Excel en open data services van StatLine 24

In deze selectie is de UntypedDataSet niet meegenomen. Dit is dezelfde dataset als de TypedDataSet, maar dan inclusief de speciale tekens die het werken met de cijfers niet goed mogelijk maakt. Voor het verschil tussen Untyped- en TypedDataSet zie “Bijlage 2: Verschil tussen Typed en Untyped Dataset”

Als PowerPivot klaar is met een brontabel verschijnt het aantal overgebrachte rijen.

CBS |Excel en open data services van StatLine 25

Per feed wordt 1 aparte sheet aangemaakt in het PowerPivot venster zoals hieronder.

Een tabel bevat meerdere feeds (werkitems in PowerPivot) en PowerPivot maakt voor ieder werkitem een sheet aan.

Iedere tabel heeft een aantal vaste feeds en een aantal specifieke feeds die alleen voor die tabel gelden. De vaste feeds zijn:  DataProperties; een beschrijving van de onderwerp –en dimensie-eigenschappen.  TableInfos; een beschrijving van de de tabel, de feed bevat maar 1 rij. Voor de details zie hieronder.  TypedDataSet; de voor grafieken aangepaste meetwaarden.
 UntypedDataSet; bevat de originele meetwaarden.

Het verschil tussen TypedDataSet en UntypedDataSet wordt in bijlage 2 nader uitgelegd.
De specifieke feeds bevatten de Dimensies. In dit voorbeeld zijn dat Perioden en Regios maar dat is per tabel verschillend.

3.4 TableInfos

TableInfos bevat de volgende kolommen waarmee de tabeleigenschappen beschreven worden. De korte omschrijving van de properties van TableInfos:

Property Omschrijving ID Uniek nummer Title Titel van de tabel ShortTitle Korte titel van de tabel Identifier Tabel identificatie Summary Samenvatting van de tabelbeschrijving Modified Datum van wijziging van de tabel ReasonDelivery De reden van wijziging Language De taal waarin wordt opgeleverd. Onderscheiden wordt ‘nl’ voor Nederlandstalige tabellen en ‘en’ voor Engelstalige tabellen. Frequency De frequentie waarmee nieuwe versies van de tabel worden gepubliceerd. Period De periode waarop de meetwaarden betrekking hebben ShortDescription Korte omschrijving van de tabel Description De uitgebreide omschrijving van de tabel DefaultPresentation De presentatievorm waarmee in StatLine wordt geopend DefaultSelection De selectie waarmee in StatLine wordt geopend GraphTypes De presentatievormen die het CBS geschikt acht om in StatLine te tonen OutputStatus Status van de tabel MetaDataModified Datum waarop de metadata is aangepast

CBS |Excel en open data services van StatLine 26

3.5 DataProperties

Deze feed bevat de volgende kolommen met eigenschappen van de onderwerpen, onderwerpgroepen en dimensies.

We onderscheiden de volgende properties:

Property Omschrijving ID Uniek ID van de data property Position Positie van de data property. De positie is het niveau in de hiërarchie. TopicGroups kennen geen positie (leeg), Topics en Dimensions hebben een positie met een oplopende nummering. ParentID Alleen gevuld bij een Topic optioneel voor een TopicGroup. Het ID van de bovenliggende TopicGroup Type Het Type Dit kan zijn:  Dimension, niet gestandaardiseerd, voorbeeld Geslacht of Leeftijd  TimeDimension, gestandaardiseerd, voorbeeld: Perioden  GeoDimension, gestandaardiseerd, voorbeeld: RegioS  GeoDetail, gestandaardiseerd, voorbeeld: RegioS  TopicGroup, onderwerpengroep, voorbeeld: Bevolking  Topic, onderwerp, voorbeeld: Vrouwen of Mannen Key Sleutel t.b.v. dimensies en onderwerpen. TopicGroup heeft geen Key Title Titel van alle onderwerpen, onderwerpgroepen en dimensies, welke in de tabel getoond worden, voorbeeld: Gemeentenaam Description Optioneel, de omschrijving van het onderwerp of onderwerpgroep, voorbeeld: Aantal in het verslagjaar geregistreerde meerderjarige verdachten. MapYear Alleen bij GeoDimension en GeoDetail t.b.v. het tonen van een kaart. Wanneer geen TimeDimension is waaruit een jaar kan worden afgeleid voor de benodigde kaart, moet een jaartal in MapYear zijn opgenomen ReleasePolicy Alleen bij TimeDimension, kan alleen Waar of Onwaar zijn.
Datatype Datatype van het onderwerp. Dit kan zijn:  Tekst (String)  Numeriek (Double, Long, Integer)
Unit Eenheid van het onderwerp, voorbeeld: aantal, %, per 1000 inwoners