Decouverte dAnalysis Services - Tutoriel

Microsoft France Division DPE

Table des matires

Prsentation ...............................................................................................................................................................2 Objectifs......................................................................................................................................................................2 Atelier 1: Cration dun base Analysis Services ..........................................................................................................3 Exercice 1 Cration du projet de base de donnes Analysis Services ....................................................................3 Exercice 2 Cration dun source de donnes ......................................................................................................5 Exercice 3 Cration de la Vue de source de donnes ..........................................................................................8 Exercice 4 Mise en place dune requte nomme ........................................................................................... 10 Exercice 5 Mise en place dune relation .......................................................................................................... 12 Atelier 2 : Mise en place de la dimension produit................................................................................................... 13 Exercice 1 : Cration de la dimension Produit avec lassistant dimension ......................................................... 13 Exercice 2 : Modification de la dimension Produit avec le concepteur de dimension ....................................... 17 Atelier 3 ................................................................................................................................................................... 19 Exercice 1 : Cration du cube Ventes avec lassistant cube ................................................................................ 19 Exercice 2 : Modification du cube Ventes avec le concepteur de cube .............................................................. 22 Ressources ............................................................................................................................................................... 25

Prsentation
Microsoft SQL server 2008/2008 R2 Analysis Services (SSAS) fournit des fonctions OLAP (Online Analytical Processing) et des fonctions de Data Mining (exploration de donnes) pour la mise en place dapplications dcisionnelles. Analysis Services prend en charge OLAP et permet de concevoir, de crer et de grer des structures multidimensionnelles contenant des donnes agrges ayant pour source des bases de donnes relationnelles. Il est possible denrichir le contenu des cubes en ajoutant des calculs dans le langage ddi MDX. Ces calculs profiteront de cette structure multidimensionnelle, permettant ainsi de rpondre des problmatiques mtiers courantes beaucoup plus rapidement quon ne laurait fait en recourant des requtes complexes en SQL . Par ailleurs, Analysis Services fournit dautres fonctionnalits avancs de design de cube parmi lesquelles les KPI (indicateurs de performance cl), les actions, les perspectives ou les traductions. Dans ce tutoriel de dcouverte nous allons nous intresser la cration dune base OLAP, la conception de dimension et la mise en place dun cube simple. Lexemple utilis est celui de lentreprise fictive EchoPilote et concerne des donnes de ventes de vtements au dtail via un rseau de succursales. Des donnes dobjectif de vente sont aussi dfinies lanne au niveau des commerciaux.Objectifs Ce tutoriel peut la fois tre ralis avec SQL server 2008/2008 R2 et SQL server 2008/2008 R2 R2. Dans ce tutoriel, vous apprendrez : Utiliser lenvironnement Business Intelligence Development Studio (BIDS). Crer une base Analysis Services et dfinir sa source de donnes. Crer et modifier une dimension et un cube simple.

Pour complter ce tutoriel vous devez avoir : Microsoft Windows XP ou suprieur, ou Windows Server 2003 ou suprieur Microsoft SQL server 2008/2008 R2 Developper, Standard ou Enterprise Edition (SP1 ou suprieur rcommand) et avoir install au minimum les composants suivant : o o o Database Engine (Moteur relationnel) Analysis Services Outils dadministration (Management Studio) et de dveloppement (BIDS)

Tlecharger les fichiers daccompagnement de ce coach

La source de donnes de la base OLAP est un base relationnelle au format SQL server 2008/2008 R2. Avant de commencer les ateliers, vous devez restaurer de celle-ci en executant le le script SQL Restore_EchoPilote.sql prsent de le rpertoire Source des fichiers daccomgnement. Pour cela utilisez la console dadminstration SQL Server Management Studio en vous connectant sur votre instance de Moteur de base de donnes relationnelle, ouvrez le fichier de script, mettez jour le chemin phyisque vers le fichier de sauvergarde pour que ce chemin corresponde lemplacement dans lequel vous lavez copi et executez-le.

Atelier 1: Cration dune base Analysis Services

Pour crer une base de donnes Analysis Services vous allez utiliser Business Intelligence Development Studio (BIDS). BIDS est le nom utilis pour nommer lenvironnement de dveloppement Visual Studio lorsque lon dveloppe des projets utilisant les fonctionnalits de Businness Intelligence de la suite SQL Server (Integration Services, Analysis Services, Reporting Services).

Exercice 1 Cration du projet de base de donnes Analysis Services

1. Rendez-vous dans le menu Dmarrer / Tous les programmes / Microsoft SQL server 2008/2008 R2 puis lancez SQL Server Business Intelligence Development Studio.

2. Aprs le lancement de Visual Studio vous pouvez crer votre projet de base de donnes Analysis Services. Pour cela, allez dans le menu Fichier / Nouveau / Projet ou utilisez la raccourci Ctrl + Maj + N

