# Datamart construction and knowledge extraction using the decision tree application to medical data

Richard Kangiama

► To cite this version:

Richard Kangiama. Datamart construction and knowledge extraction using the decision tree application to medical data. ANNALES Universitatis Scientiarum Budapestinensis - Sectio Compitatoria, In press. hal-02148899

HAL Id: hal-02148899

https://hal.science/hal-02148899

Submitted on 19 Jun 2019

HAL is a multi-disciplinary open access
archive for the deposit and dissemination of sci-
entific research documents, whether they are pub-
lished or not. The documents may come from
teaching and research institutions in France or
abroad, or from public or private research centers.

L'archive ouverte pluridisciplinaire HAL, est
destinée au dépôt et à la diffusion de documents
scientifiques de niveau recherche, publiés ou non,
émanant des établissements d'enseignement et de
recherche français ou étrangers, des laboratoires
publics ou privés.

<figure><img src="image_1.png" /></figure>

Open licence - etalab
# Datamart construction and knowledge extraction using the decision tree application to medical data.

Richard KANGIAMA,

University of Kinshasa,

Faculty of Oil and Gas: Department of Basic Sciences,

Email: richard.kangiama@gmail.com

## summary

Our contributions concern the setting up of a DataMart at the end of conducting a study on the influence of prenatal factors that contributes to the birth of children we took the case of the maternity hospital saint Joseph (during the years 2011, 2012) based on data from the Prenatal Consultation and other vital factors as well as the birth conditions of children. We started by collecting the data in the different registers and constituting a data source in Excel, then we created our operational database in Acess, then build our mini data warehouse, based on the result of the data we used extraction and dataming software which is the SPAD to highlight the parameter that influences the weight of children at birth for example and other variables.

Keywords: DataMart, Dataming, Data Warehouse

## Introduction

Decision-making is a critical issue of concern to business managers. This decision-making involves modeling the various problems they encounter in management, hence the need for a model based on the decision tree. The mini-warehouse of data / datamart being a centralized and universal vision of all the information of the company, It is a structure which aims, contrary to the databases, to gather the data of the company for purposes analytics and to assist the manager in strategic decision-making. A strategic decision is an action taken by the company's decision-makers who aims to improve, quantitatively or qualitatively, the performance of the company. A knowledge extraction problem involves extracting knowledge from a data warehouse or other data source using Datamining techniques (decision tree, Bayesian networks, neural networks, etc.). applying them through software such as SPAD, XSLT or other data analysis.

## Methodology:

Datamart Construction / Mini Data Warehouse

We have modeled the operation database with the MERISE method which is considered as our data source that will feed our datamart. In pre-analysis, we have released, the following tables for the logic model of our operational database, the construction of the latter also passes through these different steps according Raph Kimball.
Step 1: Define the process to analyze

The procedure or function refers to the topic of our mini data warehouse, We determine the
business process of the hospital saint joseph concerns by our study for our case the
processue these are the results of reports on the deliveries of women to the hospital.

Step 2: Determine the level of granularity of the data

Choosing the grain means deciding exactly what a record of a fact table represents. For
example, the childbirth entity represents the facts relating to each delivery and becomes the
fact table of the star pattern of deliveries. Therefore, the grain of the birthing table is a
delivery done at the maternity ward. After choosing the grain of the fact table, we will begin
to identify the dimensions of the fact table. By way of illustration, the sheet and obstetric
history entities will serve as references to the birthing data and will become the dimension
tables of the star pattern of deliveries. We also add Time as the main dimension, because it
is always present in the star schema based on what all events happen in a specific period.

Step 3: choose the dimensions

The dimensions determining the context in which we will be able to ask questions about the
facts established in the fact table. A well-formed set of dimensional dimensions makes the
mini data warehouse understandable and simplifies its use.

We identify dimensions with sufficiency of detail, to describe things such as deliveries and
properties with correct granularity.

