Microsoft
SQL Server 2005 Analysis Services: van gegevens- naar informatiebeheer
Nico
Jacobs, Wim Uyttersprot
Gegevensbanken zijn al jarenlang een vaste waarde in
zowat elk IT project. Naar mate de hoeveelheden gestockeerde data toenemen
worden taken zoals rapportering en analyse alsmaar belangrijker. Van daar dat
Microsoft in de nieuwe release van Microsoft SQL Server ook veel aandacht
besteed heeft aan de OLAP component: Analysis Services. In dit artikel
overlopen we de belangrijkste kenmerken van Microsoft SQL Server 2005 Analysis
Services, met de nadruk op de nieuwe aspecten.
|
MS SQL Server 2005 Analysis Services
|
|
Integratie in Microsoft Visual Studio
IntelliCube
Meerdere Fact-tabellen
XML representatie
Perspectives
Vertalingen
Proactive
caching
Key
Performance Indicator
Bijkomende
Data Mining algorithmen
|
Tabel 1: Overzicht van de nieuwe kenmerken
van Analysis Services 2005 die we in dit artikel bespreken.
SQL Server: meer dan
transacties alleen
Gegevensbanken zijn al jarenlang een vaste waarde in
zowat elk IT project. Hoewel gegevensbanken voornamelijk gebruikt worden voor
het verwerken van transacties, is de laatste jaren een sterke groei merkbaar in
applicaties die de gegevensbank gebruiken voor raporteringsdoeleinden of voor
analyse van de gegevens. Hoewel beide dezelfde gegevens als basis hebben, zijn
er toch essentiële verschillen tussen beide soorten toepassingen. Daarom zijn
er in de database wereld dan ook twee soorten gegevensbanken: OnLine
Transaction Processing (of OLTP) gegevensbanken, zoals SQL Server, en OnLine
Analytical Processing (of OLAP) gegevensbanken, zoals Analysis Services. Deze
laatsten focussen op het consumeren van gegevens, vaak geaggregeerde
waarden zoals som, gemiddelde,... . Vermits dit zowel data- als
reken-intensieve queries zijn, gaan OLAP gegevensbanken de gegevens op een
andere manier structureren, evenals bepaalde geaggregeerde waarden op voorhand
berekenen, zodat het bevragen van dergelijke gegevensbanken meer efficiënt
verloopt dan wanneer men de equivalente query aan een OLTP gegevensbank zou
stellen.
Microsoft heeft al jaren een OLAP component die samen
met SQL Server verscheept wordt: Analysis Services. Dit is een populair product
geworden bij OLAP gebruikers: De cijfers van OlapReport (http://www.olapreport.com/market.htm),
tonen aan dat Analysis Services 2000 (AS2K) veruit de meest gebruikte OLAP
engine is. Maar Microsoft beseft dat zelfs als je de eerste bent, je toch
laatst eindigt als je gaat stil staan. Microsoft Sql Server Analysis Services
2005 (AS2005) heeft dan ook verschillende grote stappen vooruit gezet in
vergelijking met de 2000 editie, en dit zowel op gebied van robuustheid,
performantie, functionaliteit als gebruiksvriendelijkheid. In dit artikel
overlopen we de belangrijkste nieuwigheden aan de hand van de voornaamste
stappen die gezet worden bij het opstellen en gebruiken van een OLAP
gegevensbank, zodat op deze manier dit artikel ook nuttig is voor mensen die
minder met OLAP vertrouwd zijn.
Van OLTP naar OLAP
Net zoals in AS2K begin je een OLAP gegevensbank door
gegevens te importeren vanuit een relationele gegevensbank. Zoals we
ondertussen van Microsoft gewoon zijn, kan je hierbij gebruik maken van eender
welke gegevensbron die OLE DB of ODBC ondersteunt. Afhankelijk van het aantal
gegevensbronnen en de kwaliteit van de gegevens, zullen er eerst stappen nodig
zijn om de gegevens foutenvrij te maken (data cleansing) en in een centrale
gegevensbank (data warehouse) te plaatsen. Hieruit worden dan de kubussen (cubes)
gecreëerd die door de verschillende applicaties kunnen gebruikt worden (we gaan
zo dadelijk in meer detail in op dit kernconcept). Dit wordt geïllustreerd in Figuur 1. Figuur 2 toont de Solution Explorer met daarin de
belangrijkste componenten van een Analysis Services 2005 project. Een nieuwe
term in AS2005 is de Data Source View. Hierbij selecteert de ontwerper
de tabellen uit de onderliggende OLTP gegevensbank, en beschikt over de optie
om gerelateerde tabellen automatisch mee te selecteren. Het resultaat hiervan
is een schema dat de basis zal vormen voor het opstellen van de kubussen.
Figuur 1: Gegevens worden eerst uit
verschillende bronnen samengebracht in een Data Warehouse, waaruit vervolgens
een of meerdere kubussen uit afgeleid worden. Applicaties queriën dan deze
kubus(sen).
Figuur 2: De belangrijkste aspecten van een Analysis
Services project
Kubus, de belangrijkste bouwsteen
Zodra de gegevens bekend zijn, kan de belangrijkste
stap gezet worden in de opbouw van de OLAP gegevensbank, namelijk het
definiëren van de relaties tussen de verschillende tabellen. Daar waar bij een
OLTP gegevensbank de relaties tussen de data opgegeven wordt at query-time,
worden deze bij een OLAP gegevensbank at-designtime vastgelegd; het is immers
deze extra kennis die de OLAP gegevensbank toelaat efficiënter queries te
beantwoorden. Net zoals in AS2K zijn de belangrijkste concepten measures
en dimensies. Measures zijn de velden waarover je informatie zoals
totalen, gemiddelden etc. wil bekomen, vergelijkbaar met de velden in een
SELECT statement. Dimensies bestaan uit de velden die je als
groepeercriterium wilt gebruiken, equivalent met de velden die je in een GROUP
BY statement gebruikt. Wanneer je bijvoorbeeld geïntereseerd bent in
verkoopscijfers en verkoopsaantallen over product, regio en/of tijd vormen
verkoopscijfers en verkoopsaantallen je measures, terwijl product, regio en
tijd velden in je dimensies vormen.
Op dit punt zijn er vele belangrijke nieuwigheden. Om te beginnen wordt de taak van het opstellen
van het schema van de OLAP gegevensbank (d.i. het vastleggen van measures,
dimensies en hun onderlinge relaties) geautomatiseerd: De OLAP engine
analyseert de gegevens in de onderliggende OLTP gegevensbank en extraheert
hieruit eigenschappen van deze velden evenals relaties tussen deze velden. Op
basis hiervan worden de beschikbare velden opgesplitst in measures en
dimensies. Een dimensie (of beter: een hierarchie binnenin een dimensie) kan
uit meerdere niveaus bestaan; bijvoorbeeld een regio dimensie kan uit een stad,
een provicie en een land niveau bestaan. AS2005 detecteert deze
afhankelijkheden automatisch en plaatst dergelijke velden op de juiste manier
in een hierarchie (IntelliCube).
Natuurlijk kan je het resulterende schema nog manueel aanpassen, of het
volledige design handmatig maken.
In het verleden waren er beperkingen op de structuur
van de tabellen waaruit deze dimensies werden afgeleid: er kon maar een tabel
zijn die de measure-velden bevat, en de relaties tussen deze ‘facttable’ en de
dimensie-velden was beperkt tot de zogenaamde sterschemas en sneeuwvlokschemas.
AS2005 laat echter toe om meerdere facttables te gebruiken in het
schema, en kan alle informatie aan die in de derde normaalvorm gerepresenteerd
is (zie Figuur
3). Ook is het vanaf nu mogelijk om in een hierarchie
niet rechtstreeks gerelateerde velden op te nemen. Een belangrijke stap op
gebied van stabiliteit is dat erg grote dimensies nu geen probleem meer vormen
voor de Multidimensionele OLAP gegevensbank (MOLAP), dit vormt dus geen reden
meer om naar de minder performante Relationele OLAP (ROLAP) over te stappen
(zie de sectie over proactive caching voor meer informatie over deze twee
manieren van data-opslag). Dit alles laat toe een bredere waaier van gegevens
op te nemen in je OLAP gegevensbank.
Figuur 3: Fragment van een schema met een
derde normaalvorm (3NF): de structuur van de onderliggende gegevens kan vanaf Analysis
Services 2005 complexer zijn dan de klassieke ster en sneeuwvlok schemas.
Integratie in Visual Studio
2005
Het opstellen van deze schemas gebeurt ook op een meer
gebruiksvriendelijke manier. Om te beginnen is de functionaliteit van de
Analysis Manager en andere tools van AS2K nu geïntegreerd in de BI
Development Studio in Microsoft Visual
Studio 2005, zodat ontwikkeling voor Analysis Services in dezelfde
vertrouwde omgeving kan gebeuren als ontwikkeling van windows of web-applicaties
(zie Figuur 1). Dit past in de doorgedreven IDE vereenvoudiging;
immers de SQL Server OLTP functionaliteit wordt ook in een omgeving gebundeld,
namelijk de SQL Management Studio. In AS2005 is er ook geen permanente
verbinding meer nodig met de onderliggende OLTP gegevensbank: Enkel bij de
start van de schema-creatie en bij het automatisch opstellen van dimensies is
er nog OLTP-toegang nodig, verder gebeurt het ontwikkelen van de schemas
volledig asynchroon. Een ander belangrijk verschil met de vorige versie
van Analysis Services is dat het resultaat van de ontwikkeling van de OLAP
gegevensbank een XML bestand is (zie
Tabel
2). Dat betekent dat het erg gemakkelijk wordt om een
OLAP gegevensbank programmatorisch te gaan ontwikkelen. Zoals we ook van andere
producten gewoon zijn, kunnen we vanuit Visual Studio de ontwikkelde
gegevensbank ook gaan deployen: het XML bestand wordt dan naar de Analysis
Services gestuurd, waar de nodige data uit de onderliggende gegevensbank
opgehaald wordt, en de eigenlijke OLAP gegevensbank geconstrueerd wordt.
Gedetailleerde informatie over dit deployment process wordt hiërarchisch weergegeven
(Figuur
5). Wat hier een beetje ontbreekt is een globale
indicator die aangeeft welk percentage van het deployment process al afgerond
is.
Figuur 4 : Het aanmaken,
deployen en browsen van een OLAP cube is geïntegreerd in Visual Studio
Figuur 5 : Fragment uit de XML representatie
van een kubus
Figuur 6 : Informatie over
het deployment process wordt in Visual Studio weergegeven.
Perspectives
Een vaak voorkomend probleem bij het opstellen van een
OLAP schema is dat er vele dimensies en measures zijn die potentieel nuttig
zijn. Wanneer deze allemaal in de OLAP gegevensbank opgenomen worden, kan dit
overweldigend zijn voor de analyst of de persoon die de rapporten ontwerpt: deze
gaan meestal een of meerdere pivot tabellen opstellen, waarbij ze measures in
de datagrid plaatsen en een of meerdere dimensies op de assen van de tabel. Als
OLAP-ontwikkelaars kunnen we het deze gebruikers gemakkelijker maken door een perspective
aan te maken. Een perspective is een kijk op een kubus: het definieert de
subset van measures en dimensies die aan een gebruiker gepresenteerd worden
wanneer deze de gegevensbank gaat queriën. Op deze manier kunnen we op een
kubus verschillende perspectives baseren, die toelaten dat verschillende
gebruikersgroepen elk hun eigen kijk op de kubus hebben. Vermits deze
perspectives direct op de kubus gemapt worden, heeft het aanmaken van deze
perspectives geen invloed op de opslag noch op de verwerkingstijd van de kubus.
Let wel op, dit mechanisme is niet bedoeld als een beveiligingsmechanisme.
Wanneer je bepaalde gebruikers de toegang tot bepaalde velden wil ontzeggen
moet je hiervoor gebruik maken van het rol-gebaseerde veiligheidsmodel op de
onderliggende kubus, zoals dit al in AS2K het geval was.
Vertalingen
In een veeltalige regio zoals West-Europa is het niet
ongewoon dat mensen die een verschillende taal spreken eenzelfde kubus gaan
raadplegen. AS2005 ondersteunt daarom vertalingen.Voor alle objecten
(zoals measures, dimensies en dimensieniveaus) kunnen we nu in verschillende
talen een naam opgeven. Wanneer een client gegevens uit de kubus opvraagt, zal
deze in zijn connectie een Locale Identifier (LCID) meegeven. Aan de hand
daarvan zal dan Analysis Services in zijn respons de namen gebruiken die in de
bijbehorende taal ingegeven zijn. Is er voor deze taal geen vertaling voorzien
in de kubus, dan zal het systeem terugvallen op de vertaling die als
standaardvertaling gemarkeerd is. Voor de eigenlijke data is het ook mogelijk
een vertaling te voorzien. Dit gebeurt door voor elk object waarvoor we een
vertaling willen voorzien, in de corresponderende tabel in de relationele
gegevensbank een kolom te voorzien met de vertaling, zoals dit bijvoorbeeld
voor productbeschrijvingen en dergelijke nu al in grotere OLTP gegevensbanken
gebeurd.
Digitale Dashboards en
rapportering
Zoals al eerder vermeld, vormt rapportering een
belangrijke toepassing van OLAP gegevensbanken. Een extreme vorm van
rapportering vormen digitale dashboards:
beleidsmensen steunen op enkele belangrijke bedrijfsindicatoren bij het
nemen van hun beslissingen. Er zijn verschillende toepassingen die de
visualisatie van dergelijke Key Performance Indicators (KPI) mogelijk
maken. In AS2005 is het mogelijk een Multi Dimensional Expression (MDX) formule
op te geven die een dergelijke KPI bepaalt. Naast de eigenlijke waarde verwacht
het systeem ook een doel (de waarde die we willen bereiken), een status (een
formule die aangeeft hoe dicht we al bij dit doel zitten) en een trend (een
formule die aangeeft in welke mate we de goede kant op gaan). Ook kunnen we
opgeven met welke ikonen we de informatie willen visualiseren. Let wel op: de
eigenlijke visualisatie ligt bij de client-applicatie, de instellingen van onze
server worden gewoon als property naar de client gestuurd. Figuur 6 toont de voorbeeld-visualisatie van twee KPIs in
Visual Studio.
Figuur 7: Voorbeeldvisualisatie van Key
Performance Indicator in Visual Studio
Meer uitgebreide rapporteringsmogelijkheden zijn
beschikbaar via SQL Server 2005 Reporting Services, waarmee een brede waaier
aan rapporten kan opgesteld worden. Dankzij een nauwe integratie van
ActiveViews, zal de constructie van rapporten nu ook door de typische business
user kunnen gebeuren. Figuur
8 illustreert de samenhang tussen al deze componenten.
Figuur 8 : Een overzicht van de
belangrijkste SQL Server 2005 componenten.
Proactive Caching
Een OLAP kubus wordt gebouwd bovenop een relationele
gegevensbank. De OLAP kubus moet daarom af en toe synchroniseren met de
relationele data. In AS2K waren er drie opslagmodellen: MOLAP, waarbij de data
uit de relationele gegevensbank in een multidimensionale gegevensbank
gerepliceerd wordt, ROLAP, waarbij elke OLAP query vertaald wordt naar een
query tegen de relationele gegevensbank, en HOLAP, de hybride vorm waarbij
geaggregeerde waarden berekend en bewaard worden in een multidimensionale
gegevensbank, maar de basisdata worden niet gedupliceerd: indien deze nodig
zijn blijft een toegang tot de relationele gegevensbank nodig. Met de
uitzondering van ROLAP zonder aggregaatswaarden, was in elk van deze scenarios
een expliciete opdracht tot synchronisatie nodig; iets wat vaak met behulp van
data transformation service (DTS, het huidige Integration Services), gebeurde.
In AS2005 echter is er proactive caching
ingebouwd, waardoor het systeem zelf de onderliggende gegevensbank kan
monitoren en indien er wijzigingen gebeuren, deze automatisch doorvoert in de
OLAP gegevensbank. Wanneer de onderliggende gegevensbank een SQL server is,
gebeurt dit via een notificatie-systeem, waarvoor de gebruiker zelf niets hoeft
te doen. Wanneer dit een andere gegevensbank is, heeft de gebruiker de keuze
tussen zelf een notificatie-systeem implementeren, of gebruik te maken van
polling. Ook hier zijn er allerlei opties instelbaar over hoe frequent een
dergelijke update moet gebeuren. Het resulaat is dat er niet drie maar een heel
scala aan opslagopties beschikbaar is (zie Figuur 7).
Figuur 9: Analysis Services 2005 biedt een
brede waaier aan opslag en update mogelijkheden
Data Mining: zoeken in de
hooiberg
Wat we tot nu toe van Analysis Services besproken
hebben is handig om de invloed van bepaalde variabelen (zoals product of regio)
op bepaalde maatstaven (zoals verkoopscijfers of winstmarges) te onderzoeken.
Maar er zijn in een kubus vaak vele variabelen, en voor sommige taken is het
moeilijk om handmatig de juiste analyses te maken. Weet jij bijvoorbeeld welk
de belangrijkste factoren zijn die het verschil maken tussen goede en minder
goede klanten? Weet jij welke producten er vaak samen aangekocht worden? Door
alle mogelijke combinaties tegen elkaar uit te zetten, kan je uit je gegevens
antwoorden op dit soort vragen afleiden, maar gegeven de grote hoeveelheid
variabelen, is dit in de praktijk onmogelijk. Daarom dat op vele plaatsen de
analysten zich beperken to het verifiëren van gekende afhankelijkheden. Het is
echter ook mogelijk om de computer zelf vele combinaties van variabelen te
laten analyseren, en de statistisch relevante correlaties te combineren in een
model. Dit noemt men Data Mining.
In AS2K waren er al twee data mining algoritmen
ingebouwd: een classificatie-algoritme en een clustering-algoritme. Veronderstel
dat je bijvoorbeeld als supermarkt graag zou weten welke van je klanten een
bepaalde soort voordeelkaart wensen aan te schaffen (bijvoorbeeld om
marketing-campagnes hierop af te stellen). Als je historische gegevens over je
klanten verzameld hebt, kan Analysis Services met zijn classificatie-algorithme
voor jou hieruit een beslissingsboom afleiden. Figuur 8 toont een voorbeeld van zo'n beslissingsboom.
AS2005 breidt de collectie van data mining technieken
uit AS2K drastisch uit. Zo is er een algoritme toegevoegd om associatieregels
(ook wel basket analysis genaamd) af te leiden. Dit zijn regels die
bijvoorbeeld correlaties in aankoopgedrag kan detecteren, zoals het beroemde
voorbeeld van Amazon (www.amazon.com), waar bij elk boek op de webpagina
getoont wordt in welke andere boeken een gebruiker waarschijnlijk ook
geïntereseerd zal zijn, gebaseerd op historisch aankoopgedrag van andere
klanten. Andere nieuwe algoritmen zijn er bijvoorbeeld voor regressie
(voorspelen van continue waarden) of tijdsreeksen (voorspellen van een volgende
waarde in een sequentie van waarden).
Figuur 10: Een deeltje van een Analysis
Services beslissingsboom: ongehuwde rijke klanten zijn voornamelijk in een
'Silver Card' geïntereseerd.
Conclusies
Analyse en rapportering worden steeds belangrijkere
facetten in de moderne bedrijfscultuur. OLAP gegevensbanken voorzien een meer
gebruiksvriendelijke en meer efficiënte toegang tot deze geaggregeerde
gegevens. Microsoft Analysis Services 2005 vormt een gebruiksvriendelijke,
krachtige OLAP server. De asynchrone ontwikkeling die in Visual Studio gebeurt,
en die kan steunen op wizards zoals IntelliCube, maakt het ontwikkelingsproces
gemakkelijker. Vermits het eindresultaat hiervan een XML document is, betekent
dit dat (onderdelen van) de OLAP kubussen ook programatorisch kunnen
gegenereerd worden. Perspectives en vertalingen laten toe grote kubussen voor
verschillende gebruikers te customiseren. Pro-actieve caching laat toe de
voordelen van ROLAP (up-to-date gegevens) te combineren met de voordelen van
MOLAP (performantie). Hiernaast komen nog vele bijkomende kenmerken, zoals meer
data mining mogelijkheden, complexere relationele basisgegevens en KPIs. Andere
kenmerken waar we in dit artikel niet dieper op ingegaan zijn zijn de debug mogelijkheden
voor MDX expressies, triggers, CLR stored procedures, caching van calculated
cells en calculated members,... Kortom, een heel arsenaal aan mogelijkheden om
analyse of raporteringstoepassingen efficiënt te ontwikkelen en deployen.
Links
http://u2u.info
http://www.u2u.be/articles.aspx
http://msdn.microsoft.com/sql/2005/2005/Articles/default.aspx
http://www.microsoft.com/Sql/reporting/
http://www.olapreport.com/market.htm
Bio
Dr. Nico Jacobs is trainer en consultant bij U2U (www.u2u.net),
waar hij zich voornamelijk in Micorsoft SQL Server en ADO.NET specialiseert.
Zijn e-mailadres is nico@u2u.be.
Wim Uyttersprot is director van U2U (www.u2u.net), de
Microsoft Partner voor Learning Solutions gevestigd te Brussel en
gespecialiseerd in .NET Training en Consultancy. Zijn e-mailadres is wim@u2u.be.