Carthage University
Faculty of Economy and Management of Nabeul
Master’s Thesis
Professional Masters in Knowledge and
Information System Engineering
Development and implementation of a decision-
making solution for tracking purchases and sales for
a retail company
Work done by : Hichem KHALFALLAH
Inside Anouar Market
Assisted by :
Mr. Amara BENJEDDOU (FSEGN)
Mr. Mohamed Ali TAAMALLAH (Anouar Market)
College Year: 2017 - 2018
Dedication
I dedicate this work to my father Mohamed KHALFALLAH and my mother
Samia CHOUK who always supported me in everything I do, no matter how
small or important it is.
Being there for me helped me a lot during my thesis. They gave me their faith,
support and encouragement.
Without them, none of my success would be possible. Thank you.
To my friends who helped me numerous times. I salute them with respect and
recognition.
God bless all of you.
“Nobody likes to fail. I want to succeed in everything I do, which isn’t
much. But the things that I’m really passionate about, if I fail at those, if
I’m not successful, what do I have?”
Marshall Bruce Mathers III
i
Thanks to
I kept all of this page just to thank all those who helped me during this work. I salute them all with
recognition for their efforts and will never forget them.
First, I address my thanks to all the professors involved in the Professional Masters of Knowledge and
Information System Engineering, each one of them spent the needed time explaining precious lessons
and were available even after college hours for additional tips.
Secondly, kind regards to Mr. Amara BENJEDDOU who always answered me and was available every
time I had concerns or issues. He was also my professor for two years and taught me and my fellow
students many things driven directly from organizations and businesses, he gave us an opportunity to
practice multiple tools and technologies that simulates the real-world applications.
I also address my thanks to Mr. Mohamed Ali TAAMALLAH since he was in charge of me in Anouar
Market’s headquarters and was always available to answer my questions, guide me, explain the
company’s business process and assisted my work.
My thanks to Mr. Anouar MEHREZ who accepted to have me in his retail company and gave me access
to the data and always greeted me in his headquarters.
Finally, my warm thanks also addressed for members of the jury that will evaluate my work.
ii
Table of contents
Dedication .................................................................................................................................................... i
Thanks to .................................................................................................................................................... ii
List of figures ............................................................................................................................................ vi
List of tables ............................................................................................................................................ viii
List of abbreviations ................................................................................................................................. ix
General introduction ................................................................................................................................. 1
Chapter I : Business Intelligence .............................................................................................................. 2
I. Introduction ........................................................................................................................................ 2
II. BI concepts ........................................................................................................................................ 2
II.1 BI definition ................................................................................................................................ 2
II.2 Operational system Vs decision-making system ...................................................................... 2
III. BI components ................................................................................................................................. 3
III.1 Data warehouse : Meaning, characteristics and benefits ...................................................... 3
III.2 Data mart ................................................................................................................................... 4
III.3 Data warehouse vs data mart................................................................................................... 4
III.4 Multidimensional modeling...................................................................................................... 5
III.5 Different models of multidimensional modeling .................................................................... 5
III.6 Multidimensional modeling Vs entity-relationship modeling ............................................... 6
III.7 Reporting ................................................................................................................................... 7
III.8 Dashboards ................................................................................................................................ 7
III.9 Data mining ............................................................................................................................... 7
IV. Architecture of a decision-making system .................................................................................... 8
IV.1 Extract, transform and load process ....................................................................................... 8
IV.2 Stocking ...................................................................................................................................... 8
IV.3 Exploiting ................................................................................................................................... 9
V. Features ............................................................................................................................................. 9
VI. Building approach for a data warehouse ...................................................................................... 9
VI.1 Data warehouse modeling......................................................................................................... 9
VI.2 Data warehouse development................................................................................................. 11
iii
VI.3 Data warehouse maintenance ................................................................................................. 11
VII. BI advantages ............................................................................................................................... 11
VIII. Conclusion .................................................................................................................................. 12
Chapter II : Project scope ....................................................................................................................... 13
I. Introduction ...................................................................................................................................... 13
II. Company presentation ................................................................................................................... 13
III. Project presentation ...................................................................................................................... 13
III.1 Existing technologies ............................................................................................................... 13
III.2 Actual difficulties .................................................................................................................... 14
III.3 Proposed solution .................................................................................................................... 14
III.4 Key performance indicators................................................................................................... 14
III.5 Functional needs ..................................................................................................................... 14
III.6 Non-functional needs .............................................................................................................. 15
III.7 Methodology of realization ..................................................................................................... 15
Advertisement
IV. Conclusion...................................................................................................................................... 15
Chapter III : The decision-making solution .......................................................................................... 16
I. Introduction ...................................................................................................................................... 16
II. Data mart modeling ........................................................................................................................ 16
II.1 Sales data mart : Sales_DM ..................................................................................................... 16
II.2 Invoices data mart : Invoices_DM .......................................................................................... 18
III. Conclusion ..................................................................................................................................... 21
Chapter IV : The decision-making solution setup ................................................................................ 22
I. Introduction ...................................................................................................................................... 22
II. Hardware and software environment ........................................................................................... 22
II.1 Used hardware .......................................................................................................................... 22
II.2 Used software ............................................................................................................................ 22
III. ETL ................................................................................................................................................ 23
III.1 Used components ..................................................................................................................... 23
III.1 Extract...................................................................................................................................... 23
III.1 Transform ................................................................................................................................ 24
iv
III.1 Load ......................................................................................................................................... 24
IV. Dashboards using Microsoft Power BI ....................................................................................... 26
IV.1 Miscellaneous information ..................................................................................................... 26
IV.2 Best suppliers and purchases amount dashboards............................................................... 26
IV.1 Local retail stores revenues comparison dashboards .......................................................... 27
IV.1 Sales per retail stores dashboards ......................................................................................... 28
IV.1 Retail stores sales evolution dashboards ............................................................................... 30
IV.1 Products sale evolution dashboards ...................................................................................... 32
V. Conclusion ....................................................................................................................................... 33
General conclusion .................................................................................................................................. 34
Webography ............................................................................................................................................. 35
v
List of figures
Figure 1 : Star schema ................................................................................................................................. 5
Figure 2 : Snowflake schema....................................................................................................................... 6
Figure 3 : Constellation schema .................................................................................................................. 6
Figure 4 : Architecture of an ETL process .................................................................................................. 8
Figure 5: Date dimension table .................................................................................................................. 16
Figure 6: Product dimension table ............................................................................................................. 16
Figure 7: Retail Store dimension table ...................................................................................................... 17
Figure 8: Sales fact table ........................................................................................................................... 17
Figure 9: Sales data mart ........................................................................................................................... 18
Figure 10: Date dimension table ................................................................................................................ 18
Figure 11: Retail Store dimension ............................................................................................................. 19
Figure 12: Supplier dimension .................................................................................................................. 19
Figure 13: Invoice fact table ...................................................................................................................... 20
Figure 14: Invoice data mart ...................................................................................................................... 20
Figure 15 : Database connection ............................................................................................................... 23
Figure 16 : Excel file retrieval ................................................................................................................... 24
Figure 17 : Job Retail Store dimension table ............................................................................................. 24
Figure 18 : Job dimensions load ................................................................................................................ 24
Figure 19 : Dimension mapping ................................................................................................................ 25
Figure 20 : Fact table Sale loading ............................................................................................................ 25
Figure 21 : Job dimension Supplier ........................................................................................................... 25
Figure 22 : Fact table Invoice loading ....................................................................................................... 26
Figure 23 : 2015 Amount .......................................................................................................................... 26
Figure 24 : 2016 Amount .......................................................................................................................... 26
Figure 25 : 2017 Amount .......................................................................................................................... 26
Figure 26 : Top 7 suppliers ........................................................................................................................ 27
Figure 27 : Delice supplier ........................................................................................................................ 27
Figure 28 : Dar Chaabene sales comparison ............................................................................................. 28
Figure 29 : Nabeul sales comparison ......................................................................................................... 28
Figure 30 : Hammamet sales comparison .................................................................................................. 28
Figure 31 : Dar chaabene sales .................................................................................................................. 28
Figure 32 : Dar chaabene 2 sales ............................................................................................................... 28
Figure 33 : Dar chaabene 3 sales ............................................................................................................... 28
Figure 34 : Tazarka sales ........................................................................................................................... 29
Figure 35 : Nabeul sales ............................................................................................................................ 29
Figure 36 : Nabeul sales 2 ......................................................................................................................... 29
vi
Figure 37 : Nabeul sales 3 ......................................................................................................................... 29
Figure 38 : Barraket Sahel sales ................................................................................................................ 29
Figure 39 : Beni Khiar sales ...................................................................................................................... 29
Figure 40 : Hammamet sales ..................................................................................................................... 29
Figure 41 : Hammamet sales 2 .................................................................................................................. 29
Figure 42 : Hammamet sales 3 .................................................................................................................. 29
Figure 43 : Korba sales ............................................................................................................................... 29
Figure 44 : Korba 2 sales ............................................................................................................................ 29
Figure 45 : Maamoura sales ...................................................................................................................... 29
Figure 46 : Evolution of beni khiar retail store ......................................................................................... 30
Figure 47 : Evolution of dar chaabene retail store ..................................................................................... 30
Figure 48 : Evolution of hammamet retail store ........................................................................................ 31
Figure 49 : Evolution of korba retail store ................................................................................................ 31
Figure 50 : Evolution of nabeul retail store ............................................................................................... 32
Figure 51 : Evolution of sabrine 1.5l water bottles ................................................................................... 32
Figure 52 : Evolution of apla drink sold .................................................................................................... 33
Figure 53 : Evolution of Delice products sold ........................................................................................... 33
vii
List of tables
Table 1 : Operational system and decision-making system aspects ............................................................ 3
Table 2 : Differences between data warehouse and data mart ..................................................................... 4
Table 3 : Multidimensional modeling Vs. entity-relationship modeling ..................................................... 6
Table 4 : Inmon approach Vs. Kimball approach ...................................................................................... 10
Table 5 : Advantages and disadvantages of Inmon and Kimball approaches ........................................... 10
Advertisement
Table 6: Attributes of Date dimension table .............................................................................................. 16
Table 7: Attributes of Product dimension table ......................................................................................... 17
Table 8: Attributes of Retail Store dimension table .................................................................................. 17
Table 9: Attributes of Sales fact table ....................................................................................................... 18
Table 10: Attributes of Date dimension table ............................................................................................ 19
Table 11: Attributes of Retail Store dimension table ................................................................................ 19
Table 12: Attributes of Supplier dimension table ...................................................................................... 19
Table 13: Attributes of Invoice fact table .................................................................................................. 20
Table 14: Talend components description ................................................................................................. 23
viii
List of abbreviations
ADSL
Asymmetric Digital Subscriber Line
BI
Business Intelligence
CRM
Customer Relationship management
ETL
KPI
OLAP
OLTP
Extract Transform Load
Key Performance Indicator
OnLine Analytical Processing
OnLine Transaction Processing
ix
General introduction
Long gone the days of having no competitors in businesses, back in time, there was usually one
dominant enterprise in each domain, but recently, a lot of businesses have established, competitors are
numerous in all domains. Enterprises keep growing exponentially and this results in huge amount of data
stored. Nowadays enterprises face two major challengers, keeping up with the pace of data growth and
dealing with their competitors.
Most company executives rely on their experience to deal with such problems, but that will not
help when they are against other company executives who try to seize every opportunity for better profit
results, thus it is needed to keep track on information that plays a vital role in decision making, not only
this will help in making quick decisions in critical times but it will also help in predicting the future of
the business.
Usually, businesses have systems that are used to store their data only, these systems have no
purpose other than storing data and cannot help in decision making, thus a new concept has been
developed, which is decision making systems. Their role is to analyze data and create visualizations that
simplify the information and present insights on the current state of business, these decision-making
systems rely on data warehouses as a source for their analyses.
Following this concept, this work is based on developing a decision-making solution for Anouar
Market, a retail company operating in Nabeul governorate. The most important business sector of such
type of companies is the daily sales made in retail stores and the purchases made by the company itself,
thus we need a decision-making solution that supports having insights on these sectors.
This thesis will contain four major chapters :
• The first chapter will define what Business Intelligence is and what are its main components, concepts,
architecture, features, advantages etc.
• The second chapter will be a project scope that presents the retail company Anouar Market, its existing
technologies, the proposed solution for the lack of a decision-making solution and the functional,
nonfunctional needs of this solution.
• The third chapter will handle the decision-making solution, mainly the designing of the data marts
needed in order to develop and implement in the final chapter.
• The fourth and final chapter will go through the setup of this decision-making solution and will present
the different dashboards realized according to Anouar Market’s needs.
• Finally, we will end this thesis with a general conclusion dealing with the work done inside this retail
company.
1
Chapter I : Business Intelligence
I. Introduction
Nowadays, with the introduction of information technology, businesses have changed and
evolved, the usage of such systems plays a big role in making sure that business departments run
smoothly, furthermore, executives have realized that having information about the environment and
competitors would result in a better profit and this has created a new concept which is Business
Intelligence. Having a decision-making system in hand is mandatory in order to help executives take the
right decisions at the right time, therefore it should be a priority to implement such technologies.
In this chapter, we will present the different concepts of BI, the decision-making system, its
architecture and the concepts of dimensional modeling. We will also mention the features, how to build
a data warehouse and finally list BI advantages.
II. BI concepts
II.1 BI definition
Business Intelligence contains a wide variety of tools, applications and methodologies that enable
organizations to collect data from internal systems and external sources; prepare it for analysis; develop
and run queries against that data; and create reports, dashboards and data visualizations to make the
analytical results available to corporate decision-makers. [NET 2]
II.2 Operational system Vs decision-making system
Operational Systems help in managing transaction-oriented applications also called Online
Transaction Processing (OLTP). They are used for order entry, financial transactions, customer
relationship management, retail sales etc. These systems have multiple users who access data
simultaneously, so an operational system must be consistent when dealing with large number of queries.
To understand the differences between an operational system and a decision-making system, the
following table compares different aspects of both systems. [NET 3]
Characteristic
Operational system
Decision-making system
Data purpose
To control and run fundamental business
To help with planning, problem solving and
tasks
decision support
Advertisement
Data content
Reveals a snapshot of ongoing business
Multi-dimensional views of various kinds of
Inserts and
updates
processes
business activities
Short and fast inserts and updates initiated
Periodic long-running batch jobs refresh the data
by users
2
Queries
Relatively standardized and simple queries
Often complex queries involving aggregations
returning few records
Processing speed
Typically very fast
Depends on the amount of data involved
Space
requirements
Can be small if historical data is archived
Larger due to the existence of aggregation
structures and history data
Database design
Highly normalized with many tables
De-normalized with fewer tables; usage of star or
snowflake schemas
Backup and
recovery
Critical, data loss means monetary loss and
Instead of backups, some environments may
legal liability
consider reloading data as recovery method
Table 1 : Operational system and decision-making system aspects
III. BI components
After defining BI and listing the differences between an operational system and a decision-making
system, we will now present in details the BI components.
III.1 Data warehouse : Meaning, characteristics and benefits
III.1.1 Meaning
The most popular definition came from Bill Inmon, known as the father of data warehouse, who
said : “A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of
data in support of management’s decision-making process.” [NET 4]
III.1.2 Characteristics
• Subject-oriented : A data warehouse can be used to analyze a particular subject area. For
example, “sales” can be a particular subject.
• Integrated : A data warehouse integrates data from multiple data sources. For example, source
A and source B may have different ways of identifying a product, but in a data warehouse, there
will be only a single way of identifying a product.
• Time-variant : Historical data is kept in a data warehouse and is organized by various time
periods. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older
data from a data warehouse.
• Non-volatile : Once data is in the data warehouse, it will not change. So, historical data in a data
warehouse should never be altered
3
III.1.3 Benefits
A data warehouse provides the opportunity to :
• Maintain data history, even if the source transaction systems do not.
•
•
Integrate data from multiple source systems, enabling a central view across the enterprise.
This benefit is always valuable, but particularly so when the organization has grown by
merger.
Improve data, by providing consistent codes and descriptions, flagging or even fixing bad
data.
• Present the organization’s information consistently.
• Restructure the data so that it makes sense to the business users.
• Add value to operational business applications, notably customer relationship management
(CRM) systems.
III.2 Data mart
A data mart is an important subset of a data warehouse. It is specifically subject oriented, and it is
designed to meet the needs of a specific group of users. Data marts can be individually designed for
departments like Sales, Finance, etc.
III.3 Data warehouse vs data mart
Data marts are generally controlled by a single department of an organization. The data for these
data marts is assembled only from a few sources. Thus, data mart and data warehouse mainly differ in
their scope and data sources. Table 2, shown below, illustrates the main differences between a data
warehouse and a data mart. [NET 6]
Characteristic
Data warehouse
Data mart
Focus
Control
Scope
Data sources
Size
Designing
Multiple subject areas
Specific subject area
Central organization unit
Generally, single department
Corporate
Advertisement
Multiple
100GB – TB+
Comparatively difficult
Line of business
Few selected
<100GB
Easy
months
Implementation time
Months to years
Table 2 : Differences between data warehouse and data mart
4
III.4 Multidimensional modeling
The main reason for using a multidimensional approach instead of the traditional entity-
relationship modeling is to achieve fast query performance against business data. Since a
multidimensional model has multiple dimensions and fact tables, it can handle complex query
constructions properly. Another reason for using such models is its integration with commonly used BI
reporting tools.
Multidimensional modeling has two major concepts : fact tables and dimension tables. [NET 7]
III.4.a Fact table
Fact tables contain the data corresponding to a particular business process. For example, a retail
organization might have fact tables related to customer purchases. The customer purchases table would
contain information about the amount of the purchase, any discounts applied, and the sales tax paid.
The information contained within a fact table is typically numeric data, and it is often data that can
be easily manipulated, particularly by summing together many thousands of rows.
III.4.b Dimension table
Dimension tables contain the objects involved to a certain business process, while fact tables
correspond to the occurred events, dimensions correspond to people, items, date or other objects.
In a retail organization, dimensions correspond to objects such as customers, items, stores,
employees. Dimension tables contain details about each instance of these objects, they contain
information about each item sold, its cost, the supplier etc.
Fact tables and dimension tables are related to each other ; fact tables contain a foreign key which
is a reference to the primary key of a particular dimension table.
III.5 Different models of multidimensional modeling
III.5.a Star schema
As illustrated in Figure 1, a star schema contains a single fact table and one table for each
dimension, it is easy to understand, easy to define. The fact table contains the foreign key of each of the
dimension tables.
Figure 1 : Star schema
5
III.5.b Snowflake schema
It is a variant of star schema model, it contains one or more tables for the dimension tables as shown
on the example of Figure 2.
Figure 2 : Snowflake schema
III.5.c Constellation schema
In a constellation schema, multiple fact tables share, as illustrated in Figure 3, one or many
dimension tables ; complicated applications require such schemas.
Figure 3 : Constellation schema
III.6 Multidimensional modeling Vs entity-relationship modeling
It is important to understand the differences between a multidimensional modeling and an entity-
relationship modeling, since a multidimensional model is used in data warehouse design ; it is oriented
around understandability, performance and end-user delivery whereas entity-relationship modeling is
useful for illustrating an information system’s entities and the relationship between them.
We illustrate the differences between these two models on Table 3 shown below.
Multidimensional modeling
Entity-Relationship modeling
Data is stored in RDBMS or multi-dimensional database Data is stored in relational database management systems
Data is used in data warehouse and data marts
Data is used for OLTP
Fact tables that are connected to dimensional tables
Several tables and chains of relationships among them
ETL tools are used to manipulate data
SQL is used to manipulate data
User friendly, interactive, drag & drop reports
Normal reports
Table 3 : Multidimensional modeling Vs. entity-relationship modeling
6
III.7 Reporting
Reporting is the art of collecting data from various data sources and presenting it to users in a way
that is understandable and ready to be analyzed. Reports generally contain data tables, cross-tab reports,
visualization features etc. The goal of reporting is to enable users to see data so they can analyze it and
make it understandable through analysis.
Reporting can serve as a tool to collect and present data to be analyzed, empower users with the
knowledge to become experts in their area of business, have the underlying figures to back up actions
and decisions. [NET 9]
III.8 Dashboards
A dashboard is an information management tool that visually tracks, analysis and displays Key
Performance Indicators (KPI), to monitor a specific business process. They are customizable depending
on a company’s needs. It displays the data in form of tables, line charts, bar charts, gauges etc.
It is the most efficient way to track multiple data sources because it provides a central location for
businesses to monitor and analyze performance, it reduces analyzing time because it provides real time
monitoring. [NET 8]
III.9 Data mining
Data mining is the process of analyzing hidden patterns of data according to different perspectives
for categorization into useful information, which is collected and assembled in common areas, such as
data warehouses, for efficient analysis, data mining algorithms, facilitating business decision making and
other information requirements to ultimately cut costs and increase revenue. [NET 5]
We enumerate below the most known benefits of data mining.
• In finance and banking : Data mining is used to create accurate risk models for loans and mortgages.
They are also very helpful when detecting fraudulent transactions.
• In marketing : Data mining technics are used to improve conversions, increase customer satisfaction
Advertisement
and...