Vous obtenez :

1) Type de projets : choisissez Projet Business Intelligence 2) Modles : Slectionnez Projet Analysis Services 3) Nom : Saisissez le nom que vous allez donner au projet de base de donnes, ici EchoPilote 3. Dans lexplorateur de solution qui est prsent droite de la fentre de BIDS, vous vous obtenez la structure suivante pour votre projet EchoPilote :

: Si lexplorateur de solution nest pas affich, cliquez sur le bouton

de la barre doutil.

Exercice 2 Cration dune source de donnes

Pour alimenter une base de donnes Analysis Services, il est ncessaire de dfinir une source de donnes relationnelle. Vous allez donc crer et dfinir les proprits de connexion de celle-ci. 1. Vous allez lancer lassistant Source de donnes, pour cela, rendez-vous dans lexplorateur de solution. Faites un click-droit sur le rpertoire Source de donnes puis Nouvelle source de donnes

2. Vous allez crer et configurer les proprits de la connexion la base de donnes relationnelle qui va servir de source de donnes, pour cela, cliquez sur le bouton Nouveau

3. Vous obtenez le Gestionnaire de connexions ci-dessous :

Renseignez alors les proprits suivantes : 1) Fournisseur : Conserver le choix par dfaut 2) Nom du serveur : Saisissez le nom de votre instance de moteur de base de donnes relationnelle SQL Server. : Si votre instance de SQL Server est une instance par dfaut vous pouvez saisir le nom de la machine, ou utiliser un des alias prdfini parmi : localhost , (local) ou . (un point) Si vous utilisez une instance nomme, faite suivre le nom de la machine ou dun de ses alias par \nominstance comme dans lexemple ci-dessus pour linstance nomme SQL2008 qui est .\SQL2008 mais que lon pourrait aussi crire localhost\SQL2008. 3) Slectionnez la base de donnes SQL EchoPilote. 4) Tester la connexion pour vous assurer que vous avez correctement configur celle-ci, puis cliquez sur OK pour revenir lAssistant source de donnes. 4. La connexion que vous venez de crer est slectionne, cliquer sur Suivant. 5. A ltape Informations demprunt didentit choisissez Hriter

6. A la fin de lassistant vous pouvez nommer votre source de donnes et cliquer sur Terminer :

Exercice 3 Cration de la Vue de source de donnes

Une fois la source de donnes cre, vous allez pouvoir ajouter la vue de source de donnes, qui est une couche dabstraction entre la base relationnelle et le contenu de la base OLAP. Elle va ainsi vous permettre dajouter une rfrence vers les tables et les vues prsentes dans la base source, sans avoir modifier le contenu de cette dernire. De plus si le contenu des tables et vues de la base source ne correspond pas exactement vos besoins, vous aurez aussi la possibilit de dfinir dans le langage SQL, des calculs nomms sur vos tables, voire des requtes nommes, vritables quivalent de vues SQL. Vous pourrez ensuite les utiliser pour construire vos objets OLAP (dimensions et cubes). 1. Vous allez lancer lassistant de vue de source de donnes, pour cela, rendez-vous dans lexplorateur de solution. Faites un click-droit sur le rpertoire Vues des sources de donnes puis Nouvelle vue des source de donnes

2. La source de donnes que vous avez cre lors de ltape prcdente est slectionne, cliquez sur Suivant

3. Vous allez ajouter toutes les tables disponibles dans la base SQL EchoPilote :

Cliquez sur le bouton portant le symbole >> puis sur Suivant. 4. A la fin de lassistant vous pouvez nommer votre Vue de source de donnes et cliquer sur Terminer

Le concepteur de Vue de source de donnes est ouvert et vous obtenez :

Enregistrez votre travail avant de passer lexercice suivant.

Exercice 4 Mise en place dune requte nomme

La table DimDate ne contient pas tous les champs ncessaires la cration dune dimension Date. Vous allez donc la remplacer par une requte nomme qui portera le mme nom que la table mais permettra dajouter des champs supplmentaires dans la vue de source de donnes. 1. Dans le diagramme du concepteur, faites un click-droit sur len-tte de la table DimDate puis Remplacer la table / Par une nouvelle requte nomme

10

2. Dans lditeur de requte nomme, copier le code SQL ci-dessous dans le volet SQL en bas de la fentre

