Implementarea Unui Flux DE Curatare Si Integrare A Datelor DIN Surse Eterogene

IMPLEMENTAREA UNUI FLUX DE CURAȚARE SI INTEGRARE A DATELOR DIN SURSE ETEROGENE

INTRODUCERE

CONCEPTE FUNDAMENTALE PRIVIND DEPOZITELE DE DATE

1.1 Definiția unui depozit de date

Există foarte multe definiții ale conceptului de Depozite de date (data warehouse), lucru care face dificilă formularea unei definiții riguroase a acestora. În anul 1990, în cadrul articolului “What is a Data Warehouse?” publicat in revista Prism, William H. Inmon menționează și definește pentru prima dată conceptul de depozit de date. Conform lui W.H.Inmon, “un depozit de date este o colecție de date structurate pe subiecte, integrate, dependente de timp si nevolatile, date care vin în sprijinul procesului managerial de luare a deciziilor”.

Voi detalia semnificațiile termenilor care apar în această definiție mai jos:

– date structurate pe subiecte: datele sunt grupate astfel încât să ofere informații cu privire la un anumit subiect sau domeniu, și nu asupra operațiunilor curente ale companiei;

– date integrate: datele sunt colectate în depozitele de date din surse multiple, fiind combinate astfel încât sa formeze un ansamblu coerent;

– date dependente de timp: toate datele dintr-un depozit de date sunt caracteristice unei anumite perioade de timp. Acest lucru este de înțeles având în vedere faptul că managerii și analiștii vor dori să analizeze de exemplu trendul vânzarilor din ultimul an comparativ cu anul precedent;

– date nevolatile: datele sunt stabile in cadrul unui depozit de date. În permanență se adaugă date noi, însă datele existente nu sunt niciodată sterse. Astfel, managerii sau analiștii au în permanență o imagine de ansamblu consistentă a activității companiei.

Această definiție rămâne valabilă și acum, la aproape 25 de ani de când a fost formulată. Există totuși situații în care această definiție nu se aplică în totalitate. De exemplu, un depozit de date specific unui singur subiect sau domeniu este în general cunoscut sub denumire de concentrare de date sau data mart. De asemenea, s-a dovedit că depozitele de date pot fi volatile. Din cauza necesității unor spații de stocare foarte mari pentru depozitele de date, în general în acestea se stochează datele corespunzatoare unui anumit număr de ani. De exemplu, dacă depozitul de date păstrează doar ultimii trei ani de date, în fiecare lună se va arhiva sau șterge cea mai veche lună, în timp ce luna cea mai nouă va fi încărcată.

Ralph Kimball oferă o definiție ceva mai simplă și concisă a noțiunii de depozit de date în cartea sa, “The Datawarehouse Toolkit”. Conform lui Kimball, “un depozit de date reprezintă o copie a datelor tranzacționale, structurată în vederea interogării si analizei informațiilor”. Aceasta este mai degrabă o interpretare funcțională a depozitelor de date. Kimball nu face referire la modul de construire a depozitelor de date ci, spre deosebire de Inmon, se focusează pe funcționalitatea acestora.

Un depozit de date reprezintă deci o bază de date care este menținută separat de bazele de date operaționale ale companiei. Datele din sistemele sursă sunt extrase, curățite, transformate și încărcate în depozitele de date în vederea sprijinirii proceselor decizionale.

Depozitele de date ar putea fi înțelese și prin prisma interferenței mediului economic și al tehnologiilor informatice avansate. Mediul economic actual este din ce în ce mai competitiv, devine tot mai complex și solicită informații elaborate pentru sprijinirea deciziilor strategice. Această tendință poate fi interpretată ca o necesitate tot mai mare de globalizare.

În contextul unui mediu economic dinamic, a apărut necesitatea realizării sistemelor informatice care să sprijine procesul informatic de la orientarea pe operațional (activitatea curentă a firmei care pleacă de la funcțiile întreprinderii și funcțiile conducerii) până la orientarea pe procesul de afacere.

Tehnologiile informatice evoluează în permanență în vederea oferirii de soluții cât mai eficiente de gestionare a volumelor uriașe de date integrate, oferind totodată instrumente pentru analiză, sinteză si detaliere adecvate. Crearea unor instrumente avansate și performante de vizualizare a datelor le va permite utilizatorilor finali să detecteze aspecte care le scăpaseră până acum din vedere. Cele mai importante domenii în care se aplică foarte bine depozitele de date sunt: comerțul, telecomunicațiile, sectorul financiar-bancar, transporturile, asigurările.

În funcție de aria de cuprindere, depozitele de date pot fi clasificate în depozite de întreprindere (Enterprise Warehouse), concentrări de date (Datamarts) și depozite virtuale de date.

Depozitele de întreprindere – înglobează toate informațiile din cadrul întregii companii. Acestea conțin un volum imens de date de ordinul zecilor de terabytes, care se pot afla atât la nivel de detaliu, cât și la nivel agregat. Avantajul evident al acestor depozite de date este cantitatea uriașă de informație disponibilă pentru a fi raportată și analizată. Dezavantajul major însă este reprezentat de costurile mari pentru stocarea și menținerea unor asemenea volume de date precum și timpul mare necesar pentru realizarea activitățile de modelare, proiectare și realizare.

Concentrările de date – reprezintă un subset al volumului de date din companie, specific unui anumit grup de utilizatori, fiind limitat la anumite arii sau subiecte specifice. De exemplu, un datamart poate fi constituit din datele de vânzări ale companiei sau datele de stoc. De obicei datele existente în concentrările de date sunt agregate. Dimensiunile datamart-urilor sunt mult mai mici comparative cu cele ale depozitelor de întreprindere. Implicit costurile de dezvoltare și întreținere sunt mult mai mici.

Depozitul virtual reprezintă un set de viziuni(views) asupra bazelor de date operaționale. Aceste depozite virtuale sunt destul de ușor de implementat, însă necesită capacități suplimentare pe serverele de date.

Un depozit de date conține, de obicei, date agregate, date detaliate și metadate.

Datele agregate – presupun prelucrarea suplimentară a datelor printr-un algoritm de sumarizare la un anumit nivel de agregare (de exemplu grupuri de articole, lună, an, etc.) în vederea obținerii unui timp de răspuns al sistemului mult îmbunătățit. Dezavantajul este creșterea redundanței datelor în interiorul depozitului de date.

Datele detaliate sunt datele la cel mai mic nivel de granularitate din depozitul de date. Acestea sunt relativ recente sau istorice și sunt disponibile la nivel de execuție.

Metadatele reprezintă informații despre datele conținute în depozitul de date. Acestea descriu structura și conținutul depozitului și furnizează referințe directe la date. Metadatele sunt folosite, de regulă, pentru administrarea depozitului de date, întrucât furnizează informații foarte importante despre sursa datelor, algoritmii de sumarizare, statisticile de utilizare etc. Metadatele conțin, de obicei, următoarele categorii de informații:

Detalii privind structura de date din depozit, precum schema depozitului, dimensiunile, ierarhiile sau definițiile datelor derivate

Date privind datele operaționale, care includ informații privind istoricul datelor și secvențele de transformare aplicate asupra lor sau circulația datelor

Date privind mapările sau transformările de la mediul operational la depozitul de date, care includ descrierile interfețelor, partiționarea datelor, filtrarea datelor sau securitatea datelor, spre exemplu

Date referitoare la performanțele sistemului

Date privind termenii economici și definițiile aferente, cunoscute și ca metadata economice

1.2 Obiectivele unui depozit de date:

1. Să furnizeze o singură versiune a adevărului – datele din depozitele de date trebuie să fie consistente pentru a câștiga încrederea utilizatorilor, de aceea sunt supuse unui proces de curățare și integrare. Atâta timp cât deciziile la nivel înalt se vor lua pe baza informațiilor existente în cadrul depozitului de date, veridicitatea și calitatea datelor reprezintă un subiect important. În vederea realizării acestui aspect, este necesară o analiză laborioasă asupra termenilor utilizați în organizație. Un termen trebuie înțeles în același mod de toți utilizatorii la fel cum denumirea lui trebuie sa fie aceeași în cazul în care semnificațiile sunt sinonime.

2. Să asigure că informația este accesibilă – utilizatorii trebuie să poată accesa datele integrate ale organizației, iar prezentarea datelor trebuie să fie intuitivă pentru utilizatori. Aceștia se pot conecta ușor la depozitul de date cu ajutorul unui microcalculator și pot realiza operații de separare sau combinare a datelor după diferite criterii. Acest proces este denumit „slicing and dicing”. Timpul de răspuns la interogări trebuie să fie cât mai mic. Securitatea poate fi realizată fie la nivelul aplicației accesate de utilizator, fie la nivelul serverului bazei de date sau chiar la ambele niveluri.

3. Să integreze cu acuratețe datele istorice – majoritatea datelor analizate capătă sens în momentul în care sunt raportate la datele anterioare. Se pot realiza rapoarte care compară performanțele actuale ale organizației cu cele din anii precedenți sau rapoarte la nivel luna pe perioade îndelungate de timp. Aceste rapoarte permit analiza trendului afacerii, lucru imposibil de realizat în sistemele operaționale. Sistemele tranzacționale sunt focalizate pe corecta înregistrare a tranzacțiilor curente, în timp ce datele istorice vor fi stocate în depozitele de date pentru a putea fi accesate rapid.

4. Să permită diferite niveluri de sinteză și detaliu ale datelor – prin intermediul rapoartelor sau al instrumentelor de interogare OLAP, utilizatorii pot vizualiza informațiile din depozitul de date la diverse niveluri de detaliere. Astfel, timpul de răspuns și efortul necesar pentru colectarea, formatarea și filtrarea informațiilor sunt reduse considerabil.

5. Să separe prelucrările de nivel operațional și analitic – sistemele operaționale și cele analitice trebuie să fie independente din punct de vedere arhitectural. Impactul asupra performanței în cazul în care aceste sisteme ar fi reunite în cadrul unui singur sistem ar putea fi unul major. Având scopuri diferite, aceste sisteme trebuie sa funcționeze pe arhitecturi diferite.

1.3 Realizarea unui depozit de date

1.3.1 Etapele realizării unui depozit de date

Procesul de stocare a datelor in depozite de date presupune un proces îndelungat, complex și laborios caracterizat prin mai multe etape printre care și crearea, popularea și interogarea bazei de date. Complexitatea acestui proces este dată atât de dimensiunile organizației, cât și de complexitatea afacerii și a sistemelor sursă. Voi sublinia în continuare, în linii mari, etapele parcurse în cadrul procesului de realizarea a unui depozit de date:

Identificarea sistemelor sursă reprezintă un prim pas în construirea unui depozit de date. În această etapă se analizează ce fel de date se vor stoca în depozitul de date, se studiază sistemele sursă pentru a se identifica originea datelor necesare deciziilor ce vin în sprijnul afacerii. Acest lucru presupune atât înformațiile recente venite din sistemul OLTP (On-Line Transaction Processing) cât și informațiile istorice pentru perioadele anterioare.

