Bonkster's SQL Server 2005 Blog
SQL Server 2005 & Google Tips

In SQL Server 2005 ontbreekt een zoekfunctie om snel een kolom te kunnen vinden. Dit kan nog weleens handig zijn als je een bepaalde kolom zoekt maar niet weet in welke tabel deze zich bevindt. De oplossing hiervoor is een eenvoudig Query:


SELECT O.NAME Tabelnaam,
C.NAME Kolomnaam
FROM sys.columns C
INNER JOIN sys.objects O ON C.object_id = O.object_id
WHERE C.NAME LIKE '%VULHIERDEKOLOMINDIEJEZOEKT%'
ORDER BY O.NAME,
C.NAME





 

Reistijd KPI met IGoogle

Posted In: , . By Ronald Kraijesteijn

Ik kwam v/d week een handigheidje tegen bij de IGoogle Gadgets, namelijk een soort van Key Performance Indicator van de actuele reistijd van A naar B. Het ding houdt ook nog eens rekening met files en drukte, precies wat de moderne forens nodig heeft!

Hoe werkt het? Vrij eenvoudig; je maakt een accountje aan bij Zomoto en voert daar de vertrekplaats en bestemming in, vervolgens wordt deze automatisch toegevoegd aan je IGoogle lijstje!

Nu rust de vraag, waar vind ik deze gadget? Hier is de URL

Screenshotje:

 

Iedere kent ze wel, hierarchieën in Analysis Services 2005. Met een hierarchy kun je bepaalde data op verschillende niveau's aan de eindgebruiker tonen. Een bekend voorbeeld is:

Jaar -> Kwartaal -> Maand -> Dag

Omdat ik het nogal onduidelijk vind hoe je dit precies moet configureren leek het mij handig hier een blogje over te schrijven. Kennis hiervan heb ik opgedaan tijdens een Cursus bij Infosupport en door het in de praktijk toe te passen in een project met OLAP Cubes.

In onderstaand voorbeeld ga ik ervanuit dat er gebruik gemaakt wordt van een datumdimensie die er ongeveer zo uitziet:



1. Maak een nieuwe Analysis Services projectje aan en configureer een datasource en een datasourceview. Voeg aan de datasourceview de datumtabel toe uit het datawarehouse, bijvoorbeel DimDatum of DimTime.

2. Maak een nieuwe dimensie aan door met de rechtermuisknop te klikken op Dimensies -> New Dimension. De wizard zal opstarten. Kies bij Dimensietype voor 'Time Dimension' en selecteer de tabel die je net in je datasourceview hebt toegevoegd, de DimDatum dus. Je krijgt daarna onderstaand schermpje. Vul de juiste waarden in, ongeveer zoals in het voorbeeld. Dit kun je later ook nog aanpassen.


Vervolgens krijg je dit schermpje:



Geef je dimensie een naampje, bijvoorbeeld: Datum. Als alles goed is gegaan zal de gegenereerde dimensie verschijnen in je lijstje met dimensies.

Nu denk je vast, is dat het? Nee, er moeten namelijk nog een paar heel belangrijke aanpassingen gedaan worden die je zeker vergeet als je het niet weet, namelijk de onderlinge relaties tussen de hierachieën instellen. Daarnaast moeten er nog wat sleutels worden goedgezet die een jaar + maand als uniek maken.

3. Open de dimensie. Met het cirkeltje op het plaatje zie je dat de onderlinge relaties niet goedstaan. Dit zie je snel over het hoofd!



Wat we nu gaan doen is de relaties goed zetten.

4. Klap aan de linkerkant van je scherm de DatumID open (of de primary sleutel van je datum dimensie). Je krijgt nu alle attributen te zien die gekoppeld zijn aan de sleutel.

5. Sleep de parent attribuut onder de child. In ons geval sleep je jaar onder Kwartaal, vervolgens Kwartaal onder Maand en Maand onder Dag. Dit doe je allemaal aan de linkerkant van je scherm dus niets naar het midden slepen!

6. Let goed op dat de namen van je hierarchie gelijk zijn aan het plaatje in het midden. Ik gebruik in mijn voorbeeld MaandNummerVanJaar en MaandNaam. De hierarchy configureren we op het integerniveau van de dimensie dus we gebruik maandnummervanjaar en kwartaalnummer van jaar. Dit moet ook zichtbaar zijn in het midden van het scherm waar de feitelijke hierarchy wordt opgebouwd!!



7. Als het goed is krijg je onderstaande afbeelding te zien. Je ziet dat de vierkante blokjes nu met elkaar zijn verbonden met pijltjes, dit betekent dat de relaties goedstaan! Uiteraard kun je zoveel hierarchieën aanmaken als je zelf wilt.



