Tag SSAS

Analysis Services dans Azure!

Fleid 2 commentaires

Je suis sûr que vous avez noté l’arrivée récente de SSAS Tabular en mode PaaS dans Azure. Je voulais rapidement revenir dessus parce que ça faisait au moins 2 ans qu’on le sentait venir, et que finalement ça valait le coup d’attendre.

aas_1.png

Rappel : je bosse chez Microsoft maintenant. Même si ceux qui me connaissent savent que ça ne changera pas grand-chose à mon avis sur les produits, je préfère le rappeler pour être 100% transparent 😉

Azure Analysis Services c’est tout simplement la possibilité de déployer ses modèles SSAS Tabular dans le cloud sans se soucier du tout de l’installation ou de la configuration d’un serveur. Si on ajoute à ça le fait qu’il est désormais possible de développer un modèle Tabular dans SSDT en mode intégré (sans disposer d’une instance workspace), on peut donc aller du prototype à la production sans jamais toucher une ISO d’installation de SQL Server. Cool 😉

« Oui mais moi j’aime mieux Multidim ! » dirons certains. J’y répondrais qu’il n’est pas écarté qu’on voit les cubes rejoindre Tabular dans le service (le flou est maintenu dans l’annonce : « Support for multidimensional models will be considered for a future release, based on customer demand ». Mais surtout je dirais que SSAS Tabular est devenu vraiment solide avec SQL Server 2016, et qu’il est urgent de lui donner une deuxième chance (performance, support du many-to-many, nouvelles fonctions DAX…).

Je vous fais un petit tour d’horizon de comment c’est génial, en montant un datamart et le cube associé en moins de 30 minutes.

  • Au programme:
    • Création d’une base SQL Azure pour notre datamart
    • Création d’une instance Azure Analysis Services
    • Création d’un modèle SSAS Tabular dans Visual Studio (SSDT)
    • Déploiement du modèle dans Azure Analysis Services
    • Accès au modèle avec Power BI, Excel et SSMS

Tout commence dans le nouveau portail Azure : https://portal.azure.com. Si vous n’avez pas de compte Azure pas de problème, vous pouvez tout essayer gratuitement

  • Première étape : Création de la base de données sur Azure SQL Database pour mon datamart, histoire de tout faire en PaaS

Pour un DWH de taille respectable on devrait plutôt utiliser Azure SQL Data Warehouse, mais pour s’amuser une SQL Database suffit:

aas_2.png

Je vais la pré-remplir d’un sample: AdventureWorksLT v12. Notez que c’est une option à la création de la base, parfait quand on veut juste jouer avec le produit:

aas_3

Je valide, et on peut laisser tourner et passer à la suite en attendant 😉

  • Deuxième étape : la création de notre instance Azure Analysis Services

Cette fois-ci on regarde du côté Intelligence + Analytics:

aas_4.png

Ne vous embêtez pas pour le pricing tier, D1 suffit pour notre petit test. Idéalement on devrait mettre la base SQL et Analysis Services dans le même groupe de ressources, et donc la même location. Par grave pour notre test si ce n’est pas le cas:

aas_5.png

Là encore je valide et on laisse tourner.

  • Troisième étape: dans SSDT (SQL Server Data Tools, les templates data/BI pour Visual Studio) on va créer un nouveau projet SSAS Tabular

Pas de panique si vous n’avez pas SSDT, il est désormais disponible en download direct et gratuit, tout comme SSMS d’ailleurs. N’hésitez pas à télécharger la version la plus récente, elle se base sur Visual Studio 2015, et est capable de gérer des projets SSAS/SSIS/SSRS de SQL Server 2012 à 2016

New Project > BI > Analysis Services > AS Tabular:

aas_6

Profitez du mode intégré, c’est tellement plus pratique:

aas_7

De là on va pouvoir se connecter à notre datamart : Model > Import From Data Source:

aas_66

aas_8

Un petit guide pour savoir comment configurer la connexion:

aas_9

On passe sur l’impersonation pour le moment avec une option par défaut:

aas_91

On veut ensuite choisir nos tables:

aas_92

De quoi construire un petit modèle, avec 2 tables de fait et 4 dimensions :

aas_93

Ça charge, et on peut valider que le modèle ressemble bien à quelque chose grâce à la vue en diagramme:

aas_94

On peut ajouter des mesures, changer la direction du filtre en bidirectionnel entre les 2 tables de fait… Ou s’en passer 😉

La partie marrante c’est le déploiement. Dans les propriétés du modèle:

aas_95

On configure la destination du déploiement. Retenez le nom du serveur (asazure://…) c’est celle qu’on utilisera plus tard pour se connecter à SSAS avec Excel ou Power BI :

aas_96

Et lorsqu’on déploie:

aas_97

Après une demande de credentials pour le processing du cube post déploiement:

aas_98

On obtient un cube déployé dans les nuages !

  • Quatrième et dernière étape: on va se connecter à notre cube avec SSMS, Power BI ou encore Excel

Le nom du serveur on l’a déjà, c’est celui qu’on a utilisé plus tôt au moment du déploiement (asazure://…).

Power BI: Get Data > SSAS

aas_991

Excel: Get External Data > SSAS

aas_992

Notez qu’il faut choisir l’option User Name / Password, et utiliser le compte Azure qui vient de créer le service (c’est juste pour le test, évidemment il est possible de créer toute une liste d’utilisateurs via Azure AD):

aas_993

Enfin, avec SSMS, si vous êtes intégré avec Azure Active Directory ça marchera tout seul, sinon voir cet article (c’est simple):

aas_994

Magique non ? 😉

Si ça vous plait, je vous encourage à l’essayer ainsi qu’à suivre le compte Twitter @Azure_AS pour être mis au courant de toutes les nouveautés.

Power Pivot vers Tabular : I need you!

Fleid 7 commentaires

J’ai besoin d’un coup de main de votre part à tous ! Hier j’écrivais un édito sur le futur de la BI Microsoft, et l’une des idées (améliorer la montée de Power Pivot vers SSAS Tabular) me tient particulièrement à cœur.

J’ai donc écrit un petit message sur le sujet aux copains MVP, et Chris Webb m’a rappelé l’existence d’un ticket Connect de Marco Russo en plein dedans.

Alors vous me voyez arriver avec mes gros sabots, ce serait vraiment vraiment cool si vous alliez voter sur ces différents items pour les faire monter au plus haut dans la liste. Ca aura un vrai impact, les items bien notés étant effectivement lus et considérés par l’équipe de développement de SQL Server.

Une vraie équipe!

Pour voter il suffit juste de disposer d’un compte « Microsoft » (Live ID/Hotmail/celui utilisé pour vos certifs/moi c’est une adresse gmail…), vous pourrez en créer un sur place si besoin, ça prend 2 minutes.

Voici les items concernés:

Merci de voter, et merci de faire passer le mot !

Un peu de MDX de bon matin…

Fleid 5 commentaires

Je pique exceptionnellement le flambeau à François pour moi aussi vous causer MDX. Évidemment ça ne sera pas aussi pointu (voir piquant) que lui, mais pour une fois j’ai quelque chose à dire sur ce sujet…

A la base mon besoin était assez simple : créer un graph dans SSRS avec une date de départ et une date de fin fixées par des attributs de dimension de SSAS.

Typiquement : j’ai une dimension projet, dans cette dimension j’ai un attribut date de départ théorique (DDT), et un attribut date de fin théorique (DFT). Je veux que peu importent mes faits, mon graph couvre cette période théorique :

MDX - Le Besoin

A mon sens la grande méthode pour faire ça c’est de créer un set de dates allant de la DTD à la DTF. Ce set doit évidemment être composé d’éléments de la dimension Temps utilisée dans le groupe de mesure concerné, il va donc falloir passer des attributs de la dimension projet ([Projet].[DDT].&[…] et [Projet].[DDF].&[…]) à la dimension temps ([Temps].[Date].&[…]).

Le MDX étant un langage particulièrement efficace en termes de manipulation de chaînes de caractères (hur hur), ça va être un bonheur… Trêve de sarcasme, on peut utiliser quelques fonctions VBA en MDX qui vont ici nous sauver la mise (à adapter en fonction des formats de vos dates):

MDX - La requête qui en cast!

Ce n’est pas ultra élégant, mais ça marche… pas !

Le message d’erreur :  La fonction Axis1 attend une expression d’ensemble de tuples pour l’argument . Une expression de chaîne ou numérique a été utilisée.

Notez que la manipulation de projection des dates sur la dimension Temps marche, elle. C’est autre chose qui coince.

Le problème c’est qu’au moment où TimeSet est évalué, on n’a pas de CURRENTMEMBER sur la dimension Projet. En effet, en même temps (ON 1) qu’on essaye de résoudre TimeSet, on parcourt la dimension Projet, le CURRENTMEMBER n’est donc pas figé. On reçoit donc ALL dans le MEMBER_VALUE, que le STRTOMEMBER n’arrive pas à mapper correctement sur la dimension Temps. C’est le drame.

 

Tout ça parce que comme en SQL, en MDX le moteur interprète la requête dans un certain ordre. En faisant un gros raccourci, ici il commence par le FROM, puis le WHERE (slicer), puis les axes en itérant sur le 1 pour résoudre le 0. Dans une étape, tous les éléments sont interprétés en même temps : ça coince effectivement sur le TimeSet.

J’ai du mal à trouver des articles intéressants sur ce sujet, n’hésitez pas à soumettre les vôtres.

Pour comprendre le problème, on peut repartir d’une requête MDX plus simple, et basée sur AdventureWorks (je ne caste plus ma Start Date sur la dimension temps, je veux juste la faire apparaître en ON 1 comme dans mon cas réel):

WITH

    MEMBER Test1 AS [Product].[Start Date].CURRENTMEMBER.MEMBER_UNIQUE_NAME

 

SELECT

        [Measures].[Order Count] ON 0,

        STRTOMEMBER(Test1)

        *

        [Product].[Product].[Product].MEMBERS ON 1

 

FROM    [Adventure Works]

Le résultat, ma date est remplacée par ALL (« Tous les Produits ») dans le STRTOMEMBER(Test1) :

Mauvaise pioche

Si maintenant je filtre mon produit en slicer (typiquement l’endroit où on mettra le paramètre du rapport pour SSRS), la requête devient valide :

WITH

    MEMBER Test1 AS [Product].[Start Date].CURRENTMEMBER.MEMBER_UNIQUE_NAME

 

SELECT

        [Measures].[Order Count] ON 0,

        STRTOMEMBER(Test1) ON 1

       — *

       — [Product].[Product].[Product].MEMBERS ON 1

 

FROM    [Adventure Works]

WHERE    [Product].[Product].&[447]

La c'est bon!

Ici il existe un CURRENTMEMBER au moment où on évalue Test1, tout roule. La solution est trouvée pour mon rapport, à moi de placer mon STRTOMEMBER(@Parameter) dans le WHERE.

Mais oui mais vous voulez itérer en même temps sur le TimeSet/Test1 et les projets/produits ? Il va falloir siouxer et passer l’attribut dans une mesure.

WITH

    MEMBER [Measures].Test1 AS [Product].[Start Date].CURRENTMEMBER.MEMBER_UNIQUE_NAME

 

SELECT

      {[Measures].[Order Count],Test1} ON 0,   

        [Product].[Product].[Product].MEMBERS ON 1

 

FROM   [Adventure Works]

Ce qui donne:

Mouais

Si ça marche pour le cas simple, on perd le cas d’application premier avec le TimeSet. Je n’ai malheureusement pas de solution à l’instant T. Si ça me vient je compléterai.

On a donc vu deux choses : comment passer d’un attribut d’une dimension à une autre grâce aux commandes VBA, et l’ordre d’exécution d’une requête MDX et comment il peut casser vos jolies sets et membres calculés. J’aimerai en rajouter une troisième, la propagation des contraintes sur les attributs dans les dimensions. François en avait parlé aux derniers JSS, j’en remets rapidement une couche ici.

Si quand vous passez votre filtre en WHERE vous utilisez la clef de la dimension, ça marche :

WITH

    MEMBER Test1 AS [Product].[Start Date].CURRENTMEMBER.MEMBER_UNIQUE_NAME

 

SELECT

        [Measures].[Order Count] ON 0,

        STRTOMEMBER(Test1) ON 1

 

FROM    [Adventure Works]

WHERE    [Product].[Product].&[447]

Ca marche!

Par contre si vous utilisez un attribut de plus haut niveau, ça ne marchera pas :

WITH

    MEMBER Test1 AS [Product].[Start Date].CURRENTMEMBER.MEMBER_UNIQUE_NAME

 

SELECT

        [Measures].[Order Count] ON 0,

        STRTOMEMBER(Test1) ON 1

 

FROM    [Adventure Works]

WHERE    [Product].[Model Name].&[Cable Lock]

Ca ne marche plus :/

En effet, si on affiche les relations entre les attributs de la dimension (via SSDT BI), on verra que le Model Name ne contraint pas la Start Date, puisque si les contraintes peuvent remonter l’arbre (Model Name vers Product), elles ne le redescendent pas à partir de là (Product vers Start Date).

La classe ce SSDT BI 2012!A vous d’utiliser les bons attributs, et de bien construire vos cubes, pour que les requêtes fonctionnent bien.

Utilisation du VBA, ordre de résolution des requêtes, propagation des contraintes sur les attributs… Pfiou, c’était du lourd tout ça ! Heureusement que le DAX arrive pour simplifier tout ça (hur hur).

Spéciale dédicace à Jordan Mootoosamy, qui a bien souffert avec moi sur cette requête 😉

BI.Quality : tests automatisés pour comparer des données entre SSAS et SQL Server

Fleid 6 commentaires

Comme je vous le disais tantôt, l’amélioration continue des équipes et des solutions passent par une automatisation des activités qui peuvent l’être. Ce n’est pas moi qui l’invente, c’est à la base de l’Agilité et du Lean. Notez que ce n’est pas une fin en soi, mais plutôt dans l’objectif de tendre vers un temps de production le plus court possible, toujours en qualité optimale.

Et une des principales manières d’avancer sur sujet c’est bien par l’automatisation des tests. C’est la raison pour laquelle je teste depuis peu BI.Quality, l’outil de test automatique gratuit développé par ORAYLIS et disponible sur le CodePlex.

BI.Quality

Ce qui est particulièrement intéressant avec cet outil c’est la possibilité de comparer des datasets en provenance de SSAS, de SQL Server, ou d’un fichier CSV. Vous comprenez l’intérêt immédiatement : avec ça on va pouvoir comparer de manière automatique son cube (via requêtes MDX) avec son DWH et/ou son ODS (via requêtes SQL). On pourra également comparer le résultat d’une même requête contre plusieurs environnements (production, intégration, développement…) pour valider que tout est bien synchro une fois la livraison terminée.

Joie !

Alors ok, l’interface ne fait pas rêver, et quasiment toute la « programmation » se fait dans des fichiers XML, donc à la main dans NotePad++. Mais la fonction rendue est tellement excellente qu’on apprend à vivre avec.

NUnit_1

Je vous fais un guide de démarrage rapide, vous allez voir c’est assez simple :

  1. Télécharger et installer NotePad++, si ce n’est pas déjà fait !
  2. Télécharger et installer NUnit (framework de test, c’est lui qui exécutera les tests créés dans BI.Quality), la version courante c’est la NUnit-2.6.2.msi
  3. Télécharger et installer BI.Quality (pas de panique, il termine sans prévenir, c’est un peu artisanal)
  4. Télécharger et lire la documentation :p
  5. Suite à ça, on dispose dans le menu démarrer :
    1. D’un répertoire BI.Quality, qui contient principalement un ZIP contenant la solution template, qui sera le modèle de départ pour tous les projets de tests. A dézipper là où vous le souhaitez pour chaque nouveau projet.
    2. D’un répertoire NUnit, qu’il va falloir associer avec votre projet de test (le répertoire dézippé), dans NUnit : File> Open Project > …\Lib\BI.Quality.dll

Une solution de test BI.Quality c’est donc un dossier composé de 4 sous-répertoires (le contenu du ZIP) :

  • \Bin\ et \Lib\ : on ne touche pas
  • \Connections\ : on va définir nos connexions là-dedans, un fichier XML correspondant à une connexion. On ne peut y utiliser que des belles chaînes de connexions OLE DB (SSAS, SQL Server < 2008R2, SQL Server 2012, à tester sur Excel, Access et SharePoint) :

BI.Quality_Connections

  • \Queries\ : on va définir nos tests là-dedans, un sous-répertoire correspondant à un test, avec :
    • des fichiers SQL, MDX ou CSV qui définissent les requêtes à utiliser dans le test
    • un fichier XML qui définit le test en lui-même

BI.Quality_Query

Franchement c’est pas sauvage non ? Je définis 2 sets de données, les <Query/>, que je compare dans un test <AssertTable/>.

Alors il existe plein de tests possibles, avec plein d’options, à voir dans le PDF de documentation ainsi que dans l’ensemble de tests livrés dans le template (1-Tutorial, 2-TechnicalTests, 3-BestPractices) que vous pouvez d’ailleurs enlever de votre projet si vous voulez avoir une solution bien propre.

Une fois que c’est fait, on retourne dans NUnit (on charge le projet si ce n’est pas déjà fait : File> Open Project > …\Lib\BI.Quality.dll), et on peut exécuter ses tests d’un simple RUN :

NUnit_2

Si la partie « Configuration Test » est gérée toute seule, NUnit va parser les XMLs de définition des connexions et des tests pour valider leur format, la partie « Query Test » est bien celle pilotée par vos tests du répertoires \Queries\ . Notez que si vous ne passez pas le « Configuration Test », c’est que vos XMLs sont mal montés : direction NotePad++ pour corriger tout ça. Si tout va bien, c’est parti pour vos tests à vous 🙂

Juste une petite remarque en passant : je n’ai définitivement pas réussi à ajouter un Delta sur un AssertTable, n’hésitez donc pas à faire plutôt des ROUNDs dans vos requêtes SQL ou MDX, si par exemple vous changez de précision entre les 2 sources. [MàJ 2013-08-19] En fait il est possible de définir un Delta dans un AssertTable, mais en utilisant une virgule plutôt qu’un point dans la valeur: delta=« 0,1%« . Trop bien 🙂

Et une deuxième petite remarque : pensez bien à recharger vos tests si vous les modifiez (File> Reload Project ou Reload Test) sinon ce ne sera pas pris en compte par NUnit.

D’un point de vue stratégie de tests, je suis partie sur les éléments suivants :

  • D’abord des agrégations de haut niveau (mon CA par an sur les 5 grands pôles d’activité), qui valident que le total général est le même partout
  • Des tests sur chaque dimension, indépendamment des faits, pour valider qu’on n’oublie personne en route et que les hiérarchies tiennent la route
  • Des tests portant sur les valeurs des mesures clefs pour chaque code atomique de chaque dimension (autant de tests que de dimensions). On a vérifié les hiérarchies dans l’étape précédente, il suffit donc de valider que chaque code dispose des bons montants unitairement pour chaque dimension pour valider quasiment tout
  • Des scénarios de référence (SQL/MDX vs valeurs en dur dans des CSV). Si les valeurs historiques (2009,2010…) n’évoluent plus, on peut se faire quelques extraits, les stocker en CSV, et les comparer régulièrement contre le cube ou le DWH. Histoire de prendre en flagrant délit la régression sur l’historique
  • Les requêtes des rapports SSRS, qu’on peut valider contre des valeurs de références ou entre plusieurs environnements
  • Enfin, toutes les requêtes issues des analyses d’anomalie, écrites pour renvoyer du vide si tout va bien

Si on maintient bien à jour sa base de tests, et qu’on exécute le projet régulièrement, il va devenir vraiment difficile de livrer du code défectueux !

Je conclue en vous livrant l’avis de Chris Webb sur l’outil, et en vous recommandant chaudement de l’essayer sur votre prochain projet ou prochaine recette. C’est simpliste, certes, mais ça fait le job, et pouvoir tous les jours exécuter toute sa batterie de test en 1 clic c’est juste magique ! Seul petit bémol: la dernière mise à jour du projet date de fin 2010, le dernier commentaire des admins de fin 2012… Alors allez le télécharger, histoire de bien montrer qu’on a besoin d’eux 😉

Quel livre pour apprendre le MDX?

Fleid 7 commentaires

Voici l’occasion de me servir de ce blog pour demander votre aide! En effet je renouvelle la bibliothèque de notre pôle de consultants en BI Microsoft, et je suis tombé sur un os en ce qui concerne la partie MDX.

Car autant j’ai une vision assez claire des références absolues sur quasiment tous les sujets décisionnels (voir en dessous), autant mon guide du MDX, Fast Track to MDX – celui qui m’a tout appris, commence à dater un peu. Quelqu’un aurait un ouvrage à me recommander sur ce sujet? Un cookbook peut être?

Et pour référence, voici mes ouvrages recommandés par sujet. N’hésitez pas à commenter cette liste 🙂

Merci à tous dans les commentaires 😉

Petits pièges de la colonne de texte libre en attribut de dimension dans SSAS

Fleid 4 commentaires

La petite anomalie retorde de la semaine nous vient de la colonne de texte libre montée en attribut de dimension dans SSAS.

Exemple de texte libre par David Stewart

Pour un peu de contexte sur le sujet, voir cet article de David Stewart, ou ce topic sur StackOverflow.

L’erreur usuelle dans cette situation c’est d’avoir la valeur NULL corrigée automatiquement par SSAS en chaîne vide (‘’), en conflit avec une éventuelle chaîne vide déjà existante. SSAS crie alors au loup à travers un message relativement clair (« Duplicate attribute key ») qu’on apprend vite à reconnaître.

Aujourd’hui j’ai eu droit à un bonus (« Attribute key not found »), dans le même contexte. Le conflit venait de la chaîne composée d’un espace ‘ ‘ avec la chaîne vide ‘’. En effet malgré la propriété de Key Column Trimming de l’attribut passé à « None » (indiquant en théorie à SSAS de préserver les espaces), l’attribut ‘ ’ disparaissait lors du process de la dimension.

A mon sens un trimming implicite a lieu durant l’une des opérations du process, mais pas toutes. Arrive un moment où SSAS ne retrouve plus ses petits, et c’est le drame. Si un ninja de SSAS veut nous en dire plus, c’est avec plaisir 🙂

La correction est  simple : effectuer le Trimming explicitement (Right ou LeftRight), ainsi tous les accès à la valeur se feront de la même manière et SSAS arrivera à linker tout le monde. S’il vous faut préserver la valeur espace, je vous laisse jouer avec les différentes valeurs des propriétés de colonne clef, ainsi qu’avec les différentes collations de vos serveurs, mais j’ai bien peur que ce soit un bug (j’ai la flemme de chercher sur le connect…). Si par contre c’est juste la différenciation entre les 2 valeurs que vous voulez préserver, n’hésitez pas à corriger la valeur ‘ ’ en par exemple ‘(space)’ dès l’ETL.

D’une manière générale, je vous recommande les settings suivants pour vos colonnes d’attributs en texte libre (sauf la collation, évidemment, il faut s’adapter à la source ;)) :

SSAS - Free Text Dimension Attribute Properties

N’hésitez pas à m’indiquer vos astuces, et amusez-vous bien 😉