conservatoire national des arts et métiers centre régional de dijon

CONSERVATOIRE NATIONAL DES ARTS ET MÉTIERS

CENTRE RÉGIONAL DE DIJON

Conception d’un entrepôt de données corrélant les effectifs en apprentissage et le suivi financier des centre de formation

28 juin 2010

Soutenance mémoire ingénieur CNAM en informatique par

Joachim PELLICIOLI

2

PLAN Contexte du projet Rappel des concepts L’entrepôt de données pour les Conseils

régionaux ETL Le data mart effectifs Le data mart financier

Bilan et perspectives Point sur le déploiement Evolutions

Période d’échange

3

CONTEXTE DU PROJET

4

CONTEXTE DU PROJET

Gestion de la formation et

de l’apprentissag

e RôleGestion des CFA Gestions des effectifs Gestion financière

[…]

5

CONTEXTE DU PROJET Analyse du besoin :

6

CONTEXTE DU PROJET Solution proposée :

Création d’un entrepôt de données pour le service de la formation et de l’apprentissage.

7

RAPPEL DES CONCEPTS

8

RAPPEL DES CONCEPTS

Entrepôt de données (Bill Inmon)

Orientées sujet Intégrées Non volatiles Historisées Résumées Processus d’aide à la décision

9

RAPPEL DES CONCEPTS

10

RAPPEL DES CONCEPTS Le modèle en étoile

11

RAPPEL DES CONCEPTS Modèle en flocon de neige

12

RAPPEL DES CONCEPTS Modèle en constellation

13

RAPPEL DES CONCEPTS

Caractéristiques OLAP OLTP

Orientation Multidimensionnelle Ligne

Utilisateur Base décisionnelle Base de production

Nombre d’utilisateurs Réduit Elevé

Accès Lecture Lecture et écriture

Type d’opération Analyse Mise à jour

Granularité d’analyse Globale Elémentaire

Quantité d’information échangée Importante Faible

Quantité d’information stockée Importante Faible

Longévité des données Historique En cours

14

Scoping

RAPPEL DES CONCEPTS Drill Rotate Slicing Scoping

RotateSlicing

15

L’ENTREPÔT DE DONNÉES POUR LES CONSEILS RÉGIONAUX

- - - - Analyse - - - -

16

ENTREPÔT DE DONNÉESANALYSE

Mars 2009Etude

Avril 2009

Mai 2009

ETLJuin 2009

Juillet 2009

EffectifsAoût 2009

Septembre 2009

Financier

Octobre 2009

Novembre 2009

FinalisationDécembre 2009

Janvier 2010

Février 2010

17

ENTREPÔT DE DONNÉES ANALYSE

ETL Entrepôt de données

Restitution des données

18

ENTREPÔT DE DONNÉES ANALYSE

ETL Entrepôt de données

Restitution des données

Programme Ymag SQL Server BO XI

WebIntelligence

19

ENTREPÔT DE DONNÉES ANALYSE

ETL Entrepôt de données

Restitution des données

Programme Ymag SQL Server BO XI

WebIntelligence

BO Designer

20

ENTREPÔT DE DONNÉES ANALYSE

Entrepôt de données

21

ENTREPÔT DE DONNÉES ANALYSE

Entrepôt de données

Effectifs FinancierDépense théorique

22

ENTREPÔT DE DONNÉES ANALYSE

Entrepôt de données

Effectifs FinancierDépense théorique

23

ENTREPÔT DE DONNÉES ANALYSE

Méthodologie de conception d’un data mart

Gestion des dimensions  : axes d’analyses. Gestion des faits  : portefeuille

d’indicateurs. Représentation graphique : modélisation.

24

ENTREPÔT DE DONNÉES ANALYSE

Axe d’analyse : Nom de l’axe d’analyse

Description Description détaillée de l’axe d’analyse. Nous le replaçons dans le

contexte et nous précisons le rôle de celui-ci.

Comptage Estimation ou nombre réel d’occurrences chargées dans la dimension.

Attributs

Nom de l’attribut 1 Description  : Description de l’attribut, explication métier.

Source de données : Définition de l’emplacement d’origine de cette

information.

Type de données  : Type de données de l’attribut (entier, chaine de

caractères, …).

Règle de calcul  : Règle de calcul pour créer l’attribut de la

dimension (condition, concaténation, …).

Contrainte  : Contrainte imposée au chargement de l’attribut

dans la dimension.

25

ENTREPÔT DE DONNÉES ANALYSE

Indicateur : Nom de l’indicateur

Description Nous décrivons précisément à quoi correspond notre indicateur. Nous donnons

également son contexte et la notion qu’il véhicule, l’échelle de grandeur

utilisée….

Règles de calcul Dans cette partie nous détaillons la formule de calcul qui a permis d’obtenir

notre indicateur (agrégation, prorata, ….).

Type de données Type de données de l’indicateur. En règle générale un fait est un élément

numérique. Nous pourrons préciser si nous travaillons en entier, en réel et avec