Proiectarea si crearea depozitelor de date sunt considerate a fi pasul următor. Procesul de proiectare si creare a depozitelor de date trebuie să îndeplinească condițiile necesare astfel încât interogarea depozitului de date să faciliteze obținerea informațiilor dorite de către utilizatorul final. Efortul este cu atât mai mare cu cât trebuie înțelese foarte bine nu numai schema bazei de date ce va fi implementată, ci și complexitatea afacerii și necesitățile utilizatorilor. Acest lucru presupune o interacțiune intensă cu utilizatorii finali. Este un lucru cunoscut că schema bazei de date va fi modificată de mai multe ori până când modelul va putea fi considerat stabil. Această etapă este deosebit de importantă, întrucă se analizează ce fel de date se vor stoca în depozitul de date, se studiază sistemele sursă pentru a se identifica originea datelor necesare deciziilor ce vin în sprijnul afacerii. Acest lucru presupune atât înformațiile recente venite din sistemul OLTP (On-Line Transaction Processing) cât și informațiile istorice pentru perioadele anterioare.

Proiectarea si crearea depozitelor de date sunt considerate a fi pasul următor. Procesul de proiectare si creare a depozitelor de date trebuie să îndeplinească condițiile necesare astfel încât interogarea depozitului de date să faciliteze obținerea informațiilor dorite de către utilizatorul final. Efortul este cu atât mai mare cu cât trebuie înțelese foarte bine nu numai schema bazei de date ce va fi implementată, ci și complexitatea afacerii și necesitățile utilizatorilor. Acest lucru presupune o interacțiune intensă cu utilizatorii finali. Este un lucru cunoscut că schema bazei de date va fi modificată de mai multe ori până când modelul va putea fi considerat stabil. Această etapă este deosebit de importantă, întrucât în momentul în care se decide popularea bazei de date cu volume mari de date, anumite componente vor fi foarte greu de modificat, impactul asupra întregii arhitecturi fiind unul destul de mare.

Colectarea datelor companiei din sistemele sursă și stocarea lor in depozitul de date este unul dintre stadiile cele mai importante, de aceea presupune costuri mari în materie de timp și efort. Realizarea acestei etape se face cu ajutorul instrumentelor de extragere, transformare și încărcare a datelor, cunoscute sub denumirea de ETL (Extract, Transform, Load). În momentul de față există peste 50 de instrumente ETL pe piață. Faza de colectare a datelor din sistemele sursă poate ajunge la costuri de milioane de dolari în funcție de mărimea companiei și poate necesita luni sau chiar ani până la finalizare. Acesta este, deci, un proces programat, care se execută cu regularitate în vederea asigurării existenței datelor în depozitul de date pe o perioada stabilită de timp.

Depistarea modificărilor datelor – actualizarea periodică a depozitelor de date din sistemele sursă este îngreunată de procesul de identificare a datelor din sistemele sursă care au suferit modificări de la ultima încărcare. Această etapă este cunoscută sub denumirea de “Depistarea modificărilor datelor (Changed data capture)”. Instrumentele actuale de ETL vin însoțite de mecanisme dedicate acestui pas.

Curățarea datelor – se realizează de obicei în momentul colectării datelor, putându-l considera ca facând parte din pasul de transformare a procesului ETL.Un depozit de date care conține date incorecte este nu numai inutil, dar și foarte periculos. Așa cum știm deja, scopul principa al unui depozit de date este de a sprijini luarea deciziilor în cadrul organizației. Dacă deciziile la nivel înalt se iau în funcție de date incorecte existente în depozitul de date, întreaga companie va suporta consecințele sau poate chiar un eșec complet. Curățarea datelor reprezintă un proces complicat de validare și corectare a datelor acolo unde este cazul, înainte de a le stoca în depozitul de date. Acest proces este cunoscut și sub denumirea de „asigurarea calității datelor”.

Agregarea datelor – de cele mai multe ori, această etapă este realizată în cadrul pasului de transformare a procesului ETL. Există, de asemenea și cazuri în care această etapă este omisă. Depozitele de date pot conține date la nivel de detaliu (o tranzacție individuală), la un nivel agregat (de exemplu o sumă la nivel de lună a datelor) sau o combinație între cele două posibilități. Avantajul de a avea datele agregate este, evident, acela al perfomanței. Majoritatea interogărilor nu se fac la nivel de tranzacție ci la nivel agregat. De exemplu, vom dori sa analizăm vânzările pe fiecare lună a anului în curs mai curând decât o vânzare particulară dintr-o anumită zi. Dezavantajul agregărilor este pierderea informației detaliate. Decizia implementării acestui pas trebuie atent analizată astfel încât refacerea acestei etape poate presupune regândirea si repopularea depozitului de date. Cea mai sigură decizie este de a construi depozitul de date cu un nivel înalt de detaliu, deși costurile de stocare pot fi uriașe.

1.3.2 Procesul de extragere, transformare și încărcare a datelor

Termenul de „ETL” este bine-cunoscut în domeniul depozitelor de date și se referă la procesul de extragere a datelor din sistemele sursă, transformarea și încărcare acestora în depozitele de date.

În momentul în care sursele de date nu pot furniza toate capacitățile și funcționalitățile unui depozit de date sau când datele provin din surse multiple, procesul ETL vine în sprijinul soluționării acestor probleme, fiind conceput să asigure calitatea informațiilor existente la nivelul depozitului de date. Componentele care realizează conexiunea între sistemele operaționale și depozitul de date sunt programe de integrare respectiv transformare. Nici chiar cele mai „corecte” sisteme operaționale nu pot fi copiate în formă identică în depozitul de date. Datele neprocesate din sistemele operaționale nu sunt de obicei inteligibile pentru majoritatea utilizatorilor. În plus, datele operaționale respectă foarte rar structura logică, orientată pe subiect caracteristică unui data warehouse. Mai mult, datele din cadrul diverselor sisteme operaționale sunt reprezentate diferit, pot utiliza diferite coduri de identificare pentru același lucru, concatenează mai multe informații în cadul unui singur câmp și asa mai departe. Datele operaționale pot proveni din diferite surse fizice: fișiere într-un format învechit, baze de date nerelaționale, fișiere de tip flat file. De aceea, datele operaționale trebuie curățate, formatate și transformate înainte de a fi încărcate într-un depozit de date.

Cum datele operaționale sunt transferate din sistemul în care au fost înregistrate în depozitul de date, acestea sunt integrate și transformate, cu ajutorul diverselor instrumente disponibile pe piață, din date specifice aplicației în date specifice întreprinderii.

Procesul ETL include și un pas de curățare a datelor, ceea ce duce la un proces de extragere – curățare – transformare – încărcare a datelor. În continuare voi descrie pașii procesului ETL.

Extragerea datelor

Acest pas reprezintă accesarea și extragerea datelor din sistemele sursă și punerea acestora la dispoziție pentru procesările ulterioare. Principalul obiectiv al pasului de extragere este acela de a accesa toate datele necesare din sistemele sursă folosind cât mai puține resurse posibil. În vederea realizării acestui lucru, pasul de extragere trebuie proiectat si realizat astfel încât să nu impacteze negativ sistemele sursă în termeni de performanță, timp de răspuns sau orice fel de blocare a sistemelor.

Există mai multe modalități de a realiza extragerea datelor din sistemele sursă:

Notificarea actualizărilor – cea mai ușoară modalitate de a accesa și extrage datele ar fi dacă sistemul sursă ar putea transmite o notificare în momentul în care o anumită informație s-a schimbat.

Extragerea incrementală – anumite sisteme nu pot trimite notificări în momentul în care datele s-au schimbat, dar pot identifica datele care au fost modificate și pot asigura extragerea acestor date. În următorii pași ai ETL-ului sistemul va trebui să identifice modificările și să permită propagarea lor. Trebuie avut însă în vedere că, utilizând un proces de extragere zilnică, vom pierde datele care au fost șterse.

Extragerea totală – există sisteme în care nu se pot identifica deloc datele care au suferit modificări fapt pentru care singura modalitate de a extrage datele din acestea este de a folosi o extragere totală. Extragerea totală presupune existența unei copii exacte a datelor disponibile la ultima extragere astfel încât să poată fi identificate modificările. Prin extragerea totală se pot trata si datele care au fost șterse.

Un aspect important, indiferent că vorbim despre extrageri incrementale sau totale, este frecvența cu care vor fi extrase datele din sistemele sursă. O atenție deosebită trebuie acordată mai ales în cazul extragerilor totale unde volumele de date pot atinge zegi de gigabytes.

Curățarea datelor

Pasul de curățare a datelor este unul dintre cele mai importante din cadrul procesului ETL întrucât asigură calitatea datelor din depozitul de date. Datele existente în depozitul de date trebuie să se afle la cea mai înaltă calitate posibilă. Datele trebuie să fie acurate, relevante, complete și concise. Un depozit de date care conține informații strategice de încredere devine o resursă foarte valoroasă în cadrul companiei pentru persoanele din direcția decizională. Dacă utilizatorii descoperă discrepanțe de date sau date alterate necorespunzător, depozitul de date nu va beneficia de încrederea utilizatorilor, va fi ignorat și va eșua. Cazul poate mai rău este acela în care utilizatorii nu descoperă datele incorecte și iau decizii pe baza unor date eronate, ceea ce poate duce la decizii eronate și chiar la falimentul companiei.

Curățarea datelor presupune anumite reguli standard, precum:

Asigurarea unicității anumitor identificatori (de exemplu sexul clienților poate suferi diverse forme: Masculin / Feminin, M / F, Bărbat / Femeie)

Convertirea NULL-urilor în forme standardizate, de exemplu „Indisponibil”

Formatarea numerelor de telefon sau codurilor poștale la o formă standard

Validarea câmpurilor dedicate adreselor, formatarea acestora într-o denumire potrivită (de exemplu Str. / Șos./ Strada/ Șoseaua/)

Validarea câmpurilor dedicate adreselor astfel încât să corespundă realității (Țara/Oraș/Județ)

Transformarea datelor

În pasul de transformare este aplicat un set de reguli pentru a transforma datele din sursă în destinație. Acesta include convertirea măsurilor la dimensiunile existente astfel încât mai târziu datele să poată fi asociate corect. Tansformarea datelor presupune joinarea datelor din diverse surse, generarea agregărilor de date, generarea cheilor surogate, calcularea măsurilor derivate și aplicarea unor reguli avansate de validare.

În cadrul acestui pas se pot realiza mai multe tipuri de transformări, dintre care amintim:

Generarea agregărilor de date – de exemplu sume după anumite perioade de timp (lună, an, etc.)

Aplicarea unui set de reguli de validare a datelor ceea ce poate presupune eliminarea datelor invalide

Determinarea unor măsuri sau câmpuri noi calculate pe baza celor existente deja

Concatenarea datelor din diverse coloane

Ordonarea datelor

Eliminarea duplicatelor – acestea pot afecta foarte mult calitatea datelor

Denormalizarea datelor

Adăugarea informației de timp

Identificarea valorilor implicite

Reconcilierea datelor provenind din surse multiple

