Microsoft BI: Introductie van SQL Server Analysis Services 2005

Business Intelligence (BI) is een van de speerpunten van Microsoft. Met de introductie van SQL Server 7 betrad Microsoft de markt van de data-analyse. Met de nieuwe versie van SQL Server (2005) heeft Microsoft de hele business intelligence stack geredesigned. Er zijn modules voor data-integratie, rapportages, data-warehousing, data-analyse en data-mining.

In dit artikel ga ik dieper in op Microsoft SQL Server Analysis Services (SSAS), de component die gebruikt wordt voor het maken van een analysemodel; de basis voor een gedegen, snelle data-analyse.

Ik zal niet proberen om alle mogelijkheden van Analysis Services volledig uit te diepen, maar zie dit artikel als een overzicht van de belangrijkste eigenschappen en mogelijkheden van deze nieuwe SQL Server module. Achter aan het artikel staan links naar de resources die gebruikt zijn bij het schrijven en die je kunt gebruiken om zelf aan de slag te gaan.

Mensen zijn goed met informatie; computers zijn goed met data. De meeste computerprogramma’s die worden gebruikt in bedrijven, zijn ervoor om de informatie die de medewerkers verzamelen op te slaan als data. Die data wordt dan gebruikt om de bedrijfsprocessen uit te voeren. Hoewel mensen heel goed zijn in het interpreteren van informatie, zijn ze een stuk slechter in het interpreteren van data die in/op een computer opgeslagen is. Er is gewoon teveel data om een goed overzicht te kunnen krijgen.

Een manier om toch inzicht te krijgen is het maken van rapportages. Die methode wordt altijd al met veel succes toegepast. Maar er is een groot nadeel: om met een veranderende markt mee te kunnen gaan willen medewerkers vaak dingen weten die niet in de standaard rapporten staan. Ze willen relaties onderzoeken, etc. En het liefst zonder tussenkomst van een IT-er. Werknemers kennen de business.

Hoe fijn zou het zijn om vragen te kunnen stellen waarvan de antwoorden snel op het scherm verschijnen. En dan het antwoord kunnen verfijnen, of misschien nieuwe vragen stellen. Hoe doet Jan het en hoe goed is Piet? Aan welke klanten verdienen we het meest? Hoe groot is de kans dat een nieuwe klant ook werkelijk geld gaat opleveren; moeten we hier veel tijd en geld aan spenderen of doen we het met minimale inspanning? Hoe staat ons bedrijf ervoor?

Het is niet meer van deze tijd om één keer per jaar de balans op te maken. En aan de hand daarvan plannen dat het volgend jaar beter moet. Business Intelligence biedt de mogelijkheid om veel frequenter de business bij te sturen. Veel sneller te signaleren dat het niet gaat zoals verwacht.

Aan de slag

Om aan de gang te gaan met de Business Intelligence Stack van Microsoft heb je een installatie van SQL Server nodig. De standaard editie bevat de basisfunctionaliteit; de volledige functionaliteit is terug te vinden in de Enterprise-editie. Gelukkig is er ook een Developer-versie, met alle functionaliteit die in de Enterprise-versie zit. Je kunt bij Microsoft een gratis trial (180 dagen) downloaden. Als je dan ook nog de voorbeeld databases (AdventureWorks, zie referenties) en voorbeeld solutions installeert, ben je klaar om aan de slag te gaan.

Met de installatie wordt de Business Intelligence Development Studio geïnstalleerd. De Business Intelligence Studio is een ontwikkelomgeving gebouwd op basis van Visual Studio 2005 voor SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS) en SQL Server Reporting Services (SSRS).

In het vervolg van dit artikel zal ik je door de belangrijkste componenten van SSAS leiden.

Unified Dimensional Model

Het Unified Dimensional Model (UDM) is de naam die Microsoft gebruikt voor de verzameling datasource-views en datakubussen van een Analysis Server project.

Het UDM vormt de basis voor analyse (Online Analytical Processing, OLAP), rapportage en datamining van de informatie in het datawarehouse. SSAS is eigenlijk niets anders dan een service die snel toegang biedt tot informatie in een verzameling datakubussen.

