Ministry of Higher Education
And Scientific Research
University of Tunis
Higher Institute of Management
Graduation Project Report
Bachelor degree: Business Intelligence
The implementation of a Business Intelligence solution for
Commercial Management
Host Company: WIMBEE TECH
Elaborated By
Dabbebi Rihab Ben Ali Jihene
Academic supervisor: Professional supervisor:
Dr. Rejeb Lilia Mrs. Fouzri Manel
Academic year
2019-2020
Acknowledgment
We take this opportunity, to express our sincere and heartfelt gratitude to each and every
member who had offered help to accomplish this work successfully.
We are grateful to our academic supervisor Dr.Lilia Rejeb head of computing department
in higher institute of management, for accepting to frame our work. For her judicious
remarks, her time and patience, her trust, support, and her availability in spite of the
responsibility she have.
We are also indebted to our Professional supervisor, head of engagement in wimbeetech
Mrs. Manel Fouzri for, her encouragement and for all the effort and time she provided us
with. For giving us the opportunity to learn and offering us invaluable advices throughout
the project. It was a great privilege and honor to work and study under her guidance.
We are thankful to Mrs. Manel Jrad the global talent manager for this precious opportunity,
her trust support and effort to ensure a comfortable work environment along with affordable
circumstances.
We would like to express our gratitude to wimbee tech team for their time help and their
interest in our work.
Finally, our recognition is addressed in advance for the effort devoted by our professors
during our study years, and for the entire Direction of the Higher Institute of Management
and to committee members, for the interest that they bring to our work.
Dedication
To the dearest and most precious person in my life, that I dedicate the fruit of my efforts, to the most
generous and wonderful mother Houda. May this work be my first sweet reward for you and more is
yet to come. You have been cherishing us for years with endless love and support. My time has come
to prove to you that you did an amazing work you did it in the most perfect ways. So here i ‘am today
making my first achievement and in this special opportunity I want to tell you how grateful i ’am for
everything you did for me, for my sister and for our family.
In Loving memory of my beloved grandparents, Belhassen and Jannet. I wish you were here so much.
May Allah have your souls in his holy mercy.
To my dear father Hichem, who has been waiting impatiently for the accomplishment of this work
with great success. I dedicate this work for you and I hope that I will make you proud.
I owe my deepest gratitude for , my second mom and my wonderful aunt Lamia, for always being here
for me and pushing me forward , thank you for everything you did for me , and i’ll make sure to make
you proud of me.
I’am also indebted for my uncle Hichem for being there for me ever since my childhood and being the
supportive friend and mentor during the hard times.
This work is also dedicated to the most beautiful sister Amira, I ‘am so proud of you and I hope
someday you will be proud of me. I am grateful to you for always being there for me as a friend and
will always be indebted for you for being my source of inspiration .Your encouraging words had always
ringed in my ears thank you for everything. My best wishes and prayers for your new promising life
with your lovely and supportive partner Bilel. I wish you both a long journey full of love and happiness.
The hard moments were the sweetest with my siblings Mohsen and Belhassen, the most lovable and
supportive brothers, I wish you both all the success and happiness in your future.
To the dearest best friend and most exceptional sister Raouaa , thank you for supporting me, and
being here for me in the toughest times. I wish you all the success and happiness.
To my very good friend Yassine, thank you for being a good listener and a protective brother
To my very good friend Wael, thank you for your support and your motivational kind words you are
unique.
To my beloved best friend and best, project partner, to the most adorable sister Rihab, thank you for
inspiring me and always being here for me in hardest moments. This work is the fruit of our hard work
and long sleepless nights. Celebrating graduation success with you would be the most original and
symbolic day.
Ben Ali Jihene
Dedication
I thank Allah the Almighty for giving me the courage, the will and the strength to do this work.
Publicité
For me the family are the major key of success so I want to start by thanking my beloved family.
The two person who gave me life and made me who I am today, who gave me all the love and support.
My treasure, the beating heart of love and tenderness, my beloved mother Chawechi Moufida for her
Unstoppable love and support. The one who encourage me and remind me of my goals every single
day, and push me forward to dream more and chase my dreams.
My protector, my inspiration, my role Model, My precious Father Dabbebi Hafneoui , as a sign of
love, gratefulness and gratitude for all the supports and sacrifices you made towards us , this work is
deeply for you , for your effort to see us happy successful and effective .
In memory of my beloved Grandfather No tribute will be expressive enough to show and testify what
I feel about you and how much respect I have for you. In honor of the dearest, most lovable
Grandfather Dabbebi Salah.
To my wonderful grandmother Chawechi Hajja, the one who always tell me her dream is taking her a
ride with my car after being successful and powerful woman.
To my mentor, my first teacher, the one who cherish learning and knowledge, who really believe in the
famous proverb seek knowledge from the cradle to the grave, The pure loving caring heart, who
believed in me and my potentials, my dearest Uncle Dabbebi Amara .
To my precious young siblings, my sweet loving sister Dabbebi Siwar, and my little charming brother
Dabbebi Motaz, all my love and affection for you. I pray Allah to see you successful and happy.
I also believe that anything is possible when you have the right people there to support you. And I have
the best and the most special.
To my childhood true friend and sister Ben Salah Amal you always been there for me, supporting and
helping me. I wish you all the best.
To the purest heart, the one who knows truly who i am. And push me forward to be the best version
of me. To the most lovable sister Chebbi Raouaa. Thank you for everything.
To my project partner, my beautiful brave friend, my supporter and motivating sister Ben Ali Jihene,
thank you for your support, love and being the friend that I dreamt of, I wish you all the best success
love and happiness that you deserve.
To all the people who believed in me, who gave me the power to dream more and seek for achieving
them , thank you all and this work is for you …
Dabbebi Rihab
Table of content
General Introduction ............................................................................................................... 1
Chapter 1: Project Scope ................................................................................................................. 3
1 Introduction ...................................................................................................................... 3
2 Presentation of the Host organization ............................................................................... 3
2.1 The Organigram ........................................................................................................................ 4
3 Project context ..................................................................................................................... 5
3.1 Analyzing the existing ...................................................................................................... 6
3.2 Criticizing the existing...................................................................................................... 6
3.3 Project solution .......................................................................................................................... 7
4 Project management methods [4] ....................................................................................... 7
4.1 Agile methods [5] ...................................................................................................................... 7
4.2 Methodology choice ......................................................................................................... 8
4.2.1 SCRUM BI .................................................................................................................... 8
4.2.2 GIMSI ............................................................................................................................ 9
4.2.3 KANBAN ...................................................................................................................... 9
4.2.4 Extreme Programming (XP) ........................................................................................ 10
5 Scrum applied to BI Projects [9] ....................................................................................... 12
5.1 Scrum evolution with Top Down approach .................................................................... 12
5.2 Scrum evolution with Bottom up approach .................................................................... 12
5.3 Adopted Approach with SCRUM................................................................................... 12
6 Conclusion ......................................................................................................................... 13
Chapter 2: Technical Requirements .................................................................................... 14
1 Introduction ....................................................................................................................... 14
2 Data warehouse Database Model ...................................................................................... 14
2.1.1 Relational model [11] .................................................................................................. 14
2.1.2 Dimensional Model [10] .............................................................................................. 14
2.1.3 Hybrid Model [12] ....................................................................................................... 15
2.2 Data Warehouse design approaches ............................................................................... 15
2.2.1 Bill Inmon Approach [13] ........................................................................................... 15
2.2.2 Ralph Kimball Approach [13] ..................................................................................... 17
2.3 Schema Types [17] ......................................................................................................... 20
2.3.1 Star Schema ................................................................................................................. 20
2.3.2 Snowflake Schema ...................................................................................................... 20
2.3.3 Galaxy schema ............................................................................................................. 20
2.4 Choice ............................................................................................................................. 20
3 Business Intelligence Tools ............................................................................................... 22
3.1 Database Management tools ........................................................................................... 22
3.1.1 Microsoft SQL Server [19] .......................................................................................... 22
Publicité
3.1.2 Oracle ........................................................................................................................... 22
3.1.3 Choice .......................................................................................................................... 23
3.2 ETL and Data Integration’s tools comparison ................................................................ 24
3.2.1 Talend [21] .................................................................................................................. 24
3.2.2 Pentaho [22] ................................................................................................................. 24
3.2.3 Microsoft Sql Server Integration (SSIS) [23] .............................................................. 25
3.2.4 Informatica Power center [24] ..................................................................................... 25
3.2.5 Choice check ................................................................................................................ 26
3.3 Data visualization tool’s comparison [26] ...................................................................... 26
3.3.1 Microsoft Power BI ..................................................................................................... 26
3.3.2 Qlik Sense .................................................................................................................... 27
3.3.3 Tableau ........................................................................................................................ 27
3.3.4 Choice .......................................................................................................................... 27
Conclusion ............................................................................................................................ 28
Sprint 0: Functional Requirements ....................................................................................... 29
1 Introduction ....................................................................................................................... 29
2 Analysis and needs specification ....................................................................................... 29
2.1 Organization Actors ........................................................................................................ 29
2.2 Product Backlog ............................................................................................................. 29
2.3 Non-functional requirements .......................................................................................... 31
3 Project Plannification ........................................................................................................ 31
3.1 Project Actors ................................................................................................................. 31
3.2 Sprints Planning .............................................................................................................. 33
3.3 Gant Diagram ................................................................................................................. 33
3.4 Project Architecture ........................................................................................................ 34
Sprint 1: Data warehouse Model .......................................................................................... 36
1 Introduction ....................................................................................................................... 36
2 Sprint Backlog ................................................................................................................... 36
1 Setting the business Specifications .................................................................................... 37
2 Logical model .................................................................................................................... 39
2.1 Fact table and dimensions............................................................................................... 39
2.1.1 Dimensions .................................................................................................................. 40
2.2 Fact Table ....................................................................................................................... 42
2.1.2 Bridge table.................................................................................................................. 42
3 Data Manipulation ............................................................................................................. 44
4 Physical Model .................................................................................................................. 46
4.1 Design of the Physical Database .................................................................................... 46
4.2 Tables Generation within the database ........................................................................... 46
Conclusion ............................................................................................................................ 51
Sprint 2: Data Integration ..................................................................................................... 52
1 Introduction ....................................................................................................................... 52
2 Sprint Backlog ................................................................................................................... 52
2 Data Extraction .................................................................................................................. 52
2.1 Creating Metadata ........................................................................................................... 53
2.1.1 Establishing database connection ................................................................................ 53
2.1.2 Creating delimited files ............................................................................................... 54
2.1.3 Job Construction .......................................................................................................... 55
4.1 Date formatting ............................................................................................................... 56
4.2 Data Types conversion ................................................................................................... 57
5 Data Loading ..................................................................................................................... 57
5.1 Static Dimensions ........................................................................................................... 57
5.2 Slowly changing dimensions .......................................................................................... 59
5.3 Rapidly changing dimensions ......................................................................................... 61
5.4 Fact Table ....................................................................................................................... 62
6 Conclusion ......................................................................................................................... 64
Sprint 3: Reporting ............................................................................................................... 65
1 Introduction ....................................................................................................................... 65
2 Sprint Backlog ................................................................................................................... 65
3 The company’s key performance indicators ...................................................................... 66
4 Data Import ........................................................................................................................ 67
5 Building the Business metrics and familiarizing with DAX ............................................. 70
5.1 Opportunity Dashboards ................................................................................................. 71
5.1.1 The number and value of opportunities in the pipe ..................................................... 71
5.1.2 Opportunity conversion rate: ....................................................................................... 73
5.1.3 Opportunities Count .................................................................................................... 78
5.2 Sales Dashboards ............................................................................................................ 84
5.2.1 Achieved Turnover ...................................................................................................... 84
5.2.2 Gross Margin Rate ....................................................................................................... 86
5.3 Customer Dashboard ...................................................................................................... 88
Publicité
5.3.1 Customer Count ........................................................................................................... 89
5.3.2 Customer Satisfaction Rate ......................................................................................... 92
5.3.3 Prospect conversion Rate ............................................................................................. 95
5.3.4 Customer Retention ..................................................................................................... 96
Conclusion: ........................................................................................................................... 98
General Conclusion .............................................................................................................. 99
Bibliography & Netography ............................................................................................... 101
Table of Figures
Figure 1: Wimbee’s services ...................................................................................................... 3
Figure 2: Wimbee’s Organigram ................................................................................................ 4
Figure 3: Model of the activities, processes, capabilities and knowledge that underpin the
commercial practice [2] .............................................................................................................. 5
Figure 4: Scrum Lifecycle [7] .................................................................................................... 8
Figure 5: The Top Down approach [14] ................................................................................... 15
Figure 6: The Bottom Up approach [14] .................................................................................. 17
Figure 7: Relational model with many to many relationships [18] .......................................... 21
Figure 8: Data integration concepts [23] .................................................................................. 24
Figure 9: Scrum stakeholders ................................................................................................... 32
Figure 10: Sprints Planning ...................................................................................................... 33
Figure 11: Gant Chart ............................................................................................................... 33
Figure 12: The Project Architecture ......................................................................................... 34
Figure 13: Operational data store schema ................................................................................ 45
Figure 14: Database Generator in power designer ................................................................... 46
Figure 15: Representation of the data warehouse in SQL Server ............................................ 47
Figure 16: SQL Query for fact Table generation ..................................................................... 47
Figure 17: SQL Query for Currency Dimension ...................................................................... 48
Figure 18: SQL Query for Opportunity_Team (Bridge table) ................................................. 48
Figure 19:SQL Query for Consultant Dimension .................................................................... 48
Figure 20: SQL-Server-Sequence ............................................................................................ 49
Figure 21:SQL Query for time dimension. .............................................................................. 49
Figure 22:Design process Hierarchy ........................................................................................ 49
Figure 23 : The Physical Data warehouse’ Design .................................................................. 50
Figure 24: Metadata wizards. ................................................................................................... 53
Figure 25: Established Connection with the ODS database ..................................................... 53
Figure 26: Centralizing the opportunity delimited file ............................................................. 54
Figure 27: The Opportunity File Schema ................................................................................. 54
Figure 28 : Loading OD_Opportunity:.................................................................................... 55
Figure 29: Tmap Component ................................................................................................... 55
Figure 30: Settings of the Output component. ......................................................................... 55
Figure 31: Example of Date Formatting in the Expression Constructor .................................. 56
Figure 32: Example of data transformation scheme ................................................................. 56
Figure 33:Example of string handling ...................................................................................... 57
Figure 34:Example of float rounding ....................................................................................... 57
Figure 35: Populating the domain dimension .......................................................................... 58
Figure 36: Populating the Opportunity type dimension ........................................................... 58
Figure 37: Time Stored Procedure ........................................................................................... 58
Figure 38: Loading the Consultant Dimension ........................................................................ 59
Figure 39: SCD type 2 implementation .................................................................................... 60
Figure 40: Flag_Historize expression for the table consultant dimension ............................... 60
Figure 41: Flag_Historize expression for the table consultant dimension ............................... 60
Figure 42: Is member expression for the opportunity team table ............................................ 61
Figure 43: Sql Query for extracting recent consultants ............................................................ 61
Figure 44 : Sql Query for extraction from the ods ................................................................... 62
Figure 45: Prospect job structure ............................................................................................. 62
Figure 46: Opportunity lookup table ........................................................................................ 63
Figure 47: Loading the Fact Table ........................................................................................... 63
Figure 48: tUniqrow component .............................................................................................. 64
Figure 49: Connecting to the SQL server Data Base ............................................................... 67
Figure 50: Selecting the necessary Table ................................................................................. 68
Figure 51: Data Charging ......................................................................................................... 69
Figure 52: Model Presentation in Power BI ............................................................................. 69
Figure 53: Table Presentation in Power BI .............................................................................. 70
Figure 54: The different visuals of Power Bi ........................................................................... 70
Figure 55: the kpi’s calculation using power BI visualization option ...................................... 72
Figure 56: The number and value of opportunities in the pipe per funnel chart ...................... 72
Figure 57: Win probability per numeric range slicers .............................................................. 73
Figure 58: Values Slicer .......................................................................................................... 73
Figure 59: Values Slicers Illustration ....................................................................................... 73
Figure 60: Date slicer ............................................................................................................... 73
Publicité
Figure 61: Performing a row operation on the updated closing date column .......................... 74
Figure 62: The Dax Query of the column type transformation ................................................ 74
Figure 63: The Dax Query for the year abstraction ................................................................. 75
Figure 64: the KPI calculation queries result ........................................................................... 75
Figure 65: Opportunity Conversion Rate per Basic Area chart ............................................... 76
Figure 66: Opportunity Conversion Number per Basic Map ................................................... 76
Figure 67: Opportunities visualization Dashboard ................................................................... 77
Figure 68: Manipulated Opportunities visualization Dashboard ............................................. 77
Figure 69: Dax Query calculating the highest Revenue ........................................................... 78
Figure 70: Dax Query calculating the lowest Revenue ............................................................ 78
Figure 71: Opportunities By opportunity status view with Donut chart .................................. 79
Figure 72: Opportunity Count per year visualized in a combo chart ....................................... 79
Figure 73: Opportunity Count by opportunity type visualized in a combo chart .................... 80
Figure 74: Opportunity Count Dashboard ................................................................................ 80
Figure 75: Opportunity Count Dashboard Manipulated with win probability slicer ............... 81
Figure 76: Dax Query measure Calculation ............................................................................. 82
Figure 77: Dax Query calculating the number of satisfied Opportunities ............................... 82
Figure 78 : Satisfied Opportunities with Zoom charts ............................................................. 82
Figure 79: Satisfied Opportunity Count Dashboard ................................................................. 83
Figure 80: Manipulating the Satisfied Opportunity Count Dashboard. ................................... 83
Figure 81: Turnover visualization with advanced gauge. ........................................................ 84
Figure 82: Turnover Visuals per line Clustered Column Combo Chart .................................. 85
Figure 83: Achieved Turnover Dashboard. .............................................................................. 85
Figure 84: Manipulated Achieved Turnover Dashboard .......................................................... 86
Figure 85: Gross Margin Rate per Chord Diagram .................................................................. 87
Figure 86: Gross Margin Rate Dashboard................................................................................ 87
Figure 87: Filtred Gross Margin Rate Dashboard .................................................................... 88
Figure 88: The Customer Number Dax query .......................................................................... 89
Figure 89: Prospect Number Dax query ................................................................................... 90
Figure 90: Illustrating the customer By Real Revenue Using multi row Cards ....................... 90
Figure 91: Customer Status explication ................................................................................... 91
Figure 92: Dax query calculating the formula ......................................................................... 91
Figure 93: Dax query active/inactive customer ........................................................................ 91
Figure 94: The KPI presentation in a treemap chart ................................................................ 92
Figure 95: The customer count and satisfaction rate dashboard .............................................. 93
Figure 96: The customer count and satisfaction rate manipulated dashboard ......................... 93
Figure 97: Customer Analyze .................................................................................................. 94
Figure 98: Manipulating the Customer analyze by slicers ....................................................... 94
Figure 99: Prospect conversion number Query Calculation .................................................... 95
Figure 100: Prospect Conversion Rate per line chart ............................................................... 96
Figure 101: The active number query ...................................................................................... 96
Figure 102: The customer retention rate calculation query ..................................................... 96
Figure 103: The prospect conversion rate and the customer retention rate dashboard ............ 97
Figure 104: Filtering the prospect conversion rate and the customer retention rate dashboard
.................................................................................................................................................. 97
Table of Tables
Table 1: Scrum Vs GIMSI ......................................................................................................... 9
Table 2: Scrum Vs Kanban [7] ................................................................................................. 10
Table 3: Scrum Vs XP [8] ........................................................................................................ 11
Table 4: The Data Mart vs Data warehouse [14] ..................................................................... 18
Table 5 : Kimball vs Inmon in data warehouse architecture [16] ............................................ 19
Table 6: SQL server vs Oracle [20] ......................................................................................... 23
Table 7: ETL tools comparison according to data quality dimensions [25] ............................ 26
Table 8:Tableau vs Qlik Sense vs Power BI [27] .................................................................... 27
Table 9: Product Backlog ......................................................................................................... 31
Table 10: Sprint Backlog .......................................................................................................... 36
Table 11: Business Document .................................................................................................. 38
Table 12:The Data warehouse Dimensions .............................................................................. 41
Table 13: The Data warehouse Measures ................................................................................ 42
Table 14: The structure of our bridge table .............................................................................. 43
Table 15: The Extracted Source Files ...................................................................................... 44
Table 16: Sprint Backlog .......................................................................................................... 52
Table 17: Sprint Backlog .......................................................................................................... 65
Table 18: KPI’s Categories ..................................................................................................... 67
Table 19: Opportunity Kpi’s Resume ...................................................................................... 71
Table 20: Sales business metrics .............................................................................................. 84
Table 21: Calculating Gross Margin by Opportunity per Calculated Column. ....................... 86
Table 22: Summary table of the Customer Kpis ......................................................................