Les utilisateurs de solutions décisionnelles expriment très régulièrement des besoins de pouvoir obtenir, à partir d’une position analytique relativement agrégée, une extraction de données à un niveau très granulaire.

Cet article a pour objectif de décrire comment répondre concrètement à ce besoin opérationnel, en détaillant des exemples de techniques mises en œuvre dans des organisations qui utilisent SQL Server Analysis Services en tant que plateforme d’analyse.

Problématique

  • Intégrer dans une solution analytique des fonctionnalités de navigation détaillée
  • Exploiter les possibilités d’Analysis Services en tenant compte des spécificités du moteur OLAP

Bénéfices

  • Une solution End to End en adéquation avec les différentes catégories d’usages dans l’entreprise
  • L’utilisation de la puissance d’Excel en tant qu’outil privilégié de consultation des données
  • Une ouverture vers les scénarios de décisionnel en libre-service

 

 

Les usages et les besoins utilisateurs

En matière de décisionnel d’entreprise, les usages se répartissent autours de 3 grandes catégories :

  • Les tableaux de bord fournissant une vue très synthétique des données, structurés autour de graphiques et de KPI (Key Performance Indicators), facilitant la prise d’information relative à des questions stratégiques pour l’entreprise

  • Les analyses Ad-Hoc prévues pour analyser une situation opérationnelle courante, et également réaliser des prédictions, en s’appuyant sur des outils de navigation interactifs pour croiser, filtrer et trier les données

  • Le Reporting opérationnel consistant à diffuser de manière massive, en mode PULL (consultation) ou PUSH (abonnement), des rapports au contenu très structuré (tableaux, matrices…) et potentiellement répartis sur plusieurs pages.

A noter que le décisionnel « en libre-service », apparu plus récemment, ouvre la voie à une catégorie d’utilisateurs spécifiques, qui souhaitent rassembler et transformer des données, construire des modèles analytiques, et mettre en forme des structures de représentation des données (rapports, tableaux de bord) afin d’implémenter dans des cycles relativement courts, des analyses qui pourront ensuite être diffusées au niveau d’une équipe ou d’une entité de l’organisation.

 

 

Explorer les données détaillées

Le besoin d’exploration détaillée des données depuis une base de données analytique se situe à la jonction entre l’analyse ad-hoc et le reporting opérationnel.

Le but est de pouvoir transposer une analyse multidimensionnelle à partir de laquelle l’utilisateur aura défini ses axes d’analyses, ses indicateurs, et ses filtres,  vers une structure tabulaire (type « listing »)  en exposant les données sous une forme très granulaire, et comportant généralement un nombre important de champs.

Ce type d’usage traduit bien souvent les besoins métiers suivants :

  • Isoler un fait précis pouvant avoir des effets importants sur une vision plus agrégée de données.

    Ex : Identifier une écriture comptable incohérente

  • Etablir une liste d’occurrences répondant à une multitude de filtres

    Ex : Listing  très détaillé de clients répondant à des critères de classification en fournissant l’exhaustivité des achats associés sur une période et une zone géographique données

  • Extraire une certaine quantité d’information à retravailler par la suite

    Ex : Croisement de données détaillées d’un cube contenant des données de ventes avec un jeu de données marketing.

 

 

SQL Server Analysis Services en tant que socle analytique

SQL Server Analyses Services est un des piliers de l’offre décisionnelle de Microsoft permettant de fournir à travers un modèle sémantique, une vue métier des données en combinant des axes d’analyses représentés par des dimensions et des hiérarchies, avec des collections de mesures et d’indicateurs calculés.

Depuis SQL Server 2012, Analysis Services est disponible selon deux modes :

  • Le mode multidimensionnel, qui est disponible depuis de nombreuses versions de SQL Server, et qui s’est enrichi à chaque version, pour être aujourd’hui une référence parmi les technologies dites « OLAP » (Online Analytical Processing)

  • Le mode tabulaire, qui est la version « In Memory » des cubes analytiques SQL Server, hérité de Power Pivot et de l’algorithme de compression et de montée en mémoire Vertipac

 

 Vue macroscopique du modèle sémantique BI

 

Le livre blanc « Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services » rédigé par l’équipe SQLCAT détaille les similitudes et les différences entre les deux modes de fonctionnement, pour définir par la suite quelle est l’approche la plus appropriée en fonction des besoins et des contraintes techniques : http://msdn.microsoft.com/en-us/library/hh994774.aspx