In figuur 1 zie je een voorbeeld van een datakubus. In deze tekening zie je drie dimensies. Dit is het maximum aantal dat op deze manier kan worden getekend, maar in de praktijk zit er geen limiet aan het aantal dimensies of het aantal feiten. Wat dimensies en feiten zijn en wat je er allemaal mee kan doen zal ik in de volgende alinea’s uitleggen.

Fig. 1: Een kubus met drie dimensies (werknemer, product en tijd) langs de assen.

In figuur 1 bevat elke cel een of meerdere feiten, in dit geval het aantal producten dat door een werknemer op een bepaald tijdstip is verkocht. Het donker gearceerde blok is de aggregatie van alle verkopen die een bepaalde werknemer ooit heeft gemaakt.

Maar eerst een stapje terug. De data uit de dimensionele database (AdventureWorksDW) vormt de basis voor de datakubussen.

Dimensioneel modelleren

Dimensionele databases (datawarehouses) worden gewoon opgeslagen in een relationele database. Het verschil met standaard databases is de structuur. Het is geen (uit)genormaliseerde database. Een beetje vreemd want we hebben de afgelopen jaren geleerd dat normaliseren een soort heilige graal is. Maar er zijn ook nadelen. De belangrijkste hiervan is performance. Relationele database zijn geënt op het snel kunnen uitvoeren van transacties.

Voor data-analyse en rapportage is het nodig om heel veel data te bekijken. Dat betekent in een traditionele database heel veel queries, waarvoor vaak geen indexen aanwezig zijn. Maar wat voor voordeel biedt het dan om data op een andere manier op te slaan?

Een dimensionele database vormt de perfecte basis voor het maken van datakubussen met SSAS. SSAS op zijn beurt is geoptimaliseerd om de data snel toegankelijk te maken, op de manier die een gebruiker wil zien. Hoe? Door de data op te splitsen in feiten en dimensies.

De omzetting van de relationele data naar de dimensionele database wordt gedaan met een ETL (Extract-Transform-Load) proces. SQL Server Integration Services is heel geschikt om de ETL processen te ontwikkelen en uit te voeren. De uitleg van SSIS is al eens in een eerder artikel besproken en gaat buiten de scope van dit artikel. In de AdventureworksDW database is dit werk al voor je gedaan, zodat je direct aan de slag kunt met SSAS.

Fig. 2: Het datawarehouse wordt gevuld met SSIS via een ETL proces.

Dimensies

Dimensies bepalen de manier waarop we de informatie willen zien. Via de dimensies is het mogelijk de kubus op verschillende manieren te “slicen”.

In figuur 1 zie je 3 voorbeelden van dimensies (werknemer, product en tijd). Door deze dimensies te gebruiken is het mogelijk vragen te beantwoorden als:

• Hoeveel verkopen heeft Piet (werknemer) gemaakt? (1 dimensie)
• Hoeveel zadels (product) zijn er op 1 januari 2006 (tijd) verkocht? (2 dimensies)
• Hoeveel fietsen (product) zijn er op 1 januari 2006 (tijd) verkocht door Piet (werknemer)? (3 dimensies)

Dimensies zijn opgebouwd uit attributen, zoals productnaam, producttype, jaar, maand, dag, enz.  Attributen kunnen worden geordend in hiërarchieën. Een voorbeeld van zo’n hiërarchie is jaar/maand/dag. Door deze hiërarchie te gebruiken is het mogelijk om beginnend op jaarniveau in te zoomen op de data van een specifieke maand van dat jaar, tot zelfs een specifieke dag.

Feiten

Feiten zijn de data waarom het draait, de businesswaarden, zoals aantal verkochte producten, verkoopprijs, etc. In SSAS worden feiten “measures” (meetwaarden) genoemd.

De feiten worden in een feitentabel opgeslagen, met een verbinding met de bijbehorende dimensies. Alle feiten in een rij van de feitentabel moeten gelden voor alle bijbehorende dimensies. Dit wordt “grain” genoemd. Zo kan je bijvoorbeeld niet de verkoopcijfers van een product op een bepaalde dag samen met het aantal van die maand in een rij opslaan. Het is dan nodig 2 feitentabellen te maken.

Hoewel feiten van alle datatypen kunnen zijn, zijn numerieke, optelbare waarden het meest bruikbaar voor het slicen.

De data in de feiten tabellen worden door SSAS, waar mogelijk, automatisch samengevoegd voor de verschillende dimensies. Dit wordt aggregeren genoemd. Voorbeelden van aggregaties zijn:

• Sum
• Count
• Distinct Count
• Avarage
• Last child
• ...

En de lijst gaat nog een eindje door. Niet alle aggregatietypes worden door alle versies van SQL Server ondersteund, maar de Development-versie ondersteunt ze net als de Enterprise-versie allemaal. Voor meer details kun je op de Microsoft site kijken (zie referenties voor een link).

Fig. 3: De Business Intelligence Development Studio

In de Business Intelligence Development Studio worden datakubussen afgebeeld door blokdiagrammen. De feiten zijn te herkennen aan een gele balk, de dimensies aan een blauwe balk.

Met calculations kun je on-the-fly feiten maken, door andere feiten op een of andere manier te combineren. In figuur 4 zie je een voorbeeld. Door het combineren van andere feiten in een wiskundige formule kunnen nieuwe feiten gemaakt worden die de informatie tonen zoals een gebruiker ze verwacht.

Fig. 4: Het scherm van de Business Inteligence Studio waarmee calculations worden gedefinieerd.

Resultaten

We hebben nu een datakubus en wat nu?

Er zijn verschillende manieren om de gegevens uit SSAS te halen, in te zien en te gebruiken. Ik zal nu een aantal van de mogelijkheden bespreken. Elk heeft zijn eigen mogelijkheden, voordelen/nadelen en doelgroep.

MDX

Net zoals je met TSQL een relationele database op SQL Server kunt bevragen, kun je een Analysis Services database met MDX (Multidimensional Expressions) benaderen. Alhoewel MDX qua syntax wel op SQL lijkt, zijn de principes heel anders. Het gaat te diep voor dit artikel om dit allemaal uit te leggen, maar hier is een voorbeeld om je kennis te laten maken:

SELECT
  /* toon alle werknemers met sales quotum */
  Filter([Employee].[Employee Name].Members,
         [Measures].[Sales Quotas Count]>0) ON ROWS,
  /* toon sales quotum en werkelijke verkoopcijfers */
  {[Measures].[Sales Amount Quota],
   [Measures].[Total Sales Amount]} ON COLUMNS
/* gegevens worden uit tutorial-kubus gehaald */
FROM [Analysis Services Tutorial]

Listing 1: Voorbeeld MDX-query

MDX queries kan je ontwikkelen in SQL Server Management Studio. In listing 1 zie je een query die de verkoopgegevens van de verkopers bevraagt. Het doel is afgezet naast de (tot nu toe) behaalde waarden. Je kunt in de output (tabel 1) zien dat de totale (All Employees) verkoopdoelen zijn gehaald.

Tabel 1: Resultaat van de MDX-query uit listing 1

Datamining

Nu wordt het pas echt interessant. Kijk eens naar websites zoals Amazon.com. Als je een boek bekijkt, zie daar een lijstje staan met boeken die andere gebruikers hebben gekocht. Als je een bekende klant bent (al een aantal boeken hebt gekocht) wordt ook deze informatie gebruikt om je boeken voor te schotelen waar je waarschijnlijk ook in bent geïnteresseerd. Het gaat zelfs zo ver dat Amazon je maandelijks een mail stuurt met “aanbevelingen”. Natuurlijk is dit allemaal om de verkoop te bevorderen, maar het biedt ook meerwaarde aan mij (als klant).

Het bedrijf Netflix, een online videotheek, gebruikt datamining technieken om te bepalen welke films je leuk zult vinden op basis van waarderingen die je hebt gegeven over andere films die je hebt geleend. Dit bedrijf heeft een wedstrijd uitgeschreven om dit proces te verbeteren met als hoofdprijs een miljoen.

Dit alles en nog veel meer kun je bereiken door het juist toepassen van datamining-technieken. Er wordt standaard een hele verzameling technieken aangeboden:

• Classification
• Clustering
• Association
• Regression
• Forecasting
• Sequence analysis
• Deviation Analysis
• Neural network

Business scorecards

Met de installatie van de Microsoft Business Scorecard Manager (BSM) en Sharepoint Webservices is het mogelijk dashboard sites (figuur 5) te maken. Op deze sites kunnen de gegevens uit SSAS via scorecards en grafieken op eenvoudige wijze inzichtelijk gemaakt worden, zoals te zien in figuur 5.