Selectarea unor anumite rânduri sau coloane relevante pentru deciziile afacerii

Transpunerea sau pivotarea – transformarea anumitor coloane în linii sau invers

Interpretarea anumitor indicatori – de exemplu sistemul sursă poate reprezenta genul feminin prin valoarea 1, genul masculin prin valoarea 2, în timp ce în depozitul de date acestea să fie reprezentate prin indicatorii „F” respectiv „M”

Încărcarea datelor

Pasul de încărcare a datelor este necesar să se realizeze respectând atât corectitudinea datelor cât și utilizarea câtor mai puține resurse posibil. Rolul de destinație în pasul de încărcare este jucat în general de o bază de date. Pentru a asigura o încărcare eficientă a datelor recomandările sunt de a dezactiva diferitele constrângeri sau indecși înainte de începerea încarcării și de a îi activa la loc când încărcarea s-a finalizat.

Așa cum se întâmplă cu orice aplicație, există posibilitatea ca procesul ETL să se termine în eroare. Acest lucru poate fi cauzat de date lipsă din sistemele sursă, valori lipsă în tabelele referențiate sau chiar erori de conexiune sau întreruperi de energie electrică. De aceea, în momentul în care proiectăm un proces ETL, trebuie sa avem în minte un sistem de recuperare a datelor în caz de eroare. Astfel, ar trebui sa putem controla cel puțin anumite faze independent de celelalte. De exemplu, dacă eroarea este generată în pasul de transformare, nu este necesar sa mai executăm pasul de extragere a datelor. Acest lucru se poate realiza ușor prin implementarea unei zone intermediare de stocare, numită Staging. Zona de stocare sau Staging presupune o zonă în care vor fi stocate datele imediat după extragere astfel încât să poată fi citite mai apoi de următoarea etapa de procesare. În zona de Staging se pot stoca și rezultatele intermediare ale procesării, însă aceasta zonă trebuie să fie accesată doar de procesul ETL. Nu trebuie să fie disponibilă pentru alte scopuri, în special utilizatorii finali nu trebuie să aibă acces la aceste date întrucât ele pot fi incomplete sau în curs de procesare, lucru ce ar putea duce la confunzii grave.

1.3.3 Surse de date pentru depozitele de date

Sursele de date ale unui depozit de date pot fi constituite din diverse componente ale sistemului informatic al companiei. Astfel, putem identifica următoarele posibile surse de date:

Baze de date – sistemele tranzacționale (OLTP – On-Line Transaction Processing)

Fisiere text, excel, etc

Aplicații de calcul tabelar

În contextul existenței mai multor surse de date eterogene, devine evidentă importanța unui centru de colectare, stocare și organizare a datelor provenite din diferite surse. Prin centralizarea datelor istorice din surse diferite se asigură un ansamblu complet și coerent de informații pe baza cărora se vor putea lua deciziile în cadrul organizației. Stocarea datelor istorice în depozitele de date permite eliberarea unui volum mare de date în cadrul sistemelor tranzacționale, acestea putând fi șterse sau arhivate la perioade mai scurte de timp.

Sistemele tranzacționale de date însumează uneori chiar în timp real toate tranzacțiile zilnice de la nivelul unei companii, precum operații în cadrul organizației (vânzări, achiziții, stocuri, listare și activare articole, decontări, contabilitate, introducere clienți noi în sistem, etc.), log-uri ale serverelor web cu cautările utilizatorilor, cercetări de piață, date din terțe părți precum datele demografice. În general sistemele sursă însă păstrează datele pe perioade scurte, de obicei o lună. Exploatarea acestor date însă poate furniza informații business foarte utile, pe baza cărora se pot realiza rapoarte importante. Acestea pot avea ca scop analiza competitorilor, a pieței sau a celor mai bune zone de vânzare de exemplu.

Din punct de vedere arhitectural, sistemele tranzacționale și depozitele de date sunt separate, motiv pentru care utilizatorii sau dezvoltatorii depozitelor de date nu au un control asupra structurii datelor din sistemele sursă. De aceea pot surveni diferite neconcordanțe între sursele de date și depozitele de date. Acestea trebuiesc tratate prin diverse mijloace fie la nivelul surselor de date fie la nivelul depozitelor de date.

Există însă și cazuri în care sistemele tranzacționale nu înregistrează anumite informații, în lipsa implementării unui mecanism specific. În acest caz, datele pot fi stocate de exemplu prin intermediul fișierelor excel. Cu toate acestea, este necesară și colectarea acestor informații pentru a obține tabloul complet al datelor companiei. În lipsa unui sistem tranzacțional de procesare a acestor informații, în soluția depozitelor de date se poate dezvolta un proces de colectare inclusiv a acestor date pentru a evita pierderea sau omiterea acestora.

Eterogenitatea datelor din diversele sisteme sursă poate conduce la probleme serioase legate de calitatea datelor cum ar fi: selecția nepotrivită a datelor relevante, lipsa unor dependențe corecte între sursele de date, lipsa unor proceduri de validare a datelor, formatarea inconsistentă a datelor sau chiar multiplicarea datelor prin extragerea acestora din surse diferite. De asemenea, unele fisiere text sau excel sunt obținute prin procesări manuale, deci datele pot fi compromise datorită erorii umane.

Un sistem tranzacțional este caracterizat de următoarele trăsături:

Este orientat pe client spre deosebire de depozitele de date care sunt orientate pe piață;

Este utilizat pentru procesarea tranzacțiilor și a interogărilor zilnice;

Datele conținute de un sistem tranzacțional manipulează date curente care se află la un nivel detaliat spre deosebire de cele dintr-un depozit de date care permite accesarea datelor istorice și gestionarea mai multor niveluri de granularitate;

Schema de acces permite operații de tip citire/scriere față de depozitele de date în care în general accesul este limitat la cel de citire;

Scopul principal al sistemelor tranzacționale este acela de a culege datele

Performanțele bazei de date trebuie sa fie ridicate, disponibilitatea sistemului este în general critică – de exemplu sistemul care înregistrează vânzările de la casele de marcat trebuie să fie funcțional pentru a evita pierderea unor date importante

Numărul de înregistrări accesate este relativ mic, de ordinul miilor, comparativ cu cel al depozitelor de date unde numărul de înregistrări este de ordinul milioanelor.

1.3.4 Analiza multidimensională a datelor

Din momentul în care datele au fost stocate și integrate în depozitul de date, se dorește exploatarea și manipularea acestora la performanțe maxime. În sprijinul acestor necesități vine tehnologia OLAP (On-Line Analytical Processing) care asigură accesul rapid la date, timpul de răspuns fiind foarte bun. Tehnologia OLAP se bazează pe metode complexe de analiză și procesare a datelor. Datele sunt reprezentate sub formă de dimensiuni și măsuri, fiind deja agregate într-o structură multidimensională cunoscută sub denumirea de cub.

Instrumentele OLAP permit utilizatorilor să analizeze datele într-un mod interactiv, din unghiuri diferite. Există trei operații de bază ale tehnologiei OLAP: consolidarea (roll-up), parcurgerea în jos (drill-down), secționarea (slicing) și schimbarea perspectivelor (dicing). Consolidarea presupune agregarea datelor care pot fi computate în una sau mai multe dimensiuni. Prin contrast, parcurgerea în jos, este o modalitate prin care utilizatorii pot ajunge să vizualizeze datele la nivel de detaliu. Secționarea și schimbarea perspectivelor reprezintă modalitatea de a extrage seturi specifice de date din cubul OLAP și vizualizarea acestor „felii” din diferite unghiuri de analiză.

Aceste perspective sunt denumite dimensiuni (de exemplu: dimensiunea timp, dimensiunea articole, dimensiunea clienți, etc.). Măsurile sunt reprezentate de acele entități care pot fi agregate în funcție de dimensiunile existente (de exemplu: vânzari, profit, etc.).

Metadata cubului este in mod normal creată pe baza unei scheme stea sau fulg de nea sau constelații de fapte. Măsurile sunt derivate din tabele de tip „fact”, în timp ce dimensiunile sunt derivate din tabele de tip „dim”. O dimensiune furnizează informațiile relevante despre o anumită măsură. De exemplu o anumită valoare a măsurii vânzărilor capătă semnificație în contextul unor dimensiuni: vânzările la nivel de regiune, vânzările la nivel de an, vânzările la nivel de magazin.

ANALIZA ȘI PROIECTAREA UNUI FLUX DE CURĂȚARE ȘI INTEGRARE A DATELOR DIN SURSE ETEROGENE

2.1 Analiza economică pentru proiectarea fluxului ETL

Așa cum am menționat în paginile anterioare, un proces ETL poate fi înțeles ca o proprietate a unui depozit de date, fiind instrumentul principal cu ajutorul căruia depozitul de date este populat. Analiza și proiectarea unui flux ETL se realizează în strânsă legătură cu schema și necesitățile depozitului de date pentru care va furniza date.

Procesele ETL pot fi cu adevărat costisitoare atât în materie de bani cât și in materie de timp. Pe lângă faza de dezvoltare propriu zisă, va exista întotdeauna necesitatea unui suport constant în vederea întreținerii procesului ETL. Este esențial însă ca modelarea fluxului ETL să se realizeze elegant și coerent astfel încât să fie facilitat lucrul și procesul de întreținere al acestuia. De asemenea, trebuie avute în vedere posibilitățile de schimbare viitoare, iar dezvoltarea fluxului trebuie realizată în așa fel încât posibilele schimbări să se poată implementa cât mai ușor și cu un impact cât mai mic asupra soluției existente. În acest mod sunt evitate erorile, necesitatea migrării datelor sau riscurile de a afecta procesul curent.

2.2 Prezentarea firmei și a activității acesteia

În continuare vom considera pentru studiu o societate comercială care are ca specific vânzările cu amănuntul. Vom face referire la acest domeniu prin denumirea sa din engleză, și anume „retail”. Vom vorbi, deci, despre un lanț de magazine cu sedii în anumite orașe din România. În București primul magazin se deschide în anul 2012, urmând ca și în alte orașe să se deschidă treptat câte un magazin, pe măsură ce business-ul evoluează.

Procesul de vânzare cu amănuntul se desfășoară în două etape. O primă etapă este aceea în care societatea achiziționează produsele de la diferiți furnizori. Cea de-a doua etapă este punerea la vânzare a produselor achiziționate de la furnizori. Profitul firmei vine atât din diferența dintre prețul de achiziție al produselor cât și din contractele încheiate cu furnizorii prin care aceștia plătesc anumite sume de bani, cu scopul obținerii unor condiții mai profitabile. Astfel, pentru o mai bună poziție la raft sau pentru o mai buna promovabilitate în interiorul magazinului, furnizorii sunt dispuși să ii acorde companiei un venit suplimentar.

Articolele sau produsele comercializate pot fi de două tipuri: articole livrate de furnizorii interni care poarta marca proprie a firmei și articole livrate de furnizorii externi. Articolele sunt clasificate în funcție de arie, categorie și subcategorie. Aria este reprezentată de două mari categorii: FOOD – produsele alimentare și NON-FOOD – produsele nealimentare.

