Modèle de données - Microsoft Center
publicité
Choisir une expérience de modélisation tabulaire ou multidimensionnelle dans SQL Server 2012 Analysis Services Article technique Microsoft Business Intelligence Auteurs Conseil Hitachi : Liz Vitt - Auteur Scott Cameron - Auteur Hilary Feier - Relecteur Microsoft : T.K. Anand - Relecteur Ashvini Sharma - Relecteur Date de publication : mai 2012 S'applique à : SQL Server 2012 Analysis Services Résumé : ce livre blanc fournit une aide pratique aux professionnels et aux décideurs BI afin qu'ils puissent déterminer si la modélisation tabulaire ou multidimensionnelle SQL Server Analysis Services 2012 est le meilleur choix pour leur prochaine solution BI. Copyright Ce document est fourni « en l'état ». Les informations et les opinions exprimées dans ce document, y compris les adresses URL et les autres références à des sites Internet, peuvent faire l'objet de modifications sans préavis. Vous assumez tous les risques liés à son utilisation. Ce document ne vous concède aucun droit de propriété intellectuelle portant sur les produits Microsoft. Vous pouvez copier et utiliser ce document à titre de référence pour un usage interne. © 2012 Microsoft Corporation. Tous droits réservés. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 2 Sommaire Introduction ................................................................................................................................................... 5 Informations de base sur la modélisation BISM ............................................................................... 5 Modélisation multidimensionnelle ................................................................................................... 5 Modélisation tabulaire .......................................................................................................................... 6 Outils d'analyse clients BISM ................................................................................................................... 7 Modèle de données .................................................................................................................................... 7 Relations de données ............................................................................................................................ 7 Relations un-à-plusieurs .................................................................................................................. 7 Relations plusieurs-à-plusieurs...................................................................................................... 8 Relations de référence ...................................................................................................................... 8 Hiérarchies ................................................................................................................................................. 9 Hiérarchies standard.......................................................................................................................... 9 Hiérarchies déséquilibrées .............................................................................................................. 9 Hiérarchies parent-enfant................................................................................................................ 9 Fonctionnalités de modélisation supplémentaires ...................................................................10 Logique métier ...........................................................................................................................................12 Transformations au niveau des lignes ...........................................................................................12 Valeurs agrégées ...................................................................................................................................13 Calculs .......................................................................................................................................................13 Scénarios de logique métier .............................................................................................................15 Logique de hiérarchie .....................................................................................................................15 Cumuls personnalisés .....................................................................................................................15 Mesures semi-additives..................................................................................................................16 Time Intelligence ...............................................................................................................................17 Indicateurs de performance clés .................................................................................................17 Conversion monétaire.....................................................................................................................17 Jeux nommés .....................................................................................................................................17 Accès aux données et stockage............................................................................................................19 Performances et extensibilité ............................................................................................................19 Modèles multidimensionnels .......................................................................................................19 Modèles tabulaires ...........................................................................................................................20 Programmabilité ....................................................................................................................................22 Sécurité ..........................................................................................................................................................22 Sécurité au niveau de la ligne/de l'attribut ..................................................................................23 Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 3 Sécurité dynamique ..............................................................................................................................23 Sécurité au niveau de la cellule et sécurité avancée ................................................................23 Résumé ..........................................................................................................................................................24 Pour plus d'informations.........................................................................................................................29 Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 4 Introduction La modélisation des données est une discipline pratiquée depuis de nombreuses années par les professionnels BI, avec un objectif commun : organiser des données disparates dans un modèle analytique qui prend en charge de manière efficace et efficiente les besoins de reporting et d'analyse de l'entreprise. La modélisation des données a évolué au fil des années grâce à de nouvelles technologies et à de nouveaux outils. Or, les organisations doivent aujourd'hui relever un nouveau défi, à savoir, fusionner leurs paradigmes de modélisation de façon transparente et cohérente pour satisfaire les divers besoins d'analyse, mais aussi pour en rendre l'expérience homogène au sein de l'entreprise. Avec SQL Server 2012, Microsoft peut les aider à atteindre cet objectif grâce à l'introduction du modèle sémantique BI (BISM), un modèle unique qui prend en charge une vaste gamme de rapports et d'analyses tout en fusionnant deux expériences de modélisation Analysis Services en arrière-plan : La modélisation multidimensionnelle, introduite par les services OLAP SQL Server 7.0 et reprise dans SQL Server 2012 Analysis Services, permet aux professionnels BI de créer des cubes multidimensionnels complexes utilisant le traitement analytique en ligne traditionnel (OLAP). La modélisation tubulaire, introduite par PowerPivot pour Microsoft Excel 2010, fournit des fonctionnalités de modélisation des données libre-service aux analystes d'entreprise et de données. L'expérience de modélisation tabulaire est davantage accessible aux utilisateurs qui ont passé des années à exploiter des données à l'aide d'outils bureautiques tels qu'Excel et Microsoft Access. Dans SQL Server 2012, la modélisation tabulaire a été étendue pour permettre aux professionnels BI de créer des modèles tabulaires dans Analysis Services, ou d'importer un modèle tabulaire PowerPivot dans Analysis Services. Notez que le modèle PowerPivot ne peut pas être importé dans un modèle multidimensionnel Analysis Services. L'objectif de ce livre blanc est de vous aider à déterminer quelle expérience de modélisation SQL Server 2012 Analysis Services (tabulaire ou multidimensionnelle) convient le mieux à votre prochaine solution BI. Les descriptions et les recommandations de produits de ce document se réfèrent à SQL Server 2012 Analysis Services, disponible depuis mars 2012. Les caractéristiques du produit et les recommandations sont susceptibles de changer au fil de l'évolution de la modélisation multidimensionnelle ou tabulaire d'Analysis Services, dans les versions ultérieures de SQL Server. Informations de base sur la modélisation BISM Avant d'aborder de façon approfondie les différences entre la modélisation multidimensionnelle et la modélisation tabulaire, commençons par une brève présentation des expériences de modélisation BISM fournies par SQL Server 2012 Analysis Services. Modélisation multidimensionnelle Globalement, la modélisation multidimensionnelle crée des cubes composés de mesures et de dimensions basées sur les données contenues dans une base de données relationnelle. Pour utiliser ce paradigme, le serveur Analysis Services doit être configuré pour s'exécuter en mode multidimensionnel, sa valeur par défaut. Dans ce mode, le moteur OLAP utilise le modèle multidimensionnel pour préagréger d'importants volumes de données et garantir un temps de réponse à la requête rapide. Le moteur OLAP peut stocker ces agrégations sur un disque avec stockage OLAP multidimensionnel (MOLAP) ou dans la base de données relationnelle avec stockage OLAP relationnel (ROLAP). Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 5 Les principales caractéristiques de la modélisation multidimensionnelle sont les suivantes : Modèle de données enrichies - Le modèle multidimensionnel de SQL Server 2012 Analysis Services est à sa sixième version et fournit des fonctionnalités étendues aux mesures et aux dimensions à partir des datasets simples et complexes généralement disponibles dans les entrepôts de données de l'entreprise. Les datasets complexes comprennent généralement des fonctionnalités avancées telles que les relations plusieurs à plusieurs, les hiérarchies parent-enfant, et la localisation. Le modèle multidimensionnel fournit cette fonctionnalité prête à l'emploi. Analyse complexe - Le modèle multidimensionnel fournit également un calcul avancé et un langage de requête appelé MDX (Multidimensional Expressions). Avec MDX, vous pouvez créer une logique métier et des calculs complexes utilisables n'importe où dans l'espace multidimensionnel pour élaborer des dotations financières, des calculs de série chronologique, ou des mesures semi-additives. Bien qu'une modélisation des données complète et une analyse complexe constituent des avantages importants en matière de modélisation multidimensionnelle, elles comportent généralement des cycles de développement plus longs et peuvent être un obstacle lorsqu'il faut s'adapter rapidement à la conjoncture professionnelle. En outre, l'expérience multidimensionnelle tend à exiger des compétences de modélisation et MDX avancées. Modélisation tabulaire La modélisation tabulaire organise les données dans des tables associées. Si vous souhaitez utiliser la modélisation tabulaire, Analysis Services doit être configuré pour s'exécuter en mode tabulaire. En mode tabulaire, vous pouvez utiliser le moteur en mémoire (In-Memory) xVelocity (anciennement Vertipaq) pour charger les données tabulaires en mémoire et accélérer la réponse aux requêtes, ou vous pouvez utiliser DirectQuery pour passer les requêtes à la base de données source et exploiter les fonctions de traitement des requêtes. Les principales caractéristiques de la modélisation tabulaire sont les suivantes : Familiarité - Les données tabulaires sont familières à de nombreux publics qui utilisent régulièrement des tables stockées dans des bases de données relationnelles, Excel, ou Access. En outre, les calculs sont écrits en DAX (Data Analysis Expressions), un langage de formule considéré comme une extension du langage de formule Excel. De ce fait, les compétences nécessaires pour créer des modèles tabulaires sont davantage répandues ou plus faciles à acquérir par rapport aux compétences requises pour créer des modèles multidimensionnels. Souplesse - Étant donné que les données ne sont pas organisées de façon rigide dans des mesures et des dimensions, la modélisation tabulaire peut accélérer les cycles de développement, car elle nécessite moins de préparation des données en amont et moins de rigueur de conception que les modèles multidimensionnels. Cette architecture permet également de s'adapter plus facilement aux changements apportés à la modélisation des données, lorsqu'il faut mettre à jour les relations et les calculs en fonction des nouveaux besoins. Si la familiarité et la souplesse de la modélisation tabulaire sont des facteurs clés, elles présentent également des inconvénients. Par exemple, la modélisation tabulaire peut ne pas convenir pour les solutions comportant des datasets très complexes ou qui requièrent une logique métier sophistiquée. Les utilisateurs du langage DAX peuvent généralement créer des formules DAX pour fournir les fonctionnalités analytiques absentes dans le modèle tabulaire. Dans ces cas, toutefois, il peut être plus approprié et efficace d'utiliser les capacités avancées natives fournies par la modélisation multidimensionnelle. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 6 Outils d'analyse clients BISM Que vous choisissiez la modélisation multidimensionnelle ou la modélisation tabulaire, il est important de noter que vous pouvez utiliser des outils clients qui génèrent aussi bien le langage MDX que le langage DAX pour interroger le modèle. Excel et SQL Server Reporting Services sont des outils clients qui génèrent des requêtes avec MDX, et Power View est un outil client qui génère des requêtes avec DAX. Il y a deux exceptions à cette règle. Power View est un outil d'exploration et de visualisation des données interactif inclus dans le complément SQL Server 2012 Reporting Services pour Microsoft SharePoint Server 2010 Enterprise Edition. Si vous souhaitez utiliser Power View ou tout autre client d'analyse utilisant DAX pour interroger un BISM, vous devez utiliser un modèle tabulaire. Il est prévu que les versions ultérieures de SQL Server permettent d'utiliser DAX pour interroger des modèles MDX, afin qu'ils puissent être accessibles par un outil client comme Power View. Les modèles tabulaires qui ont été configurés pour utiliser DirectQuery requièrent un outil client qui génère les requêtes DAX, comme Power View. Il est prévu que dans les versions ultérieures de SQL Server, les modèles tabulaires configurés pour utiliser DirectQuery acceptent les requêtes MDX. Modèle de données Les caractéristiques de votre modèle de données sont essentielles pour déterminer le choix de votre expérience de modélisation. Relations de données Il est fondamental qu'un modèle de données représente correctement la façon dont les éléments de données qu'il contient sont corrélés et connectés, exactement comme pour les différentes pièces d'un puzzle. Les modèles tabulaires et les modèles multidimensionnels exigent que vous définissiez des relations entre vos tables de données sources. Les relations couramment utilisées dans la modélisation des données sont les relations un-à-plusieurs, plusieurs-à-plusieurs, et de référence. Relations un-à-plusieurs Dans une relation un-à-plusieurs, un enregistrement unique d'une table est lié à plusieurs enregistrements d'une autre table. Un exemple de relation un-à-plusieurs est un client qui a passé plusieurs commandes. Les modèles de données tabulaire et multidimensionnel gèrent en mode natif des relations un-à-plusieurs. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 7 Relations plusieurs-à-plusieurs Dans une relation plusieurs-à-plusieurs, plusieurs enregistrements d'une table sont liée à plusieurs enregistrements d'une seconde table. Par exemple, un client a une relation un-à-plusieurs avec les commandes ; toutefois, chaque client peut être classés en catégories dans un ou plusieurs profils de clientèle (par exemple : très sportif, sportif occasionnel ou amateur de fitness). L'analyse des commandes par profil de client peut s'avérer difficile lorsque des relations plusieurs-à-plusieurs existent, car une double classification peut se produire : La commande d'un vélo par un client qui est très sportif et amateur de fitness à la fois peut facilement être comptabilisée deux fois lorsque les commandes par profil de client sont additionnées pour obtenir les commandes totales. En général, les relations plusieurs-à-plusieurs sont gérées en les éclatant en deux relation un-à-plusieurs à l'aide d'un pont ou d'une table intermédiaire, comme l'illustre la figure 1. ID Client 1 … Nom du client Elizabeth Johnson … Table Client ID Client Commande client Montant des ventes 1 1 1 … S9100 S9101 S9102 … $ $ $ 4 000 2 500 7 000 … Table Commandes ID Client Profil de client 1 Sports Enthusiast Casual Gamer 1 Fitness Expert 1 … … Pont / Table intermédiaire pour attribuer le profil du client Figure 1 - Exemple de relation plusieurs-à-plusieurs Dans les modèles multidimensionnels, vous pouvez définir et créer des relations plusieurs-à-plusieurs directement dans le modèle de données, en identifiant la table pont, puis en la mettant en relation avec d'autres tables dans votre modèle. Lors de l'agrégation, Analysis Services applique une somme distincte pour garantir que les totaux sont correctement résumés et non gonflés de façon inappropriée. Les modèles tabulaires SQL Server 2012 Analysis Services ne prennent pas en charge la définition des relations plusieurs à plusieurs. Toutefois, vous pouvez utiliser le langage DAX pour créer des formules qui gèrent les difficultés liées à ces relations. Relations de référence Un modèle de données peut contenir un ensemble d'attributs communs liés à plusieurs entités. Par exemple, les attributs géographiques sont liés aux clients, aux fournisseurs et aux magasins. Dans la modélisation multidimensionnelle, vous devez créer une dimension qui contient les attributs usuels, puis créer des relations de dimension de référence sur chaque dimension associée. En modélisation tubulaire, il est inutile de créer des relations de référence. Dans un modèle tabulaire, il vous suffit de créer des relations entre la table qui contient les attributs usuels et les tables contenant les entités associées. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 8 Hiérarchies Les hiérarchies classent les données en structures d'arborescence pour faciliter l'exploration au niveau du détail. Hiérarchies standard Les hiérarchies standard se composent de niveaux classés provenant des colonnes de vos données sources. Par exemple, une hiérarchie de produit peut classer les produits en sous-catégories, qui peuvent ensuite être classées en catégories. Dans ce cas, vous avez une hiérarchie à trois niveaux, chaque niveau provient d'une colonne distincte dans vos données sources. Les hiérarchies simples, comme la hiérarchie de produit décrite ici, sont prises en charge dans les modèles tabulaires et dimensionnels à la fois. Notez que dans les modèles multidimensionnels, une étape supplémentaire est requise pour créer des relations d'attribut, afin d'identifier de façon explicite les relations un-à-plusieurs entre des attributs dans chaque dimension. Il est fortement recommandé de définir des relations d'attribut car elles permettent une conception plus efficace des agrégations précalculées, et car la sémantique MDX repose sur des relations d'attribut. La modélisation tabulaire est plus simple, car vous ne créez pas de relations d'attribut. Les modèles tabulaires ne précalculent pas les agrégations, et la sémantique DAX ne repose pas sur l'identification des relations un-à-plusieurs entre les attributs, c'est pourquoi, en modélisation tabulaire, il n'y a pas d'équivalent aux relations d'attribut de la modélisation multidimensionnelle. Hiérarchies déséquilibrées Les hiérarchies déséquilibrées se forment lorsqu'un élément de données spécifique n'est pas contenu dans l'arborescence hiérarchique. Par exemple, une hiérarchie de produit déséquilibrée se forme s'il y a des produits qui ne sont jamais classés dans une sous-catégorie, tout en ayant des affectations de catégorie de produit. Dans ce cas, plutôt qu'afficher l'écart dans l'arborescence, vous pouvez choisir de le masquer pour faciliter l'exploration au niveau du détail. Les modèles multidimensionnels prennent entièrement en charge les hiérarchies déséquilibrées, contrairement aux modèles tabulaires. Hiérarchies parent-enfant Les hiérarchies parent-enfant ont une structure hiérarchique plus complexe. Toutes les branches d'une hiérarchie parent-enfant n'ont pas le même nombre de niveaux. Par exemple, une relation parent-enfant entre un employé et un responsable peut produire une hiérarchie dans laquelle certains responsables ont uniquement des rapports directs, alors que d'autres responsables ont des rapports directs, qui ont à leur tour leurs propres rapports directs. Ce type de hiérarchie se forme en créant une relation entre deux colonnes dans une table de données sources, comme le montre la figure 2. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 9 Employé Employee Ken J. Sánchez Brian S. Welcker Amy E. Alberts Jae B. Pak David M. Bradley Kevin F. Brown Directeur Manager Ken J. Sánchez Ken J. Sánchez Brian S. Welcker Amy E. Alberts Ken J. Sanchez David M. Bradley Données sources parent-enfant Arborescence de la hiérarchie parent-enfant Figure 2 - Hiérarchie parent-enfant Les modèles multidimensionnels offrent des fonctionnalités prêtes à l'emploi qui vous permettent de définir et de créer des hiérarchies de type parent-enfant en fonction des relations dans vos données sources. Dans les modèles tabulaires, vous pouvez utiliser des fonctions DAX pour créer des formules qui parcourent et utilisent la structure parent-enfant dans les calculs. Pour plus d'informations sur l'utilisation des hiérarchies parent-enfant dans les modèles tabulaires, consultez Fonctionnement des fonctions pour les hiérarchies de type parentenfant dans DAX (http://msdn.microsoft.com/fr-fr/library/gg492192(v=sql.110).aspx). Fonctionnalités de modélisation supplémentaires Outre les relations et les hiérarchies de données, des fonctionnalités de modélisation supplémentaires peuvent vous aider à choisir la meilleure expérience de modélisation : Les perspectives permettent de définir un sous-ensemble d'un modèle de données pour simplifier l'exploration par les utilisateurs finaux. Les perspectives sont disponibles dans les modèles multidimensionnels et tabulaires à la fois. Dans les modèles multidimensionnels, les traductions permettent d'afficher la dimension, l'attribut, la mesure, le membre calculé, ainsi que d'autres noms d'objets et valeurs de membres de dimension, dans la langue spécifiée par les paramètres régionaux de l'ordinateur. Pour activer cette fonctionnalité, le développeur du modèle doit fournir des noms d'objets traduits et référencer les colonnes dans les données sources contenant les valeurs de membre de dimension traduites. Les modèles tabulaires ne fournissent pas cette fonctionnalité. Les actions permettent aux utilisateurs finaux d'exécuter un rapport Reporting Services, d'accéder à une URL, ou d'initier une opération externe en fonction du contexte de la cellule où l'action se produit. Par exemple, au moyen d'une action, un utilisateur peut lancer une page Web qui affiche le catalogue des produits d'une société automatiquement filtrés en fonction du ou des produits qu'il consultait. Les actions sont prises en charge en mode natif dans les modèles multidimensionnels, et de nombreux outils clients (comme Excel et Reporting Services) permettent aux utilisateurs d'effectuer des actions. Dans SQL Server 2012, la capacité de créer des actions dans un modèle tabulaire à l'aide de SQL Server Data Tools n'est pas prise en charge. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 10 L'extraction vous permet d'accéder aux données détaillées stockées dans votre modèle. Elle est disponible dans la modélisation multidimensionnelle et tabulaire. Les modèles multidimensionnels vous permettent également de créer des actions d'extraction, afin de personnaliser l'expérience d'extraction en spécifiant les colonnes qui sont retournées et l'espace du cube où l'action est activée. L'écriture différée est une fonctionnalité généralement requise dans les applications de gestion budgétaire et prévisionnelle. Dans ce cas, les utilisateurs professionnels souhaitent généralement effectuer une analyse de scénario, en mettant à jour des valeurs dans le modèle, puis en publiant le résultat pour que d'autres puissent le consulter. Les modèles multidimensionnels fournissent la prise en charge native de l'écriture différée des données. Dans SQL Server 2012, les modèles tabulaires ne prennent pas en charge cette fonctionnalité. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 11 Logique métier La logique métier peut apporter une valeur ajoutée énorme à un modèle de données, sous la forme de calculs et de règles d'entreprise qui améliorent les données pour que l'utilisateur final puisse les analyser. La modélisation tabulaire et la modélisation multidimensionnelle offrent des langages de formule enrichis pour implémenter la logique métier. La modélisation multidimensionnelle repose sur MDX, et la modélisation tabulaire sur DAX. Avant de passer aux scénarios de logique métier avancés de chaque paradigme, il est important de présenter généralement la façon dont la logique métier peut être appliquée à l'aide des transformations au niveau des lignes, des valeurs agrégées, et des calculs dans la modélisation multidimensionnelle ou tabulaire. Transformations au niveau des lignes Vous pouvez avoir besoin d'effectuer des calculs et de transformer les données qui ne sont pas disponibles d'emblée dans les données sources. Par exemple, vos données sources peuvent contenir une colonne Montant des ventes et une colonne Taux de change monétaire, mais la conversion du montant des ventes dans la devise étrangère peut manquer, ou bien, vos données sources peuvent contenir le prénom et le nom des employés mais ne pas fournir leur nom concaténé complet. Notez que dans ces exemples, le calcul ou la manipulation des données doit se produire au niveau de la ligne et des données non agrégées. En modélisation multidimensionnelle, les transformations au niveau des lignes sur les données non agrégées doivent être réalisées avant le chargement des données dans le modèle, ou lorsque le modèle est interrogé. Vous pouvez transformer des attributs de dimension, tels que les noms des employés, en appliquant la transformation dans le système de source de données, ou en écrivant une expression SQL qui est appliquée lorsqu'Analysis Services interroge la base de données source. Les transformations au niveau des lignes contenant des données numériques peuvent être effectuées à l'aide d'une expression SQL avant le chargement des données dans Analysis Services, ou elles peuvent être appliquées au moyen d'une expression MDX dans une instruction Scope, afin que le calcul soit appliqué au niveau de la ligne. Si la transformation est appliquée avant le chargement des données, Analysis Services peut préagréger les valeurs numériques. Si la transformation est appliquée au moyen d'une instruction Scope, l'agrégation se produit au moment de la requête. En modélisation tubulaire, les transformations au niveau des lignes sont créées à l'aide de colonnes calculées. Lorsque vous créez une colonne calculée, vous ajoutez la colonne à une table spécifique dans votre modèle, et vous utilisez des formules DAX pour définir les valeurs de la colonne. La formule est ensuite évaluée pour chaque enregistrement de cette table, et est chargée en mémoire tout comme n'importe quelle autre colonne du modèle. Cette souplesse vous permet d'améliorer vos données directement dans le modèle tabulaire en fonction de vos besoins d'analyse spécifiques, et réduit les modifications des sources de données en amont, qui peuvent ne pas être en mesure d'appliquer vos modifications en temps voulu. Les colonnes calculées sont un moyen très pratique de créer et conserver des calculs qui doivent être effectués à un niveau détaillé dans vos données, avant d'être agrégés. Bien qu'il s'agisse d'une flexibilité puissante, notez que les colonnes calculées ne sont pas destinées à nettoyer les données brutes ou à transformer celles générées par des processus d'extraction, transformation et chargement (ETL). Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 12 Valeurs agrégées En modélisation multidimensionnelle, vous utilisez des mesures pour créer des valeurs agrégées. Le moteur OLAP Analysis Services préagrège les mesures d'un cube en utilisant des fonctions d'agrégation telles que SUM, COUNT, MIN, MAX, DISTINCT COUNT, et bien d'autres. Pendant le traitement du cube, chaque mesure est agrégée de bas en haut entre toutes les hiérarchies. Étant donné que ce traitement a lieu avant l'analyse de l'utilisateur, les mesures préagrégées peuvent offrir d'énormes avantages pour les performances des requêtes. Lorsque vous créez une mesure dans le cube, il existe une relation un-à-un entre une mesure du cube et une colonne numérique de vos données sources. Ainsi, dans la modélisation multidimensionnelle, les mesures sont utiles lorsque vous devez procéder à une agrégation descendante des éléments de données numériques qui (1) existent dans vos données sources au niveau de détail le plus bas et (2) requièrent un cumul au moyen d'une des fonctions d'agrégation natives du cube. En modélisation tabulaire, vous utilisez aussi des mesures pour créer des valeurs agrégées. Vous créez une mesure en sélectionnant une colonne, puis en spécifiant la fonction d'agrégation (SUM, COUNT, DISTINCT COUNT, MIN, MAX ou AVERAGE), ou vous pouvez écrire une expression DAX qui spécifie la fonction à utiliser pour agréger la mesure. En modélisation tubulaire, les données au niveau des lignes sont stockées en mémoire et les agrégations sont calculées au moment de la requête. Comme expliqué dans la section suivante, en modélisation tabulaire, les mesures peuvent également être utilisées pour appliquer des calculs. Il peut s'agir de calculs basés sur plusieurs colonnes agrégées. Calculs En modélisation multidimensionnelle, vous utilisez le langage MDX pour créer des calculs. MDX est à la fois un langage d'expression et de requête comportant des fonctions qui comprennent d'emblée la conception des dimensions, des hiérarchies, des relations d'attribut, et des mesures d'un cube. Cette compréhension native permet de créer des expressions succinctes et puissantes qui appliquent la logique métier dans plusieurs contextes de données. Vous créez et enregistrez les calculs MDX dans le script de calcul du cube, où vous pouvez contrôler l'ordre dans lequel la logique est appliquée. Les membres calculés sont les calculs MDX les plus courants. Ils sont évalués au moment de la requête, une fois les données préagrégées. Les membres calculés peuvent être créés dans n'importe quelle dimension. Lorsqu'ils sont créés dans la dimension de mesures, ils sont souvent appelés mesures calculées. Les membres calculés peuvent être relativement simples, et comporter des opérations arithmétiques de base, telles que les ventes par unité (ventes/unité) ou la dépense par personne (dépense/personne). Ils peuvent également être plus complexes lorsque vous devez appliquer des règles d'entreprise spécifiques telles que le ratio des ventes moyennes sur 3 périodes ou la marge annuelle jusqu'à ce jour. Par exemple, si vous souhaitez calculer les ventes pour la période actuelle sous la forme d'un pourcentage de la période parente, vous pouvez utiliser le calcul MDX suivant. [Measures].[Sales Amount] / ([Date].[Calendar].CurrentMember.Parent,[Measures].[Sales Amount]) Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 13 Créer un membre calculé dans une dimension différente de la dimension de mesures ajoute une valeur à un attribut dans la dimension. Par exemple, si vous avez un attribut de dimension qui contient une liste de couleurs, vous pouvez ajouter le membre calculé Couleurs primaires qui additionnera les valeurs des couleurs rouge, vert et bleu. En modélisation tubulaire, la création d'une mesure est similaire à la création d'un membre calculé dans la dimension de mesures dans un modèle multidimensionnel. En modélisation tubulaire, vous ne pouvez pas ajouter une valeur à une colonne dans une table, par conséquent, la modélisation tabulaire ne prend pas en charge l'équivalent de la création d'un membre calculé dans une dimension différente de la dimension de mesures dans un modèle multidimensionnel. Les affectations d'étendue sont plus avancées que les mesures calculées, mais elles sont également plus puissantes. Comme indiqué dans la section « Transformation au niveau des lignes » plus haut, vous pouvez utiliser une instruction Scope afin que les calculs soient appliqués au niveau de la ligne. Toutefois, vous pouvez également utiliser une instruction Scope pour spécifier une plage de cellules du cube où vous souhaitez appliquer un calcul. Les affectations d'étendue sont compilées avant la requête et permettent à Analysis Services de fournir un chemin d'exécution optimisé lorsque le calcul est interrogé. Étant donné leur puissance, les affectations d'étendue peuvent non seulement effectuer le travail de plusieurs mesures calculées, mais elles sont aussi plus efficaces. Par exemple, dans une solution de gestion budgétaire, vous souhaitez que le budget de l'année suivante pour la région Est soit égal à 90 % du budget de l'année en cours. Vous souhaitez que le nouveau budget de la région Ouest soit égal à 75 % du budget de l'année en cours. Vous souhaitez que le nouveau budget de la région Sud soit égal à 105 % du budget de l'année en cours et que le nouveau budget de la région Nord soit le même que le budget de l'année en cours. Au lieu d'écrire une seule mesure calculée complexe avec des instructions IF imbriquées ou plusieurs mesures calculées qui isolent chaque scénario de budget individuellement, vous pouvez utiliser les affectations d'étendue pour appliquer efficacement ces ratios au niveau de la région, puis pour agréger les totaux de données. Par exemple, si vous souhaitez convertir le montant des ventes dans une devise étrangère avec les taux de change quotidiens, vous pouvez utiliser l'expression MDX suivante : Scope([Date].[Date]); This = [Measures].[Sales Amount] * [Measures].[Daily FX Rate]; End Scope; En modélisation tabulaire, vous utilisez le langage DAX pour créer des calculs. Comme mentionné précédemment, en modélisation tubulaire vous appliquez des calculs au niveau de la ligne en créant des colonnes calculées. Vous pouvez également appliquer les calculs lorsque vous créez une mesure, en entrant une expression DAX. Étant donné que vous utilisez explicitement une combinaison de fonctions DAX au niveau des lignes et de fonctions d'agrégation, les mesures dans les modèles tabulaires sont extrêmement flexibles. Vous pouvez appliquer des fonctions au niveau des lignes, puis appliquer une fonction d'agrégation afin que la mesure applique les calculs avant l'agrégation, ou vous pouvez appliquer des fonctions d'agrégation en premier lieu, puis appliquer les fonctions au niveau des lignes afin que la mesure applique les calculs après les agrégations. DAX peut évaluer de manière dynamique une formule dans des contextes de données différents (pas seulement la vue actuelle d'une feuille de calcul Excel ou un tableau croisé dynamique) grâce à un ensemble de fonctions spéciales, appelées des fonctions FILTER. Au sens large, ces fonctions ont le même objectif que les affectations d'étendue Analysis Services car elles vous permettent de définir et d'effectuer un calcul sur un ensemble de lignes. Par exemple, vous pouvez utiliser les fonctions FILTER pour traiter l'exemple de gestion budgétaire décrit précédemment. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 14 Scénarios de logique métier Maintenant que vous avez une idée de la façon de créer et d'appliquer la logique métier de base dans MDX et DAX, considérez les scénarios de calcul suivants pour comparer et évaluer les expériences de modélisation tabulaire et multidimensionnelle. Logique de hiérarchie Comme indiqué précédemment, les hiérarchies permettent aux utilisateurs professionnels de monter ou descendre dans la hiérarchie pendant l'analyse de données. Dans certains cas, il est utile de créer des calculs qui parcourent la hiérarchie. Par exemple, imaginez une dimension de produit comportant une catégorie de produit, une souscatégorie de produit, et le produit lui-même. Pour chaque niveau dans la hiérarchie, vous souhaitez ajouter un calcul qui mesure la façon dont les membres de chaque niveau contribuent au total des ventes du parent. Cela s'appelle un calcul de pourcentage du parent, étant donné qu'il doit parcourir la hiérarchie pour retourner la valeur souhaitée. MDX et DAX fournissent des fonctions pour travailler sur les données organisées dans une hiérarchie, et créer des calculs de pourcentage du parent ; toutefois, les fonctions MDX semblent plus simples et faciles à utiliser. Par exemple, dans MDX, c'est cette expression qui donne le pourcentage du parent dans la dimension Product. [Measures].[Sales Amount] / ([Product].[Product Categories].CurrentMember.Parent, [Measures].[Sales Amount]) Dans DAX, on utilise l'expression plus complexe qui suit pour créer le même calcul de pourcentage du parent. IF( ISFILTERED(Product[Product]) ,[Sales]/CALCULATE([Sales],ALL(Product[Product])) ,IF( ISFILTERED(Product[Subcategory]) ,[Sales]/CALCULATE([Sales],ALL(Product[Subcategory])) ,1 ) ) Cumuls personnalisés Bien qu'une somme des données uniforme soit applicable dans de nombreux scénarios, dans certaines situations, vous pouvez souhaiter exercer un contrôle plus précis sur le cumul de vos données. Par exemple, c'est le cas des modèles de finance où vous avez un graphique des comptes (généralement, dans un format parent-enfant) avec une logique de cumul spécifique requises pour chaque compte. Comme indiqué ici, pour calculer la marge brute (Gross Margin) on prend les ventes nettes (Net Sales) moins le coût total des ventes (Total Cost of Sales), et pour calculer le bénéfice d'exploitation (Operating Profit), on prend la marge brute (Gross Margin) moins les frais d'exploitation (Operating Expenses). Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 15 Les modèles multidimensionnels fournissent non seulement la prise en charge native des hiérarchies parent-enfant, mais également une intelligence comptable intégrée, qui vous permet d'appliquer facilement des opérateurs unaires et des formules MDX au niveau du compte, et de piloter le cumul de données. Dans les modèles tabulaires, l'intelligence comptable ou parent-enfant n'est pas intégrée, mais vous pouvez créer votre propre solution au moyen d'une combinaison de colonnes et de mesures calculées pour générer la hiérarchie parent-enfant et appliquer le cumul personnalisé. Mesures semi-additives De manière générale, les mesures semi-additives sont celles qui agrègent uniformément toutes les dimensions à l'exception de la date. Par exemple, le solde d'ouverture et le solde de clôture sont des mesures semi-additives. Pour ces mesures, vous devez appliquer une logique spéciale pour synthétiser correctement les données par période de temps. En effet, le solde du stock en cours du mois de mars n'est pas la somme du stock en cours de tous les jours du mois de mars. En outre, ce solde doit également fonctionner correctement pour tous les attributs de date, comme le trimestre et l'année. Par exemple, le solde du stock en cours du Trimestre 1 doit être le même que celui enregistré le 31 mars (en supposant que le 31 mars est le dernier jour du Trimestre 1). Les modèles multidimensionnels prennent directement en charge les mesures semiadditives avec des fonctions d'agrégation spéciales comme First Child, Last Child, FirstNonEmptyChild, et LastNonEmptyChild. Si ces fonctions d'agrégation ne répondent pas à vos besoins de logique spécifiques, vous pouvez également écrire des formules MDX personnalisées. Les modèles tabulaires proposent des fonctions similaires telles que ClosingBalanceMonth et OpeningBalanceMonth. Il existe des fonctions supplémentaires qui s'appliquent à tous les autres attributs de date, comme le trimestre et l'année. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 16 Time Intelligence Presque toutes les solutions BI que vous rencontrez requièrent l'assistant Time Intelligence. Les fonctions Time Intelligence permettent de calculer des résumés de l'année jusqu'à ce jour et des comparaisons avec l'année précédente. MDX et DAX fournissent des fonctions Time Series ; toutefois, chacune utilise une conception du modèle de données légèrement différente. Les modèles multidimensionnels fournissent des fonctions Time Intelligence prêtes à l'emploi au moyen de l'Assistant Analysis Services Business Intelligence. Avec cet Assistant, les calculs de temps peuvent être ajoutés à la création de la dimension de temps et appliqués à toutes les mesures du modèle. Bien que l'assistant ne soit qu'un moyen parmi d'autres de créer des calculs de temps, vous pouvez également écrire vos propres calculs MDX dans un modèle multidimensionnel. Dans les modèles tabulaires, bien qu'il n'y ait pas d'assistant de création de calculs Time Intelligence, vous pouvez les créer manuellement en créant des formules DAX qui exploitent plusieurs fonctions, dont TOTALMTD, TOTALYTD et SAMEPERIODSLASTYEAR. Indicateurs de performance clés Les indicateurs de performance clés (KPI) identifient les mesures spéciales que vous souhaitez surveiller par rapport à une valeur cible, au moyen d'un indicateur visuel tel qu'un feu rouge. Les modèles tabulaires et multidimensionnels prennent en charge l'un comme l'autre les indicateurs de performance clés. Les deux permettent d'affecter une cible pour une mesure et de comparer la valeur réelle à la cible pour évaluer l'état des performances de la mesure. Les modèles multidimensionnels permettent en outre d'évaluer la tendance de l'indicateur de performance clé et d'affecter un indicateur visuel distinct pour représenter la façon dont il évolue dans le temps. Conversion monétaire Les conversions monétaires permettent de convertir des données monétaires d'une ou plusieurs devises sources dans une ou plusieurs devises de déclaration. Par exemple, si les transactions de vente de votre organisation sont en EUR, en JPY et en USD, pour consolider la déclaration des ventes de l'ensemble de votre entreprise, vous devez convertir les transactions de vente dans une ou plusieurs devises de déclaration. Pour implémenter des conversions monétaires dans l'un des deux types de modélisation, vous devez avoir accès aux taux de change et inclure ces données dans votre modèle. Dans les modèles multidimensionnels, vous pouvez utiliser l'Assistant Analysis Services Business Intelligence pour créer des calculs de conversion monétaire MDX optimisés pour prendre en charge plusieurs sources et devises de déclaration. Dans un modèle tabulaire, vous pouvez créer votre propre solution de conversion monétaire en créant des formules DAX. Jeux nommés Dans la modélisation multidimensionnelle, les jeux nommés vous permettent de retourner un ensemble de membres de dimension fréquemment utilisés dans des applications de reporting. Par exemple, vous pouvez créer un jeu nommé pour retourner les 12 derniers mois. En créant ce jeu nommé dans votre cube, vous pouvez définir de manière centralisée la logique du jeu, pour accéder au jeu à partir de n'importe quelle application de reporting, et pour simplifier la logique enregistrée dans votre application de reporting. Pour créer le jeu nommé des 12 derniers mois (Last 12 Months), vous pouvez utiliser l'expression MDX suivante. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 17 Create Set CurrentCube.[Last 12 Months] As Max([Date].[Calendar].[Month]).Lag(11):Max([Date].[Calendar].[Month]) Les jeux nommés ne sont pas disponibles dans la modélisation tabulaire. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 18 Accès aux données et stockage Performances et extensibilité Les performances et l'extensibilité sont des facteurs importants qui doivent être considérés pour la réussite d'une solution BI. Chaque expérience de modélisation repose sur des technologies sous-jacentes différentes ; chacune a des caractéristiques de performances et des comportements distincts que vous devez comprendre pour choisir la solution de modélisation adéquate. Modèles multidimensionnels Comme indiqué précédemment dans ce livre blanc, les modèles multidimensionnels Analysis Services utilisent un moteur OLAP. Sur disque, les données OLAP peuvent être stockées dans les architectures de données MOLAP et ROLAP. Dans MOLAP, les données sont stockées sur le disque dans un format multidimensionnel optimisé, avec une compression 3x classique. Dans ROLAP, les données sont stockées dans la base de données relationnelle source. En matière de performances, il est généralement utile de distinguer : les performances des requêtes et les performances de traitement. Performance des requêtes Les performances des requêtes affectent directement la qualité de l'expérience de l'utilisateur. Par conséquent, il s'agit du benchmark principal utilisé pour évaluer la réussite d'une implémentation OLAP. Analysis Services fournit plusieurs mécanismes pour accélérer les performances des requêtes, notamment des agrégations, la mise en cache, et l'extraction de données indexées. En outre, vous pouvez améliorer les performances des requêtes en optimisant la conception de vos attributs de dimension, des cubes, et des requêtes MDX. L'option principale qui s'offre à vous pour optimiser les performances des requêtes est d'utiliser des agrégations. Une agrégation est un récapitulatif précalculé des données, utilisé pour améliorer les performances des requêtes dans les modèles multidimensionnels. Lorsque vous interrogez un modèle multidimensionnel, le processeur de requêtes Analysis Services décompose la requête en demandes pour le moteur de stockage OLAP. Pour chaque demande, le moteur de stockage tente d'abord de récupérer les données du cache du moteur de stockage en mémoire. Si aucune donnée n'est disponible dans le cache, il tente de récupérer les données d'une agrégation. Si aucune agrégation n'est présente, il récupère les données des partitions d'un groupe de mesures. En concevant des agrégations de données, vous identifiez le schéma d'agrégation le plus efficace pour votre charge de travail d'interrogation. Lorsque vous concevez des agrégations, vous devez tenir compte des avantages qu'elles offrent pour l'interrogation, et comparer cela au temps requis pour créer et actualiser les agrégations. En fait, ajouter des agrégations inutiles peut empirer les performances des requêtes, car si les correspondances sont rares, l'agrégation est déplacée dans le cache de fichier en risquant d'écraser ce qu'il contient. La mise en cache est tout aussi essentielle pour régler les performances des requêtes Analysis Services. Vous devez disposer de suffisamment de mémoire pour stocker toutes les données de dimension, et pour mettre en cache les résultats de la requête. Lors de l'interrogation, la mémoire est principalement utilisée pour stocker les résultats mis en cache dans le moteur de stockage et les caches du processeur de requêtes. Pour optimiser les avantages de la mise en cache, vous pouvez augmenter la réactivité de la requête en préchargeant les données dans l'un des deux caches ou dans les deux caches à la fois. Cette opération peut être effectuée avant l'exécution d'une ou plusieurs requêtes, ou en utilisant l'instruction de création du cache. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 19 Performances de traitement Le traitement est l'opération qui actualise les données d'une base de données Analysis Services. Plus les performances de traitement sont rapides, plus vite les utilisateurs peuvent accéder aux données actualisées. Analysis Services fournit plusieurs mécanismes pour optimiser les performances de traitement, notamment la conception efficace des dimensions, les agrégations, les partitions, et une stratégie de traitement économique (qui compare, par exemple, le traitement incrémentiel, l'actualisation complète et la mise en cache proactive). Vous pouvez utiliser les partitions pour séparer des données de mesure (généralement, les données de la table de faits) en unités physiques. Une utilisation efficace des partitions peut améliorer les performances des requêtes, les performances de traitement, et faciliter la gestion des données. Pour chaque partition, vous pouvez utiliser une conception d'agrégation et une planification de l'actualisation distinctes, ce qui peut optimiser considérablement les performances. Pour chaque table de faits, vous pouvez également associer les partitions MOLAP et ROLAP. Ce type de stratégie de partitionnement permet l'interrogation en temps réel, ou bien d'accéder à des jeux de données trop gros pour être traités dans un cube. Des techniques d'optimisation des requêtes et du traitement telles que celles-ci peuvent vous aider à mettre à l'échelle vos modèles multidimensionnels afin de gérer des téraoctets de données. Pour plus d'informations sur le réglage des performances, consultez Guide des performances d'Analysis Services 2008 R2 (http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/10/10/analysis-services-2008-r2performance-guide.aspx). Modèles tabulaires Les modèles tabulaires utilisent le moteur d'analyse xVelocity, qui permet le traitement des données en mémoire, ou DirectQuery, qui transmet les requêtes à la base de données source pour exploiter les fonctions de traitement des requêtes. Les avantages des bases de données en colonnes et du traitement des données en mémoire sont équivalents. Les bases de données en colonnes autorisent une compression plus élevée que le stockage traditionnel, généralement une compression 10x, en fonction de la cardinalité des données. La cardinalité des données se focalise sur la caractérisation de la distribution des données au sein d'une colonne unique. Une cardinalité élevée signifie que les valeurs des données dans une colonne sont fortement uniques (par exemple, le nombre de clients). Une cardinalité faible signifie que les valeurs des données dans une colonne peuvent se répéter (par exemple, le sexe et l'état civil). Plus la cardinalité des données est faible, plus la compression est élevée, ce qui signifie que davantage de données peuvent tenir dans la mémoire à tout moment. Comme pour les modeleurs de données, il est important de comprendre la cardinalité de vos données pour déterminer les jeux de données qui conviennent mieux à votre modèle tabulaire, et les besoins de mémoire associés pour prendre en charge le modèle. Performances des requêtes Lorsqu'un utilisateur interroge un modèle tabulaire, le moteur exécute des analyses de mémoire pour récupérer les données et calculer les agrégations à la volée, sans besoin de traiter les E/S du disque. Cette approche peut considérablement améliorer les performances des requêtes sans nécessiter de paramétrage spécial, ni une gestion de l'agrégation. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 20 La façon la plus simple et adaptée d'optimiser les performances des requêtes pour les modèles tabulaires est d'augmenter la mémoire disponible. Du point de vue de l'extensibilité, le volume des données est essentiellement limité par la mémoire physique. Il est recommandé de disposer d'une mémoire suffisante pour contenir toutes les données dans votre modèle tabulaire. Dans les scénarios où la mémoire est limitée, le moteur en mémoire fournit également une pagination de base, selon la mémoire physique. De plus, il existe des paramètres de configuration côté serveur qui permettent de gérer plus précisément la mémoire disponible pour les modèles tabulaires. Pour plus d'informations sur la configuration de la mémoire du modèle tabulaire, consultez Propriétés de mémoire (http://msdn.microsoft.com/fr-fr/library/ms174514.aspx). Performances de traitement En ce qui concerne les performances de traitement, les modèles tabulaires se distinguent des modèles multidimensionnels de deux façons : Contrairement aux modèles multidimensionnels, les modèles tabulaires chargent les données directement dans la mémoire et ne requièrent pas l'écriture des données sur le disque. Étant donné que les modèles tabulaires ne catégorisent pas les données en dimensions et groupes de mesures, le traitement peut être beaucoup plus souple. Ces deux différences se traduisent par moins de surcharge à chaque actualisation des données, ce qui, à son tour, accélère le bouclage et augmente l'agilité. Prenons l'exemple suivant. Dans votre organisation, il est courant que les représentants se déplacent régulièrement dans plusieurs régions. Les utilisateurs souhaitent consulter les chiffres de vente cumulés par dernière région visitée et par affectation des représentants. Dans un modèle multidimensionnel, pour accomplir cette tâche, vous devez commencer par actualiser la dimension de l'organisation des ventes. Une fois la dimension commerciale actualisée, vous devez actualiser la partition du groupe des mesures de vente. L'actualisation de la partition des ventes met à jour les données et les agrégations détaillées. L'étape finale de la préparation des données (recommandée en tant que meilleure pratique) consiste à interroger le cache des requêtes Analysis Services pour récupérer les informations utiles sur le disque en mémoire. Selon votre conception de modèle de données, la taille des données, et la technique de traitement choisie (traitement incrémentiel ou complet), cela peut prendre plusieurs minutes ou plusieurs heures. La bonne nouvelle est qu'il existe plusieurs techniques ayant fait leurs preuves. Les professionnels BI les utilisent tous les jours pour optimiser l'empreinte du traitement des modèles multidimensionnels en équilibrant les besoins de traitement des données et les demandes de disponibilité des données. Examinons à présent le même scénario dans un modèle tabulaire. Dans le modèle tabulaire, les concepts de dimensions et de groupes de mesures n'existent pas. En revanche, les données sont organisées dans des tables qui possèdent des relations entre elles. Supposez que les données de l'organisation des ventes et les données de vente se trouvent dans leurs tables respectives et soient liées par une relation basée sur le représentant. Avec cette conception, lorsque vous actualisez la table d'organisation des ventes, elle met automatiquement à jour toutes les colonnes calculées, les relations, et les hiérarchies utilisateur impactées. Cela signifie que les données de vente reflètent automatiquement les cumuls par région actualisés, sans besoin d'être retraitées. Cette souplesse offre des avantages considérables lorsque vos dimensions changent rapidement et les données doivent refléter les dernières mises à jour. En outre, notez qu'avec les modèles tabulaires, il n'est pas nécessaire de créer des agrégations, d'écrire des données sur le disque, ou d'interroger le cache des requêtes pour obtenir les données en mémoire. Avec les modèles tabulaires, les données passent directement du disque à la mémoire et sont prêtes à l'emploi. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 21 Similairement aux modèles multidimensionnels, les modèles tabulaires vous permettent d'éclater les données de vos données en partitions, en évitant les traitements de données inutiles. Par exemple, vous pouvez diviser les tables en plusieurs partitions : une partition mensuelle pour chaque mois de l'année en cours, et une partition annuelle pour chacune des années précédentes. Cette approche permet d'isoler les partitions qui nécessitent une actualisation. Contrairement aux modèles multidimensionnels, notez que bien que vous puissiez traiter plusieurs tables en parallèle, vous ne pouvez pas traiter les partitions d'une seule table en parallèle. DirectQuery Comme alternative au mode en mémoire xVelocity des modèles tabulaires, les professionnels BI peuvent créer des modèles tabulaires à l'aide du mode DirectQuery. DirectQuery est disponible pour les modèles tabulaires avec des sources de données SQL Server. Il permet de contourner le traitement des données en passant les requêtes et les calculs DAX à la base de données source pour tirer parti des fonctionnalités de SQL Server. Cela peut s'avérer utile pour les grands volumes de données nécessitant une actualisation fréquente. Avec DirectQuery, toutefois, les colonnes calculées et certaines fonctions DAX ne sont pas prises en charge. Programmabilité AMO (Analysis Management Objects) est l'API de développement et de gestion des objets Analysis Services. Elle a été créée avant la modélisation tabulaire, puis ajoutée à Analysis Services. Par conséquent, elle contient uniquement les classes des objets traditionnellement associés à la modélisation multidimensionnelle : les cubes, les dimensions, les groupes de mesures, les scripts MDX, etc. Toutefois, cette API peut également être utilisée pour développer et gérer les modèles tabulaires. Il s'agit d'un avantage de la modélisation multidimensionnelle et tabulaire encapsulé par le modèle sémantique BI. Alors que les modèles tabulaires et dimensionnels ont une structure interne différente, BISM présente la même interface externe. Bien que vous puissiez utiliser AMO pour programmer des modèles tabulaires et multidimensionnels à la fois, il s'agit d'une interface moins intuitive pour les modèles tabulaires. Pour plus d'informations et un exemple de code AMO de modèle tabulaire, consultez Didacticiels sur Analysis Services (http://msdn.microsoft.com/fr-fr/library/hh231701.aspx) Sécurité Il est important de se doter d'une stratégie de sécurité des données appropriée pour garantir que chaque utilisateur a accès aux données appropriées. Les organisations doivent contrôler l'accès aux données afin d'assurer la sécurité de leurs ressources et se conformer aux règlements sur le respect de la vie privée. Les modèles multidimensionnels et les modèles tabulaires proposent un ensemble de fonctions robustes qui répondent à une vaste gamme d'exigences de sécurité. Il existe de légères variantes dans les fonctions, qu'il est important de comprendre avant de choisir l'expérience de modélisation la plus adaptée à vos besoins de sécurité. Dans Analysis Services, vous gérez des projets multidimensionnels et tabulaires en créant un rôle et en accordant des autorisations au rôle. Ensuite, vous ajoutez des noms d'utilisateur et des groupes Windows au rôle, leur accordant ainsi des accès en fonction des autorisations du rôle. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 22 Sécurité au niveau de la ligne/de l'attribut Dans un projet multidimensionnel, vous utilisez le concept de sécurité des données de dimension pour gérer l'accès au niveau de la ligne. Pour implémenter la sécurité des données de dimension pour un rôle, vous accordez ou refusez l'accès aux données de dimension en sélectionnant ou en désélectionnant des membres de la dimension. Par ailleurs, vous pouvez implémenter une configuration de sécurité plus complexe en définissant un jeu de membres à l'aide d'une expression MDX. Vous pouvez également indiquer si le rôle doit être accordé ou refusé aux nouveaux membres de la dimension. L'accès que vous accordez ou refusez à un membre de la dimension impacte l'accès d'un rôle aux membres associés de la dimension. Par exemple, si vous limitez un rôle de sorte qu'il ne puisse accéder qu'à la sous-catégorie de produit Vélos tout terrain, les membres du rôle peuvent uniquement consulter la catégorie de produit Vélos et les produits et les ventes qui appartiennent à la sous-catégorie Vélos tout terrain. Dans un projet tabulaire, vous implémentez la sécurité au niveau de la ligne en accordant l'accès aux lignes d'une table. Dans un projet tabulaire SQL Server Data Tools, vous accordez les autorisations en entrant une expression DAX qui filtre les lignes d'une table. Le rôle a accès aux nouvelles lignes de la table si elles correspondent au filtre DAX. L'accès que vous accordez à une ligne dans une table impacte l'accès d'un rôle aux lignes des tables associées. Si deux tables ont une relation un-à-plusieurs, les filtres de lignes sur la table du côté « un » de la relation filtrent les lignes de la table du côté « plusieurs » de la relation, mais pas dans l'autre sens. Par exemple, si vous limitez un rôle de sorte qu'il ne puisse voir que la ligne Vélos tous terrain dans la table de souscatégorie de produit, les membres du rôle peuvent uniquement voir les lignes des tables des produits et des ventes liées à la sous-catégorie Vélos. Toutefois, les membres du rôle peuvent toujours voir toutes les lignes de la table de catégories de produits (vélos, habillement, et ainsi de suite.). Sécurité dynamique Votre organisation peut être amenée à limiter l'accès aux données en fonction de l'ID d'un utilisateur ou d'autres critères dynamiques. Par exemple, les associés sont uniquement autorisés à voir leurs propres performances et leurs données RH. Toutefois, la création d'un rôle de sécurité pour chaque personne dans une entreprise peut s'avérer impossible. Au lieu de cela, vous pouvez implémenter la sécurité dynamique, qui permet de piloter la logique de sécurité en fonction de l'ID d'un utilisateur ou d'autres critères dynamiques. Les projets tabulaires et multidimensionnels prennent en charge la sécurité dynamique. Vous pouvez configurer une sécurité dynamique basée sur l'utilisateur si vos données contiennent une relation entre les ID d'utilisateur et les données auxquelles les utilisateurs peuvent accéder, en incluant la relation dans l'expression MDX ou DAX que vous utilisez pour gérer les autorisations. Sécurité au niveau de la cellule et sécurité avancée Pour de nombreuses applications, il est nécessaire de restreindre l'accès aux données avec des critères plus complexes qu'une simple ligne dans une table. Prenez, par exemple, une enquête de satisfaction des employés qui partage les résultats agrégés d'une enquête de rétroaction. Ces modèles contiennent souvent des données très sensibles, et chaque réponse à l'enquête doit rester confidentielle. Même si le modèle ne contient pas les noms des utilisateurs, si l'échantillon est restreint, l'identité des participants peut être facilement déduite. Dans ce cas, vous pouvez implémenter une logique plus complexe qui tient compte de la taille de l'échantillon, et donne accès à la mesure résultante uniquement si le nombre de réponses est supérieur à un certain seuil. En outre, il peut y avoir des questions et des combinaisons métriques spécifiques que vous souhaitez restreindre de sorte qu'elles ne soient visibles qu'aux RH. Les projets multidimensionnels vous permettent d'implémenter directement des fonctionnalités de sécurité avancées qui ne sont pas disponibles dans un projet tabulaire. Dans un projet multidimensionnel, vous pouvez implémenter la sécurité au niveau des cellules pour restreindre l'accès à une cellule ou à un groupe de cellules spécifique dans votre modèle. La sécurité au niveau des cellules n'est pas fournie dans un modèle tabulaire. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 23 En outre, les projets multidimensionnels vous permettent de contrôler l'utilisation des valeurs totales affichées, d'accorder ou de refuser l'autorisation d'explorer des données au niveau du détail, et de créer des membres par défaut pour chaque rôle. Dans un projet multidimensionnel, les valeurs de synthèse préagrégées sont calculées lorsque les données sont traitées dans un modèle pour améliorer les temps de réponse des requêtes. Par exemple, la valeur « Ventes de tous les produits » est une valeur précalculée. La sécurité des données de la dimension est appliquée après le traitement des données, par conséquent, même si un utilisateur ne peut accéder qu'à la catégorie Vélos, par défaut, la valeur « Ventes de tous les produits » est la somme des ventes pour Accessoires, Vélos, Habillement, et ainsi de suite. Il peut s'agir ou non de la valeur que vous souhaitez afficher pour les membres du rôle. Dans ce cas, si vous souhaitez que la valeur « Ventes de tous les produits » soit limitée à la valeur « Ventes de vélos », vous devez activer les valeurs totales affichées. En activant les valeurs totales affichées, vous limitez les valeurs de synthèse, de sorte qu'elles doivent être égales à la somme des valeurs de détail qu'un rôle a l'autorisation de voir. Cette modification affecte le temps de réponse des requêtes, car les valeurs de synthèse doivent être calculées au moment de la requête. Les projets tabulaires ne précalculent pas les valeurs de synthèse, par conséquent, ces dernières sont toujours égales à la somme des valeurs de détails, autrement dit, les valeurs totales affichées sont toujours activées dans un modèle tabulaire. Dans un modèle multidimensionnel, vous pouvez accorder l'autorisation d'exploration au niveau du détail rôle par rôle. Dans un modèle tabulaire, les rôles ne sont pas utilisés pour contrôler l'accès à la fonction d'extraction. En revanche, tous les rôles peuvent explorer les valeurs au niveau du détail. Dans un modèle multidimensionnel, vous pouvez spécifier un membre par défaut pour chaque attribut dans une dimension. Un membre par défaut se comporte comme un filtre qui est appliqué automatiquement. Par exemple, si le membre par défaut de la valeur Année est 2012, alors par défaut, seules les données de 2012 s'affichent. Toutefois, un utilisateur peut choisir de consulter les données d'une année différente, ou d'afficher les données de toutes les années. Dans un modèle multidimensionnel, vous pouvez configurer un membre par défaut pour chaque attribut qui s'applique à tous les rôles, ou bien définir un membre par défaut différent rôle par rôle. Vous ne pouvez pas spécifier une valeur par défaut dans un module tabulaire. En revanche, si vous souhaitez un filtre par défaut, vous devez configurer cette fonction dans votre outil de reporting et d'analyse. Résumé Dans SQL Server 2012, Microsoft a introduit le modèle sémantique Business Intelligence (BISM) pour prendre en charge une vaste gamme de besoins de reporting et d'analyse. Les deux expériences de modélisation contenues dans le modèle BISM, la modélisation multidimensionnelle et la modélisation tabulaire, offrent des fonctionnalités supplémentaires qui vous permettent de choisir les fonctions les mieux adaptées à vos besoins. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 24 La modélisation tabulaire propose une expérience de modélisation facile d'accès, avec des fonctions qui satisferont la plupart des besoins de reporting et d'analyse. La plupart des utilisateurs savent utiliser des tables et des relations et apprennent rapidement à implémenter la logique métier au moyen du langage DAX, apparenté à Excel. Cette facilité d'utilisation, ainsi que la modélisation simple et souple offerte par l'expérience tabulaire, permettent de développer rapidement les solutions. Le moteur en mémoire et orienté colonne xVelocity offre un temps de réponse aux requêtes extrêmement rapide pour des jeux de données qui peuvent contenir des milliards d'enregistrements. Les modèles tabulaires prennent en charge tous les outils de reporting et d'analyse qui génèrent les requêtes MDX, comme Excel et Reporting Services, ainsi que Reporting Services Power View, qui génère les requêtes DAX. La modélisation multidimensionnelle propose des fonctions étendues pour vous aider à relever les défis BI plus complexes et à grande échelle. Le modèle de données multidimensionnel associé à MDX fournit des fonctionnalités prêtes à l'emploi pour la création de modèles élaborés, et l'implémentation d'une logique métier complexe. Le stockage des données sur disque, les agrégats précalculés, et la mise en cache dans la mémoire, permettent aux modèles multidimensionnels de monter en charge jusqu'à plusieurs téraoctets et fournissent des réponses rapides aux requêtes. Avec la sécurité au niveau des cellules, vous pouvez répondre à des exigences de sécurité rigoureuses. En résumé, la modélisation tabulaire propose une expérience de modélisation simplifiée, avec des fonctions susceptibles de satisfaire la plupart de vos besoins de reporting et d'analyse. Si vous avez besoin d'une modélisation, d'une logique métier, ou d'une sécurité complexes, ou si vous cherchez une solution à très grande échelle, la modélisation multidimensionnelle est probablement mieux adaptée à vos besoins. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 25 Le tableau suivant résume et compare les caractéristiques des modèles multidimensionnels et tabulaires. Groupe de fonctions Critère de décision Temps d'implémentation Courbe d'apprentissage Multidimensionnel/ Tabulaire / / Modèle de données Relations des données Modèle de données Hiérarchies Modèle de données Fonctionnalités de modélisation supplémentaires Langage de calcul Logique métier Logique métier Logique métier / / Calculs / / / Fonctions d'agrégation / Modélisation multidimensionnelle Temps d'implémentation plus long. Modélisation tabulaire Temps d'implémentation plus court. L'apprentissage de la modélisation dimensionnelle et du langage MDX est plus ardu, mais ces solutions fournissent des fonctions complexes en mode natif. Un-à-plusieurs. Plusieurs-à-plusieurs. Les relations de référence doivent être explicitement modélisées. La modélisation relationnelle et le langage DAX apparenté à Excel sont plus simples à aborder mais si vos besoins sont complexes, des expressions DAX sophistiquées seront requises. Un-à-plusieurs. La relation plusieurs-à-plusieurs requiert des expressions DAX. La modélisation des relations entre les tables crée des relations de référence. Prise en charge native pour les hiérarchies standard. Les hiérarchies parent-enfant requièrent des expressions DAX. Perspectives et extractions Prise en charge native pour les hiérarchies standard, déséquilibrées, et parent-enfant Perspectives, traductions, actions, extractions, procédures stockées et écriture différée. MDX Prise en charge native des calculs courants et complexes. Sum, Count, Min, Max, Distinct Count, None, ByAccount, AverageOfChildren, FirstChild, LastChild, FirstNonEmpty et LastNonEmpty. DAX Prise en charge native des calculs courants et de nombreux calculs complexes. Sum, Count, Min, Max, Average, DistinctCount, et différentes fonctions Time Intelligence comme FirstDate, LastDate, OpeningBalanceMonth et ClosingBalanceMonth. Groupe de fonctions Logique métier Critère de décision Logique de hiérarchie Multidimensionnel/ Tabulaire Modélisation multidimensionnelle Fonctions d'exploration des hiérarchies standard et parent-enfant. / Logique métier Logique métier Indicateurs de performance clés Conversion monétaire Accès aux données et stockage Accès aux données et stockage Montée en charge Accès aux données et stockage Accès aux données et stockage Sources de données / / / Performances / Réel, objectif, état et tendance, avec des indicateurs graphiques Prise en charge de la conversion multidevise à l'aide de l'Assistant Business Intelligence. Très grande échelle (plusieurs téraoctets) Index et mesures préagrégées stockés sur le disque. Données de la dimension et résultats des requêtes mis en cache dans la mémoire. Compression de données d'environ 3x. Bases de données relationnelles. / Langage de requête / MDX Modélisation tabulaire Fonctions DAX pour explorer les hiérarchies de type parent-enfant, expressions DAX pour implémenter la logique dans les dimensions standard. La logique de hiérarchie est généralement plus difficile avec DAX. Réel, objectif et état avec des indicateurs graphiques. Implémentation à l'aide d'expressions DAX. Grande échelle (des milliards d'enregistrements) Stockage des données basé sur la colonne, en mémoire. Compression de données d'environ 10x. Bases de données relationnelles, Excel, texte, flux OData, Azure Data Market, Analysis Services. DAX MDX (mode In-Memory uniquement) Groupe de fonctions Accès aux données et stockage Critère de décision Stockage de données Multidimensionnel/ Tabulaire / Accès aux données et stockage Accès aux données et stockage Compression des données / Programmabilité Sécurité Sécurité / / - Moins de fonctions. - Plus de fonctions. Modélisation tabulaire In-Memory - Toutes les données mises en cache dans la mémoire utilisent le moteur d'analyse orienté colonnes xVelocity DirectQuery - Données stockées dans SQL Server 2012. ROLAP - Dimensions, faits et données agrégées stockés dans une base de données relationnelle. Généralement 3x. Généralement 10x. Excel Services, Reporting services, Microsoft PerformancePoint, et d'autres outils clients. Reporting Services Power View, Excel, Reporting Services, PerformancePoint, et d'autres outils clients. / Outils clients Accès aux données et stockage Modélisation multidimensionnelle MOLAP - Dimensions, faits et données agrégées stockés sur le disque. Données de la dimension et résultats des requêtes mis en cache dans la mémoire. Reporting Services Power View pris en charge dans les futures versions de SQL Server. XMLA, ASSL, ADOMD.NET, MSOLAP, AMO, Windows PowerShell pour AMO. Développée pour être utilisée avec les modèles multidimensionnels. Sécurité au niveau des membres de la dimension et au niveau de la cellule Sécurité dynamique. XMLA, ASSL, ADOMD.NET, MSOLAP, AMO, PowerShell pour AMO. Disponible mais moins intuitive dans les modèles tabulaires. Sécurité au niveau des lignes Sécurité dynamique. Pour plus d'informations Site Web SQL Server http://www.microsoft.com/sqlserver/ TechCenter SQL Server http://technet.microsoft.com/fr-fr/sqlserver/ Centre de développement SQL Server http://msdn.microsoft.com/fr-fr/sqlserver/ Blog de l'équipe Analysis Services et PowerPivot http://blogs.msdn.com/b/analysisservices/ Analysis Services http://msdn.microsoft.com/fr-fr/library/bb522607.aspx Modélisation multidimensionnelle (SSAS) http://msdn.microsoft.com/fr-fr/library/hh230904.aspx Modélisation tabulaire (SSAS Tabulaire) http://msdn.microsoft.com/fr-fr/library/hh212945.aspx Fonctionnalités par mode de serveur ou par type de solution (SSAS) http://msdn.microsoft.com/fr-fr/library/hh212940(v=sql.110).aspx Avez-vous trouvé ce document utile ? Nous apprécions vos commentaires. Sur une échelle de 1 (faible) à 5 (excellent), quelle note donneriez-vous à ce document ? Expliquez pourquoi. Par exemple : Avez-vous attribué une bonne note car le document fournit de bons exemples, contient des captures d'écran très utiles, est clairement rédigé, ou pour d'autres raisons ? Avez-vous attribué une mauvaise note car le document fournit de mauvais exemples, contient des captures d'écran pas claires ou est mal rédigé ? Vos commentaires nous aident à améliorer la qualité des livres blancs que nous publions. Envoyez vos commentaires. Choisir un paradigme de modélisation des données dans SQL Server 2012 Analysis Services 29
