Werken in Excel met data uit Oracle

Augustus 2009 - Veel bedrijven slaan de data uit hun bedrijfsinformatiesystemen op in een Oracle database. Dit is heel verstandig, maar het is lastig om even snel, zonder speciale tools grafieken of selecties uit de database te presenteren. Ook het maken van analyses en what-if scenario’s is niet eenvoudig.

Een oplossing voor deze uitdaging kan zijn om de gewenste data over te halen naar Excel, een voor de meeste gebruikers en managers makkelijk te gebruiken tool. Microsoft Excel biedt de mogelijkheid om op een eenvoudige manier een selectie van gegevens op te halen en te presenteren, grafieken zijn voor veel mensen duidelijker dan rijen cijfertjes.

In dit Whitebook wordt stapsgewijs uitgelegd hoe je op een eenvoudige manier data uit een Oracle database in Excel kan analyseren. De voorbeelden die in dit Whitebook worden beschreven zijn uitgewerkt met de Nederlandse Microsoft Excel 2003 met indien nodig de 2007 variant erbij.

Hoe werkt het?

In de volgende stappen zullen we de procedure doorlopen waarna het eenvoudig toe te passen is.

• bronnen bepalen/aanmaken;
• koppeling maken;
• query maken/invoeren;
• rapport en/of grafieken opmaken;
• bronnen bepalen/aanmaken.

Voordat je begint moet je goed weten welke informatie je uiteindelijk nodig hebt. Je kunt allerlei gegevens koppelen maar heb je dan relevante informatie of een heleboel ongestructureerde data? Begin eerst met het bepalen van wat je wilt laten zien en uit welk systeem/database de gegevens moeten worden opgehaald. Om deze gegevens binnen Excel te kunnen gebruiken moet de daarvoor een zogenaamde gegevensbron worden geselecteerd. Dit is in deze context puur een verbinding met al opgeslagen data zoals een financieel- of voorraad systeem.

Excel versie 2003: Ga in het menu Data naar Externe gegevens importeren en kies dan Nieuwe databasequery
Excel versie 2007: Tab Gegevens – Externe gegevens ophalen – Van andere bronnen – Van MsQuery

Kies voor nieuwe gegevensbron nadat je een vinkje bij “Query’s maken/bewerken met behulp van de wizard Query hebt gezet.

Hier wordt de methode gekozen waarop Excel de data gaat ophalen.

Op deze PC is de Oracle client software geïnstalleerd. Als dit niet het geval is kun je via de ODBC Administrator een DSN (Data Source Name) aanmaken. Dit is afhankelijk van de Windows-versie en valt buiten de scope van dit artikel.

Koppeling maken

Geef de username-wachtwoord combinatie op waarmee je het informatiesysteem wilt bevragen. Bij veel organisaties zijn gebruikers aangemaakt die alleen data kunnen lezen uit de database. Dit heeft de voorkeur, omdat je dan niet (al dan niet) per ongeluk gegevens kan muteren. Bij Service Name dient de alias, zoals deze in de tnsnames.ora gehanteerd wordt, te worden ingevuld. In dit voorbeeld wordt gebruik gemaakt van het in de Oracle-wereld welbekende scott/tiger account.

De ingetikte gegevens, met uitzondering van het wachtwoord, worden opgeslagen in een verbindingsbestand, zodat je ze de volgende keer weer kunt gebruiken. Je kunt ervoor kiezen om ook het wachtwoord op te slaan, dit is makkelijk maar natuurlijk een beveiligingsrisico.

Query maken/invoeren

Kies de tabel of view die je nodig hebt. Vaak zijn er views gemaakt waarin gegevens gecombineerd en/of gegroepeerd zijn. Standaard krijg je veel te veel tabellen te zien, je kunt het resultaat filteren door……de eerste 2 letters in te tikken of selecteer bij owner het gewenste schema. (Bij 2007 zit dit onder "Opties")

Bij 2007 kun je ook nog de kolommen selecteren:

Klik op de gewenste tabel verplaats ze naar rechts.

Je kunt op een grafische manier aangeven welke gegevens je wilt zien (drag & drop), zie voorbeeld 1 of rechtstreeks een query invoeren, zie voorbeeld 2.

