Gebruikershandleiding Dynamisch rapporten maken met Cubes mbv Excel Groep Gent Auteur : Julien Verplanken Versie : 2.1
Gebruikershandleiding Rapporten maken met Cubes mbv Excel:
Groep Gent Projectnaam Klant-info
Nvt Groep Gent Alle Nvt Nvt Nvt Nvt Nvt 1 2.1
Klant Departement/Dienst
Klantcoördinator Projectleider Business analist Technisch analist Ontwikkelaar Iteratie Versie-beheer Datum 19/06/2012 20/06/2012 20/06/2012 02/08/2012 20/12/2012
Omschrijving Concept Controle Goedkeuring en publicatie Aanpassing: generieke versie Installatie AS OleDB driver Contactinformatie
&
Wijziging gemaakt door Julien Verplanken Gino Vertriest Gino Vertriest Julien Verplanken Julien Verplanken
Versie 0.1 0.2 1.0 2.0 2.1
2 / 29
Inhoudsopgave 1
Inleiding ............................................................................................................................................. 5 1.1 Doel ......................................................................................................................................... 5 1.2 Gebruikte afkortingen .............................................................................................................. 5 1.3 Referentie documenten ........................................................................................................... 6 2 Toegang via Excel ............................................................................................................................ 7 2.1 Versies van Excel .................................................................................................................... 7 2.1.1 Excel 2005 en ouder ............................................................................................................ 7 2.1.2 Excel 2007 ........................................................................................................................... 7 2.1.3 Excel 2010 ........................................................................................................................... 7 2.2 Installatie.................................................................................................................................. 9 2.3 Verbinden met een kubus ...................................................................................................... 10 2.4 Werken met een draaitabel ................................................................................................... 13 2.4.1 Inleiding ............................................................................................................................. 13 2.4.2 Metingen ............................................................................................................................ 14 2.4.3 Groeperen .......................................................................................................................... 14 2.4.4 Filteren ............................................................................................................................... 15 2.4.5 Sorteren ............................................................................................................................. 17 2.4.6 Vernieuwen van de data .................................................................................................... 18 2.4.7 Belangrijke opties .............................................................................................................. 18 2.4.8 Meerdere draaitabellen ...................................................................................................... 21 2.4.9 Slicers ................................................................................................................................ 22 2.4.10 Berekeningen op een meting ......................................................................................... 24 2.4.11 Bewerkbare draaitabel ................................................................................................... 26 2.4.12 Rapport statisch maken ................................................................................................. 26 2.5 Grafieken ............................................................................................................................... 28 2.5.1 Inleiding ............................................................................................................................. 28 2.5.2 Invoegen ............................................................................................................................ 28
3 / 29
Figurenlijst Figuur 1: Sparklines voorbeeld ................................................................................................................ 7 Figuur 2: Slicers voorbeeld ...................................................................................................................... 8 Figuur 3: Excel in Start Menu ................................................................................................................ 10 Figuur 4: Gegevensverbinding menu .................................................................................................... 10 Figuur 5: Server invullen ........................................................................................................................ 10 Figuur 6: Cube of Perspective selecteren ............................................................................................. 11 Figuur 7: Verbinding opslaan................................................................................................................. 11 Figuur 8: Locatie voor invoegen draaitabel ........................................................................................... 11 Figuur 9: Bestaande verbindingen menu .............................................................................................. 12 Figuur 10: Bestaande verbindingen venster.......................................................................................... 12 Figuur 11: Rapporten portaal ................................................................................................................ 12 Figuur 12: Lege draaitabel..................................................................................................................... 13 Figuur 13: Lijst met draaitabelvelden .................................................................................................... 13 Figuur 14: Hiërarchische dimensie ........................................................................................................ 15 Figuur 15: Filteropties in draaitabel ....................................................................................................... 15 Figuur 16: Labelfilter in draaitabel ......................................................................................................... 16 Figuur 17: Veld naar Rapportfilter gesleept........................................................................................... 16 Figuur 18: Rapportfilter boven draaitabel .............................................................................................. 16 Figuur 19: Sorteren in draaitabel veld ................................................................................................... 17 Figuur 20: Sorteren in draaitabel kolom ................................................................................................ 17 Figuur 21: Sorteren in ribbon ................................................................................................................. 17 Figuur 22: Sorteer menu........................................................................................................................ 17 Figuur 23: Sorteeropties venster ........................................................................................................... 18 Figuur 24: Gegevens vernieuwen ......................................................................................................... 18 Figuur 25: Subtotaal aanvink menu ....................................................................................................... 18 Figuur 26: Eigenschappen van gegevenverbinding menu .................................................................... 19 Figuur 27: Eigenschappen van de verbinding ....................................................................................... 19 Figuur 28: Opties voor draaitabel .......................................................................................................... 20 Figuur 29: Weergaveopties voor draaitabel .......................................................................................... 20 Figuur 30: Draaitabel kopiëren .............................................................................................................. 21 Figuur 31: Draaitabel plakken ............................................................................................................... 21 Figuur 32: Slicer menu .......................................................................................................................... 22 Figuur 33: Attributen voor slicers selecteren ......................................................................................... 22 Figuur 34: Slicers ingevoerd .................................................................................................................. 22 Figuur 35: Draaitabelverbindingen menu .............................................................................................. 23 Figuur 36: Draaitabelverbindingen venster ........................................................................................... 23 Figuur 37: Selectie type berekening ...................................................................................................... 25 Figuur 38: Berekeningen in draaitabel .................................................................................................. 25 Figuur 39: Optie converteren naar formules .......................................................................................... 26 Figuur 40: Draaitabel geconverteerd naar formules .............................................................................. 26 Figuur 41: Gegevensverbindingen ........................................................................................................ 26 Figuur 42: Werkmapverbinding verwijderen .......................................................................................... 27 Figuur 43: Grafiek types ........................................................................................................................ 28 Figuur 44: Subtypes Kolom grafiek ....................................................................................................... 28 Figuur 45: Resultaat Kolom grafiek ....................................................................................................... 29
4 / 29
1 Inleiding 1.1
Doel
Dit document heeft als doel een handleiding te zijn voor de gebruikers van de kubussen die werden opgezet in het kader van BI projecten gerealiseerd door Digipolis Gent. Het aanspreken van de kubussen wordt in deze handleiding gerealiseerd met behulp van Excel.
1.2
Gebruikte afkortingen
Afkoring BI DWH GIS Ribbon SSAS SSIS SSRS
Volledig Business Intelligence Data Warehouse Geographic Information System Het menu in de nieuwere Office softwarepakketten bestaat uit een aantal tabbladen, ribbons genaamd. SQL Server Analysis Services SQL Server Integration Services SQL Server Reporting Services
5 / 29
1.3
Referentie documenten
Volgende bronnen werden geraadpleegd voor het schrijven van dit document:
1.4
Aanpak BI project (versie van 2009-11-18) door Filip Koppen Documentatie structuur (versie van 2011-06-24) door Jan Aertsen What’s new in Excel 2010? http://office.microsoft.com/en-us/excel-help/what-s-new-in-excel-2010-HA010369709.aspx (versie van 2012-08-02) door Microsoft © Microsoft SQL Server 2008 R2 Feature Pack http://www.microsoft.com/en-us/download/details.aspx?id=16978 (versie van 2012-12-20) door Microsoft ©
Contactinformatie
Bij problemen of vragen is het steeds mogelijk contact op te nemen met volgende personen: Gino Vertriest (BI Architect) –
[email protected] Julien Verplanken (BI Analist/Ontwikkelaar) –
[email protected]
6 / 29
2 Toegang via Excel 2.1
Versies van Excel
2.1.1
Excel 2005 en ouder
Deze versies beschikken niet over de nodige functionaliteit. 2.1.2
Excel 2007
Excel 2007 bevat alle courante functionaliteit voor het raadplegen van kubussen. 2.1.3
Excel 2010
Excel 2010 bevat een aantal extra features:
Slicers: handige paneeltjes om snel selecties te kunnen maken Named sets: men kan bepaalde gebieden (sets) in een werkblad een alias (name) geven Uitgebreide zoekfunctionaliteit Sparklines: een type grafieken die in 1 veld passen
Bovendien zijn er talloze verrijkingen aangebracht, zoals:
64bit ondersteuning Verhoogde performantie van berekeningen Verbeterde functies en filters PowerPivot Add-In voor rapportage op maat Verbeterde draaitabellen Verbeterde conditionele formattering
Het wordt dus sterk aangeraden om met deze laatste versie van Excel te werken. De standaard werkboeken die we op het rapporten portaal gepubliceerd hebben maken gebruik van slicers.
Figuur 1: Sparklines voorbeeld
7 / 29
Figuur 2: Slicers voorbeeld
8 / 29
2.2
Installatie
Om te kunnen verbinden met de kubus dient er een driver geïnstalleerd te worden. Naargelang je versie van Windows (32 of 64 bit) dien je een ander installatiebestand te gebruiken: 32bit versie - http://go.microsoft.com/fwlink/?LinkID=188444&clcid=0x409 64bit versie - http://go.microsoft.com/fwlink/?LinkID=188445&clcid=0x409 Aan de instellingen in de vensters van deze installer hoef je niets aan te passen. Als je telkens op “Next” en “Finish” klikt zou de installatie vlekkeloos moeten verlopen. Aarzel niet bij problemen of vragen: contacteer het Digipolis BI-team.
9 / 29
2.3
Verbinden met een kubus
1
Open Excel
Figuur 3: Excel in Start Menu
2
Ga naar Gegevens Van andere bronnen Van Analysis Services
Figuur 4: Gegevensverbinding menu
3
Geef de servernaam in De productieserver GENBIASPR01.gentgrp.gent.be
noemt
Figuur 5: Server invullen
10 / 29
4
Selecteer de database en kubus
Figuur 6: Cube of Perspective selecteren
5
Sla de verbinding op met de gewenste naam en voltooi de wizard
Figuur 7: Verbinding opslaan
6
Geef aan waar je de draaitabel wil invoegen
Figuur 8: Locatie voor invoegen draaitabel
11 / 29
Opmerking 1: Reeds gemaakte verbindingen kan je steeds opnieuw openen, zonder dat je nog iets moet ingeven. Daarom is het ook zinvol de verbinding een duidelijke naam te geven. Figuur 9: Bestaande verbindingen menu
Figuur 10: Bestaande verbindingen venster
Opmerking 2: Op het rapporten portaal streven we ernaar steeds een bestand klaar te hebben waarop de verbinding met de kubus reeds gemaakt is. Zie http://BI-rapporten/Reports Figuur 11: Rapporten portaal
12 / 29
2.4 2.4.1
Werken met een draaitabel Inleiding
Een draaitabel (in het Engels: PivotTable) staat toe data weer te geven op een dynamische manier Hiermee kan men data samenvatten, rangschikken, groeperen en bewerken. Bij het opzetten van een nieuwe verbinding naar een kubus is de draaitabel leeg.
Figuur 12: Lege draaitabel Men kan metingen toevoegen (aangegeven met het icoon) , filteren op bepaalde velden en groeperingen doorvoeren in rijen en kolommen.
Figuur 13: Lijst met draaitabelvelden
13 / 29
2.4.2
Metingen
Metingen bevatten getalwaarden, zoals bijvoorbeeld ‘Aantal Vaststellingen’, ‘Opgelegde boete’, ‘Bedrag Eerste Aanmaning’, ‘Gemiddelde Boete’, … Bij het aanvinken van een meting worden deze automatisch toegevoegd in het volgende paneel:
Waarna we het volgende resultaat krijgen:
2.4.3
Groeperen
Om de metingen op te splitsen volgens bepaalde velden, kan men deze toevoegen aan rijen of kolommen.
Met als volgende resultaat:
14 / 29
In dit voorbeeld hebben we een hiërarchische opdeling (op Classificatie):
Figuur 14: Hiërarchische dimensie
Deze classificaties kan men uitklappen tot het gewenste niveau (door op het + tekentje te klikken). Opmerking: Men kan de subtotalen die je hierboven ziet uitschakelen. (Zie onder paragraaf 2.4.7: Belangrijke opties.) 2.4.4
Filteren
Om bepaalde gegevens onzichtbaar te maken en andere in de kijker te zetten kan men filteren. In volgend voorbeeld wil ik filteren op de 3 geselecteerde commisariaten : (Rechts klikken op de te filteren velden).
Figuur 15: Filteropties in draaitabel
Men heeft hierbij volgende opties:
Alleen geselecteerde items behouden Geselecteerde items verbergen: al de overige commisariaten blijven zichtbaar Eventueel bestaande filters wissen Top 10: de 10 (of een ander aantal) hoogste of laagste waarden in de geselecteerde groepering volgens een aan te geven meting Labelfilters: Filteren op de labels binnen een veld
15 / 29
Figuur 16: Labelfilter in draaitabel
Waardefilters: bijvoorbeeld enkel de kolommen met waarden boven 1000 vaststellingen worden weergegeven
Opmerking: Filteren kan op een geavanceerde en visueel aantrekkelijke manier door Slicers te gebruiken. (Zie onder paragraaf 2.4.9.) Filteren is ook mogelijk vanuit de lijst met draaitabelvelden. Als je een veld naar het Filter-paneel sleept wordt dit automatisch 2 rijen boven de draaitabel toegevoegd aan de lijst met filters en kan men op de geselecteerde velden waarden beginnen filteren:
Figuur 17: Veld naar Rapportfilter gesleept
Figuur 18: Rapportfilter boven draaitabel
Door ‘Meerdere items selecteren’ aan te vinken kan je op meer dan 1 item filteren.
16 / 29
2.4.5
Sorteren
Men kan gegevens sorteren van hoog naar laag (qua getalwaarden of alfabetisch) en omgekeerd. Dit kan enerzijds in de draaitabel (rechts klikken op de te filteren waarden):
Op eindtotalen en aantallen
Figuur 19: Sorteren in draaitabel veld
Op rij- en kolomvelden
Figuur 20: Sorteren in draaitabel kolom
Analoog kan je dit ook doen vanuit de ribbon (nadat je een rij, kolom of veld in de draaitabel geselecteerd hebt):
Figuur 21: Sorteren in ribbon
Figuur 22: Sorteer menu
Waarna je volgend scherm te zien krijg:
17 / 29
Figuur 23: Sorteeropties venster
De samenvatting geeft aan wat het effect gaat zijn op de draaitabel.
2.4.6
Vernieuwen van de data
Bij het toevoegen of verwijderen van velden wordt er steeds een verbinding gemaakt met de kubus en wordt de meest recente data van de kubus getoont. Indien men echter het Excel bestand bewaard en terug opend zal de data niet standaard ververst worden. Om de data te verversen dient men op de knop Gegevens Alles vernieuwen te klikken. (Een alternatief is automatisch vernieuwen inschakelen, zie paragraaf 2.4.7).
Figuur 24: Gegevens vernieuwen
2.4.7
Belangrijke opties
Hier worden een aantal belangrijke opties bij courant gebruik van draaitabellen uit de doeken gedaan. Verwijderen subtotalen
Figuur 25: Subtotaal aanvink menu
Men kan een subtotaal in het rechtermuismenu uitvinken.
Automatisch vernieuwen
Als je wil dat er bij het opnieuw openen van een gesloten Excel automatisch de inhoud vernieuwd wordt dan kan je dit zo instellen: Ga naar Gegevens Eigenschappen
18 / 29
Figuur 26: Eigenschappen van gegevenverbinding menu
en vink ‘Gegevens vernieuwen bij het openen van bestand’ aan:
Figuur 27: Eigenschappen van de verbinding
Opmerking: Het maximum aantal opgehaalde rijen staat ingesteld op 1000. In de meeste gevallen moet dit ruim voldoende zijn. Is dit echter te limiterend, dan kan dit ook in dit scherm verhoogt worden.
Klassieke weergave
De weergave in Excel 2010 staat zo ingesteld dat je de ervaring hebt van in een gewoon werkblad te werken en dat je verschillende rijen/kolommen kan selecteren. Indien je de opties wil in de draaitabel zelf rijen/kolommen te kunnen verslepen, dan dien je de Klassieke weergave in te schakelen:
19 / 29
Figuur 28: Opties voor draaitabel
Onder Opties voor draaitabel… Weergave vind je deze optie terug:
Figuur 29: Weergaveopties voor draaitabel
20 / 29
2.4.8
Meerdere draaitabellen
Om meerdere draaitabellen in 1 Excel werkboek te hebben volstaat het om deze te kopiëren en plakken (naar een andere geselecteerde locatie).
1
Selecteer de draaitabel volledig
2
Kopieer via rechtermuis of CTRL + C
Figuur 30: Draaitabel kopiëren
3
Selecteer het veld van bestemming en plak met de rechtermuis of CTRL + V (Dit beeld geeft trouwens nog een handige nieuwe feature van Excel 2010 weer: een voorbeeldweergave van een plak-operatie alvorens je de gewenste plakoptie werkelijk kiest!)
Figuur 31: Draaitabel plakken
Opmerking: Je kan evenwel een draaitabel toevoegen door een nieuwe verbinding te maken (of een bestaande verbinding te openen) via het Gegevens menu. Het is echter zo dat je één slicer enkel kan verbinden met draaitabellen die op eenzelfde verbinding zijn opgebouwd. (Meer hierover in paragraaf 2.4.9.)
21 / 29
2.4.9
Slicers
Een slicer is een visueel aantrekkelijke manier om te filteren op bepaalde waarden voor een veld. Je kan een slicer toevoegen op de volgende manier:
Figuur 32: Slicer menu
Hierna krijg je een scherm te zien waarin je de velden kan selecteren waarvoor je een slicer wil:
Figuur 33: Attributen voor slicers selecteren
Met als resultaat:
Figuur 34: Slicers ingevoerd
Slicers hebben toepassing op één verbinding en kunnen dus voor verschillende draaitabellen tegelijk gebruik worden. Om de verbindingen met draaitabellen te controleren of in te stellen:
22 / 29
Figuur 35: Draaitabelverbindingen menu
Figuur 36: Draaitabelverbindingen venster
Tip: als je dit veel zou gebruiken kan het nuttig zijn je Werkbladen en Draaitabellen een herkenbare naam te geven.
23 / 29
2.4.10 Berekeningen op een meting Naast de meting-waarden kan men op een meting ook extra berekeningen uitvoeren. Dit kan eenvoudig op volgende manier: 1
Voeg de meting nogmaals toe
Gevolg:
2
Stel het type anders in (en pas de naam bij voorkeur aan).
24 / 29
Figuur 37: Selectie type berekening
3
Resultaat:
Figuur 38: Berekeningen in draaitabel
25 / 29
2.4.11 Bewerkbare draaitabel Een draaitabel wordt gezien als een onontbindbaar object in Excel. Dit kan je echter aanpassen door naar Hulpmiddelen voor draaitabellen OLAP Converteren naar formules te gaan.
Figuur 39: Optie converteren naar formules
Dit zorgt ervoor dat je zelf kolommen of rijen kan invoegen en je eigen formules/berekeningen kan gebruiken.
Figuur 40: Draaitabel geconverteerd naar formules
In de veld-editor zie je dan de formule die naar het lid van de kubus verwijst. Je kan hierna wel niet meer aan de lijst met Draaitabelvelden (zie Figuur 13) en de verbindingen met slicers worden verbroken.
2.4.12 Rapport statisch maken Om een statisch rapport te verkrijgen – waarbij de gegevens bij het opnieuw openen niet meer aangepast worden – dient men de verbinding van de draaitabel met de kubus te verwijderen. Dit bekomt men op volgende wijze. 1. Klik de draaitabel aan en ga naar Gegevens Verbindingen in de ribbon.
Figuur 41: Gegevensverbindingen
26 / 29
2. Verwijder de verbinding die de draaitabel gebruikt.
Figuur 42: Werkmapverbinding verwijderen
27 / 29
2.5
Grafieken
2.5.1
Inleiding
Een grafiek is een visuele interpretatie van de inhoud van een draaitabel. Een grafiek is dan ook gekoppeld aan een draaitabel. Zodoende kan men met behulp van slicers real-time zowel draaitabellen als grafieken manipuleren, wat voor een vlotte en aangename gebruikerservaring zorgt. 2.5.2
Invoegen
Onder het menu Invoegen vind je meteen een aantal grafiektypes: Kolom, Lijn, Cirkel, Staaf, Vlak, Spreiding en Overige…
Figuur 43: Grafiek types
Tip: Voor de eenvoud ga je best al op een draaitabel staan met je muiscursor vooraleer je een grafiektype kiest. Dan is de verbinding ineens gemaakt en stelt Excel automatisch de grafiekinstellingen in. Voorbeeld: de verschillende sub-types in grafiektype kolom:
Figuur 44: Subtypes Kolom grafiek
28 / 29
Figuur 45: Resultaat Kolom grafiek
De verschillende types en subtypes spreken voor zich. Eventueel kunnen na het invoegen van de grafiek nog een aantal extra opties ingesteld worden (bijvoorbeeld: inverteren van de assen, het type notatie voor getallen, naam van grafiek, naam van assen, naam van legende, regels voor kleurverdelingen, …).
29 / 29