SELECT DateID, [Date], CAST(DAY([Date]) AS VARCHAR(2)) + ' ' + CASE WHEN LEN(MoisDesc) > 4 THEN LEFT(LOWER(MoisDesc), 3) + '.' ELSE LOWER(MoisDesc) END + ' ' + RIGHT(CAST(Annee AS CHAR(4)), 2) AS DateDesc, Annee * 100 + DATEPART(mm, [Date]) AS MoisID, CASE WHEN LEN(MoisDesc) > 4 THEN LEFT(MoisDesc, 3) + '.' ELSE MoisDesc END + ' ' + RIGHT(CAST(Annee AS CHAR(4)), 2) AS AnneeMoisDesc, Annee * 10 + DATEPART(qq, [Date]) AS TrimestreID, 'T' + CAST(DATEPART(qq, [Date]) AS char(1)) + '-' + CAST(Annee AS CHAR(4)) AS TrimestreDesc, Annee, MoisDesc, MoisNum FROM dbo.DimDate

3. Confirmez en cliquant sur OK. Vous pouvez constater que la table t remplace par la requte nomme (notez le changement dicne), mais bien conserve la relation avec la tale FaitVenteDetail.

11

Exercice 5 Mise en place dune relation

Lorsque quelles ne sont pas prsentent dans la base source, la vue de source de donnes permet aussi de dfinir des relations entre les tables ou requtes nommes. La table de fait FaitObjectif contient des donnes dfinies un niveau de granularit lanne. Vous allez crer la relation des deux objets FaitObjectif et DimDate en vous basant sur leur champ anne respectif. 1. Dans la fentre du concepteur de vue de source de donnes, faites un click-droit sur la colonne Annee de la table FaitObjectif puis Nouvelle relation

2. Dans la fentre Spcifier la relation qui saffiche, slectionnez DimDate dans la liste Table de destination (cl primaire), vous verrez que le champ Annee est renseign dans la liste Colonnes de destination.

3. Cliquez sur OK pour confirmer, puis enregistrez la vue de source de donnes.

12

Atelier 2 : Mise en place de la dimension produit

Exercice 1 : Cration de la dimension Produit avec lassistant dimension
Vous allez crer la dimension Produit de votre base OLAP. Pour cela, vous allez dabord utiliser lAssistant dimension, pour ensuite la modifier avec le concepteur de dimension. Vous pouvez poursuivre avec le projet que vous avez cr dans lAtelier 1 ou bien ouvrir celui prsent dans le sous-rpertoire Dmarrage du rpertoire Atelier 2, en double cliquant sur le fichier EchoPilote.sln 1. Dans lexplorateur de solutions, faites un click-droit sur le rpertoire Dimensions / Nouvelle dimension Si lcran de prsentation de lassistant saffiche vous pouvez cocher la case Ne plus afficher cette page prsente en bas gauche et cliquez sur Suivant.

2. A ltape Slectionner la mthode de cration, conservez loption Utiliser une table existante prslectionne par dfaut :

13

3. A ltape suivante, slectionner la table DimProduit dans la liste Table principale. La colonne ProduitID est alors slectionne par dfaut dans la liste des colonnes cls car cest la cl primaire de la table. Elle servira de colonne cl pour lattribut cl qui constitue lattribut principal de la dimension. Pour dfinir la colonne qui servira de source au libell de cet attribut, choisissez ProduitDesc dans la liste Colonne de nom, puis cliquez sur Suivant.

14

4. Pour pouvoir ajouter des attributs supplmentaires la dimension Produit, cochez les cases pour les champs Gamme desc et Type Produit Desc et renommez-les en Gamme et Type Produit, cliquez sur Suivant.

15

5. A la dernire tape de lassistant, nommez votre dimension Produit et cliquez sur Terminer.

16

Exercice 2 : Modification de la dimension Produit avec le concepteur de dimension

Vous vous retrouvez dans le concepteur de dimension. Grce celui-ci, vous allez modifier manuellement la dimension Produit. 1. Renommez lattribut Produit ID en Produit

Vous allez crer une hirarchie plusieurs niveaux aussi appele Hirarchie utilisateur . Pour cela vous allez utiliser les attributs Produit, Gamme et Type de produit. 2. Faites un click droit sur lattribut Type de Produit puis Dmarrer une nouvelle hirarchie.

La nouvelle hirarchie apparait dans le volet Hirarchies au centre du Concepteur de dimension.

Renommez cette hirarchie en Nomenclature Produit 3. Pour ajouter ensuite les autres 2 niveaux, faites un glisser-dplacer de lattribut Gamme sur la zone <nouveau niveau> prsente sous le niveau Type Produit, puis faites de mme avec lattribut Produit sous le niveau Gamme.

17

Vous obtenez alors la hirarchie 3 niveaux ci-dessous :

