Rencontre des utilisateurs SAS de la région de Montréal 27 septembre 2005 Bureau de planification...

Rencontre des utilisateurs SAS de la région de Montréal27 septembre 2005

Bureau de planification et d’études institutionnelles

Implantation d’un entrepôt de données à l’Université Laval

Commentaires sur l’expérience vécue à ce jour

Richard Massé

www.ulaval.ca

Mandat du BPEI (extrait)

• Effectue les études nécessaires à la gestion de l’information institutionnelle.

• Toute activité ad hoc d’étude, de recherche, d’analyse-conseil, de coordination, de soutien, de supervision, d’évaluation ou de gestion spécifiquement confiée par le vice-recteur exécutif

www.ulaval.ca

Res. HumainesRechercheFinances

ImmeublesÉtudes

Fondation

G10CREPUQ

MEQSTATCAN

ACPAU

Direction

BPEI

VRE

VRR

VRAF

VRDRI

Carrefour de l’information de gestion

VRRH

www.ulaval.ca

Répartition de la charge de travail

• Recherche de l’information dans les systèmes opérationnels 50%

• Production des rapports 40%

• Analyse des données 10%

En février 2004 :

www.ulaval.ca

Répartition de la charge de travail

• Recherche de l’information dans les systèmes opérationnels 10%

• Production des rapports 10%

• Analyse des données 80%

En février 200? (espéré):

www.ulaval.ca

Comment en arriver à ce résultat ?

Mettre en place un entrepôt de données !

Mais… C’est quoi au juste un entrepôt de données ???

www.ulaval.ca

Définition d’un entrepôt de données

A data warehouse is a subject oriented, integrated, non-volatile, and time variant collection of data in support of management’s decisions.(W.H. Inmon, Building the Data Warehouse)

Un entrepôt de données est un mécanisme permettant de remplacer le processus actuel de gestion des données par un mécanisme de gestion de la connaissance organisationnelle.(L’équipe du BPEI)

BPEI

Infrastructure pour exploiter l’information

Entrepôt

Cubes OLAPOnline analytical processing

Tableaux de bordRapports Analyses

Forages de données

Bases de données opérationnelles

Données externes

ExtractionsVues

www.ulaval.ca

Les acquis…

• Nos bases de données comportent (pour

le moment) peu de redondance au niveau des tables nécessaires à la production des rapports destinés à la direction de l’institution.

• Notre structure Oracle est relativement bien normalisée et documentée.

• Modèle « large » mais peu « profond ».

www.ulaval.ca

Ce qu’il manque…

• Un système de documentation des indicateurs.

• Une structure nous permettant de conserver une image historique des données.

• Un mécanisme de chargement de l’entrepôt.

• Un mécanisme de diffusion des rapports.

• Un environnement d’analyse des résultats.

• Etc.

www.ulaval.ca

Plan de match – Contexte organisationnel

• Que nous ne pouvons pas implanter toutes les composantes informatiques en même temps.

• Qu’il faut utiliser au maximum les ressources déjà en place. (Service informatique, Service des communications, etc.)

Le BPEI est composé de 4 personnes. Le directeur, 2 agents de recherche et de planification ainsi qu’un informaticien (moi). Cela implique :

www.ulaval.ca

Plan de match – Notre entente…

• Je m’occupe de la structuration et du chargement des données.

• Jacques et Luc utilisent ces données et produisent eux-mêmes les analyses nécessaires.

Dans ce contexte de ressources limitées, nous avons dû nous entendre relativement à la répartition des tâches :

Vue d’ensemble

FichierExcel

FichierTexte

FichierSAS

Tables des systèmes opérationnelsInstitutionnels (SIGO, PeopleSoft, Maximo, etc.)

Serveur ETL Studio Entrepôt de données du BPEI(en format Oracle)

Extraction des systèmes opérationnels

Chargement de l’entrepôt de données

Marc Boivin Louis-René Rheault Claude Aussant

Extraction dans les différents secteurs

G10 CREPUQ Ministère de l’éducation

Extractions produites par nos collaborateurs

Site Web du BPEI(Jahia)

SAS Excel

Production d’analyses

Direction de l’Université Laval

Analyses pour lahaute direction

Analysesinstitutionnelles

