Gerelateerde items

Wat is de optimale grootte van een dc?(Expertartikel, 18 jan 2010)

 

Excel voor self-service Business Intelligence(Dossier, 21 dec 2009)

 

Vraagvoorspelling in Excel: hoe doe je dat?(Dossierartikelen, 5 mrt 2007)

 

Een andere reden om over te stappen naar Access is als er verschillende bestanden gekoppeld moeten worden. Dit kan in Access veel efficiënter dan met 'vertical lookups' in Excel.  

Excel of Access

Excel is een rekenprogramma dat ook gebruikt kan worden voor data analyse. Access is een database programma dat ook gebruikt kan worden voor rekenwerk. Echter, Excel gaat handiger om met rekenwerk. En Access gaat handiger om met grote tabellen en met onderliggende relaties.

Voor de meeste data analyses worden overigens beide programma's gebruikt. In Access wordt de ruwe data gegroepeerd tot overzichtelijke tabellen, die naar Excel worden gekopieerd, alwaar de data verwerkt wordt tot de gewenste presentatievorm (tabellen en/of grafieken).

Data-analyse in Access is specialistisch werk?

Veel mensen kunnen goed overweg met Excel. Een deel hiervan kan ook iets met Access. Maar het aantal mensen dat goed overweg kan met Access is redelijk beperkt. Dit heeft denk ik drie oorzaken:

• Iemand die voor de eerste keer Access opent zal niet meteen snappen hoe je er mee moet werken. Je moet bijvoorbeeld eerst weten wat een tabel is, wat een query is en wat de verschillen zijn (zie verderop in dit artikel).
• Excel wordt vaak dagelijks gebruikt, voor allerlei toepassingen. Access wordt veel minder gebruikt, alleen de meeste business analisten zullen Access vaak gebruiken. Hierdoor duurt het voor de meeste mensen langer voordat men echt goed bekend is met Access en voordat men de handigheidjes kent.
• Access is abstracter en minder gebruikersvriendelijk dan Excel. Bij Excel zie je meteen het resultaat van de berekening, bij Access moet je eerste de query afmaken en dan uitvoeren. Ook de manier waarop queries worden opgebouwd is niet voor iedereen evident. Ik zie bijvoorbeeld dat het vaak even duurt voordat het kwartje valt bij het instellen van criteria in een query (bijvoorbeeld hoe stel ik in dat ik alleen artikelen met voorraad > 0 wil meenemen)

Introductie Access

De basis van een Access bestand zijn Tabellen met gegevens. Denk bijvoorbeeld aan een tabel met orderregels en een artikel master file. In één Access bestand staan vaak meerdere tabellen (vergelijkbaar met tabbladen in een Excel bestand).

Met een Query kan een analyse worden gedaan op één of meerdere tabellen. Via een grafisch scherm kunnen tabellen eenvoudig gekoppeld worden, bijvoorbeeld op basis van artikelnummer. Bijvoorbeeld als een orderregel tabel met de artikel master worden gecombineerd, zijn alle artikel kenmerken uit de artikel master beschikbaar per orderregel. Je kunt dan bijvoorbeeld het aantal regels per productgroep bepalen of artikel afmetingen gebruiken om te bepalen hoeveel m³ verstuurd is.

Een pivot table (draaitabel) in Excel lijkt op een query, maar mist de mogelijkheid om verschillende tabellen te combineren. In de loop der jaren is de functionaliteit van pivot tables wel dichter bij die van Access queries komen te liggen.

Het resultaat van een query is eigenlijk ook weer een tabel, die weer als basis kan dienen voor nog een query. Het verschil tussen een query en een tabel is dat een query eerst uitgevoerd moet worden voordat de gegevens bekend zijn.

Verder kent Access ook Formulieren (tonen van tabellen en/of queries op het scherm volgens een vooraf gedefinieerde lay-out), Rapporten (idem maar dan bedoeld om af te drukken) en Data Access pagina's (bekijken en bewerken van gegevens via internet). Echter voor data analyse zijn deze niet van belang.

Stappenplan data-analyse in Access

Een data analyse kan bestaan uit de volgende stappen:

• 1. Inlezen van bestanden in Access
• 2. Data structureren en verifiëren
• 3. 'Source queries' maken
• 4. Data analyse

1. Inlezen van bestanden in Access

Inlezen in Access gebeurt vaak uit Excel bestanden of uit tekstbestanden (CSV of TXT). Het voordeel van Excel bestanden is dat deze zich aanpassen aan de land-instelling van de gebruiker (vaak Nederlands of Engels/Amerikaans), zodat datums en getallen altijd goed worden ingelezen. Bij tekstbestanden gebeurt dit niet en moet men opletten dat bijvoorbeeld:

• 3 april geen 4 maart wordt (dag-maand of maand-dag)
• 1.000 geen 1 wordt (duizend separator en decimaal symbool zijn omgedraaid)
• Separator bij CSV bestanden (punt-komma bij NL, komma bij US/GB)

2. Data structureren en verifiëren

Voordat aan de analyses begonnen kan worden, is het belangrijk om eerst de data te controleren en verbanden te leggen tussen tabellen. Dit kan in een aantal stappen:

• Unieke sleutels definiëren in tabellen, bijvoorbeeld artikelnummer in de artikel master. Dit voorkomt dubbeltellingen als een koppeling wordt gemaakt met een andere tabel.
• Consistentie check, bijvoorbeeld komen alle artikelen die in de orderfile staan voor in de artikelfile.
• Definiëren en bewaren van de structuur in "Relaties" (standaard menu-knop)
• Verwijderen van onbruikbare records.
• Controles op belangrijkste kengetallen, zoals aantal regels, m³ verstuurd, waarde van de voorraad. De totalen die vanuit de details berekend worden in Access, moeten overeenkomen met de gangbare KPI's.

3. 'Source queries' maken

Een "source query" is een query die data klaarzet voor verdere analyses. Bijvoorbeeld in een "source query orderregels" wordt de orderregel gekoppeld aan de artikelfile, wordt het volume per regel berekend en wordt de orderregel gesplitst in volle pallets en in een orderverzamelgedeelte. Ook worden artikelen / regels uitgesloten die niet relevant zijn voor de analyse.

Alle analyses op orderregels zijn bij voorkeur gebaseerd op deze query.

4. Data-analyse

Als alle tabellen en source queries zijn gedefinieerd kan de data analyse snel en efficiënt worden uitgevoerd. Voorbeelden van queries die veel worden gemaakt zijn:

• ABC analyses
• Totaal aantal orders, regels, m³, .... per jaar / maand / productgroep
• Order frequentie tabel
• SKU overzicht: lijst van alle artikelen met bijvoorbeeld per artikel:
• o Kenmerken: omschrijving, productgroep, leverancier, afmetingen,
• o Inslag data: regels, pallets, m³,
• o Uitslag data: regels, volle pallets, orderpick regels, m³,
• o Voorraad data: stuks, pallets, waarde

Tot slot

Oefening baart kunst; je leert het snelst door het vaak te doen. Belangrijk is wel om de resultaten te controleren. Klopt het totaal aantal regels / stuks nog? In Excel kan veel mis gaan (zie bijvoorbeeld de 'horror stories' in http://www.eusprig.org/). Omdat Access abstracter is dan Excel is het risico op fouten zeker niet kleiner.