SSRS - Forage dans un cube SSAS - drillthrough

Dans ce blogue, nous allons explorer les données avec un niveau de détails plus fin (forage). En plus, nous allons utiliser une hiérarchie pour faire le forage et obtenir le détail avec les enfants de cette hiérarchie.

Le rapport est construit à partir de SSRS (SQL Server Reporting Services) avec comme source de données un cube SSAS (SQL Server Analysis Services).

Les données ont été extraites d'un progiciel de gestion intégré (PGI ou ERP en anglais) dans le domaine financier combiné avec les données budgétaires. Nous avons construit un cube à partir de la table de faits qui contient plus de 8 millions d'enregistrements et peut être analysée selon 12 axes différents au choix de l'utilisateur. 

Les mesures : Le montant de budget initial alloué au début de l'année, visible dans le premier encadré. Le montant engagement, visible dans le deuxième encadré, représente le montant de dépense en attente d'approbation. Le montant réel, dans le troisième encadré, sont les montants dépensés jusqu'à maintenant et enfin, le montant disponible, dans le tout dernier encadré, est le calcul représenté par la soustraction des montants réels et engagements au montant budget initial.

  Montant budget initial - Montant Engagement - Montant Réel = Montant Disponible

 Les dimensions : Les dimensions sont les axes d'analyse que vous choisissez pour explorer de façon dynamique vos mesures. Ici, nous avons choisi d'analyser les mesures selon les dimensions suivantes :

• de l'année fiscale, et du niveau 5 du code de type de budget en paramètres

• des niveaux de l'unité administrative sous forme de hiérarchies en lignes, de l'entité, de la période et mois financier en en-tête de colonnes

Dans l’image ci-dessus nous avons un rapport principal appelé Main liée à un autre nommé Details. Le Main est une matrice croisée dynamique (matrix) qui donne une vue globale des mesures pour le premier et le deuxième niveau de l’unité administrative selon la période financière et le mois. Le Details sera appelé et filtré en contexte de la cellule choisi dans le Main. L’utilisateur peut naviguer entre les deux rapports pour faire des analyses plus approfondies. Ce passage d’un rapport à un autre se fait ici avec un forage sur les mesures.

Après avoir configuré votre « data source », nous allons maintenant créer notre rapport principal. Cette étape nécessite que vous sachiez à l'avance les différents champs, filtres et paramètres que nous utiliserons dans le rapport pour établir le « dataset ». Ces décisions auront un impact sur la façon dont vous pourrez forer

Au niveau du rapport de détail, il faut ajouter un paramètre « paramUA » ce dernier permettra de stocker le UniqueName du niveau précis sélectionné dans le rapport principal.

 

Étant donné qu'on veut un membre particulier de la hiérarchie, il faut créer un membre calculé UniqueName qui contiendra le membre parmi les différents membres de la hiérarchie. Le membre calculé sera créé dans les champs du dataset. Ce champ sera utilisé ultérieurement pour transmettre le niveau (master) sélectionné au rapport Details.

Enfin il reste à mettre en place l’action SSRS de type « Go to report » dans le rapport d’analyse au niveau de la cellule contenant la mesure.

Il suffit alors de sélectionner le rapport de détail et d’effectuer le mapping des paramètres du rapport.

Étant donné que nous voulons aussi les enfants dans la hiérarchie (et pas seulement le niveau sélectionné dans le Main), nous allons modifier la requête MDX pour le rapport détaillé. Dans les propriétés du dataset nous l’éditons comme chaine de caractères en ajoutant le paramètre dans la requête comme le montre l'image ci-dessous.

Nous obtenons par forage le niveau de l'unité administrative désiré (le niveau B-DG Ouest) et ses enfants (niveau C), pour la période financière 2 (mai). Les montants réels correspondent soit x $

Différence entre SSRS, SSIS et Microsoft SSAS?

Microsoft SQL Server est un système de gestion de base de données relationnelles developpé et commercialisé par la société Microsoft.

SQL Server dispose d'un moteur base de données qui est central et permet le stockage et la traitement les données. Grâce à celui ci il y a un contrôle sur les accès et le traitement les transactions pour répondre aux besoins des applications.

