Archive

Articles taggués ‘SSAS’

Le programme de Microsoft pour 2012 [1/2]

On en saura sans doute beaucoup plus à l’occasion des Microsoft Techdays 2012 (prévus les 7,8 et 9 Février prochain), mais voici déjà une brève présentation des différents produits et évolutions qui vont désormais constituer l’écosystème BI autour de SQL-Server 2012. SQL-Server « Denali » – c’est son nom de code – est prévu en version commerciale pour la fin de l’année. (Pour l’heure il n’est disponible qu’en Release Candidate depuis le site officiel). Petit tour d’horizon en deux parties.

Mont McKinley ou "Denali"

Le mont McKinley, ou "Denali" en langue locale athapascane, plus haut sommet d'Amérique du Nord, culmine à 6194m

1.1/ SQL-Server 2012 aka « Denali »

Pour sa mouture 2012 de SQL-Server, Microsoft met en avant 2 fonctionnalités nouvelles pour son SGBD:

  • Le AlwaysOn qui est une solution de reprise en cas de sinistre (disaster recovery plan) afin de garantir une haute disponibilité de la base de données. Cette fonctionnalité est sensée garantir un « failover » deux fois plus rapide en cas de sinistre. Voir cette vidéo de présentation.
  • Les ColumnStore Index (projet « Apollo ») pour proposer de meilleures performances sur des requêtes de type jointure en étoile. Ces indexes colonnes viennent enrichir la liste des solutions d’optimisation et de tuning existantes sur les précédentes versions (indexes, indexes cluster, statistics…). Plus d’info dans ce livre blanc sur les bénéfices d’Apollo.

On notera aussi d’autres innovations intéressantes sur SQL-Server 2012:

  • Le Change Data Capture (CDC) qui supporte maintenant les bases de données Oracle
  • Le SSMA (SQL Server Migration Assistant), qui permet d’automatiser la migration de base de données non SQL Server vers SQL Server
  • Juneau: une nouvelle interface de développement rassemblant Visual Studio et Management Studio dans la même IDE
  • La fonction FileTable permettant de créer un objet base de données lié à un fichier localisé dans un dossier du système de fichier.
  • Etc…

1.2/ BI Semantic Model (BISM)

La vraie nouveauté conceptuelle de SQL-Server 2012 est l’arrivée du BI Semantic Model (ou BISM). Il s’agit là d’un vrai modèle Business de l’entreprise qui vient rassembler et enrichir les précédentes notions d’UDM (SSAS) et de report model (Report Builder).

Ce modèle est composé de 3 couches:

  • une couche d’accès aux données offrant un accès en ROLAP, MOLAP, Vertipaq (nouveau serveur OLAP en mémoire, faisant partie du projet Apollo), ou direct query
  • une couche Business logique qui offre une structuration des données soit en OLAP via SSAS et MDX ou bien via powerpivot et DAX
  • une couche data model qui supporte les données tabulaires et multi-dimensionnelles

Ces BI semantic models pourront être créés à partir de Visual Studio ou de PowerPivot.

Vous l’aurez compris, Microsoft tente une nouvelle fois de présenter une alternative métier crédible à la  notion d’ »univers » cher à Business Objects. En l’intégrant avec toutes ses technologies existantes (ce qui n’était pas le cas de feu les report models), il semble cette année mettre toutes les chances de son coté. A voir si les BISM tiennent le choc sur le gros volumes.

A noter qu’il n’est pas prévu pour l’instant de possibilité de migrer des UDM en BISM.

BI Semantic Model architecture

Le BISM dans le nouveau écosystème SQL-Server 2012

1.3/ Integration Services

