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)

zaterdag 8 oktober 2011

Blog 3. PowerPivot, de juiste versie en installatie.

Wat zegt Microsoft over Powerpivot?
PowerPivot voor Excel 2010 is een hulpprogramma voor gegevensanalyse met een ongekende computermatige kracht die rechtstreeks beschikbaar is in de software waarmee gebruikers al vertrouwd zijn: Microsoft Excel.
Met deze invoegtoepassing voor Excel kunt u grote hoeveelheden gegevens zeer snel omzetten in nuttige informatie, zodat u over de benodigde antwoorden beschikt.
U kunt de resultaten eenvoudig met anderen delen. Via PowerPivot kunt u zelfs de bedrijfsefficiëntie van de IT-afdeling verbeteren met SharePoint-beheerprogramma's.

Wat is kortweg het verschil met een normale Pivot?
Powerpivot kan met een veel hogere snelheid, veel meer data verwerken.
Ik heb me laten vertellen, tot 5 miljoen berekeningen per seconde en tot 1 miljard mutaties.
(afhankelijk natuurlijk van de processor en intern geheugen van je computer)
Bij mij liepen grote databestanden met de gewone draaitabel vaak vast door de databeperking van de draaitabel. Bovendien kun je in Powerpivot zelf queries en links maken tussen verschillende tabellen. Powerpivot heeft wel iets weg van een mix tussen Access en de draaitabel.

Mijn systeem:
Met welke hardware werk ik?
Een HP desktop met dualcore processor en 3GB intern geheugen.
Met welke software?
Windows XP SP3 en Excel2010 (32-bits versie)

Installatie:
1.      Voorbereiding
a.      Als je besturingssysteem niet voldoet aan, Microsoft® Windows® 7 or Microsoft® Windows® Server 2008 R2, installeer dan eerst : Net framework 4.0 (dotNetFx40_Full_setup.exe)   http://www.microsoft.com/downloads/nl-nl/details.aspx?FamilyID=9cfb2d51-5ff4-4491-b0e5-b386f32c0992
c.       Zorg dat je Excel2010 en alle gedeelde onderdelen in Office2010 hebt geïnstalleerd.


2.      Installatie:
a.      Zorg dat je de juiste versie van powerpivot gebruikt. Ik had eerst de standaard versie, maar hierin missen een aantal features die zeer welkom zijn.
b.      Code name 'Denali' PowerPivot for Microsoft® Excel is er niet in het nederlands, maar de engelse versie werkt goed samen met een Nederlandse versie van Excel2010. Hier de juiste link: http://social.technet.microsoft.com/wiki/contents/articles/3774.aspx
c.       Let op dat je de juiste keuze maakt tussen de 32 of 64 bits versie
d.      Lees nog even goed de system requirements en instructions door, zodat je zeker weet dat het straks ook goed werkt op je systeem.
e.      Klik op download en als het goed is, wordt dit bestand in je downloadmap opgeslagen.
f.        Zorg dat Excel2010 in gesloten en dubbelklik op dit bestand, volg de instructies op het scherm.
g.      Als alles goed gaat wordt nu de add-in in Excel2010 geïnstalleerd.

3.      Look and feel
Wat is er nu gebeurd?
Als je Excel opent, is er een nieuw tabblad voor de PowerPivot aangemaakt.
(rode pijl).


(Vervolg blog 4, de kennismaking en eerste oefeningen.)










Iemand vroeg aan mij, maakt het zoveel uit welke Windows versie je gebruikt?


Mijn antwoord :Dat maakt zeker uit.

In oudere versies is niet alle benodigde software geïnstalleerd.
Hieronder een beschrijving wat in elk geval aanwezig hoort te zijn VOORDAT je PP gaat installeren.

1.0 MS OFFICE 2010. (tenminste de met rood omrande instellingen)


2.1 De installatie van PowerPivot mislukt als de runtime van VSTO 4.0 niet is geïnstalleerd
U moet de runtime van Visual Studio Tools for Office (VSTO) 4.0 installeren voordat u PowerPivot installeert. U kunt de VSTO-runtime downloaden in het Microsoft Downloadcentrum Beschrijving: http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-components-sitefiles/10_5F00_external.png .
Dit probleem is alleen van toepassing op gebruikers van Office 2010. Wanneer Office 2010 SP1 is uitgebracht, hoeft de VSTO-runtime niet meer afzonderlijk te worden gedownload.