Voorbeeld 1: grafisch de query maken

Klik op het sterretje onder EMP zodat alle kolommen geselecteerd worden. Het resultaat wordt getoond zodat je kunt controleren of je computer je goed begrepen heeft.

Klik op "Gegevens retourneren" zodat je kunt kiezen waar de gegevens terecht moeten komen.

Hieronder zie je het resultaat:

Dit was een voorbeeld met één tabel, zonodig kun je verschillende tabellen linken.
Je moet eerst een extra tabel toevoegen en daarna zonodig de link leggen door links te klikken op deptno en deze naar de andere deptno te slepen.
In de onderstaande afbeelding zie je ook een filter - alleen rijen die aan een of meer voorwaarde(n) voldoen, in dit geval alleen afdeling 30.

Voorbeeld 2: de SQL-query ingeven

Klik op de "SQL" button.

Daarna kun je zelf een SQL statement invoeren zoals hieronder weergegeven.

Je kunt deze natuurlijk ook uitgebreider en complexer maken naargelang de informatie die je uiteindelijk nodig hebt.

Een waarschuwing verschijnt dat de query niet grafisch weergegeven kan worden.

Je krijgt dan de data die je hebt opgevraagd in je werkblad nadat je op "Gegevens retourneren" geklikt hebt.

Bij voorbeeld 1 is het voor de visueel ingestelden onder ons makkelijker te zien wat er opgevraagd gaat worden, bij voorbeeld 2 heb je alle mogelijkheden van SQL voorhanden zoals inner/outerjoins, unions en dergelijke – meer geschikt voor mensen met SQL kennis. (SQL staat voor Structured Query Language – het is een standaardtaal voor relationele databases, in de jaren 70 van de vorige eeuw ontwikkeld door IBM)

Rapport en/of grafieken opmaken

Van de opgehaalde gegevens kun je een net rapport en/of met de wizard grafieken mooie plaatjes maken – onderstaand een voorbeeld van de loonsom per vestiging.

Je kunt het verder naar je eigen smaak inrichten:

Je kunt de informatie periodiek verversen, iedere keer bij het openen van het bestand of handmatig wanneer jij het wilt. Dit doe je door in het werkblad rechts te klikken en voor Gegevens Vernieuwen te kiezen. Bij Excel 2007 is dit gesplitst in Externe gegevenseigenschappen en eigenschappen van de verbinding.

Ik gebruik deze techniek zelf voor het maandelijks genereren van ledenlijsten voor een vereniging waarvoor ik de ledenadministratie doe.

Microsoft biedt ook online ondersteuning:

• Instructie
• Snelzoekkaart

Aandachtspunten

Een minpuntje vind ik dat je bij datumvelden altijd de tijd meekrijgt, als je dit niet wilt moet je via celeigenschappen het formaat aanpassen. Als je een vinkje bij Celopmaak behouden plaatst, is dit een eenmalige actie.

Deze manier van werken kan nooit een vervanging zijn van een Business Intelligence oplossing vanwege de volgende redenen:

• onvoorspelbare belasting op je productiesysteem; als je onervaren gebruikers los laat (een join zonder where over 2 hele grote tabellen geeft wel een probleem);
• inzicht in afgeschermde data; als er extra beveiliging wordt gedaan in de applicatie (bijv. bij Java toepassingen) kan de gebruiker nu wellicht veel meer zien dan mag;
• ongecontroleerde datamutaties; een slimme gebruiker kan met tools als MSSQL ook een update/delete/insert uitvoeren;
• foutieve interpretatie van data; uiteindelijk heb je een BI systeem nodig waar de semantiek wordt toegevoegd aan de platte data.

Conclusie

Op deze manier kun je van de laatste stand van je bedrijfsinformatie op een snelle en herhaalbare manier gegevens extraheren en grafieken maken zonder dat je de gegevens steeds opnieuw handmatig hoeft te kopiëren. Het is alleen toepasbaar voor gebruikers die weten waar ze mee bezig zijn. Maak alleen gebruik van databaseusers met alleen leesrechten en werk bij voorkeur op een rapportage database.