Les nouveautés 2012 concernant l’intégration de données sont articulées autour d’outils complémentaires à SSIS: Data Quality Services et le déjà célèbre Master Data Services.

  • Data Quality Services (DQS): c’est un outil destiné à maintenir et améliorer la qualité des données de l’entreprise en provenance de sources tierces (clients, fournisseurs, …).
    • DQS permet aussi la modélisation métier des données. Par le biais d’une interface client il sera possible de créer des bases de connaissance (type thésaurus).
    • Ces bases contiendront des règles d’épuration, de validation ainsi que les données de référence. DQS est aussi un outil précieux pour le recherche de doublons et dans l’évolution des bases de connaissance.
    • Enfin, les différentes règles de nettoyage pourront être utilisées au niveau les flux de données dans Integration services (SSIS) via un nouveau composant.
  • Master Data Services (MDS) qui existait déjà dans la version précédente de SQL-Server a été grandement amélioré. Il permet de définir les données de références, de gérer les métadonnées. Grâce à son intégration comme plugin d’Excel, il permet de filtrer et modifier les données avant de les visualiser dans Excel. On regrettera qu’il n’existe apparemment toujours pas de système de « lock » pour gérer les accès simultanés…

1.4/ Analysis Services

SSAS est indiscutablement le parent pauvre des nouveautés de cette version Denali. Tellement pauvre que dans la foulée de la présentation l’an dernier son sort a agité la blogosphère spécialisée (voir cet article entre autre). De nombreux experts ont caressée l’hypothèse que Microsoft soit en train de s’employer à mettre son moteur OLAP sur une voie de garage.

Il est vrai que le manque de souplesse des modèles UDM sous-jacents à SSAS a souvent été pointé du doigt tant il représentait une difficulté à résoudre les problématiques métiers les moins standards. Cela a conduit les experts du monde entier à « tordre » l’outil dans tous les sens afin de faire rentrer une logique métier calquée sur du relationnel, dans une modélisation multi-dimensionnelle.

C’est dans cette perspective qu’il faut voir l’arrivée de BISM, Vertipaq et DAX. Ces outils sont destinés à compléter SSAS plus qu’à le remplacer. Car comme cela a été exprimé plus haut, les volumes très importants de données nécessiteront toujours de « dénormaliser » et de passer en dimensionnel. Ces « concurents » au couple SSAS/MDX permettront juste de se simplifier la vie pour modéliser des problématiques métiers relationnelles sur des volumes faibles.

La suite de cet article sur les nouveautés de SQL-Server 2012 sera publiée début Février. Au programme: Reporting Services, Crescent, PowerPivot, Barcelona et Azure !

Liens utiles :

Formation en soirée « MDX », le 18 janvier à Paris

Venez rencontrer les équipes Homsys à l’occasion de cette Formation !
Au programme : échanges avec nos experts, ateliers et bonnes pratiques.

Homsys vous propose de participer à ses Formations en Soirée avec une session à Paris « Initiation au MDX dans le cadre d’un cube SSAS », le mercredi 18 janvier à partir de 18h30.

Cette formation a pour objectif de vous donner un aperçu du MDX et de son application dans un cube Analysis Services.

Vous y apprendrez notamment :

  • Les notions les plus importantes du MDX
  • Les fonctions MDX les plus utiles pour un cube SSAS
  • La création d’indicateurs via script MDX dans un cube
  • La création de jeu de données (SET) et de membres calculées
  • L’utilisation de la « time intelligence »

Pré-requis : avoir déjà des connaissance sur SSAS (idéalement avoir suivi les deux premières FES SSAS).

Rendez-vous dans nos locaux, au 38 rue de Ponthieu – 75008 Paris.

Pour en savoir plus : site Homsys
Pour vous inscrire* :
- Mail : klalandre[at]homsys.com
- En ligne : sur le site Homsys

*Participation gratuite après confirmation par Homsys.

Formation en soirée « SSAS avancé », le 19 octobre à Paris


Venez rencontrer les équipes Homsys à l’occasion de cette Formation !
Au programme : échanges avec nos experts, ateliers et bonnes pratiques.

Homsys vous propose de participer à ses Formations en Soirée avec une session à Paris « SQL Server Analysis Services (SSAS) avancé », le mercredi 19 octobre à partir de 18h30.

Cette formation vous donnera un aperçu des fonctionnalités avancées d’un cube Analysis Services. Vous y apprendrez notamment :

  • Les relations évoluées (référencé, many to many)
  • Le paramétrage des agrégations et des partitions
  • La création d’indicateurs MDX
  • La création d’actions (notamment drillthrough)
  • La gestion des traductions
  • L’implémentation des perspectives
  • A savoir déployer et sécuriser les données dans Analysis Services