For example, anyone in the SHEET dimension is described by the attributes NF,
NOMPOSTNOM, AGE, CIVIL STATUS, NATIONALITY, ADDRESS, LEVELS; the ANTOBSTETRI
dimension is described by the following attributes: na, GRAVIDA, PARITE, ABORTION, DEATH,
the TIME dimension is described by the following attributes: TIME, DAYS, MONTHS AND
YEARS.

Step 4: Identify the metrics (facts): For our case the fact is DELIVERY, the metrics are the numerical data PROVENANCE (CPN), ETATSER, WEIGHT, AP GAR. Metrics are measurable and computable entities for statistical and numerical utilities.

Datamart: In summary we can synthesize our decision-making system as such. Since we are
building a datamart we want to stop at this stage, to explain, justified our case studies.

Table of Fact: The measurements are defined by the DELIVERY table and are as follows:
ORIGIN, STATE, WEIGHT and AP GAR.

Dimensions

Our wants to carry out analyzes according to various axes of observation.
Time axis:

Year, Month and Days;

Axis sheet:

Card Number, Name Post Name, Age, Marital Status, Nationality, Address and Education

Level;

Anti_obstreti axis:

Gravida, Parity, Abortion and Death;

## Results:

- Star schema of the warehouse under SQL server 2008

<figure><img src="image_2.png" /><figcaption>Figure 1: Datamart star schema.</figcaption></figure>

- Data mining module

To realize our data mining module with SPAD data analysis software, to facilitate the interpretation. We imported from an Excel file the result of an MDX query (SQL query on the Multidimensional Database) on our DataMart from there, we imported these data to SPAD to do the ACP, the complexity of these SPAD software is that it brings together a panoply of analysis algorithms and integrated data processing within it.

This table summarizes the synthetic data obtained after our MDX query on Datamart
<figure><img src="image_3.png" /><figcaption>Fig1. Chart Synthetic Data Representation</figcaption></figure>

Description of age groups by interval:

Age group 1: 19 to 21 years old, Age group 2: 21 to 23 years old, Age group 3: 23 to 25 years old, Age group 4: 25 to 27 years old, Age group 5: 27 to 29 years old, Age group 6: 29 to 31 years old, Age group 7: 31 to 33 years old, Age group 8: 33 to 35 years old, Age group 9: 35 to 37 years old, age group 10: 37 to over.

We will present the results of our analyzes carried out with the SPAD software on the delivery data. Table of Own Values and Contribution of the Axes.

ANALYSIS OF BINARY CORRESPONDENCE:

OWN VALUES

OVERVIEW OF CALCULATION PRECISION: TRACE BEFORE DIAGONALIZATION .. 0.0397

SUM OF OWN VALUES .... 0.0397

HISTOGRAM OF THE 4 FIRST OWN VALUES

+---------+---------+---------+-------+------------------------------------------+
| NUMERO | VALUE | PERCENT. | PERCENT. |
+---------+---------+---------+-------+
| CLEAN  | CUMULA |         |         |
+---------+---------+---------+-------+
| 1 | 0.0360 | 90.79 | 90.79 |
| 2 | 0.0035 | 8.73 | 99.52 |
| 3 | 0.0002 | 0.47 | 100.00 |
| 4 | 0.0000 | 0.00 | 100.00 |
+---------+---------+---------+-------+

COORDINATES, FREQUENCY CONTRIBUTIONS ON AXES 1 TO 4

ACTIVE FREQUENCIES