Scorecards worden opgebouwd uit lijsten met Key Performance Indicators (KPI’s). Een KPI is een getal waarvan van te voren is bepaald hoe deze waarde zich over de tijd moet ontwikkelen. Een makkelijk voorbeeld is verkoopcijfers. Aan het begin van het jaar worden er targets opgesteld. Tijdens het jaar is het mogelijk om de verkoopcijfers tegen deze target-aarden af te zetten. Het verloop kan dan afgebeeld worden door iddel van plaatjes (groen rondje als alles er goed uitziet, rode ruit als we achterblijven op het target). Ook is het mogelijk om een trend te bepalen, zodat je in een opslag kan zien of het steeds beter gaat, of dat de verkopen stagneren.

De Business Scorecard Manager maakt gebruik van de Office Webcomponents. De Office Webcomponents zijn een verzameling webcomponenten om draaitabellen en grafieken op het internet te publiceren.

Fig. 5: Voorbeeld van een dashboard-pagina met een Business Scorecard

Integratie met Excel

Met Excel is het mogelijk een verbinding te maken met SSAS. Kies voor invoegen – pivot table (of pivot chart). Je kan nu op dezelfde manier slicen als in de Business Intelligence Development Studio.

Proclarity

Voor de werkelijke data-analyse freaks zijn er programma’s om diepe data-analyse uit te voeren. Microsoft heeft dit jaar ProClarity over genomen, en zal de producten van ProClarity volgend jaar uitbrengen onder eigen vlag.

Met ProClarity is het mogelijk om interactief de data te bekijken en te visualiseren. Ook is het mogelijk om dashboard-sites te maken die nog mooiere plaatjes geven en meer interactiviteit bieden dan mogelijk is met de Business Scorecard Manager, Excel en de Office Webcomponents.

Conclusie

Microsoft heeft ons, ontwikkelaars, een werkelijk prachtige toolkit gegeven om mooie applicaties mee te maken. De ontwikkelomgeving is geïntegreerd in Visual Studio en heel intuïtief in het gebruik. Zoals je gewend bent van Microsoft is het mogelijk om van alle kanten uitbreidingen en verbeteringen te maken voor jouw specifieke oplossingen.

Microsoft heeft in korte tijd een goede positie veroverd in de wereld van Business Intelligence, vooral als het gaat om de prijs-prestatie-verhouding.

Natuurlijk is er nog veel meer te vertellen, maar ik hoop dat ik je een goede indruk heb kunnen geven wat er allemaal mogelijk is met deze toolbox die standaard wordt meegeleverd met SQL Server 2005. Ik wens je net zoveel plezier als ik heb gehad bij het uitproberen wat er allemaal mogelijk is met SSAS.

Jouw ideeën bepalen wat er mogelijk is, Microsoft geeft je slechts een, heel uitgebreide, toegankelijke bouwdoos.

Nuttige informatie

Afkortingen

• UDM = Unified Dimensional Model
• SSAS = Microsoft SQL Server Analysis Services
• SSIS = Microsoft SQL Server Integration Services
• SSRS = Microsoft SQL Server Reporting Services
• MDX = Multidimensional Expressions
• OLAP = OnLine Analytical Processing
• ETL = Extract-TransForm-Load
• BI = Business Intelligence

Boeken

• Applied Microsoft Analysis Services 2005 – Teo Lechev
• The Microsoft Data Warehouse Toolkit – Joy Mundy, Andy Thornthwaite, Ralph Kimball
• MDX Solutions With Microsoft SQL Server Analysis Services – George Spofford

Referenties

• Microsoft SQL Server: Business Intelligence Solutions: http://www.Microsoft.com/SQL/solutions/bi/default.mspx
• SQL Server 2005  Trial Software: http://www.Microsoft.com/SQL/downloads/trial-software.mspx#EJB
• In dit artikel heb ik gebruik gemaakt van de AdventureWorks database. Je kunt deze database downloaden: SQL Server 2005 Samples and Sample Databases http://www.Microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en.
  Ook kun je hier helpfiles en tutorials vinden die dieper op de materie ingaan.
• Voor meer info over de achtergrond van dimensioneel modelleren kun je kijken op de site van Ralph Kimball: http://www.kimballgroup.com/