Pré-requis : vous disposez déjà d’une première expérience BI et connaissez les bases de SSAS (création de la Data source View, création d’une dimension, création d’un cube simple).

Rendez-vous dans nos locaux, au 38 rue de Ponthieu – 75008 Paris.

Pour en savoir plus : site Homsys

Pour vous inscrire* :
- Mail : klalandre[at]homsys.com
- En ligne : sur le site Homsys

*Participation gratuite après confirmation par Homsys.

Ecrire une requête MDX personnalisée sous Excel 2007

Il peut être parfois utile d’exécuter une requête MDX directement depuis Excel. Ainsi, on ne s’encombre pas de toutes les dimensions / mesures du cube, et ainsi, on exécute la requête beaucoup plus rapidement.
On peut ainsi utiliser directement un tableau croisé dynamique que l’on mettre à jour régulièrement, afin d’avoir une information toujours à jour.

La méthode est simple : utiliser un fichier datasource (.odc) personnalisé avec sa requête MDX

Pour cela :

1. Téléchargez ce fichier, et enregistrez-le dans le dossier ‘Mes Data Sources’ du dossier Mes documents.

2. Ouvrez-le avec un éditeur de texte classique, et modifiez les lignes suivantes :

  • ligne 8 <title>Detail_Forecast all regions</title> : remplacez par un titre approprié
  • ligne 12 <o:Name>Detail_Forecast all regions</o:Name> : remplacez par un titre approprié. Attention, c’est le nom qui apparaîtra sous Excel dans la liste des connexions.
  • ligne 18 <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<Nom_de_la_base_OLAP>;Data Source=<Serveur_OLAP>;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error</odc:ConnectionString> : remplacez par la datasource voulue (que l’on trouve généralement dans un ficher de cube Excel), ou remplacez celle-ci par les valeurs appropriées.
  • ligne 20 <odc:CommandText>Insérer la requête MDX ici</odc:CommandText> : C’est ici que vous allez insérer la requête MDX. Attention de ne pas faire apparaître de paramètres (@Parameter, CONSTRAINED) car Excel ne les supporte pas.

3. Enregistrez le fichier.

4. Ouvrez Excel, et aller sur l’onglet ‘Données’.

5. Cliquez sur ‘Connexions existantes’ et sélectionnez votre fichier datasource précédemment enregistré

6. Une fois ouvert, le résultat de la requête s’affichera sous forme de table. Si vous souhaitez plutôt l’utiliser sous forme de Pivot, à l’étape 4, allez à l’onglet ‘Insertion’, puis sélectionnez ‘TblCroiséDynamique’, et choisissez le fichier datasource.

Categories: Trucs & astuces Tags: , , ,

Formation en soirée « Introduction à SSAS » le 28 juin à Paris

Venez rencontrer les équipes Homsys à l’occasion de cette Formation ! au programme : échanges avec nos experts, ateliers et bonnes pratiques.

Homsys vous propose de participer à ses Formations en Soirée avec une première session à Paris « Introduction à SQL Server Analysis Services (SSAS) », le mardi 28 juin à partir de 18h30.

    Cette formation a pour objectif de vous donner les bases afin de bien démarrer un projet Analysis Services. Vous y apprendrez :

  • A comprendre l’utilisation de SQL Server Analysis Services pour vos futurs projets d’analyse décisionnelle avec notamment ses forces mais aussi ses faiblesses
  • A maîtriser la création de cubes multidimensionnels avec BIDS
  • A être en mesure de modéliser et de réaliser des dimensions et des mesures dans une solution Analysis Services
  • A savoir déployer et sécuriser les données dans Analysis Services
  • A utiliser Analysis Services au travers de la puissance de navigation offerte par Excel 2010

Pré-requis : vous disposez déjà d’une première expérience BI

Rendez-vous dans nos locaux, au 38 rue de Ponthieu – 75008 Paris.

Pour en savoir plus : site Homsys

Pour vous inscrire* :
- Mail : klalandre[at]homsys.com
- En ligne : sur le site Homsys

*Participation gratuite après confirmation par Homsys.

Réaliser un système décisionnel avec SQL Server 2008 R2

