Introduction

Pour l’informatique, ou plus généralement pour les entreprises, un mot est récurrent : l’information. Celle-ci est essentielle de nos jours en entreprise, car c’est la connaissance de l’information qui permet la prise de décision.

Un des principaux rôles du système d’information est la gestion, l’exploitation et la sauvegarde de celle-ci.

A ce niveau, l’information correspond à des données. Le rôle de la Business Intelligence est de transférer, modifier, consolider et analyser les données afin de les rendre plus facilement accessibles, de faciliter la prise de décisions.

Pour commencer, le concept de la Business Intelligence sera détaillé afin de mettre en évidence l’intérêt de ce processus. Dans les chapitres suivants, un exemple du déploiement d’une telle solution sera présenté avec Microsoft SQL Server 2005.

Business Intelligence et SQL Server 2005

1.1 Généralités

Jusqu’à la mise en place des solutions d’informatique décisionnelle, les bases de données étaient des solutions de sauvegarde des informations dont le principal atout était le gain de place : un serveur de base de données peut contenir l’équivalent en volume d’informations de très nombreux classeurs. L’accès aux données était possible, mais réservé aux informaticiens.

En effet, il s’agissait de bases de données relationnelles, et il était indispensable de connaître la structure de la base pour s’y repérer, ce qui, lorsqu’il y a plusieurs bases contenant chacune plus d’une centaine de tables, peut réellement être difficile. Or, les marchés évoluant de plus en plus vite, il est devenu indispensable de trouver une solution permettant la prise de décisions rapide, c’est à dire une solution qui permette l’utilisation simplifiée des données, des informations.

Cette solution va consister à séparer le système opérationnel du décisionnel. Plus concrètement, le système d’information de l’entreprise sera constitué de deux pôles :

  • Un premier pôle, opérationnel, qui représentera la partie production de l’entreprise (ventes, commandes, gestion de stocks, données comptables, productions,…) et dont le rôle principal, au niveau informatique sera la saisie des données.
  • Un second pôle, décisionnel, qui consistera à rapatrier les données saisies au niveau opérationnel, à les analyser et à les traiter afin de les rendre accessibles pour les décideurs. C’est dans le cadre de ce second pôle que la Business Intelligence intervient. L’accès aux données se fait grâce à des rapports, indicateurs, graphiques,… qui permettent d’avoir facilement et en temps quasi-réel la situation de l’entreprise, ainsi que des estimations sur l’avenir.

1.2 Vocabulaire

Datamining, Datawarehouse, Business Intelligence, Datamarts, … nombreux sont les termes employés lorsque l’on travaille sur un projet de Business Intelligence. Voici quelques définitions des termes à connaitre avant de commencer un tel projet.

  • Business Intelligence : en français : informatique décisionnelle : solution permettant de regrouper les données dans un Datawarehouse et de les retraiter afin de faciliter la prise de décisions.
  • Datawarehouse : entrepôt de données adapté au traitement des données dans le but de fournir une aide à la décision. Il s’agit donc d’un concept spécifique à l’informatique décisionnelle et qui regroupe l’ensemble des données de l’entreprise sous forme de Datamarts, c’est un ensemble de Datamarts.
  • Datamart : magasin de données spécialisé dans un processus métier. Il s’agit d’un extrait d’un Datawarehouse. Utilisé directement par reporting services, il se situe entre le Datawarehouse et le reporting, les données sont déjà prêtes pour être utilisées à des fins décisionnelles.
  • Integration Services : outil de SQL Server 2000 & 2005 permettant le rapatriement des données. Il s’agit d’utiliser les fonctions de Business Intelligence de Visual studio afin de créer un package dont l’exécution automatique permettra de rapatrier les informations dans le Datawarehouse, ce qui facilite l’actualisation des données.
  • Analysis Services : outil de SQL Server 2000 & 2005 permettant l’analyse des données et la transformation de celles-ci afin de disposer d’un modèle dimensionnel, plus adapté aux traitements des données que le modèle relationnel lorsqu’il s’agit d’informatique décisionnelle.
  • Reporting Services : outil de SQL Server 2000 & 2005 permettant la mise en forme des données sous forme de rapports et graphiques, accessibles via un intranet, par mail, …
  • OLE DB : API Microsoft permettant d’utiliser simplement les bases de données de différents SGBD (Oracle, SQL Server,…) en vue de remplacer ODBC.
  • ODBC : API utilisé pour l’accès aux bases de données. Offrant des possibilités réduites, il vaut mieux lui préférer OLE DB lors du développement de Business Intelligence avec Visual Studio. A noter que l’accès aux bases de données Informix ne peut se faire que par l’intermédiaire d’ODBC.

1.3 Présentation de la solution SQL Server 2005

SQL Server, SGBD relationnel très utilisé en tant que tel, dispose depuis la version 2000 d’outils de Business Intelligence qui sont : integration services, analysis services et reporting services. Cette liste n’est pas exhaustive, mais il s’agit des trois principaux outils de BI disponibles à partir de la version standard. La solution de l’éditeur américain repose sur les technologies serveur Microsoft (Windows server et Internet Application Services) ainsi que sur la solution de développement Visual Studio.

sql

1.4 Pré-requis

