Ik ben de laatste paar weken druk bezig geweest met mijn nieuwe blogsite. Deze is vanaf nu te vinden op http://www.sqlblog.nl/
Deze bloggersite zal ik dan ook niet meer bijwerken! Tot snel op de nieuwe SQLBlog.nl site!
Deze blog-entry beschrijft hoe je eenvoudig een multi-value parameter kunt doorsturen van rapport A naar rapport B. Ik ben zelf lange tijd aan het klooien geweest om dit voor elkaar te krijgen. De oplossing was uiteindelijk erg simpel, het werkt precies hetzelfde als een normale parameter doorsturen. Het probleem was dat SSRS2005 standaard een (0) achter de parameter zet in de drillthrough aktie.
Wat ik zijn de gekozen weeknummer van Report A doorsturen naar Report B.
Report A: Parameter @GeselecteerdeWeekID (Multi-value)
Report B: Parameter @GeselecteerdeWeekID (Multi-value)
Ga naar de cell die je wilt linken naar Report B en klik op "Properties". Ga vervolgens naar "Navigation" en klik daar op "Jump to Report". Selecteerde het Report B.
....vervolgens selecteer je de parameter die je wilt doorsturen.
Klik de parameter aan. Standaard zet hij een (0) erachter, deze moet je weghalen en het werkt goed! Haal je de (0) niet weg stuurt hij alleen de eerste waarde van de reeks waarden door. Kies je bijvoorbeeld de weken: 200801, 200802,200803 , dan stuurt hij alleen 200801 door!
http://www.sqlblog.nl/component/option,com_jomcomment/id,98/opt,com_content/task,trackback/
Ik liep onlangs tegen een vraagstuk aan die ik wel interessant vond om op mijn blog te plaatsen. Het behandelt een veelvoorkomende situatie waarbij je een dataset meerdere keren gebruikt, ook wel een recursieve query genoemd. Men vroeg het aantal gewerkte weken per periode per uitzendkracht.
Per periode kan iemand meerdere declaraties hebben waarbij het getal “weken gewerkt” wordt bijgehouden. Om te bepalen hoeveel weken iemand in een periode heeft gewerkt pakken we de laatste “weken gewerkt” in een periode en trekken daar de laatste “weken gewerkt” uit de vorige periode vanaf, klinkt simpel toch?
Onderstaande afbeelding toont het resultaat wat we als basis gaan gebruiken om “weken gewerkt” per periode vast te kunnen stellen:
We hebben onze data gegroepeerd op jaar, Periode444Nummer, krachtcode en pakken de MAX(WekenGewerkt) per periode. Als we nu willen weten hoeveel weken iemand heeft gewerkt in periode 6-2008 moeten we 76-72 uitvoeren. Iemand heeft tenslotte aan het einde van periode 6-2008 76 weken gewerkt en aan het einde van periode 5-2008 72 weken gewerkt. Dit verschil is wat hij in periode 6 heeft gewerkt.
Om dit alles mogelijk te maken genereren we bovenstaande standaard dataset die we vervolgens kunnen hergebruiken, ook wel Common Table Expression genoemd (CTE). Ook voegen we een rijnummer toe per krachtcode en sorteren dit op krachtcode, jaar en periodenummer. Verder hebben we ook het vorige jaar nodig om te kunnen bepalen hoeveel weken iemand gewerkt heeft in de eerste periode van het huidige jaar! Dit zorgt voor een resultaat dat er ongeveer zo uitziet:
De sortering zorgt ervoor dat alles netjes in volgorde staat zodat we straks eenvoudig de weken van elkaar kunnen aftrekken!
Vervolgens maken we onderstaande query en voeren dit op de eerdere gegenereerde dataset uit:
De kracht zit hem in het stukje met de cirkel eromheen. Hierbij joinen we de tabel op zichzelf zodat we het resultaat van de vorige week gewerkt kunnen vinden. Dit trekken we van de huidige week af en vinden zo het resultaat.
De complete query ziet er ongeveer zo uit:
Uiteraard kun je dit voorbeeld in veel meer situaties gebruiken, bijvoorbeeld voor het berekenen van cumulatieven.
Parameter security
In onze omgeving maken we gebruik van een ASP.NET webportal voor het aanbieden van rapportages welke allemaal zijn gemaakt in Reporting Services 2005. De security met betrekking tot wie welke rapporten mag openen en wie welke parameters mag selecteren wordt bepaald in deze portal aan de hand van de NTUserName van de gebruiker (Active Directory). De portal vergelijkt deze NTUsername met een Werknemerstabel waarin meer informatie staat over de gebruiker, bijvoorbeeld zijn functie en kostenplaats.
Wie mag wat selecteren?
Om te bepalen wie wat mag selecteren (parameters) wordt ook weer gekeken naar de NTUserName van de gebruiker. Met behulp van stored procedures wordt nu de parameterbox op de rapportenportal gevuld met waarden.
Dit lijkt prima te werken tot je met drillthrough rapporten gaat werken. Stel dat ik een rapport (A) maak met een link naar een doorklikrapport (B). A stuurt een aantal parameters mee naar B om enkel de data te tonen die relevant is.
Zodra ik A exporteer naar Excel blijven de hyperlinks naar B intact. Ik kan vanuit Excel klikken op de link en kom netjes op rapport B uit. Het probleem is nu dat alle parameters worden getoond in de url. De gebruiker kan nu met een beetje handigheid de url aanpassen en gegevens te zien krijgen die niet voor zijn ogen zijn bestemd.
Voor dit probleem zijn meerdere oplossing mogelijk:
1) In de query van je rapport neem je iets op mbt security in combinatie met een security tabel
2) Op de database (niet aan te raden)
3) Versleutelingen meesturen in de link
In ons geval hebben we gekozen voor oplossing 3. Ik zal de details besparen waarom we dit gekozen hebben.
Uitleg mbv een eenvoudig voorbeeld
Rapport A heeft een parameter: GeselecteerdeJaar
Rapport B heeft een parameter GeselecteerdeJaar en Parameters SecurityPmt/ SecurityPmt2
Doel: Stel dat de gebruiker het jaar aanpast in de URL dan moet het rapport geen data laten zien.
In de hyperlink van A-> B stellen we in bij parameter mapping:
Linkerkant GeselecteerdeJaar, Rechterkant: =Parameters!GeselecteerdeJaar.Value
Linkerkant SecurityPmt, Rechterkant: =Parameters!GeselecteerdeJaar.Value * 2
We zeggen dus dat de SecurityPmt in Rapport B het gekozen jaar is * 2. Stel dat we 2008 kiezen als jaar zal hij 4016 meesturen als securityparameter.
Vervolgens moeten we nog een controle instellen op Rapport B.
We stellen een default value in voor SecurityPmt2
=IIF(Parameters! SecurityPmt.Value / 2 = Parameters!GeselecteerdeJaar.Value,1,0).
We zeggen hier: deel de securityparameter door 2. De uitkomst moet gelijk zijn aan het gekozen jaar. Als dit waar is dan word ik waarde 1, anders word ik waarde 0.
Tot slot zetten we een beveiliging op de dataregion door de visibility aan te passen:
=IIF(Parameters! SecurityPmt2.Value=1,False,True)
Als de waarde v/d parameter 1 is moet hij NIET ONZICHTBAAR worden. Als hij 0 is wel.
Dit is dus een goede methode om extra beveiliging toe te passen bij het doorsturen van parameters. Het voorbeeld dat ik geef met *2 is natuurlijk niet voldoende. Zoek hier een mooie formule bij die niet te raden is en stuur deze mee.
Oplossing voor het tonen voor bijv. alle klanten waar de afgelopen 2 jaar geen zaken mee is gedaan, uitgaande van een integer als datumveld.
SELECT * FROM AANKOPEN
WHERE LaatsteAankoop <= (YEAR(DATEADD(yy, -2, GETDATE())) * 10000) + (MONTH(DATEADD(yy, -2, GETDATE())) * 100) + (DAY(DATEADD(yy, -2, GETDATE())))
Delen door 0 kan niet in reporting services/sql server. Met deze eenvoudige formule kun je delen door 0 voorkomen zodat de #error melding niet meer verschijnt:
= IIF(SUM(Fields!OmzetVorigJaar.Value) + 0 = 0, Nothing,
SUM(Fields!MargeVorigJaar.Value) / IIF(SUM(Fields!OmzetVorigJaar.Value)+0=0,1,
( SUM(Fields!OmzetVorigJaar.Value) ) ))
Deze formule rekent het margepercentage uit van het vorige jaar (margevorigjaar/ omzetvorigjaar).
Eerst controleer je of het getal waardoor je wilt delen 0 is ( getal + 0 = 0). Als dit zo is vul je de textbox op met Nothing en stopt de verdere berekening. Nothing is hetzelfde als NULL in SQL, alleen met een ander naampje. Je kunt ook een lege string in de textbox stoppen: "". Mijn ervaring is dat je hiermee problemen kunt krijgen als je de uitkomst van deze cel in een andere cel wilt gebruiken (ReportItems). De input is dan een lege string en daar kun je geen formules mee maken.
Als het getal geen 0 is dan wordt de deling uitgevoerd.
Hoe configureer je een rapport dynamisch zodat je het rapport kunt weergeven in kwartalen, maanden en weken met behulp van een simpele parameter?
Maak een parameter aan: SelectiePeriode met de values: Week, Maand, Kwartaal. De labels/values kun je gelijk houden om de leesbaarheid te vergroten.
In de matrix in de groupcolumn:
=IIF(Parameters!SelectiePeriode.Value = "Kwartaal", Fields!Kwartaal445Code.Value, IIF(Parameters!SelectiePeriode.value = "Maand", Fields!maand.value, "Week " + Cstr(Fields!Weeknummer.value))))
Let op, als bijvoorbeeld kwartaal drie geen data bevat zal deze ook niet getoond worden. Dit los je op in je dataset door dummynamen te gebruiken.
Voorbeeld:
SELECT DISTINCT
Jaar,
Kwartaal,
Maand,
WeeknummervanJaar as Weeknummer,
NULL AS Waarde1
NULL AS Waarde2
ETC....
From dimdatum
UNION ALL
SELECT * FROM Table -> Hier komt je dataset
Vandaag kwam ik een handige tool tegen waarmee je eenvoudig je SQL Server 2005 database kunt voorzien van de nodige informatie. Zo is het mogelijk een beschrijving van je tabellen vast te leggen en kun je ieder veld binnen een tabel beschrijven, zo ook triggers en andere objecten binnen een tabel. De informatie die je beschrijft wordt in de SQL Server 2005 database bewaard als extented property en iedereen kan dit vervolgens bekijken; rechtermuis op een tabel, properties, extented properties.
De tool heet Data Dictionary Creator en is gratis te downloaden vanaf Codeplex.
Context
Het gebeurt regelmatig dat er op een dynamische manier csv bestanden uitgelezen moeten worden, bijvoorbeeld wekelijkse rapporten van een bepaalde afdelingen. Daarbij is de bestandsnaam van het csv bestand iedere week anders (dynamisch). Daarnaast moet er voorkomen worden dat een bestand meer dan een keer wordt uitgelezen. Zodra het bestand is uitgelezen dan krijgt het de status “verwerkt”. Als hij deze status heeft zullen er geen verdere mutaties meer mogelijk zijn.
Benodigd
We hebben totaal drie tabellen nodig.
Tabel 1: in deze tabel slaan we de namen op van de CSV bestanden die al zijn uitgelezen;
Tabel 2: in deze tabel slaan we tijdelijk de bestandsnamen op van CSV bestanden uit de folder die wordt gescand;
Tabel 3: in deze tabel slaan we de inhoud van de CSV file(s) op.
Voorbereiding
Maak een variabele aan op package-scope. Noem deze variable bijvoorbeeld “csvfile” van het formaat String. Zet de waarde op 0. Deze variabele is benodigd om straks de filenames in op te slaan en weg te schrijven naar een tabel.
Stap 2
Dit is een ForEachLoop Container die de opgegeven folder gaat scannen op bestanden met de extensie “.csv”. Zodra er een .cvs bestand is gevonden wordt deze weggeschreven in Tabel
Tabje collection: kies hier de For Each File Enumerator. Kies vervolgens de folder waar de bestanden zijn opgeslagen en kies voor de extensie *.csv. Dit zorgt ervoor dat alle bestanden die deze extensie hebben worden meegenomen. Uiteraard ben je hier vrij om in te vullen wat je wilt, bijvoorbeeld: budget_week*jaar*.*
Tabje Variable Mappings: hier moet de variabele worden gekozen waarin de bestandsnaam moet worden weggeschreven. Aangezien er maar een variabele is aangemaakt kiezen we deze, “user::csvfile” met index 0.
Stap 3
Deze taak bevindt zich in de For Each Loop en schrijft de gevonden bestandsnaam weg in Tabel 2.
Tabje General: stel de connectie in en vul bij SQLStatement in:
INSERT INTO Tabel2(CSVFileName) Values (?)
Tabje Parameter Mapping: hier wordt de variabele gekoppeld aan de input. Met andere woorden; hier geven we aan welke variabele we gebruiken in ons SQL statement en welke het vraagteken vult. We koppelen hier de variabele user::csvfile als input aan onze statement en geven hem het type Varchar mee en Parameter 0. Deze laatste is heel belangrijk. Vergeet je hier de
Stap 4
Tijdens stap 1 t/m 3 is de folder gescand op CSV bestanden en zijn alle gevonden bestanden weggeschreven in Tabel 2, de staging tabel. Nu willen we weten welke bestanden er nieuw zijn bijgekomen sinds het package voor de laatste keer is gedraaid (de eerste keer zullen alle bestanden nieuw zijn). We gaan dit controleren door Tabel 2 te vergelijken met Tabel 1. We zullen kijken welke bestandsnamen nieuw zijn en schrijven deze vervolgens weg in Tabel 1. Hiervoor maken we een Dataflow Task aan.
Als eerste wordt er een OLEDB source aangemaakt die alle records van Tabel 2 ophaalt. Daarna vindt er een lookup plaats. Deze lookup kijkt naar Tabel 2 en vergelijkt de bestandsnamen met Tabel 3. De nieuwe records die worden gevonden zullen worden weggeschreven in een OLEDB destination, Tabel 3. De overige records wordt niks mee gedaan en belanden in de Trash Destination (dit is een gratis addon).
Stap 5
In stap 4 hebben we de bestandsnamen die nog niet voorkwamen weggeschreven in tabel 3. Tabel 3 bevat een extra kolom, IsVerwerkt. Deze geeft aan of het bestand al is uitgelezen of niet. Omdat de nieuwe bestanden uiteraard nog niet zijn uitlezen krijgen deze de status 0. Dit doen we met een Execute SQL task:
UPDATE Tabel3 SET IsVerwerkt = 0 WHERE IsVerwerkt IS NULL
Stap 6
Als het goed is hebben we nu een correcte tabel 3 waarin staat welke bestanden inmiddels zijn verwerkt (status TRUE) en welke nog moeten worden verwerkt (status FALSE). Om die laatste gaat het uiteraard. In stap 6 zullen we de bestanden die een status FALSE hebben gaan uitlezen en plaatsen in Tabel 1. Deze taak zal opnieuw de folder scannen en bekijken welke bestanden een status van IsVerwerkt FALSE hebben in tabel 3.
Herhaal stap 2 met dezelfde instellingen.
Stap 7
Stap 7 bekijkt of de gevonden bestandsnaam een status 0 heeft. De volgende statement wordt gebruikt:
SELECT CSVFileName FROM Tabel1 WHERE [IsVerwerkt] = 0
Zet resultset op Single row en voer in het tabblad Result Set in: ResultName: CSVFileName (deze moet overeenkomen met je SQL-statement), Variable name: user::csvfile
Stap 8
Op deze stap ga ik niet te diep in. Wat hier gedaan moet worden is het bestand dat nog niet is verwerkt uitlezen. De bestandsnaam moet dynamisch zijn. Zodra we de dataflow ingaan moet de bestandsnaam dus worden aangepast aan de naam van het bestand. Maak hiervoor een flat file connection manager aan en maak mbv een expressie de connectionstring variabel, in mijn geval:
@[User::filepath]+ @[User::csvfile]. De eerste is een variable die ik zelf gebruik om het filepath te vullen, hier kun je ook bijv C:\ invoeren. Door dit te doen zal de connectionstring iedere keer worden aangepast met de naam van het bestand waarmee je de loop ingaat.
Stap 9
Het bestand is nu uitgelezen dus is hij automatisch verwerkt. Wat er nog gedaan moet worden is de status van IsVerwerkt op 1 zetten. Dit doen we met een Execute SQL Task met het volgende statement:
UPDATE Tabel3 SET IsVerwerkt = 1 WHERE CSVFileName = ?
Het vraagtekentje vullen we door een parameter mapping aan te maken, csvfile, input, varchar, 0.
Dat was het voor zover. Ik weet niet of dit de beste methode is om een folder met CSV’s uit te lezen en vervolgens in te lezen maar in mijn situatie werkt het prima. Mochten er vragen of opmerkingen zijn, schroom niet om een reply te posten!
-Ronald
Ik ben gisteren op de ITHerolympics geweest in het Nieuwegeinse Business Center. Er waren diverse presentaties te zien over de 3 nieuwste producten van Microsoft (SQL2k8, Windows Server 2k8, Visual Studio 2k8). Na afloop kreeg ik een schattig rood tasje mee met de nodige software. Mijn oog viel direct op de Nederlandse versie van het Microsoft Technet Magazine. Nu blijkt ook dat dit een gratis 2-maandelijks magazine is. Online kun je een abonnement afsluiten.
Linkje
Verder was het gigantisch druk. Het nadeel was ook dat de sessies die ik wilde volgen vol zaten aangezien de lokalen niet al te groot waren. Gelukkig heb ik wel de nodige kennis opgedaan van de nieuwe mogelijkheden van SQL Server 2008, helaas niet van de BI-onderdelen. Ik zal dus nog de nodige kennis door wat blogs en andere sites af te struinen.
Back to work!
-Ronald
In datawarehouseland gebruiken we in onze DimDatum integers als primaire sleutels. Vaak komt het voor dat we een DateTime value moeten omzetten naar een integer om een join te kunnen maken met de DimDatum tabel. Dit kan met het volgende T-SQL Statement:
YEAR(<
In <
En andersom is ook weleens handig! Hier de functie:
CREATE FUNCTION [dbo].[FromDateIDtoDateValue](@Date varchar(8))
RETURNS datetime
AS
------------------------------------------------------------------------------
-- FromDateIDtoDateValue
-- To Translate a YYYYMMDD date to a valid datetime
-- Returning 00000000 as a NULL value
BEGIN
-- Declare the return variable here
DECLARE @FunctionResult datetime
IF isdate(@Date) = 1 SET @FunctionResult = cast(@Date as datetime)
Else SET @FunctionResult = null
RETURN(@FunctionResult)
END
Een simpele vraag: hoe ken ik in SSIS2005 een waarde toe aan een variabele die afkomstig is uit een SQL-query? Het antwoord hierop is vrij eenvoudig maar je moet het net even weten.
Ik wil bijvoorbeeld de naam van een kracht ophalen uit een tabel en deze toekennen aan een variabele om deze later weer te kunnen gebruiken.
1. Maak een variabele aan, bijvoorbeeld "VolledigeNaam" type: string.
2. Maak een SQL-Task aan en maak een connectie aan.
3. Type je statement
4. Zet de resultset op "Single Row"
5. Ga naar het tabblad Result Set. Vul in de eerste kolom (Result Name) de waarde in die je aan de variabele wilt toekennen, exact zoals de query teruggeeft en kies in de tweede kolom de juiste parameter.
Als het goed is heb ik nu de variabele 'VolledigeNaam' gevuld met de naam van een kracht.
Microsoft SQL Server 2008 CTP voor Februari is deze week uitgekomen. Het is een volledige release met alle functies :-)
"The SQL Server team presents the future of SQL Server! SQL Server 2008 February CTP represents a significant leap in features, functionality, and improvements. Get your hands on the February CTP at the Download Center and then come back here to learn about all the features."
Meer informatie op deze site!
Verschijdene malen ontstond er tijdens het exporteren van rapportages die in Reporting Services 2005 waren gemaakt een grijze achtergrond in Excel. De reden is onverklaarbaar en ook op de technische forums was er weinig over te lezen tot de oplossing ergens opdook.
Voeg in de Report Designer een klein tekstboxje toe net boven je tabel of matrix. Zet de achtergrond van de textboxje op transparant en je probleem is opgelost! De oorzaak is niet bekend.
Ik stond vorige week voor een verassing toen ik in een dimensie binnen OLAP had ge-processed. Een van de attributen binnen deze dimensie was een veld van het type Boolean. Wat ik verwachtte te zien was een van de waardes: True, False of Unknown (de laatste voor lege velden). Tot mijn verbazing kreeg ik dit te zien: True, " " (leeg veld). Dit was dus niet wat ik verwacht had!
Na wat rondvragen was de oplossing vrij eenvoudig. SSAS gooit NULL values en FALSE velden op een grote berg. Er bleken een aantal records in mijn tabel te zitten met de waarde NULL. SSAS vind dat FALSE velden nu ook automisch NULL zijn dus verdwijnt de FALSE member.
De oplossing is simpel, maak een "Named Calculation" aan in je datasourceview met een code die op onderstaande lijkt:
(CASE WHEN [MetLoonDoorbetaling] = 1 THEN 'JA'
ELSE 'Nee'
END)
Op deze manier valleen alle NULL values onder de categorie FALSE en de rest automatisch onder TRUE.
Vond vandaag een handig tooltje als Addin voor de Business Intelligence Development Studio.
Download hem hier
Functies
Afgelopen vrijdag heb ik een poging gewaagd voor het 70-446 PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005. Gelukkig was deze poging ook direct succesvol.
Vergelijk ik dit examen met de 70-445 was hij een stuk eenvoudiger.Je kreeg ongeveer 6 cases en iedere case bevatte zo’n 8 tot 10 vragen. Per case had je tussen de 28 en 32 minuten de tijd. Mijn ervaring was dat dit ruim voldoende was. Totaal had je 210 minuten de tijd ( 3 ½ uur).
Wat ik me nog kan herinneren waren de volgende cases/vragen:
Fabrikam; een of andere fabriek met een DWH oplossing.
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:
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!