2.2 .NET Framework 4.0 vereist op de computer
Als .NET Framework 4.0 niet is geïnstalleerd op uw computer, wordt de SQL Server-versie met codenaam "Denali" van PowerPivot for Excel CTP3 wel geïnstalleerd, maar wordt het tabblad PowerPivot niet weergegeven op het Excel-lint. U moet .NET Framework 4.0 installeren.

Denk er daarnaast aan dat voor Windows 7 de 64 bits versie van PP moet worden geïnstalleerd. http://social.technet.microsoft.com/wiki/contents/articles/3774.aspx

Tenslotte, als er in verkeerde volgorde is geïnstalleerd, zul je PP moeten deinstalleren, de ontbrekende software moeten installeren en daarna opnieuw PP installeren.

dinsdag 4 oktober 2011

Blog 2. Transparantie en gebruikersgemak

Eigenlijk door toeval kwam ik weer in contact met een oude relatie die al een aantal jaren met dezelfde vragen rondliep. Twee jaar gelden was hij al ver gevorderd met het bouwen van queries via een ontwikkelde Excel add-in tool. Het probleem waar hij tegenaan liep was, dat de ontwikkelde tool eigenlijk niet gebruikersvriendelijk en niet transparant was.  
De uitkomsten waren echter veelbelovend.
Razendsnelle reports in Excel via queries die direct aan diverse ERP databases waren gekoppeld. Door mijn enthousiasme over de tool en mijn eigen interesse in automatiseren van diverse rapporten, zijn toen een aantal standaard reports in Excel ontwikkeld die snellere en meer overzichtelijke informatie gaven als de standaard rapporten uit de ERP software zelf. In dit geval SAP B1.

Gezien de  intransparante methodiek van de tool, moest ik telkens nog de hulp van adviseurs inschakelen.  We hebben met die tool onder meer gebouwd aan een liquiditeitsmodule waarin alle relevante data werd meegenomen.
Dus budget, debiteur/crediteur, btw, orderportefeuille en zelfs leads.
Als voorbeeld: Als een accountmanager een lead had, waarbij de kans op succes > 70% was, werd deze lead meegewogen in de toekomstige verwachting van liquiditeit. Iedere nieuwe order die in het systeem werd gezet, werd direct volautomatisch in het model betrokken inclusief de inkoopverplichtingen die daaruit voortvloeide. De liquiditeitsprognose gaf inzicht in de komende 3 a 4 maanden.

Het idee om een transparante module te gebruiken, waarbij gebruikers veel meer zelfstandig kunnen aanpassen en ontwikkelen, kwam na de introductie van Office 2010 in zicht.

Toen Microsoft haar Officepakket 2010 ontwikkelde, hield MS rekening met de behoefte dat gebruikers  op een gebruiksvriendelijkere manier tussen SQL databases en Office moesten kunnen communiceren.  De bestaande optie draaitabellen voldeed, gezien de toenemende vraag naar informatie, niet meer. De draaitabellen (pivots) lopen bij grotere datatanken al snel vast en worden dan relatief traag in de verwerking. Bovendien zou het wenselijk zijn, als de gebruiker, zelf de data van meerdere tabellen relatief eenvoudig aan elkaar kon knopen.

Microsoft kwam met een gratis add-in voor Excel, PowerPivot.

(vervolg blog 3), welke versie, installatie met uitleg.

dinsdag 27 september 2011

Blog 1. ERP gekoppelde reporting via Excel.

Als financial heb ik altijd interesse gehad om mijn werkzaamheden zo goed mogelijk te automatiseren en zo efficiënt mogelijk in te richten. Daarbij is het van groot belang om relevante informatie goed en gestructureerd in een ERP systeem vast te leggen.
Doe je dat niet, dan wordt de informatievoorziening onvolledig.
“garbage in is garbage out”

Eigenlijk zijn alle ERP systemen goed toegerust op het vastleggen van allerhande (financiële) informatie, alleen schort het nogal eens aan de informatie-output. Vaak zijn er een aantal standaard rapporten beschikbaar, maar die blijken vaak niet voldoende.