quelle précision.

Fonction d’agrégation Fonction utilisée pour restituer notre indicateur (rappel : un indicateur est un

fait additif, nous pourrons par exemple l’indiquer par la commande SQL

« SUM »).

Unité de mesure Quelle est l’unité de mesure de notre indicateur (métrique, monétaire, nombre,

….).

Source de données Indique la provenance de l’information source qui nous a permis de créer

l’indicateur du data warehouse.

Contraintes Contrainte imposée lors du chargement de l’indicateur dans la table des faits.

26

ENTREPÔT DE DONNÉES ANALYSE

Etapes

- Sélection d’une activité à analyser.

- Collecte des informations de dimensions et création des axes d’analyse.

- Recherche des hiérarchies et formalisation.

- Création du portefeuille d’indicateurs.

- Création d’un modèle en étoile mettant en relation les dimensions avec les faits.

- Maquettage (rapports et tableaux de bord)

- Présentation aux clients pour validation.

27

L’ENTREPÔT DE DONNÉES POUR LES CONSEILS RÉGIONAUX

- - - - ETL - - - -

28

ENTREPÔT DE DONNÉESETL Problématique

ETL à déployer dans X Conseils régionaux Intégration de données SQL Serveur Intégration de données autres

Traitement automatique Suivi des traitements

Modification de la structure de l’entrepôt de données

29

ENTREPÔT DE DONNÉESETL

30

ENTREPÔT DE DONNÉESETL

31

L’ENTREPÔT DE DONNÉES POUR LES CONSEILS RÉGIONAUX

- - - - Effectifs - - - -

32

ENTREPÔT DE DONNÉESEFFECTIFS Table des faits sans fait

33

ENTREPÔT DE DONNÉESEFFECTIFS

34

ENTREPÔT DE DONNÉESEFFECTIFS Exemple de restitution

35

ENTREPÔT DE DONNÉESEFFECTIFS Exemples de questions

Obtenir les noms des dix CFA ayant le plus gros pourcentage des effectifs féminins dans les métiers du bois (technicien constructeur bois, charpentier, scieur, …).

Obtenir la répartition des apprentis handicapés par département.

Obtenir la liste des diplômes qui ont subi le plus fort taux de croissance durant trois années consécutives.

36

L’ENTREPÔT DE DONNÉES POUR LES CONSEILS RÉGIONAUX

- - - - Financier - - - -

37

ENTREPÔT DE DONNÉES ANALYSE

Entrepôt de données

Effectifs Financier

Comptes généraux

Frais de personnel

Taxe d’apprentissag

eDépense théorique

38

ENTREPÔT DE DONNÉESFINANCIER

Information à mettre en valeur

Contrôle de l’évolution des budgets Données agrégées pour analyse Recherche d’anomalies …

39

ENTREPÔT DE DONNÉESFINANCIER

Mise en place

Sur le principe évoqué en analyse Recherche des dimensions et hiérarchies Recherche des indicateurs Création des data marts et maquettes …

+ expérience du data mart effectifs

40

ENTREPÔT DE DONNÉESFINANCIER Problématique

Toutes les Régions ne travaillent pas avec la même finesse des données.

Solution Intégration des données au plus bas niveau

Données détaillées pour certaines Régions Données automatiquement agrégées pour les autres.

41

ENTREPÔT DE DONNÉESFINANCIER

42

ENTREPÔT DE DONNÉESFINANCIER

Indicateurs

Comptes généraux : débit, crédit, budget, …. Frais de personnel : salaires, charges, ETP, … Taxe d’apprentissage : réalisé, budget Dépense théorique : heures, dépenses,

subventions, ….

43

ENTREPÔT DE DONNÉESFINANCIER

44

ENTREPÔT DE DONNÉESFINANCIER

45

ENTREPÔT DE DONNÉESFINANCIER

46

ENTREPÔT DE DONNÉESFINANCIER

47

ENTREPÔT DE DONNÉESFINANCIER

48

BILAN ET PERSPECTIVES

49

BILAN ET PERSPECTIVES Points sur les évolutions :

Demande d’évolution / amélioration 24 demandes 15 acceptées 5 en projets 4 refusées

Ajouts : 1 nouvel indicateur 2 nouvelles dimensions 7 nouveaux filtres

50

IMPLANTATION FIN 2009

Effectifs : 20 101 Centres : 67 Budget : 89 000 000 €

51

IMPLANTATION MI 2010

Effectifs : 77 572 Centres : 191 Budget : 271 000 000 €

52

IMPLANTATION FIN 2010

Effectifs : 229 193 Centres : 649 Budget : 958 000 000 €

53

PERSPECTIVES Amélioration de l’existant

Ajout d’indicateurs, de dimensions, de filtres. Ajout de nouveaux data marts

Enquête résultat aux examens …

Ouverture Ajout d’un data mart sur la gestion des primes

aux employeurs (WinCRPrimes).

54

PÉRIODE D’ÉCHANGE