Over de auteur Peter ter Braake is zelfstandig SQL Server docent/consultant. Hij is MCT sinds 2002 en SQL Server MVP sinds begin 2012. Hij werkt met SQL Server Reporting Services sinds de eerste release in 2004.
978 90 395 2721 4 123
9 *uklpdo#bxmxyv*
Aan de slag met Reporting Services 2012
Ook alle instellingen die u kunt kiezen voor rapporten nadat ze in gebruik zijn gekomen, komen aan de orde. Dankzij de opgaven biedt het boek voldoende mogelijkheden om te oefenen en ervaring op te doen. Zo kunt u de behandelde stof direct in de praktijk toepassen. Aan de slag met Reporting Services 2012 is bedoeld voor studenten en professionals die met Reporting Services gaan werken. Voorkennis van Reporting Services is niet noodzakelijk, maar kennis van het Microsoft-platform is een pre. Daarnaast is het in de praktijk onontbeerlijk om enige kennis te hebben van SQL of als u met kubussen wilt werken, van MDX.
ter Braake
Microsoft SQL Server 2012 Reporting Services is een uitgebreide tool om rapporten mee te maken, te beheren en te gebruiken. Het biedt vele mogelijkheden en opties en is bruikbaar in zowel de kleinste, alsook de grootste en meest kritische omgevingen. De beste manier om een uitgebreid product zoals Reporting Services te leren kennen is er actief mee te werken. Dit boek is geschreven om u daarbij te helpen. Stap voor stap wordt u begeleid bij het installeren van Reporting Services en vervolgens bij het maken van rapporten. Bij alle keuzes wordt stilgestaan bij de mogelijke opties en wanneer te kiezen voor welke optie. Zo leert u al doende goede en duidelijk rapporten te bouwen en ze te gebruiken.
Aan de slag met Reporting Services 2012 voor Microsoft SQL Server Peter ter Braake
Aan de slag met Reporting Services 2012 voor Microsoft SQL Server
Peter ter Braake
Meer informatie over deze en andere uitgaven kunt u verkrijgen bij: Sdu Klantenservice Postbus 20014 2500 EA Den Haag tel.: (070) 378 98 80 www.sdu.nl/service
© 2013 Sdu Uitgevers, Den Haag Academic Service is een imprint van Sdu Uitgevers bv.
Zetwerk: Redactiebureau Ron Heijer, Markelo Omslagontwerp: Studio Bassa, Culemborg Omslaguitvoering: Carlito’s Design, Amsterdam
ISBN 978 90 395 2721 4 NUR 123 / 995
Alle rechten voorbehouden. Alle intellectuele eigendomsrechten, zoals auteurs- en databankrechten, ten aanzien van deze uitgave worden uitdrukkelijk voorbehouden. Deze rechten berusten bij Sdu Uitgevers bv en de auteur. Behoudens de in of krachtens de Auteurswet gestelde uitzonderingen, mag niets uit deze uitgave worden verveelvoudigd, opgeslagen in een geautomatiseerd gegevensbestand of openbaar gemaakt in enige vorm of op enige wijze, hetzij elektronisch, mechanisch, door fotokopieën, opnamen of enige andere manier, zonder voorafgaande schriftelijke toestemming van de uitgever. Voor zover het maken van reprografische verveelvoudigingen uit deze uitgave is toegestaan op grond van artikel 16 h Auteurswet, dient men de daarvoor wettelijk verschuldigde vergoedingen te voldoen aan de Stichting Reprorecht (Postbus 3051, 2130 KB Hoofddorp, www.reprorecht.nl). Voor het overnemen van gedeelte(n) uit deze uitgave in bloemlezingen, readers en andere compilatiewerken (artikel 16 Auteurswet) dient men zich te wenden tot de Stichting PRO (Stichting Publicatie- en Reproductierechten Organisatie, Postbus 3060, 2130 KB Hoofddorp, www.cedar.nl/pro). Voor het overnemen van een gedeelte van deze uitgave ten behoeve van commerciële doeleinden dient men zich te wenden tot de uitgever. Hoewel aan de totstandkoming van deze uitgave de uiterste zorg is besteed, kan voor de afwezigheid van eventuele (druk)fouten en onvolledigheden niet worden ingestaan en aanvaarden de auteur(s), redacteur(en) en uitgever deswege geen aansprakelijkheid voor de gevolgen van eventueel voorkomende fouten en onvolledigheden. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, without the publisher’s prior consent. While every effort has been made to ensure the reliability of the information presented in this publication, Sdu Uitgevers neither guarantees the accuracy of the data contained herein nor accepts responsibility for errors or omissions or their consequences.
Voorwoord Voor u ligt een boek over Microsoft SQL Server 2012 Reporting Services. Reporting Services is een uitgebreide tool om rapporten mee te maken, te beheren en te gebruiken. Het biedt vele mogelijkheden en opties en is bruikbaar in zowel de kleinste, alsook de grootste en meest kritische omgevingen. Afhankelijk van uw situatie wilt u meer of minder van de mogelijkheden benutten. De beste manier om een uitgebreid product zoals Reporting Services te leren kennen is er actief mee te werken. Dit boek is geschreven om u daarbij te helpen. Stap voor stap wordt u begeleid bij het installeren van Reporting Services en vervolgens bij het maken van rapporten. Bij alle keuzes wordt stilgestaan bij de mogelijke opties en wanneer te kiezen voor welke optie. Zo leert u al doende mooie rapporten te bouwen en ze te gebruiken. Ook alle instellingen die u kunt kiezen voor rapporten nadat ze in gebruik zijn gekomen, komen aan de orde. Dit boek bevat opgaven om al het geleerde ook in de praktijk toe te passen. Zoals gezegd is doen belangrijk om met Reporting Services te leren werken. Middels de opgaven kunt u de nodige ervaring opdoen. Dit boek gaat niet uit van enige voorkennis. Het is bedoeld voor studenten en professionals die nu of in de toekomst met Reporting Services gaan werken. Tegelijkertijd is kennis van het Microsoft-platform een pre. Daarnaast is het in de praktijk onontbeerlijk om enige kennis te hebben van SQL (Structured Query Language) of MDX (Multi Dimensional eXpressions) als u met kubussen gaat werken. Dit boek was niet tot stand gekomen zonder het begrip van mijn vrouw. Ook wil ik Bert Brummelhuis en Albert van Dok bedanken voor hun input. Veel plezier met Reporting Services! Peter ter Braake Bunnik, januari 2013
v
Inhoud
Voorwoord
v
1
Inleiding informatiemanagement 1.1 Inleiding Business Intelligence 1.1.1 De relationele database 1.1.2 Business Intelligence 1.2 Het datawarehouse 1.3 Het stermodel 1.4 Soorten beslissingen 1.5 Soorten rapporten 1.6 OLAP-kubus 1.6.1 Voordelen van kubussen 1.6.2 Tabular Model 1.7 Ontstaan SQL Server Reporting Services 1.8 Tot slot
1 1 1 2 4 5 8 10 12 13 15 15 17
2
Inleiding Microsoft SQL Server Reporting Services 2.1 Wat is SQL Server Reporting Services? 2.2 SQL Server-edities 2.3 Installeren van Reporting Services 2.4 Stand-alone installatie 2.4.1 Controle 2.5 Configureren van stand-alone Reporting Services 2.5.1 Configureren van de ReportServer-database 2.5.2 Configureren van de Web Service 2.5.3 Overige instellingen 2.5.4 Tot slot 2.6 SharePoint integrated mode 2.7 Configureren voor SharePoint integrated mode 2.7.1 Reporting Services-service toevoegen aan SharePoint 2.7.2 Reporting Services Service Application 2.8 Activeren Power View 2.9 Installeren van PowerPivot voor SharePoint 2.9.1 PowerPivot Gallery aanmaken 2.9.2 BI Semantic Model Connection 2.10 Downloaden en installeren Report Builder 3.0 2.11 Tot slot
19 19 20 22 23 26 27 28 30 32 33 34 35 36 36 38 38 39 39 39 41
vii
Aan de slag met Reporting Services 2012
viii
3
Eenvoudige rapporten maken 3.1 Projecten 3.1.1 Shared Data Sources 3.1.2 Shared Datasets 3.2 Het eerste rapport 3.2.1 Data Source 3.2.2 Dataset 3.2.3 Text, Table of Stored Procedure? 3.2.4 Data Regions 3.2.5 Basale opmaak 3.2.6 Headers en footers 3.2.7 Conditionele opmaak en afwisselende achtergrond 3.2.8 Sortering en herhalingen 3.2.9 Groepen 3.2.10 Berekende velden 3.3 Report Builder 3.3.1 Maken van een rapport 3.4 Tot slot
4
Interactieve rapporten en Data Regions 4.1 Rapporten met parameters 4.1.1 Queryparameters 4.1.2 Parameter Properties 4.1.3 Gekoppelde parameters 4.1.4 Filteren op gebruikersnaam 4.1.5 Multiple Values 4.1.6 Filters 4.2 Interactieve rapporten 4.2.1 Document Map 4.2.2 Drill-down 4.2.3 Action 4.3 Data Regions: List, Chart en Matrix 4.3.1 Templates 4.3.2 List 4.3.3 Chart 4.3.4 Matrix- en subrapporten 4.3.5 Drill-through naar Stamkaart 4.4 Meer visualisaties 4.4.1 Sparkline 4.4.2 Data Bar 4.4.3 Indicators en Gauges 4.5 Pagina-instellingen 4.5.1 Page size en Interactive page size 4.6 Gegevens uit SQL Server Analysis Services 4.6.1 Gegevens uit een kubus 4.6.2 Gegevens uit een Tabular Model 4.7 Tot slot
43 43 45 47 49 50 51 53 57 59 64 68 70 72 75 79 80 81 83 83 84 85 87 89 90 92 95 96 97 100 101 102 103 105 107 108 109 110 111 112 115 115 116 116 118 118
Inhoud
5
Beheer van Reporting Services 5.1 In gebruik nemen van rapporten 5.1.1 Projectinstellingen 5.1.2 Publiceren vanuit SQL Server Data Tools 5.1.3 Andere opties 5.1.4 Tot slot 5.2 Rapportinstellingen 5.2.1 De rapport Data Source 5.2.2 Caching 5.2.3 Snapshots 5.2.4 Report History 5.2.5 Rapporttime-outs 5.2.6 Parameters 5.2.7 Linked Reports 5.3 Subscriptions 5.3.1 Standard Subscriptions 5.3.2 Data-driven Subscriptions 5.4 Security 5.4.1 Role-based Security in native mode 5.4.2 Rechten in SharePoint 5.5 Data Alerts 5.6 Tot slot
119 119 119 121 122 123 123 123 127 130 132 133 133 134 135 135 138 140 141 142 143 144
6
PowerPivot en Tabular Models 6.1 Inleiding 6.1.1 Wie maakt BI-oplossingen? 6.1.2 Selfservice BI 6.2 PowerPivot 6.2.1 Downloaden en installeren 6.2.2 Een PowerPivot-werkmap maken 6.2.3 Een PowerPivot-rapport maken in Excel 6.2.4 Het model verbeteren 6.2.5 Nieuwe kolommen maken 6.2.6 Sorteren 6.2.7 Hiërarchieën 6.2.8 Relaties maken 6.2.9 Tot slot 6.3 Business Intelligence Tabular Model 6.3.1 Tabular Models versus Multidimensional Models 6.3.2 Een Tabular Model maken met SQL Server Data Tools 6.3.3 Analyze in Excel 6.3.4 Berekende waarden toevoegen 6.3.5 Tijdintelligentie toevoegen 6.3.6 Instellingen voor rapportages 6.3.7 KPI’s maken 6.3.8 Perspectives
145 145 145 147 148 148 148 151 153 154 156 157 159 160 160 160 161 163 164 165 166 169 170
ix
Aan de slag met Reporting Services 2012
7
x
6.3.9 PowerPivot versus Tabular Model 6.3.10 Publiceren van het model 6.4 Tot slot
171 173 173
Power View 7.1 Connecties maken 7.2 Het eerste Power View-rapport 7.3 Rapporten bekijken en highlighting 7.3.1 Opslaan van een rapport 7.3.2 Exporteren naar PowerPoint 7.4 Filters en Slicers 7.4.1 Filters 7.4.2 Slicers 7.5 Views, Tiles en Cards 7.5.1 Views 7.5.2 Tiles 7.5.3 Cards 7.6 Bubble en Scatter Charts 7.6.1 Scatter Charts 7.6.2 Bubble Charts 7.6.3 Extra opties 7.7 Tot slot
175 175 177 180 182 182 182 182 183 185 185 185 186 187 188 188 188 189
Opgaven Opgaven bij Hoofdstuk 3 en 4 – Creating Reports Opgave 1 – Territory Sales Drilldown Opgave 2 –Sales Order Detail Extra opgaven bij Hoofdstuk 3 en 4 – Report Builder 3.0 Opgave 3 – Publish Report Parts Opgave 4 – Report Builder 3.0 Opgave 5 – Aanpassingen maken aan het rapport Opgaven bij Hoofdstuk 5 – Reporting Management Opgave 6 – Deploying en Linked Reports Opgave 7 – Scheduling Opgave 8 – Monitoring Opgave 9 – Caching en Snapshots Opgave 10 – Security Opgaven bij Hoofdstuk 6 – Tabular Model Opgave 11 – PowerPivot Opgave 12 – Maak een Excel-rapportage Opgave 13 – Maak SSAS Tabular Model Opgave 14 – Deploy en gebruik van Tabular Model Opgave 15 – Extra Opgaven bij Hoofdstuk 7 – Power View Opgave 16 – Power View-visualisaties maken
191 191 191 195 196 196 198 199 199 199 200 200 201 202 202 202 204 206 209 210 210 210
213
Index
Hoofdstuk 1
Inleiding informatiemanagement Met Reporting Services en de bijbehorende tools kunt u rapporten maken, in productie nemen en beheren. In dit boek gaat u over al die facetten leren. Voordat u daar in Hoofdstuk 2 echt mee begint, is het goed om eerst een kader te scheppen. Waarvoor is een tool zoals Reporting Services bedoeld en waar komt Reporting Services vandaan? Beide vragen worden beantwoord in dit eerste hoofdstuk.
1.1
Inleiding Business Intelligence
Mensen hebben binnen bedrijven altijd al met informatie gewerkt. Als u in het kader van uw werk een beslissing moet nemen, wilt u die het liefst baseren op kennis en op feiten. U heeft dus informatie nodig. Rapporten bieden als het goed is die informatie. Daarom zijn rapporten onmisbaar in het bedrijfsleven. Met de opkomst van de computer kwam die informatie in databases terecht. Samen met de opkomst van databases ontstond de behoefte om rapportages te maken op basis van de gegevens in die databases. Iedereen wil weten wat er hier en nu speelt binnen het bedrijf. En veel van wat er te weten is, zit in de database. 1.1.1
De relationele database
Begin jaren zeventig van de vorige eeuw ontwikkelde Edgar F. Codd van IBM de relationele database. Marketingverhalen over de eerste relationele databases uit die tijd vertellen ons dat het real-time voor handen hebben van informatie via rapportages de meerwaarde van (relationele) databases is. Als gegevens handmatig uit dossiermappen gehaald moeten worden en overgeschreven moeten worden op een rapportage, zult u waarschijnlijk minder informatie, op een later moment krijgen. OLTP versus OLAP Uit die tijd komen ook de termen OLTP en OLAP. OLTP staat voor Online Transaction Processing en OLAP betekent Online Analytical Processing. In beide gevallen heeft online hier niets te maken met het internet (dat bestond toen nog niet). Het refereert aan het feit dat we tijdens het werken live een connectie hebben met de database. Dus krijgt de gebruiker direct het antwoord op de vraag die hij/zij stelt. Naast het online werken met de database werd de relationele database hand in hand ontworpen met SQL, oftewel Structured Query Language. In eerste instantie was de beoogde naam van die taal zelfs English Query Language, wat de ambitie goed weergeeft. SQL zou ons in staat moeten stellen om in het Engels (of zo dicht als mogelijk bij het Engels) een vraag te formuleren en er dan real-time antwoord op te krijgen. Kortom, precies wat we willen.
1
Aan de slag met Reporting Services 2012
1.1.2
Business Intelligence
Tegenwoordig is Business Intelligence een groot vakgebied waarin bedrijven veel investeren. De term Business Intelligence is een soort parapluterm waar we veel onder zouden kunnen scharen. Een exacte definitie is dan ook moeilijk, maar zou zoiets kunnen zijn als: Business Intelligence beoogt iedereen in een organisatie op het juiste moment van de juiste informatie te voorzien, in het juiste formaat. Deze definitie is eigenlijk een zinnetje dat bij de introductie van relationele databases in de jaren zeventig al gebruikt werd. Blijkbaar heeft de relationele database toch niet gebracht wat er toentertijd van gedacht werd, anders was Business Intelligence nu niet zo groot geweest. Waarom is dat? Er is een aantal oorzaken aan te wijzen waarom de relationele database met een rapportageomgeving niet voldoet: V Bedrijven hebben niet één database, maar meer databases. V De kwaliteit van de gegevens in databases laat te wensen over. V De performance van de databases is niet toereikend. V Gegevens worden overschreven door nieuwe gegevens. Bedrijven hebben niet één database maar meer databases Het eerste probleem zit hem in het feit er bijna altijd sprake is van meer dan één database. De verkoopafdeling werkt met een CRM-pakket (Customer Relationship Management) waarin onder andere alle orders terechtkomen. De Financiële Administratie heeft zijn eigen financiële pakket waarin facturen worden ingeboekt, grootboekposten worden aangemaakt en betalingen worden verwerkt. We zouden in staat moeten zijn uit beide systemen de omzet te halen. Alle orders uit een periode tezamen bepalen de omzet van die periode. Net zo goed is de omzet de som van alle betalingen uit een periode. Toch blijkt in de praktijk keer op keer dat beide systemen met een ander antwoord komen, met soms grote verschillen tussen beide antwoorden. Deels zal het probleem hier zitten in definities. Wat is nu eigenlijk omzet? Wat verstaan we daar precies onder? En wanneer reken je iets als omzet: als de klant zijn handtekening zet, als je zelf je goederen of diensten levert, of als het geld op je rekening wordt bijgeschreven? Er is een grote kans dat verschillende systemen verschillende definities gebruiken. Dat is meteen een belangrijke les voor iedereen die rapporten gaat maken: zorg dat definities helder zijn voordat je begint, documenteer deze definities en gebruik consequent dezelfde definities in verschillende rapporten. Kwaliteit van de gegevens in databases laat te wensen over Een andere oorzaak van onbetrouwbare antwoorden zit hem in de kwaliteit van de gegevens. Veelal zijn gegevens handmatig ingevoerd (dat wordt overigens wel steeds minder) of de gegevens zijn nog ergens handmatig verwerkt, dan wel bewerkt. En waar mensen werken, worden fouten gemaakt. Een komma is zo verkeerd gezet, maar kan wel een factor tien schelen in een bedrag, waardoor totalen niet meer kloppen.
2
Hoofdstuk 1 – Inleiding informatiemanagement
Daarnaast zijn gegevens vaak incompleet, zoals postcodes die niet zijn ingevuld. Een ander veel voorkomend probleem is dat van de dubbele gegevens. Een klant komt in de database eenmaal voor met de toevoeging B.V. en eenmaal zonder. Dezelfde klant komt dan vaker in de database voor, wat analyses als omzet per klant onbetrouwbaar maakt. Er bestaan bedrijven die niets anders doen dan databases van hun klanten opschonen om bovenstaande problemen zo veel mogelijk op te lossen. De performance van de databases is niet toereikend Naast bovengenoemde kwaliteitsproblemen zijn er ook nog performance-issues. Informatie uit de database krijgen, houdt in: een query schrijven, oftewel een SQL SELECT-statement schrijven als de database een relationele database is. En SQL lijkt misschien een beetje op Engels, en dus zou het makkelijk moeten zijn een vraag te formuleren, de praktijk is toch anders. Al was het maar dat een database uit honderden tabellen kan bestaan en je als queryschrijver de tabelstructuur moet kennen om te weten welke tabellen je nodig hebt en hoe die tabellen onderling bij elkaar horen. Vaak is degene die op businessgerelateerde vragen antwoorden zoekt (de gebruiker van de rapporten) niet degene die SQL beheerst en/of niet de persoon die de rechten heeft om die vragen zelf rechtstreeks aan de database te stellen. Maar als de gebruiker zijn vraag eerst moet uitleggen aan iemand van ICT en die ICT-er, gezien zijn/haar volle agenda, dan een week later pas de query gaat schrijven, duurt het lang voordat de gebruiker antwoord krijgt. Veel te lang. Los van dit kennisprobleem (heeft de gebruiker wel de technische kennis om rapporten te maken?) is er ook een performanceprobleem van meer technische aard. De meeste operationele systemen hebben wat we noemen ‘uitgenormaliseerde data bases’. Dat houdt in dat niet alle gegevens in één en dezelfde tabel zitten, maar dat de gegevens verdeeld zijn over meer tabellen, met als uitgangspunt om redundantie (het dubbel opslaan van gegevens) te voorkomen. Dat is goed voor zowel de snelheid waarmee we in de database kunnen schrijven (nieuwe informatie invoeren, maar ook bestaande gegevens aanpassen) alsook voor de kwaliteit van de gegevens. Maar door normaliseren wordt het complexer de gegevens uit de database te halen. De leessnelheid wordt slechter. We zullen namelijk in bijna alle gevallen gegevens uit meer tabellen moeten halen (oftewel joins schrijven). En los van het feit dat het schrijven van de query daar lastiger van wordt, moet de database die complexe query oplossen en daar heeft de database uiteraard tijd voor nodig. Met een beetje pech krijgen we er nog locking- en blockingproblemen bij waardoor de performance nog slechter wordt. Blocking treedt op als de ene gebruiker gegevens aan het wijzigen is en de andere gebruiker op hetzelfde moment die gegevens wil lezen. Het lezen zal moeten wachten tot het schrijven klaar is. Wachten vertaalt zich in deze context natuurlijk tot slechte performance. Gegevens worden overschreven door nieuwe gegevens Een ander probleem waar sommige databases mee te maken hebben, is het probleem dat gegevens vaak overschreven worden door nieuwe gegevens zonder de oude ge
3
Aan de slag met Reporting Services 2012
gevens te bewaren. Stel bijvoorbeeld dat een verkoper die verantwoordelijk is voor de regio Noord gaat verhuizen naar Maastricht. Om zijn reistijd te beperken verandert hij ook van regio, hij wordt verantwoordelijk voor de regio Zuid. In veel systemen wordt nu Noord overschreven door Zuid. Voor de dagelijkse gang van zaken geen probleem. Maar als we nu proberen te analyseren hoe veel omzet is gerealiseerd in de regio Zuid en we die analyse doen via de verantwoordelijke verkoper, blijkt de omzet veel hoger uit te komen dan wat echt gerealiseerd is. De omzet van voor de verhuizing is meeverhuisd naar de regio Zuid. Kortom, voor een eenduidig, betrouwbaar en snel rapportagesysteem hebben we waarschijnlijk meer nodig dan een mooie rapportagetool en een database. Niet alle bedrijven hebben bovenstaande problemen. Het hangt af van de grootte van het bedrijf (hoeveelheid data, hoeveelheid databases, hoeveelheid gebruikers), de mate van integratie tussen de verschillende systemen en de performance-eisen die we zelf stellen. Is er sprake van één of meer van de bovenstaande problemen, dan kan wellicht een datawarehouse een oplossing bieden.
1.2
Het datawarehouse
Voordat bedrijven die te kampen hebben met boven beschreven problemen meteen een volledig datawarehouse gaan inrichten, is er misschien nog een tussenoplossing mogelijk. Regelmatig wordt er een zogenaamde rapportagedatabase gemaakt. In de meeste gevallen is dat niet meer dan een kopie van de operationele database waarop de rapporten dan gebaseerd worden. Een belangrijke reden om een rapportagedatabase te maken is offloading van de rapportageworkload naar een ander systeem. Met andere woorden: één systeem wordt gebruikt voor de dagelijkse processen, een ander systeem wordt gebruikt om uit te lezen. Het schrijven en tegelijkertijd lezen, levert nu geen concurrencyproblemen (het tegelijkertijd werken) meer op, wat zowel voor het operatio nele systeem als voor het rapportagesysteem performanceproblemen kan oplossen. De implementatie kan heel eenvoudig zijn. Zet bijvoorbeeld elke nacht de back-up van de operationele database, die hoogstwaarschijnlijk toch gemaakt wordt, terug op een andere server. Nadeel is natuurlijk dat de rapportages nu niet meer real-time zijn. De informatie die in de rapporten getoond wordt, is nu maximaal een dag oud. Uiteraard kunnen hier met iets genuanceerdere technieken betere resultaten behaald worden. Een rapportagedatabase lost misschien een deel van de problemen op, maar zeker niet alle. De volgende stap zou kunnen zijn een datawarehouse in te gaan richten. Van een datawarehouse wordt weleens gezegd dat het de ‘single version of the truth’ is, dan wel moet zijn. Oftewel, niet langer verschillende databases met andere antwoorden, maar één centrale database (het datawarehouse) voor alle vragen en analyses. En met maar één systeem, is er maar één antwoord. Niet langer andere data en verschillende definities omdat er toevallig verschillende databases zijn. Het datawarehouse wordt gevoed vanuit de verschillende bronnen. Daarbij wordt gekozen voor centrale definities van zaken zoals omzet en cost of sales en worden gegevens die kwalitatief niet voldoen
4
Hoofdstuk 1 – Inleiding informatiemanagement
aan de eisen aangevuld en opgeschoond (voor zover mogelijk) tot ze wel voldoen. Met andere woorden: het datawarehouse is een database die de waarheid bevat. Een datawarehouse is uiteindelijk gewoon een (relationele) database. Dat wil zeggen dat we tabellen maken en de gegevens opnemen in die tabellen. Een voordeel van een datawarehouse ten opzichte van een rapportagedatabase (zoals eerder besproken), is dat we een nieuwe tabelstructuur kunnen bedenken voor het datawarehouse. En die structuur kunnen we optimaliseren voor het doel waarvoor het datawarehouse dient: rapporten maken en analyses doen ten behoeve van de besluitvorming in een bedrijf. Veel lezen dus in de database en niet of nauwelijks schrijven. Een model dat daar uitermate geschikt voor is, is het stermodel.
1.3
Het stermodel
Over wat de beste structuur voor een datawarehouse is, bestaat nog veel discussie. De eerste die met een modelleringstechniek voor datawarehouses kwam was Ralph Kimball. Hij is de bedenker van dimensioneel modelleren en daarmee van het ster model. Bill Inmon was een andere pionier op het gebied van datawarehousing en kwam met een andere architectuur. Relatief nieuw is de door Dan Linstedt bedachte Data Vault, een derde manier om een datawarehouse op te zetten. Het gaat te ver om hier alle drie de architecturen te bespreken. Een grote overeenkomst is echter dat alle drie zeggen dat het stermodel een goede tabelstructuur is om op te rapporten. Vandaar dat we het stermodel hier wel kort zullen beschrijven.
Figuur 1.1 Voorbeeld van een stermodel met de feitentabel factResellerSales in het midden
5
Aan de slag met Reporting Services 2012
In figuur 1.1 ziet u een voorbeeld van een stermodel. In latere hoofdstukken gaat u op basis van deze tabellen rapporten maken. De naam stermodel komt voort uit de structuur. Er is één centrale tabel, de feitentabel, met eromheen een aantal tabellen, de zogenaamde dimensietabellen. De feitentabel Bij een sterschema (stermodel) gaan we uit van een proces in de organisatie waar we rapportages en analyses voor moeten maken. De eerste stap is het bepalen van de feiten rond dit proces. Hoe is het proces te meten? Wat bepaalt dat een proces succesvol verloopt of niet? Voor een verkoopproces liggen zaken als omzet, marge, kosten, verkochte aantallen, et cetera, voor de hand. Over het algemeen hebben we het daarmee over numerieke grootheden. Aggregeerbare grootheden ook, want van dagomzetten kunnen we ook weekomzetten maken. De tweede belangrijke stap is het bepalen van de grain, oftewel het detailniveau waarop we de gegevens willen bijhouden. Willen we omzet per dag of per week bijhouden? Of is tijd hier eigenlijk niet direct van belang, maar is het beter omzet per verkochte order bij te houden? Een verkeerd detailniveau kiezen is desastreus. Van dagomzetten kunnen makkelijk weekomzetten berekend worden (gewoon de zeven dagen van dezelfde week bij elkaar optellen), maar terug van weekomzetten weer dagomzetten maken, is niet meer mogelijk. Simpel de weekomzet door zeven delen levert immers niet het juiste resultaat. De keerzijde is dat het opslaan van meer details direct leidt tot meer data en dus een grotere database met de bijbehorende performance-issues tot gevolg. Omdat het hier gaat om de meetbare feiten van een proces, wordt de tabel waar het hier over gaat de feitentabel genoemd. De dimensietabellen Vervolgens bepalen we de zogenaamde dimensies, de grootheden die binnen het proces dat we beschrijven een rol spelen. De keuze voor het detailniveau geeft ons al veel informatie. Stel dat we als detailniveau gekozen hebben voor: de omzet per dag, per product, per winkel. Dit levert meteen drie dimensies op: een datumdimensie die bijna altijd aanwezig is, een productdimensie en een winkeldimensie. Blijkbaar gaat het hier om het verkopen van producten in winkels van een winkelketen. Naast de dimensies die we nu gevonden hebben, bestaan er misschien nog wel andere dimensies. We zouden kunnen denken aan een verkoperdimensie, reclameactiedimensie, et cetera. Het kennen en doorgronden van het proces dat we modelleren is cruciaal voor het bedenken van de juiste dimensies en dus voor het ontwikkelen van de het juiste stermodel. De dimensietabellen zijn zogenaamde ‘platgeslagen’ tabellen. Daarmee wordt bedoeld dat de tabellen niet uitgenormaliseerd zijn zoals in de meeste operationele databases. Ze staan bol van de redundantie (dubbele gegevens). In een OLTP-systeem is dat, zoals eerder gezegd, slecht voor zowel de kwaliteit van de gegevens als voor de performance van de database. Daarbij wordt dan wel de schrijfperformance van de database bedoeld. Voor het lezen, en daar gaat het natuurlijk om in een datawarehouse, heeft het juist veel voordelen om niet te normaliseren. De gegevens worden makkelijker en
6
Hoofdstuk 1 – Inleiding informatiemanagement
sneller benaderbaar. Stel bijvoorbeeld dat het salesproces in het bovengenoemde voorbeeld om groentewinkels gaat. Alle aardappels worden ingekocht door een inkoper genaamd Jan. Alle aardappels hebben een prijs per kilo (geen stuksprijs). De productdimensie zou er nu als volgt uit kunnen zien. ProductNummer
ProductNaam
ProductType
Inkoper
PrijsType
Prijs
1
Bintje
Aardappel
Jan
Per kilo
2,00
2
Eigenheimer
Aardappel
Jan
Per kilo
2,50
De kolommen Inkoper en PrijsType zijn hier redundant. De waarde van de kolommen volgt immers uit het feit dat het ProductType Aardappel is. In een genormaliseerd systeem zouden we hier twee tabellen van maken. De dimensies in een stermodel geven betekenis aan de feiten uit de feitentabel. We vullen de dimensietabel met zo veel mogelijk beschrijvende informatie, veelal character strings. Dus waar de feitentabel vooral uit numerieke gegevens bestaat, bestaan dimensietabellen veelal uit kolommen met tekst. Via deze beschrijvende kolommen kunnen we analyseren en filteren. Met de feitentabel en de bijbehorende dimensietabellen is het stermodel af. Uiteraard zijn er veel uitzonderingen en speciale gevallen. Over dimensioneel modelleren zijn hele boeken volgeschreven, dus bovenstaande korte beschrijving is verre van accuraat en volledig. Als we het ERD (entiteitenrelatiediagram) tekenen, oftewel het plaatje van de tabellen en hun onderlinge verbanden, zien we waarom het een stermodel wordt genoemd. Centraal staat de feitentabel met daaromheen de dimensietabellen, zodat het geheel er enigszins uitziet als een ster. Zo’n ster bestaat over het algemeen uit slechts een paar tabellen. Eén feitentabel met negen dimensies, dus tien tabellen tezamen, is al redelijk veel. Geen tientallen, honderden of zelfs meer, zoals bij uitgenormaliseerde databases wel normaal is. Een gevolg daarvan is dat het schrijven van queries makkelijker wordt. Bovendien hebben moderne databasesystemen, zoals SQL Server, speciale optimalisaties die queries op sterschema’s extreem efficiënt kunnen uitvoeren. Een sterschema is dus goed voor het gebruikersgemak (degene die de queries schrijft en dus degene die rapporten maakt) en voor de performance. Met een datawarehouse kunnen we dus de gegevens van meer databases, eventueel zelfs aangevuld met gegevens uit externe bronnen, samenbrengen in één grote database. Bovendien kunnen we met het overhalen van de gegevens naar het datawarehouse, het ETL-proces (Extract, Transform, Load), de kwaliteit van de gegevens en daarmee van de informatie, aanpassen. En door de mogelijkheid een andere server en een ander databasedesign te kiezen, kunnen we de performance sterk verbeteren. Maar zoals altijd in de databasewereld, is er een prijs voor al die voordelen. De gegevens zijn even up-to-date als de laatste keer dat het datawarehouse is gevuld. De rapporten
7
Aan de slag met Reporting Services 2012
geven dus geen real-time informatie, maar een beetje verouderde informatie. Vaak zien we dat datawarehouses elke nacht opnieuw gevuld worden, maar andere schema’s zijn natuurlijk ook mogelijk. Wat wijsheid is qua vulschema’s, hangt af van de eisen die de business stelt aan de informatie die uit het datawarehouse moet komen en van de grootte van de hoeveelheid data. Maar hoe erg is het eigenlijk als rapporten geen real-time informatie bevatten?
1.4
Soorten beslissingen
Eerder hebben we als definitie voor Business Intelligence gebruikt: Iedereen in een organisatie op het juiste moment van de juiste informatie voorzien, in het juiste formaat. Daaraan moeten we eigenlijk toevoegen: met als doel beslissingen te kunnen nemen op basis van betrouwbare informatie (in plaats van op basis van een onderbuikgevoel of slechts ‘ervaring’ van de betrokken persoon). De mensen moeten goed geïnformeerd zijn, zodat ze op basis van die informatie de goede beslissingen kunnen nemen. Maar wie neemt welke beslissingen? Microsoft kwam bij de introductie van SQL Server 2005 met de slogan: ‘BI for the masses.’ Met andere woorden, iedereen, van hoog tot laag, neemt weleens beslissingen. En voor iedereen geldt: die beslissingen zouden gebaseerd moeten zijn op informatie. Uiteraard is er wel verschil tussen de verschillende beslissingen en de impact die een beslissing heeft op de totale business. In de literatuur worden drie soorten beslissingen onderscheiden: strategische beslissingen, tactische beslissingen en operationele beslissingen. Strategische beslissingen Strategische beslissingen zijn beslissingen ten aanzien van de strategie van een bedrijf. Waar willen we heen? Hoe moet het bedrijf er over vijf jaar uitzien? Hoe reageren we het beste op de crisis, zodat we de crisis goed doorstaan en er het liefst sterker uitkomen dan we erin gingen? Dit soort besluiten kenmerkt zich door een lage frequentie, maar een hoge impact op de business en daarmee op de mensen. Een ICT-opleider zou zich bijvoorbeeld in crisistijd, als er weinig cursussen worden gevolgd, kunnen omvormen tot een consultancybedrijf. De docenten moeten dan consultants worden die gedetacheerd worden, verkopers moeten geen cursussen meer verkopen maar mensen bij klanten plaatsen en de planners raken misschien hun baan wel helemaal kwijt. Grote impact dus op iedereen. Maar als de kogel eenmaal door de kerk is, moet je deze koers blijven varen. Je kunt niet na één maand alweer het roer omgooien. Om een beslissing, zoals hierboven beschreven, gefundeerd te kunnen maken, heb je veel informatie nodig. Het management wil de omzet, marge en winstcijfers van de afgelopen jaren hebben. Hoe zijn we gevaren tijdens de vorige crisis? Het liefst moet deze informatie aangevuld worden met externe informatie. Hoe deden de concurrenten het toen? Hoe reageerde de markt toen en verwachten we nu een zelfde reactie? Om dit soort vragen te kunnen beantwoorden zijn veel gegevens nodig, namelijk omzet-
8
Hoofdstuk 1 – Inleiding informatiemanagement
en margegegevens over meerdere jaren. Daarbij zijn we niet meer geïnteresseerd in details, maar in geaggregeerde data. Individuele cursisten en cursussen zijn niet meer interessant. De totale omzet is interessant. Individuele klanten zijn niet meer interessant, maar wel de vraag welke branches het ondanks de crisis goed bleven doen. Veel data dus, samengepakt tot een beperkte hoeveelheid informatie. Van real-time eisen aan de informatie is hier geen, of zo goed als geen sprake. Tactische beslissingen De tweede soort beslissingen is die van de tactische beslissingen. Tactische beslissingen horen voornamelijk thuis bij het middenmanagement. Ze gaan over de uitvoer. Hoe brengen we de gekozen strategie in de praktijk ten uitvoer? Over het algemeen wordt dit per afdeling bekeken. Er worden doelstellingen bepaald en die doelstellingen moeten gecontroleerd worden. Zitten we nog op schema? Gaan we de doelstellingen halen? Deze vraag kan een manager van een afdeling zich stellen, maar een teamleider kan zich die vraag ook stellen voor zijn team en een individuele werknemer voor zichzelf. Deze vragen en de besluiten waartoe de antwoorden leiden, kenmerken zich door een hogere frequentie dan strategische besluiten. De impact is beperkter en de hoeveelheid gegevens die we nodig hebben om zinvolle informatie te leveren om de besluiten weer goed onderbouwd te nemen, is kleiner. Het gaat eerder over maanden dan jaren en eerder over afdelingen of teams dan over het hele bedrijf. Nog steeds is er niet echt sprake van een harde eis voor real-time informatie, hoewel daar uitzonderingen op bestaan. Bovendien kunnen de betrokken mensen daar anders over denken. Ik heb ooit een manager horen eisen dat zijn rapport ten aanzien van het ziekteverzuim van vorig jaar real-time moest zijn! Operationele beslissingen De derde soort beslissingen is die van de operationele beslissingen. Dit zijn de dagelijkse besluiten die bijna iedereen neemt tijdens het uitvoeren van zijn of haar dagelijkse taken. Denk bijvoorbeeld aan een inkoper die op basis van de huidige voorraad besluit wel of niet extra in te kopen. De frequentie van dit soort beslissingen is heel hoog, we hebben er niet al te veel informatie voor nodig en de impact van een (verkeerde) beslissing zal niet al te groot zijn. Uiteraard willen we alle processen binnen het bedrijf zo veel mogelijk optimaliseren en is het dus noodzaak ook deze beslissingen op basis van accurate informatie te nemen, in plaats van op gevoel of ervaring af te gaan. Bij operationele beslissingen komen real-time eisen (terecht en onterecht) het meest voor. Moet een supermarkt nog brood bijbakken als de voorraad bijna op is? Elke klant die voor een leeg schap staat, is een ontevreden klant die misschien wel naar de concurrent overstapt. Maar elk brood dat na sluitingstijd nog niet verkocht is, moet worden weggegooid en dat is kapitaalvernietiging. Voor zo’n beslissing heb je niets aan de voorraad en het aantal verkopen van gisteren. Actuele informatie is nu opeens van groot belang.
9
Aan de slag met Reporting Services 2012
1.5
Soorten rapporten
In de eerder gekozen definitie van Business Intelligence staat ook dat mensen de informatie in het juiste formaat moeten krijgen. Deels sluit dat aan bij het bovenstaande verhaal over soorten beslissingen. Deels gaat het ook over de mensen die de informatie moeten krijgen en hoe die mensen werken. Operationele rapporten zijn heel vaak lijstjes. Bijvoorbeeld een lijst van klanten van wie het abonnement bijna afloopt, of een lijst van mensen die trial software van de website hebben gedownload. Of een lijst van producten waarvan de voorraad onder een kritieke grens is gezakt, en ga zo maar door. Bij tactische beslissingen horen vaker rapporten die in één oogopslag de stand van zaken laten zien. Dashboards worden dan interessant. Dashboards laten vaak in een grafische weergave snel op hoofdlijnen zien hoe iets ervoor staat. Daar horen over het algemeen ook KPI’s bij. In figuur 1.2 ziet u een voorbeeld van een dashboard.
Figuur 1.2 Dashboard uit SQL Server Management Studio
Een KPI, kritischeprestatie-indicator (key performance indicator), laat zien hoe een actuele stand van zaken zich verhoudt tot een doelstelling of beoogde stand van zaken. Meestal gebeurt dat via een icoontje, een stoplicht, een smiley, een uitslaande meter, of vergelijkbare grafische indicatoren. Is bijvoorbeeld het ziekteverzuim groter dan de doelstelling, dan staat het stoplicht op het dashboard van de HRM-manager op rood. Extra bij een dashboard hoort vaak dat de gebruiker kan klikken om meer detailinformatie te krijgen, bijvoorbeeld het verzuim per afdeling, per dag. Rapporteren versus analyseren Zowel op tactisch als strategisch niveau is het analyseren van processen met behulp van gegevens ook vaak van belang. We moeten ons afvragen of rapporten geschikte tools zijn om te analyseren. Analyseren is iets dynamisch en rapporten zijn vrij sta-
10
Hoofdstuk 1 – Inleiding informatiemanagement
tisch, zelfs als ze veel interactieve elementen zoals drill-down bevatten. Drill-down betekent zoveel als doorklikken naar meer gedetailleerde informatie. Rapporten zijn vooraf gemaakt, met een vooraf gedefinieerd doel. Analyseren kan je brengen op nieuwe terreinen, bij nog niet eerder gestelde vragen en dus bij informatie die nog niet is verwerkt in standaard rapporten. Maar met grafieken en draaitabellen kunnen we al een aardige stap maken. We zouden in een brainstormsessie willekeurige vragen moeten kunnen stellen (‘out of the box’ denken) en meteen de antwoorden moeten kunnen zien. Dat is waar analyseren echt begint en waar we voorbij het punt komen van wat rapporten te bieden hebben. Wie is de gebruiker? Een rapportagesysteem dat theoretisch perfect is maar niet gebruikt wordt, is een slecht systeem. De mensen moeten de rapporten wel gebruiken. Ten eerste moet de informatie op de rapporten dus kloppen, betrouwbaar zijn. Daarnaast moeten de mensen er prettig mee werken. Helaas is dat een subjectief verhaal. Neem een wat oudere directeur die nog net heeft leren e-mailen, maar voor alle overige computergerelateerde zaken afhankelijk is van zijn of haar secretaresse. Waarschijnlijk is het beste rapport in dit geval een rapport in PDF-formaat dat reeds door iemand anders is geprint. Maar iemand met veel meer computervaardigheden en met misschien zelfs een technische achtergrond en inzicht in de beschikbare ge gevens, neemt niet zomaar genoegen met een papieren uitdraai. Een rapport in Excel waar hij zelf zijn eigen filtering, sortering en zelfs bewerkingen aan kan toevoegen, is beter geschikt. Sommige bedrijven gebruiken Excel juist niet vanwege alle bewerkingen die mensen er dan nog mee kunnen doen. Dat komt de eenduidigheid en herleidbaarheid van de informatie namelijk niet ten goede. Met een beetje pech discussiëren we alsnog over welk rapport klopt, in plaats van het te kunnen hebben over door iedereen vertrouwde rapportages en daarmee over de inhoud. Wellicht is een rapport in HTML, dus op het scherm, met de juiste interactieve mogelijkheden wel een goede tussenoplossing. Het hangt allemaal af van de gebruiker. Dus is het kennen van de gebruiker en hoe een rapport gebruikt gaat worden een belangrijk aspect bij het ontwikkelen van rapporten. Analyse vooraf Samenvattend kunt u concluderen dat het heel belangrijk is om te weten met welk doel u een rapport gaat ontwikkelen en wie de gebruikers van het rapport zullen zijn. Welk proces moet ondersteund worden? Wie gaat er welke beslissingen op baseren? Hoe kunt u de benodigde kennis die een beslissing vergt het beste inzichtelijk maken? Wat zijn de vaardigheden van de gebruikers? Op basis van deze vragen moet, samen met de beoogde gebruikers, een rapportopzet gemaakt worden. U kunt sturen, en via prototypes mogelijkheden aandragen. De gebruiker moet het accepteren, de meerwaarde zien, en het rapport uiteindelijk gebruiken.
11
Aan de slag met Reporting Services 2012
1.6 OLAP-kubus Een belangrijke vraag die bij het maken van een rapport beantwoord moet worden, is: waar zullen de gegevens vandaan komen? Tegenwoordig wordt veel gebruikgemaakt van OLAP-kubussen, en dus is er een goede kans dat SQL Server Analysis Services uw bron wordt. Analysis Services is de Microsoft-tool om kubussen te maken en te beheren. De afkorting OLAP staat voor Online Analytical Processing. Hier komen we het woord analyse weer tegen. En Analysis Services heeft zijn naam ook niet voor niets gekregen. In de vorige paragraaf is al gezegd dat rapporteren eigenlijk iets vrij statisch is, terwijl analyseren juist dynamisch moet zijn. Willekeurige vragen kunnen stellen en razendsnel antwoorden krijgen (‘free form questions, answers at the speed of thought’). Met een relationeel datawarehouse gaat dat zeker niet lukken. Voordat zelfs een ervaren SQL-programmeur de query heeft geschreven (of bij elkaar geklikt) is er al zo veel tijd verstreken dat we allang niet meer van razendsnel kunnen spreken. En dan moet de query nog uitgevoerd worden. Kubussen moeten aan dit probleem tegemoet komen. Excel on steroïds Een kubus zouden we kunnen zien als een grote, meerdimensionale draaitabel. Een gewone draaitabel bestaat uit twee assen en de snijpunten (cellen) van die assen. Op de ene as staan bijvoorbeeld alle producten die verkocht worden, op de andere as staan alle maanden van het jaar en in de cellen (elke cel is het snijpunt van een product met een maand) staat het aantal dat van het betreffende product in die maand is verkocht. Draaitabellen vormen een belangrijke reden waarom Excel zo populair is geworden. Een kubus kan je zien als ‘Excel on steroïds’. Het is een draaitabel zoals in Excel, maar dan niet beperkt tot twee assen, maar met net zo veel assen als u nodig heeft.
Figuur 1.3 Draaitabel in Excel met omzet per productcategorie per jaar
Denk nog eens terug aan het eerder besproken stermodel. Een stermodel bestaat uit een feitentabel met daarin voornamelijk numerieke zaken, zoals aantallen, omzet, marge, et cetera, en dimensietabellen met beschrijvende zaken die context (betekenis) geven aan de feiten. Dit sluit naadloos aan bij een kubus. De assen van de kubus zijn de dimensies, de cellen vullen we met de feiten. Omdat er meer dan twee dimensietabellen voorkomen in een stermodel, hebben we ook meer dimensies in een kubus. Kubussen worden dan ook wel ‘multidimensionele databases’ genoemd.
12
Hoofdstuk 1 – Inleiding informatiemanagement
MDX Een tweede reden waarom Excel zo populair is, naast de draaitabelfunctionaliteit, is de rekenkracht. Met formules maakt u gemakkelijk berekende velden. Voor kubussen geldt weer hetzelfde. Zoals de taal SQL bij relationele databases hoort, zo hoort de taal MDX (Multi Dimensional eXpressions) bij kubussen. Met MDX zijn we in staat om relatief eenvoudig, complexe berekeningen te maken. Soms is een berekening in MDX gewoon eenvoudiger dan het programmeren van dezelfde berekening in SQL. Sommige berekeningen zijn in SQL helemaal niet mogelijk, maar wel in MDX. MDX heeft onder andere een rijke verzameling aan statistische en financiële functies die we kunnen gebruiken. En het resultaat van deze berekeningen kunnen we gebruiken om cellen te vullen en dus om de gegevens in de kubus te verrijken. Slice and dice Een uitdrukking die hoort bij kubussen, en die goed aansluit bij wat analyseren is, is ‘to slice and dice’. Dat vertaalt zich misschien het beste naar willekeurig door je gegevens browsen. ‘To slice’ betekent een ‘plakje’ uit je kubus snijden, oftewel een filter definiëren. Als we bijvoorbeeld geïnteresseerd zijn in omzetgegevens over 2012, dan zetten we een filter op jaartal. Alles wat vanaf nu uit de kubus komt, heeft alleen nog maar betrekking op 2012. Als we dus nu de lijst van producten met bijbehorende omzet opvragen, krijgen we alleen producten te zien die in 2012 verkocht zijn, met de bijbehorende omzet. ‘To dice’ betekent dat je willekeurige cellen in de kubus kunt benaderen door op de dimensies de juiste elementen (dimension members) te selecteren. Al met al kunnen we dus als het ware real-time door de gegevens in de kubus wandelen. Dat wil zeggen, als we de juiste tools hebben. Excel is zo’n tool. Het is een geweldige analyseomgeving die goed aansluit op kubussen (met name SQL Server Analysis Services). Reporting Services is goed in staat gegevens uit een kubus te halen en daar een rapport van te maken. Reporting Services behandelt de gegevens echter precies zoals het gegevens uit een relationele database behandelt. Het vuurt de query af op de database, in het geval van een kubus dus een MDX-query, en toont de gegevens in het formaat zoals de ontwikkelaar van het rapport heeft bedacht. Het interactieve element van willekeurig door je gegevens wandelen, is daarmee grotendeels weg. 1.6.1
Voordelen van kubussen
Zoals hierboven beschreven sluiten kubussen goed aan op het begrip analyseren. Een kubus is als het ware een grote, krachtige spreadsheet met meer dimensies en een krachtige expressietaal. Maar er zijn meer voordelen. Het automatisch laten genereren van queries gaat beter dan bij relationele databases en de performance is (vaak) beter. Genereren van queries Onafhankelijk van het soort database moet er een query naar de database gestuurd worden om gegevens terug te krijgen. Voor een kubus is dat een MDX-query, voor een relationele databases is dat een SQL-query. Veel tools kunnen queries genereren. Daarnaast zijn er grafische tools die ons helpen queries in elkaar te slepen, in plaats
13
Aan de slag met Reporting Services 2012
van ze zelf te moeten schrijven. Voor beide geldt over het algemeen dat deze tools beter zijn met kubussen dan met relationele databases. Tools die queries maken, of die ons helpen queries te maken, zijn afhankelijk van het ontwerp van de database. Als in een relationele database geen relaties tussen tabellen zijn gedefinieerd (foreign keys), kunnen ze vaak niet de juiste query maken. Als dat wel lukt, is het nog maar de vraag of het ook een query is die goede performance zal laten zien. Bij het ontwerpen van kubussen wordt rekening gehouden met de gebruiker. Relaties tussen feiten en dimensies worden vastgelegd. Daardoor zijn tools veel beter in staat goede MDX-queries te genereren dan dat ze SQL-queries kunnen genereren. Dat houdt op zijn beurt in dat iemand met weinig kennis van de querytaal, maar met goede tools, met kubussen verder komt dan met relationele databases. Performance Kubussen hebben naast dat ze dynamisch analyseren faciliteren nog een ander voordeel. Bij veel analyses en rapportages is er sprake van een hoog aggregatieniveau. Om de dagomzet van een supermarkt te berekenen, moeten duizenden kassabonregels worden opgeteld. Om de landelijke dagomzet van de hele supermarktketen te berekenen of om de weekomzet te berekenen, wordt dat nog vele malen meer. Door die grote hoeveelheid data is dit soort operaties ogenblikkelijk duur, waarbij ik met duur denk aan performance. Rapporten die dit soort feiten tonen, zullen nooit een heel goede performance laten zien. Om de performance van rapporten die hooggeaggregeerde data tonen te verbeteren, wordt binnen datawarehouses vaak gebruikgemaakt van aggregatietabellen. Dat houdt in dat de dagomzet of weekomzet (of misschien wel beide) van de supermarkt ’s nachts berekend wordt. Van het resultaat van de berekening wordt een aparte tabel gemaakt. Het rapport kunnen we nu baseren op deze aggregatietabel. Daardoor is de hoeveelheid data die het rapport opvraagt veel kleiner en dat levert veel performancewinst op. Bovendien hoeft er niet meer gerekend te worden aan de data zoals die uit de database komt, wat nog eens veel performancewinst oplevert. Dubbele winst dus. Het nadeel van deze truc (de aggregatietabel) zit hem voornamelijk in de afhankelijkheid van het rapport met de aggregatietabel. De persoon die de query schrijft, moet op de hoogte zijn van het bestaan van de tabel en deze gebruiken, in plaats van de nietgeaggregeerde feitentabel te gebruiken. Een rapport met een net iets andere insteek kan misschien wel, maar misschien ook niet profiteren van dezelfde aggregatietabel. Maandomzetten zijn immers niet te berekenen uit weekomzetten, omdat een nieuwe maand halverwege een week kan beginnen. Dus het rapport dat maandomzetten toont kan geen gebruikmaken van de aggregatietabel met weekomzetten. Bovendien maakt een rapport niet automatisch gebruik van de juiste aggregatietabel. De query die de basis vormt van het rapport is hardcoded. Als we een rapport sneller willen maken door een aggregatietabel die aansluit bij het rapport alvast vooraf te maken, moet het rapport ook aangepast worden.
14
Hoofdstuk 1 – Inleiding informatiemanagement
Bovenstaand probleem wordt, als een kubus goed ontworpen is, door kubussen opgelost. De oplossing is eigenlijk hetzelfde als wat hierboven beschreven is, namelijk vooraf al aggregaties berekenen en deze in de kubus opnemen. Tijdens het uitvragen van de kubus zijn daardoor minder berekeningen nodig waardoor we een betere performance mogen verwachten. Het grote verschil met aggregatietabellen in een relationele database is dat de ‘slimheid’ om deze aggregaties ook daadwerkelijk te gebruiken is ingebakken in de queryengine. Als we met MDX een geaggregeerde waarde opvragen uit de kubus, kijkt de kubus automatisch of er een aggregatie aanwezig is. De waarde wordt alleen berekend als er geen aggregatie aanwezig is. Als het goed is, gebruikt de kubus zelfs aggregaties om aggregaties op een hoger niveau te berekenen. Stel: er zijn aggregaties gemaakt voor maandomzetten en er wordt in een query om kwartaalomzetten gevraagd. In dat geval zal de kubus herkennen dat een kwartaal bestaat uit drie maanden, de drie betreffende maandomzetten ophalen, optellen, en het resultaat teruggeven. Het mooie hiervan is dat de query precies hetzelfde blijft. Als we dus constateren dat een rapport traag is omdat de onderliggende query geen gebruikmaakt van aggregaties, kunnen we de kubus aanpassen, zodat de juiste aggregaties in de kubus aangemaakt worden en het rapport zal deze automatisch gaan gebruiken. Samenvattend zijn er twee redenen waarom we rapporten zouden willen baseren op een kubus. Allereerst is dat de extra functionaliteit die wellicht al in de kubus zit. Met MDX kan de kubus verrijkt zijn met complexe berekeningen. Vanuit het perspectief van het rapport zijn die berekeningen gewoon cellen (velden) die we kunnen gebruiken zonder zelf complexe (en daarmee waarschijnlijk trage) queries te schrijven. Daarnaast biedt een kubus vaak voor vergelijkbare queries een betere performance, zeker als de queries niet om detailgegevens vragen maar geaggregeerde data opvragen. 1.6.2
Tabular Model
Met SQL Server 2012 heeft Microsoft naast de boven beschreven kubussen ook de mogelijkheid gemaakt van Tabular Models. Tabular Models bieden ook goede performance, maar halen dat grotendeels uit het feit dat het in-memory databases zijn. Tabular Models staan een beetje op dezelfde plaats in een BI-architectuur, tussen het datawarehouse en de rapportagetools in. Ze moeten het makkelijker maken de data in het datawarehouse te ontsluiten. U leert meer over Tabular Models in Hoofdstuk 6.
1.7
Ontstaan SQL Server Reporting Services
In 2004 kwam Microsoft SQL Server Reporting Services op de markt. In 2012 kwam Microsoft SQL Server 2012 op de markt. Nieuw in deze versie is Power View, een eenvoudig te gebruiken rapportagetool. Reporting Services heeft in eerdere versies als kritiek gekregen dat het vooral een tool voor techneuten was, ondanks het feit dat er verschillende tools meegeleverd werden voor verschillende doelgroepen.
15
Aan de slag met Reporting Services 2012
Visual Studio Reporting Services was flexibel van opzet, maar de rapporten moesten gemaakt worden in Visual Studio, dezelfde ontwikkelomgeving waarin ook websites en bijvoorbeeld Visual Basic-applicaties worden gemaakt. Dat was geen probleem voor ontwikkelaars, maar voor sommige gebruikers bleek dat een te moeilijke omgeving. En in de praktijk worden rapporten vaak gemaakt door de eindgebruikers van die rapporten en dus niet door ontwikkelaars. Report Builder Als alternatief voor Visual Studio was er de Report Builder. Dit was een eenvoudige drag-and-drop tool om rapporten mee te maken. Een eis voor het gebruik van de Report Builder was dat er een zogenaamd Report Model bestond. Een Report Model is een semantische laag. Semantisch komt van het woord ‘semantiek’, dat betekenis betekent. Het model heeft als belangrijkste doel een abstractielaag te vormen tussen de database en de rapportbouwer. Het idee is dat de rapportbouwer geen technische kennis nodig heeft van de onderliggende database en niet zelf queries hoeft te schrijven. Het model ‘vertaalt’ de technische aspecten naar businesstermen en genereert de benodigde queries. In theorie allemaal erg mooi, maar de functionaliteit van de rapporten bleef ver, te ver, achter bij wat met Visual Studio gemaakt kon worden. Report Builder 2.0 / 3.0 Als antwoord op de kritiek dat de functionaliteit tekortschoot, kwam Microsoft met Report Builder 2.0 en later met Report Builder 3.0. Nog steeds was het mogelijk rapporten te baseren op Report Models, maar het was geen vereiste meer. Daarnaast nam het aantal mogelijkheden van de rapporten sterk toe. Helaas is het in de praktijk meestal zo dat als een product meer mogelijkheden krijgt, de ontwikkelomgeving ook weer ingewikkelder wordt. En dus schoot Microsoft met de Report Builder 3.0 zijn doel voorbij: voor de doelgroep waarvoor deze tool was ontwikkeld, was hij ondertussen toch weer te complex geworden. En de mensen waarvoor dat niet geldt, kunnen net zo goed met Visual Studio werken om op die manier de volledige functionaliteit te hebben. Power View Power View is nu het antwoord van Microsoft. Power View beoogt net als de eerste Report Builder een eenvoudige tool te zijn waarmee iedereen die een beetje met een computer overweg kan, rapporten kan bouwen. Veel van de ondertussen vertrouwde functionaliteit van Reporting Services vinden we er niet in terug. En van veel van die zaken is het nog maar de vraag of we die ooit gaan krijgen. Microsoft wil niet weer de fout maken van Report Builder 3.0. Bij Power View voelt het echter niet als beperking. Het biedt mooie functionaliteit en heeft een ander doel dan rapporten gemaakt met Visual Studio. Er is een belangrijke overeenkomst met de eerste Report Builder: rapporten die u maakt met Power View zijn gebaseerd op een model. Voor Power View is dat Analysis Services Tabular Model, oftewel het Business Intelligence Semantic Model. Dit borduurt voort op de introductie van PowerPivot in Excel en wordt gebouwd in SQL Server Analysis Services 2012. Meer hierover leest u in Hoofdstuk 6 en 7.
16
Hoofdstuk 1 – Inleiding informatiemanagement
1.8
Tot slot
SQL Server Reporting Services is een tool om gegevens uit databases te tonen aan gebruikers. Gebruikers kunnen hier beslissingen op baseren, ze kunnen werken met informatie. Reporting Services kan gebruikt worden om operationele rapporten op productiedatabases te maken, maar ook om strategische rapportages te maken die op grote datawarehouses gebaseerd zijn. Reporting Services komt met een paar tools. Uiteraard zijn er de tools om de rapporten mee te maken, met verschillende tools voor mensen met verschillende achtergronden. Ook zijn er tools om een rapportageomgeving mee te beheren. De rest van dit boek richt zich op wat Reporting Services te bieden heeft en hoe u Reporting Services kunt gebruiken. In het volgende hoofdstuk leert u eerst hoe Reporting Services te installeren. Hoofdstuk 3 en 4 gaan over het maken van rapporten in SQL Server Data Tools, oftewel Visual Studio. Hoofdstuk 5 richt zich op het beheer van de rapporten die u in Hoofdstuk 3 en 4 heeft gemaakt. In Hoofdstuk 6 gaat u een Business Intelligence Semantic Model maken om daar in het laatste hoofdstuk, Hoofdstuk 7, met Power View rapporten op te baseren.
17
Index A Action 100, 109 Advanced Mode 63, 99 Afdelings-BI 147 aggregate functies 74 aggregatietabel 14 Allow Multiple Values 90 B Background color 63 bar chart 106 Bookmark 100 Browser 141 Bubble Chart 188 Business Intelligence 2, 8 C Cache Refresh Options 130 cache warming 137, 139 caching 94, 127 Calculated Field 77 Card 186 CDATE 86 Chart Data Region 57, 106 color 63 Column Groups 72 CONCATENATE 156 conditionele opmaak 69 connectiestring 45 Corporate BI 145 D dashboard 10 Data Alerts 143 Data Bar 110, 111 Data-driven Subscriptions 135 Data Region 57 Dataset 47 Shared Dataset 47 DataSetName 59 Data Source 45 Shared Data Sources 45
datawarehouse 4 DATEADD 166 datumtabel 165 DAX 155 Default Field Set 167 Default Image 168 default instance 23 Default Label 168 deployen 119 diagramweergave 158 dimensies 6 DirectQuery 171 Document Map 96 double-hop 126 drill-down 97 drill-through 97, 101, 108 Duplicate View 185 E E-mail Settings 32 Embedded Connection 50 Encryption Keys 32 ExecutionTime 67 exporteren naar PowerPoint 182 Expression Builder 66 F feitentabel 6 Fields-collectie 76 filteren 84 filters 181 footers 64 friendly name 149 G Gauges 110, 115 Globals 67 Globals-collectie 76 Groepen 72 group footer 73 group header 73
H headers 64 HideDuplicates 72 Hide from client tools 164 hiërarchie 158 I IF 166 IIF 69 Image 64 Impersonate 126 Indicator 110, 113 interactive page size 115 intermediate report 128 Internal 89 K Keep Unique Rows 167 Kerberos 126 KPI 10, 110, 113, 169 kubus 12, 160 L Label Field 89 Language 60, 62 Layout-toolbar 66 lay-outvenster 178 Linked Reports 134 List Data Region 57, 103 Lookup 102, 114 LookupSet 102 M Manage Cache Refresh Plans 130 managed service account 26 Manage Parameters 134 Manage Permissions 143 Manage Processing Options 131, 132 Manage Subscriptions 137, 139 Map 110 213
Aan de slag met Reporting Services 2012
Matrix Data Region 57, 107 MDX 13 multidimensionele database 12 Multiples 189 N naamgevingsconventie 45 named instance 23 New Project... 43 NoRowsMessage 87 Now 86 Null Delivery 137 O OLAP 1, 12 OLTP 1 OverwriteDatasets 121 OverwriteDataSources 121 P page breaks 75 page headers en footers 64 PageNumber 68 page size 115 paginanummers 68 parameter 84 Defaultwaarde 86 Parameters collectie 85 Personal BI 145, 147 Perspectives 171 pie chart 106 Placeholders 67 Play Axis 189 Pop out 181 PowerPivot Gallery 39, 175 PowerPivot-veldenlijst 151 Power View 16 Preview 59 Processing Options 129 Property venster 59 publiceren 119 pull delivery 135 push delivery 135 Q Query Designer 54 214
R RDL 50 regional settings 61 RELATED 155 renderformaten 115 Report Builder 16 Report Builder 3.0 39 Report Data-venster 50 Report History 132 Reporting Services Configuration Manager 27 ReportItems-collectie 75 Report Manager 22 ReportName 67 Report Parameter Properties 85 Report Server Project 44 Report Server Project Wizard 44 Row Groups 72 Row Identifier 167 RowNumber 70 rs.exe 123 rsreportserver.config 33 RunningValue 76 S Scatter Chart 187 scorecard 113 security 140 Selfservice BI 145, 147 service account 26 SharePoint Central Administration 35 slicers 152, 183 Snapshot 130 Snapshot Options 132 Solution Explorer 44 Sorteren op kolom 156 sortering 71 Sparkline 110 SQL Server Data Tools 25 SQL Server Installation Center 34, 35 staafdiagram 106 static members 99 stermodel 5
Stored Procedure 53 Sub Report 107 Subscriptions 135 SUM 164 Switch 70 T taartdiagram 106 Table Behaviour 167, 169 Table Data Region 57 Tablix 58 Tabular Model 161, 162 TargetDatasetFolder 120 TargetDataSourceFolder 120 TargetReportFolder 120 TargetReportPartFolder 120 TargetServerURL 119 templates 102 textbox 59 Tiles 185 time-outs 133 ToggleItem 98 toolbox 57 TotalPages 68 TOTALYTD 165 trends 110 U UserSort 72 V Value Field 89 valuta 62 veldenlijst 153 Verbergen voor clienthulpprogramma’s 153 Views 54, 185 Visibility 98 W Web Service URL 30 Windows Authentication 126 Y Year 86
Over de auteur Peter ter Braake is zelfstandig SQL Server docent/consultant. Hij is MCT sinds 2002 en SQL Server MVP sinds begin 2012. Hij werkt met SQL Server Reporting Services sinds de eerste release in 2004.
978 90 395 2721 4 123
9 *uklpdo#bxmxyv*
Aan de slag met Reporting Services 2012
Ook alle instellingen die u kunt kiezen voor rapporten nadat ze in gebruik zijn gekomen, komen aan de orde. Dankzij de opgaven biedt het boek voldoende mogelijkheden om te oefenen en ervaring op te doen. Zo kunt u de behandelde stof direct in de praktijk toepassen. Aan de slag met Reporting Services 2012 is bedoeld voor studenten en professionals die met Reporting Services gaan werken. Voorkennis van Reporting Services is niet noodzakelijk, maar kennis van het Microsoft-platform is een pre. Daarnaast is het in de praktijk onontbeerlijk om enige kennis te hebben van SQL of als u met kubussen wilt werken, van MDX.
ter Braake
Microsoft SQL Server 2012 Reporting Services is een uitgebreide tool om rapporten mee te maken, te beheren en te gebruiken. Het biedt vele mogelijkheden en opties en is bruikbaar in zowel de kleinste, alsook de grootste en meest kritische omgevingen. De beste manier om een uitgebreid product zoals Reporting Services te leren kennen is er actief mee te werken. Dit boek is geschreven om u daarbij te helpen. Stap voor stap wordt u begeleid bij het installeren van Reporting Services en vervolgens bij het maken van rapporten. Bij alle keuzes wordt stilgestaan bij de mogelijke opties en wanneer te kiezen voor welke optie. Zo leert u al doende goede en duidelijk rapporten te bouwen en ze te gebruiken.
Aan de slag met Reporting Services 2012 voor Microsoft SQL Server Peter ter Braake