Ik had grote behoefte om door middel van diverse reports overzichten te creëren en te rapporteren over:
  • De cashflow-ontwikkeling voor + 2/3 maanden.
  • De salesperformance en marge per regio, medewerker, productgroei.
  • Een forecast op basis van de beschikbare orderportefeuille en budget.
  • Een (geconsolideerde) P&L en balans overzicht per einde periode, YTD en budget.
  • De PBC lijst van de halfjaarlijkse accountantscontrole en zoveel mogelijk hiervan te  automatiseren. (scheelt een hoop aan accountantskosten)
  • Een 2 wekelijkse kredietrapportage voor de bank om de ruimte van de borrowing base te berekenen.
  • Een automatische afloopcontrole voor de vreemde valuta-contracten.
  • Een voorraadanalyse waarin vermeldt ouderdom, batch- gerelateerde inkoopprijzen, omloopsnelheid, afzet, omzet- en margebijdrage.
  • Een afdelingsafhankelijk dashboard met KPI’s
  • En zo kan ik nog wel even doorgaan!

Alle informatie hiervoor was aanwezig, alleen hoe kreeg ik die gestructureerd in de diverse rapportages? De database-structuur van diverse ERP systemen lijkt soms opzettelijk complex te zijn ingericht of vanuit het verleden verkeerd te zijn opgezet en doorontwikkeld.

Kijk maar eens naar de Sql database van Exact voor Globe. Het aantal bestanden en tabellen is zo groot geworden, dat het onbegonnen werk is om dit aan elkaar te knopen. Voor Exact moest ik destijds externe hulp inroepen om de juiste queries* te laten ontwikkelen.
*Queries zijn informatiefilters van een database, waarin alleen de relevante gefilterde informatie aanwezig is. 
Ik denk dat bijna alle ERP rapporten (standaard en maatwerk) worden gebaseerd op deze queries. Druk binnen Exact voor Globe in een rapport maar eens op Ctrl + Q en de relevante query wordt zichtbaar.

Hoe ver ben ik destijds zelf gekomen?
Nadat ik externen verzocht had om diverse queries met informatie te maken, kon ik die via Excel middels de optie “gegevens” binnenhalen (zie onderstaande afbeelding).

Dit is Excel 2010, 2007 is qua scherm gelijk.

Vervolgens gebruikte ik de draaitabellen (pivots) om de data overzichtelijk te rangschikken en rapporten mee te bouwen. Al snel ontstond het probleem dat draaitabellen een limiet aan dataverwerking hebben. Werd de data-output te groot, dan liep je draaitabel vast.

Het is best vreemd dat ondanks het feit dat het merendeel van de Nederlandse MKB bedrijven Exact gebruikt en iedereen behoefte heeft aan “eigen” rapporten, men nog steeds dure externe hulp moet inschakelen om die maatwerkqueries te laten bouwen. Er is kennelijk geen query op het internet beschikbaar of er is geen behoefte om de kennis te delen. De onwetendheid of matige interesse over dit onderwerp zorgt er voor dat we teveel geld aan externen betalen voor onze informatievoorziening.

Wat zou het geweldig zijn als je queries per ERP pakket zou kunnen bundelen en op internet beschikbaar kon stellen. Iets soortgelijks heb ik ondervonden toen ik met SAP B1 ging werken en ik ben ervan overtuigd dat dit voor meer ERP pakketten geldt.

De voordelen.
Denk je eens in welke kostenbesparingen je zou kunnen doorvoeren als je gekoppelde standaard- en maatwerkrapporten vanuit diverse ERP systemen automatisch in Excel zou kunnen publiceren, waarmee je vervolgens kunt rekenen en waaraan je zelfs externe informatie zou kunnen toevoegen.
  • De accountantskosten kunnen naar beneden.
  • De maandafsluiting wordt sneller en beter.
  • De management stuurinformatie is altijd up-to-date, kan omvangrijker en/of specifieker.

Een hoop vragen zijn bij mij opgekomen waarop ik antwoorden wilde hebben:
  • Waarom zijn deze queries niet gewoon op internet te downloaden?
  • Hebben andere financials niet dezelfde informatiebehoefte?
  • Is er één gebruiksvriendelijke add-in voor meerdere ERP pakketten in Excel.
  • Naar welke rapporten is dan de meeste vraag?
  • Zou het ook mogelijk zijn om externe informatie aan bestaande informatie te koppelen?
  • Kunnen rapportages via het web worden gepubliceerd?

Ik ben het gaan uitzoeken en werd  spontaan gevraagd om in een projectgroep deel te nemen die zich bezighield met de oplossing van een hoop van mijn vragen.

Lees verder in mijn volgende blog (2)