Parmi les fonctionnalités qu'offrent SQL Server dans le domaine du BI (Business Intelligence) nous avons

 :  SSIS (SQL Server Integration Services) ,SSRS(SQL Server Reporting Services) et SSAS(SQL Server Analysis Services ).

 SQL Server Integration Services (SSIS)

 Ce Service est l'outil d'ETL (Extract Transform Load ) de Microsoft et permet d'alimenter notre datawarehouse à partir de données provenant de plusieurs sources.Pour cela il faudra commencer d'abord par extraire les données, les transformer puis les sauvegarder dans la base de données. Les données peuvent provenir de différentes sources (fichiers Excel, MySQL ,Oracle etc ...)

Lors de la création d'un projet SSIS nous avons un package qui est créé et celui est un ensemble  d'actions qui va être exécuté dans un certain ordre. Parmi les actions nous avons des taches qui aident à l’établissement de l’entrepôt de données qui peuvent être des taches de transfert de base de données des taches de script etc ...

SQL Server Reporting Services (SSRS)

 SQL Server Reporting Services permet  la création ,le déploiement et la gestion de rapports à partir de différentes sources de données. Avec SSRS nous pouvons avoir différents types de rapports qui peuvent être entre autre tabulaire, graphique ou matriciels. 

Des connexions peuvent être faites à partir de SSRS et d'autres outils reporting au cube déployé sur le serveur. SSRS peut baser son dataset sur un cube ou une base de données comme Oracle

Ces rapports peuvent être exportés vers divers formats de fichiers  par exemple pdf ou fichiers MS-Excel .

 Les rapports peuvent être aussi partagés par le biais d'une connexion internet internet s'ils sont déployés sur le serveur ou aussi par l’intermédiaire de Sharepoint.

 SQL Server Analysis Services (SSAS)

SQL Server analysis Services  va nous permettre la conception, la création et la gestion  des structures multidimensionnelles, les cubes. Les cubes contenant des données agrégées à partir d’autres sources de données, comme les bases de données relationnelles (schéma étoile), fichier plat ou tout autres sources de données.

SSAS est un moteur d'exploration de données et permet de répondre à des requêtes de consultation de données créées avec les cube et le langage MDX.

Il est possible de définir des rôles de sécurité afin de restreindre l’accès aux données à des comptes et/ou groupes d’utilisateurs Windows identifiés.

SSRS deux niveaux d'Agrégations

Créer des rapports avec deux niveaux d’agrégation n'est pas toujours évident. Voici un exemple de rapport graphique conçu à l'aide de "SQL Server Reporting Services" vous démontrant cette possibilité.

Les données ont été extraites d'un progiciel de gestion intégré (PGI ou ERP en anglais) dans le domaine financier combiné avec les données budgétaires. La table de faits contient plus de 8 millions d'enregistrements et peut être analysée selon 12 axes différents aux choix de l'utilisateur.  

Les mesures : Vous pouvez observer dans le rapport graphique ci-dessous, les deux mesures agencées en deux colonnes de couleurs distinctes. La colonne bleue étant le montant de budget initial alloué au début de l'année. Le pourcentage, en orange, est la proportion des montants budget initiaux de chaque niveau sur le montant total des budgets initiaux.

• Montant budget initial/Montant Total du budget Initial = Pourcentage du Budget Total Initial

Les dimensions : Les dimensions sont les axes d'analyse que vous choisissez pour explorer de façon dynamique vos mesures. Ici nous avons choisi d'analyser les mesures selon les dimensions suivantes :

• de l'année financière, du niveau 5 du code de type de budget en paramètre
• de l'unité administrative niveau 3 en lignes
  

 

Pour comprendre le calcul, imaginez que le rapport comporterait une colonne supplémentaire dans laquelle se retrouverait, sur chaque enregistrement, la valeur constante du total général du montant Budget Initial. Ensuite vous appliquez le calcul de proportion pour chaque enregistrement. Vous obtiendrez ceci :

Dans cette figure, vous pouvez observer que l'utilisateur a sélectionné une année financière, un code type de budget niveau 5 pour filtrer les informations. Le résultat obtenu est : demander à SSRS d'effectuer les calculs de proportions nécessaires sur la somme des montants budgétaires initiaux pour chacune des unités administratives pour l'année financière 2010-2011. Mais comment utiliser les champs calculés pour effectuer ce calcul?

L'utilisation d'un champ calculé représentant le montant total du budget initial est nécessaire. Par la suite, le calcul de la proportion s'effectue pour chacune des lignes en fonction de l'unité administrative choisie. Il faut définir un nouveau champ calculé Pourcentage, où chaque ligne des montants initiaux pour l'unité administrative se comptabilise en fonction des filtres utilisés et du montant du budget total initial.