Accesul în magazine se face pe baza unui card de client. Fiecare client este identificat în sistem printr-un cod unic. Necesitatea informațiilor despre clienți a devenit cu atât mai mare cu cât afacerea a evoluat. Astfel, având informațiile referitoare la clienți, compania poate realiza diverse studii sau analize de data mining pentru a înțelege cât mai bine preferințele și nevoile clienților în funcție de diverși factori. Aceste studii pot aduce informații prețioase managerilor și analiștilor în vederea luării de decizii care să sporească profitabilitatea afacerii.

Pentru înregistrarea activităților curente din cadrul magazinelor sunt folosite sisteme tranzacționale specifice domeniului vânzărilor cu amănuntul. Astfel, pentru a înregistra vânzările se folosește sistemul POS (Point Of Sale). Înregistrarea clienților în sistem se face prin intermediul unei aplicații CRM (Customer Relationship Management). Fiecarei zone a business-ului îi corespunde un sistem tranzacțional prin intermediul căruia datele sunt înregistrate pentru a fi transmise mai departe către analiza.

2.3 Descrierea informațiilor care vor fi stocate și analizate

Un indicator important pentru orice societate comercială este acela al profitului înregistrat la nivel de magazin sau chiar lanț de magazine în funcție de diverse aspecte. În vederea obținerii unei imagini consistente asupra acestui indicator și asupra părților sale componente, ne vom concentra, pentru exemplificarea fluxului de curățare și integrare a datelor, pe acest segment constituent al depozitului de date.

Astfel, în depozitul de date sunt stocate informațiile referitoare la achizițiile companiei. Achizițiile reprezintă informațiile legate de livrările făcute de furnizori: articolele achiziționate, cantitatea, furnizorul care le-a livrat, data achiziției și prețul de achiziție.

Cum menționam, compania se ocupă cu revinderea produselor achiziționate de la furnizori. Este necesară deci înregistrarea vânzărilor. O vânzare conține informații referitoare la articolul vândut, furnizorul corespondent, data vânzării, indicatorul promoțional (dacă articolul s-a aflat la promoție sau nu), prețul de vânzare.

Există, de asemenea, un venit suplimentar realizat din firmă prin contractele încheiate cu furnizorii, contracte ce stabilesc anumite condiții de promovabilitate pentru articolele unui furnizor precum și costurile pe care acesta trebuie să le plătească în schimb. Venitul suplimentar se încasează lunar la nivel de furnizor.

Toate aceste măsuri sunt stocate în depozitul de date pe o perioadă de trei ani. Această perioadă este suficientă pentru a analiza direcția în care se îndreaptă compania și pentru a stabili măsurile potrivite pentru a aduce îmbunătățiri afacerii. Pentru ca aceste măsuri să capete un înțeles cât mai semnificativ pentru afacere, se înregistrează informații legate de articole, categorii, furnizori, magazine și clienți.

2.3.1 Diagrama cazurilor de utilizare

Diagrama cazurilor de utilizare este o reprezentarea a interacțiunii utilizatorilor cu sistemul, evidențiind relația dintre utilizatori și diferitele acțiuni sau evenimente care pot fi declanșate de aceștia. Utilizatorul este reprezentat prin noțiunea de „actor”. O diagramă a cazurilor de utilizare poate reprezenta mai multe tipuri de actori, indicând funcționalitățile sistemului ce pot fi accesate de către aceștia.

Actorul principal în cazul depozitelor de date este administratorul bazei de date. De asemenea, pe partea de raportare se interacționează cu utilizatorii finali care extrag rapoartele de analiză din sistemele OLAP.

Administratorul bazei de date are responsabilități ce includ crearea utilizatorilor, stabilirea și acordarea drepturilor pentru aceștia, asigurarea unui backup periodic al datelor pentru a evita situațiile neplăcute de pierdere a datelor. Din atribuțiile administratorului de baze de date fac parte și monitorizarea și întreținerea bazei de date în parametri corespunzători de funcționare. De asemenea, tot administratorul bazei de date este cel care rulează joburile de extragere din sistemele sursă și încărcare a lor în depozitul de date. Atribuțiile în cadrul sistemului OLAP sunt similare.

În diagrama cazurilor de utilizare prezentată mai jos, avem de-a face cu trei subsisteme cu care actorul principal, administratorul bazei de date, interacționează: subsistemul surselor de date, subsistemul depozitului de date ( DWH sau datawarehouse) și ETL și subsistemul OLAP.

În cadrul subsistemului surselor de date, administratorul execută joburile de extragere a datelor care vor fi mai departe folosite pentru inserare în tabelele finale ale depozitului de date. De observat că, în acest sistem administratorul are doar drepturi de citire a datelor, de unde rezultă doar un caz de utilizare posibil ca și interacțiune cu acest subsistem.

Subsistemul denumit „DWH și ETL” este reprezentat de baza de date propriu zisă în care vor fi integrate datele extrase din sistemele sursă. Am inclus în acest subsistem și procesul ETL, întrucât cele două lucrează în strânsă legătura pentru a asigura disponibilitatea datelor necesare pentru analiză. Procesul ETL este cel responsabil de extragerea, transformarea și încărcarea datelor.

Figura 1. Diagrama cazurilor de utilizare

2.3.2 Diagrama de activitate a procesului ETL

Diagramele de activitate sunt reprezentări grafice ale unor fluxuri de lucru în care se evidențiază pașii activităților ce se pot desfășura condițional, în paralel sau secvențial. Acestea modelează atât procesele organizaționale cât și pe cele computaționale.

Diagrama de activitate pentru fluxul de curățare și integrare a datelor utilizat de noi este prezentată mai jos:

Figura 2. Diagrama de activitate a procesului ETL

După ce sunt extrase din sistemele sursă datele suportă o validare inițială înainte de inserarea lor în zona de staging. În cazul în car apar inconsistențe de date ce nu pot fi tratate, ETL-ul va fi întrerupt cu un mesaj de eroare. Dacă datele sunt corecte, atunci acestea vor fi inserate mai departe în staging. Pasul următor este de a curăța și valida datele în conformitate cu tabelele destinație. În cazul în care validarea datelor eșuează, procesul ETL va fi întrerupt cu un mesaj de eroare. Dacă curațarea și validarea datelor s-au efectuat cu succes, atunci se verifică dacă trebuiesc efectuate transformări pe seturile de date. Dacă există transformări de efectuat, se efectuează aceste transformări, datele sunt încărcate în tabelele destinație ale depozitului de date și procesul ETL este finalizat cu succes. Dacă nu există transformări de efectuat, atunci se trece direct la scrierea datelor în depozitul de date și se finalizează procesul ETL cu succes.

2.4 Proiectarea surselor de date

Sursele de date pentru un depozit de date pot fi atât baze de date relaționale cât și fișiere excel, flat files sau alte tipuri de fișiere. În lucrarea de față am ales drept surse de date pentru exemplificare bazele de date și fișiere de tip excel.

Cum menționam mai sus, sursele de date tranzacționale sunt de fapt baze de date relaționale. Fiecare tip de tranzacție posibilă în sistem este înregistrată în sistemul tranzacțional corespunzător, unde este stocată pentru o perioadă de maximum două luni. Datele referitoare la clienți sunt întreținute în sistemul CRM, datele referitoare la vânzări sunt menținute în sistemul POS. Pentru o gestiune mai bună a informațiilor, compania a decis existența a două sisteme reprezentate de două baze de date diferite care vor corespunde celor două categorii de informații care fac subiectul lucrării noastre.

Astfel, vom avea o bază de date sursă numită MasterData care conține informațiile despre clienți, articole, categorii, furnizori și magazine. Tabelele corespunzătoare sunt md.clienți, md.articole, md.furnizori, md.magazine.

Cea de-a doua bază care va constitui sursa noastră de date este numită MovementData și conține informațiile de vânzări, achiziții și venit suplimentar. Tabelele corespunzătoare sunt mov.vânzări, mov.achiziții, mov.venit_suplimentar.

Există o a treia sursă din care depozitul de date îsi extrage datele și anume fișiere excel transferate către o locație cunoscută. În momentul achiziției de produse de la un anumit furnizor, acesta poate încheia un contract cu magazinul astfel încât produsele sale să fie mai bine promovate în schimbul unei sume de bani. De exemplu, un furnizor conștientizează că o mai bună poziție la raft îi poate vinde articolele mai repede și chiar în cantități mai mari decât dacă s-ar afla într-un loc mai puțin vizibil. O parte din aceste încasări suplimentare sunt înregistrate prin sistemul de Bonus, însă există și tipuri de contracte care nu sunt înregistrate în sistemul tranzacțional din motive arhitecturale curente. De aceea, pentru a evita o imagine incompletă a venitului suplimentar înregistrat, a fost necesară soluționarea acestei probleme prin găsirea unei modalități de a încărca inclusiv aceste date în depozitul de date. În momentul în care fișierele sunt găsite în această locație, se încearcă prelucrarea și încărcarea lor de către procesul fluxul de curățare și încărcare a datelor. Formatul fișierelor este unul prefedinit așa încât să se evite erorile cauzate de formatarea fișierului. Denumirea fișierelor este, de asemenea, una standard. Fișierele vor avea numele de „import_file_x” unde x devine numărul curent al fișierului.

2.5 Proiectarea depozitului de date

Gândirea și implementarea unui depozit de date este un proces foarte complex care trebuie tratat cu cea mai mai importanță pentru a asigura reușita proiectului. O trăsătură importantă a unui depozit de date este flexibilitatea. Încă din faza incipientă a proiectului trebuie avut în vedere că sistemul trebuie să permită adăugarea noilor funcționalități sau opțiuni într-un mod cât mai puțin invaziv asupra arhitecturii deja existente pentru a evita daunele majore. Arhitectura depozitului de date și tehnologiile folosite trebuie să permită deopotrivă cu ușurință modificările ce pot surveni ulterior. Procesul de întreținere și de modificări ulterioare poate deveni foarte costisitor dacă nu sunt respectate recomandările cu privire la simplitatea și scalabilitatea întregului sistem.

Proiectarea unui depozit de date este diferită față de proiectarea unui sistem tranzacțional. În timp ce scopul unui sistem tranzacțional este acela de a înregistra volume mari de înregistrări noi sau modificări ale datelor deja înregistrate, scopul unui depozit de date este de a stoca și organiza volume mari de date pentru a putea fi folosite cu ușurință de către sistemele de raportare utilizate de către persoanele implicate în procesul decizional al companiei. De asemenea, în momentul în care apare necesitatea unui nou sistem tranzacțional, utilizatorii știu exact care sunt funcționalitățile de care au nevoie pentru a asigura o înregistrare corectă și completă a tranzacțiilor companiei. Acest lucru nu este valabil și pentru utilizatorii unui depozit de date. Pe măsură ce depozitul de date își va demonstra utilitatea și acuratețea, utilizatorii vor realiza că au nevoie de funcționalități de analiză din ce în ce mai complexe sau chiar vor solicita aducerea de informații suplimentare din sistemele sursă.

