[SSAS] Cubes OLAP : implémentation avec SSDT

Ce billet s’intéresse à la création d’un cube OLAP (OnLine Analytical Processing) via l’assistant (wizard) de SQL Server Data Tools (SSDT), avec donc un focus sur au moins la version 2012 de SQL Server.

Dans le cas d’une utilisation de BIDS (Business Intelligence Development Studio, de SQL Server 2005 à SQL Server 2008 R2), le mode opératoire est sensiblement le même, bien que l’interface diffère.

Modus operandi

Le datawarehouse AdventureWorksDW sera utilisé comme exemple, avec DimDate, DimProduit, DimCustomers et FactInternetSales comme tables permettant de déterminer, surtout, les mesures associées à la vente de produits sur Internet par période.

Avant de commencer…

Ouverture et création d’un nouveau projet SSAS :


Au sein de la boîte modale qui s’ouvrira, spécifions les informations (choix du modèle, nom du projet, chemin d’accès,…) de notre futur projet comme suit :


Un clic sur OK aura pour effet de créer la solution (ou projet) SSAS_Cube visible dans l’explorateur de solutions :


Création d’une source de données

La création d’une source de données (ou l’importation d’une existante) est nécessaire avant toute création du cube. Pour ce faire :

  • Lancement de l’assistant de création d’une source de données.


Une boîte de dialogue modale de bienvenue (pouvant être ignorée à l’avenir) s’ouvrira :


La description fournie par ladite boîte est assez limpide pour comprendre la finalité de la source de données qui sera créée.

  • Définition des informations de connexion.


La source de données peut être créée soit à partir d’une connexion existante ou nouvelle, soit en se basant sur un objet existant.

Nous allons opter pour la création d’une source de données à partir d’une nouvelle connexion (clic sur le bouton New) :


Le login SQL mac-rog visible ci-dessus a été arbitrairement créé avant. Vous pouvez utiliser un autre login SQL existant ou le mode d’authentification Windows (mode par défaut).

La base de données choisie est le datawarehouse
AdventureWorksDW sur lequel nous travaillerons.

Un clic sur Test Connection permet de confirmer le bon fonctionnement (ou non) de la connexion :


Et sitôt le tout validé, nous obtiendrons l’interface suivante :


Cliquez sur Next >.

  • Définition des options d’emprunt d’identité.


Cette étape permet de spécifier les informations de connexion qu’utilisera SSAS pour se connecter à la source de données, que ce soit lorsque l’on travaillera avec la visionneuse de la source de données (Data Source Viewer) ou lors du traitement du cube (via l’extraction de données issue de ladite source).

Dans notre cas, le compte de service de SSAS a été choisi. Cependant, les autres options peuvent être choisies sachant que :

  • L’utilisation d’un username et d’un mot-de-passe spécifique peut être idéal quand un très (trop ?) grand nombre de sources de données est utilisé et que l’on souhaite leur attribuer des informations de connexion dédiées.
  • L’utilisation des informations de connexion de l’utilisateur courant nécessite que ce dernier possède au moins un accès en lecture à la source de données. Cette option n’est pas recommandée le cas échéant.
  • Les informations d’héritage permettent de forcer SSAS à utiliser les informations de connexion de l’objet parent, qui n’est autre que la base de données AdventureWorksDWH, dans notre cas. Cela assure une gestion centralisée des informations d’emprunt d’identité pour la source de données associée à ladite base, ainsi que pour celles l’utilisant. Pour définir le nom et le mot-de-passe de l’utilisateur à utiliser, il suffit d’aller dans les propriétés dans le base de données SSAS au sein de SQL Server Management Studio (SSMS) – DataSourceImpersonationInfo, pour être exact. Plus de détails ici :
    http://technet.microsoft.com/en-us/library/ms365384.aspx
    .
Pour de plus amples informations générales sur l’emprunt d’identité avec SSAS en mode multidimensionnel : http://technet.microsoft.com/en-us/library/ms187597.aspx.

  • Validation de la configuration de la source de données.


Sitôt le tout validé, la source de données fraîchement créée peut être visible dans l’explorateur de solutions avec l’extension .ds :


Définition d’une vue de source de données

  • Lancement de l’assistant de création d’une vue de source de données.


Une boîte de dialogue de bienvenue s’ouvrira avec la description de l’intérêt d’une vue de source de données :


  • Sélection de la source de données préalablement créée.


  • Sélection des objets (dimensions et faits) destinés à construire le futur cube.


  • Validation de la configuration de la vue de la source de données.