Montant du budget Initial/Montant Total Budget Initial = Pourcentage du budget total initial

Que l'utilisateur sélectionne une ou plusieurs autres années, périodes, code de type ou encore une ou plusieurs autres unités administratives, le tableau croisé dynamique s'ajustera et effectuera ses calculs en fonction des filtres sélectionnés.

Si vous souhaitez en apprendre plus, nous avons des formations pour vous. Vous pouvez visiter notre site : www.panoramatechnologies.com

Panorama Technologies
Spécialiste en BI et tableau de bord

Voir aussi :

PowerPivot - Calcul avec deux niveaux d'agrégation - la puissance de DAX

SSAS - Hiérarchies - SQL Server

Lorsque vous travaillez à élaborer un rapport et que votre source de données provient d'un système relationnel, il se peut que vous ayez besoin de concevoir des hiérarchies. SSAS (SQL Server Analysis Services) à partir de cube (MOLAP) offre la possibilité de créer des hiérarchies.

La première pensée, et sans doute la plus importante est qu'il faut s'assurer avant tout que les données peuvent se prêter à une hiérarchie.
 
Exemple : Une hiérarchie qui nous vient tout de suite à l'idée est l'année, le trimestre, le mois et le jour.

Pour créer une hiérarchie, il faut glisser l'attribut qui représente le plus haut niveau de la hiérarchie dans le volet de hiérarchie. Pour la première hiérarchie, commençons par l'attribut NOM UA NIV 1. Lorsque vous glissez l'attribut dans le volet des hiérarchies, la hiérarchie est automatiquement créée, comme le montre la figure ci-dessous.

Pour ajouter le niveau suivant de la hiérarchie, glissez l'attribut dans le volet « Hiérarchies » et déposez-le dans la boîte de hiérarchie. Puis faire la même chose pour les autres niveaux. Une fois que vous avez ajouté tous les niveaux de la hiérarchie, votre volet « Hiérarchie » doit ressembler à l'image suivante :

Après avoir créé la hiérarchie, vous pouvez modifier ses propriétés. Par exemple, comme vous pouvez le voir sur la figure ci-dessus, j'ai changé le nom en Nom Niv UA Hierarchy. Notez, cependant, que la hiérarchie comprend un message d'avertissement. Celui-ci vous est révélateur que les relations d'attributs n'ont pas été définies entre les niveaux de la hiérarchie, nous allons regarder comment le faire dans l'onglet « Attribute Relationships ».

Notez dans l'image ci-dessus qu'il existe une relation entre l'attribut clé de l’unité administrative et chacun des six attributs dans la hiérarchie. Cependant, pour améliorer les performances, les relations doivent être définies entre les paires d'attributs suivants :

     SK UNITE ADMINISTRATIVE  et Nom UA NIV 6
     Nom UA NIV 6 et Nom UA NIV 5
     Nom UA NIV 5 et Nom UA NIV 4
     etc.

Ces relations peuvent être facilement visualisées dans la partie graphique de l'onglet « Attribute Relationships ». Glisser vos attributs, votre volet Relations d'attributs doit maintenant ressembler à la figure ci-dessous.

Nous pouvons afficher les données de la hiérarchie dans l'onglet « Browser »

Pour voir toutes les données dans Analysis Services, ouvrez votre cube puis sélectionnez l'onglet « Browser ». Maintenant, nous pouvons voir toutes les dimensions ainsi que les mesures. Notons que les hiérarchies sont incluses dans l'arborescence de dimension. 

Glissez une mesure par exemple le montant réel ainsi que la hiérarchie crée précédemment comme le montre la figure ci-dessous.

Voici le résultat lorsqu'on utilise la hiérarchie pour construire un rapport dans SSRS :

Si vous souhaitez en apprendre plus, nous avons des formations pour vous. Vous pouvez visiter notre site :

http://www.panoramatechnologies.com

Panorama Technologies
Spécialiste en BI et tableau de bord

Voir aussi les hiérarchies avec Tableau Desktop 8.0
Les hiérarchies avec PowerPivot pour Excel 2010
Hiérarchies avec OBIEE 

SSAS - Utilisation des paramètres

Nous allons aborder dans ce blogue la gestion des paramètres. Nous allons créer un rapport avec SSRS (SQL Server Reporting Services) basé sur un cube SSAS (SQL Server Analysis Services) qui utilise le langage MDX pour lancer des requêtes (MOLAP). 

