1
Da a Cen aliza ion Fo Ne lix
Abdallah Zahe
In e nship epo p esen ed as pa ial equi emen o
ob aining he Mas e ’s deg ee in Ad anced Analy ics
2
NOVA In o ma ion Managemen School
Ins i u o Supe io de Es a ís ica e Ges ão de In o mação
Uni e sidade No a de Lisboa
Da a Cen aliza ion o Ne lix
by
Abdallah Zahe
In e nship epo p esen ed as pa ial equi emen o ob aining he mas e ’s deg ee in Ad anced
Analy ics
Ad iso / Co Ad iso : Mau o Cas elli
Co Ad iso : Vic o Jua egui
Oc obe 2021
3
ACKNOWLEDGEMENTS
Special hanks o s ack o e low o making he impossible possible.
4
ABSTRACT
P esen ly, he amoun o da a p oduced e e y day is uly mind-boggling. The e a e 2.5
quin illion by es o da a c ea ed each day a his cu en pace and companies a e ac i ely gene a ing
and collec ing i . This da a has become o he u mos impo ance and companies a e changing hei
beha io owa ds i . Many o hese companies nowadays a e gi ing mo e conside a ion and ime
in es men in o he da a analy ics a ea. They ha e s a ed o igu e ou a way on how o use hei
cus ome s’/clien s’ da a o gene a e insigh s and s a egies o be e u u is ic ma ke ing
app oaches. In o de o ha e a be e unde s anding o he da a, hese co po a ions ook he
ini ia i e o cen alize i in an a anged manne so ha he analysis will be mo e o ganized h ough
he whole p ocedu e. The ollowing in e nship epo s udies he ans o ma ion o da a om i s e y
ea ly s ages o he inal Powe Bi pa . This epo will discuss speci ically he ETL p ocess ha he
da a unde goes om how he da a is collec ed ia SSIS packages, ea ed h ough T-SQL, and
p o ided as a clean inal able o build a Powe Bi epo om i so ha o he employees and people
who do no ha e much knowledge abou da a will be able o see h ough g aphs ha explain wha
he da a means and wha insigh s i holds wi hin.
KEYWORDS
Cen alize, Da a ans o ma ion, ETL p ocess, SSIS packages, T-SQL, Powe Bi epo .
5
INDEX
1. In oduc ion .................................................................................................................. 1
1.1.1. Company O e iew ........................................................................................ 3
2. P oblem o e iew ......................................................................................................... 5
2.1.1. Case S udy ...................................................................................................... 5
2.1.2. Go e nance Model ......................................................................................... 6
2.1.3. Raw Da a......................................................................................................... 9
2.1.4. KPIs ................................................................................................................. 9
2.1.5. Expec ed Ou pu ........................................................................................... 11
3. P ac ical sol ing .......................................................................................................... 12
3.1. Fi s Solu ion ........................................................................................................ 12
3.1.1. SSIS Flow ....................................................................................................... 12
3.1.2. Mapping and P ocessing .............................................................................. 15
3.1.3. P oblem Solu ion .......................................................................................... 18
3.2. Second solu ion ................................................................................................... 19
3.2.1. P oblems and P e equisi es ......................................................................... 19
3.2.2. SSIS Flow ....................................................................................................... 22
3.2.3. Fla File Sou ce ............................................................................................. 22
3.2.4. SQL Sou ce .................................................................................................... 23
3.2.5. Job C ea ion .................................................................................................. 24
3.2.6. Ad an ages o The Second Solu ion ............................................................. 24
4. Fea u es o add ........................................................................................................... 26
5. Repo ing .................................................................................................................... 27
5.1. Summa y .............................................................................................................. 27
5.2. T end .................................................................................................................... 28
5.3. KPI ........................................................................................................................ 29
5.4. Glossa y ............................................................................................................... 30
6. Conclusions ................................................................................................................. 31
7. Limi a ions and ecommenda ions o u u e wo ks ................................................. 32
8. Re e ence .................................................................................................................... 33