Pour le déploiement d’une solution de Business Intelligence sous SQL Server 2005, il faut installer Internet Information Services dans la mesure où vous souhaitez permettre l’accès aux rapports depuis Internet ou un intranet. L’exemple qui illustrera cet article s’effectue sur un Serveur sous Windows server 2003 Entreprise édition, Microsoft SQL Server 2005 Standard Edition et IIS 6.0.

produit

Présentation du projet

En vue d’avoir un suivi quotidien de l’activité de l’entreprise : la Brigade de Sapeurs Pompiers de Paris, une solution de Business Intelligence doit être mise en place afin de faciliter l’accès aux statistiques opérationnelles.

Pour cela, une base relationnelle qui actuellement sert d’historique va être utilisée. Cette base, ancienne, ne respecte pas les règles classiques d’une base relationnelle (absence de clé primaire, …), ce qui ne sera pas sans poser quelques problèmes. La structure de la table est difficile à comprendre au premier abord, ce qui illustre parfaitement la difficulté d’accès aux informations pour toutes personnes ne connaissant pas la structure de la base.

La structure est décrite ci-dessous plus précisément.

2.1 Gestion des adresses et des sites

La BSPP a recensée toutes les rues de sa zone d’intervention. On distingue dans cette zone trois types de sites :

  • Adresses classiques
  • Etablissements signalés
  • Etablissements répertoriés.