Quel que soit le mode d’utilisation retenu (multidimensionnel ou tabulaire), il est important de respecter un certain nombre de bonnes pratiques en termes de modélisation afin de tirer le meilleur parti des performances du moteur OLAP d’Analysis Services.

Voici quelques règles parmi les plus élémentaires :

- Construire un cube au-dessus d’une base de données relationnelle modélisée en « étoile » ou « flocon de neige »

- Définir des clés techniques numériques (« Surrogate key ») pour lier les tables de faits avec les tables de dimension, et les utiliser en tant que « Key Column » au niveau des attributs clés des dimensions

- Adapter l’activation des hiérarchies d’attributs en fonction des besoins réels, et favoriser l’utilisation des propriétés de membres pour afficher de l’information détaillée

- Optimiser la relation hiérarchique entre les attributs d’une dimension

 

 Relations hiérarchiques des attributs d’une dimension

 

La modélisation des dimensions et des attributs associés fait partie des piliers majeurs du design d’un cube, et la question de la granularité de ces axes métiers et des indicateurs potentiellement associés fait partie des points essentiels en phase de conception.

Le fait que des utilisateurs expriment des besoins d’exploration très détaillée des données peut avoir une influence sur la manière dont le cube et le modèle de données sous-jacent sont conçus.

Implémenter une dimension contenant autant d’occurrences qu’il existe de lignes dans la table de faits ne produit pas d’effet bénéfique sur les performances du cube :

  • Atténue les effets des mécanismes d’agrégation des structures de données sur disque d’Analysis Services (hors stratégie d’agrégation)

  • Nécessite une gestion avancée de la dimension (ex : rafraichissement incrémental)

  • Entraine un surcout à la fois en IOs et en ressources CPU en phase de réponses aux requêtes

  • Nécessite plus de ressources mémoires pour le stockage de la dimension

Un autre point d’attention concerne des requêtes sur les cubes qui consisteraient à imbriquer une collection conséquente de hiérarchies, d’attributs et d’indicateurs, afin de descendre au niveau le plus détaillé dans les données.

Il existe des techniques spécifiques permettant de répondre à ce besoin, de manière optimisée, tout en garantissant la meilleure expérience utilisateur sur l’ensemble de la pyramide des usages décisionnels.

 

 

Les fonctionnalités natives

De nombreuses possibilités techniques sont disponibles pour activer des fonctionnalités de navigation détaillées dans les structures de données des cubes Analysis Services.

 

La navigation entre rapports avec Reporting Services

La plateforme de rapports opérationnels SQL Server Reporting Services offre des fonctionnalités de navigation entre les rapports, qui permet d’obtenir à travers un second rapport les données détailles d’une partie précise d’un premier rapport (ex : cellule, en-tête de ligne, série...)

Le passage de paramètres entre les deux rapports permet de définir le contexte de la requête du second rapport, et de dynamiser l’appel à la fonction de navigation dans la structure du premier rapport.

Configuration d’une action dans Reporting Services

 

Les actions dans Analysis Services

La version multidimensionnelle d’Analysis Services propose des fonctionnalités dites d’actions, qui permettent d’étendre l’interactivité entre l’utilisateur et le cube analytique.

Ces actions permettent de répondre concrètement au besoin de navigation détaillée dans les données du cube.

Les actions de type DrillThrough permettent de prédéfinir un contexte et la structure en colonnes d’un flux de données détaillées, issues du cube, et disponible à partir d’outils tels qu’Excel.

Les avantages de cette solution résident dans sa grande simplicité de mise en œuvre, tout en laissant la possibilité à l’utilisateur d’utiliser les données extraites en fonction de ses besoins, notamment à travers les fonctionnalités de traitement des données d’Excel.

Il est important de souligner que l’intégralité des données du modèle qui sont candidates à une exposition de type Drillthrough doivent faire partie intégrante du cube.

Configuration d’une action de type Drillthrough

 

Appel de l’action de type Drillthrough depuis Excel

 

Affichage des données détaillées dans Excel

 

Le principal intérêt de configurer une action DrillThrough au niveau du cube réside dans le fait d’encapsuler une ou plusieurs configurations de données détaillées, sans paramétrage dans l’application cliente.