Diferențe majore între cele două tipuri de sisteme apar și prin prisma stategiilor de backup și restore. Cum majoritatea datelor dintr-un depozit sunt date istorice care nu se modifică, atunci nu sunt necesare backup-uri periodice ale acestor date, spre deosebire de sistemele tranzacționale unde schimbările sunt frecvente și deci și backup-urile trebuiesc realizate cu o frecvență sporită.

Scopul principal pe care trebuie să îl îndeplinească un depozit de date este acela de a oferi accesul rapid la informația necesară pentru analiză și raportare. Pentru a realiza acest lucru, în proiectarea depozitului de date se folosește modelarea dimensională care permite o organizare eficientă a datelor astfel încât interogările să fie cât mai performante chiar și când vorbim de agregări pe volume foarte mari de date.

În continuare va fi prezentat depozitul de date folosit în această lucrare atât din punct de vedere arhitectural, cât și funcțional.

Depozitul de date este reprezentat de o baza de date relațională care va stoca informațiile activității de vânzare cu amănuntul desfășurată de compania noastră. Această bază de date este denumită Retail.

La nivelul bazei de date avem mai multe scheme care corespund scopurilor îndeplinite de tabelele existente:

Schema stg – reprezintă zona de staging în care sunt inserate inițial datele extrase din sistemele sursă;

Schema dim – caracterizează tabelele de dimensiuni din depozitul de date;

Schema fact – caracterizează tabelele de măsuri din depozitul de date;

Schema nok – este folosită pentru tabelele ce conțin datele care nu au putut fi prelucrate și inserate în tabelele finale. De exemplu, în tabelele fact se inserează doar acele înregistrări care corespund unor informații existente în tabelele de dimensiuni. În cazul în care am primit, de exemplu, vânzări pe un articol care nu a fost listat încă în sistemul MasterData, atunci înregistrările respective vor fi redirectate către tabela nok.vânzări pentru a evita întreruperea procesului ETL din cauza erorilor de referențiere a cheilor. Cazurile acestea sunt nu numai posibile dar și frecvente, având sisteme tranzacționale independente. De aceea, în momentul proiectării trebuie avute în vedere și tratate corespunzător. Aceste tabele vor fi reprocesate la încărcarea următoare în eventualitatea că între timp informațiile lipsă au fost primite din sistemul sursă și se pot încărca în tabelele destinație.

În cadrul acestui proiect vom realiza, pe lângă depozitul de date și fluxul de curățare și integrare a datelor din sistemele sursă, și un sistem OLAP care să permită analiza și raportarea figurilor existente în depozitul de date. Cubul OLAP reprezintă de fapt o bază de date multidimensională care vine în sprijinul necesității utilizatorilor finali de performanță deosebită care să le permită accesul rapid la date. De asemenea, cubul de date le pune la dispoziție utilizatorilor un instrument intuitiv, ușor de utilizat și foarte flexibil prin care să își extragă diverse rapoarte în funcție de criteriile pe care și le stabilesc.

Mai jos se regăsește o reprezentare conceptuală a arhitecturii ce se va evidenția în cadrul lucrării de față.

Figura 3. Schema conceptuală a arhitecturii proiectului prezentat

Depozitul de date a fost construit cu ajutorul unei scheme de tip fulg de nea. O asemenea schemă poate fi identificată prin tabelele de fact situate central care sunt conectate la diverse dimensiuni. Metoda de tip fulg de nea presupune normalizarea dimensiunilor, astfel încât dimensiunile la care sunt conectate tabelele fact se pot conecta la rândul lor la alte dimensiuni. Rezultă astfel o aranjare logică a tabelelor a cărei diagramă entitate – legătură se aseamănă cu aceea a unui fuld de nea.

Mai jos se poate analiza diagrama entitate – legătură pentru baza de date Retail a depozitului nostru de date.

Figura 4. Diagrama entitate – legatură a bazei de date Retail

Diagrama este compusă din tabelele fact și dim disponibile în depozitul nostru de date. Pe lângă acestea, în depozit se mai regăsesc și tabele intermediare care ajută la diverse transformări sau calculații sau tabele de logare a activităților de încărcare a datelor. Există de asemenea și tabelele în care sunt păstrate înregistrările care nu pot fi inserate în tabelele fact din diverse motive.

În continuare voi prezenta tabelele folosite precum și componența și semnificația câmpurilor folosite.

Tabela dim.articole – conține informații despre articolele comercializate de lanțul nostru de magazine. Câmpurile reprezentative pentru această tabelă sunt:

id_articol: fiecare articol este identificat printr-un cod unic în sistem

nume_articol: numele articolului

id_subcategorie: subcategoria din care face parte articolul

marca_proprie_ind: indicator care ne spune dacă articolul este marcă proprie a companiei

Tabela dim.categorii – aici sunt păstrate informațiile despre ierarhiile de categorii, subcategorii și aria corespunzătoare. Avem deci, următoarele informații:

id_subcategorie: codul unic al subcategoriei din care face parte un articol

nume_subcategorie: denumirea subcategoriei

id_categorie: codul unic al unei categorii care reunește mai multe subcategorii

nume_categorie: numele categoriei

aria: aria din care face parte o anumită categorie F = FOOD(produse alimentare), NF = NON-FOOD (produse nealimentare)

Ierarhia articole – categorii este reprezentată în figura de mai jos:

Figura 5. Ierarhia articole – categorii a depozitului de date

Tabela dim.magazine conține informații referitoarele la magazinele deschise de către organizația noastră în întreaga țară. Astfel, vom regăsi aici informații referitoare la codul magazinului (id_magazin), numele magazinului (nume_magazin), orașul respectiv județul în care este deschis magazinul, precum și data la care magazinul a fost deschis (data_deschidere). Avem și informații suplimentare precum telefonul, adresa e-mail sau numărul de angajați ai magazinului.

Tabela dim.furnizori stochează informațiile relevante despre furnizori, respectiv: codul unic al furnizorului asignat din sistemul sursă (id_furnizor), numele furnizorului (nume_furnizor), indicator care ne arată dacă un furnizor este un furnizor intern sau un furnizor extern (furnizor_intern_ind). Există și informații suplimentare ale furnizorilor precum adresa de e-mail, numărul de telefon, site-ul web al furnizorului precum și orașul respectiv județul din care provin.

Tabela dim.clienți conține informațiile despre clienții magazinelor. Fiecare client este identificat în sistem printr-un cod unic de înregistrare (id_client). Informații despre numele și prenumele clientului sunt de asemenea păstrate, precum și sexul clientului, numărul de telefon, adresa de e-mail, orașul, județul și adresa clientului. Există de asemenea un indicator care ne spune dacă clientul este activ sau inactiv. Un client este considerat inactiv dacă nu a mai efectuat tranzacții în cadrul magazinului în ultimul an.

Tabela dim.time conține informații referitoare la diverse perioade de timp. Dintre acestea amintim: codul lunii (id_luna) care reprezintă o modalitate de a identifica mai ușor o lună. Formula din spatele acestui cod este id_luna = an * 100 + luna. Avem, de asemenea, informația de dată la nivel de zi în format „YYYY-MM-DD”, precum și ziua_anului (numărul zilei din an), ziua_săptămânii (numele zilei din saptămână), anul, luna ca numărul lunii din an sau sub formă de denumire (Ianuarie, Februarie, etc.)

Tabelele fact conțin informațiile despre măsuri, adică valorile efective ale vânzărilor realizate, ale achizițiilor încheiate sau al venitului suplimentar câștigat. Utilizatorii finali sunt interesați de aceste rezultate pentru că le oferă o imagine foarte bună despre direcția în care se îndreaptă compania, măsurile care se pot lua pentru a îmbunătăți afacerea și pentru a dezvolta profitabilitatea acesteia. Desigur, măsurile capătă înțelesuri multiple și nuanțe diferite în momentul în care sunt analizate în funcție de dimensiunile disponibile în depozitul de date. În acest fel, se va constata care sunt articolele care se vând cel mai bine, care categorii sunt cel mai puțin profitabile deși poate ocupă un spațiu mai mare în cadrul magazinului. Se pot observa și trendurile sezoniere, astfel încât se poate decide care sunt articolele care aduc cel mai mare profit în funcție de anotimp. Există o mulțime de rapoarte care se pot realiza pe baza datelor existente în depozit și care furnizează informații prețioase despre întreaga activitate a companiei.

Tabela fact.vânzări este, așa cum spune și numele, tabela în care sunt stocate vânzările zilnice realizate de lanțul de magazine. Câmpurile reprezentative pentru această tabelă sunt următoarele:

id_articol: vânzările sunt realizate la cel mai mic nivel de detaliu, adică la nivelul de articol. De aceea, identificatorul articolului este prezent sub formă de coloană în tabela de vânzări;

id_furnizor: legătura dintre un articol și un furnizor se face doar prin movement-ul realizat. Astfel, se consideră că un furnizor livrează un anumit articol doar dacă în una din tabelele de vânzări sau achiziții există o înregistrare care asociază furnizorul cu articolul respectiv. În tabela fact.vânzări vom păstra deci și identificatorul furnizorului alături de cel al articolului;

id_magazin: identificatorul magazinul în cadrul căruia s-a realizat vânzareal;

id_client: codul clientului care a efectuat tranzacția;

id_luna: acest câmp este introdus prin intermediul ETL-ului. Reprezintă o modalitate mai simplă de a reprezenta luna corespunzătoare unei vânzări. Codul lunii este obținut după formula YYYY * 100 + MM;

promotie_ind: indicator care ne spune dacă articolul era la promoție în momentul vânzării;

data_vânzare: data la nivel de zi a vânzării;

cantitate_buc: cantitatea de produse vândute în număr de unități

cantitate_kg: cantitatea de produse vândute în număr de kilograme. Un articol se poate vinde fie ca și unitate, fie la kilogram, în funcție de natura acestuia. Dacă articolul este vândut la bucată, atunci câmpul cantitate_kg va fi NULL și viceversa;

preț_bucată_fără_tva: prețul înregistrat în sistemul sursă este cel fără tva, pe o singură unitate (sau kilogram) de produs;

preț_bucată_cu_tva: prețul cu tva inclus pe unitate (sau kilogram) de produs. Acest preț nu este disponibil în sistemul sursă, el fiind adăugat prin intermediul fluxului ETL prin formula: preț_bucată_cu_tva = preț_bucată_fără_tva * 24% + preț_bucată_fără_tva

preț_fără_tva: prețul total de vânzare fără tva corespunzător întregii cantități cumpărate. Acest câmp nu este disponibil în sistemul sursă, deci este adăugat prin intermediul procesului ETL. Formula folosită în cadrul ETL-ului este: preț_fără_tva = COALESCE (cantitate_buc, cantitate_kg) * preț_bucată_fără_tva

preț_cu_tva: prețul total de vânzare cu tva inclus corespunzător întregii cantități cumparate. Acest câmp nu este disponibil în sistemul sursă, fiind adăugat prin intermediul procesului ETL prin formula: preț_cu_tva = COALESCE (cantitate_buc, cantitate_kg) * (preț_bucată_fără_tva * 24% + preț_bucată_fără_tva)