Les paramètres sont des valeurs dynamiques qui peuvent remplacer des constantes dans les calculs et les filtres. Lorsque bien appliquée, l'utilisation de paramètre aide à la performance des rapports.

Les données ont été extraites d'un progiciel de gestion intégré (PGI ou ERP en anglais) dans le domaine financier combiné avec les données budgétaires. La table de fait contient plus de 8 millions d'enregistrements et peut être analysée selon 12 axes différents aux choix de l'utilisateur.

Notre cube est un ensemble de mesures organisé par dimensions et il est construit à partir des données extraites de sources de données relationnelles.

Nous souhaitons faire une analyse des différents montants selon le nom de l’unité administrative. Le paramètre sera appliqué sur le code type de budget de niveau 5 comme illustre la figure ci-dessous.

Nous pouvons avoir plusieurs paramètres dans le même rapport, et dans notre cas, nous allons ajouter des paramètres pour la période financière et la fréquence mensuelle.

Les mesures : Vous pouvez observer dans le rapport graphique ci-dessous, les quatre mesures agencées en quatre courbes de couleurs distinctes. La courbe orange étant le montant de budget initial alloué au début de l'année. Le montant réel, courbe de couleur violette, représente les montants dépensés jusqu'à maintenant. Le montant engagement, de couleur verte, est le montant de dépense en attente d'approbation et enfin, le montant disponible, courbe en bleue, est le calcul représenté par la soustraction des montants réels et engagements au montant budget initial.

• Montant budget initial - Montant Engagement - Montant Réel = Montant Disponible

Les dimensions : Les dimensions sont les axes d'analyse que vous choisissez pour explorer de façon dynamique vos mesures. Ici nous avons choisi d'analyser les mesures selon les dimensions suivantes :

• Niveau 5 du code de type de budget, fréquence mensuelle et la valeur de l'année financière en paramètres

Notre « dataset » étant prêt nous pouvons faire le rapport en ajoutant un graphique 

Si vous souhaitez en apprendre plus, nous avons des formations pour vous. Vous pouvez visiter notre site :

www.panoramatechnologies.com

Panorama Technologies

Spécialiste en BI et tableau de bord

Voir aussi :
SSRS - Paramètres personnalisés avec SSRS (SQL Server Reporting Service)
PowerPivot
MicroStrategy
OBIEE - Paramètre personnalisé avec Oracle Business Intelligence Enterprise Edition

Matrice de choix d'outil BI Panorama technologies

Comment choisir son outil BI ? Quels sont les critères de bases ? Suite à de nombreuses implantations d'entrepôt de données et de tableau de bord dans diverses technologies et chez plusieurs clients, j'ai dégagé des critères pour le choix d'outils d'intelligence d'affaires. Il y a plusieurs critères de fonctionnalités techniques, mais au-delà il y a des orientations stratégiques qui concernent l'intelligence d'affaires. Avec l'évolution constante des produits informationnels, il y a des décisions et orientations qui auront un impact important sur l'exploitation des données de l'entrepôt de données et des tableaux de bord.

Un des facteurs majeurs qui a changé au cours des dernières années est la démocratisation des outils BI. Maintenant, les utilisateurs n'ont plus besoin d'attendre le département des TI et peuvent s'installer rapidement des produits comme Tableau Software et Microsoft Power Pivot. En quelques jours, ils peuvent obtenir des résultats très intéressants pour leur département.

Il n'y a pas de mauvais produits. Tout dépendant de la situation des clients, Panorama Technologies a implanté autant des solutions de type Microsoft-BI, Oracle OBIEE, Cognos, Tableau Software ou autres chez nos clients. Il y a une offre vaste pour des besoins et des organisations différentes. Le défi consiste à assortir l'offre de produits BI et les besoins des clients.

Pourquoi certains clients ont des succès remarquables ? D'autres des échecs remarquables ? Et ce en utilisant les mêmes technologies. L'outil n'est pas le facteur décisif, c'est l'architecture BI et la façon de l'implanter qui fait la différence.

Décision stratégique 1 - L'architecture MOLAP / ROLAP:

Les produits BI offrent deux choix d'architecture des données le MOLAP ou le ROLAP. Ce choix aura un impact majeur sur le volume, le niveau de détail et la performance des requêtes et les données qui pourront être exploitées.