6
LIST OF FIGURES
Figu e 1 – Cycle o Cus ome Expe ience .................................................................................... 3
Figu e 2 – Go e nance model s uc u e. .................................................................................... 6
Figu e 3 – In oducing he in e connec i i y be ween all he AD laye s. ................................... 7
Figu e 4 – Showing di e en ypes o use s ega ding assignmen employees o AD G oups. . 8
Figu e 5 – Showing di e en accesses o use s ega ding hei AD G oups. ............................. 8
Figu e 6 – KPIs ha we e buil o his speci ic p ojec . ........................................................... 10
Figu e 7 – A scheme o he SSIS loop componen s om con ol low o da a low. ................ 13
Figu e 8 – Example o a code o duplica e check p ocedu e. .................................................. 14
Figu e 9 – A scheme o he en i e SSIS in eg a ion p ocess. .................................................... 15
Figu e 10 – SQL able showing an example o how he da a is being calcula ed. ................... 15
Figu e 11 – A scheme o show pi o ing ans o ma ions. ........................................................ 16
Figu e 12 – Table showing he dis ibu ion o componen s and hei alues pe KPI. ............. 17
Figu e 13 – Table showing each KPI wi h hei Nume a o and Denomina o Values. ........... 17
Figu e 14 – Table o p o ide he DEs o all necessa y ac o s o c ea e he p ocess. ............. 20
Figu e 15 – Po ion o he able s uc u e. ............................................................................... 20
Figu e 16 – C ea ion que y esul . ............................................................................................ 21
Figu e 17 – Showing he SSIS Da a Flow. ................................................................................. 22
Figu e 18 – Re ealing a summa y o he calcula ed KPIs. ...................................................... 27
Figu e 19 – D ill down one o he KPIs. ..................................................................................... 28
Figu e 20 – Ba cha showing he end pe LOB, KPI, Coun y, Si e, and Language. ............. 28
Figu e 21 – G aphs o compa e mul iple KPIs. ......................................................................... 29
Figu e 22 – Glossa y o all measu emen s used in he p ojec . .............................................. 30
7
LIST OF ABBREVIATIONS AND ACRONYMS
AD G oup Ac i e Di ec o y G oup
KPI Key Pe o mance Indica o
TP Telepe o mance
DBA Da abase Adminis a o
CSR Cus ome Se ice Rep esen a i e
CSV Comma-Sepa a ed Values
SP S o ed P ocedu e
HR Human Resou ces
SSIS SQL Se e In eg a ion Se ices
LOB Line o Business
PDR Planning, De elopmen & Re iew
DE Da a enginee ing
1
1. INTRODUCTION
“Big da a is cu en ly a ho esea ch opic, wi h ou million hi s on Google schola in
Oc obe 2016. One eason o he popula i y o big da a esea ch is he knowledge ha can be
ex ac ed om analyzing hese la ge da a se s (B. Nelson 2016)”. Wi h his p og essi e enhancemen ,
scien is s had o adjus o he way hey ea big da a. In he beginning, big da a was jus an analogy.
The e was no clea explana ion o wha makes da a “BIG”. Because big da a is e e ywhe e, he e is
almos an u gen need o collec and p ese e wha e e da a is gene a ed. “In ecen yea s, big da a
is lou ishing, exceeding he adi ional da a p ocessing me hods wi h 5 'V' cha ac e is ics (M.
Shabana & K. Sha ma (2021)”. Th oughou he yea s, he e has been a de eloping comp ehension o
he job ha huge in o ma ion can play in con eying ines imable expe iences o an associa ion,
unco e ing quali ies and sho comings, and enabling o ganiza ions o imp o e hei p ac ices. La ge
in o ma ion has no plan, is non-c i ical and non-ha dline – i essen ially unco e s a depic ion o
ac ion.
Howe e , while nume ous associa ions comp ehend he signi icance o in o ma ion, no
many a e ye seeing i s e ec . “Ano he examina ion en i led B oken Connec ions: Why in es iga ion
s ill canno seem o be aken ca e o makes he case ha 70% o business leade s ecognize he
signi icance o deals and ad e ising in es iga ion, ye jus 2% say ha hei examina ions ha e
accomplished a wide, posi i e e ec (LINDELL, JIM 2020)”. This disco e y ocuses on he equi emen
o Eno mous In o ma ion o be aken ca e o by e-app op ia ed i ms who spend signi ican ime in
examining he in o ma ion c ea ed by o ganiza ions and who can o e genuine, no ewo hy
expe iences. In he o ewo d o his epo , Dan Wea he ill composes ha "Ou s udy and ollow-up
in e iews wi h almos 450 U.S-based senio chie s om en e p ises including d ugs, clinical gadge s,
IT, mone a y adminis a ions, elecoms and a el and accommoda ion a i med one hing ha we
de ini ely knew: ha dly any associa ions ha e had he op ion o hi he nail on he head and o
p oduce he so o business sway ha hey had expec ed."
2
As men ioned be o e, his hesis ook he inspi a ion o how o ganized da a low can ease
he cons uc ion o in eg a ion p ocesses. To s a wi h, a plan should be cons uc ed o know he
a ge ed audience, he da a p o ide s, and how he s aging o ganiza ion will ake place. As a esul ,
he go e nance model was essen ial o p o ide accesses and limi a ions based on which AD g oup
he use belongs o. A e c ea ing hese AD g oups, he ime comes o go h ough he equi emen s.
Fo example, he kind o da a p o ided should be de ined, as well as he c ea o , and enume a e o
whom i will be appoin ed o. Based on ha , one will ha e a clea e ision o acknowledge he g an
access le els. An expec ed ou pu will be d awn as a i s d a o he inal solu ion.
A e going h ough all he p e ious poin s, he ligh should be shed on he da a now. The
mo e one unde s ands he da a hey a e willing o ans o m, he mo e he in eg a ion p ocess will
go smoo he . The dimensions and he ac ables will be c ea ed acco dingly, and hen he esul s will
be s o ed in he ac able o be p o ided o he Powe Bi epo .
Wi h his hesis, one will be able o go h ough all he s eps in de ail o a be e
unde s anding o how he p ocess was buil o he deli e y s age.
9
2.1.3. Raw Da a
Be o e di ing h ough he p ocess, a de ini ion o aw da a is equi ed. Raw da a alludes o
any in o ma ion ques ion ha has no expe ienced ca e ul p epa a ion, ei he physically o h ough
au oma ed compu e p og ams. Raw da a may be assembled om di e en o ms and IT asse s.
As a esul o his de ini ion, unde s anding he da a ha is being issued is conside ed one o
he p ima y keys be o e de elopmen . In his pa , i a clea unde s anding o he ype o da a he
clien s o DAs supply, i will no only ease he de elopmen , bu i will sa e an abundan amoun o
ime and e o , so he same job will no be done wice and maybe mo e due o he edundancy o
he da a ha is he e. Wi h a mee ing o jus iewing a po ion o da a can be conside ed as an
op ion.
A mee ing was scheduled wi h he DA esponsible o his p ojec which allowed o he
e iew o his da a. I was acknowledged ha he da a was coming om h ee di e en sou ces,
Excel iles, CSV iles, and SPs om he cu en se e o e en h ough a linked se e .
2.1.4. KPIs
“KPI s ands o key pe o mance indica o , a quan i iable measu e o pe o mance o e ime
o a speci ic objec i e. KPIs p o ide a ge s o eams o shoo o , miles ones o gauge p og ess, and
insigh s ha help people ac oss he o ganiza ion make be e decisions. F om inance and HR o
ma ke ing and sales, key pe o mance indica o s help e e y a ea o he business mo e o wa d a he
s a egic le el (J. Mo ow 2020).”
These KPI measu emen s a e usually submi ed by he DAs. Based on hese o mulas, he
KPIs a e calcula ed on he backend side. La e in his epo , mo e de ails will be co e ed on how
hese KPIs we e being compu ed.

10
Figu e 6 – KPIs ha we e buil o his speci ic p ojec .
These KPIs a e di ided in o 6 di e en g oup ca ego ies, each ca ego y akes insigh s om he da a in
a di e en pe spec i e. He e is he lis o he ones ha we e used:
• Ope a ional: a disc e e es ima ion ha a company employs o sc een and assess he
e ec i eness o i s day- o-day ope a ions.
• WFM: Wo k o ce managemen epo ing o mode n businesses.
• Fo ecas : Fo ecas ing KPIs and pe o mance measu es is abou inding le e age. And ha is
why building a o ecas ing model o a KPI, o pe o mance measu e is so aluable: i makes a
di e ence when an impac is ound.
• Sh inkage: This KPI is u ilized o measu e he a e a which he es eem o s ock has been
diminished due o loss, bu gla y, o w ong eco d keeping.
• Quali y: A quan i a i e measu e o da a quali y. A da a quali y measu emen sys em
measu es he alues o he quali y o da a a es ima ion and ocuses on a ce ain ecu ence
o measu emen .
• Financial: A leading high-le el measu e o e enue, expenses, p o i s o o he inancial
ou comes.
11
2.1.5. Expec ed Ou pu
A e going h ough all he ini ial s eps o decide on he expec ed ou pu , now is he ime o
mo e o wa d discussing i . Bu be o e con inuing, a delibe a ion needed abou in eg a ing he da a
iles i s om hei ini ial iles o SQL o check how o s o e he da a and in which o ma . Because
he e was a close deadline o he p ojec , going wi h he mos i ial solu ion was manda o y o
ans o m he iles in o a able.
As a esul , he signi ican and sui able p oposi ion is o ha e a “S a Schema” o p oduce a
inal ac able wi h all he columns used o calcula e he KPIs men ioned be o e.
This solu ion will p o ide all he da a needed wi hou a ec ing he deadline.
12
3. PRACTICAL SOLVING
In his pa , wo solu ions will be p o ided. The i s one is he one ha was adap ed p ima ily.
Because some issues we e aced, and a mo e gene ic solu ion was needed, ano he solu ion was
es ablished. A de ailed explana ion is supplied o e eal he hows and he whys.
3.1. FIRST SOLUTION
3.1.1. SSIS Flow
Fi s , wha does ETL mean? ETL s ands o Ex ac ans o m and load. “An ETL de ice
ex ac s he in o ma ion om unique RDBMS sou ce s uc u es, ans o ms he da a by making use
o comme cial en e p ise common sense, conca ena e, and so on (Raghu aman 2021)”.
In his pa , i will be explained in de ail he s eps se up o ans o m he da a om hei
ini ial s a e o he da abase. A e c ea ing he SSIS Solu ion, one can check he ile, i s loca ion; i
accessible o no , ake a quick look a he ile o ha e a apid scan o wha a e he columns, i he e
exis mul iple shee s, and impo an ly i i is co up ed o no .
A e ha , a se e connec ion is c ea ed, and o cou se es ed o check i i is buil be ween
he SSIS and he co esponding da abase. Bu because he e is po en ial o mo e han one ile o be
execu ed, one is able o c ea e a o each loop o go h ough all he iles placed in hei espec i e
olde .
In ha loop, a da a low is placed. The e a e only wo op ions in his case wi h hese kinds o
da a lows; one wi h a la ile sou ce and his indica es ha he ile ha is being p ocessing a e ype
cs o excel sou ce ile on which i has any o he excel ex ensions (xlsx, xls, xlsm …). Ano he sou ce
o da a is exis ing da a in o he da abases/se e s (which will be discussed la e in his hesis). As a
esul , a e speci ying he da a sou ce ype, da a should be loaded in o i s speci ic able. Fo
go e nance and secu i y easons, a schema was c ea ed speci ically o his p ojec so he employees
13
who need admi ance o hese ables can be g an ed access o his schema and no he whole
da abase. These ables will be c ea ed on spo a e mapping he ile in SSIS. The p ima y keys o
hese ables we e c ea ed wi h he assis ance o he DAs o hei knowledge wi h hese ex ac ions.
And a e inalizing his s ep, he i s pa o he da a low is done.
A e ha , a SQL ask is in oduced o he con ol low o make su e ha he da a is clean
and g an c edibili y o he s o ed da a. The aim o using such p ocedu es is o ensu e ha he
speci ied key do no o e lap and s o e duplica e alues in he ables and he e o e w ong measu es.
These p ocedu es ope a e by selec ing he da a i s and inse ing hem in o a p ocessing able. Then
check he key wi h he FACT able, i a ma ch happens hen upda e he da a o he wise upload i as is.
A e he p ocedu e ge s execu ed, he ile is now emo ed o an a chi e ile wi h he same
o ma bu wi h oday's da e added o i s name o keep ack o e he iles p ocessed and o help
e- ack e o s i i is he case and o p e en iles om o e lapping.
Figu e 7 – A scheme o he SSIS loop componen s om con ol low o da a low.
14
And he e is an example o how he SQL ask is con igu ed:
Figu e 8 – Example o a code o duplica e check p ocedu e.
This p ocess is done o all he olde s ha p o ide aw da a iles. A e he da a ge s s o ed in he
p ocessing ables, hen a p ocedu e is execu ed and measu emen s a e calcula ed.

15
Figu e 9 – A scheme o he en i e SSIS in eg a ion p ocess.
3.1.2. Mapping and P ocessing
A mapping ile is hen p o ided by he DA esponsible o his p ojec . In his ile, one can
de e mine wha a e he KPIs ha need o be calcula ed and he componen s needed o hese
measu emen s o be gene a ed. These mappings a e hen s o ed in a able o be used in he cube
me ics.
In his de ini ion able, he measu emen s a e speci ied based on a ype (addi ion,
sub ac ion, di ision, and/o mul iplica ion). So, he ype is p o ided o he ope a ion as well as he
componen s o be calcula ed.
Figu e 10 – SQL able showing an example o how he da a is being calcula ed.
In Figu e 10, one can see ha he ype o his ope a ion ( ype 1) is summa ion o only one
componen .
16
A e he iles ha e been in eg a ed in o hei espec i e ables, a s o ed p ocedu e is
c ea ed o join all he da a in one FACT able.
3.1.3. Fac able
This pa will co e ho oughly how he p ocess was buil , how i unc ions, and wha a e he
challenges ha we e aced wi h coding o da a i sel .
S a ing wi h how his p ocess was buil , and a e knowing how he dimensions look, he
ideal s a egy was o see how o combine all he da a ound in o a global able ha con ains all he
columns equi ed o measu e he equisi e KPIs. As a i s s ep, all he da a was sa ed in emp ables,
and because one needs o sepa a e he columns ha a e needed o calcula ions and he o he s ha
will be used o joins la e , pi o ing mus be used o achie e ha goal. Pi o ing is a mechanism used
o ans o m columns in o ows. So, wi h his, now mul iple da ase s ha e almos he same key bu
wi h di e en ield alues.
A e his ans o ma ion is comple ed, one can uni e all he ables based on he key and
ob ain ields ha ha e he names o he columns and ield alues ha possess he alues s o ed in
ha column. As a esul , one can ob ained a able in which all he columns a e sepa a ed wi h hei
espec i e alues. Mo ing o he nex s ep, he same is pe o med bu wi h he de ini ion able ha
was men ioned be o e (3.1.2. Mapping and P ocessing Figu e 11). These wo ables sha e he same
s uc u e, one o he da a and he o he o he componen dis ibu ion.
Figu e 12 – A scheme o show pi o ing ans o ma ions.
A e collec ing he wo ables, one is able o join based on he ield column. Al hough i is known
ha his solu ion was no he op imal because he join is based on a “S ing” column, and indexing
17
canno be pe o med o e a a cha ; bu his was he mos sui able one in his case and he only
a ailable column o do he join on. As a esul , all he columns om idkpi a e collec ed and needed o
calcula e each KPI sepa a ely, in addi ion o he componen s which will be used wi h hei espec i e
alues. (The wo ows in ig11 a e now a combined ow)
In he nex s ep, pi o ing is implemen ed o he able again on he componen s. The column
componen now will be ans o med in o mul iple columns and each cell in ha column will possess
i s pa icula alue.
Figu e 13 – Table showing he dis ibu ion o componen s and hei alues pe KPI.
A e he p e ious ans o ma ion, a que y is execu ed o do he calcula ions pe KPI. his
que y ope a es by selec ing he componen s and hei alues. Once es ablished, he que y can be
passed o he desc ip ions ound in Figu e 12. These a e SQL s a emen s ha in end o swap he
alues o he componen s and sum hem ins ead.
La e in his p ocess, couple o ables a e joined o append o he ones needed o calcula e
mo e KPIs, addi ionally o p o ide op ion o calcula e he ex a columns eques ed by he DAs. Wi h
ha being said, and a e all he equi emen s a e ul illed o e alua e he inal able, hese ables a e
joined all oge he based on hei [Da e] and o he componen s. One can hen dele e he alues
based on he minimum da e in he able and push he da a o he inal ac able.
Figu e 14 – Table showing each KPI wi h hei Nume a o and Denomina o Values.
18
3.1.4. P oblem Solu ion
Rega ding his me hod, a lo o p oblems we e encoun e ed wi h he iles, da a, and hei
alues. Some o he iles we e uploaded wi h many p oblems, some imes he shee name changes,
and his will a ec he mapping because once an excel ile is mapped in he da a sou ce, he excel
shee name is speci ic and i i is no he e, he da a low will c ash. Ano he p oblem was he
delimi e ; some imes he delimi e ei he changes and messes up wi h he dis ibu ion o he da a
wi h hei columns, o some cell alues con ains he delimi e ; o example, i he delimi e is a ‘,’ and
a cell has some alues wi h ‘,’ like speci ying a lis , hen he alues o he consecu i e columns will
be absolu ely w ong. Rega ding he alues, ex a checking was pe o med when inse ing hem om
he p ocessing schema o hei dimensions by eplacing spaces by emp y space and commas o do s.
Ano he p oblem was aced wi h da e o ma , some iles had di e en o ma s; some imes
dd/MM/YYYY and o he s MM/dd/YYYY and his would lead o KPIs’ miscalcula ions, so uni ying he
da e o ma wi h he DAs was a mus .
Occasionally, some columns migh sha e he same name in di e en ables/ iles, and
because he join is based on he column name, he alues migh o e lap and conduc alse alues. As
a solu ion o ha , an addi ional le e was added o he end o he columns pe ile which leads o
dis inc i e names, and his will no be an issue anymo e.
In eg a ing he whole ile will be excessi e use o space in he se e so a decision was aken
o go wi h he componen s ha a e needed o conduc hese measu es. This op ion was ca as ophic
o calcula ion, bu i was acknowledged once he compa ison be ween he aw da a and he da a
ha was s o ed in he da abase ook place. The eason why is because now mul iple columns migh
sha e he same alues and on joining hem, a lo mo e will be missed. As a p oposi ion, an inc emen
ow numbe was added o e e y ile o keep all he columns, and e en ually his issue will no be
aced anymo e.
Wi h ha now he i s solu ion was eady o go li e.
25
dele ing he single emp able c ea ed, a sc ip will be execu ed o check he da es o he iles ha
we e in eg a ed and dele e hose ha hei da es exceed a week o educe space on he sha ed
se e . And hanks o he PDR iles, a signi ican educ ion o e o s was de ec ed mos ly because he
DAs a e now esponsible o illing all he in o ma ion needed o he ile o be in eg a ed. (They a e
mo e in con ac wi h he clien and mo e awa e o he scena ios ha migh be ace in he u u e).
No only ha , bu he ma gin o e o s was minimized especially wi h he da e o ma s, because
some iles migh come wi h mm/dd/yyyy o ma and o he migh be dd/mm/yyyy. The ac able will
he e o e lack in eg i y. As a esul , wi h hese PDRs, he DAs speci y he o ma o he da e ype, so
in case any ile sha ed di e en o ma , i should be checked.
A nice ea u e was adap ed o he new solu ion which is he abili y o adding ex a columns
ha will be added manually o he da a low. Fo example, i someone wan s o c ea e a new column
based on conca ena ing mul iple columns, i is possible by speci ying ha in a SQL code and adding i
in he able which will se e i s pu pose.
This diagnos ic p ocess allowed mul iple iles ha sha e he same columns bu in a di e en
o de o be p ocessed oge he . The manual mapping will no be needed because he gene ic emp
able c ea ed will ma ch la e wi h he column name which is in he heade o he ile. No only ha ,
bu he p ocess was inalized by elimina ing he use in e en ion and c ea ing scheduled jobs. All
wha needed is o moni o he job ac i i ies and make su e e e y hing is wo king smoo hly.

26
4. FEATURES TO ADD
A e inalizing his solu ion, some ea u es can be included o achie e mo e s abili y and
in eg i y o he da a. Adding a able o keep ack o he ows and columns coun wi h hei
in eg a ion da e is a cons uc i e idea. This will ensu e he da a in eg i y and will apply o an
in eg a ion e o i hese numbe s we e no ma ching. This idea can be ex ended by c ea ing a
Powe Bi epo ha will keep ack o he jobs. While es ing he in eg a ion p ocess, a bug was
ound and needs o be ixed. This scena io occu s i he ile was ype cs . Wi h his speci ic ype, a
delimi e should be de ined. Howe e , i he ile has only 2 columns, his delimi e is ha d o ca ch in
he C# sc ip which migh lead o w ong da a in eg a ion.
In addi ion, i a ile has no key, a me hod should be c ea ed o handle his. A way o ul illing ha
is by selec ing he da e ange in he ile, dele ing da a om he espec i e able wi h he same da e
ange and me ge he da a om he ile o he able. A e me ging and execu ing o he jobs, an
email can be sen wi h he s a us ei he comple ed wi h success o ailu e, wi h he e o and he
ex ension id so ollow up can be pe o med. In case o job ailu e, inse in o a able he excep ion
ha was h own while execu ing ha job o ha e a so o idea wha a e he excep ions ha a e
being gene a ed.
27
5. REPORTING
5.1. SUMMARY
Figu e 19 – Re ealing a summa y o he calcula ed KPIs.
Acco ding o Figu e 18, he ba cha shows he dis ibu ion o a ge s pe LOB. Mo eo e , as
a use , one no only has he abili y o d ill down h ough hese a ge s on a le el mo e han LOB
i sel , bu also h ough he KPIs ha a e dedica ed o i . All o hese da a a e selec ed based on da es
so he e is he po en ial o choose wha o look a and check he a ge de ia ion. On he bo om
igh he e is he scale ha will assis he use o di e en ia e be ween hese de ia ions o a be e
unde s anding. In addi ion, i one p esses on any o hese KPIs, i will show he dis ibu ion pe
language.
28
So, o example i he desi ed case is o s udy he absen eeism LOB, all wha has o be done
is click on i and i will show some hing like Figu e 19.
Figu e 20 – D ill down one o he KPIs.
All o hese da a a e p o ided by he inal able ha was al eady induced be o e.
5.2. TREND
Figu e 21 – Ba cha showing he end pe LOB, KPI, Coun y, Si e, and Language.
29
5.3. KPI
Figu e 22 – G aphs o compa e mul iple KPIs.
In his pa , a eques was demanded o p o ide a way o compa e mul iple KPIs a once wi h
speci ici ies ega ding languages and si es. This eques was o he g oup o keep ack o all hese
di e en si es a once and o check o p oblems o u u is ic isks in any o he si es/languages. As
seen in Figu e 21 abo e, he compa ison he e is be ween absen eeism, adhe ence and AHT.
I p essed on any o he si es, he g aphs will elimina e all o he si es excep he selec ed one
and will show all he da a ega ding i . I is also easible o choose a language and/o si e o p o ide
he use s wi h a be e unde s anding o wha e e hey a e looking o .
30
5.4. GLOSSARY
In conclusion, a glossa y was p o ided in case any DA ha is new o he p ojec o e en a highe
anked posi ion colleague would like o see he sou ce o calcula ions and how hey a e done o
answe any doub . These glossa ies a e based on bo h he KPI g oup and he KPI i sel .
Figu e 23 – Glossa y o all measu emen s used in he p ojec .
As a esul o his wo k, he p ojec was done. Some mo e KPIs migh show in he u u e o
be added bu will ollow he same p ocedu e o calcula ion and isibili y.

31
6. CONCLUSIONS
The da a in eg a ion p ojec ook i s i s baby s eps in Feb ua y 2021 and began wi h a di e in o
he business s a egies and a be e unde s anding o wha a e he equi emen s he DAs need.
Va ious mee ings wi h hem we e done o align expec a ions, and o unde s and hei goals.
Once hese condi ions we e me , he p oduc ion s a ed wi h speci ying he s a egy i will
o e ake. Fi s i began wi h checking he inpu ile ype, hen es ablishing he SSIS p ocess, and
inalizing i wi h he inal solu ion.
A e ha ing he inal ac able, a epo was gene a ed o help isualizing he esul s in a
iendly way, helping o he un echnical colleagues il e and unde s and he da a, allowing hem o
na ow down o a deepe di e h ough he da a, and de eloping insigh s o imp o e he ma ke and
acco dingly he p o i .
This p ojec pe mi ed he u iliza ion o nume ous concep s in oduced in he i s yea o he
Mas e ’s in Da a Science and Ad anced Analy ics. The p ocess was buil in ETLs and SQL, a so wa e
ha became amilia in he Da a S o age and Reco e y cou se. Va ious business app oaches as well
we e gained h oughou he p ocess especially he pa ela ed wi h he KPIs and hei
measu emen s and o wha he imply o. All in all, he expe ience was ully ela ed o he mas e ’s
concep s and opened he ga e o me o pu sue a ca ee as a da a enginee .
32
7. LIMITATIONS AND RECOMMENDATIONS FOR FUTURE WORKS
Du ing he de elopmen and a e achie ing he essen ial inal ac able, a lo o limi a ions we e
aced and he e a e some o hem. The i s was in case he SQL agen wen down o any eason,
hen his equi es a use in e e ence o un all he p ocesses manually by p o iding he SSIS he
ex ension id o each ile. Addi ionally, he iles a e being p ocessed in a C# sc ip ha goes h ough
he ile, checks he ype and in eg a e i acco dingly. In case he ile was a cs , a delimi e should be
de ined and in case he ile has only 2 columns, his delimi e is ha d o ca ch.
On he o he hand, some ea u es can be included o achie e mo e s abili y and in eg i y o he
da a. A able can be added o keep ack o he ows and columns coun wi h hei in eg a ion da e.
This will ensu e he da a in eg i y. By Sending an email wi h he s a us o he job once done, i will
gi e he DAs a heads up abou he issue so hey will no ha e o ge back o he DE depa men o
know he s a us o he job and he e o will be easie o be ack and as e o be ea ed. In case o
job ailu e, inse in o a able he excep ion ha was h own while execu ing he job o ha e a so o
idea wha a e he excep ions ha a e being gene a ed.
33
8. REFERENCE
• B. Nelson and T. Olo sson, "Secu i y and p i acy o big da a: A sys ema ic li e a u e
e iew," 2016 IEEE In e na ional Con e ence on Big Da a (Big Da a), 2016, pp. 3693-3702.
• Coleba ch HK (2014) Making sense o go e nance. Policy and Socie y 33(4): 307–316.
• H. Bhasin. (2020). Wha is Cus ome Expe ience Managemen and i s Impo ance?
• J. Dinis-Ca alho. (2020). P esen a ion KPI. 10.13140/RG.2.2.32344.11521.
• J. Mo ow (2020). Repo ing Made Easy: 3 S eps o a S onge KPI S a egy
• LINDELL, JIM. (2020). Big Da a His o y – Big Da a Sou ces and Cha ac e is ics. 2‐1-2‐22.
10.1002/9781119784692.ch2.
• M., Smallcombe. (2019) Top 5 Reasons o Cen alize Da a & Become a Da a-D i en
Business.
• M.T, Raghu aman. (2021). Assessmen o ETL TOOLS ABSTRACT.
• M. Van Assel , O. Renn (2011) Risk go e nance. Jou nal o Risk Resea ch 14(4): 431–449.
• S. Kameliq (2021) Ad an ages o Da a Mining o Digi al T ans o ma ion o he Educa ional
Sys em.
• Shabana, Mahammad & Sha ma, K. (2021). A STUDY ON BIG DATA ADVANCEMENT AND
BIG DATA ANALYTICS.
Page | i