Tabela fact.achiziții conține înregistrările articolelor cumpărate de către companie de la furnizorii cu care lucrează în scopul de a le revinde mai departe în cadrul magazinelor pentru obținerea profitului. Câmpurile acestei tabele sunt următoarele:

id_articol: compania cumpără de la furnizor articole livrate de acesta; codul articolului este stocat deci în tabela de fact.achiziții;

id_furnizor: codul furnizorului de la care s-au achiziționat produsele;

id_magazin: codul magazinului în care s-au livrat articolele achiziționate;

cantitate_buc: articolele sunt achiziționate în anumite cantități, disponibile la bucată sau la kilogram în funcție de natura produsului; Acest câmp păstrează cantitatea achiziționată dintr-un produs disponibil la bucată;

cantitate_kg: cantitatea achiziționată dintr-un produs disponibil la kilogram;

preț_bucată: prețul de achiziție înregistrat în sistemul sursă, pe o singură unitate (sau kilogram) de produs;

preț_achiziție: prețul total de achiziție corespunzător întregii cantități; Această valoare nu este disponibilă în sistemul sursă, fiind calculată și adăugată prin intermediul ETL-ului prin formula preț_achiziție = COALESCE (cantitate_buc, cantitate_kg) * preț_bucată.

Tabela fact.venit_suplimentar – conține venitul suplimentar încasat de companie din negocierile realizate cu furnizorii pentru o mai bună promovabilitate a produselor acestora sau pentru diverse opțiuni și beneficii ce se pot acorda furnizorilor. Tabela fact.venit_suplimentar este la nivel de articol în ceea ce privește granularitatea. Este interesant să observăm că atât tabela sursă corespunzătoare din baza de date MovementData cât și tabela stg.venit_suplimentar corespunzătoare venitului suplimentar în zona de staging au granularitatea la nivel de furnizor. De asemenea, fișierele excel ce sunt importate în tabela inițială stg.import_venit_suplimentar sunt tot la nivel de furnizor. Avem, deci, de-a face cu o transformare mai complexă a datelor realizată în cadrul procesului ETL și anume aceea de a aloca la nivel de articol veniturile încasate la nivel de furnizor și lună. Acest lucru se realizează prin intermediul unei proceduri stocate, numită usr. alocare_venit_suplimentar.

Algoritmul de alocare al venitului de pe furnizor la nivel de articol se realizează în funcție de achizițiile realizate în ultimele 12 luni de la luna încasării venitului suplimentar și este descris mai detaliat în cele ce urmează:

pentru o combinație de furnizor și lună prezentă în tabela fact.venit_suplimentar se identifică perioada corespunzătoare de 12 luni în urmă de la luna realizării venitului;

se identifică articolele furnizorului – adică acele articole pentru care furnizorul nostru a realizat cel puțin un tip de movement în tabela fact.vânzări sau fact.achiziții;

se calculează procentele corespunzătoare fiecărui articol al furnizorului din suma totală a achizițiilor încheiate cu compania în ultimele 12 luni;

se calculează venitul corespunzător fiecărui articol prin înmulțirea între procentele obținute la nivel de articol la pasul anterior și venitul corespunzător combinației furnizor – lună

în cazul în care în ultimele 12 luni nu s-au realizat achiziții cu furnizorul în cauză, dar există totuși articole rezultate din vânzări sau sau achiziții anterioare, atunci venitul se alocă în mod egal la numărul de articole identificat prin pasul b).

Astfel, tabela fact.venit_suplimentar conține următoarele câmpuri:

id_articol: codul articolului căruia i-a revenit un anumit venit în urma algoritmului de alocare

id_furnizor: codul furnizorului

id_lună: luna pentru care s-a realizat venitul suplimentar

venit: suma efectivă încasată de companie în urma încheierii contractului cu furnizorul

IMPLEMENTAREA APLICAȚIEI

3.1 Tehnologii utilizate

Tehnologiile cu ajutorul căreia se vor implementa depozitul de date, procesul ETL și sistemul OLAP trebuie atent selecționate, iar alegerea trebuie făcută abia în momentul în care s-a încheiat etapa de analiză și proiectare și documentare a întregului sistem. Alegerea trebuie făcută astfel încât soluția sistemului să nu fie dependentă de o tehnologie anume. În contextul unei piețe dinamice în ceea ce privește domeniul depozitelor de date și al necesităților din ce în ce mai complexe ale utilizatorilor, trebuie să prevedem posibilele schimbări ce pot apărea și să alegem acea tehnologie care se pretează cel mai bine pentru noi în funcție de caracteristicile sistemului nostru. Acestea pot fi grupate în interfețe pentru utilizatori, motoare sau așa numitele engine-uri ale depozitelor, platforme hardware, sisteme software și mecanisme de securitate.

Utilizatorii extrag informații prețioase din depozitele de date prin intermediul interfețelor disponibile. De aceea, utilizatorii trebuie consultați în momentul în care este aleasă o interfața de acces la depozitul de date. Două criterii principale în alegerea unei anumite interfețe sunt performanța și caracteristica prietenoasă, intuitivă a interfeței. Pentru a înțelege mai bine cât de importantă este perspectiva utilizatorului în alegerea unei anumite tehnologii, vom vorbi puțin despre tipurile de utilizatori care pot folosi un sistem de tip depozit de date. Există utilizatori care vor executa interogări standard sau rapoarte cu un număr limitat de parametri. Există și utilizatori care vor fi focusați pe varianța figurilor în timp și care vor avea nevoie de rapoarte standard pe care să le poată genera sau să le primească periodic pentru a le putea analiza. O altă categorie de utilizatori sunt cei care vor face analize mult mai complexe asupra datelor, vor interoga toate datele disponibile, le vor filtra și le vor categorisi în funcție de necesitățile de raportare. Acești utilizatori au nevoie de rapoarte customizate și interogări ad-hoc. Există și un tip de utilizatori care sunt orientați inclusiv pe zona tehnică. Acești utilizatori sunt fie deja familiarizați cu domeniul tehnic, fie își doresc să învețe și să devină cât mai independenți în a accesa sistemul, să dețină controlul complet al informației pe care o poate extrage și formata.

Din punctul de vedere al motoarelor depozitelor de date, acestea trebuie să suporte necesitățile depozitelor de date și toate modalitățile de acces. Problemele principale includ capacitatea de încărcare a informațiilor în depozitul de date, implementarea unui mecanism de control al accesului la date și compatibilitatea cu interfețelor cu utilizatorii. Arhitectura, necesitățile de performanță și dimensiunea întregului depozit de date vor determina caracteristicile tehnologiei care va fi implementată. De exemplu, un depozit de date nu va necesita numai o tehnologie relațională ci și acces multidimensional și o arhitectură de tip client/server.

Platformele hardware sunt selecționate prin răspunsul la cateva întrebări – cheie: Care este volumul de date care vor fi stocate în depozitul de date și cât de mult se poate acomoda platforma la acest volum fără un impact puternic asupra perfomanței? Cât de scalabilă este platforma? Este o platformă optimizată pentru performanțe necesare depozitelor de date? Platforma este compatibilă cu software-ul ales pentru depozitul de date? Câți utilizatori vor accesa simultan baza de date? Interogările acestora vor fi simple sau complexe? Pentru a răspunde la aceste întrebări trebuie să luăm în considerare toate caracteristicile platformelor hardware, nu numai viteza CPU-ului și capacitatea discului, dar și capacitatea memoriei și capacitățile de intrare/ieșire.

Planificarea capacităților unui depozit de date nu este o știință exactă. Subestimarea este, de cele mai multe ori, o regulă și nu o excepție. Unii experți sfătuiesc dublarea resurselor estimate inițial întrucât complexitatea interogărilor și numărul utilizatorilor pot crește exponențial la numai câteva luni de la implementare. Chiar și cu o resurse inițiale suficiente, este absolut primordială alegerea unui sistem scalabil pentru a suporta traficul crescut imposibil de cuantificat, dar totuși inevitabil.

Securitatea depozitul de date se referă atât la accesul utilizatorilor la date cât și la securitatea fizică a datelor. Un depozit de date este o sursă a informațiilor întreprinderii de tip read-only. De aceea, dezvoltatorii nu au de ce să fie preocupați de faptul că utilizatorii ar putea face modificări sau ștergeri nepermise. Însă dezvoltatorii trebuie să asigure în aceeași măsură protecția datelor împotriva accesului neautorizat și accesabilitatea la date pentru toți utilizatorii companiei care trebuie să efectueze analize de business pe acestea. Cea mai bună soluție este aceea de a permite tuturor utilizatorilor companiei de a avea cunoștință de defințiile măsurilor existente, dar accesul efectiv la date să se realizeze în urma unor aprobări obținute pe baza unor criterii bine stabilite. Securitatea bazelor de date trebuie asigurată de către dezvoltatori și din punctul de vedere al metodelor de backup și restore, replicare a bazelor de date sau toleranța la erori.

Înainte de a ne decide pentru o anumită tehnologie, trebuie deci analizați factori – cheie precum capacitatea și frecvența de încărcare a datelor în depozitul de date, implementarea unui mecanism de securitate optim, performanța sistemului, gradul de solicitare a sistemului prin intermediul interogărilor, dimensiunea bazei de date, numărul posibili de utilizatori sau accesabilitatea datelor.

Pentru realizarea acestui proiect am utilizat tehnologia Microsoft SQL Server, versiunea SQL Server 2012, dezvoltată de Microsoft. SQL Server include o gamă de servicii adiționale, care furnizează funcționalități suplimentare față de sistemul principal de gestiune al bazei de date. Dintre aceste servicii, mai jos se regăsesc cele cu care am interacționat în realizarea aplicației de față.

Database Engine – reprezină serviciul principal pentru stocarea, procesarea și asigurarea securității datelor. Prin intermediul Database Engine se asigură accesul controlat și procesarea rapidă a tranzacțiilor cu scopul soluționării într-un timp cât mai redus chiar și cele mai solicitante cerințe din cadrul companiei. Database Engine este folosit pentru realizarea bazelor de date relaționale tranzacționale dar și analitice. Această etapă include crearea tabelelor de stocare a datelor și obiectele bazei de date, precum indecși, viziuni și procedurile stocare prin care se vizualizează, gestionează și se asigură securitatea datelor. Prin intermediul SQL Server Management Studio se pot gestiona obiectele bazei de date, iar prin intermediul SQL Server Profiler se pot detecta operațiile și activitățile efectuate de server.

SQL Server Integration Services (SSIS), reprezintă componenta baze de date Microsoft SQL Server care poate fi folosită pentru implementarea procesului ETL. Această soluție permite o gamă variată de task-uri de migrare a datelor, fiind o platformă dedicată integrării datelor și asigurarea implementării fluxurilor aplicației. Reprezintă un instrument rapid si flexibil, util în procesul de extragere, transformare și încărcare a datelor, dar și în întreținerea automatizată a bazelor de date sau actualizări ale cubului multidimensional.