8. Nu moeten we nog de sleutels goedzetten. Een maand is namelijk niet uniek! Hetzelfde geldt voor een kwartaal. Een maand komt immers voor in ieder jaar! Klik aan de linkerkant op het attribuut "MaandNummerVanJaar" en dan op properties. Er verschijnt een flinke lijst aan properties aan de rechterkant van je schem. De property die je nodig hebt is: "KeyColums". Klik op het ... knopje bij de property. Je krijgt nu dit schermpje:



Klink links op "Add" en zoek bij Source naar "Jaar". Wat je nu doet is een combinatie maken van maand + jaar zodat deze uniek wordt.



Klik op OK! Herhaal dit voor het KwartaalNummerVanJaar attribuut. Om te testen of het goed is geconfigureerd kun je de Dimensie even deployen/processen en op de "Browse" knop drukken. Als alles goed is krijg je onderstaande afbeelding te zien! Je krijgt nu voor de maanden de nummers 1 t/m 12 te zien en de kwartalen 1 t/m 4. Dit is natuurlijk niet zo netjes want we willen graag de maandnamen zien en de Kwartaalnummers!

9. Dit doe je bij de property "NameColum". Stel dat we MaandNummerVanJaar nemen als attribuut dan vullen we bij NameColumn in: MaandNaam. Deze kun je als het goed is kiezen uit het lijstje!

Voorbeeldje:



Als het niet wordt weergegeven als hierboven dan is de kans groot dat je sleutels of hierarchie niet juist is ingesteld!

Meerdere hierarchieën en de configuratie van de sleutels
Hennie vroeg hoe je de sleutels moet configureren wanneer je meerdere hierarchieën wilt gebruiken in een dimensie. Ik heb hier even naar gekeken en het antwoord is vrij eenvoudig.

Stel we hebben een dimensie DimFunctie. In deze dimensie hebben we twee hierarchieën:

  • Functie Type -> Functie Naam
  • Functie Niveau -> Functie Naam
We moeten nu dus twee combinaties van sleutels maken, voor allebei de hierarchie een. Wat je nu het beste kunt doen is de eerste hierarchie configureren zoals hierboven beschreven is. Om de tweede hierarchie te configureren doe je het volgende:
1. Sleep vanaf de rechterkant je hoogste niveau naar het midden, een nieuwe hierarchie verschijnt (in mijn geval Functie Niveau). Vervolgens sleep je vanaf rechts het tweede niveau daaronder (Functie Naam). Als het goed is herkent het programma dat je Functie nu twee keer wilt gebruiken en maakt hij een nieuwe attribuut aan die hetzelfde heet, gevolgd door het cijfer 1. Ik krijg nu dus een attribuut aan de linkerzijde: Functie Naam 1. Vervolgens configureer ik deze op dezelfde manier, ik sleep dus het parent item onder Functie Naam.





Hennie vroeg ook nog om een voorbeeld van een tijdsdimensie met meer dan 1 hierarchie, zie screenshot:



Dat was het voor zover. Mocht je commentaar of tips hebben op dit artikel, laat even een berichtje achter!

 

Favorieten in IGoogle bijhouden

Posted In: , , . By Ronald Kraijesteijn

Google heeft een leuke gadget voor de IGoogle interface, namelijk de favorieten. Er zijn twee verschillende varianten verkrijgbaar, zoek op: Google Bookmarks bij de gadgets. Hiermee kun je eenvoudig al je favorieten op een centrale plek beheren. Daarnaast is er een addon te krijgen voor Mozilla zodat ook daar je favorieten worden weergegeven. Het leuke is dat je in je IGoogle een tabblad kunt inrichten met al je favorieten. Je kunt namelijk de bookmark gadget meerdere keren toevoegen aan je tabblad!

Als je de Google Toolbar voor IE7 installeert is er standaard een knopje "Google Bladwijzers". Zodra je inlogt met je Google Account verschijnen ook hier je favorieten.

Kortom, 1 centrale plek voor je favorieten waar je ook bent en nooit meer een back-up hoeven maken!


 

Wanneer er een fout ontstaat in je ETL-package wil je als Datawarehouse beheerder ook graag weten wat en wanneer er iets fout is gegaan. Wanneer je gebruik maakt van een event handler wordt er bijvoorbeeld een e-mail afgevuurd met de melding dat er een fout is opgetreden maar waar het fout is gegaan is dan onbekend.

Dit artikel laat zien hoe je een event handler kunt configureren in je masterpackage die een rapport mailt met errordetails vanaf het startpunt van de laatste run. Ik ga er even vanuit dat er een masterpackage aanwezig is die alle subpackages aanroept en dat logging is geactiveerd. Logging is namelijk nodig om te kunnen bepalen wanneer de laatste run gestart is.