Sébastien Fantini, expert Microsoft au sein d’Homsys, publie un des premiers ouvrages en français sur la nouvelle suite BI de Microsoft : « Business Intelligence avec SQL Server 2008 R2 – Maîtrisez les concepts et réalisez un système décisionnel ».

Il s’adresse à tous les membres d’une équipe décisionnelle : chef de projet, architecte, développeur ETL, développeur de rapports, assistance à la maîtrise d’ouvrage (AMOA). Les concepts clés du décisionnel sont détaillés tout au long du livre et mis en application concrètement au travers d’un cas. Ainsi, au cours des différents chapitres, le lecteur va utiliser les différents outils de la suite SQL Server pour bâtir progressivement le système décisionnel d’une société virtuelle, Distrisys. L’ouvrage regorge de solutions concrètes et professionnelles et de bonnes pratiques issues des retours d’expérience de l’auteur. Sont abordés les sujets suivants : – L’architecture des serveurs et le choix des licences – La modélisation de l’entrepôt de données – La conception du cube Analysis Services – La réalisation des différents types de flux d’alimentation ETL avec Integration Services – L’utilisation d’Excel et de PowerPivot pour exploiter les données décisionnelles – La réalisation de rapports opérationnels et décisionnels avec Reporting Services.

Nouveauté juillet 2010 aux Editions ENI – Lien Amazon

SSAS : Aggréger des mesures de cube sous forme de médiane

Lors d’une récente mission chez un client nous avons été confronté au besoin d’agréger des mesures de cube sous forme de médiane.

Intérêt d’agréger une mesure en médiane

Concrètement, ce besoin revient à retourner la valeur médiane d’une mesure pour les dimensions en cours, plutôt que classiquement la somme ou la moyenne.  Sur un exemple basique, pour une mesure Chiffre d’Affaire, autour d’un axe d’analyse Région, le besoin client est de retourner la valeur médiane du CA de chacune des régions :

EX :    [Bretagne=10M€,   Sud Ouest=17M€, PACA=23M€,   Rhone Alpes=31M€,   IDF=51M€ ]

Le CA au niveau national sera la valeur médiane de la liste des valeurs régions, soit 23M€.

Fonctionnellement prendre la valeur médiane (plutôt que la valeur moyenne) a pour effet de minimiser l’impact des valeurs extrêmes, et donc de minimiser le « bruit » que pourrait provoquer des erreurs de saisie ou de chargement.

EX : Si l’on rajoute [Alsace=300M€] à la liste précédente (ce qui pourrait correspondre à une erreur de saisie – avec un zéro de trop)

La valeur médiane au niveau national est 27M€

=> L’impact de l’erreur de saisie de l’opérateur Alsacien sur le CA national est minimisé. Une agrégation en moyenne aurait « tiré vers le haut » le CA national (moyenne = 72M€)

Sur l’exemple donné – qui est volontairement simpliste – le bénéfice de l’agrégation en médiane est plutôt léger, mais sur une liste de valeurs plus importante, il y a de nombreux cas fonctionnels où la médiane d’une liste de valeurs est bien plus représentative (et intéressante pour l’utilisateur) que la moyenne .

Problématique

Le problème vient du fait que les agrégation de type médiane n’est pas supporté nativement par Analysis Services.

Type d'agrégation supportés par SSAS

Types d'agrégation supportés par SSAS

Solution

La solution que nous avons proposée est d’utiliser la fonction MDX  MEDIAN(). C’est une fonction mathématique de base qui retourne la valeur médiane d’une mesure pour un « set » donné.

Elle doit être appelée avec 2 paramètres :

  • L’ensemble de valeurs sur lequel « ventiler » la valeur médiane, le « set »
  • et la mesure à agréger

Dans le cas du CA par région cela donnerait une mesure calculée du style  :

CA_National =     MEDIAN( [Geography].[Region].Members, (= liste de valeurs)
[Measures].[CA]) (= mesure à agréger)

=> Cette solution fonctionne et retourne effectivement la valeur médiane du CA de chaque région

En allant plus loin

On remarquera qu’en réalité la solution proposée n’agrège pas – à proprement parler – en médiane : elle calcule la valeur médiane d’une mesure existante. Et ce n’est pas tout à fait la même chose ! Car cette mesure existante a son propre type d’agrégation…