SQL Server Analysis Services (SSAS) – reprezintă soluția furnizată de Microsoft pentru dezvoltarea bazelor de date multidimensionale utilizate în suportul deciziilor și în domeniul de inteligență a afacerii. Acest instrument oferă accesul la date prin intermediul rapoartelor de business sau al aplicațiilor utilizatorilor precum Excel, Reporting Services sau alte instrumente de analiză. Procesul obișnuit al instanței de Analysis Services include dezvoltarea unui model OLAP, deployment-ul modelului sub formă de bază de date în cadrul instanței Analysis Services, procesarea bazei de date

SQL Server Reporting Services (SSRS) – este instrumentul oferit de către Microsoft în vederea realizării rapoartelor precum și a deploymentului și gestiunii acestor rapoarte necesare organizației. Reporting Services include tehnici de programare care permit extinderea și customizarea funcționalităților de raportare. Prin intermediul acestui instrument se pot realiza rapoarte interactive, tabulare sau grafice, din baza de date relațională sau din cea multidimensională. Rapoartele pot fi publicate, programate pentru procesare sau accesate pe bază de solicitare.

În realizarea lucrării, am creat bazele de date corespunzătoare sistemelor tranzacționale respectiv depozitului de date prin intermediul serviciului Database Engine furnizat de tehnologia Microsoft. Tot aici s-au creat procedurile stocate și funcțiile necesare pentru popularea depozitului de date sau pentru rapoartele dezvoltate.

Pentru popularea depozitului de date am folosit tehnologia SQL Server Integration Services. Astfel, datele din sistemele sursă au fost importate în tabelele de staging. Ulterior, au fost supuse procesului de curățare și algoritmilor de transformare specifici, urmând ca ulterior să fie încărcate în tabelele finale.

Cu ajutorul tehnologiei SQL Server Analyses Services am proiectat și dezvoltat cubul multidimensional de date care va pune la dispoziția utilizatorilor datele sub o formă accesibilă într-un mod rapid și intuitiv. După ce cubul este deployat și procesat, acesta poate fi accesat de către utilizatori. Pe baza cubului de date, am realizat rapoarte prin serviciul SQL Server Reporting Services.

Într-un depozit, datele sunt primite cu regularitate. Acesta este motivul pentru care și procesul de încărcare a datelor trebuie să fie executat periodic. De asemenea, din momentul în care datele cele mai recente au ajuns în tabelele finale din baza de date relațională, trebuie să procesăm cubul OLAP astfel încât rapoartele utilizatorilor să includă și să reflecte figurile actualizate. Avem nevoie, deci, de o programare a execuției procesului ETL, urmată de procesarea cubului OLAP. În acest sens, am dezvoltat, cu ajutorul utilitarului SQL Server Agent pus la dispoziție de Microsoft, jobul care poate fi programat periodic în funcție de necesitățile depozitului de date. În cadrul acestui job au fost incluși pașii de executare a pachetelor SSIS și procesare a cubului de date.

3.2 Implementarea fluxului de curățare și integrare a datelor

Procesul ETL pe care îl vom analiza și implementa în continuare este realizat în mai multe etape corespunzătoare anumitor faze specifice sistemului existent.

Extragerea datelor

Primul pas este acela de extragere a datelor din sursele corespunzătoare. În cazul nostru, sursele de date considerate vor fi atât baze de date, cât și fișiere excel care sunt transferate către o locație stabilită. Datele extrase din sistemele sursă vor fi inserate inițial într-o zonă de stocare, zonă cunoscută sub denumirea de staging. Tabelele corespunzătoare zonei de staging, în care vor fi inserate datele extrase din sistemele sursă, sunt cele care fac parte din schema stg. Informația existentă în această etapă este o imagine identică a datelor existente în sistemele sursă. Datele nu sunt deci prelucrate sau curățate în niciun fel în acest moment al procesului. Acest pas este necesar în primul rând pentru a putea relua procesul de la acest pas intermediar în cazul în care fluxul întâmpină probleme și se termină cu eroare. În al doilea rând, stocarea datelor într-o zonă intermediară, într-o formă identică cu cea în care au fost extrase din sistemul sursă, este utilă pentru a face posibilă o analiză preliminară în cazul unor probleme de date. Astfel, se poate izola dacă problemele de date vin din sistemul sursă sau din transformările ulterioare care au loc pe parcursul procesului ETL.

Implementarea acestui pas este realizată cu ajutorul pachetelor SSIS dezvoltate cu ajutorul utilitarului Microsoft Visual Studio 2010. Microsoft Visual Studio 2010 se instalează odată cu pachetul SQL Server 2012. Pentru a crea un nou proiect SSIS, din Visual Studio se selectează File –> New Project –> New Project –> Integration Services Project. Se dă numele proiectului dorit, iar apoi se poate continua cu crearea primului pachet care va face parte din noul proiect SSIS.

Datele sursă provin, cum am menționat anterior, atât din fișiere excel cât și din două baze de date relaționale MasterData respectiv MovementData corespunzătoare sistemelor tranzacționale existente la nivel de companie.

Extragerea datelor din bazele de date relaționale

Pentru extragerea datelor din cele două baze de date prezentate, am dezvoltat un proiect SSIS denumit Retail_Stage care conține trei pachete, descrise după cum urmează:

Start_STAGE.dtsx – acesta este pachetul principal al proiectului SSIS responsabil cu extragerea datelor din sistemele sursă și încărcarea lor în tabelele de staging. Îl putem considera ca fiind un pachet de control. În cadrul acestui proiect se lansează în execuție pachetele care vor extrage datele din bazele de date sursă și le vor insera în zona de staging. Întregul proces este logat în tabela de logare dbo.etl_log. Astfel, la lansarea în execuție a acestui pachet, o linie nouă va fi inserată in tabela dbo.etl_log și va corespunde procesului care tocmai a fost inițiat. Primul pachet care este executat este cel care extrage datele din sistemul MasterData, care este descris în continuare.

STAGE_Load_MasterData.dtsx – acest pachet este utilizat pentru a extrage informațiile referitoare la clienți, articole, categorii, magazine și furnizori disponibile în baza de date MasterData. Primul pas este de a goli tabelele de staging în care vor fi inserate datele extrase din baza de date sursă. După ce tabelele de staging au fost golite, urmează extragerea datelor din tabelele sursă corespunzătoare și inserarea lor în cele de staging. La acest nivel nu se realizează nicio procesare a datelor, aceste nu suferă transformări, nu sunt curățate. În momentul completării acestui pas, tabelele de staging vor conține informațiile din tabelele sursă într-o relație de 1:1.

STAGE_Load_Movement.dtsx – similar cu încărcarea de MasterData se realizează și încărcarea datelor de movement. Întâi sunt golite tabelele de staging pentru a evita dublarea datelor, iar apoi sunt încărcate datele din baza de date MovementData, fără a efectua transformări asupra datelor.

Execuția cu succes sau cu eroare a fiecăruia dintre pachetele prezentate este logată în tabela de logare dbo.etl_log. Mai jos se regăsesc exemplificări ale pachetelor SSIS prezentate:

Figura 6. Pachetul SSIS Start_STAGE.dtsx

Figura 7. Pachetul SSIS STAGE_Load_MasterData

Figura 8. Pachetul SSIS STAGE_Load_Movement

Extragerea datelor din fișierele Excel.

Fișierele în format Excel sunt transmite către o locație stabilită anterior, într-un director identificat prin calea: C:\Users\Desktop\NightRun. Fișierele au o denumire fixă de tipul import_file_x, unde x reprezintă numărul curent al fișierului. De asemenea, după ce fișierele sunt procesate, acestea vor fi transferate din directorul inițial într-un director numit Archived, identificat prin calea C:\Users\Desktop\NightRun\Archived.

Importul fișierelor se realizează tot cu ajutorul pachetelor SSIS. În acest sens a fost dezvoltat pachetul ImportFiles.dtsx care constă în doi pași ce se execută într-o buclă repetitivă atâta timp cât în directorul sursă există fișiere neprocesate. Primul pas este cel prin care fiecare fișier existent în directorul stabilit este preluat și inserat într-o tabelă denumită stg.import_venit_suplimentar. Cel de-al doilea pas îl reprezintă mutarea fișierului în directorul Archived, în momentul în care acesta a fost procesat. Mai jos se pot observa foarte clar acești pași:

Figura 9. Pachetul SSIS ImportFiles.dtsx

Curățarea și transformarea datelor. Încărcarea datelor în tabelele destinație.

Pasul următor este de a aplica diverși algoritmi de curățare și de transformare a datelor deja încărcate în zona de staging. Calitatea datelor reprezintă unul dintre cele mai importante aspecte ce trebuie asigurate în cadrul unui depozit de date, de aceea este necesară o curățare corespunzătoare a datelor. De asemenea, în funcție de cerințele utilizatorilor, este posibil să avem nevoie de măsuri suplimentare în depozitul de date care pot fi derivate din cele extrase din sursele de date. Pot apărea situații în care nivelul de agregare sau detaliu dorit în tabelele destinație diferă față de nivelul de agregare respectiv detaliu disponibil în tabelele din staging. Acesta este pasul în care se implementează calculul diverselor măsuri noi și totodată se aplică algoritmii de agregare sau detaliere a datelor.

Pasul final, cel de încărcare a datelor, presupune inserarea datelor în tabelele destinație. Pentru a asigura o calitate a datelor cât mai ridicată, e recomandat ca tabelele destinație să fie referențiate corespunzător. Astfel, se evită anumite scăpări din procesul de curățare și se poate garanta o calitate superioară a datelor.

Toate aceste operații de curățare, transformare și încărcare efectivă a datelor sunt realizate în cadrul unui nou proiect SSIS, numit Retail_Frontroom. Motivul pentru care am dezvoltat proiecte separate pentru aceste etape este în primul rând o delimitare mai clară a scopurilor și funcționalităților diferite ale celor două proiecte, cât și posibilitatea mult mai ușoară de a executa izolat unul dintre pașii procesului ETL în cazul în care apar probleme la rulare.

Proiectul Retail_Frontroom conține o structură similară cu proiectul Retail_Stage. Avem, deci, trei pachete SSIS care fac parte din acest proiect:

Start_Frontroom.dtsx – acesta este pachetul principal al proiectului Retail_Frontroom. Este, de fapt, un pachet de control prin intermediul căruia se gestionează executarea pachetelor Load_DIM.dtsx și Load_FACT.dtsx. Ca și în cazul încărcării zonei de staging, procesul de transformare și încărcare în tabelele destinație este logat în tabela de logare dbo.etl_log. Astfel, la lansarea în execuție a acestui pachet, o linie nouă va fi inserată in tabela dbo.etl_log și va corespunde procesului care tocmai a fost inițiat, adica „Load Frontroom”. Primul pachet care este executat este pachetul Load_DIM.dtsx, urmat fiind de LOAD_FACT.dtsx. Această precedență este dată în primul rând de cheile secundare definite la nivelul tabelelor fact care referențiază tabelele de dimensiuni. Aceasta este o primă modalitate prin care se asigură consistența datelor ce vor fi inserate în tabelele depozitului de date. În cazul în care nu sunt respectate cheile de referențiere, procesul va fi întrerupt și va fi necesară o analiză a cauzelor care a generat eroarea, urmată de o soluționare a acestora în vederea continuării procesului de încărcare.