La vue de source de données créée est visible au sein de l’explorateur d’objets avec l’extension .dsv :


Et le schéma relationnel associé à la vue de source de données est également disponible au sein de la zone de design de ladite vue :


Construction du cube

  • Lancement de l’assistant de création de cube.


Une boîte de dialogue modale de bienvenue (pouvant être ignorée) décrivant brièvement les fonctionnalités de l’assistant de création du cube sera lancée :


  • Sélection de la méthode de construction.


A cette étape, nous optons pour Use existing tables, ce qui permettra d’utiliser les objets de la vue de source de données créée plus haut.

  • Sélection de la vue de source de données et du groupe de tables de mesures (ou de faits) à partir de la vue de source de données préalablement créée.


Le bouton Suggest permet de laisser à l’assistant le soin de choisir quelles tables pourraient être utilisées dans les groupes de mesures.

  • Sélection des mesures.


Cette étape permet, pour chaque table de faits/mesures sélectionnée (dans notre cas, seulement FactInternetSales) quelles sont les colonnes devant être prises en compte dans le futur cube.

  • Sélection des dimensions.


  • Validation de la configuration du cube à créer.


Un clic sur Finish permet de valider la construction du cube, visible au sein de l’explorateur d’objets avec ses dimensions :


Comme on peut le noter, tout cube a pour extension .cube et toute table de faits ou de dimensions, .dim. Et il est possible de les nommer autrement (en éliminant les espaces ou le préfixe Dim ou Fact, par exemple) :


Et dans la zone de design de SSDT (automatiquement lancée après la construction du cube par l’assistant, sinon accessible en effectuant un clic-droit, puis View Designer sur le cube, au sein de l’explorateur de solutions) :


Edition de dimensions

Ajout d’attributs aux dimensions

Bien que l’assistant de création de cubes permette de sélectionner des dimensions à incorporer, il n’ajoute pas leurs attributs.

Pour ajouter des attributs, il suffit d’effectuer un double-clic sur chaque dimension concernée, au sein de l’explorateur de solutions, puis, dans la zone de design qui s’ouvrira, déplacer les attributs idoines de Data Source View vers Attributes. Dans notre exemple, prenons DimProduct, et ajoutons les attributs EnglishProductName et Color :


Faisons de même pour les autres dimensions, de façon à ce que les attributs sélectionnés soient les suivants :

  • Pour DimDate :


  • Pour DimCustomer :


A l’instar des dimensions, les attributs peuvent bien sûr être renommés. Dans notre cas, cela a été effectué pour ProductName :




Définition d’une hiérarchie d’attributs

Les hiérarchies permettent de faciliter la navigation de données en passant de celles résumées à celles détaillées., ce qui est bénéfique au niveau des performances.

Dans notre contexte, DimDate peut bénéficier d’une hiérarchie, avec CalendarYear comme niveau le plus élevé de façon à permettre à SSAS de calculer les ventes de produits par année, puis de stocker leurs résultats de façon permanente de sorte qu’un recalcul annuel des ventes ne soit plus une absolue nécessité.

Et pour ce faire, si ce n’est déjà fait, double-cliquons sur ladite dimension au sein de l’explorateur de solutions afin d’accéder à sa zone de design, puis faisons glisser les attributs idoines dans la section Hierarchies comme ci-dessus (l’ordre est non-négligeable, sachant que le nombre de points défini le niveau, où plus il y a de points, plus le niveau est bas) :


On suppose, bien sûr, que les attributs ont été ajoutés dans la section Ajout d’attributs aux dimensions. Et si vous ambitionnez de créer plusieurs hiérarchies différentes, il est recommandé de renommer proprement Hierarchy (dans notre cas DateHierarchy).

Notons que dans l’Error List de SSDT (ou BIDS, selon), un avertissement sera levé : « Warning Dimension [DimDate] : Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies ». Cela signifie que chaque attribut membre d’une hiérarchie ne doit pas être affiché seul.

Cet avertissement peut être ignoré si, au niveau métier, il ne s’avère pas problématique. Toutefois, en cas de prise en compte, la valeur de la propriété AttributeHierarchyVisible d’un attribut donné (Month, par exemple) peut être paramétrée à False :

Définition d’une relation d’attributs