Les actions de type Reporting et URL permettent de conjuguer les usages interactifs à partir des cubes Analysis Services avec les composants de la plateforme  Reporting Services, qui offrent à la fois une grande liberté dans le formatage du rapport d’extraction, ainsi que de nombreuses possibilités d’export: Excel, Word, PDF, CSV, XML…

Configuration d’une action de type Reporting

L'exploration Excel

Excel fournit également au niveau des tableaux croisés dynamiques une option de navigation détaillée, également basée sur la commande MDX DRILLTHROUGH.

Cette interaction avec le cube fournie par Excel, ne nécessite pas la configuration d’une action dans Analysis Services. 

Le flux renvoyé  correspond exactement au niveau de granularité de la mesure ciblée, telle qu’elle est paramétré dans le cube (ex : date, produit, client, vendeur, promotion ….).

 Action de navigation détaillée dans Excel

 

 

La commande MDX DrillThrough

Le langage MDX (langage natif d’interrogation des cubes multidimensionnels Analysis Services) expose une commande DRILLTHROUGH qui permet aux applications clientes d’interroger sous la forme d’un SELECT le niveau le plus détaillé de la structure de données d’un cube analytique.

Contrairement aux autres types de requêtes MDX basées sur un canevas multidimensionnel (lignes, colonnes, slices…), le format de sortie (RETURN) de la commande DRILLTHROUGH est semblable à celui d’une requête sur une base relationnelle, ce qui est adapté pour un travail sur des données détaillées.

La clause RETURN de la commande DRILLTHROUGH permet de définir la liste des informations retournées (attributs de dimensions et mesures) renvoyées par la requête.

En cas d’absence de clause RETURN, la commande MDX DRILLTHROUGH renverra les attributs clés de chaque dimension constituant la granularité du groupe de mesure ciblé dans le scope de la requête.

--DEFINITION DU NOMBRE MAXIMUM DE LIGNES RENVOYEES

DRILLTHROUGH MAXROWS 10000

--DEFINITION DU SCOPE DU DRILLTHROUGH

SELECT FROM [Adventure Works]

WHERE {([Measures].[Reseller Sales Amount],[Date].[Calendar].[Month].&[2008]&[5],[Geography].[Country].&[United States],[Product].[Product Categories].[category].&[2])}

--DEFINITION DU FLUX DE SORTIE (le nom des dimensions est préfixé par $)

RETURN

[$Geography].[City],[$Date].[Date],[$Reseller].[Reseller],[$Product].[Subcategory],[$Product].[Product],[$Product].[Product Line],[$Promotion].[Promotion],

[$Source Currency].[Source Currency],[$Measures].[Reseller Sales Amount],[$Measures].[Reseller Order Quantity]

Exemple d'utilisation de la commande MDX DRILLTHROUGH

La commande DRILLTHROUGH présente les possibilités suivantes :

  • Configuration personnalisée de la structure en flux de sortie

  • Définition précise de l’espace du cube concerné à travers des filtres sur les dimensions et les hiérarchies

  • Possibilité de remonter des informations issues de différentes dimensions et de différents groupes de mesures

  • Possibilité de limiter le nombre maximal de lignes retournées

 

 

Les possibilités d'extension à travers la customisation d'Analysis Services

Analysis Services en mode multidimensionnel offre des possibilités d’extension des fonctionnalités couvertes par le langage MDX, en offrant aux développeurs un modèle objet permettant de renforcer l’intégration entre les cubes analytiques et les applications qui en tirent profit.

Ces extensions de fonctionnalités, dites « procédures stockées » sont basées sur le Framework .NET, et sont compilées dans Visual Studio afin de produire une DLL.

Une fois déployées sur le serveur avec le niveau de sécurité et le contexte d’exécution qui conviennent, ces DLLs exposent des fonctions qui peuvent être appelées directement à travers une requête MDX, une action ou un rôle de sécurité.

La mise en œuvre d’une sécurité dynamique peut par exemple s’appuyer sur une procédure stockée .NET pour interroger un référentiel de sécurité externe au cube :

http://blogs.technet.com/b/sql/archive/2014/01/03/sql-server-chez-les-clients-securisez-les-donnees-de-votre-systeme-decisionnel.aspx