Load_DIM.dtsx – informațiile referitoare la clienți, articole, categorii, magazine și furnizori sunt încărcate din tabele de staging în tabelele de dimensiuni prin intermediul acestui pachet. În cadrul acestui pachet este adăugată și dimensiunea de timp. Pentru a introduce informațiile corespunzătoare de timp în tabela dim.time, se folosește funcția dbo.func_get_time_dim. Tot acum datele din dimensiuni sunt curățate pentru a asigura o consistență și coerență cât mai mare. Astfel, una dintre operații este aceea de a corecta posibilele variații ale informațiilor legate de sexul clienților. Pot exista diverse forme precum F/Female/Woman/W sau M/Male/Man. În depozitul de date, informația va fi reprezentată prin simbolurile „F” respectiv „M”. Pentru acest acest lucru, vom realiza operații de update, acolo unde simbolurile nu corespund cu cele stabilite în depozit. O altă operație de curățare a datelor este aceea de a corecta județul pe baza unei mapări oraș – județ. Această operație este necesară deoarece pot surveni erori umane de genul județul Timișoara în loc de Timiș sau Târgu-Mureș în loc de Mureș.

Load_FACT.dtsx – în cadrul acestui pachet are loc încărcarea măsurilor din staging în tabelele fact.vânzări, fact.achiziții și fact.venit_suplimentar. Cele mai multe transformări sunt realizate în această etapă, întrucât aici sunt calculate măsurile derivate sau aici se realizează agregări ale datelor sau calcularea nivelului de detaliu corespunzător. Există și la acest pas o curățare a datelor, așa încât valorile finale care vor fi analizate de analiști și manageri să nu fie influențate de posibilele discrepanțe ale datelor. Astfel, datele sosite din sistemul sursă care nu au corespondent în tabelele de dimensiuni sunt redirecționate în tabelele aparținând schemei nok. Vom discuta mai detaliat despre întregul proces mai jos.

Mai jos se regăsec pachetele SSIS utilizate în proiectul Retail_Frontroom.

Figura 10. Pachetul Start_Frontroom.dtsx

Figura 11. Pachetul Load_DIM.dtsx

Figura 12. Pachetul Load_FACT.dtsx

După cum menționam anterior, pachetul Load_Fact.dtsx conține cele mai importante procesări și transformări de date în vederea pregătirii loc într-o formă cât mai ușor accesibilă pentru utilizatorii finali. Vom lua pentru exemplificare fluxul de încărcare a vânzărilor. Data Flow-ul corespunzător este cel din figura de mai jos:

Figura 13. Data Flow pentru încărcarea tabelei fact.vânzări.

Datele de vânzări din tabela sursă stg.vânzări, sunt supuse anumitor validări și transformări pentru a fi aduse în forma finală, compatibilă cu formatul impus de tabela destinație fact.vânzări. Astfel, prin folosirea unei transformări de tip Lookup, sunt selecționate doar acele înregistrări care corespund unor informații valide de master data. Acest lucru înseamnă că dacă sunt găsite înregistrări care nu corespund informațiilor existente în tabelele de dimensiuni acestea vor putea fi fie ignorate, fie trimise în eroare, fie redirecționate către o tabelă de stocare a acestor informații. De exemplu, în cazul nostru, este posibil ca sistemul operațional al vânzărilor să înregistreze vânzări pe articole care, din diverse motive, încă nu au fost listate în sistemul MasterData. În acest caz, pentru a evita erorile de procesare ale acestor înregistrări care nu respectă constrângerile de chei secundare, vom identifica aceste cazuri cu ajutorul transformării Lookup. Înregistrările care nu corespund vor fi redirecționate către tabela nok.vânzări. Vom stoca aceste înregistrări în eventualitatea că la încărcările viitoare vom primi informațiile de master data corespunzătoare în tabelele de dimensiuni și vom putea finaliza încărcarea acestor linii în tabela fact. La următoarea rulare a procesului ETL, înregistrările din table nok.vânzări vor fi reprocesate. În cazul în care am primit informațiile necesare în tabelele de dimensiuni, linia corespunzătoare va fi inserată în tabela fact.vânzări și stearsă din tabela nok.vânzări.

Pentru acele înregistrări care au trecut procesul de validare conform cu informațiile din dimensiuni, se realizează o altă tranformare SSIS, numită Derived Column. Prin intermediul acesteia, se pot calcula și adăuga câmpuri noi. Mai jos se pot observa și analiza calculele derivate care sunt efectuate pentru tabela fact.vânzări.

Figura 14. Câmpuri derivate adăugate în tabela fact.vânzări prin procesul ETL.

Cum din sistemul sursă primim informația de preț pe bucată fără TVA, iar utilizatorii finali au nevoie de o imagine completă a figurilor de vânzări, vom efectua calculații care ne vor aduce prețul pe bucată cu TVA inclus(preț_bucată_cu_tva), prețul de vânzare pe întreaga cantitate fără TVA (preț_fără_tva) și prețul de vânzare pe întreaga cantitate cu TVA (preț_cu_tva). Formulele aplicate au fost explicate mai sus și se regăsesc și în Figura 14 într-o formă în care pot fi interpretate de către SSIS.

De asemenea, pentru ușurința identificării sau agregării datelor la nivel de lună, vom utiliza un identificator de lună, numit id_lună, reprezentat prin formula YYYY * 100 + MM. Astfel, pentru luna ianuarie 2015 vom avea id_lună = 201501, pentru luna februarie 2015 vom avea id_lună = 201502, etc.

După finalizarea tuturor validărilor și transformărilor, datele sunt în sfârșit inserate în tabela finală fact.vânzări. Unor transformări similare este supusă și tabela fact.achiziții înainte ca datele din tabela stg.achiziții să ajungă în tabela destinație.

Datele de venit suplimentar sunt supuse unor transformări diferite. Cum nivelul de detaliu din tabela fact.venit_suplimentar este acela de articol, iar datele din sistemul sursă vin la nivel de furnizor, s-a decis alocarea venitului pe articole. Acest lucru se realizează prin executarea unui task SSIS denumit Execute SSIS Task. Cu ajutorul acestui task, putem executa din pachetul SSIS procedura usr.alocare_venit_suplimentar, cea responsabilă de alocarea venitului la nivel de articol. Algoritmul de alocare este scris în limbaj T-SQL, descrierea completă a acestuia fiind prezentată în capitolul anterior.

3.3 Implementarea cubului OLAP

Pentru a dezvolta baza de date multidimensională, am folosit tot Microsoft Visual Studio 2010. Am selectat un nou proiect Analysis Services pe care l-am denumit Retail_OLAP. Pașii urmați pentru construirea cubului de date sunt următorii:

Data Sources – definirea unei conexiuni la baza de date folosită ca sursă pentru cubul nostru

Data Source Views – adăugarea tabelelor sau așa numitelor „named queries” care vor fi folosite pentru crearea dimensiunilor și măsurilor cubului.

Cubes – crearea efectivă a cubului cu includerea dimensiunilor și măsurilor.

În urma executării acesor pași, am obținut schema cubului Retail.cube redată mai jos:

Figura 15. Structura cubului OLAP.

Datele din interiorul cubului vor fi agregate și procesate de către motorul OLAP al tehnologiei Analyses Services, în funcție de dimensiunile, ierarhiile și măsurile proiectate. Pentru a avea în permanență o imagine completă și recentă a datelor din depozitul de date, cubul trebuie reprocesat de fiecare data când intervin actualizări de date în baza de date relațională sau când este modificată structura cubului. Cu ajutorul cubului OLAP vom putea exploata datelor fie prin acțiuni intuitive de tipul „drag & drop”. Accesul la cub se poate face direct, prin intermediul Analysis Services, prin intermediul Excelului care ne permite conectarea la o sursă Analysis Services sau prin intermediul rapoartelor SSRS. Așa cum se poate observa, performanțele de citire ale datelor sunt extrem de ridicate, rapoartele fiind generate imediat. Pe baza acestor date, analiștii pot formula strategii de dezvoltare a business-ului, deci este evidentă importanța ca datele raportate să nu fie alterate pe întregul drum parcurs între sistemele sursă, depozitul de date și cubul de date.

3.4 Executarea programată a întregului proces de încărcare a datelor

Pentru ca întregul proces de încărcare a datelor din sistemele sursă în depozitul de date și mai apoi în cubul OLAP să se poată realiza periodic, am dezvoltat un job care execută toți pașii prezentați cu ajutorul utilitarului SQL Server Agent.

Rularea pachetelor SSIS de încărcare a fișierelor excel se realizează la pasul numărul 1 al jobului, fiind urmat de încărcarea datelor din bazele de date relaționale în pasul denumit LoadStage. Pasul al treilea este de încărcare a datelor din tabelele de staging în tabelele dim și fact. Pasul final este procesarea cubului OLAP.

Figura 16. SQL Server Agent – jobul Retail

Acest job poate fi programat să ruleze periodic, zilnic sau lunar în funcție de necesități. De asemenea, jobul poate fi executat la cerere pornind fie de la pasul numărul 1, fie de la un pas intermediar.

CONCLUZII

BIBLIOGRAFIE:

http://datawarehousinginfos.blogspot.ro/2010/08/definition-of-data-warehousing.html

http://www.mi.bxb.ro/wp-content/uploads/2012/Articole/Art5-13.pdf

http://www.feaa.uvt.ro/bi/wp-content/uploads/2010/12/Abordari-de-tip-Data-Warehousing-Implementare-in-MS-SQL-Server-2005.pdf

https://docs.oracle.com/database/121/DWHSG/concept.htm#DWHSG9288

https://technet.microsoft.com/en-us/library/aa906003(v=sql.80).aspx

http://sqlmag.com/database-administration/7-steps-data-warehousing

http://www.visible.com/company/whitepapers/dwcsf.pdf

http://en.wikipedia.org/wiki/Data_warehouse

http://en.wikipedia.org/wiki/Extract,_transform,_load

BIBLIOGRAFIE:

http://datawarehousinginfos.blogspot.ro/2010/08/definition-of-data-warehousing.html

http://www.mi.bxb.ro/wp-content/uploads/2012/Articole/Art5-13.pdf

http://www.feaa.uvt.ro/bi/wp-content/uploads/2010/12/Abordari-de-tip-Data-Warehousing-Implementare-in-MS-SQL-Server-2005.pdf

https://docs.oracle.com/database/121/DWHSG/concept.htm#DWHSG9288

https://technet.microsoft.com/en-us/library/aa906003(v=sql.80).aspx

http://sqlmag.com/database-administration/7-steps-data-warehousing

http://www.visible.com/company/whitepapers/dwcsf.pdf

http://en.wikipedia.org/wiki/Data_warehouse

http://en.wikipedia.org/wiki/Extract,_transform,_load