Enterprise Guide

Chargement de l’entrepôt

Système GII - Module de définition des indicateurs

institutionnels

Jacques Turcotte

Luc Simon

Définition des indicateurs

Rapport décrivant les

indicateurs GII

UtilisateursRapports BPEI

Demandes de validation des définitions

Modélisation de l’entrepôt

de données du BPEI

Non, rétroaction

Définitions approuvées ?

Commentaires des utilisateurs

Richard Massé

Est-ce que l’information modélisée peut-être conservée

dans GII ?

Système GII - Entrepôt générique

Entrepôt de données du BPEI(en format Oracle)

Chargement de l’entrepôt via ETL STUDIO

Chargement via GII

Oui

FichierExcel

FichierTexte

FichierSAS

Marc Boivin Louis-René Rheault Claude Aussant

G10 CREPUQ Ministère de l’éducation

Information à intégrer

Configuration du chargement

Non

Système GII - Module de définition des indicateurs

institutionnels

Jacques Turcotte

Luc Simon

Définition des indicateurs

Rapport décrivant les

indicateurs GII

UtilisateursRapports BPEI

Demandes de validation des définitions

Modélisation de l’entrepôt

de données du BPEI

Non, rétroaction

Définitions approuvées ?

Commentaires des utilisateurs

Richard Massé

Oui

Configuration du chargement

Documentation

FichierExcel

FichierTexte

FichierSAS

Tables des systèmes opérationnelsInstitutionnels (SIGO, PeopleSoft, Maximo, etc.)

Serveur ETL Studio

Extraction des systèmes opérationnels

Marc Boivin Louis-René Rheault Claude Aussant

Extraction dans les différents secteurs

Extractions produites par nos collaborateurs

Obtention des données

Modélisation de l’entrepôt

Modèle d’intégration

Représente notre vision fonctionnelle de l’Université.Ce modèle est indépendant des considération techniques de stockage.

Modèle relationnel.Ajout du concept « temporel » au modèle.

Schéma Oracle = EDI_INTR

Modèles de diffusionPermet de diffuser l’information aux différents « utilisateurs ».

N’est pas nécessairement relationnel.Peux être en étoile, en flocon ou un simple fichier contenant l’information dans un format non normalisé.

Schéma Oracle = EDI_DIFF

Modèles de présentation

Est généré à partir du modèle de diffusion.Peut prendre plusieurs forme en fonction de l’outil d’exploitation (XML, Excel, Beyond 20/20, fichier SAS, etc.)

Le but du modèle de présentation est de permettre l’exploitation des données dans un outil donné.Le modèle de présentation peut être identique au modèle de diffusion si l’outil utilisé le permet .

Modèle de l’application GII

Modèle générique permettant de conserver les résultats contenus dans un rapport papier.Supporte environ 80% des rapports.

Permet de conserver le résultat d’une mesure donnée et non les enregistrements utilisés pour calculer ce résultat.

Schéma Oracle = D_EDI

Tables des systèmes opérationnelsInstitutionnels (SIGO, PeopleSoft, Maximo, etc.)

FichierExcel

FichierTexte

FichierSAS

Rapport remis au

BPEI

Modélisation de l’entrepôt

de données du BPEI

Richard Massé

Est-ce que l’information modélisée peut-être conservée

dans GII ?

Système GII - Entrepôt générique

Entrepôt de données du BPEI(en format Oracle)

Chargement de l’entrepôt via ETL STUDIO

Chargement via GII

Configuration du chargement

Non

Chargement

Diffusion des données

Extranet

Site Web du BPEI(Jahia)

Système GII - Module de définition des indicateurs

institutionnels

Luc Simon

Rapport décrivant les

indicateurs GII

Entrepôt de données du BPEIModèle de présentation en format SAS

SAS Excel

Direction de l’Université Laval

Enterprise Guide

Rapport en format « papier »

Rapports en format PDF ou HTML

Luc Simon Jacques Turcotte

Section publique

Cette section est accessible à tous.Utilisé pour diffuser de l’information officielle

Intranet

Cette section est accessible principalement aux agents deplanification des différentes facultés.

Cette section est accessible principalement aux agents deplanification des autres universités