Stap 1: Event Handler configureren
Het eerste dat moet worden gedaan is het aanmaken van een event handler die een aantal stappen doorloopt op het moment dat er iets fout gaat. Klik op het tabblad "Event Handlers" en kies het niveau waarop de event handler moet afgaan. In onderstaand voorbeeld is de scope van de event handler het gehele package. Je kunt een event handler ook op een individuele task configureren. Kies in de tweede dropdown box de optie "OnTaskFailed". Dit zorgt ervoor dat deze event handler wordt aangeroepen op het moment dat de package failed.

Vervolgens sleep je de volgende taken in het scherm: dataflowtask, send mail task en een file system task. Vervindt deze in dezelfde volgorde aan elkaar met de precedence contraint (groene pijl).

Stap 2: Data-flow task configureren
Vervolgens gaan we een dataflow task aanmaken die een dataset voor ons op gaat halen met informatie over de foutmeldingen. Dit is handig om vast te kunnen stellen waar de fout daadwerkelijk is ontstaan. Standaard worden alle fouten gelogd in de sysdtslog90 tabel.



Dubbelklik op de dataflowtask, je komt nu in de dataflowtask editor. Sleep een OLEDB-Source op het scherm en een Flat File Destination en verbindt deze met elkaar met de groene pijl. Geef de twee taken direct betekenisvolle namen zoals: OLEDBSRC Sysdtslog90 en FFD ErrorLogETL zodat voor andere mensen ook duidelijk is wat er gebeurd zonder in de codes te neuzen. Je dataflowtask ziet er ongeveer zo uit:


Nu moeten we de OleDB Source gaan instellen zodat alleen de foutmeldingen worden opgehaald die zijn ontstaan tijdens de laatste run. Omdat SSIS start- en eindtijden van packages logt wordt zo ook de starttijd van het masterpackage. Wat we nu eigenlijk willen weten is:
- > Geef me een lijstje van alle foutmeldingen die zijn ontstaan tijdens de laatste run.

Het eerste dat we dus willen weten is wanneer de laatste run is gestart. Dit kunnen we vinden door te zoeken naar de laatste keer dat het event "PackageStart" is opgetreden voor het masterpackage. Daarna gaan we kijken welke foutmeldingen er zijn ontstaan na dit tijdstip. De volgende query is hiervoor te gebruiken. Het enige dat je moet veranderen is de naam van de package.

--------------------------------------
SELECT ROW_NUMBER() OVER (ORDER BY Starttime ASC) AS FoutNr,
source AS Bron,
starttime AS Starttijd,
[message] AS Foutmelding
FROM sysdtslog90 AS S
WHERE starttime >= (SELECT TOP 1
starttime
FROM sysdtslog90
WHERE source = 'VUL_HIER_JE_PACKAGENAAM_IN'
AND Event = 'PackageStart'
ORDER BY starttime desc
)
AND [event] LIKE '%error%'
--------------------------------------
Voer bovenstaande query in bij het configureren van je oledbsource en kies als OLE DB Connection Manager een connectie naar de database waarin de sysdtslog90 tabel te vinden is en kies als Data Acces Mode voor "SQL command"


Nu gaan we de verkregen data wegschrijven in een textfile die we straks gaan mailen. Dubbelklik op de eerder aangemaakte Flat File Destination en configureer deze naar wens. Maak hierbij dus ook een connection manager aan met bijvoorbeeld een delimited text file, net wat je het makkelijkst vindt.


De dataflowtask is nu gereed. Er wordt een dataset opgehaald met alle foutmeldingen en deze worden netjes weggeschreven in een textfile.

Wat we nu nog moeten doen is een e-mail laten versturen naar de beheerder.

Stap 3: E-mail task Configureren
Keer terug naar je event handler tabblad en dubbelklik daar op de Send Mail Task. Configureer daar alle instellingen die nodig zijn dus: SMTP Server (eerst aanmaken), From, To, Subject, MessageSource. Bij attachment kies je de textfile die eerder is aangemaakt tijdens de dataflowtask. Dit path moet dus overeenkomen, bijv: d:\SQL\Log\ErrorLogETL.txt

Het kan zijn dat hij nu een warning geeft omdat het textbestand immers nog niet bestaat. Tijdens het uitvoeren van het package zal dit bestand automatisch gegenereerd worden dus je kunt deze foutmelding negeren. Let ook even op McAfee virusscan die standaard port25 blokkeert. Dit kun je uitschakelen.

Stap 4: Logfile opruimen
Het is wel zo netjes om na afloop de aangemaakte logfile weer op te ruimen. Dubbelklik op de File System Task. Kies daar voor Operation: Delete File. Bij SourceConnection kies je de Flatfileconnection die eerder aangemaakt is en die de log bevat.


We zijn nu klaar. Test of het werkt door een error ergens in te bouwen en het package te runnen. Voor opmerkingen of vragen, laat een bericht achter!