Vous remarquez quun icne davertissement est prsent dans le coin suprieur gauche de la hirarchie. Si vous passez le curseur au-dessus de celui-ci, vous voyez le message suivant apparaitre dans une bulle daide : Les relations d'attributs n'existent pas sur un ou plusieurs niveaux de cette hirarchie. Il peut s'ensuivre une diminution des performances de la requte. 4. Pour rsoudre ce problme rendez-vous dans longlet Relations dattributs. Vous constatez que lattribut Produit est directement reli aux 2 autres. Pour optimiser la conception de votre hirarchie il faut que les attributs utiliss dans celle-ci soient relis de proche en proche cest--dire que les niveaux enfants soient relis directement leur parent. Ainsi Produit doit tre reli Gamme et Gamme Type Produit pour reconstituer un chainage correct des attributs et permettre Analysis Services doptimiser par la suite les temps de calculs (traitement et requte). Pour dplacer la relation entre Produit Gamme vers Gamme Type Produit, faites glisser Gamme sur Type Produit comme indiqu ci-dessous :

Vous pouvez retourner dans longlet Structure de la dimension et constater que lavertissement sur la hirarchie Nomenclature produit a disparu.

Enregistrez votre travail et fermer BIDS avant de passer lAtelier 3.

18

Atelier 3
Dans cet Atelier, Vous allez crer le cube Ventes qui utilisera la dimension Produit, mais aussi plusieurs autres dimensions ajoutes dans le projet de base Analysis Services EchoPilote. Rendez-vous dans le rpertoire Atelier 3 puis dans le sous-rpertoire Dmarrage et double-cliquez sur le fichier EchoPilote.sln pour ouvrir un projet, qui en plus de Produit, contient les dimensions Canal (les canaux de vente), Date, Client et Commercial (les succursales et les commerciaux de lentreprise EchoPilote).

Exercice 1 : Cration du cube Ventes avec lassistant cube

1. Dans lexplorateur de solutions faites un click-droit sur le rpertoire Cubes puis Nouveau Cube

2. Dans le premier cran de lAssistant Cube conserver le choix par dfaut et cliquez sur Suivant

3. A ltape suivante, cliquez sur le bouton Suggrer, vous constatez que les 2 tables de faits : FaitObjectif et FaitVentes sont automatiquement coches dans la liste de table. Cela est d la structure de votre base SQL et relations rcupres depuis celle-ci dans la Vue de source de donnes. Cliquez sur Suivant.

19

4. Dans lcran choix des mesures, dcochez uniquement Fait Objectif Nombre et cliquez sur Suivant

20

5. Conservez toutes les dimensions qui sont selectionnes et cliquez sur Suivant

6. A la dernire tape cliquez sur Terminer

21

Exercice 2 : Modification du cube Ventes avec le concepteur de cube

1. Une fois dans le Concepteur de Cube dans longlet Structure de cube, rendez-vous dans le Volet Mesures. Renommez les groupes de mesures Fait Objectif et Fait Vente Detail en Objectif et Vente Detail et la mesure Fait Vente Detail Nombre en Nb Ventes.

2. Vous allez ensuite mettre en place la relation entre la dimension Date et le groupe de mesure Objectif. Pour cela, rendez-vous dans longlet Utilisation de la dimension du Concepteur de Cube. Dans la zone situe lintersection entre le groupe de mesure Objectif et la dimension Date, cliquez sur le bouton pour lancer lditeur de relation

22

3. Dfinissez la relation en slectionnant successivement les proprits suivantes :

1) Type de relation : Normale 2) Attribut de granularit : Annee 3) Colonnes de groupe de mesures : Annee Cliquez sur OK pour confirmer. 4. Vous allez dployer votre base EchoPilote et son contenu (Source de donnes, dimensions, cubes) sur le serveur Analysis Services. Dans lexplorateur de solution, faites un click-droit la racine du projet EchoPilote puis Dployer.

23

BIDS va alors lancer le dploiement de la base en envoyant un script de cration ou mise jour de la structure de celle-ci vers linstance Analysis Services et il enchainera ensuite automatique un traitement (= une alimentation en donnes) de la base OLAP. 5. Vous pouvez donc tester votre cubes Ventes en vous rendant dans le dernier onglet du concepteur de cube, longlet Parcourir et en slectionnant les lments du cubes et les disposant la manire dun tableau crois dynamique tel que vous pourriez le faire dans le tableau Excel de la suite Office.

Voil qui conclut votre coach de Dcouverte dAnalysis Services 2008/2008 R2. Pour plus dinformation consultez les sites rfrencs dans la section Ressources.

24

Ressources
Vous trouverez de nombreuses ressources sur Internet, mais voici dj un bon point de dpart Site Microsoft SQL Server franais : http://www.microsoft.com/france/serveur/sql Site TechNet de SQL Server : http://technet.microsoft.com/fr-fr/sqlserver/default.aspx Site portail sur SSAS : http://www.ssas-info.com/ Blog de lEcho Pilote: http://blogs.technet.com/echopilote/ Add-in gratuit pour BIDS nomm BIDS Helper sur le site codeplex : http://bidshelper.codeplex.com/ Centre de dveloppement SQL Server : http://msdn.microsoft.com/fr-fr/sqlserver/default.aspx

25