+---------------------------------+-----+------------------------------+-------------------------+
| FREQUENCIES | COORDINATES | CONTRIBUTIONS | COSINUS CARRES |
+---------------------------------+-----+------------------------------+-------------------------+
| IDEN - COURT WORDER P.REL DISTO | 1 2 3 4 0 | 1 2 3 4 0 | 1 2 3 4 0 |
+---------------------------------+-----+------------------------------+-------------------------+
| CPN - provenance CPN 2.90 0.26 | -0.47 0.19 | -0.06 0.00 0.00 | 18.0 31.1 48.0 0.0 0.0 | 0.85 0.14 0.01 0.00 0.00 |
| GEST - the gestation of the m | 0.07 0.01 | 0.05 -0.03 0.01 | -0.05 0.00 | 0.0 0.0 0.0 99.9 0.0 | 0.41 0.14 0.01 0.44 0.00 |
| PAR - the parity of the mother | 5.83 0.43 | -0.64 -0.12 0.01 | 0.00 0.00 | 66.6 25.1 2.4 0.0 0.0 | 0.96 0.03 0.00 0.00 0.00 |
| AVOR - the number of times av | 1.02 0.36 | -0.45 0.38 0.10 | 0.00 0.00 | 5.8 43.6 49.6 0.0 0.0 | 0.57 0.41 0.03 0.00 0.00 |
| POID - the weight of the child | 90.17 0.00 | 0.06 0.00 0.00 0.00 | 9.6 0.2 0.0 0.1 0.0 | 1.00 0.00 0.00 0.00 0.00 |
+---------------------------------+-----+------------------------------+-------------------------+

COORDINATES, CONTRIBUTIONS AND SQUARE COSINUS OF INDIVIDUALS

AXES 1 TO 4

+---------------------------------+-----+------------------------------+-------------------------+
| INDIVIDUALS | COORDINATES | CONTRIBUTIONS | COSINUS CARRES |
+---------------------------------+-----+------------------------------+-------------------------+
| P.REL DISTO IDENTIFIER | 1 2 3 4 0 | 1 2 3 4 0 | 1 2 3 4 0 |
+---------------------------------+-----+------------------------------+-------------------------+
| Tr1 9.53 0.03 | 0.16 0.08 0.01 | 0.00 0.00 | 6.8 16.1 10.8 0.0 | 0.81 0.18 0.01 0.00 0.00 |
| Tr2 9.94 0.02 | 0.05 0.12 -0.02 | 0.00 0.00 | 0.6 40.5 17.0 12.7 0.0 | 0.13 0.85 0.02 0.00 0.00 |
| Tr3 9.47 0.04 | 0.21 0.01 0.01 | 0.00 0.00 | 11.4 0.3 6.5 5.3 0.0 | 0.99 0.00 0.00 0.00 0.00 |
| Tr4 10.17 0.01 | -0.12 0.03 -0.01 | 0.00 0.00 | 3.8 3.4 3.2 6.9 0.0 | 0.92 0.08 0.00 0.00 0.00 |
| Tr5 9.31 0.02 | 0.13 -0.04 -0.01 | 0.00 0.00 | 4.6 4.2 8.7 6.7 0.0 | 0.91 0.08 0.01 0.00 0.00 |
<figure><img src="image_4.png" /></figure>

## C.2. Decision tree

<figure><img src="image_5.png" /><figcaption>Figure 3: Dendrogram obtained after analysis with SPAD
C3. Graphic</figcaption></figure>

<figure><img src="image_6.png" /><figcaption>Figure 4: Variable group graph and contribution</figcaption></figure>

## Discussions:

- Interpretation of results and discussion

Determination of axes

For the variables we take a threshold of 25%:

We can say that for the axis 1: The parity of the mother contributed to 66% to the creation of the axis 1
it is of coordinates negative, for axis 2: Parity of the mother to contributed of 25% at the creation of
the axis 2 it is of negative coordinates, which leads to say that the number of times that the mother to
give birth contributed of 43,6% to the creation of the axis 2 it is of coordinates positive, pre-natal consultation contributed 31.1% to the creation of axis 2 it is negative coordinates.

For axis 3: The pre-natal consultation contributed 48% to the creation of axis 3 it is positive coordinates, the number of times that the mother to give birth contributed 49.6% to the creation of the axis 3 it is positive coordinates.

For axis 4: The gestite contributed 99% to the creation of axis 3 it is negative coordinates. for individuals (10%)

For axis 1: The age group 3 contributed 11.4% to the creation of the axis1 it is coordinates positive and The age group 6 contributed 59.4% to the creation of the axis1 it has negative coordinates.

For axis 2: The age group 1 contributed 16, 1% to the creation of axis 2 it is positive coordinates and the age group 2 contributed 40.5% to the creation of the axis2 it is of positive coordinates in continuation, the age group 10 to contributed of 18,4% to the creation of the axis2 it is of coordinates negative