Toutes les informations concernant les rues de la zone d’intervention de la BSPP et les différents types de sites sont disponibles dans les tables suivantes :

  • rue4 (contient les numéros et libelles des rues)
    •  numrue, clé primaire
    • deno : dénomination des rues
  • mc5 (contient les informations complémentaires (type de rue, mots clefs))
    • numrue : identification de la rue
    • codtyp : avenue, boulevard,…
    • motcle : mots clefs permettant la recherche lors d’un d’appel
  • proche (contient des informations complémentaires sur le découpage des rues par zone d’intervention ainsi que les informations concernant les établissements répertoriés)
    • numrue : identification de la rue
    • numsit : identification du site (égal à « NULL » si adresse classique, ainsi, si une rue contient quatre établissements, il y aura cinq entrées dans la table).
  • signale ( contient les informations sur les établissements signalés, la structure étant la même que « proche », ces informations auraient pu être directement intégrées dans la table « proche »)
    • numrue : identification de la rue
    • numsit : identification du site (ne contient que des établissements signalés, ainsi, si une rue contient quatre établissements, il y aura quatre entrées dans la table) (pour « proche » et « signale » : d’autres champs concernant le découpage par zone existe, mais dans la mesure ou ceux-ci ne sont pas utiles dans le cadre du rapport de statistiques opérationnels, ils ne sont pas présentés).
  • site (contient les informations sur les établissements (signalés et répertoriés))
    • numsit : identification de l’établissement
    • raisoc : raison sociale de l’établissement
  • commune (contient le nom de la ville ainsi que le département)
    • codvil : identification de la ville
    • ville : libelle de la ville
    • coddep : code du département
  • types (contient le libelle du type de voie (avenue, rue, …)
    • codtyp
    • libtyp

2.2 Motifs d’interventions

Deux tables existent pour la gestion des motifs d’interventions :

  • motif (contient tous les motifs de départ de véhicules en interventions)
    •  codmtf
    •  libmtf
  • motinv (contient tous les motifs réels d’interventions : renseignés par le responsable de l’intervention après celle-ci, cela permet de renseigner plus précisément le type d’intervention effectué et de corriger les erreurs du au manque de précision lors des appels)
    • codmtf
    •  libmtf

Ainsi, il est possible qu’un véhicule qui part pour feu de poubelle soit en réalité confronté à un feu d’appartement. Ce système permet de distinguer le motif de départ et l’intervention réellement effectuée.

2.3 Intervention, ordre et CS

Trois autres tables vont être nécessaires pour la mise au point du projet.

  •  intervention (contient les informations sur l’intervention : lieu, date de l’appel, …)
    •  numint : numéro de l’intervention (remis à zéro chaque année)
    • csemet : centre émetteur du départ sur intervention (csemet + numint forment la clef primaire par année des interventions)
    •  numrue : adresse de l’intervention
    •  numsit : établissement concerné (si nécessaire)
    •  datapp : date de l’appel (date + heure (à la seconde))
    •  codmtf : motif de départ
    •  codint : motif réel
  • ordre (recense la date de l’ordre de départ en intervention et le CS (centre de secours) sélectionné pour cette intervention (il peut donc y avoir plusieurs entrées pour une intervention))
    • numint : numéro de l’intervention (remis à zéro chaque année)
    • csemet : centre émetteur du départ sur intervention (csemet + numint forment la clef primaire par année des interventions)
    • datord : date de l’ordre de départ
    • csint : CS concerné par l’intervention
    • cs (recense tous les centres de secours de la brigade)
    • codcs : identifiant du CS
    • libcs : libellé du CS
    • grpt : groupement du CS (la zone brigade est découpée en trois groupement)
    • pccie : compagnie du CS (chaque groupement est subdivisé en compagnie d’incendie)

Il s’agit des champs qui seront utilisés durant le projet.

2.4 Problèmes liés à la structure de la base de données

Comme cela est visible, la base est loin d’être optimisée (peu de clef primaire, information redondante). En effet, maintenant que la structure de type relationnelle du projet est présentée, il est possible de se rendre compte des problèmes que peut poser une base de ce type. Ces problèmes empêchent un accès simple aux données dans le but de prendre une décision.

Les problèmes qui vont se poser sont les suivants :

Pas de jointure simple possible entre intervention et ordre dans la mesure ou nous travaillons sur une base ancienne de plusieurs années et que « numint » se remet à zéro tous les ans. Il va être nécessaire d’utiliser les dates, mais les mêmes références ne sont pas disponibles dans ces deux tables (« datord » diffère de « datapp »).

La gestion des rues est très complexe, avec des redondances d’informations. Il va être nécessaire de revoir entièrement la structure de la table adresse en décomposant selon trois cas : le cas des adresses classiques, le cas des établissements répertoriés et le cas des établissements signalés (ces deux derniers pouvant être traités de la même façon.

Il faudra enfin prendre en compte les possibles évolutions : changement de nom de rue, … afin de créer un historique de ces interventions.

Nous allons pouvoir corriger ces problèmes avec integration services.

note

Integration services

Grâce à integration services, il est possible de créer simplement un package de récupération des données, package qui, une fois installé sur le serveur SQL de production, se chargera de rapatrier le contenu des bases sources vers le Datamart et ce, selon la planification configurée lors de l’installation du package sur le serveur.

Avant de s’intéresser au fonctionnement d’Integration services, il est indispensable d’établir la modélisation du Datamart. Pour accroître les performances, le modèle relationnel est délaissé en Business Intelligence au profit du modèle dimensionnel ou multidimensionnel.

3.1 Modélisation dimensionnelle

Il est important de comprendre la structure des données en Business Intelligence avant de commencer à réaliser la modélisation dimensionnelle.

L’ensemble des données sera stocké dans le Datawarehouse. Celui-ci sera constitué de plusieurs Datamarts, chacun répondant à un besoin métier spécifique. Dans notre exemple, ce besoin est le suivant : avoir un accès simple aux différentes statistiques opérationnelles. Nous allons donc concevoir un Datamart répondant à ces contraintes. Il existe principalement deux types de modélisation : l’étoile et le flocon. Le flocon étant un dérivé de l’étoile avec une notion de hiérarchie supplémentaire, nous allons utiliser l’étoile.

Le principe est le suivant : chaque Datamart contient une table des faits et des tables de dimensions selon le schéma suivant :

La table de faits contient les ids des dimensions et les valeurs numériques non connus avant le dépannage. Ainsi, pour distinguer les dimensions de la table des faits, il faut retenir qu’une dimension ne peut être qu’un ensemble fini d’éléments connus avant que le fait ne se produise.

Ainsi, ici, adresse, motif, motinv, date, heure et CS sont connus avant qu’une intervention n’ait lieu. La date et l’heure sont la plupart du temps des dimensions. Selon le degré de précision nécessaire, il est suffisant, d’avoir une seule et unique table contenant à la fois la date et l’heure.

Néanmoins, dans le cadre de l’exemple, nous souhaitons avoir une précision à la seconde. Supposons que nous envisagions une période de 10 ans. Cela nécessiterait (60*60*24*365*10) = 315 360 000 enregistrements. En effet, dans une table de dimension, tous les cas doivent déjà être présents dans la table. Devant le nombre trop élevé d’enregistrements nécessaires dans le cadre de l’exemple, il est préférable de subdiviser heure et date, ce qui fait respectivement 86400 et 3650 enregistrements.

En résumé, en modélisation dimensionnelle, tous les paramètres pour lesquelles les valeurs sont connues deviennent des dimensions, les autres sont stockés dans la table des faits. Ici, on va obtenir une dimension CS, une dimension date, une dimension heure, une dimension motif, une dimension motinv et une dimension adresse. La seule inconnue est le numéro dans la rue.

ATTENTION : dans l’exemple, la table Adresse deviendra une dimension seulement dans la mesure où nous disposons de toutes les rues de Paris. Si cette valeur était une donnée récupérée au fur et à mesure des interventions, cette donnée aurait été stockée dans la table des faits.

NB : Dans le cas ou les dimensions peuvent subir de légères modifications, elles restent considérées comme des dimensions et integration services permettra de tenir compte des changements.

3.2 Démarrer avec integration services

Pour commencer un projet integration services, il faut lancer le logiciel SQL Server Business Intelligence Development Studio et choisir nouveau>projet>projet integration services.

Automatiquement, un package est crée, par défaut, il se nomme package.dtsx. A partir de la, deux solutions sont possibles :

  • Utilisation d’un assistant import/export : l’utilitaire crée automatiquement les flux de données qui correspondent aux besoins.
  • Création manuelle des flux de données.
  • C’est cette dernière solution qui va être détaillée.

3.3 Création manuelle du package

Deux types de flux sont disponibles les flux de contrôles et les flux de données. Pour utiliser des flux de données, il faut placer un flux de contrôles de type « Tâche de flux de données ». De nombreux autres flux de contrôles existent, seulement trois seront utiles dans cet exemple : « Tâche de flux de données », « Tâche de traitement SQL Server Analysis Services » et « Tâche d’exécution de requêtes SQL ». Le principe reste valable pour les autres flux de contrôles, sachant qu’une documentation est disponible sur le site MSDN.

Pour créer une tâche de flux de contrôle, il suffit de faire un simple glissé-déposé avec celle-ci sur l’espace de travail.

IMPORTANT : Ne pas hésiter à subdiviser en nombreux flux de contrôles, en effet, il est possible de les désactiver indépendamment les uns des autres, ce qui permet, lors des tests de n’exécuter que la partie nécessaire.

Voici la structure en flux de contrôles de l’exemple :

La tâche d’exécution de requête SQL n’apparait pas ici, elle a été utilisée afin de créer un package de remise à zéro (suppression et création des tables avec leurs contraintes d’intégrité), package indépendant de celui-ci qui servira à la récupération quotidienne des données.

Le package va nécessiter des connexions vers les serveurs de données d’origine et de destination, il faut au préalable avoir crée des connections vers les bases de données sollicitées au niveau du gestionnaire de connexions (ne pas confondre avec les sources de données). Dans notre exemple, nous allons configurer deux types de connexions différents, l’une vers un serveur MySQL (Source) avec ODBC (Assurez vous d’avoir installé le pilote ODBC préalablement.), l’autre vers un serveur SQL Server 2005 (destination). Pour cela, il suffit de faire un clic droit sur l’espace du gestionnaire de connexions, de choisir « Nouvelle connexion… » et de choisir le type de connexion (ODBC ou OLE DB dans notre cas).

Nous avons ainsi deux connexions : une source qui s’appelle MySQL et une destination : StatOPE. StatOPE correspond au serveur SQL qui hébergera la solution de Business Intelligence.

3.4 Gestion des flux de données

Pour l’instant, aucune donnée n’a pour l’instant été récupérée depuis le serveur source MySQL.

Pour récupérer des données et les traiter, nous allons utiliser les « Tâches de flux de données » crées précédemment. Pour cela, il suffit, soit de cliquer sur l’onglet flux de données puis de choisir dans la liste déroulante sur quel flux nous souhaitons travailler, soit de double cliquer sur la « Tâche de flux de contrôle » correspondante.

schema

De nombreux éléments peuvent être utilisés sur les flux de données :

schema

De même que pour les flux de contrôles, toutes ces transformations de flux de données ne seront pas utiles dans cet exemple.

Mise en place de la source

Pour pouvoir récupérer les données depuis le serveur MySQL, étant donné que l’on ne peut pas utiliser une source OLE DB avec MySQL, il faut utiliser une source de type datareader.

schema

Puis, il faut configurer MySQL comme source, et enfin, taper la requête SQL permettant de récupérer les données souhaitées (seules les requête reconnu par la version du serveur MySQL utilisé comme source seront reconnues).

schema

schema

NB : Pour les sources de type OLE DB, la manipulation sera similaire à la mise en place du destinataire du flux de données, étant donné que dans notre exemple, il s’agit d’un serveur SQL Server 2005. Inversement, en cas de destinataire de type MySQL, il conviendra d’utiliser un destinataire de type datareader.

NB : A noter que pour les sources de données de type OLE DB, il n’est pas indispensable de taper une requête SQL, il est possible de cocher les colonnes souhaitées.

Réalisation d’une jointure

schemaschema

Très utile dans le cadre de l’exemple, la jointure est assez simple à mettre en place.

Attention, il est indispensable de procéder à un tri sur le flux de données avant la jointure.

Celle-ci présente deux entrées. Les deux entrées doivent avoir au moins une clef en commun, trié dans le même ordre. Il en va de même lorsqu’il y a plusieurs clefs.

schema

On obtient ainsi la jointure suivante :

schema

Utilisation de Unir tout

schema

Unir tout est un composant qui permet de réunir les informations de plusieurs flux de données. Ainsi, dans notre exemple, nous avons trois catégories d’adresse, l’adresse classique, l’adresse des établissements répertoriés qui possède en plus de l’adresse classique un numéro de site, et l’adresse des établissements signalés qui dispose là encore d’un numéro de site. Nous allons donc réunir les trois types d’adresses dans une seule et même table avec l’adresse classique plus le numéro de site (à NULL si adresse classique).

Il suffit de choisir les colonnes, en indiquant à gauche, les colonnes de la table finale et à droite les colonnes correspondantes dans les tables existantes.

schema

Dimension à variation lente

schema

La dimension à variation lente est un composant indispensable dans notre exemple. Il s’agit du composant qui va prendre en compte les éventuelles modifications dans une table de dimension. Un assistant se lance lors de la configuration du composant.

ATTENTION : Pour la mise en place du composant, il est indispensable de posséder une clef d’entreprise dans la table de dimension. Cette clef, qui ne peut pas être un attribut auto-incrémenté, doit être unique. La clef d’entreprise correspond donc à un attribut ou a un ensemble d’attributs qui pourrai(en)t remplir le rôle de clef primaire.

Par la suite, il suffit de sélectionner la table finale et de spécifier les attributs qui sont des clefs d’entreprises.

schema

Puis, concernant les autres attributs, il faut choisir de quel type d’attribut il s’agit. Trois possibilités sont offertes :

  • Attribut fixe : les attributs ne peuvent pas être modifiés;
  • Attribut Modifiable : les attributs peuvent être modifiés, les anciennes données sont perdues. (Cela peut être problématique vis à vis de la table des faits, car on peut se retrouver avec un attribut qui pointe sur un élément ayant été modifié);
  • Attribut d’historique : les attributs doivent disposer d’une à deux colonnes en plus, et il possède ainsi une date de début et une date de fin, les attributs n’ayant pas de date de fin étant les attributs possédant les valeurs les plus récentes.

schema

Il suffit alors de spécifier le nom des colonnes (de type datetime) qui contienne les informations de début et de fin.

schema

Les modifications éventuelles des tables de types dimensions sont ainsi prise en compte. Dans l’exemple, nous avons choisi les attributs d’historiques. A la fin de l’assistant, tous les composants nécessaires sont automatiquement générés, depuis la gestion des modifications jusqu’à l’écriture dans la table. Ce qui nous donne, dans le cas des attributs de type historique :

schema

Tous les éléments nécessaires aux traitements des adresses sont expliqués. Le schéma de flux de données est le suivant :

schema

schema

La fin étant la même que le schéma de dimension à variation lente ci-dessus.

Destinataire de flux de données

schema

De même que pour la source de flux de données, nous utilisons dans l’exemple principalement deux types : datareader ou OLE DB. Dans notre cas, c’est un serveur SQL qui est destinataire des données (99,9% des cas en Business Intelligence avec SQL Server 2005, la base étant stockée sur le serveur de BI), nous allons donc utiliser un composant OLE DB comme destinataire. Il suffit alors de sélectionner la connexion à utiliser, StatOPE dans notre cas, puis de choisir la table de destination.

schema

Il ne reste plus qu’à vérifier le mappage des colonnes :

schema

NB : Le composant vous propose de créer la table adéquate correspondant au flux d’entrée du composant, pour cela, il suffit de cliquer sur nouveau, au niveau du choix de la table.

schema

3.5 Conclusion : Integration services

Les composants décrient ci-dessus permettent de traiter les cas des dimensions. Il y a encore de très nombreux autres traitement possible sur le flux de données, tel que le changement de type de données ou le composant script qui permet d’exécuter un script en Visual basic (utilisé dans la table de fait pour changer le format de date : AAAA-MM-DD en DD/MM/AAAA sans avoir recours à un format de type datetime). Voici ci dessous l’intégralité des trois flux de données du package.

Transfert des 6 dimensions :

schema

Création de la table de faits

schema

Le traitement avec Integration Services est terminé. On dispose maintenant d’un package de rapatriement des données.

ATTENTION : Lors de la création de vos projets IS, ne perdez jamais de vue qu’il s’agit d’un package qui sera exécuté régulièrement et automatiquement, dont le but est le rapatriement des données.

Analysis services

Analysis services est un outil qui va être utilisé pour l’optimisation des données en vue de la prise de décision. Il permet également la mise en place d’action et d’indicateur de performance clé (KPI). Etant donné qu’un modèle ressemblant au modèle dimensionnel a été mis en place sous integration services, l’utilisation d’Analysis services est simple, il sera assez aisé de créer un cube. La prise de décision nécessite des indicateurs et des mesures. Ces informations doivent être organisées selon des axes d’analyse. Ces axes correspondent aux dimensions. Le cube correspond à l’organisation géographique de ces données avec une table des faits (origine) et trois tables de dimensions (axes). Il s’agit donc d’un cas particulier, néanmoins, le terme de cube est utilisé couramment lorsqu’il s’agit d’un tableau (une table des faits et deux dimensions) ou lorsqu’il s’agit d’un hyper cube (une table des faits et n dimensions avec n>3).

La création du cube sous Analysis services va permettre d’améliorer les performances d’analyses (pré calcul) en mettant en place une base de données multidimensionnelle à partir de la base de données en étoile (ou flocon) issue d’integration services.

4.1 Création d’un projet analysis services

De même que pour intégration services, il faut utiliser Business Intelligence Development Studio et choisir « Nouveau projet Analysis services ». Puis, avant de pouvoir mettre en place le cube, il faut mettre en place la source de données ainsi que la vue de source de données.

Mise en place des sources de données

Pour mettre en place une source de données, il faut faire un clic droit sur source de données, puis sur « Nouvelle source de données ». Un assistant se lance.

schema

Deux cas se présentent, soit la connexion est déjà présente et il suffit de la sélectionner, soit elle n’existe pas et il faut la créer. Pour cela, il suffit d’indiquer le serveur utilisé et de sélectionner la base de données utilisée. Après avoir sélectionné la source de données, il faut préciser le compte à utiliser pour la connexion. La source de données est configurée. ATTENTION : Assurez-vous que le compte indiqué dispose des autorisations adéquates sur SQL Server Analysis services (différentes à celle de SQL Server).

Mise en place d’une vue de source de données

De même que pour la source de données, il faut faire un clic droit sur vue de source de données pour sélectionner « Nouvelle vue de source de données ». Un assistant se lance. Sélectionnez la source de données que vous souhaitez utiliser.

schema

Précisez, en fonction de votre base de données sources, comment doivent être identifiées les clefs étrangères. Sélectionnez les tables nécessaires au projet (toutes dans le cas de l’exemple).

schema

Il ne vous reste plus qu’a nommer la vue. Cette dernière est affichée. Vérifiez que tous les liens entre la table de fait et les tables de dimensions (clé primaire/étrangère) existe. Si ce n’est pas le cas, vous pouvez compléter la représentation. Vous obtenez ainsi une représentation du projet.

schema

La vue de source de données est disponible.

Configuration du serveur distant

Dans le cas ou le serveur de déploiement est un serveur distant, il faut le configurer. Pour cela, il suffit de faire un clic droit sur le projet et de sélectionner propriétés. Puis, choisissez déploiement. Il vous reste à préciser le serveur et la table (celle-ci doit être préalablement crée sur le serveur SQL.).

ATTENTION : Comme mentionné ci dessus, il est indispensable que l’utilisateur effectuant la mise en place d’Analysis services dispose des autorisations adéquates sur le serveur distant.

schema

4.2 Création du cube (et des dimensions)

Dans la mesure où nous disposons déjà des tables organisées en tant que table de fait et tables de dimensions, la création du cube est très rapide, et la génération des dimensions automatique.

Après avoir sélectionné « Nouveau cube » avec un clic droit sur cubes, il faut sélectionner : « Construire le cube en utilisant une source de données », ainsi que « génération automatique ».

schema

Puis, il faut sélectionner la source de données correspondantes.

schema

Il faut alors vérifier que la table de fait et les tables de dimensions sont correctement sélectionnées.

schema

Puis, vous pouvez générer le cube (ne pas modifier les autres options).

schema

Le cube est généré, les dimensions également.

schema

Et la structure du cube est la suivante :

schema

Il ne reste plus qu’à traiter le projet (clic droit sur le projet, traiter).

Pour vérifier, vous pouvez vous connecter sur le serveur SQL Analysis Services.

schema

Il existe de nombreux autres outils qui pourront faire l’objet d’un autre article, tels que le KPI, les actions,…

Néanmoins, à l’instar d’Integration services, nous ne pouvons aborder toutes les options et autres fonctionnalités. Pour découvrir celle-ci, vous pouvez vous référer à la bibliographie située à la fin de cet article.

Reporting services

Reporting services est le service qui va permettre l’accès aux informations sous formes de graphiques et/ou de rapports. Pour permettre l’accès aux données via un intranet et/ou Internet, il est indispensable qu’Internet Information Services soit installé. L’accès au serveur de rapports a alors lieu par l’adresse : ‘ https://ip_du_serveur_ou_nom_dns/reports/’.

5.1 Création d’un projet reporting services

De même que pour intégration services, il faut utiliser Business Intelligence Development Studio et choisir « Nouveau projet Report server ».

Il est également nécessaire de configurer la source de données (se référer à Analysis services).

ATTENTION : Il ne s’agit plus de se connecter à SQL Server mais à SQL Server for Analysis Services.

5.2 Création d’un rapport

Pour créer un rapport, il faut faire un clic droit sur rapport et sélectionner « Ajouter un nouveau rapport ». Un assistant se lance. Il faut commencer par sélectionner la source de données.

Puis, il va falloir générer une requête DMX (comparable à SQL pour les bases de données), pour cela, il faut cliquer sur le générateur de requêtes.

schema

Par la suite, il suffit de faire de simple glissé-déposé des attributs que l’on souhaite voir apparaître sur le rapport final.

Par exemple, le nombre de fait par zone et par type de dépannage.

schema

Puis, on choisi le type de présentation de données souhaité : tabulaire ou matrice. Dans notre exemple, nous choisissons matrice.

Puis, nous répartissons les attributs sélectionnés précédemment en fonction de l’affichage souhaité.

schema

Avec grpt qui représente le regroupement en zone de dépannage, codint et libint étant les identifiants du type de dépannage et fait_count étant le nombre de fait correspondant à ce tri.

Puis, nous choisissons un thème graphique (ici, ardoise).

Le rapport est prêt. Un aperçu est immédiatement disponible en cliquant sur aperçu.

schema

NB : Le regroupement est obtenu en modifiant le groupe libelle : on choisi de masquer le libelle par défaut et on choisi le code comme étant l’élément pouvant l’affiché.

schema

De même, on peut appliquer des tris et des filtres.

Ainsi, si on rend visible les raisons du dépannage, on obtient par exemple :

schema

5.3 Mise en place d’un graphique

Les graphiques sont disponibles dans les outils. Pour mettre en place un graphique (par exemple un graphique de type secteur) il suffit de glisser ce composant à l’emplacement souhaité, de sélectionner le type de graphique et de spécifier les différents paramètres.

schema

On obtient ainsi :

schema

Il y a encore de nombreuses options qui ne seront pas développez ici, referez-vous à la bibliographie.

5.4 Publication du rapport

Avant de publier le rapport sur le serveur IIS, il est indispensable de configurer l’accès à ce serveur. Pour cela, il faut remplir le champ TargetServerURL avec l’URL permettant d’importer les rapports (par défaut : https://nom_du_serveur_ou_ip/reportserver/).

ATTENTION : Ne pas confondre cette URL avec l’URL d’accès au site de reporting (https://nom_du_serveur_ou_ip/reports).

Une fois configuré, il suffit de faire un click gauche sur le projet, de choisir déployer, et de se rendre à l’adresse du site de reporting, le rapport est en place.

Annexe 1 – Gestion de la sécurité du site de reporting

A1.1 Introduction

Dans le but de contrôler l’accès aux ressources du site internet de reporting, trois processus seront implémentés pour le contrôle et la gestion des droits :

  • Authentification à partir des autorisations NTFS (configurée à partir d’IIS)
  • Gestion des droits à partir du site Internet
  • Gestion de la sécurité des sources de données

Cette implémentation, qui sera intégralement basée sur Active Directory, va permettre de contrôler les utilisateurs avant l’accès au site, tout en leur permettant de ne voir que les rapports et dossiers les concernant.

Les étapes nécessaires à la mise en place de cette gestion des droits sont détaillées ci-dessous.

A1.2 Autorisation IIS/NTFS

Cette première partie a pour but de contrôler l’identité de l’utilisateur du site, identité qui permet par la suite de gérer les droits. Ce type d’authentification se base sur le système de fichiers NTFS qui permet de créer des droits d’accès sur des dossiers, droits qui définiront les accès au site internet.

La configuration se fait ainsi en deux étapes :

  • Configuration d’IIS pour la prise en charge du contrôle des droits.
  • Configuration des autorisations NTFS sur le dossier contenant le site Internet.

Configuration de IIS

Pour paramétrer ces autorisations, il faut se rendre dans les propriétés du site dans lequel est installé le reporting (par défaut : « Site Web par défaut »), puis à l’onglet « Sécurité du répertoire ».

schema

Dans « Authentification et contrôle d’accès », décocher « Activer la connexion anonyme ».

schema

Quatre types d’authentification existent sous IIS :

  • Authentification Windows intégrée : récupération des informations d’ouverture de sessions pour l’identification sur le site (valable si chaque utilisateur consulte le site depuis un ordinateur appartenant au domaine et sur lequel il s’est identifié avec ses paramètres personnels lors de l’ouverture de session).
  • Authentification Digest pour les serveurs de domaine Windows : une boîte de dialogue apparaît demandant à l’utilisateur de saisir son login et mot de passe du domaine pour se connecter au site Internet. Par commodité, l’utilisateur peut enregistrer son nom d’utilisateur et mot de passe sous sa session pour permettre un accès rapide au site. Ce type d’authentification n’est possible que sur des serveurs de domaine Windows.
  • Authentification de base : ATTENTION : NE PAS UTILISER SAUF SI UTILISE CONJOINTEMENT AVEC SSL. En effet, il s’agit d’une authentification similaire à l’authentification DIGEST mais le mot de passe est transmis en clair sur le réseau (risque de capture de trame).
  • Authentification .NET Passport : Cette méthode requiert l’accès à Internet, il n’est donc pas envisageable de l’utiliser sur un Intranet.

L’utilisation de l’authentification DIGEST est recommandée, car, bien que nécessitant une boîte de dialogue supplémentaire, celle-ci permet une authentification fiable et sécurisée, sauf si l’utilisation du site se fait seulement depuis des postes clients du domaine Windows (pas d’accès depuis un extranet par exemple), auquel cas l’authentification Windows intégrée est en effet suffisante en terme de sécurité et est transparente pour l’utilisateur.

L’association des deux peut se révéler très intéressantes, dans la mesure où cela permettra une authentification transparente pour les utilisateurs déjà logués sur leur machine avec un compte disposant des autorisations d’accès au site, et cela laissera tout de même la possibilité d’utiliser un autre compte le cas échéant.

Il faut néanmoins noter que tout ordinateur laissé avec une session non verrouillée permettra à un utilisateur lambda d’accéder au site avec les droits du propriétaire de la machine (cela peut aussi être le cas si l’utilisateur enregistre son mot de passe avec l’authentification Digest).

Une fois le type d’authentification choisi, il ne reste plus qu’à configurer les autorisations NTFS du dossier du site.

NB : L’utilisation de SSL reste possible, notamment dans la perspective de la mise en place d’un accès depuis Internet, ou cela deviendra indispensable avec la mise en place d’une autorité de certification (sauf si l’accès se fait depuis un VPN L2TP/IP Sec).

Configuration des autorisations NTFS

La mise en place des autorisations NTFS peut se faire directement depuis le gestionnaire des services Internet (IIS).

schema

Pour configurer ces autorisations, il suffit de faire un clic droit sur le répertoire virtuel du serveur de rapport et de choisir autorisations.

On a ainsi accès à la fenêtre de configuration NTFS du répertoire contenant le site.

schema

Pour simplifier la gestion des droits, sachant qu’il ne s’agit que de contrôler l’accès au site, il peut être plus simple de créer, dans Active Directory, un groupe report contenant tous le personnel autorisé à accéder au site.

Ainsi, il suffira d’accorder au groupe l’autorisation de lecture sur le dossier. Par la suite, il suffira d’ajouter les utilisateurs ayant l’autorisation de se connecter au site au groupe report.

ATTENTION : Les objets systèmes « utilisateurs » et/ou « utilisateurs authentifiés » doivent être supprimés.

A1.3 Gestion des droits sur le site

Le site possède son propre système de gestion de l’accès aux ressources. Il s’agit d’un système très proche des autorisations NTFS et qui se base sur l’identité lors de l’authentification auprès du site, et donc des informations de l’Active Directory.

Pour chaque dossier et rapport, il est possible de configurer les autorisations d’accès. Par défaut, ils héritent des autorisations du dossier parent, mais il est néanmoins possible de rompre cet héritage, puis de le rétablir par la suite.

Pour configurer ces options de sécurité, il suffit de se rendre dans les propriétés du dossier et/ou du rapport puis dans la section sécurité.

Cinq options sont disponibles ensuite :

  • Browser : Autorise l’accès au dossier/rapport en lecture seule.
  • Content manager : Permet de gérer le contenu du serveur : ajout de dossier, ajout de rapport.
  • My Reports : Permet de créer et de gérer les dossiers/rapports dans un espace personnel (actuellement désactivé sur le serveur).
  • Publisher : Permet de publier des rapports et de créer des rapports liés accessibles à tous (contrairement à My Reports).
  • Report Builder : Permet d’avoir accès au générateur de rapport.

schema

schema

Ces autorisations, paramétrables pour chaque dossier et rapport permettent un contrôle précis des ressources.

Ainsi, dans l’exemple ci-dessous, on peut constater l’accès différent aux ressources pour un même dossier en fonction du compte utilisé lors de la connexion.

schema

Cet exemple porte sur des dossiers, il est possible d’obtenir le même résultat avec des rapports.

schema

NB : A noter la différence des droits qui est visible également au niveau de la barre d’outils.

schema

Par l’utilisation de ces deux processus d’authentification, il est possible, simplement et de façon très précise, de restreindre l’accès aux ressources et au site.

A1.4 Gestion de la sécurité des sources de données

Un dernier point concerne l’utilisation des sources de données. En effet, par défaut, la sécurité intégrée de Windows est sélectionnée.

Néanmoins, dans la mesure où cela peut permettre d’accéder aux données en empruntant l’identité d’un utilisateur sans que ce dernier soit au courant, il est conseillé de désactiver cette possibilité dans l’utilitaire de configuration de la surface d’exposition : Utilitaire de configuration de la surface d’exposition > l’utilitaire de configuration de la surface d’exposition pour les fonctionnalités > MSSQLSERVER > Reporting services > Sécurité intégrée de Windows > décocher « Activer la sécurité intégrée de Windows pour les connexions à la source de données de rapports ».

schema

Aussi, lors de la configuration des sources de données, il sera nécessaire de fournir un compte permettant la connexion à la base de données (compte ayant les droits nécessaires pour l’accès aux données).

Ce compte peut être différent pour chaque serveur de base de donnée afin d’accroître la sécurité, il devra disposer de droit limité à la consultation des données et l’option du compte active directory « le mot de passe n’expire jamais » devra être côchée.

schema

A1.5 Conclusion

La mise en place de la sécurité d’un site Internet de reporting doit se faire avec méthode, car la multiplicité des options peut, le cas échéant, entrainer l’oubli d’une option, oubli qui, lorsqu’il s’agit de sécurité, peut avoir de fâcheuse conséquence.

Néanmoins, une bonne gestion des droits lors de la mise en place va permettre par la suite un accès agréable et transparent pour l’utilisateur (dossier interdit non visible, authentification invisible car lié à l’ouverture de session,…).

Annexe 2 : Personnalisation du site de reporting

Le site Internet de reporting utilise la technologie CSS. Il est donc assez aisé de modifier la charte graphique de celui-ci.

Le fichier de style CSS se trouve à la racine du site de reporting, dans le dossier style : C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportManager\Styles\

Il s’agit du fichier : ReportingServices.css.

Toutes les couleurs utilisées sont listées au début du fichier. Dans la mesure où il n’est pas possible de connaître le code du site de reporting, il sera difficile de modifier les paramètres autres que les couleurs et les images utilisées. Les couleurs étant commentées, il est possible de modifier rapidement ces dernières.

Les images facilement modifiables sont logo.jpg et fond.jpg situées dans le dossier images à la racine du site de reporting : C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ ReportManager\Images\

  • Logo correspond à la bannière du site. Fond correspond à l’arrière plan du site. Les dimensions de ces images doivent être de : Logo : 85 pixel (ATTENTION : il s’agit de la valeur maximale sous peine de modifier l’organisation du site). Pour la largeur, dans la mesure où cette image est centrée, et afin de prendre en charge les différentes résolutions, il faut soit prévoir une image large (1600 pixels actuellement) dont les bords extérieur sont unis, soit une image d’une largeur inférieur à 750 pixels qui apparaitra de façon centré sur le site. Le premier cas peut se révéler plus simple à l’usage pour la gestion de la couleur des polices.
  • Fond : mis à part la prise en compte des différentes résolutions, il n’y a aucune contrainte sur la taille de cette image.

Au final, voici le résultat pour les Pompiers de Paris :

Avant : schema

Après : schema

Conclusion

Si de nombreuses grandes entreprises ont adoptées la Business Intelligence, ce n’est pas le cas de toutes. La raison principale étant le coût, l’arrivée de la solution de Business Intelligence de SQL Server 2005 pourrait démocratiser l’accès à cette technologie, qui, bien exploitée, peut faciliter la prise de décision et permettre d’avoir une vue objective de la santé de l’entreprise.

En effet, à travers l’exemple qui a été abordé, on peut constater la simplicité de mise en oeuvre. Et si tout n’est pas encore configurable, il s’agit néanmoins d’une solution bon marché de Business Intelligence qui allie simplicité, performance et sécurité, le tout autour d’un SGBD réputé.

A travers les différentes déclinaisons de SQL Server, et les nombreuses fonctionnalités, il apparaît clairement que ce produit peut convenir à la fois aux grandes et aux petites entreprises.

Bibliographie

  • Le datawarehouse – Guide de conduite de projet, éditions EYROLLES.
  • Deploying Business Intelligence with Microsoft SQL Server 2005, éditions McGraw-Hill/Osborne Media (en Anglais).