Il n’est pas inhabituel d’avoir pour avertissement, dans l’Error List, le message suivant : « Warning – Hierarchy [DimDate].[DateHierarchy] : Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance ». Les relations d’attributs permettent à SSAS d’améliorer les performances des requêtes, l’optimisation du stockage pour une dimension,… Il est donc recommandé de plancher sur chaque avertissement en rapport avec les relations d’attributs.

Dans notre cas précis, cela concerne DimDate, et la solution est d’établir une relation entre les attributs hiérarchisés (du niveau le plus bas vers le niveau le plus élevé), comme suit (au sein de l’onglet Attribute Relationships de la zone de design de la dimension) :


Cela aura notamment pour effet d’éliminer l’icône d’avertissement de la table des hiérarchies :


Déploiement, traitement, visualisation des données analytiques

    Déploiement du cube

Sitôt le cube créé, sa structure a besoin d’être déployée sur une instance SSAS pour ensuite être traitée (création d’agrégats pour le requêtage rapide,…).

Le déploiement est simple :

  • Accès au menu Project pour aller dans les propriétés générales de notre solution :


  • Dans la section Deployment de la boîte de dialogue qui s’ouvrira, vérification/validation du nom du serveur :


  • Au sein du menu Build, sélectionner Deploy SSAS_Cube (où SSAS_Cube est, pour rappel, le nom de notre solution) :


  • Appréciation des résultats du déploiement :


Traitement du cube

Si vous souhaitez traiter le cube (suite à une mise-à-jour au niveau de la base, par exemple), vous pouvez faire un clic-droit sur AdventureWorksDW.cube (au sein de l’explorateur d’objets), pour ensuite sélectionner Process… :


Cela lancera une boîte modale de confirmation de traitement (avec possibilité d’effectuer une analyse d’impact) :


Si tout va bien, après lancement du traitement du cube (Run), le résultat sera le suivant :


Notez que si vous êtes un flemmard, vous pouvez presser sur F5 pour lancer le débogage qui réalisera le déploiement et traitement du cube OLAP.

Exploration du cube

Notons que suite à l’abandon d’OWC (Office Web Control) sur lequel est basé le Cube Browser de BIDS (2005 et 2008 (R2)), ce dernier n’est plus supporté à partir de SQL Server 2012, et donc SSDT. L’exploration du cube se fait essentiellement via d’autres outils (Excel, SSRS,…) ou une version très basique du browser (i.e., dépourvue d’options comme Drop Row Fields here, Drop column Fields here,… ce qui limite pas mal de choses)
à la sauce SSDT (voir ci-dessous).

Pour visualiser les données déployées :


Comme l’indique la figure ci-dessus, il suffit d’effectuer un clic-droit sur le cube – au sein de l’explorateur de solutions – puis de sélectionner
les sous-menus souhaités.

Un clic sur Browse ouvrira une zone de design permettant de naviguer au sein du cube :


Les dimensions Due Date (date d’échéance), Order Date (date de commande) et Ship Date (date d’expédition) sont créées automatiquement par l’assistant, à partir de la dimension DimDate et sont produites par le biais de clés étrangères référençant FactInternetSales.

Faites glisser des mesures et des colonnes de dimensions pour l’analyse des données suivant les besoins. Par exemple, le prix unitaire par produit, par période (mois, trimestre, année) :


Une exploration Excel peut faire l’affaire pour bénéficier d’une exploration via PivotTable :


  • Lancement de la feuille Excel.


    Dans la barre d’outils verticale (ou liste) à gauche se trouvent les dimensions et mesures pouvant être ajoutés aux champs de la PivotTable.

  • Exemple d’exploration de données :


Les données analytiques ci-dessous présentent les ventes effectuées par produit, par mois, par trimestre, par année. Et pour chaque période donnée, la quantité vendue.

D’autres possibilités de manipulations dimensionnelles existent (i.e., plus gros consommateurs par période,…).

Pour aller plus loin…

Ce billet a permis d’aborder le minimum syndical concernant la construction d’un cube OLAP avec SSAS. D’autres concepts seront abordés dans des billets dédiés (perspectives, KPIs,…).

Gardez un œil ici.

Publicités

Un commentaire sur “[SSAS] Cubes OLAP : implémentation avec SSDT

  1. Hamid dit :

    Bonjour,

    Très bon tutorial cela m’a aidé à comprendre le concept OLAP.
    Bonne continuation.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s