For axis 3: The age group 1 contributed 10.8% to the creation of the axis 3 it is positive coordinates, the age group 2 contributed 17% to the creation of the axis 3 it is negative coordinates, the age group 7 contributed 27% to the creation of the axis 3 it is coordinates positive and the age group 10 contributed 20% to the creation of the axis 3 it has negative coordinates.

For axis 4: The age group 2 contributed 12.7% to the creation of axis 4 it is negative coordinates and the age group 9 contributed 55.6% to the creation of Axis 4 has positive coordinates.

Interpretation:

The age range from 29 to 31 is associated with the parity of the mother or we can still say that the parity better explains this age group and the age range from 29 to 31 is the age group women have given birth a lot, the age group 10 is associated with the NPC, we can say that the majority of this woman is the NPC.

In this slice 2, age that moms have aborted a lot.

We can still say in the age range of 21 to 23 years; young girls are often urged to do abortions.

In this age group 7, the majority of mothers have not followed NPC. Most of these women think they are already adults and neglect NPC.

In this slice 9, age is the age group that the majority of women have already given birth more than once.

Conclusion

We have arrived at the end of our contribution, which focused on the extraction of knowledge from a DataMart using the decision tree, application to medical data. In our work, we first talked about the decision-making system that is presented as the set of processes that collects, integrates, models and presents data. We also talked about data warehouses, which are the heart of the decision-making system that plays a role in the repository for the company since it allows to federate data often scattered in different sources of data. To finish with the concept of dataminingwhich makes it possible to do in-depth research on the data of the warehouse, with a tree of decision to make the extraction of knowledge. We realized the datamart with SQL server 2008 with a model of the decision tree to allow
us to make a decision on our data. From what precedes, we are convinced that all the concerns answer
the problem of our work. Our contribution was to create a DataMart on childbirth and from this, we
were able to build a decision tree that we interpreted at the end.

Bibliographical references

1. R. Kimbal, L. Reeves and W. Thornthwaite, Data Warehouse: Guide to Conduct Project, Eyrolles, 4th edition 2008.

2. B. Burquier, Business Intelligence with SQL SERVER 2008: Implementation of a decisional project, Dumond, 2008.

3. ADIBA .M, Data warehouses and data mining, Paris 2002;

4. Bertrand Burquier, Business intelligence with 2008, Implementation of a decisional project, Dunod, 2009;

5. DANIEL T. LAROSE, From Data to Knowledge an introduction to Datamining, Vuibert, 2005;

6. GUIJARRO Vincent, The Decision Tree ID3 algorithm, Lille, 2006;

7. KIMBALL .R and m. Ross, Data Warehouses, Practical Guide to Dimensional Modeling, Vuibert, Paris, 2003;

8. N.KASORO, B. KASEREKA, Data Warehouses and Multidimensional Analysis of Timber Industry Exploitation in the Democratic Republic of Congo, Annales Faculty of Science, Vol 1, 2013.

9. Cristiano Xaviera, Fernando Moreiraa, *, Agile ETL, ERIS 2013 - Conference on ENTERprise Information Systems / PRojMAN 2013 - International Conference on Project Management / HCIST 2013 - International Conference on Health and Social Care Information Systems and Technologies, UniversidadePortucalense, Rua Dr. Bernardino de Almeida, 541, Porto, Portugal, Portugal, ScienceDirect

10. KasoroMulenda Nathanael, Alain KuyundaMayu and NdoziMansangu R, dig complex data and tuning the data warehouse a comparative study between the classical classification and symbolic dynamic classification, the ISS-KIN notebooks, interdisciplinary review, vol.9, July 2014

11. Alain KUYUNSA MAYU *, Nathanael KASORO Mulenda **, RostinMatendo **, Modeling of Fuzzy datawerahouse, applied engineering, 2017

12. Calista M. Harbaugha, Jennifer N. Cooperb, Administrative databases, Volume 27, Issue 6, December 2018, Pages 353-360,
https://www.sciencedirect.com/science/article/pii/S1055858618300891