G10 CREPUQ Ministère de l’éducation

Rapport expédié par courriel

www.ulaval.ca

Gestion de Indicateurs Institutionnels (GII)

• Un dictionnaire permettant de documenter des indicateurs.

• Une structure générique permettant de conserver des résultats.

Constatation : Avant de créer un entrepôt, vaux mieux savoir de quoi celui-ci va traiter !

Constatation : Les gestionnaires ne travaillent pas avec des tables et des colonnes mais bien avec des indicateurs. Par conséquence GII est :

www.ulaval.ca

Exemple de rapport - Fiche descriptive pour le comité du budget

www.ulaval.ca

Définition d’indicateurs

www.ulaval.ca

Définition d’indicateurs

www.ulaval.ca

Définition d’indicateurs

www.ulaval.ca

Gestion de l’historique

• Constatation : Le concept de temps n’est pas toujours présent dans un système opérationnel. Il est cependant nécessaire dans un système décisionnel.

individu

numero_dossier

nom

prenom

¥ code_statut

statut

code_statut

description1,1 0,N

www.ulaval.ca

Gestion de l’historique

• Constatation : Un cube permet de conserver l’historique des données relativement à un sujet précis.

• Mais que faire si l’on a une question imprévue ?

• Difficile de créer des cubes si l’on ne connaît pas les questions.

• Il nous faut un mécanisme plus générique.

www.ulaval.ca

Gestion de l’historique

• Il faut mettre en place un mécanisme nous permettant d’enregistrer l’état des systèmes opérationnels à un moment x. (Proc photo).

• Avec ce mécanisme nous avons le meilleur des 2 mondes. Nous avons une vision plus large qu’avec des cubes sans pour autant renoncer à ceux-ci.

• Bien entendu, il s’agit de photos et non d’un film!

www.ulaval.ca

Structure de l’entrepôt

• L’utilisation d’une structure semblable aux tables opérationnelles est possible car nos systèmes opérationnels sont relativement bien intégrés.

• Comme nous parlons des « mêmes » structures il est plus facile de comparer avec les acteurs des systèmes opérationnels nos requêtes et nos résultats sans pour autant délaisser le côté temporel des données.

• On ne parle pas ici d’une structure de diffusion mais bien d’une structure « d’archivage » de l’information contenue dans les systèmes opérationnels.

www.ulaval.ca

Structure de l’entrepôt

•La structure de l’entrepôt est la suivante :

IMGH_individu

numero_dossier

VERSION_ENTREPOT

nom

prenom

¥ code_statut

DATE_AJOUT_ENTREPOT

DATE_DESACTIVATION_ENTREPOT

TEMOIN_DOSSIER_ACTIF

IMGH_statut

code_statut

VERSION_ENTREPOT

description

DATE_AJOUT_ENTREPOT

DATE_DESACTIVATION_ENTREPOT

TEMOIN_DOSSIER_ACTIF

1,1 0,N

www.ulaval.ca

Comparaison et chargement

www.ulaval.ca

Structure de l’entrepôt

•Le chargement de l’entrepôt est fait de la façon suivante :

Entrepôt

Base de données opérationnelle

SIT$CONDITION_EMPLOI

SIT$DEMANDE_PAIEMENT

SIT$DONNEE_SOCIALE

etc...

SAS

3 - Écriture des modifications dans l’entrepôt1 – Copie de la table dans SAS

ABC_HIST

SEQ_ABC

VERSION

DATE_CREATION

HORODATE_DEBUT_ACTIVATION

HORODATE_DEBUT_FIN

TEMOIN_ACTIF

Autres variables

ABC

2 - Extraction de la version courante del’entrepôt

www.ulaval.ca

Production des rapports statutaires

•Les rapports statutaires sont créés à l’aide de SAS DM, SAS/Assist et Enterprise Guide.

• Les procédures sont encapsulées dans des macros afin de faciliter la lecture et la réutilisation.

• L’utilisation de macro va faciliter l’intégration dans ETL Studio.

www.ulaval.ca

Production des rapports statutaires (Exemple)

%extraire_condition_emploi(donnees.Imgh_condition_emploi, staging.condition_emploi);

%extraire_fractionnement_emploi(staging.condition_emploi, donnees.Imgh_fractionnement_emploi, staging.fractionnement_emploi);...

