Projet 4 : UBO DataBase

Cuni Frédéric-A14
Projet 4 : UBO DataBase
1
Genèse du projet
1.1 Définition
Pragma : application en ligne où les potentiels clients acceptent une offre proposée par
Alter Domus (services pour les sociétés ou les fonds),
CAC : le Client Acceptance est un processus selon lequel tous les nouveaux clients sont
identifiés jusqu’à l’ultime bénéficiaire et formellement acceptés par le Comité d’Acceptation
Client avant le début de la prestation de services.
Phytagoras : base de données mondiale permettant de connaître les personnes à risque ou à
risque potentiel.
AD Connect : portail de stockage de documents d’Alter Domus (GED) : gestion électronique
des documents
MDM : Master Data est une base de données de Alter Domus qui récence les clients, les
employés et les personnes rattachés aux clients ou investisseuses,
eFront : est une DB qui permet de gérer les investisseurs associés à des fonds, et de gérer les
fonds eux-mêmes.
1.2 Explications
Alter Domus propose des offres de gestion de fond, comme évoqué dans la présentation de
l’entreprise. Cela signifie qu’un fond peut être levé et par conséquent que des investisseurs
participeront à ce fond, qu’ils soient une personne physique ou morale (entreprises).
Dans le cas d’une personne morale, il existe un ou plusieurs UBO rattachés. Un UBO
(Ultimate Beneficial Owner) est la personne physique qui est le propriétaire le plus haut des
bénéfices. C’est lui qui touche l’argent en bout de chaîne.
Université de Technologie de Compiègne - Département Génie Informatique
1/13
Cuni Frédéric-A14
Il est primordiale de connaître ces UBO car il se peut qu’ils soient des terroristes ou
participent à du blanchiment d’argent. Dans ce cas Alter Domus doit les dénoncer aux
autorités compétentes et donc doivent disposer d’un outil permettant d’investiguer ces UBO et
de les référencer (d’où le nom de UBO database).
Actuellement, lorsqu’une offre est acceptée dans Pragma, elle est envoyée au CAC qui se
charge de l’acceptation client.
Le CAC fait un examen manuel du client dans Pythagoras, qui leur fournit un rapport en
retour qui sera injecté dans AD Connect.
Le client, une fois accepté, est créé dans la base de données MDM.
Lorsqu’un nouvel investisseur est associé à un fond, les employés envoient les informations le
concernant au business qui le rentre dans un fichier Excel.
Ce fichier est ensuite injecté dans Pythagoras une fois par mois par l’équipe Delivery.
Les UBO sont donc répertoriés dans un fichier Excel ce qui pose des problèmes de sécurité,
de maintenance et de lisibilité du fichier.
2
Spécifications
Lorsque j’ai commencé ce projet, les spécifications de ce dernier n’étaient pas réalisées. C’est
pourquoi de nombreux meeting furent organisés avec l’équipe business pour comprendre les
attentes et le travail à réaliser.
D’où le nom du projet, Alter Domus souhaitait avoir une base de données UBO qui référencie
les différents fonds gérés par la société, tous les investisseurs ainsi que les personnes
physiques ou morales au-dessus de ceux-ci, avec, en haut de la chaine, les UBO. La
conception de la base de données étant la première partie du travail.
Dans un second temps, il m’a été demandé de charger les UBO existants du fichier Excel dans
la base de données à l’aide d’un ETL, et de charger des données de la base de données MDM
vers UBO toujours à l’aide d’un ETL.
3
Réalisation
Université de Technologie de Compiègne - Département Génie Informatique
2/13
Cuni Frédéric-A14
3.1 Base de données UBO
La base de données UBO comprend donc deux types de personnes : les entités, elles peuvent
être des fonds, des entités réglementées,… ; et les individus.
Il existera des relations entre ces personnes par exemple investisseur, promoteur, UBO,
directeur, … et un statut qui définit cette relation (active, inactive,…).
Des documents seront également présents au niveau d’une personne (carte d’identité,
passeport,…) et un statut qui définit les documents référencés (conforme, non conforme, pas
reçu,…).
Dans un premier temps, une base de données indépendante UBO fut créée en respectant les
spécialités ci-dessus. Cependant, un problème est survenu : la duplication des données. En
effet, la base de données MDM (Master Data) recense les clients, les employés et les
personnes rattachés aux clients ou investisseurs, ce qui signifie que de nombreuses données
seront dupliquées entre Master Data et UBO si celle-ci est une base de données indépendante.
Il a donc été décidé de créer un schéma SQL UBO dans Master Data pour éviter de dupliquer
des informations, ce qui signifie que les nouveaux investisseurs par exemple seront créés dans
Master Data et dans UBO, lié par un ID (identifiant), et les relations seront donc faites dans le
schéma SQL UBO.
On peut voir dans la figure du schéma SQL UBO ci-dessous, qu’un Record peut être de type
Individu ou Entité, qui sont tous deux liés à la table Country en ce qui concerne le lieu
d’activité, le lieu de naissance,…
Une table RiskRating est liée à cette table pour spécifier le risque lié au Record en question.
La table Record est liée à la table PERSONS de Master Date, car comme expliqué
précédemment, une personne peut être physique (individus) ou morale (entité). C’est donc
une table de lien avec Master Data.
Les clients d’Alter Domus ne sont que des entités, c’est pourquoi la table LegalEntityInfo est
liée à la table CLIENTS de Master Data. Cette donnée peut être nulle lorsqu’une nouvelle
entité est ajoutée à un fond mais que celle-ci n’est pas client chez Alter Domus, juste
investisseur.
On peut voir la table Relationships, qui correspond aux relations entre les Record, c’est
pourquoi il y a un double lien entre la table Relationships et Record, avec une table Status
Université de Technologie de Compiègne - Département Génie Informatique
3/13
Cuni Frédéric-A14
pour spécifier le statut de la relation, et une table RelationType pour spécifier le type de
relation.
Un Record peut avoir plusieurs documents, c’est pourquoi une table intermédiaire
RecordDocuments est créée entre la table Record et la table Documents, pour eviter une
relation n -> n entre ces tables. Une table DocumentType est liée à la table Document pour
spécifier le type de document, ainsi que le statut avec la table DocumentStatus.
Une table IndividualFatcaInfo est également ajoutée pour pouvoir envoyer les informations
nécessaires au Fisc américain comme expliqué dans la partie projet web.
FIGURE 4 5 : Schéma SQL UBO dans Master Data
3.2 Migrations des données existantes
Université de Technologie de Compiègne - Département Génie Informatique
4/13
Cuni Frédéric-A14
3.2.1
Présentation du fichier Excel actuel
Les données existantes sont dans un fichier Excel et il faut les intégrer dans le schéma SQL
avec les relations appropriées.
Pour des raisons de confidentialité, je ne peux exposer le fichier Excel.
La syntaxe du fichier est visible sur la figure ci-dessous.
Lorsque la colonne « Investor ID » n’est pas nulle cela signifie que l’entité ou l’individu en
question à un lien directement avec le fond.
Au contraire, lorsque cette colonne est nulle mais que la colonne « Link ID » ne l’est pas, cela
signifie que l’entité ou l’individu en question à un lien avec l’identifiant de cette colonne.
Ces liens sont définis par la colonne « Partner Role ».
Dans les exemples créés de ma propre initiative sur la figure, on peut constater :
-
une entité factice appelé « Fred Corporation » qui est investisseuse dans le fond
« Fond Test » avec un « Investor ID » de 10,
-
un individu « CUNI Frédéric » qui est UBO de l’entité « Fred Corporation » comme
on peut le voir dans la colonne « Link ID » qui vaut 10,
-
un autre individu « Durant Claude » qui est investisseur direct du fond « Fond Test »
avec un « Investor ID » de 2.
FIGURE 46 : Exemple du fichier Excel des UBO ac tuel
3.2.2
ETL de migration des données du fichier Excel
Pour charger ces données dans le schéma SQL UBO, un ETL a été réalisé. Comme pour le
projet RiskMetrix Conversion, un SSIS fut utilisé, à la seule différence que pour ce projet, ce
SSIS n’est à lancer qu’une seule fois.
Le SSIS comporte les étapes suivantes :
Université de Technologie de Compiègne - Département Génie Informatique
5/13
Cuni Frédéric-A14
FIGURE 4 7 : SSIS de migration de données fichier Excel / base de données
La première étape est une requête SQL, comme le montre la figure ci-dessous, qui consiste à
créer une table temporaire des investisseurs, c’est-à-dire ceux dont la colonne « Investor ID »
n’est pas nulle dans le fichier Excel.
FIGURE 4 8 : Requête SQL de création d’une table temporaire
Université de Technologie de Compiègne - Département Génie Informatique
6/13
Cuni Frédéric-A14
Ensuite un flux de données est créé pour charger dans le schéma SQL UBO tous les fonds
présents dans le fichier donc la colonne « Name of Fund ». Ces fonds existent déjà dans
Master Data dans la table CLIENTS, donc en premier lieu il faut faire une recherche des
fonds dans cette table et les ajouter dans UBO. Si ils n’existent pas dans la table CLIENTS,
on transmet une erreur dans la base de données ETLTechnical, basée sur le même principe
que le projet RiskMetrix Conversion.
L’étape suivante est de créer les investisseurs dans UBO, donc tous ceux présents dans la
table temporaire. Une recherche est faite dans Master Data pour savoir si ils existent ou non.
Dans le cas négatif, on le crée d’abord dans Master Data à l’aide d’une « stored procedure »,
donc une requête SQL avec paramètre qui se charge de créer automatiquement la personne.
On aura donc deux « stored procedure », une correspondante à la création d’un individu,
figure 49, et l’autre correspondante à la création d’une entité, figure 50.
FIGURE 4 9 : S tored procedure de création d’un individu dans Master Data
Université de Technologie de Compiègne - Département Génie Informatique
7/13
Cuni Frédéric-A14
FIGURE 5 0 : S tored p rocedure de création d’une entité dans Master Data
Une fois la personne créée, il faut la générée dans UBO et donc créer un Record. Ceci se fait
de nouveau, à l’aide d’une « stored procedure », figure 51.
FIGURE 5 1 : S tored procedure de création d’une e ntité dans UBO
Université de Technologie de Compiègne - Département Génie Informatique
8/13
Cuni Frédéric-A14
Dès lors que les fonds et les investisseurs sont chargés, on peut alors créer leur relation qui est
définie dans la colonne « Partner Role » du fichier.
Ensuite viennent les personnes qui sont en relation directe avec l’investisseur et non le fond,
par exemple CUNI Frédéric qui est UBO de Fred Corporation dans l’exemple ci-dessus.
Le principe est le même que le chargement des fonds et investisseurs, on utilise la « stored
procedure » appropriée et on crée la relation entre les personnes en fonction de la colonne
« Partner Role ».
Une fois toutes les personnes du fichier et leur relation chargées, la dernière étape consiste à
la suppression de la table temporaire à l’aide d’une requête SQL.
3.2.3
ETL de migration de données Master Data -> Schéma SQL UBO
Dans la base de données Master Data, il existe une table CLIENT_DIRECTORS,
CLIENT_SHAREHOLDERS et CLIENT_SPECIAL_MANDATES. Dans chacune de ces
tables est présent un identifiant client et un identifiant personne, ce qui signifie, pour la table
CLIENT_DIRECTORS par exemple, que la personne est directeur de la société désignée par
l’identifiant client. Rappelons que les clients d’Alter Domus ne sont que des sociétés.
Exemple :
Table CLIENT_DIRECTORS
ID_CLIENT
ID_PERSON
10
11
L’ID_CLIENT 10 désigne la société « Fred Corporation » et l’ID_PERSON désigne
l’individu CUNI Frédéric.
Cet exemple signifie que Cuni Frédéric est Directeur de la société Fred Corporation. Cela
représente donc la relation entre ces deux personnes.
Le but étant de charger dans UBO les personnes se trouvant dans ces trois tables dans UBO.
Ces personnes, que ce soient des clients ou autres, existent déjà dans la base de données
Master Data. Il faut donc juste faire un lien entre UBO et Master Data pour les personnes
concernées et ajouter les relations comme explicité ci-dessus.
Université de Technologie de Compiègne - Département Génie Informatique
9/13
Cuni Frédéric-A14
Un SSIS est donc réalisé pour effectuer ce chargement, figure 52.
Comme tout SSIS la première étape consiste à écrire dans la table des logs ETLTechnical que
le SSIS est lancé et la dernière étape écrit que le SSIS s’est terminée avec succès.
Chargement de la table
CLIENT_DIRECTORS
Chargement de la table
CLIENT_SPECIAL_
MANDATES
Chargement de la table
CLIENT_SHAREHOLDERS
FIGURE 5 2 : SSIS exportations de données Master Data -> UBO
Ensuite la table CLIENTS_DIRECTORS est chargée dans le schéma SQL UBO. Pour se
faire, il suffit d’utiliser la stored procedure, évoquée plus haut, de la création d’un Record
dans UBO. Une fois le Record créé il suffit de charger la personne soit dans individus, soit
dans entité en fonction et de faire le mappage avec Master Data.
La relation entre la personne et le client est directeur, comme explicité dans l’exemple. La
seconde étape consiste alors à créer la relation précédente entre les personnes.
Ce principe est similaire pour les deux autres tables.
3.3 Phase de test
Pour tester le chargement du fichier Excel dans UBO, j’ai repris l’exemple évoqué lors de la
présentation du fichier Excel. Nous avions :
Université de Technologie de Compiègne - Département Génie Informatique
10/13
Cuni Frédéric-A14
-
une entité factice appelée « Fred Corporation » qui est investisseuse dans le fond
« Fond Test »,
-
un individu « CUNI Frédéric » qui est UBO de l’entité « Fred Corporation »,
-
un autre individu « Durant Claude » qui est investisseur direct du fond « Fond Test ».
Le chargement du fichier par le SSIS a été effectué. Pour vérifier si les données se sont
chargées correctement, il suffit d’ouvrir la table Relationship et de regarder les relations
présentes, nous obtenons le tableau ci-dessous :
FIGURE 5 3 : Table Relationship de l’exemple
Comme la table Relationship ne contient pas que les trois lignes d’exemples, j’ai effectué une
requête récursive pour retrouver tous les liens en partant d’un fond à savoir le « Fond Test »
pour l’exemple. Ce fond a un identifiant égal à 2, la requête va donc chercher toutes les
relations en rapport avec cet identifiant. La requête est la suivante :
FIGURE 5 4 : Requête récursive pour retrouver tous les liens re latif s à un fond
Le « With » de la requête va regrouper tous les liens relatifs à l’identifiant 2 donc relatif au
fond. Ensuite le « Select » permet d’afficher les données voulues et permet de faire le
mappage avec les différentes tables de Master Data et du schéma SQL UBO. On obtient le
résultat ci-dessous :
Université de Technologie de Compiègne - Département Génie Informatique
11/13
Cuni Frédéric-A14
FIGURE 5 5 : Résultat de la requête récursive
On peut donc voir que les résultats obtenus sont bien ceux énoncés précédemment. Pour plus
de clarté, on peut regarder le schéma ci-dessous.
FIGURE 5 6 : Schéma de relation de la requête récursive
Le test du chargement des trois tables de Master Data vers UBO s’est réalisé de la même
manière. Le résultat d’un test de la table CLIENT_SHAREHOLDER est présent dans le
schéma ci-dessus, car « Fond Test » est un client d’Alter Domus et Fred Corporation est une
entité donc une personne. On peut voir que la relation s’est bien réalisée.
3.4 Migration du projet
La base de données Master Data sera mise à jour dans l’environnement UAT en y ajoutant le
schéma SQL UBO comme dans l’environnement de développement. Ceci se fera par le biais
de stored procedure qui créeront automatiquement la base de données telle qu’elle est dans
l’environnement de développement à l’aide d’une option de génération de script présent dans
l’outil Microsoft. Les scripts seront donc lancés en UAT par l’équipe delivery.
Une fois la base de données générée en UAT et les tests SSIS validés par mon manager dans
l’environnement de développement, les deux SSIS peuvent être envoyés à l’équipe delivery
qui les déploiera sur le serveur UAT et feront les dernières vérifications pour ensuite les
migrer sur le serveur de production.
Université de Technologie de Compiègne - Département Génie Informatique
12/13
Cuni Frédéric-A14
3.5 Conclusion
Ce projet m’a permis, en premier lieu de connaître plus en détail le monde du business et le
fonctionnement de celui-ci au sein d’Alter Domus.
Il m’a permis de me perfectionner en SSIS et en base de données (créations et requêtes).
Ce projet a permis de créer la base de données et de charger des informations existantes
dedans. Ces données sont maintenant structurées et faciles à comprendre. Elles pourront être
envoyées fréquemment à la base de données Pythagoras pour vérifier si les personnes
présentent sont à risque ou non, et être envoyées au fisc américain pour vérifier si les
personnes américaines déclarent leurs revenus.
Je n’ai pas eu le plaisir de réaliser les écrans de l’application, car les spécifications les
concernant n’étaient toujours pas approuvées lorsque je suis parti de l’entreprise.
Université de Technologie de Compiègne - Département Génie Informatique
13/13