C’est à dire, qu’elle calcule la valeur médiane, d’une liste de valeurs qui restent à leur type d’agrégation par défaut. Dans l’exemple, la formule d’agrégation ne fonctionne qu’au niveau national : la valeur retournée est la médiane des valeurs CA au niveau région, valeurs qui sont elle même agrégées… dans leur type d’agrégation par défaut définit dans SSAS (c’est à dire Somme, Moyenne etc…)

Le besoin de notre client était plus complexe que cela : il souhaitait une valeur médiane d’une mesure quelque soit l’axe d’analyse et quelque soit le niveau de navigation dans la dimension.

Par exemple sur une mesure DélaisRésolutionIncident, le souhait est de voir retournée la valeur médiane (de toute les valeurs disponibles) quelque soit l’axe d’analyse : que ce soit au niveau d’un mois, d’une année, d’un service, d’une région, d’une sous région etc.

Nous avons proposé une solution calée sur la précédente : utiliser la fonction MEDIAN en passant cette fois comme « set »  l’ensemble des lignes  de faits disponibles. Cela donne quelque chose comme :

MEDIAN_DelaisResIncident =     MEDIAN( [DimFait].[DimFait hierarchy].[DimFait fact line].Members, (= ensemble des lignes de fait)
[Measures].[ValDelaisResolutionIncident]) (= mesure à agréger)

Pour cette formule nous avons du créer dans le cube – via le DSV – une dimension de fait dont le nombre de lignes au niveau le plus fin est exactement le même que celui de la table de fait. Cette dimension « virtuelle » ne sert qu’à définir le « set » de valeurs.

=> Cette solution fonctionne et permet une réelle agrégation de mesure sous forme de médiane.

PS : Ce second exemple illustre bien l’intérêt de la médiane évoqué en introduction : si par exemple un incident est resté non clôturé plusieurs mois, alors que généralement ils le sont en quelques minutes, cette anomalie « pourrira » l’indicateur de moyenne. L’indicateur de médiane en revanche sera bien plus pertinent pour l’utilisateur.

Temps de réponse

Si la solution proposée fonctionne sur le papier et avec nos jeux de tests, elle s’est révélée un peu décevante dans la pratique. Avec l’augmentation du nombre de lignes de faits, le temps de réponse du cube explose lorsque l’on fait appel à la mesure utilisant la médiane.

En effet, avec un nombre de ligne de l’ordre de plusieurs dizaines de milliers d’enregistrements, le temps de réponse dépasse les 2 minutes (le « time out » de notre outil de restitution). A titre de comparaison, l’agrégation classique en moyenne, sur les mêmes données met moins d’une seconde…

=> Cette solution est donc à utiliser avec précaution.

Analyse

Mais pourquoi cela prend-t-il autant de temps ?

Cette fonction MDX  MEDIAN est à l’origine une fonction mathématique, pas une fonction d’agrégation. Les valeurs ne sont donc pas pré-calculées avec cette médiane comme elles le seraient avec les agrégations en Moyenne ou en Somme. La solution proposée est en réalité une utilisation détournée de la fonction MEDIAN

Dans tous les exemples d’utilisation que l’on trouve de cette fonction,  le premier paramètre (le « set ») est de cardinalité très faible. Alors que dans le cas exposé ci-dessus l’ensemble de « ventilation » est l’ensemble des lignes de faits, donc un ensemble à cardinalité plutôt élevée.

La fonction Median ne permet pas de pré-aggrégation dans la mesure ou son algorithme de résolution nécessite 2 « passages » sur la liste de valeur (là où la Moyenne n’en nécessite qu’un) : un passage pour trier toutes les valeurs, un autre pour prendre la moyenne des deux valeurs du milieu de la liste triée.

Dans le premier exemple avec le CA par région, en réalité le cube se base sur les pré-agrégations sous forme de Somme du CA stockées pour chaque région, et calcule ensuite la médiane de ces pré-agrégations. L’algorithme des 2 « passages » n’est en fait déroulé que sur une liste de 22 valeurs. Tandis que dans notre cas il est déroulé sur le nombre de lignes de fait.

=> La fonction MDX  MEDIAN n’est donc à utiliser pour agréger une mesure que sur des tables de faits de faible population.