SQL Server Analysis Services 2008, quoi de neuf dans la boite?

Je voulais publier cette article un peu plus tôt,…; il vaut mieux tard que jamais:

Un peu d’historique …

Microsoft est entrée dans le marché du décisionnel avec la version 7 de SQL Server, il y a déjà près de 11 ans. A cette époque, seul des structures de taille importante pouvaient investir dans ce type de technologie. Depuis, le décisionnel se démocratise dû à des coûts attractifs et des connaissances de mieux en mieux maîtrisées.

SQL Server 2005 est incontestablement un serveur d’analyse d’une grande richesse avec une nouvelle architecture basée sur l’UDM, Unified Dimensional Model, qui intègre à la fois un modèle relationnel et un modèle agrégé.

Cette richesse n’a pas toujours été facile à digérer et les bonnes pratiques laissaient de temps en temps de coté avec des performances pas toujours au rendez-vous. Suite à ce constat, SQL Server Analysis Services 2008 proposent de nouveaux outils afin de guider le concepteur dans la modélisation, la création d’agrégats et la supervision du serveur.

Présentons les nouveautés de ce serveur en matière de conception et exploitation.

Vers une meilleure modélisation

Avant de rentrer dans le cœur du sujet, représentons les concepts et les bonnes pratiques de modélisation des liens entre attributs.

Les relations d’attributs

Tous les attributs sont liés directement ou indirectement à l’attribut clé. En créant des hiérarchies, il est facile de créer des relations redondantes.

Ainsi la relation Customer à City n’est pas redondante, ce qui n’est pas le cas pour la relation suivante Customer à Country qui peut être obtenu indirectement.

Cette redondance a bien sur un impact sur le stockage de la base de données.

De plus les attributs possèdent un type qui peut être qualifiés de rigide ou de flexible.

Une relation entre attribut est dite rigide si elle n’évolue pas dans le temps comme par exemple, le sexe d’une personne.

Lorsque les types de relation sont flexibles, les agrégats sont automatiquement supprimés lors du traitement de la dimension et doivent être reconstruit à l’aide de l’instruction « process index » contrairement aux relations rigides. Par défaut, le type de relation est flexible.

Ce point est important à prendre en compte lorsque la fenêtre pour traiter le cube est limitée.

Les hiérarchies naturelles

A partir de lien d’attributs, il est possible de définir des hiérarchies naturelles et non naturelles.

Une hiérarchie est dite naturelle lorsqu’une unicité est définie pour chaque parent de la hiérarchie.

Ainsi la hiérarchie Country-State-City-Customer est naturelle tandis que Age-Gender-Customer ne l’est pas. En effet, si nous remontons la hiérarchie, à partir d’un sexe donné, il n’est pas possible de définir le parent ; le seul moyen est de naviguer par l’attribut clé.

D’un point de vue performance, une hiérarchie naturelle est matérialisée alors qu’une hiérarchie non naturelle est calculée à la volée.

Sous SQL Server 2005, il était facile de créer une hiérarchie non naturelle car l’option de la dimension KeyDuplicate était à IgnoreError.

Comment implémenter les bonnes pratiques ?

Le nouvel outil BID 2008, Business Inelligence Developpment, propose une nouvelle interface simplifié afin de guider l’utilisateur dans l’implémentation des bonnes pratiques de conception.

Nous avons vu qu’il est important d’utiliser avec précaution des hiérarchies non naturelles et d’éviter de créer des attributs redondants, ce qui auraient des impacts négatifs sur les performances.

Afin d’avoir une synthèse des relations entre les attributs, BID propose un nouvel onglet, l’attribute relationShip designer.

Ainsi, sous la figure ci-dessous nous pouvons aisément voir à l’aide du message d’avertissement qu’il y a une redondance entre les relations MonthsàCalendar QuarteràCalendar Semester et Monthsà Calendar Semester.

Cette version apporte donc un ensemble de règles de validation de bonnes pratiques. L’utilisateur est donc guidé, ce qui continue à démocratiser la Business Intelligence.

Il est d’ailleurs possible d’accéder et de modifier ces règles de validation et en particuliers de les activer ou désactiver soit au niveau du projet ou alors au niveau d’un élément plus fin comme la dimension.

Vers une meilleure maitrise des outils d’administrations

Les agrégats

Analysis services 2008 permet aux administrateurs et personnes en charge de la maintenance des cubes, de générer des sous totaux et de les stocker dans un environnement OLAP.

Une requête MDX générée par un client (type Excel) peut ramener un grand nombre de données à des granularités différentes. Générer des agrégats pré-calculés et pré-stockés permet d’assurer un temps de récolte des données beaucoup plus rapide.

Lorsqu’une requête demande des données à un niveau autre que le niveau le plus fin, le moteur procède, en temps réel, à l’agrégation des données jusqu’à obtenir le niveau de granularité désiré.

Ces données sont tout d’abord lues dans la partition puis agrégées par le moteur. La génération d’agrégats stockés permet de s’affranchir de cette phase d’agrégation en temps réel en stockant les valeurs pré-calculées dans un fichier d’agrégats.

Stocker des agrégats dans une base multidimensionnelle est une façon efficace d’améliorer sensiblement les performances en termes de temps de réponse.

Ces agrégats vont être stockés dans des fichiers, il est donc intéressant de se fixer un ratio Taille sur disque Agrégats / taille sur disque Données.