%i77nb_profs_reguliers_etc(staging.condition_emploi , donnees.Imgh_individu , staging.nb_professeurs_reguliers);...%consolidation_indicateurs;

%produire_tableau_g10;

Outils utilisés

Base de données Oracle de production (SEDI)

Base de données Oracle d’approbation (SEDA)

Base de données Oracle développement (SEDD)

Serveur de fichier BPEI contenantl’application GII (bpei1)

Serveur métadonnées de production

Poste de Luc Simon ou Jacques TurcotteContient les logiciels

SAS et Enterprise Guide

Poste de Richard MasséContient les logiciels

ETL Studio,SAS et

Enterprise GuideRapport Silverrun

Serveur de métadonnées SAS de développement

Responsable : SIT

Responsable : BPEI

Serveur Silverrun (Picasso)

www.ulaval.ca

Expériences acquises à ce jour

Les trois principaux facteurs à tenir en compte pour mettre en place un entrepôt de données sont :

1 - Les définitions

2 - Les définitions

3 - Les définitions !!!

www.ulaval.ca

Expériences acquises à ce jour

• Le nombre d’informaticiens disponibles

• La fréquence de chargement de l’entrepôt

• Les modes de diffusions des résultats

• La capacité d’analyse des outils sélectionnés

• La courbe d’apprentissage des produits utilisés

Les 5 facteurs suivants ont un impact très important sur l’architecture choisie :

www.ulaval.ca

Expériences acquises à ce jour

• Permet de produire des programmes plus faciles à lire et à décortiquer.

• Permet de bien isoler les définitions dans des blocs de code réutilisables intégrables dans ETL Studio.

• Permet de faire facilement le lien entre la définition GII des indicateurs et le programme correspondant.

• Permet de minimiser les « lacunes » de programmation SAS de l’auteur !

L’utilisation de traitement encapsulés dans des macros offre les avantages suivants :

www.ulaval.ca

Expériences acquises à ce jour

• Avec la version actuelle de SAS incluant le serveur de métadata et les procédures stockées on peut utiliser la puissance d’analyse de SAS et ce même à l’extérieur de l’environnement SAS.

• Avec l’ODS il est possible de produire de très beaux rapports et de permettre la diffusion de ceux-ci de multiples façons.

Notez ici que le présentateur n’avait pas fait de SAS depuisenviron 10 ans !

www.ulaval.ca

Expériences acquises à ce jour

• Les articles présentés dans le cadre des SUGI sont généralement très bien faits et très instructifs.

• Ces articles sont disponibles via le web.

Notez ici que le présentateur n’avait pas fait de SAS depuisenviron 10 ans !

www.ulaval.ca

Conclusion - Où sommes-nous rendus ? (phase 1)

• Mettre en place l’entrepôt.• Automatiser la production des rapports statutaires.

• Acquérir et mettre en place les outils d’analyse et de production de rapports ad hoc nécessaires au gens du BPEI.

• Diffuser les résultats de façon « statique » via notre site W3.

www.ulaval.ca

Où sommes-nous rendus ? (phase 2 – à venir)

• Diffuser les résultats de façon « dynamique » via ??? (XML – XSLT)

• Construire des cubes ?• Produire des analyses plus poussées à l’aide d’outil de forage de données ?

• Il y aura à considérer la mise en place des outils mais également l’implantation d’une culture liée à l’utilisation de ces outils.

www.ulaval.ca

Que nous réserve l’avenir ???

• Nous pensons que l’architecture actuelle sera adéquate d’ici la venue des progiciels de gestion des études et de gestion des ressources humaines.

• Cependant les questions d’intégration des données devront être revues en fonction des technologies disponibles (intégration par les données, EAI, SOA, etc.). Il est clair qu’un lien étroit avec les gens du département des systèmes est obligatoire si l’on veut réussir.

www.ulaval.ca

Questions

Richard Massé, M.Sc.Analyste de l’informatique

Vice-rectorat exécutif Bureau de planification et d’études institutionnelles (BPEI)

Téléphone : (418) 656-2131 poste 4258Courriel : Richard.Masse@vrex.ulaval.ca

MERCI !MERCI !