zaterdag 26 november 2011

Blog 5 : Powerpivot DAX Formules,instellingen en measures.

Zorg voordat je allerlei formules gaat maken eerst dat je een TimeTable hebt aangemaakt.
Dat doe je gewoon in leeg tabblad van je excel rapportage als volgt:


Ik heb de sheet als tabel opgemaakt en de tabel Timetable genoemd.
Vervolgens koppel je de sheet aan Powerpivot.

Powerpivot wordt automatisch geopend en de timetable wordt toegevoegd.
Nu moet de tabel nog specifiek benoemd worden tot DE unieke datumtabel.

Selecteer hiervoor in PowerPivot het tabblad ontwerpen en vervolgens de ribbon "als datumtabel markeren" aanvinken. De optie daaronder vraagt je de juiste datumkolom te selecteren. In mijn geval is dat dus de kolom Date.

Nu alleen nog de tabel op de kolom Date koppelen met de tabel waarin alle data staat.

Alvast een paar Pivot measures:

YTD actual = TOTALYTD(SUM(ReportData[Amount]);TimeTable[Date])
YTD Last Year = TOTALYTD(SUM(ReportData[Amount]);SAMEPERIODLASTYEAR(TimeTable[Date]))
YTD 12mnd rolling= CALCULATE(SUM(ReportData[Amount]);DATESBETWEEN(TimeTable[Date];FIRSTDATE(DATEADD(TimeTable[Date];-11;MONTH));LASTDATE(DATEADD(TimeTable[Date];0;MONTH))))

vrijdag 25 november 2011

Blog 4. PowerPivot, Kennismaking en een voorbeeld met Exact Globe

De uiteindelijke PowerPivot in Excel werkt grotendeels hetzelfde als een normale Excelpivot (draaitabel). Als je dit toepast in combinatie met de Sliceroptie (zie in Excel2010) heb je een krachtige tool.

Echter, de weg waarlangs de data wordt opgehaald en hoe deze data wordt opgeslagen in het Excelsheet is geheel anders. De PowerPivots worden gebaseerd op tabellen waarvan de data erg simpel kan worden ingelezen. Door een zogenaamde intern-memory engine kunnen razendsnel grote databestanden worden ingelezen, geanalyseerd en berekend.

In Powerpivot kunnen door middel van diverse formules onder meer nieuwe berekende kolommen worden toegevoegd aan bestaande data. De programmeerfunctie hiervoor heet DAX en lijkt erg op de al bekende Excelfuncties.  Het toevoegen van tabellen kan van diverse bronnen komen.(SQL, Access, Excel, TXT etc)

Gebruik bijvoorbeeld de SQL database van het ERP systeem en voeg externe tabellen met relevante informatie toe. In Windows Azure market kun je een grote diversiteit aan data vinden.

Eenvoudig importeren van tabellen in PowerPivot.

Ik heb mij verdiept in de SQL ERP database van Exact Globe en ik zal aan de hand van een voorbeeld een eenvoudige rapportage uitwerken.

Voorbeeld:  Data importeren uit de sql database van ExactGlobe

Klik op de PowerPivot ribbon in Excel.

 Selecteer in het PowerPivotvenster “From database & From SQL server

In het venster dat opent vul je de Server name en Database name in en klik op test connection om te kijken of de verbinding in orde is en klik op Volgende.

 In het volgende venster kan een keuze gemaakt worden voor een tabellijst of handmatig een query schrijven. In dit geval klik op list of tables en op volgende.

Hier zie je een overzicht van alle tabellen van ExactGlobe. Ik selecteer de tabellen grtbk en gbkmut, geef de tabel een andere “friendly Name” en druk op Preview & Filter om de data van de tabellen te bekijken.

Het volgende venster vind ik erg goed overdacht. Hier kun je per kolom aangeven of je deze wil importeren (linker selectievakje)

en of je een filter wilt toepassen (rechtervakje)
In deze tabel (gbkmut) filter ik de blanks uit van de kolom dagbknr & van kolom transsubtype X. Bovendien vink ik de laatste kolom uit voor rapportage. Ik gebruik hem alleen om te filteren.

In de tabel grtbk selecteer ik onderstaande kolommen en klik OK.

Hierna kom je terug in het voorgaande venster en zie je in de kolom Filter Details dat de tabellen gefilterd zijn.

Klik op voltooien en PowerPivot haalt razendsnel de uitgefilterde data op uit SQL.

Vervolgens moeten beide tabellen nog middels een relatie met elkaar worden verbonden
Dat kan je visualiseren, klik hiervoor op het ikoon Diagram view.

Je ziet onderstaand nu de 2 tabellen waartussen een relatie moet worden gelegd.
De kolom reknr van beide tabellen bevat in dit geval identieke informatie.
Klik op één van deze kolommen, hou de muis ingedrukt en sleep de lijn die ontstaat naar de andere tabel in kolom reknr en laat los.
Powerpivot bepaalt aan de hand van de data de manier waarop de relatie wordt gelegd.
In dit geval bevat de tabel AccountHeader van ieder record, slechts één uniek reknr en de tabel AccountDetails veel records met hetzelfde reknr. Als je op de lijn klikt, kun je controleren of de relatie goed is gelegd.

Je bent nu klaar in PowerPivot. Sluit PowerPivot en ga in Excel naar het Tabblad PowerPivot en selecteer de Ribbon PivotTable. Onder deze knop zitten diverse opties, maar selecteer in dit geval Flattened Pivottable.

En vervolgens

Sleep vervolgens de volgende kolommen in de pivotfieldlist , fris het geheel een beetje op met de hulpmiddelen voor draaitabellen en je krijgt bijvoorbeeld zoals onderstaand een kolommenbalans per boekjaar waarin je met de slicers de balans of resultatenrekening kunt selecteren en per dagbknr de details kan analyseren.


Naslagwerk:

 (In Blog 5:  basis functies van DAX en PowerPivot standaard instellingen)