La taille occupée sur disque est un facteur très important dans le dimensionnement de l’architecture disque. Prenons l’exemple de 4 dimensions composées d’une hiérarchie, chacune d’entre elle comporte 5 niveaux. Si l’on souhaite générer des agrégats pour l’ensemble des croisements alors il faut considérer 5x5x5x5 – 1 = 624 agrégats. La taille sur disque sera alors exponentielle, il s’agira donc de se poser les questions suivantes :

- Quels niveaux doivent être ou non agrégés ?

- Peut-on générer des sous ensembles d’agrégats, fils d’ensembles déjà existants ?

- Quels sont les avantages et inconvénients sur l’utilisation de données agrégées ?

- Quelle taille disque doit-on considérer pour nos agrégats ?

- Quel pourcentage d’optimisation est-on en droit d’attendre de ces optimisations ?

Dès lors en version 2005 il est possible via le profiler de capturer les vecteurs d’agrégats correspondants aux différentes requêtes évaluées par le serveur.

Les évènements « QuerySubcube » du profiler nous renvoient des messages de type :

1000000000,00000000000000000000000000000000000000,00000000000,00000000000,101000000101000011,0000000000,0000000000,00000000000

Chaque SubCube correspond au résultat d’une requête et la colonne « duration » du profiler nous permet de voir le temps d’exécution de la requête ou subcube considéré.

Le résultat est une vision binaire de l’exécution, chacun des 1, 0 correspond à l’attribut d’une dimension. Un 1 correspond à une occurrence, un 0 à la position All de l’attribut considéré.

Le décryptage du vecteur d’agrégats est alors possible via un outil « Agregation Manager » dans SQL Server 2005 permettant ainsi de visionner le croisement et créer un agrégat sur celui-ci.

Décodage d’un vecteur d’agrégats

Dans Analysis Services 2008 cette notion d’agrégats a été ajoutée à l’outil de développement BIDS. Un nouvel onglet dans l’affichage du cube permet la mise en place fine d’agrégats.

Les DMVs (Dynamic Management Views) dans Analysis Services 2008

Une des évolutions majeures d’ Analysis Services 2008 est les DMVs mises à disposition pour assurer l’administration de la solution.

Les DMVs permettent maintenant de répondre aux questions suivantes :

- Trouver et arrêter une requête en cours

- Avoir des informations sur l’utilisation d’une requête, qui quand …

- Avoir des informations sur l’état du serveur …

Le concept reste le même que sur les DMVs SQL Server. Les demandes et résultats sont visibles sur simple requête.

Syntax: Select * from $system.”NameOfTheDiscover”

Les principales DMVs :

Le script suivant permet l’intégration des DMVs à Windows Server 2008 via PowerShell.

Ce script permet l’exécution d’une DMV sur un serveur Analysis Services local ou distant.

##############################################################################

##

## Return the results of a SSAS 2008 DMV

##

## ## Retrieve data (from the local SSAS server. default instance)

## -Sql "SELECT * FROM $SYSTEM.Discover_Connections"

##

## ## Retrieve data (from a remote server, default instance)

## -Sql "SELECT * FROM $SYSTEM.Discover_Connections" -server "WIN2K8"

##

## ## Retrieve data (from a remote named instance)

## -Sql "SELECT * FROM $SYSTEM.Discover_Connections" -server "WIN2K8\SALES"

##

##############################################################################

param(

    [string] $server = "(local)",

    [string] $sqlCommand = $(throw "Please specify a query."),

   [string] $provider = "msolap"

  )

##write-Output "sqlCommand: $sqlCommand" ## debug

## Prepare the connection string based on information provided

$connectionString = "Provider=$provider; " +

                    "Data Source=$server; "

## Connect to the data source and open

$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString

$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand, $connection

$connection.Open()

## Fetch the results, and close the connection

$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command

$dataset = New-Object System.Data.DataSet

[void] $adapter.Fill($dataSet)

$connection.Close()

## Return all of the rows from their query

$dataSet.Tables[0]

Les sauvegardes

20% des cubes ont aujourd’hui une taille supérieure à 50Go. Le backup de ces solutions est une étape critique dans l’exploitation et l’administration de toute solution multidimensionnelle.

Il s’agit alors de trouver le meilleur moyen pour effectuer un backup d’une solution OLAP dans les meilleures conditions avec les meilleures performances possibles.

Analysis Services 2005 dispose d’un système de backup qui fonctionne pour la plupart des cubes en dessous de 20GB. Les performances sont alors dégradées sur des cubes à très forte volumétrie. La solution palliative reste la copie de fichier.

Analysis Services 2008 dispose d’un système de backup complètement revu qui permet de garantir un haut niveau de performance sur des cubes de tous types et toutes tailles. La courbe de backup reste linéaire sur la volumétrie alors que celle issue d’Analysis Services 2005 devenait exponentielle.

Cette avancée permet de garantir des temps de backups constants sur des volumétries qui souvent évoluent de façon significatives dans le temps.

En conclusion…

L’arrivée de SQL Server 2008 apporte des avancées considérables en termes de souplesse de développement, tuning de la solution et supervision des cubes Analysis Services.

Le développeur est aujourd’hui capable, via l’interface de développement, de maitriser toutes les parties d’une solution Analysis Services 2008.

L’administrateur dispose aujourd’hui de réels moyens de supervision et d’administration de la solution OLAP pouvant ainsi assurer un suivi et une qualité de service optimale.