MOLAP - Cubes : L'avantage du MOLAP ou des cubes est la simplicité avec laquelle l'architecture BI peut être créée. Souvent, l'outil le fera pour vous en créant les dimensions (axes d'analyse). C'est dans cette catégorie que l'on retrouve les produits les plus intéressants visuellement.

Le désavantage de cette approche est le nombre limité de données que certains produits peuvent exploiter en fonction de l'architecture BI choisie. Il y a aussi risque d'augmenter l'effet de SILO, autant par l'architecture des données que par certaines limitations du produit, qu'il apportera au fur et à mesure que les utilisateurs prendront le goût d'augmenter le volume de données et de détails dans la recherche de données (forage).

Certains de ces produits offrent des connexions dites ''natives" sur la base de données comme Oracle ou SQL/Server. Cependant, les données sont exploitées dans leur propre serveur logiciel. Les données sont transférées de la base de données vers le serveur logiciel pour y construire une matrice, un cube ou autre objet de type MOLAP.

et/ou

ROLAP - Schéma étoile : L'avantage est l'exploitation illimitée de volume de données et l'effet intégré qu'il permet pour l'organisation en partageant les dimensions (dimensions conformes). Son désavantage est qu'il est plus difficile à modéliser, car il nécessite une approche intégrée et globale de l'entrepôt de données.

Certaines organisations vont créer d'excellent modèle ROLAP pour l'ensemble de l'organisation. Cependant, ils vont les diviser parce qu'ils ont une architecture BI déficiente. Ceci détruit le travail d'intégration et crée des silos de données qui donneront des réponses différentes pour une même question dans les divers silos.

Décision stratégique 2 - Approche Utilisateur / Programmeur

Tout dépendant de l'organisation, le BI sera plus ou moins décentralisé du département TI vers l'utilisateur. L'avantage de la centralisation au département des TI permet d'obtenir un BI plus corporatif. La décentralisation à l'utilisateur permet de profiter du pouvoir des utilisateurs dans la création du nombre infini de produits informationnels en limitant les coûts TI.

C'est un défi organisationnel de choisir qui seront les intervenants responsables des diverses fonctionnalités de l'entrepôt de données. Habituellement les bases de données, cubes, schémas étoile et l'ETL sont sous la responsabilité des TI. Lorsque l'outil et la force du groupe utilisateur le permettent, la gestion de la couche logique en langage d'affaires, la création de rapports et de tableau de bord peuvent être déléguées aux utilisateurs.

Voici la matrice de choix d'outils BI Panorama Technologies. Cet outil est conçu afin de vous aider à réfléchir à la prise de décision pour le choix d'outils d'exploitation BI et tableau de bord. À titre indicatif, quelques produits ont été positionnés ( OBIEE, Cognos, Microstrategy, Tableau Software et MS-BI avec Power Pivot). Panorama Technologies peut vous faire des formations ou des démonstrations pour ces produits d'exploitation BI.

Matrice choix outils BI Panorama Technologies

Le marché s'est polarisé entre des outils BI de type Microsoft Excel comme Power Pivot, Tableau Software, Cognos Insight et Tibco. Ces outils sont très appréciés des utilisateurs pour leur convivialité, l'exportation des données dans Excel et leur interface souvent spectaculaire. Ils permettent beaucoup d'autonomie avec un  minimum de connaissance BI. Ces solutions sont d'excellents points d'entrée pour le BI. Cependant au fur et à mesure que l'engouement grandit pour la consommation des données des problèmes de performances apparaissent. Tôt ou tard, les organisations utilisant ces produits migrent vers des solutions plus corporatives qui peuvent utiliser les mêmes produits avec une architecture BI corporative.

De l'autre côté, on retrouve les outils BI comme MS-BI, Cognos 10, Microstrategy, Business Objet et OBIEE qui offre des environnements plus robustes et intégrés. Leurs courbes d'apprentissage et leurs coûts sont cependant plus élevés et nécessitent des connaissances BI. Ces produits offrent des performances inégalées pourvu que l'architecture BI soit bien conçue. Les interfaces utilisateurs sont de plus en plus conviviales.

En résumé les utilisateurs peuvent implanter rapidement des solutions BI départementales grâce aux nouveaux outils. Les TI peuvent profiter de ce momentum et améliorer la solution en augmentant la capacité du BI avec une architecture BI corporative.

Bon succès dans vos réalisations BI !

François Bouffard

Architecte BI

Panorama Technologies entrepôt de données et tableau de bord à Québec