Les procédures stockées dans Analysis Services sont également adaptées pour étendre les possibilités natives de DRILLTHROUGH en couvrant les aspects suivants :

  • Couvertures des mesures calculées

  • Prise en charge des données très granulaires qui ne sont pas montées dans la structure du cube

  • Paramétrage applicatif et formatage customisé

Le projet ASSP « Analysis Services Stored Procedure » disponible en téléchargement sur le site CodePlex propose des extensions de la commande DrillThrough : http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough

 

 

Mise en œuvre d'une solution spécifique de navigation détaillée

Dans le cadre d’un projet de refonte d’une plateforme décisionnelle, et en réponse à un besoin global de navigation à un niveau très détaillé dans des cubes, une solution de « Drillthrough » spécifique a été mise en œuvre chez plusieurs clients de Microsoft Consulting Services, dont les métiers sont les suivants :

  • Gestion comptable et financière

  • Gestion locative

  • Cosmétique

 

Cette solution répond aux besoins métiers suivants :

  • Large utilisation d’extractions au format « liste » (jusqu’à 80 colonnes)

  • Capacité à intégrer dans les extractions des informations non activées dans les cubes (Ex : commentaires d’écritures comptables)

  • Performance des extractions en termes de temps de réponse

  • Maitrise des en-têtes des champs dans le flux de données retourné

  • Possibilité de rajouter de nouveaux champs dans les extractions

  • Intégration complète avec Excel et les tableaux croisés dynamiques

  • Solution sécurisée (respect des restrictions sur le périmètre de données visibles par l’utilisateur)

 

La spécificité de cette solution réside dans sa capacité à partir d’un contexte de navigation multidimensionnelle depuis un cube, pour extraire par la suite des données dans des vues relationnelles.

 

L’architecture technique de cette solution est composée des éléments suivants :

  • Procédures stockée .NET déployée sur le serveur SSAS et référencée par des actions de type ROWSET

  • Vues SQL indexées et partitionnées, interrogées par une procédure stockée

  • Délégation Kerberos et rôle d’application SQL Server pour supporter la sécurité applicative de bout en bout

 

Le principe de fonctionnement global est le suivant :

    • L’utilisateur est connecté au cube sous Excel, en authentification Windows

    • L’utilisateur navigue dans le cube, et choisis d’appeler une extraction à travers les actions qui sont exposées par le cube, et visibles dans un menu contextuel du tableau croisé dynamique Excel

    • L’action est déclenchée et invoque la procédure.NET, elle-même exécutée sous l’identité de l’utilisateur

    • La procédure .NET récupère les informations du contexte de navigation, et se connecte à un rôle d’application  spécifique sur le serveur SQL

    • Une procédure stockée T-SQL est appelée, afin d’extraire les données choisies, en se basant sur le contexte utilisateur (position au niveau du cube et périmètre de données autorisé)

    • Le flux de données sortant est renvoyé sur une nouvelle feuille du classeur Excel

 

Le rôle d’application, dont les informations d’accès sont protégées, permet de gérer une sécurité applicative de bout en bout (en respectant notamment les règles d’accès aux données), tout en évitant d’exposer les vues indexées aux utilisateurs.

 Architecture globale

 

Exemple de résultat retourné par la solution spécifique

 

 

L’expertise Microsoft Consulting Services au service de ses clients

Les consultants et architectes de l’équipe MCS Data Insights interviennent au quotidien dans la conception, l’implémentation et le déploiement de solutions permettant à leurs clients d’exploiter la valeur potentielle que représentent les données opérationnelles.

Cet éventail de compétences se décline autours des plateformes de données, le décisionnel d’entreprise et en libre-service, la gestion de données maîtres (MDM), la qualité de données (DQM), le Big Data et le Cloud.

 

Pour plus d’informations sur les offres packagées Microsoft Consulting Services, rendez-vous sur http://www.microsoft.com/france/services

Plus d’informations sur les blogs « SQL Server chez les clients ».

 

Mathias Ekizian, Consultant Data Insights, Microsoft Consulting Services

J’interviens dans le cadre de projets décisionnels chez des clients, en tant que leader technique, et également en assistance pour une validation d’architecture, de l’audit, ou une optimisation de solutions basées sur la gamme de produits Microsoft Business Intelligence.  Je suis également spécialisé dans les problématiques de gestion de données maîtres (MDM) construites à partir de SQL Server Master Data Services (MDS).