François Jehl - Blog sur la BI Microsoft

[Techdays 2012] On vous attend dans une semaine!

2012-01-30T17:52:00.003+01:00

Les Techdays c'est la semaine prochaine, j'ose espérer que vous êtes d'ores et déjà tous inscrits! Dans le cas contraire, il n'est pas trop tard! En ce qui me concerne, j'anime cette année avec mon petit camarade Aurélien Koppel une session qui se targue de vous aider à choisir la bonne solution pour votre projet décisionnel en SQL Server 2012, entre de la BI Personnelle, une couche sémantique et un cube. C'est la DBI 202, sobrement intitulée "Analysis Services 2012 : BI Personnelle, couche sémantique, cube, quelle(s) solution(s) pour un nouveau projet décisionnel?".
Ce sera mercredi 8 février, de 11h à 12h, et pour vous y inscrire c'est ici.

En espérant vous y voir, dans le cas contraire, je serai en vadrouille sur le salon dès mardi 8, entre le stand Infinite Square, celui de SQL Server, et celui du GUSS.
A la semaine prochaine!

[SSRS] Un tableau croisé dynamique (oui dynamique!) avec Reporting Services

2012-01-06T16:05:00.015+01:00

Alors celle là c'est l'arlésienne des questions Reporting Services.

Peut-on utiliser RS pour générer un TCD?

La réponse que l'on entend souvent tourne autour de ce gloubiboulga de Consultant MSBI "Oui mais c'est de la bidouille, RS n'est pas un client ad-hoc. Sinon vous connaissez Excel?"
...
Soyons honnête c'est assez vrai. Mais ce n'est pas parce que tordre un outil n'est pas vraiment complètement prévu qu'il ne faut pas le faire. Bah oui sinon on n'aurait jamais eu de dimensions techniques dans des cubes AS, et puis la vie serait ennuyeuse il faut avouer. Alors pourquoi ce post, et pourquoi aujourd'hui? Parce qu'on vient de me demander ce que fait la fonction Fields() - oui je dis bien la fonction Fields() - dans SSRS. Et que l'utilité principale que je lui connais est précisément de faire ça.

La fonction Fields()?

La fonction Fields(string) permet de convertir un nom de champ passé en paramètre en l'expression Fields!.Value correspondante dans la portée de l'expression. Oui, en quelque sorte c'est de la Reflection, Reporting Services Staïle.

Oui mais quel rapport avec un TCD?

J'y viens, j'y viens. Créons un rapport avec une matrice. Ce rapport s'appuie sur une requête bêbête en SQL, qui renvoie des trucs de vente.

Créons deux datasets bidons. Un s'appelle Mesures, l'autre Axes. Chacun comprend respectivement les noms de champs du dataset Données du type considéré, à savoir que Mesures contient 'PU', 'Qté' et 'CA' et Axes le reste des noms. Appuyons maintentant trois paramètres, deux monovalués s'appuyant sur Axes et s'appelant respectivement Lignes et Colonnes et un multivalué s'appelant Mesures et s'appuyant sur... bravo vous avez deviné, le dataset Mesures. OK ça ne fait absolument rien pour le moment, mais il faut avouer, c'est beau.

La partie drôle arrive. Créons une matrice, avec un groupe de lignes, un groupe de colonnes, et trois cellules pour nos trois mesures. L'expression de groupement en ligne est donc basée sur Fields et vaut:

=Fields(Parameters!Lignes.Value).Value

L'expression en colonnes est du même esprit. Enfin, il ne reste plus qu'à conditionner la visibilité des cellules de mesures à une expression du genre:

=InStr(Join(Parameters!Mesures.Value,","),"PU")=0

Une fois que l'on fait tourner tout cela, on a donc quelque chose du style (et on ne se MOQUE PAS des couleurs :)):

On pourrait aller beaucoup plus loin, gérer un nombre d'axes dynamique (bon avec une limite certes...) mais ce serait exagérer. Simplement c'est un usage marrant de la fonction Fields je trouve! Vous en pensez quoi?

A bientôt!

PS: Pour les nostalgiques, le DataSet s'appuie sur Northwind, j'étais en mode hommage à SQL 2000 et j'en avais assez d'AW.

Edit: Charles-Henri Sauget a lu ce papier et, voyant que je ne me décidais pas à partager mon RDL, a implémenté la solution que je décris sur son blog. Allez faire un tour ;)

[Journées SQL Server] Epilogue et précisions

2012-01-03T23:11:00.007+01:00

J'ai reçu pas mal de questions relatives à ma démo bonus de fin de session (que vous pouvez voir sur le webcast). Pour rappel il s'agissait de montrer qu'il était possible d'injecter du MDX dans un modèle BISM Tabular, et de voir qu'il était interprété par Excel. Alors comment est-ce possible?

Déjà ce à quoi vous avez assisté est la définition même de BISM (le côté tolkienien du "One model to rule them all"): les modèles Tabular et Multidimensional sont basées sur des structures objet (au sens AMO et donc XMLA du terme) identiques. Comprenez qu'un modèle tabulaire est défini en sous jacent comme possédant une DataSourceView, un cube qui s'appelle Sandbox (comme dans PowerPivot), des Dimensions, des MeasureGroups... et donc des MdxScripts (oui car je vous rappelle que SSAS gère potentiellement plusieurs MdxScripts que l'on peut modifier mais c'est une autre histoire).

Si vous vous connectez en AMO sur ce modèle (ou regardez la définition XMLA envoyée au déploiement du modèle tabular) vous constaterez que cet objet MdxScript possède des calculs de deux types, en MDX et en DAX. Entres autres il définit une DefaultMeasure bidon égale à 1 (probablement pour des raisons de performances). Un client OLAP classique comme Excel est parfaitement capable de lire les deux types mesures, ce qui signifie que le script expose bien les métadonnées de ces deux types. La démonstration que j'ai faite consistait simplement à rajouter une mesure en MDX (et à scoper dessus) pour prouver que ça marche sous un client OLAP.

Mais attention! La mesure que j'ai créée aurait été invisible en DAX. Je ne l'ai montré qu'à deux personnes qui m'ont posé la question et se reconnaitront mais ma mesure était inconnue du parser DAX. En d'autres termes et à date le formula engine DAX est capable uniquement de voir les calculs exprimés en DAX. De plus ce cas n'est pas supporté. Officiellement les modèles tabulaires ne gèrent pas les calculs MDX, et l'édition du MdxScript est proscrite. Vous voilà prévenus!
Au passage cela répond à une question posée en fin de session: non, les requêtes MDX envoyées à un modèle Vertipaq ne sont pas traduites en DAX.

Voilà, à bientôt!

PS: pour finir un rappel des langages supportés sur quel modèle:
Tabular supporte DAX et MDX mais pas avec DirectQuery pour ce dernier. Multidimensional supporte uniquement MDX.

Journées SQL Server: les webcasts sont en ligne!

2011-12-22T15:06:00.008+01:00

Les webcasts des Journées SQL Server, sont en ligne! Vous pouvez donc vous (re)délecter de notre session avec Aurélien Koppel, dont le but est de vous aider à choisir entre BISM Tabular et Multidimensional pour un nouveau projet SSAS.
Vous avez le choix entre directement la regarder ci-dessous, ou aller sur le site Showcase d'hébergement de Microsoft.

&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/object&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;script type="text/javascript"&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; document.write("&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;script type='text/javascript' src='" + (window.location.protocol) + "//c.microsoft.com/ms.js'" + "&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;\/script&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;");&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/script&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;/div&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;

N'hésitez pas à voter pour la vidéo si elle vous plait (bon et si elle ne vous plaît pas aussi, la démocratie est ainsi faite). Pour le reste, si vous avez envie de regarder un peu de BI par ce temps pluvieux - certes c'est Noël mais sachez que c'est un loisir tout ce qu'il y a de plus normal en période de fêtes - n'hésitez pas à fureter sur le site Showcase (le lien juste au dessus de la vidéo).

Vous y trouverez un tas de sessions intéressantes et agréables à regarder, particulièrement celle de Jean Pierre Riehl et Florian Eiden sur la modélisation dimensionnelle, et celles de Thomas Ricquebourg que j'ai trouvé bluffant sur DQS et SSIS, ou il était en featuring respectivement avec Sébastien Madar et JP.

Regardez leurs sessions, notez (bien!), commentez, croyez moi ça fait plaisir :)

Merci encore aux présents, et à très bientôt!

Journées SQL Server: Merci à tous

2011-12-16T13:42:00.007+01:00

300 personnes! Je ne dirais pas que le succès était inespéré, car l'organisation a été rapidement assaillie de demandes d'inscriptions dès l'ouverture, mais tout de même une très jolie surprise! Nous voilà donc en présence d'une belle communauté sur SQL Server qui ne demande qu'à être développée.

Merci au GUSS, particulièrement à Arian Papillon, Christian "Cricri" Robert, Sébastien Madar et Jean-Pierre Riehl pour une organisation idéale, très agréable pour les speakers que nous sommes et j'imagine aussi pour les visiteurs. De même merci à Microsoft, et tout particulièrement à l'équipe SQL Server (Damien Cudel, Jean-Marc Monfort et Hugues Valentin) pour leur disponibilité tout au long de l'évènement pour répondre à nos question, à vos questions sur le produit.

Et puis bien sûr merci à vous: en ce qui me concerne l'accueil pendant et après notre session avec mon compère Aurélien Koppel a été on ne peut plus chaleureux, et a permis de mettre des visages sur des noms déjà connus, et qui donnent envie d'être encore plus connus (la micro-communauté Microsoft BI de Développez.com était venue en force!).

Pour la suite, les webcasts seront bientôt en ligne, stay tuned. Et pour le reste... rendez vous aux TechDays les 6-7 et 9 février 2012!

A bientôt!

[SSRS] Dépasser 65536 lignes dans un export Excel

2011-12-02T13:46:00.001+01:00

Cette astuce est un petit peu connue mais entre les forums et les clients, cela fait trois fois en quelques jours que l'on me pose cette question: comment faire pour exporter des rapports SSRS de plus de 65536 lignes en XLS sans avoir pour résultat ça:

Donc je me permets de la poster, si cela peut rendre service.

Alors l'idée est d'exploiter une fonctionnalité de l'exporteur Excel, qui crée une nouvelle sheet lorsque l'on insère un PageBreak explicite. Pour cela j'ai d'abord créé une table dans un rapport SSRS qui s'appuie sur une requête de test qui me renvoie 100 000 lignes sur... 2041 pages A4.

Evidemment, l'export de celle-ci en Excel rate lamentablement (cf plus haut). Alors la solution pour pallier à cela est donc de créer des PageBreak toutes les 65536 lignes: on va donc créer un groupe dans la table, qui groupe les données sur le résultat de la division entière du numéro de la ligne par 65536. Si vos souvenirs de CM1 sont bons, alors vous ne serez pas étonné que 1 DIV 65535 = 0, 65536 DIV 65535 = 1...etc. L'expression équivalente en RS se traduit par Int(RowNumber(Nothing) / 65536) comme visible ci-dessous:

Il ne reste plus qu'à dire à SSRS d'insérer un PageBreak entre chaque instance du groupe (i.e. entre chaque valeur distincte de Int(RowNumber(Nothing) / 65536)). Cela s'active dans l'onglet PageBreaks de l'éditeur de groupe. Une fois ceci fait, l'export Excel se déroule sans problèmes et me sort un Excel de deux sheets.

Mais car il y a un mais, ce PageBreak disgracieux vient s'ajouter à tous les exports, y compris à ma visualiseuse HTML. J'ai donc un saut de page inutile page 1308 de mon rapport de 2000 pages. Pointillisme peut-être mais cela peut heureusement être évité, grâce à la fantastique variable globale RenderFormat introduite depuis SQL Server 2008 R2. Cette globale permet de récupérer l'exporteur utilisé dans une expression, et donc de conditionnellement changer un élément en fonction de l'extension de rendu. Dans mon cas, je vais désactiver le PageBreak, sauf si je suis en Excel. La valeur de PageBreak.Disabled sur le groupe est donc dynamique et vaut (Globals!RenderFormat.Name <> "EXCEL").

Et c'est tout de suite plus joli, les autres extensions ne sont plus affectées par ce hack.

Tout cela deviendra inutile (ou presque) avec SQL Server 2012 qui dès l'année prochaine permettra d'utiliser le XLSX pour les exports. Bon ça servira lorsqu'on voudra dépasser les 1.000.000 de lignes :)

A bientôt!

4 bonnes raisons de venir découvrir Analysis Services 2012 aux journées SQL Server

2011-12-01T13:22:00.001+01:00

Cela arrive très vite maintenant, notre session aux Journées SQL Server avec Aurélien Koppel est déjà dans moins de deux semaines. Alors un petit teaser ne faisant pas de mal, j'ai voulu vous donner quelques excellentes raisons de venir nous voir, si tant est que vous en ayez besoin.

1 - Le futur d'Analysis Services se dessine maintenant

Ce papier date de maintenant un an et je me rappelle encore de l'état d'incompréhension dans lequel la communication de Microsoft avait mis la communauté de développeurs Analysis Services: nous pensions tous que ce sur quoi nous avions bâti notre carrière (pour certains cela fait presque 15 ans), à savoir la promotion de l'OLAP, du MDX, bref l'époque d'Analysis Services leader du marché allait être reniée par un vague portage serveur de PowerPivot sous le nom délicat de BISM.
Soyons clair il n'en est rien et vous verrez pourquoi.

2 - BISM Tabular va permettre de développer des projets BI beaucoup plus rapidement

Le but de Microsoft en'a jamais changé: occuper tous les segments du marché des bases d'analyse. Avec Analysis Services, il est leader, avec PowerPivot il concurrence QlikView en frontal. Reste que les Report Models ayant été un échec et Analysis Services s'avérant trop complexe pour les petits projets, les couches sémantiques type B####### O###### - pour ne pas les citer - occupent encore une place très importante en entreprise. Et c'est précisément là qu'il y a encore des parts à prendre. Chez ce type de clients/prospects le discours officiel époque SQL Server 2000 "chez Microsoft, la couche sémantique c'est le cube" ne passe plus: les cubes sont vus comme trop complexes pour une bonne partie des projets, ce qui signifie des experts rares, une courbe d'apprentissage longue et au final un coût qui effraie.
BISM Tabular est simplement un ajout à Analysis Services d'un mode de conception plus simple pour élargir la base potentielle de clients, et nous vous montrerons à quel point cela est simple.

3 - BISM Multidimensional continue plus que jamais

T.K. Anand l'affirme, les développeurs AS connus (Teo Lachev, Chris Webb...) le confirment: pour les projets BI enterprise-wide, critiques, complexes d'un point de vue métier: bref l'essentiel de nos projets existants: le choix est et restera en 2012 Analysis Services UDM, renommé Multidimensional. Son script MDX ses relations en M2M, Parent Child, le support des Custom Rollup, Unary, sa gestion des grosses volumétries... bref toutes ces fonctionnalités exclusives sont encore des critères de choix évidents en faveur de l'OLAP traditionnel pour ces typologies de projets non encore couverts par Tabular.
Nous vous les récapitulerons.

4 - Demain un modèle unique?

Les modèles Tabular supportent d'être requêtés en MDX, les modèles Multidimensional ne gèrent pas encore le DAX - problématique pour PowerView - mais cela devrait venir rapidement. Le script MDX est une exclusivité Multidimensional, comme le stockage en mémoire est une exclusivité Tabular... Il est évident que toutes ces features vont fusionner à l'avenir dans un modèle unique, et c'est d'ailleurs le souhait de l'équipe produit.
Nous verrons ensemble quelles fonctionnalités peuvent être espérées à brève, moyenne et longue échéance, avec toujours une idée en tête: vous aider à faire le bon choix pour tous vos nouveaux projets.

Voilà j'espère vous avoir donné -encore plus - envie: les journées se tiennent au siège de Microsoft, à Issy Les Moulineaux, les 12 et 13 décembre et pour vous inscrire, c'est ici!

A très bientôt!

Les Journées SQL Server (édition 2011)

2011-10-24T13:12:00.000+02:00

Rejoignez la communauté des professionnels SQL Server !

Les experts francophones de SQL Server, les MVP (Most Valuable Professionnal) et les MCM (Microsoft Certified Master) se réunissent à Paris pour 2 jours de conférences entièrement gratuits.

Vous pourrez découvrir les nouvelles fonctionnalités de SQL Server 2012 mais aussi assister à des sessions sur des sujets pointus au travers d’un parcours consacré au moteur de base de données, d’un autre consacré à la Business Intelligence et enfin un parcours dédié aux appliances SQL Server.

Au total, ce ne sont pas moins de 23 sessions qui vous attendent sur AlwaysOn, Power View, SSIS Server, les index colonnes, SQL Server Data Tools, SQL Azure, BISM, les performances, la sécurité, etc..

Inscrivez-vous dès maintenant

Cet événement est organisé par la communauté SQL : le groupe des utilisateurs francophones (GUSS) et les MVP, avec l’appui et la participation de Microsoft et du sponsor Platinum : HP

Tout au long des journées, vous pourrez également poser toutes vos questions à l’espace rencontre à nos différents sponsors éditeurs, presse ou SSII. Les speakers sont également à votre disposition.

Cet événement aura lieu les

12 et 13 décembre
au
Centre de conférences Microsoft

41 quai du Président Roosevelt
Issy-les-Moulineaux

GUSS est affilié au PASS (Professional Association for SQL Server), leader des communautés SQL Server à travers le monde. Nous les remercions pour leur soutien dans l’organisation de cet événement.

[SSRS] Utiliser la fonction Lookup pour traduire les parties statiques des rapports

2011-10-17T15:56:00.003+02:00

Analysis Services offre des possibilités de traduction intéressantes, qui permettent évidemment de traduire les rapports qui s'appuient dessus. Grâce à la communication du LCID de l'interface cliente, la structure et le contenu du cube sont alors exposées dans la langue adéquate. Mais quiconque à déjà réalisé des rapports sait que ceux-ci sont aussi composés de parties "statiques", non dépendantes du cube, qui par conséquent ne tirent aucun bénéfice des traductions OLAP. Les titres des rapports, entêtes de tableaux, descriptions, sont alors autant de libellés qu'il va falloir gérer dans un contexte internationalisé.

Une solution intéressante à ce problème est apportée par la fonction Lookup de SQL Server 2008 R2, celle ci permettant de récupérer dans un DataSet l'enregistrement correspondant à une valeur de clé. En effet, stocker les libellés dans une base est une chose, les récupérer en utilisant les (limitatives) fonctions d'agrégations de SSRS en est une autre.

1) 1ère étape, on stocke dans une table au format Clé/Valeur/Langue les différents libellés du rapport. Prenons comme exemple Produit:

Rapport;Libelle;Traduction;Langue;

Ventes;Product;Produit;FR-FR;

Ventes;Product;Product;EN-EN;

...

2) 2ème étape on crée un DataSet, par exemple nommé "Dictionnaire", qui récupère uniquement et dynamiquement les libellés correspondant au rapport et à la langue en cours:

SELECT Libelle, Traduction

FROM dbo.Traductions

WHERE Rapport=@Rapport AND Langue=@Langue

@Rapport est mappé à la valeur globale Globals!ReportName, @Langue à User!Language, deux valeurs exposées par défaut par Reporting Services qui donnent respectivement le nom du rapport en cours et la culture de l'utilisateur connecté.

3) 3ème étape dans chaque cellule ayant besoin d'être traduite, on va chercher le libellé correspondant, avec une expression Lookup:

=Lookup("Dictionnaire";Fields!Libelle.Value; "Product";Fields!Traduction.Value)

Magie de la technique, les libellés sont traduits automatiquement, et surtout on peut rajouter des langues à volonté sans retoucher aux rapports!

En espérant vous avoir donné des idées.

PS1: pour ceux que cela intéresse on aurait pu effectivement imaginer stocker dans la table le nom du contrôle SSRS plutôt qu'une clé gérée par le développeur. Le problème est qu'il est difficile de manière propre de connaître le nom d'un contrôle dans une expression SSRS. Essayez vous verrez c'est très sale et pas assuré d'être stable. Cela oblige donc à documenter.

PS2: On peut aussi vouloir gérer la traduction par un paramètre, auquel cas on n'utilisera pas User!Language mais un paramètre à soi.

PS3: Attention à l'interaction avec SSAS: Reporting Services travaille avec des libellés de culture (fr-fr, en-us...), SSAS avec des LCID par défaut.

[SSRS] Une Parent Child mise à plat dans plusieurs champs

2011-09-14T20:03:00.000+02:00

Une petite note rapide: dans Reporting Services, lorsque l'on veut utiliser une hiérarchie Parent/Enfant, tous les membres sont exposés dans la même colonne, peu importe leur niveau.

Et si vous désirez avoir un champ par niveau? Impossible de CrossJoiner sur une même hiérarchie (C'est interdit par MDX!), et si vous googlisez, on vous dira soit que c'est un soucis de provider et que vous n'avez qu'à utiliser l'OLE DB, soit de créer des membres calculés, soit d'arrêter les P/C voire Reporting Services tout court...

Mais on peut hacker, un peu de la même manière que pour le DrillThrough dans un de mes vieux posts.

La propriété "DbpropMsmdFlattened2"

Effectivement, ce soucis de colonne est un soucis lié au provider, plus exactement de configuration de la commande AS. Sur un sujet lié lui aussi à RS, Greg Galloway m'a fait tilter dans ce post, en disant que le provider Analysis Services pour RS met les propriétés ReturnCellProperties et DbpropMsmdFlattened2 à true. Son post parle du premier, le deuxième nous intéresse aujourd'hui.

Tout d'abord son assertion est vraie: vous pouvez le constater dans le Profiler sur les events Query Begin.

Quel est l'effet de ces deux propriétés? Dans la doc des propriétés XMLA, il est spécifié que la première demande l'exposition des propriétés de cellules en tant que colonnes - ce dont traite Galloway - et que la seconde "Fournit tous les membres d'une hiérarchie parent-enfant dans une seule colonne de table".

Bingo?

On pense alors qu'il ne reste plus qu'à rajouter DbPropMsMdFlattened=false à la connection string.... Et bien non car cela ne marche pas: la propriété est insérée dans la commande par RS, donc après établissement de la connexion, et override le choix fait dans la connection string.

La solution? Switcher en mode de requête DMX.

Dans ce cas, ces deux propriétés ne sont pas affectées (on le voit là aussi dans le Profiler, les deux lignes ne sont pas présentes dans l'entête de commande), mais ce pour des raisons que je ne connais pas. On perd le designer mais la requête marche comme on le souhaite, j'ai bien mes niveaux dans des champs différents.

Conclusion

Après est-ce la bonne approche? A vous de juger, effectivement cela ressemble beaucoup à un hack douteux mais dans le cas (qui est le mien) ou on ne fait pas un usage extensif des P/C, si un hack certes douteux mais utilisé rarement évite de recréer et maintenir une data source dédiée ça peut valoir le coup...

A bientôt!

[SSRS] Dernière valeur d'un DataSet comme valeur par défaut

2011-09-06T11:38:00.003+02:00

C'est drôle comme parfois une solution toute bête peut vous échapper pendant pas mal de temps. Lorsque vous utilisez un DataSet pour remplir les available values d'un paramètre dans SSRS, il vous est sûrement déjà arrivé de souhaiter sélectionner la dernière valeur comme valeur par défaut (allez par hasard pour une dimension temps).

Donc première intuition vous sélectionnez une expression comme valeur par défaut, expression qui fait référence au DataSet en cours (Fields!...) pour prendre la dernière valeur (aggrégation Last()).

Et vous prenez une erreur comme quoi vous ne pouvez pas utiliser Fields! dans ce type d'expression d'où vous faites comme tout le monde et vous réécrivez un DataSet spécifique pour la dernière valeur, ce qui est quand même dommage d'un point de vue bases de données, parce que l'info vous l'avez déjà dans le DataSet précédent...

Et en réfléchissant un peu on trouve rapidement une solution: il suffit de déclarer un paramètre "dummy", internal et multivalué, qui prend les valeurs du même DataSet que le vrai paramètre intéressant en available et en default.

Dans notre paramètre initial, section default, il suffit alors de saisir l'expression:

=Parameters!Dummy.Value(Parameters!Dummy.Count-1)

Et ça marche: on n'a pas le droit de référencer Fields! mais on peut référencer Parameters dans cette expression.

En espérant que ça vous serve, à bientôt!

[SSAS] Quelques rappels de Multiselect, Sets et AutoExists

2011-08-27T18:08:00.001+02:00

La notion d'existence sur les sets lorsque l'on réalise des requêtes MDX filtrées (soit par un slicer soit par un subcube) est une des notions les plus complexes à comprendre pour un novice (voire même pour un non novice en MDX). Dans ce post, je vais essayer de faire un bref rappel synthétique du fonctionnement de tout cela dans SSAS. Il y a eu pas mal de trucs écrits sur le net chez Chris Webb ou feu Mosha, mais je pense que synthétiser tout ça ne fait pas de mal, surtout avec la généralisation des filtrages par subcube dans les générateurs de requêtes.

Filtrage par slicer

Il faut savoir qu'historiquement MDX permet de réaliser un filtrage d'une requête un peu à la manière de SQL en utilisant la clause WHERE, que l'on appelle aussi slicer.

En MDX lorsqu'une requête est exécutée, les deux axes principaux (Axis0 et Axis1 aussi appellés COLUMNS et ROWS) sont "existés" (existed en anglais): on applique sur ces axes la clause Exists qui ne retourne alors que les membres valables pour le filtrage. Il ne surprend alors personne que la requête suivante ne retourne que les jours de l'année 2007. (A noter que l'on peut aussi faire du multiselect dans les clauses Where, que l'Aggregate en mode SQL 2000 n'est plus usité.).

SELECT {[Date].[Date].[Date]} ON 0

FROM [Adventure Works]

WHERE ([Date].[Calendar].[Calendar Year].&[2007])

L'axis0 affiche un résultat équivalent à:

Exists

(

{[Date].[Date].[Date]},

{[Date].[Calendar].[Calendar Year].&[2007]}

)

C'est ce que l'on appelle l'Autoexists. Mais ce comportement n'est pas appliqué partout dans une requête MDX, et certains sont parfois surpris...

Mot clé Existing

La requête suivante par exemple ne renvoit pas 365 ou 366 mais.... 1188.

WITH

MEMBER [Measures].[Nb Days In Year]

Count

(

{[Date].[Date].[Date]}

)

SELECT {[Measures].[Nb Days In Year]} ON 0

FROM [Adventure Works]

WHERE ([Date].[Calendar].[Calendar Year].&[2007])

En effet le set de Dates dans la named calculation n'est pas existé dans le contexte. Le slicer n'est pas pris en compte et on renvoit tous les membres Date. Un développeur MDX aguerri connait alors la clause Existing (http://msdn.microsoft.com/fr-fr/library/ms145541.aspx) qui force le jeu à être évalué dans le contexte actuel de la requête. En modifiant mon membre de cette manière:

MEMBER [Measures].[Nb Days In Year]

Count

(

Existing {[Date].[Date].[Date]}

)

On obtient bien alors 365.

Autoexists sur les named sets

Là ou cela devient drôle c'est que si l'on déclare le set de dates, mettons un jeu nommé Jours, l'Existing est superflu.

SET [Days]

{[Date].[Date].[Date]}

MEMBER [Measures].[Nb Days In Year]

Count

(

[Days]

)

Renvoie alors 365. Les jeux nommés de requête sont par défaut existés... Ce qui peut poser de gros problèmes de compréhension (imaginons que l'on souhaite mettre dans le set les jours d'un YTD il serait alors... vide si on a un jour dans le slicer). C'est ce qu'explique cet excellent article de Chris Webb (http://cwebbbi.wordpress.com/2008/07/15/named-sets-autoexists-and-katmai/) qui va beaucoup plus loin).

C'est le comportement par défaut de l'autoexists, aussi appellé Autoexists de type 1 décrit sur MSDN (http://msdn.microsoft.com/fr-fr/library/ff487119.aspx): l'autoexists est appliqué sur les axes et les jeux de requête. C'est un autoexists deep, c'est à dire qu'il s'applique à chaque étape du calcul. (Si mon set est calculé à partir d'une composition de fonction, Exists est appliqué sur chaque fonction), par opposition à l'autoexists shallow, qui ne s'applique que sur le résultat final. Je reste volontairement dans le vague, si vous voulez voir plus d'implications de ces différents modes d'Autoexists, direction chez Chris Webb, sachez seulement que l'autoexists sur les sets peut être par exemple débrayé en modifiant le type d'AE appliqué sur le serveur.

Static Sets et Dynamic Sets

Mais attention, si les jeux nommés de requête sont bien existés par défaut, il n'en est pas de même des jeux créés dans le script MDX du cube!

Les sets pré-2008, aussi appellés Static Sets ne sont pas évalués dans le contexte de la requête, ce qui rend très difficile la récupération des membres "existants" dans un calcul MDX si la requête utilise des subcubes (typique d'Excel en version >12 avec SQL Server 2005).

Dans le cas de notre requête précédente en la réécrivant "Excel Style", cela annule l'effet de la clause Existing, et si la mesure [Nb Days In Year] est calculée dans le script sur un Static Set cete dernière reste fausse (le compte renvoyant là encore 1188).

SELECT {[Measures].[Nb Days In Year]} ON 0

FROM

(

SELECT {[Date].[Calendar].[Calendar Year].&[2007]} ON 0

FROM [Adventure Works]

)

La solution est venue avec Katmai (2008) et l'introduction des Dynamic Sets, qui sont existés dans le contexte de requête.

Un jeu de ce type

CREATE DYNAMIC SET CURRENTCUBE.[Days]

AS [Date].[Date].[Date] ;

C'est donc la seule manière de remplacer Existing dans le cas d'utilisation de sous-cube pour le filtrage depuis le script MDX. L'article de Mosha sur les multiselect friendly MDX calculations (http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm) est donc de moins en moins utile avec cette évolution d'Excel. On en vient à utiliser des Dynamic Sets à la place des Existing, comme montré dans ce très bon post (http://ms-olap.blogspot.com/2010/02/solution-von-ssas-2008-multi-selects-in.html) d'Hilmar Buchta.

Les subselects on d'autres implications, entre autres choses en plus d'appliquer Exists ils appliquent aussi VisualTotals, ce qui a d'autres effets inéressants, comme ceux décrits récemment par Jeffrey Wang dans ce post (http://mdxdax.blogspot.com/2011_08_01_archive.html)

Voilà en espérant vous avoir raffraichi la mémoire.

A bientôt!

[SSAS] DistinctCount sans les NULL et NullProcessing=Preserve

2011-07-22T14:45:00.006+02:00

"NULL is the Chuck Norris of the database .

Nothing can be compared to it."

Il arrive à tout consultant de se faire avoir par une fonctionnalité qu'il croyait tellement logique qu'il ne pensait pas qu'elle puisse être mal implémentée. Là en l’occurrence il s'agit du DistinctCount dans Analysis Services sur des colonnes potentiellement nulles.

Comptage des NULL en relationnel

Dans toute base SQL, un COUNT(MaColonne) ou les valeurs de MaColonne sont nulles ignore les NULL. Il en est de même pour les COUNT(DISTINCT MaColonne).

SELECT COUNT(DISTINCT MaColonne)

FROM

(

VALUES

(NULL),(1)

)

T(MaColonne)

Vous pouvez tester avec ceci qui renverra invariablement "1"

Comptage en MDX

Passons à Analysis Services. Dans la même logique en MDX, tout fonctionne exactement de la même manière. Si vous tapez:

WITH

MEMBER CountOfT

DistinctCount

(

{

[Time].[Year].&[2011]

, Null

}

)

SELECT CountOfT ON 0

FROM [Cube]

Vous obtiendrez le même résultat que précédemment à savoir 1.

Comptage des NULL en agrégation DistinctCount

En revanche si vous créez une mesure (et donc un groupe de mesure dédié) en DistinctCount sur une colonne potentiellement nulle, vous constaterez que... les NULL sont comptés. Bien sûr auparavant vous aurez pris soin de passer le NullProcessing en Preserve sur la mesure pour que les NULL ne soient pas convertis en 0 par SSAS (le comportement par défaut revient à ZeroOrBlank: les convertir en zéro ou chaine vide selon le type de la mesure.)

D'après Charles Wang dans ce post il s'agit a priori d'un bug de SSAS.

La seule solution - comme dit par Deepak Puri dans le post cité au dessus - revient donc à appuyer le groupe de mesure dédié du DistinctCount sur une Named Query qui filtre les NULL sur la table d'origine et non sur cette dernière directement.

Si vous avez d'autres solutions, n'hésitez pas. Et moi maintenant je teste avant de m'avancer :)

SQL Server Denali CTP 3, les nouveautés décisionnelles

2011-07-13T00:44:00.005+02:00

Vous pouvez dès à présent télécharger SQL Server Denali CTP 3 à cette adresse.

Un petit focus sur les nouveautés intéressantes dans le domaine du décisionnel introduites par cette nouvelle CTP.

Analysis Services

-L'introduction de BISM, BI Semantic Model, le nouveau modèle de conception de cube utilisant une version serveur du moteur Vertipaq de PowerPivot.

Reporting Services

-Le projet Crescent, le nouvel outil de design de rapport interactif, orienté conception et consommation en ligne, conçu pour fonctionner sur BISM. Il remplace Report Builder 1.0, RB 3.0 existe toujours, il est lui sobrement renommé Report Builder.

-La possibilité de mettre en place des alertes pour être notifié de changements dans les données sous-jacentes aux rapports en mode Sharepoint

Autres

BIDS passe sous Visual Studio 2010 :)

[SSRS] DrillDown to... à la Excel sous Reporting Services

2011-07-12T00:56:00.000+02:00

Que voilà une demande intéressante et amusante. Dans Excel, il y a sur les hiérarchies la possibilité d'effectuer un Drill-Down to jusqu'à un niveau spécifique. Comprendre que l'on n'est pas obligé de descendre toute la hiérarchie pour enfin arriver au niveau souhaité. Si il y en a une dizaine, ça économise des clics :)

Dans Reporting Services, les boutons de Drill sont positionnés potentiellement sur une TextBox lorsqu'elle est par exemple le ToggleItem d'un groupe. Ce toggle est déplié, ou plié par défaut.

Alors comment faire pour satisfaire à la demande en question? Simplement en instaurant un paramètre masqué DrillDownLevel, qui va permettre via des Actions sur des TextBox de réappeller le rapport avec un DrillDownLevel spécifique. Ce DrillDownLevel paramétrant qui gouverne l'état visible ou invisible du groupe.

Création du paramètre.

Rien de sorcier: un paramètre technique masqué de type integer fera très bien l'affaire. Ne pas oublier de lui mettre une Default Value à 0.

Configuration des groupes

La seule petite subtilité est celle ci: la visibilité du groupe n'est pas True ou False en fixe: elle dépend de la valeur de DrillDownLevel: pour l'item de niveau 1, il est masqué tant que le DrillDownLevel est inférieur à 1.

Sur la même logique la TextBox dépliant ce groupe (généralement dans le groupe parent) est à l'état [-] dès que ce DrillDownLevel est supérieur ou égal à 1.

Une action pour commander tout ça

J'ai fait le choix de commander la chose via des actions sur les entêtes de colonne. Sur chaque entête de colonnes on positionne une action GoToReport, en passant la valeur appropriée de DrillDownLevel si on est sur la bonne colonne.

Et voilà le résultat! (Je sais je suis nul en choix de couleurs, c'est un de mes défauts préférés).

Bon reporting!

[SSAS] Sécurité dynamique avec une Factless

2011-07-10T10:17:00.002+02:00

Ce sujet a été maintes fois abordé, mais je me retrouve souvent à expliquer le concept, voilà un petit rappel qui pourra en aider certains. Des détails d'implémentation sont trouvables un peu partout sur le net du moment que l'on cherche bien :)

J'ai déjà parlé il y a longtemps de l'implémentation de la sécurité dynamique de dimensions (Dynamic Dimension Security en bon langage de newsgroup SSAS). Il s'agit pour ceux qui n'ont pas envie de limiter la vue des membres d'une dimension par une expression MDX dynamique pour chaque utilisateur, ce qui est possible par l'utilisation de la fonction MDX UserName, qui renvoie l'ID de l'utilisateur connecté.

C'est très facile à faire lorsque l'on a une notion d'utilisateur dans le cube, comprendre lorsque les faits à sécuriser sont à la granularité de l'utilisateur: on rajoute le login en tant que Member Property dans le cube et c'est presque terminé. C'est d'ailleurs ce que j'avais fait dans mon exemple.

En revanche que faire lorsque le cube est à une granularité supérieure? Lorsque la notion d'utilisateur n'y est pas représentée d'un point de vue métier? On peut alors représenter cette notion d'utilisateur dans une ou plusieurs tables de faits dédiées, et gérer la sécurité à l'aide de relations Many-To-Many. C'est d'ailleurs ce que l'on fait en SQL lorsque l'on doit gérer de la sécurité à la ligne de données.

Une dimension technique d'utilisateur

On va d'abord créer une dimension Utilisateur, qui a un sens uniquement technique. Potentiellement cette dimension peut ne contenir que deux attributs, une Surrogate Key et un identifiant type Active Directory. Cet identifiant peut aussi être un identifiant autre, applicatif que l'on pourra passer via le noeud CustomData de la chaîne de connexion (plutôt que d'utiliser le UserID).

Une table de faits Factless par Securable

Mettons que l'on ait un reporting dans le cube fait par Unité Organisationnelle, chacune pouvant être vue par un ou plusieurs utilisateurs: on va créer une table de faits FactlessDroitsUniteOrganisationnelle qui sera simplement composée d'un doublet (UniteOrganisationnelleID, UtilisateurID). On crée un groupe de mesure pour chacune dans le cube. On a donc une relation Fait -> DimUniteOrganisationnelle <- FactlessDroitsUniteOrganisationnelle -> DimUtilisateur.

Une relation Many-To-Many par Securable

Chaque fait relié à un élément à sécuriser dispose alors d'une relation Many-To-Many avec une instance en Role Playing Dimension de la dimension Utilisateur. On crée donc dans le cube autant d'instances de cette dimension que l'on a de dimensions à sécuriser. Pour chaque table de faits concernée par une relation avec une dimension à sécuriser, on rajoute dans le Dimension Usage une relation Many-To-Many avec l'instance appropriée de la dimension Utilisateur (i.e. les tables de faits en lien Regular avec les UO se retrouvent alors en lien M2M avec l'instance de dimension UtilisateurUO à travers le groupe de mesure basé sur FactlessDroitsUniteOrganisationnelle).

Et côté rôles...

On se retrouve alors à sécuriser la dimension Utilisateur (pas les instances ou CubeDimensions, la Shared Dimension). ce qui descend la sécurité sur toutes les instances. On utilise alors une sécurité basée sur UserID (ou CustomData si on est dans un système différent).

Conclusion

Cette solution de sécurité, que j'ai déjà implémenté avec un succès certain, n'a finalement comme inconvénient que de rajouter un certain nombre de tables. Elle est en revanche relativement transparente et assez facile à administrer, et est surtout simplissime à gérer côté SSAS?
A noter qu'une foultitude d'autres solutions existent, et que le codage d'une extension en .NET pour gérer la sécurité, s'il est dangereux car appliqué à chaque requête, peut parfaitement être considéré comme une approche robuste.

[SSAS] Une fonction Split en full MDX

2011-04-07T16:13:00.007+02:00

J'en avais déjà parlé dans ce post, il manque une fonction Split à MDX. Non pas que cela ne puisse pas se coder dans l'absolu - les procédures stockées managées en .NET sont pour ça - mais je me suis dit qu'en coder un en MDX pouvait être une application marrante de la récursivité (bien que complètement inutile hormis mon plaisir personnel je vous l'avoue).

Alors en MDX comment faire? Il n'y a pas de boucles, donc la seule technique valable est d'utiliser la récursivité. Car oui MDX permet de faire des récursions, ce qui signifie qu'il autorise un membre à se faire référence à lui même dans sa définition. On a parfaitement le droit d'écrire ça par exemple:

WITH
MEMBER Measures.[Toto]
AS ([Date].[Date].PrevMember, Toto)*1.1

Pour plus de détails allez voir l'excellent post de Mosha à ce sujet.

Alors comment cela peut-il m'aider dans mon cas?

Commençons déjà par définir deux membres de base, ma chaîne et le caractère de Split:

WITH
MEMBER String
AS "0,1,22,333,4444,55555"

MEMBER Separator
AS ","

Le but va donc être de sortir une mesure qui pour un jeu de membres donnés va retourner toutes les sous-chaînes délimitées par le séparateur. Tout ce dont vous avez besoin c'est donc d'un ensemble de membres assez grand (plus de membres que d'occurences du séparateur) pour affecter votre mesure. J'ai pris le niveau fin de la dimension Date d'Adventure Works mais vous faites comme vous voulez.

Mon membre Splitter est défini comme suit:

MEMBER Splitter AS
CASE
WHEN ([Date].[Date].CurrentMember IS [Date].[Date].[Date].Members.Item(0))
AND (VBA!InStr(String,Separator) > 0)
THEN String
WHEN (VBA!InStr([Date].[Calendar].PrevMember,Separator) > 0)
THEN Right
(
[Date].[Calendar].PrevMember,
Len
(
[Date].[Calendar].PrevMember
)
-
InStr
(
[Date].[Calendar].PrevMember,
Separator
)
)
ELSE
NULL
END

Cas 1: Si on est au premier membre du jeu, on affecte le premier membre à la valeur de la chaîne de caractère initiale

Cas 2: Si on est un autre membre et qu'il reste des séparateurs dans la chaîne, on prend la partie droite du membre précédent (la récursivité est là) jusqu'au premier séparateur.

Cas 3: Le travail est terminé, on retourne NULL

Ce membre retourne cela:

C'est presque ce qu'il me faut. Je rajoute juste un membre qui prend la partie gauche de chaque valeur de Splitter:

MEMBER Splitted
AS
CASE
WHEN VBA!InStr(Splitter,Separator)>0
THEN VBA!Left(Splitter,VBA!InStr(Splitter,Separator)-1)
ELSE Splitter
END

SELECT [Splitted] ON 0,
NON EMPTY {[Date].[Date].[Date].Members} ON 1
FROM [Adventure Works]

Et j'obtiens:

Tadaaa. Certes assez inutile j'en conviens, mais à mon avis un bon exemple de l'utilisation de la récursivité pour de l'itération en MDX.

A bientôt!

EDIT du 28/04/2011: à signaler au passage ce superbe - comme souvent - article de Jeffrey Wang sur la récursivité en MDX: http://mdxdax.blogspot.com/2011/04/performance-considerations-for.html

[SSRS] Un paramètre pour rechercher... en SQL

2011-03-30T00:21:00.000+02:00

Une petite variante sur le même mode de ma recherche dans une dimension en MDX qui commence à dater: la version SQL.

Deux choses à créer

- Un paramètre multivalué (le champ de recherche)

- Un DataSet SQL (le résultat)

On commence par créer un joli DataSet sur une source SQL qui a la structure de ce que l'on veut renvoyer. Pour moi ce sera ça:

SELECT FrenchProductName

FROM AdventureWorksDW2008..DimCustomer

On le valide pour avoir les métadonnées. Maintenant on va créer une expression en lieu et place de cette requête, en cliquant sur le bouton ad-hoc:

L'expression a pour but de générer une requête like %% pour chaque mot recherché, le tout unionné et ordonné par nombre de matches (bon je vous l'accorde ça n'est pas PageRank).

SELECT FrenchProductName

FROM

(

SELECT ProductKey, FrenchProductName

FROM AdventureWorksDW2008..DimProduct

WHERE FrenchProductName LIKE '%vélo%'

UNION ALL

SELECT ProductKey,FrenchProductName

FROM AdventureWorksDW2008..DimProduct

WHERE FrenchProductName LIKE '%randonnée%'

)

AS T

GROUP BY ProductKey, FrenchProductName

ORDER BY COUNT(*) DESC, FrenchProductName ASC

Pour générer cela on va donc créer une expression qui est d'abord constituée du début et de la fin de la requête.

"SELECT FrenchProductName, COUNT(*) Rank "+

"FROM "+

"("+

...

")"+

"AS T "+

"GROUP BY ProductKey, FrenchProductName "+

"ORDER BY COUNT(*) DESC, FrenchProductName ASC"

Entre chaque élément contenu dans le paramètre Recherche, on va intercaler du SQL suffisant à générer l'ensemble de requête UNION visibles au dessus.

Join

(

Parameters!Recherche.Value,

"%' "+

"UNION ALL "+

"SELECT ProductKey,FrenchProductName "+

"FROM AdventureWorksDW2008..DimProduct "+

"WHERE FrenchProductName LIKE '%"

)

Ne reste plus qu'à rajouter le code SQL suffisant à traiter le premier et le dernier élément de la liste et on a le résultat final:

="SELECT FrenchProductName, COUNT(*) Rank "+
"FROM "+
"("+
"SELECT ProductKey, FrenchProductName "+
"FROM AdventureWorksDW2008..DimProduct "+
"WHERE FrenchProductName LIKE '%"+
Join
(
Parameters!Recherche.Value,
"%' "+
"UNION ALL "+
"SELECT ProductKey,FrenchProductName "+
"FROM AdventureWorksDW2008..DimProduct "+
"WHERE FrenchProductName LIKE '%"
)+
"%') AS T "+
"GROUP BY ProductKey, FrenchProductName "+
"ORDER BY COUNT(*) DESC, FrenchProductName ASC"

Une fois l'expression définie comme requête du DataSet on peut la tester, par exemple en la donnant comme source d'un paramètre:

Voilà, à bientôt!

[SSRS] Traitement des hiérarchies Ragged dans le Query Designer de RS

2011-03-29T20:47:00.001+02:00

Le Query Designer de Reporting Services pour la partie MDX a une particularité sympathique que vous avez peut être déjà remarqué si vous ou vos utilisateurs utilisez Report Builder.

Il gère l'utilisation des hiérarchies Ragged n'importe comment. Et je me suis rappellé de ça hier soir en faisant une démo.

Pour rappel une hiérarchie ragged est une manière de gérer des hiérarchies déséquilibrées (ou la distance entre (All) et le membre le plus fin d'une branche n'est pas constante: cas typique des employés d'une entreprise...) avec une hiérarchie utilisateur "classique" (et non une Parent Child) principalement pour améliorer les perfs et contourner la relative mauvaise gestion des P/C par SSAS. On utilise pour ça la propriété HideMemberIf sur les niveaux de la hiérarchie utilisateur pour cacher des niveaux superflus.

Une fois réglé les problèmes connus (on borde par le bas, i.e. pour que ça marche les niveau "à vide" devront être vers le bas et non pas au milieu de la hiérarchie, cf ce post de Chris Webb) on est tous contents: ça marche dans Excel, dans les OWC (BIDS + SSMS) et dans Reporting Services... voir ci dessous, sans commentaires:

Pour information ici j'ai projeté une Ragged de 6 niveaux créée en HideMemberIf=ParentName, ainsi qu'une mesure de base, et j'ai filtré avec un attribut qui stocke le niveau (i.e. la distance au membre (All)) pour ne prendre en compte que les membres jusqu'au niveau 5, donc pas le niveau fin.

Et ça ne me renvoie rien: alors que je vous assure, j'ai des données.

Alors pourquoi?

Tout simplement parce que ce Query Designer est stupide à souhait. Désolé de le dire mais c'est le cas. Si je fais rapidement une analyse de ce qu'il me projette en lignes je verrais que c'est ce set là:

{ ([Organisation].[Hiérarchie Organisations].[Niveau 6].ALLMEMBERS ) }

Or effectivement, je n'ai pas de membres niveau 6. Si j'écrivais ce MDX, dans le but de projeter tous les membres de toute une hiérarchie j'écrirais quelque chose de la sorte:

{ ([Organisation].[Hiérarchie Organisations].ALLMEMBERS ) }

Puisque je n'ai jamais demandé de me filtrer uniquement les membres du Niveau 6. Bref en éditant le MDX je récupère ce que je veux , des (null) aux niveaux non renseignés. Au passage le NON EMPTY m'enlève le niveau 6 (cf mon filtrage plus haut).

Donc je dois faire écrire du MDX à mes utilisateurs dans Report Builder?

Hmmm. Autant vous dire tout de suite que ça pourrait être une solution immédiate mais que ce n'est pas forcément celle que je vous recommande :).

L'investissement en formation serait à mon avis un poil disproportionné. Comme ça je vois une solution utilisable dans le designer: rendre les niveaux de la hiérarchie utilisateur visibles.

Dès lors il suffit de notifier aux utilisateurs que ce sont ces hiérarchies d'attributs qui doivent être utilisées dans ce type de cas pour afficher les niveaux par CrossJoin, la précédente hiérarchie étant alors utilisée uniquement comme filtre. Problème: avec mes 6 niveaux cela risque rapidement de hurler. L'utilisateur n'aime pas trop enchaîner les glisser-déposer.

Si vous avez une suggestion, n'hésitez pas à la donner, je suis preneur.

Bonne journée!

PS: Vous avez le droit de voter pour corriger cela, sur Connect.

[TD 2011] Les webcasts

2011-03-18T15:14:00.006+01:00

Les webcasts des TechDays 2011 sont disponibles sur le site.
En ce qui concerne notre session avec Romuald, Exploiter Analysis Services au maximum de son potentiel, vous pouvez la regarder ci-dessous, télécharger la présentation PowerPoint, ou aller la visualiser sur sa page d'origine.

A bientôt!

CEP vs ETL, StreamInsight et SSIS dans la BI

2011-03-09T20:47:00.007+01:00

Je suis de retour du MVP Summit (avec un très bel event au Safeco Field de Seattle que vous voyez ici) et à nouveau chez mon client préféré! Hormis vous dire que ce que nous avons appris augure de bien belles choses sur SQL Server Denali en général, et sur ses modules BI en particulier, ne comptez pas trop sur moi pour aller plus loin dans les détails car j'ai signé un NDA avec mon sang...

Par contre plus sérieusement j'ai été pas mal intéressé là bas par les scénarios de convergence des différents produits sortis ou à venir. Il est vrai qu'après une grosse phase de simplification marketing autour de briques bien identifiées (SQL Server 2005), l'offre BI à l'horizon Denali devient de plus en plus complexe à lire pour nos chers clients et utilisateurs. Par conséquent la recherche à minima de convergences entre les différents modules est un sujet intéressant.

Intéressant techniquement pour adresser plus de problématiques, intéressant pédagogiquement car c'est de notre faculté à rendre cette offre lisible que dépendra en grande partie l'adoption des ces produits.

Le positionnement du CEP StreamInsight par rapport à l'ETL SSIS est un bon exemple de cela.

ETL et CEP dans la BI

Un ETL (Extract Transform Load) comme SSIS est le produit traditionnellement utilisé pour charger les entrepôts de données ou data warehouses. Orienté batch (ensemble de traitements bien identifiés) et planifié à une certaine fréquence, il est utilisé pour aller chercher les données dans les bases opérationnelles, et les amener dans les bases d'analyse, non sans leur avoir appliqué un nombre généralement considérable de vérifications, aggrégations, filtrages et autres consolidations.

Depuis quelques années et l'avènement du "mythe" (entre guillemets car il devient petit à petit réalité) des entrepôts de données (near)real-time ou zero-latency illustré côté Microsoft par des features telles que le Proactive Caching dans SSAS (pour ne citer que lui), on parle d'une supplantation des ETL par des CEP, des moteurs de Gestion d'Evenements Complexes (Complex Event Processing).

Un CEP est un moteur d'analyse de flux (souvent) constants de données, à la recherche de singularités, ou simplement à des fins d'aggrégation.

StreamInsight, sorti avec SQL Server 2008 R2 est le CEP de SQL Server, il s'agit d'un moteur utilisable en .NET dont les règles de traitement et d’agrégation et l'expression de KPI s'écrivent par des requêtes Linq sur les flux entrants, donnant des possibilités quasi infinies en terme de transformations, le tout en temps réel. Le pas est vite franchi pour certains oracles du décisionnel: ils prédisent que les ETL et les bases temporaires feront bientôt partie du passé.

Remplacer SSIS par StreamInsight dans l'alimentation d'entrepôts?

Soyons brefs: à date et sauf pour des ultra particuliers, il n'en est évidemment pas question, en tous les cas aujourd'hui, et ce pour plusieurs raisons:

1) Pas de composants spécialisés ETL: StreamInsight pour le développeur est un framework: les composants de DataFlow que l'on est amenés à utiliser en ETL avec SSIS seraient à recoder, ainsi que les interfaces d'entrée, de sortie...

2) Trop orienté développeur: utiliser StreamInsight à la place de SSIS aujourd'hui est aussi aisé que d'utiliser SSIS uniquement par le biais de son API de génération de packages... Disons pas aussi simple qu'avec un designer graphique.

3) Administration limitée: là encore les fonctionnalités out-of-the-box de StreamInsight sont restreintes car répétons-le ce n'est pas son usage premier: un CEP doit traiter des données en masse et peut se permettre des erreurs: un entrepôt contient la version corporate de la "vérité": il n'y a pas de droit à l'erreur la supervision doit être facile et les erreurs aisément traçables.

Au bilan je ne crois pas que sauf pour des tables de faits spécifiques, ou des entrepôts dédiés à la latence zéro on n'assiste avant quelque temps à la gestion de l'ETL sous SI. En revanche Microsoft est tout à fait conscient de l'intérêt suscité par certaines potentialités de StreamInsight pour les développeurs ETL...

Avant la fusion, des scénarios de convergence...

StreamInsight permet en effet de requêter des flux en Linq: quelle puissance cela donnerait de pouvoir faire les mêmes requêtes sur des PipelineBuffer, ce qui revient en effet à embarquer un moteur StreamInsight dans SSIS.

A l'inverse, pour adresser l'absence de composants dans StreamInsight ou pourrait nourrir des lots SSIS à partir de données issues de CepStream, scénario inverse ici puisque cela revient à embarquer un lot SSIS dans un flux StreamInsight.

Ping Wang et Wee Hyong Tok des équipes StreamInsight et SSIS ont écrit un WhitePaper inspiré comme souvent de retours clients sur ce type d'usages de ces deux produits couplés: vous pouvez le trouver ici.

En espérant avoir clarifié quelques points et vous avoir donné quelques idées.

A bientôt!

[TechDays 2011] Merci!

2011-02-11T11:47:00.006+01:00

Encore une fois merci à tous ceux (très nombreux) qui sont venus nous voir en session aux TechDays cette année avec mon collègue Romuald Coutaud!

Comme toujours beaucoup de questions très intéressantes, d'échanges de points de vues enrichissants... Vivement l'année prochaine!

Je mettrai très prochainement sur mon SkyDrive les exemples abordés en démonstration, n'hésitez pas à venir jeter un coup d'oeil de temps en temps!

En plus de tous les participants, je voulais remercier encore une fois Access IT IDF qui nous a soutenus et conseillés pendant la préparation, ainsi que l'équipe SQL Server et Application Platform de Microsoft pour leur support et leurs retours sur la session, ainsi que leur très gentil accueil pendant ma petite journée d'ATE sur le stand SQL avec Jean-Pierre Riehl :)

[SSIS] Concaténer les valeurs d'une ligne de données

2011-02-11T11:34:00.004+01:00

Petit besoin intéressant: vouloir reproduire le fonctionnement des sources de Data Flow en concaténant les valeurs de toutes les colonnes d'une ligne de Pipeline dans une colonne unique (à des fins de log par exemple, pour tout mettre dans une seule table SQL).

La technique n'est pas très compliquée, il suffit de savoir comment s'y prendre au départ.

Tout d'abord il faut que le composant soit dynamique: cela veut dire que le script doit marcher pour tout buffer d'entrée. Cela élimine immédiatement la méthode ProcessInputRow(ScriptBuffer) au détriment de ProcessInput(PipelineBuffer). Il est en effet impossible d'accéder à une ligne d'un ScriptBuffer via une méthode générique, il faut utiliser des accesseurs qui portent le nom de la colonne, que par essence on ne connaît pas (et j'annonce: la Reflection n'est pas une option ici, même si la première fois que j'ai codé ce script je l'ai fait comme ça...).

Donc pour travailler avec un PipelineBuffer il faut connaître l'index de la colonne à récupérer: bonne pioche, le Script Component bénéficie d'une méthode GetColumnIndexes() qui renvoie les indexes des colonnes d'un Input100 ou Output100. Une fois ces indexes récupérés, on a plus qu'à concaténer avec un StringBuilder.

Le code (simplissime) est ci-dessous (à noter que je n'ai qu'une seule colonne dans mon Output100: ma valeur de concaténation).

public class ScriptMain : UserComponent
{
private int[] columnIndexes;
private int outputColumnIndex;
public override void PreExecute()
{
    base.PreExecute();
    columnIndexes = this.GetColumnIndexes(this.ComponentMetaData.InputCollection[0].ID);
    outputColumnIndex = this.GetColumnIndexes(this.ComponentMetaData.OutputCollection[0].ID)[0];
}

private string RowConcat(PipelineBuffer b, int[] columnIndexes, char separator)
{
    StringBuilder sb = new StringBuilder();
    foreach (int columnIndex in columnIndexes)
    {
        if (b[columnIndex] != null)
            sb.Append(b[columnIndex].ToString());
        sb.Append(separator);
    }
    return sb.ToString();
}

public override void ProcessInput(int InputID, PipelineBuffer Buffer)
{
    while (Buffer.NextRow())
    {
        Buffer.SetString(outputColumnIndex, RowConcat(Buffer,columnIndexes,';'));
    }
}
}

[TD 2011] Les TechDays c'est parti!

2011-01-06T10:34:00.006+01:00

Dernière ligne droite pour les TechDays 2011, qui auront lieu du 8 au 10 février au traditionnel Palais des Congrès. Vous pouvez déjà depuis quelques jours vous inscrire sur le site dédié avec l'identifiant Live qui va bien.

J'aurais le plaisir de vous y retrouver sur le stand Access IT, mais aussi en session: avec Romuald Coutaud mon éminent - que dis-je extraordinaire - collègue, nous aurons le plaisir de vous présenter une session sur les usages avancés d'Analysis Services. Dit autrement nous présenterons les fonctionnalités que tout les développeurs et administrateurs ne connaissent pas forcément, mais qui font d'un projet AS bien plus qu'un simple TCD sous Excel: les assignments, les dimensions techniques, les actions, les traductions, le Proactive Caching, la gestion des aggrégations... et bien d'autres choses encore, articulé autour de retours d'expérience et d'exemples concrets. C'est la session DBI301 intitulée "Exploiter SQL Server Analysis Services au maximum de son potentiel".

Au niveau des autres sessions BI, je retiendrai particulièrement celle de Gaël Duhamel sur du Data Mining articulé a priori autour de Commerce Server, connaissant ses dernières publications et sa spécialisation. L'articulation du mining d'AS dans CS est un sujet qui m'intéresse et que je n'ai jamais vraiment eu le temps de creuser.

A voir aussi la session de Reporting Services dans Azure, là aussi une chose qui risque d'exploser dans l'année, et qui fait partie de la tête de gondole des priorités de MS dans la BI.

Je n'oublierai pas mon ancien collègue Aurélien Koppel, qui présente un scénario de PowerPivot dans Sharepoint, sujet là aussi intéressant dans la mesure ou je n'y ai pas touché depuis les CTP de Kilimandjaro et que je n'ai pas eu le plaisir de le voir en projet.

Je ne vais pas lister toutes les sessions ici, je vous laisse fureter sur le site: vous verrez que comme chaque année un grand nombre de thématiques sont abordées, et qu'il va être dur de faire un choix.

J'espère en tous les cas vous retrouver là bas cette année encore!

A bientôt!

Donald Farmer quitte Microsoft

2011-01-06T10:30:00.004+01:00

La news est tombé sur son blog et a été reprise par Décidéo, Donald Farmer, un des principaux artisans d'Analysis Services depuis la 10 ans quitte Microsoft pour... QlikTech pour exercer le job d'évangéliste sur QlikView.

De la part du penseur de PowerPivot rien de très révolutionnaire, il précise d'ailleurs que la raison de son départ n'est pas technologique, mais plus liée à son besoin de structures plus petites, avec moins de pression.

Après Mosha c'est une autre page qui se tourne sur AS...

Bonne année 2011

2011-01-01T19:27:00.003+01:00

Je vous souhaite à tous une excellente année 2011. En espérant qu'elle soit aussi riche pour le sujet qui nous intéresse qu'espéré, avec les previews puis les releases à venir de Crescent et de BISM au sein de SQL Server Denali (11).

Merci encore à ceux qui prennent le temps de lire ce blog, je penserai à vous cette année!

[SSAS] Une dimension tranches d'âge ou comment jouer avec le temps en MDX

2010-12-02T15:55:00.007+01:00

Je ne sais pas si vous avez déjà eu envie de gérer une dimension de tranches d'âges (ou d'une quelconque durée) sous SSAS. Intuitivement ce n'est pas très simple car cela fait partie des choses qui doivent être gérées en pur MDX, a fortiori dans un domaine (les dates) ou la moindre erreur peut transformer la requête en gouffre à performances.

Alors voici une petite méthode éprouvée. Encore une fois il ne s'agit pas là de la solution (je suis un garçon assez modeste :) ) mais d'une manière de faire qui me donne toute satisfaction. Je suis bien sur ouvert à toute suggestion d'amélioration.

Première étape: créer sa table de base

Personnellement je la crée dans la DSV: c'est une table technique qui a mon sens n'a pas grand chose à faire dans mon beau Data Mart (mais elle pourrait y être si le coeur vous en dit).

Sa structure est simple: un code/libellé de tranche, ainsi que deux attributs entiers supplémentaires, les bornes inférieures et supérieures de l'intervalle.

Voici la requête de construction, basée sur le beau TABLE VALUE CONSTRUCTOR de SQL Server 2008:


SELECT * FROM
(
VALUES
(1, '0 - 24 ans', 0, 24),
(2, '25 - 29 ans', 25, 29),
(3, '30 - 34 ans', 30, 34),
(4, '35 - 39 ans', 35, 39),
(5, '40 - 44 ans', 40, 44),
(6, '45 - 49 ans', 45, 49),
(7, '50 - 54 ans', 50, 54),
(8, '55 - 59 ans', 55, 59),
(9, '60 ans +', 60, 70)
) AS t (TrancheAgeId, TrancheAgeLabel, BorneInf, BorneSup)

Comme vous le voyez rien de rare.

Seconde étape: a little MDX

Dans mon cas mon cube est assez simple: j'ai une dimension temps en usage classique (qui référence mon fait) et une dimension temps référencée qui jour le rôle de dimension "Date de naissance". Les deux ont même structure (si vous connaissez LinkMember, ceci a évidemment un intérêt non négligeable). Ma dimension tranche d'âge est ajoutée aux CubeDimensions mais sans usage, c'est ce que nous appelons une dimension technique (comme le DateTool de Marco Russo par exemple).

Comme je n'aime pas taper des kilomètres de MDX, j'ai essayé de compacter un brin la définition de cette joyeuseté d'assignment.

Le but est pour chaque Tranche d'âge d'aggréger la mesure courante (Aggregate) sur l'intervalle de la tranche d'âge en cours, en prenant bien soin de forcer le set sur lequel on aggrège sur le DefaultMember des tranches d'age (au moment de l'assignment, les tranches sont à null sauf la tranche par défaut (All) qui comprend les données: si on ne spécifie pas cela, le CurrentMember sur le set à aggréger se positionne sur la tranche en cours, qui n'a pas de données).

SCOPE ([Tranche Age].[Tranche Age].[Tranche Age].Members);
This =
Aggregate
(
[Tranche Age].[Tranche Age].DefaultMember*
]DebutTranche[
:
]FinTranche[
END SCOPE;

Il reste à calculer ces deux "bornes" de tranche. On peut le faire en effectuant tout d'abord des opérations sur la dimension Temps: le membre bas de la tranche d'âge est le jour courant sur la dimension Temps - vous noterez au passage que la date actuelle dans mon cas est un ClosingPeriod au niveau jour: car mon groupe de mesure est à la granularité mois avec une dimension au jour - moins la borne inférieure de la Tranche d'Age. Ce "moins" (un DateAdd en SQL) se gère très bien avec un ParallelPeriod en MDX. L'intervalle recherché étant un intervalle sur la Date de Naissance, on ramène le membre issu de la dimension Temps sur la dimension Date de Naissance via un LinkMember, autorisé car on a même structure sur les deux dimensions.

   ParallelPeriod
(
    [Date Naissance].[Calendrier].[Année],
    [Tranche Age].[Tranche Age].CurrentMember.Properties("Borne Sup"),
    LinkMember
    (
     ClosingPeriod([Date Actuelle].[Calendrier].[Jour])
     ,[Date Naissance].[Calendrier]
    )
)

On arrive finalement au bout de script suivant:

SCOPE ([Tranche Age].[Tranche Age].[Tranche Age].Members);
This =
Aggregate
(
[Tranche Age].[Tranche Age].DefaultMember*
ParallelPeriod
(
    [Date Naissance].[Calendrier].[Année],
    [Tranche Age].[Tranche Age].CurrentMember.Properties("Borne Sup"),
    LinkMember
    (
     ClosingPeriod([Date Actuelle].[Calendrier].[Jour])
     ,[Date Naissance].[Calendrier]
    )
   )
:
ParallelPeriod
(
    [Date Naissance].[Calendrier].[Année],
    [Tranche Age].[Tranche Age].CurrentMember.Properties("Borne Inf"),
    LinkMember
    (
     ClosingPeriod([Date Actuelle].[Calendrier].[Jour])
     ,[Date Naissance].[Calendrier]
    )
   )
);
END SCOPE;

Voilà pour la pause MDX du jour, en espérant que cela puisse vous servir.
A bientôt!

[SSAS] Polémiques sur SSAS, PowerPivot et l'avenir de l'UDM

2010-11-16T21:26:00.004+01:00

Si vous êtes abonnés aux blogs qui comptent en BI MS vous avez sûrement pu assister à un sacré grabuge. Rarement une annonce de release et un summit PASS n'auront déclenché autant de réactions.

Les faits

En résumé, T.K. Anand (PM chez Corp) a présenté la semaine dernière au PASS les nouveautés de SSAS dans Denali et quelques pistes sur l'avenir. Dans sa présentation, une seule slide sur SSAS "traditionnel" dit aussi UDM - concernant surtout des ajouts très très mineurs - tout le reste sur l'intégration de VertiPaq (le moteur de PowerPivot) dans SSAS, sous le nom de BISM (BI Semantic Model). Au menu, une vraie intégration serveur, un modèle de sécurité, des KPI..

La goutte d'eau a été l'annonce que Crescent (l'outil de reporting full web) ne sera compatible... que BISM.

Cela a entraîné une crainte légitime de la plupart des membres de la communauté, résumées par Chris Webb dans ce post. En gros, devons nous, à cause de ce que l'on pourrait appeler une "mode", devenir des utilisateurs de PowerPivot sous Visual Studio? Quid des gros projets UDM vantés à longueur de BI Conference ou de PASS, ils sont devenus soudainement inintéressants?

T.K. Anand a alors fourni une réponse plutôt intéressante, et qui peut paraître étrange à première vue. Son approche est de faire un parallèle entre UDM vs BISM et le combat périmé entre natif et managé, entre C++ et C# qui a eu lieu à l'apparition de .NET.

Pour lui, SSAS UDM est mature: il n'a plus besoin de grosses adjonctions. Par contre, comme C++ il est trop compliqué pour la plupart des projets et mal maîtrisé par la plupart des développeurs entraînant une pénurie de ressources - vous connaissez combien de consultants SSAS? et combien de compétents? - et des projets mal réalisés.

La solution est donc de le conserver pour les projets de grande envergure, qui le nécessitent, et à des "experts" dédiés, et de fournir une solution plus simple pour le vulgus des développeurs. BISM répondra à la plupart des petits projets - la majorité - rien n'empêchant la migration plus tard vers un environnement UDM.

En résumé aujourd'hui les applications critiques, les OS sont en C++, les "petites" applis en C# et plus personne ne se bat pour savoir lequel est le meilleur. Ils conviennent chacun à des types de développeur, à des types de projets différents.

Ce que j'en pense

Alors mon avis là dessus est assez mitigé. Je suis d'accord sur l'analyse, moins sur son application.

1) Sur l'argument de l'accomplissement de l'UDM je ne suis pas complètement d'accord, SSAS n'est à mon avis pas terminé et il y a encore pas mal de boulot! Qui me viennent comme ça, parmi une multitude, quid d'une vraie gestion des Parent Child? De nouveautés sur la gestion de membres calculés (possibilité de gérer des hiérarchies de membres calculés...)? Et les fonctions de date en MDX? Quelques annonces (pour un SP1?) de ce genre de choses devrait rassurer tout le monde.

2) De même, dire que BISM suffira pour l'immense majorité des cas me semble un peu optimiste: je n'ai jamais vu de projets, même en PME ou on n'a pas besoin de faire un assignment d'une manière ou d'une autre. Soyons optimiste en pensant que ça suffira pour attraper beaucoup de projets ou l'OLAP dissuade aujourd'hui, et aller chasser sur les terres restantes de BO qui trouve là un adversaire bien plus puissant que lui. Reste alors à clarifier la position de BISM vis à vis de PowerPivot standalone et intégré...

Parce qu'avec une offre OLAP qui va de PowerPivot, PowerPivot intégré SharePoint, BISM et UDM les commerciaux vont bien s'amuser, et nous avec.

3) Enfin sortir Crescent qui est une vraie révolution et empêcher de l'utiliser sur les bases les plus répandues me semble être une hérésie commerciale. Chris dit que les équipes travaillent à résoudre cela. Dont acte.

Denali est annoncé comme fondamental surtout pour SSIS et SSAS. Côté ETL ça s'annonce presque parfait: tout ce dont nous avions souhaité semble être là.
Côté SSAS les produits annoncés et les nouveautés sont intéressantes j'adore DAX, j'ai bien aimé PowerPivot, l'idée de BISM me botte bien. Un peu de clarté commerciale devrait suffire à générer de l'engouement, je fais confiance à MS là dessus.

Moi, petit consultant, je demande juste qu'on laisse tranquille mon UDM et qu'on le fasse évoluer, et surtout que Crescent le supporte... C'est pas beaucoup demander non?

[News] Premières impressions sur Denali CTP1 et SSIS

2010-11-16T18:02:00.018+01:00

Je joue en ce moment avec la CTP1 de Denali. Hormis les nouveautés relationnelles très sympa (je pense au EXEC WITH RESULT SETS) que Christian Robert a très bien résumé, c'est surtout Integration Services qui bénéficie du plus gros lifting.

Enfin un designer du XXIème siècle!

BIDS dans cette CTP est encore sous VS 2008 mais le designer est maintenant en WPF! C'est l'occasion de profiter de quelques améliorations:

- C'est beaucoup plus joli, fluide, et à mon avis plus stable (bye bye COM!). L'avantage de l'abandon de MSDDS pour le diagraming est de rendre le XML de "rendu" beaucoup plus lisible et surtout parsable. Par exemple il sera possible de récupérer les commentaires (ça vous rappelle probablement un de mes posts...) puisqu'ils sont maintenant écrits en clair (enfin en XML commenté, sous forme de noeud AnnotationLayout). Sans compter que ça apporte un truc incroyable: on peut maintenant faire CTRL-Z ou CTRL-Y: une révolution!

- La toolbar est moins linéaire et permet de gérer des favoris, elle relègue les composants les moins utilisés dans des dossiers Others.

-Côté Data Flow, c'est aussi pas mal. Là aussi la toolbar est classée, des wizards apparaissent pour créer les adapteurs source et destination, mais en plus de ça on peut éditer les composants déconnectés, et pas en mode avancé. Si on rajoute la connectivité avec Data Quality Services c'est assez sympathique.

Des nouveautés aussi pour le déploiement

Dans le domaine du management des packages le déploiement et la partie serveur ont été complètement revus. (Ceci dit au passage, impossible de se connecter pour moi sans passer par l'UAC, cf ici).

Côté serveur de base, une node Integration Services apparait, permettant de créer un Catalog SSIS, en réalité une base, nommée par défaut SSISDB (et remplaçant donc MSDB, les DBA seront contents). C'est maintenant ici que se gèrent les lots SSIS en v11 et non plus dans le service auparavant dédié.

D'un point de vue logique, ce catalog comprend en suite des Projects: finie la structure bordélique des lots arrangés dans une foultitude de dossiers logiques. De plus ces projets sont nativement versionnés! (A l'assaut de TFS!!!)

De plus ce ne sont plus réellement les Dtsx que l'on déploie: comme l'a montré Jamie Thomson le build des projets SSIS génère une sorte de binaire (un fichier .ispac) dans un dossier /bin, qui est en fait ce qui est déployé. Comme le dit Jamie, ça commence sacrément à ressembler à du développement et plus trop à du vieux DTS... Et c'est tant mieux.

Côté BIDS, on peut maintenant, et sans BIDS Helper déployer directement depuis le projet, le réglage CreateDeploymentUtility disparaissant au profit du nom du serveur SSIS, là encore comme dans l'addin cité précedemment (et aussi AS, RS au passage). Il suffit dès lors dans un wizard de renseigner le Catalog, et le Project.

Les packages comprennent de plus des paramètres, des variables renseignables depuis le wizard de déploiement (et en l'état bindables à des variables d'environnement).

Ceci évite d'avoir à saisir des valeurs de variable type Package.Variables[XYZ].Value comme on devait le faire auparavant. De plus ces paramètres sont ensuite librement utilisables comme des variables dans le lot.

Une petite note sur Dependency Services

La nouvelle fonctionnalité Dependency Services, de suivi des relations et des modifications dans les bases de données est étendu à SSIS: il est donc possible de visualiser les dépendances d'un package vis à vis de certaines bases. Dit autrement savoir rapidement quel serait l'impact de la modification d'un champ dans votre Data Mart sur les lots d'alimentation, les autres tables...

J'attends de creuser un peu mais cela sort complètement la partie serveur de la boite noire où elle était confinée, et surtout cela permettrait d'avoir une vraie analyse d'impact au niveau de la stack BI.

En espérant vous avoir donné envie de l'installer!

A bientôt!

Des news et une CTP de SQL Server Denali (2011?) à la Keynote du PASS

2010-11-10T09:40:00.007+01:00

Hier soir a eu lieu la Keynote du PASS Summit 2010 (PASS, la Professionnal Association for SQL Server est la plus importante communauté autour de SQL, très largement soutenue par MS) retransmise en direct sur le site du PASS, comme les autres keynotes.
Cela a été l'occasion d'apprendre quelques petites choses, dont la disponibilité de la CTP 1 de SQL Server Denali. (http://www.microsoft.com/sqlserver)

Plusieurs choses intéressantes ont été abordées, d'abord la fusion du log-shipping, du clustering et du mirroring dans une "unique infrastructure" sous le nom de SQL Server AlwaysOn si j'ai bien entendu.

Mais c'est surtout côté BI que beaucoup de travail semble avoir été fait

- L'absence de maintien de Proclarity Web qui manque à beaucoup semble avoir porté ses fruits: le Project Crescent a été abordé: il s'agit d'un outil de Reporting Web très joli, dont la position par rapport à Proclarity Analytics devra être clarifiée...

- Côté SSIS on annonce une refonte totale de la partie serveur (le plus gros travail sur ce sujet depuis la sortie de 2005 dixit Amir Netz) ainsi qu'une adjontion appellée Data Quality Services dont le nom parle tout seul.

- Côté SSAS le moteur Vertipaq est intégré côté serveur dans Analysis Services, mais aussi dans SQL Server relationnel a priori.

- Enfin beaucoup de choses côté outils de développement, tous les projets BI et Database fusionnant dans un unique projet Visual Studio 2010 dont les designers sont complètement chamboulés! Par exemple une démo de démarrage d'un projet BI depuis... une feuille PowerPivot générée sous Excel, a été montrée.

Bref beaucoup de choses intéressantes en perspective.

A noter que pour un retour plus précis, Christian a fait un billet très intéressant (http://blogs.codes-sources.com/christian/archive/2010/11/09/sql-server-denali-quelles-sont-les-nouveaut-s-dans-la-ctp1.aspx)

A bientôt!

[SQL] Une Parent Child en lignes/à plat en SQL avec les CTE et l'instruction PIVOT

2010-10-09T16:45:00.001+02:00

Je vous l'avoue il y a des fois où j'aime bien céder à la tentation du nerd, celle de se lancer des petits défis inutiles de prime abord mais tellements jouissifs une fois résolus. Un de mes favoris a été la mise à plat de ParentChild, vous l'aurez deviné pour faire plaisir à SSAS.

Bref rappel: beaucoup de SI expriment les hiérarchies sur deux colonnes IdParent, IdEnfant, ce qui est convenable pour le stockage. L'ami Analysis Services éprouve quelques difficultés connues avec cette structure Parent/Enfant ou ParentChild (Basiquement une dimension construite comme cela ne générera d'aggrégats qu'au niveau du membre (All) et jamais en dessous). D'où l'envie de transformer un référentiel ParentChild en table mise à plat, i.e. où chaque niveau est représenté par une colonne.

Jetons d'abord un coup d'oeil à ma table avec la requête suivante:

select Code, CodeRattachement
from TbRef

Une Parent Child bien classique en somme...

Premier besoin: pour mettre chaque enregistrement dans la bonne colonne, nous allons avoir besoin du rang dans la hiérarchie. Ca, une CTE sait très bien le faire.

with
CteRang(Code, Rang)
as
(
select Code, 1
from TbRef
where CodeRattachement is null
union all
select TbRef.Code, CteRang.Rang+1
from TbRef
inner join CteRang
on TbRef.CodeRattachement = CteRang.Code
)
select Code, Rang
from CteRang

On a bien un calcul de rang, comme visible ici:

Bon passons aux choses sérieuses: pour avoir le bon matériel, nous allons générer toutes chaines hiérarchiques dit autrement tous les supérieurs de chaque tuple (le niveau maximal apparaitra donc non plus une seule fois mais n fois, n étant le nombre de noeuds.)

Pour cela nous allons faire une CTE non plus partant de la racine mais de la base: pour chaque noeud (membre d'ancrage avant le UNION ALL), nous allons générer tous ses supérieurs (membre récursif après le UNION ALL).

Le noeud d'ancrage a un rang issu de la CTE précédente, les noeuds récursifs le dérivent en retranchant 1 (il s'agit de supérieurs du noeud d'ancrage. De plus, on prend bien soin de garder l'ID du noeud duquel on est en train de construire la chaîne hiérarchique (Pvt dans mon cas)

with
CteRang(Code, Rang)
as
(
select Code, 1
from TbRef
where CodeRattachement is null
union all
select TbRef.Code, CteRang.Rang+1
from TbRef
inner join CteRang
on TbRef.CodeRattachement = CteRang.Code
),
CteMiseAPlat (Pvt,Rang,Code,Parent)
as
(
select ref.Code,CteRang.Rang,ref.Code,CodeRattachement
from TbRef ref
inner join CteRang
on CteRang.Code = ref.Code
union all
select cte.Pvt, cte.Rang - 1 , ref.Code,ref.CodeRattachement
from TbRef ref
inner join CteMiseAPlat cte
on cte.Parent = ref.Code
)
select *
from CteMiseAPlat
order by Pvt, Rang

Et ça marche:

Vous auriez pu vous en douter, il ne reste plus qu'à... pivoter tout cela, avec une belle instruction PIVOT. On pivote autour de mon champ Pvt, en prenant le Rang comme nom de colonne et en le remplissant avec le Code (mis dans une fonction d'agrégation bidon). On remplace donc le SELECT * précédent par:

select Pvt,[1],[2],[3],[4],[5],[6]
from
(
select Pvt, Rang, Code
from CteMiseAPlat
) as u
pivot
(
Max(Code)
FOR Rang IN ([1],[2],[3],[4],[5],[6])
) as t

Ce qui donne, tadaaaa:

Voilà! La performance n'est pas excellentissime (mieux vaudrait sûrement précalculer les rangs), mais je trouve que c'est un joli couplage de CTE et de PIVOT pour un usage très commun.

A bientôt!

[SSRS] Contrefaçon de Magic Quadrant Gartner sous Reporting Services

2010-10-06T14:37:00.009+02:00

Ce post s'inscrit dans la série "trucs faisables avec Reporting Services alors que l'on ne pensait pas". Il trainait dans mon Inbox depuis quelques temps et je me suis décidé à le publier à la suite d'une question sur un forum. Alors pour faire un MagicQuadrant Gartner, qui pour rappel ressemble à ça:

Il vous faut:

- Un Reporting Services

- Une source de données composées de coordonnées (X,Y) et de labels

- 10 min de votre temps précieux

- Un soupçon de perversité

Alors commençons. On commence par prendre un graphique de type nuage de points, sur lequel on affiche nos points en X et en Y (Valeur et Catégorie: série ne sert à rien car l'ami Gartner affiche les points de la même couleur).

Beau mais c'est pas encore ça. Comme on aura pris soin de normaliser les coordonnées (j'ai pris entre -100 et 100) on commence par formater les axes. Au menu:

- Axes scalaires, minimum -100 et maximum -100

- Intersection positionnée à 0 (Comme ça les axes se croisent en 0,0)

On affiche les labels des points, en cliquant sur "Afficher les étiquettes de données" lors d'un clic droit sur le graphique. Et dans les propriétés d'étiquettes de série on choisit notre label via une expression du style Fields!Label.Value: de mieux en mieux. On finit enfin le travail sur les axes:

- Exit les labels

- Exit les graduations primaires comme secondaires

Quelques zones de texte plus loin, et quelques encadrés mieux placés on obtient ce beau résultat, auquel il ne manque que des... flèches (mais Reporting Services ne sait pas (sic) dessiner des flèches). Amusez vous bien!

PS: On notera au passage ma partialité logicielle :)

[SSRS] Un (presque) diagramme de Gantt nativement sous Reporting Services

2010-08-25T10:53:00.009+02:00

Je ne sais pas si vous est déjà venue l'idée saugrenue de tenter de mettre le Project Plan en Reporting Services. Si d'aventure comme à moi l'idée vous était venue en 2005, vous avez sûrement utilisé la méthode mathématico-barbare énoncée par exemple ici.
A partir de 2008 nous disposons d'un composant au nom pas forcément explicite, le "Range Bar" qui peut être utilisé pour faire rapidement un diagramme de Gantt assez rapide. En effet il s'agit d'un graphique à barres où lesdites barres disposent de deux propriétés, début et fin.

Bon première étape bidouiller des données de type MS Project, j'ai fait ça sous Excel: Tâche, Rôle, Date début, Date fin.
Une fois un dataset et une data source créé sous SSRS, il ne reste plus qu'à créer une Range Bar.

Une fois le graphique créé, il faut choisir les données à représenter. Une valeur correspondra donc à une tâche, bornée par sa date de début et sa date de fin, et on affichera le nom de la tâche dans l'axe des ordonnées.

Pour représenter les rôles, pour copier Project on peut choisir de les afficher en tant que labels sur les barres. Il faut d'abord activer les labels:

Puis aller dans les propriétés de ces mêmes labels et ne pas afficher la valeur courant mais plutôt une expression, dans mon cas Fields!Role.Value.

Il ne reste plus qu'à contempler le résultat... bon pas extraordinaire mais explicite.

A noter que pour un vrai Gantt, payant cette fois ci, vous pouvez vous rendre sur le site de Dundas, qui édite là encore un très joli composant, le Dundas Gantt Chart, dont une démo est disponible ici.

En espérant que mon Gantt moche - mais gratuit - vous ait donné des idées...
A bientôt!

[SSRS] Comment utiliser des ESRI Shapefiles (shp) pour Reporting Services

2010-08-12T14:00:00.009+02:00

Un tout petit post au milieu de l'été :). Pour rappel, Reporting Services 2008 R2 supporte en natif la cartographie. Les cartes affichées peuvent être de trois types:

Cartes intégrées à la Map Gallery (basiquement les US)
Cartes issues de requêtes SQL sur des types geography
Cartes au format ESRI Shapefile (*.shp)

Ce dernier format est un standard de représentation vectoriel utilisé dans les systèmes d'informations géographiques (SIG ou GIS in english) qu'il vous faudra trouver vous mêmes pour les intégrer dans votre rapport. Chaque carte shp est composée de polygones décrits par leurs métadonnées.

Une fois votre carte choisie et ajoutée, il ne vous reste plus qu'à mapper vos données de dataset avec les identifiants des polygones du Shapefile. Typiquement une région, une ville, un pays, représenté par un identifiant ISO, son nom... Dans mon exemple mon shapefile est composé de pays décrits par leur identifiant ISO, et j'ai ajouté l'identifiant en question à ma requête pour mettre en correspondance chaque polygone avec une valeur.

Ceci fait vous pouvez à votre guise colorer la carte selon la valeur, et même mettre en place des actions (URL Action, Reporting Action) pour naviguer dans des rapports par des clics sur la carte. Chouette non?

Une dernière chose: je vous ai dit que vous deviez trouvez vous même les Shapefiles. Cela peut être laborieux, et certains shp ne passent pas dans SSRS en vous fournissant des erreurs assez gothiques. Je vous fournis donc quelques liens.

Pour des niveaux très fins et complets de tous les pays du monde, go sur Diva-GIS. Testé et approuvé.
Il est aussi possible d'en trouver sur Geofabrik.de (en allemand) mais plus simples.

Voilà, à vous de tester maintenant!

[SSRS] Chart et Lookup: ça marche aussi!

2010-07-28T12:15:00.012+02:00

Un précepte, que dis-je un commandement CharltonHestonnien est tombé avec la sortie de SSRS 2008 R2: le fait de ne pas pouvoir utiliser plusieurs DataSets dans une DataRegion. Ce précepte est tombé avec l'arrivée des fonctions Lookup, MultiLookup et LookupSet. Que font ces fonctions?

Pour le Lookup par exemple, sur le modèle de son équivalent SSIS, il prend en paramètre une clé dans le DataSet de la DataRegion (une colonne) et va chercher une valeur dans un second DataSet dont la clé correspond à la clé du premier (en gros une jointure qui ne ramènerait que le premier match). Multilookup permet de passer un ensemble de clés de la source et donc de retourner l'ensemble des premiers matchs pour chaque clé, LookupSet permet de faire un Lookup qui ne retourne plus le premier match mais tous (une jointure interne, en somme). Pour les anglicistes, Stacia Misner a fait un très bon papier ici que je ne m'amuserai pas à paraphraser.

Pour revenir au sujet en revanche personne n'a expliqué que cela pouvait être utile pour des charts. Ce qui fait que j'ai beaucoup entendu que les charts restaient mono-dataset. Eh bien non et en voilà la démonstration:

Ici j'ai fait un rapport tout bête sur AWDW2008R2: un DataSet qui renvoie la somme des InternetSales par année, un autre pour les ResellerSales. Je crée la courbe associée:

Pour rajouter mon Lookup, je vais créer une série de valeurs calculée (Expressions).

Cette expression sera donc une recherche du champ Sales du DataSet InternetSales, en jointant avec les champs CalendarYear des deux DataSets.

Et voilà le résultat!

A vous les charts avec plein de DataSets!
A bientôt!

[SSIS] "The package path references an object that cannot be found"

2010-07-25T11:01:00.014+02:00

Alors ce message d'erreur: simple warning ou erreur fatale? Et bien les deux...

Revenons un peu en arrière et expliquons le contexte: un fichier de configuration SSIS contient sous forme Clé/Valeur des valeurs à affecter au runtime à des propriétés du package. En 2005, le fait qu'une propriété n'existe pas dans le package mais soit déclarée dans le fichier de configuration générait un petit warning de rien du tout.

En 2008, un appel à DTEXEC avec en paramètre ce type de fichier de configuration générait une erreur de chargement fatale. Résolu en SP1 CU4, ce bug - il faut donc l'appeller ainsi - a été longtemps considéré par tout le monde, moi y compris, comme une nouvelle feature, obligeant à arrêter de partager les fichiers de configuration entre les différents packages utilisant chacun des parties dédiées du fichier. Bien entendu ce "nouveau / ancien" mode de fonctionnement perdure sur R2:

Soit un nouveau Package comprenant la variable A. Cette variable comprend "A's Default Value". Le package comprend un unique composant script qui affiche cette valeur dans une MessageBox.

Soit son fichier de configuration, qui en plus de donner à A la valeur "A's Configured Value" cherche aussi à configurer B qui n'existe pas.

A l'exécution on a bien un warning, mais la valeur de A a bien été affectée, comme en témoigne la MessageBox.

Le lien sur Connect: http://support.microsoft.com/kb/974076

Nouveau MVP!

2010-07-02T09:42:00.004+02:00

J'ai appris hier que j'avais été nommé MVP SQL Server. Merci à Microsoft pour cette jolie preuve de considération!
Et bien sûr un grand merci à Thomas et plus globalement à toute la fantastique équipe avec qui je bosse depuis bientôt un an.

[PowerPivot] "Could not load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’" Suite et fin!

2010-06-18T14:44:00.004+02:00

Ca y est, je vous en avais parlé mais le problème est enfin trouvé: cette erreur d'installation est due à l'installation de PowerPivot et d'un SSAS natif sur le même serveur en commençant par SSAS.

Msmdsrv (le processus SSAS natif) inclut une référence vers Microsoft.AnalysisServices.SharePoint.Integration, qui n'est pas installée en mode natif. Cette erreur de chargement de référence empêche le chargement ultérieur de la dll lors de l'installation de l'instance Vertipaq.

La solution: installer PowerPivot AVANT une instance SSAS. Ou alors paramétrer le setup de PowerPivot pour que la dll soit chargée malgré cet échec de référence. J'ai testé cette dernière option qui marche bien. Vous trouverez tous les détails sur l'article de Dave Wickert (de Microsoft) : http://powerpivotgeek.com/2010/06/14/installation-failure-could-not-load-file-or-assembly-microsoft-analysisservices-sharepoint-integration-dll/.

A bientôt!

[SSAS] Automatiser l'import export de traductions (Translations)

2010-06-08T10:21:00.007+02:00

Il vous est déjà sûrement arrivé d'avoir à localiser un cube, c'est à dire de traduire sa structure de manière à ce qu'une dimension Produit s'appelle "Produkt" lorsque le cube est consulté depuis un poste germanique, "Product" depuis un anglophone... etc...

Le mécanisme utilisé est celui des traductions (ou Translations en VO) affectable depuis BIDS dans l'onglet du même nom pour les cubes et les dimensions.

Le fait que l'on doive l'éditer dans BIDS est intéressant mais problématique lorsque l'on utilise un prestataire pour traduire le cube: il faut en effet soit fournir un accès en remote au cube, soit forger soit même un fichier Excel par une multitude de copier/coller.
Etant assez peu fan de ce genre de procédé je me suis mis en tête de voir comment ce genre d'application pouvait s'automatiser.

Tout d'abord BIDS communique avec SSAS par AMO, il est donc intéressant de se plonger dans technet pour voir comment ces Translations sont représentées dans le modèle. Dans ce très petit paragraphe de l'article "Programming AMO Advanced Objects" on remarque que Translations est une collection qui est attachée à tout objet pouvant être traduit, et qui comprend des objets Translation.
Cet objet comprend - toujours en se référant à la doc - trois propriétés texte, Caption Description et DisplayFolder ainsi qu'un identifiant de langue entier (qu'on imagine être 1033 pour l'anglais, 1036 pour le français...).
1ère constatation: il n'y a pas de repository central des translations, il faudra parcourir l'arbre des composants d'une base pour extraire ces objets.

Ensuite qu'est ce qui peut être traduit sur un composant et comment est-ce associé à une propriété d'un objet Translation? Là pour le coup rien n'est défini dans le modèle, c'est là encore du côté de la doc et de cet article que la réponse est venue.
2ème constation: ce qui peut être traduit n'est pas déductible du modèle, il va falloir le modéliser

Enfin en creusant un peu j'ai remarqué quelques incongruités dans le modèle: les Attributes utilisent une spécialisation de Translation (AttributeTranslation), de même les collections de traductions ne s'appellent pas toujours Translations.
3ème constatation: le modèle n'est pas très facile et cohérent, ce qui rend difficile son traitement automatisé.

La modélisation

La première étape pour se débarasser de ces problèmes est donc arriver à modéliser l'arbre des propriétés traductibles des objets de la base, ce en gérant les potentielles incongruités. Un arbre étant assez facile à modéliser en xml, je m'y suis attelé, ce qui donne le fichier XML suivant:

<?xml version="1.0" encoding="utf-8" ?>
<Object name="Databases">
 <Object name="Cubes">
  <Object name="MeasureGroups">
   <Object name="Measures">
    <Property name="Name" translation="Caption"/>
    <Property name="DisplayFolder" translation="DisplayFolder"/>
   </Object>
   <Property name="Name" translation="Caption"/>
  </Object>
  <Object name="Dimensions">
   <Property name="Name" translation="Caption"/>
  </Object>
  <Object name="Perspectives">
   <Property name="Name" translation="Caption"/>
  </Object>
  <Object name="Kpis">
   <Property name="Name" translation="Caption"/>
   <Property name="Description" translation="Description"/>
   <Property name="DisplayFolder" translation="DisplayFolder"/>
  </Object>
  <Object name="Actions">
   <Property name="Name" translation="Caption"/>
   <Property name="Name" translation="Description"/>
  </Object>
  <Property name="Name" translation="Caption"/>
  <Property name="Description" translation="Description"/>
 </Object>
 <Object name="Dimensions">
  <Object name="Attributes">
   <Object name="AttributeRelationships" id="AttributeID">
    <Property name="Name" translation="Caption"/>
   </Object>
   <Property name="Name" translation="Caption"/>
   <Property name="AttributeHierarchyDisplayFolder" translation="DisplayFolder"/>
   <Property name="MembersWithDataCaption" translation="MembersWithDataCaption"/>
  </Object>
  <Object name="Hierarchies">
   <Object name="Levels">
    <Property name="Name" translation="Caption"/>
   </Object>
   <Property name="Name" translation="Caption"/>
   <Property name="AllMemberName" translation="Caption" collection="AllMemberTranslations"/>
  </Object>
  <Property name="Name" translation="Caption"/>
  <Property name="AttributeAllMemberName" translation="Caption" collection="AttributeAllMemberTranslations"/>
  <Property name="UnknownMemberName" translation="Caption" collection="UnknownMemberTranslations"/>
 </Object>
 <Property name="Name" translation="Caption"/>
 <Property name="Description" translation="Description"/>
</Object>

Bon, maintenant il est possible de parcourir cet arbre et d'extraire les champs du cube devant être traduits en les documentant suffisamment pour faciliter leur import ultérieur.

Le code d'export

Bon ça n'est pas le code le plus propre de l'année - il s'agit d'un prototype :) - mais bon ça marche. L'idée est simplement de partir du noeud Database (le paramètre ModelComponent) et de son élément XML associé pour faire un parcours en profondeur et retrouver par réflexion les collections d'enfants et leurs membres.

    public IEnumerable ExtractTranslations(ModelComponent c, XElement e)
    {
        Type t = c.GetType();
        string ID = (e.Attribute("id") != null)
           ? e.Attribute("id").Value
           : "ID";
        string path = e.Attribute("name").Value + "[" +
            t.GetProperty(ID).GetValue(c, null).ToString() + "]";

        //Pour chaque propriété du noeud courant on retourne une ligne
        foreach (XElement p in e.Elements("Property"))
        {
            object propertyValue = t.GetProperty(p.Attribute("name").Value)
               .GetValue(c, null);
            if (propertyValue != null)
            {
                yield return new OutputLine()
                {
                    Path = path,
                    Value = t.GetProperty(p.Attribute("name").Value)
                       .GetValue(c, null).ToString(),
                    Property = p.Attribute("translation").Value,
                    CustomCollection = (p.Attribute("collection") != null)
                       ? p.Attribute("collection").Value
                       : "Translations"
                };
            }
        }
        //Puis on fait un appel récursif par enfant de chaque collection d'enfants
        foreach (XElement o in e.Elements("Object"))
        {
            var childrenCollection = t.GetProperty(o.Attribute("name").Value)
                .GetValue(c, null) as IEnumerable;
            foreach (ModelComponent child in childrenCollection)
            {
                foreach (OutputLine ol in ExtractTranslations(child, o))
                {
                    ol.Path = path + "." + ol.Path;
                    yield return ol;
                }
            }
        }
    }

Quel est le résultat en sortie? Un fichier fait un peu comme cela (j'ai laissé une seule ligne en exemple)ça:

Databases[Adventure Works DW 2008]
.Cubes[Adventure Works]
.MeasureGroups[Fact Internet Sales 1]
;Translations
;1033
;Caption
;Internet Sales

Et cette chose là peut s'importer facilement: si si la preuve!

Code d'import

La on va descendre par réflexion le path , pour affecter à la collection de traductions (au nom fourni en paramètre) un membre qui comprendra pour le champ fourni la valeur récupérée.


public void PutTranslation(Server root, InputLine l)
{
    //D'abord on descend le path jusqu'a l'objet a traduire
    MajorObject closerMajorObject = root;
    ModelComponent current = root;
    while (l.Path.Length > 0)
    {
        string currentPath = l.Path.Split('.')[0];
        object childrenCollection = current.GetType()
            .GetProperty(currentPath.Split('[')[0])
            .GetValue(current, null);
        //On positionne le courant sur la collection fille, a l'index specifie
        PropertyInfo accessor = childrenCollection.GetType()
            .GetProperty("Item", new Type[] { typeof(string) });
        current = accessor
            .GetValue(childrenCollection
            , new object[] { currentPath.Split('[')[1].TrimEnd(']') }) as ModelComponent;
        //Si on est sur le dernier noeud on vide la chaine pour sortir
        if (l.Path.Contains('.'))
            l.Path = l.Path.Substring(currentPath.Length + 1);
        else
            l.Path = string.Empty;
    }
    //On recupere la collection a modifier
    object translationCollection = current.GetType()
        .GetProperty(l.CustomCollection)
        .GetValue(current, null);

    //On recherche le type de traduction attendue
    //(les DimensionAttribute utilisent un type particulier)
    string type = (current.GetType().Name == "DimensionAttribute")
        ? "AttributeTranslation"
        : "Translation";
    //On recherche la methode add sur la collection
    MethodInfo translationAdder = translationCollection.GetType()
        .GetMethods()
        .Where(m => m.Name == "Add" && m.GetParameters()[0].ParameterType.Name == type)
        .First();
    //On recherche un objet de traduction pour ce langage si il existe
    object translation = translationCollection.GetType()
        .GetMethod("FindByLanguage")
        .Invoke(translationCollection, new object[] { l.Language });
    //Sinon on cree un nouvel objet en invoquant le constructeur (int32)
    if (translation == null)
    {
        translation = translationAdder.GetParameters()[0].ParameterType
            .GetConstructor(new Type[] { typeof(Int32) })
            .Invoke(new object[] { l.Language });
        translationAdder.Invoke(translationCollection, new object[] { translation });
    }
    //On affecte la valeur
    translation.GetType()
        .GetProperty(l.Property)
        .SetValue(translation, l.Value, null);
}

Voilà avec ça ça suffit pour tout importer, et vous pouvez même le tester: j'ai intégré cela dans un petit lot SSIS sur mon SkyDrive. N'hésitez pas si vous avez des questions!

A bientôt!

[SSRS] Afficher une image alternative si l'image n'existe pas

2010-05-27T14:34:00.011+02:00

Bon question assez demandée, et pourtant assez peu référencée donc je donne ma solution - qui est, n'en doutez pas, perfectible.

Bon d'abord on va dans les propriétés du rapport ajouter du beau - sic - VB bien crade. Cette fonction homemade va simplement faire une webrequest sur l'URL de l'image. Si celle ci se déroule bien on renvoie true, false dans tous les autres cas.

Public Function ImageExists(ByVal imgPath As String) As Boolean
  Dim netCall As System.Net.HttpWebRequest = _
  System.Net.HttpWebRequest.Create(imgPath)
  netCall.Credentials =  System.Net.CredentialCache.DefaultCredentials
  netCall.Timeout = 1000
  Dim resp As System.Net.HttpWebResponse = Nothing
  Try
     resp = netCall.GetResponse()
     If resp.StatusCode = System.Net.HttpStatusCode.OK
        Then Return true
  Catch ex As System.Exception
     Return false
  End Try
End Function

Il ne reste plus qu'à utiliser ce "magnifique" code dans l'expression Source, comme ici:

=IIf(Code.ImageExists(Fields!ImageURI.Value.ToString())
,Fields!ImageURI.Value
,http://www./.../noImageAvailable.jpg)

Voilà le résultat, à bientôt!

PS: bon ici c'est pour un accès par URL, pour un accès en local un File.Exists aurait suffi. Dans le cas d'une image BLOB ça se traite en SQL!

[SSIS] Modifier l'apparence des packages via l'API [FAIL]

2010-05-23T13:05:00.011+02:00

Ahhhh celle là on me l'a posée très récemment, au travers de la question "Comment ajouter programmatiquement des commentaires à un package" et je dois avouer que je me suis cassé la tête des nuits dessus il y a quelques mois, à la suite de la lecture d'un thread très proche entre Jamie Thomson et l'équipe de dev.

SSIS vous le savez fournit une très belle API de design de packages, qui permet de modifier tout un tas de composants appartenant à ce beau schéma que j'affectionne:

Mais dans ce schéma et dans la doc de l'API, aucune note sur l'apparence... Comment réorganiser les tâches, ajouter un commentaire?

Ouvrons donc un package SSIS en mode code pour voir comment cela est stocké. Un package est bien un fichier XML stockant toutes ses propriétés et composants sous format XML, comme on peut le voir ci-dessous:

En revanche dans ce code XML bien propre aucune mention de l'apparence, du positionnement des éléments, des commentaires. Par contre en scrollant un peu vers le bas, on trouve une propriété de package au nom étrange:

<DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">

Hmm... et cette propriété contient du code XML dont les balises > et < ont été échappées avec leur code (gt, lt). Ce code XML est très long et abscons et décrit des positions d'objets. Un commentaire, objet qui n'apparait pas dans l'api, apparaît ici sous cette forme (j'ai "deséchappé" les caractères pour que ça ressemble à du XML (le commentaire est encodé en binaire dans la propriété binary) :

La première chose que l'on remarque ici c'est le nom du contrôle de commentaire, MSDDS.Text. Qu'est ce que c'est que ça?

Quelques recherches plus tard vous auriez appris que DaVinci Design Surface est une très vieille API native de design de diagrammes (tiens donc...) utilisée dans Visual Studio 5.0 et 6.0 mais ayant vocation à disparaître. La team SSIS l'aurait utilisé en 2005 faute de mieux. Il y a donc une grosse différence entre MSDDS - le code XML échappé bourré au milieu des éléments - et la structure des packages.

Un commentaire ne fait pas partie des objets de package... Too bad.

Je vais vous épargner mes digressions au Reflector et mes réouvertures de packages en interop MSDDS pour modifier les commentaires, tentatives ayant toutes abouti à créer des monstres imparsables (jouer au Dr Frankenstein n'est décidément pas rentable).

Moralité: pour le moment, pour de la documentation automatique, utilisez les champs Description des objets, c'est bien plus facile, accessible via l'API, et en plus ça tiendra probablement la migration en SSIS Denali, ce qui ne serait probablement pas le cas de mes cochoncetés en natif.

A bientôt!

[SSIS] Utiliser PowerShell pour remplacer DtUtil et le manifeste

2010-05-17T14:50:00.010+02:00

Ceux qui lisent ce blog - merci à eux! - le savent, je ne supporte pas le manifeste de déploiement et utilise plutôt l'utilitaire DtUtil pour déployer mes packages.
Il arrive malheureusement que l'on me rétorque que le manifeste de déploiement dispose de nombreux avantages par rapport à mon utilitaire préféré.
...pour être honnête en réalité il n'en a que deux.

1) Il est plutôt joli
2) Il permet de modifier l'emplacement de la configuration dans le package

Si l'on omet le point numéro 1 (la cosmétique étant par nature subjective) on se dit que la seule chose sympatique que permet ce manifeste c'est d'éditer le lot pour changer sa node "Configuration".

Quels seraient donc les specs de l'utilitaire "idéal"?

Il serait automatisable - ce qu'est DtUtil mais pas le manifeste - et pourrait déployer des configurations tout en modifiant leur chemin - ce que ne sait pas faire DtUtil. Il s'agirait donc d'un DtUtil capable de modifier un dtsx.

DtUtil et le manifeste supplantés par PowerShell!

L'API .NET SSIS et son objet Application permettent d'ouvrir un lot, de le modifier et de le déployer ou que ce soit (DTS, SQL, File), ils constituent donc une solution stable (pas d'édition de XML très sale) et... scriptable donc automatisable, grâce à PowerShell!

Un petit exemple

En trois temps:

1) On modifie le package

$app = new-object Microsoft.SqlServer.Dts.Runtime.Application
$pkg = $app.LoadPackage($oldPackagePath,$null)
$oldConfigPath = $pkg.Configurations[0].ConfigurationString
$pkg.Configurations[0].ConfigurationString = $newConfigPath

2)On déploie la configuration à sa destination

Copy-Item $oldConfigPath $newConfigPath

3) On déploie le package à sa destination (en fichier ici mais cela aurait pu être SQL)

$app.SaveToXml($newPackagePath,$pkg,$null)

Et voilà!

[SSIS 2008] Quoi?? "The package path references an object that cannot be found"

2010-05-17T12:03:00.004+02:00

Mais que se passe-t-il? Ce fichier de configuration, que vous utilisiez en 2005 passait très bien en DtExex /ConfigFile - certes avec quelques warning - mais le voilà qui vous lève l'erreur suivante:

The package path references an object that cannot be found

Ce problème s'ajoute à la série des modifications de comportement de DtExec (voir aussi le fait déja abordé que les configurations définies dans le package prennent le pas sur celles de /ConfigFile)
mais il ne s'agit pas ici une feature mais bien un bug corrigé dans le CU4 du SP1 de SQL Server 2008. Si vous ne pouvez pas l'installer sachez que configurer le fichier en tant que configuration dans BIDS permet d'exécuter le package comme avant.

Source: Lien sur le support .

[SSIS] Le Data Flow et les Expressions ("Ca marchait bien dans DTS!")

2010-05-13T16:22:00.000+02:00

L'autre jour me vient l'idée de modifier dynamiquement la propriété MinSimilarity d'un Fuzzy Lookup depuis un fichier de configuration... Et oh stupeur c'est impossible.

Parce qu'on ne peut pas tout modifier dans SSIS, ça n'est pas DTS...

Nous reparle pas de DTS!

Ah DTS... les scripts ActiveX qui permettent de modifier n'importe quelle propriété du package quel "bonheur". En effet le modèle objet d'un lot DTS 2000 était directement accessible en écriture depuis le lot lui même. Pratique mais un tel nid à bug qu'un tout autre paradigme a été employé pour SSIS. Les assignations dynamiques de propriétés passent par des Expressions strictement encadrées, mais qui n'enlèvent pas (ou peu) de fonctionnalités de paramétrage dynamique. (Voir ce très vieux post de Jamie Thomson)

Et le Data Flow?

En ce qui concerne le Data Flow, seul le conteneur est de type "exécutable" et possède donc des expressions. Ceci dit certains attributs de ses enfants peuvent être utilisés comme expression: pour cela ils sont dupliqués en tant qu'attributs du composant Data Flow à la création des composants enfants. Par exemple un DataFlow contenant un Lookup possèdera deux propriétés supplémentaires, [NomDuLookup].[SQLCommand] et [NomDuLookup].[SQLCommandParam] qui correspondent aux propriétés avancées du Lookup. Celles ci sont donc assignables par expression..

Mais on constatera rapidement que tout n'est pas exposé... et que certaines propriétés de composants de Data Flow seraient bien pratiques en tant qu'expressions. La liste de ces propriétés settables par expression est trouvable ici sur MSDN.

Pourquoi elles ne sont pas toutes exposées...

Lorsque l'on développe une tâche de Data Flow avec le framework, pour chaque attribut, on peut choisir sa valeur d'ExpressionType. C'est elle qui définit si cette propriété sera exposée ou pas (par duplication dans le composant Data Flow parent) à l'éditeur d'expression, et qui permetrait

- de modifier une propriété par expression

- de l'adresser via une configuration

Cette propriété ExpressionType est un index de l'énumération DTSCustomPropertyExpressionType, positionné par défaut à CPET_NONE (pas settable par expression). Or pour qu'un attribut soit adressable par expression, il faut explicitement que l'ExpressionType vale CPET_NOTIFY.

Les développeurs de SSIS ont globalement setté cette propriété a plein d'endroits utiles... mais ils ont parfois oublié de l'activer.

Et pour les autres propriétés? Dans le cas du MinSimilarity du Fuzzy Lookup?

Il faut donc ruser pour contourner et ça ne sera de toute manière pas très propre. La solution, puisqu'on ne peut pas modifier le package en cours va être de... le modifier depuis un package "lanceur".

//On instancie une application pour éditer le lot

Microsoft.SqlServer.Dts.Runtime.Application application

= new Microsoft.SqlServer.Dts.Runtime.Application();

//On récupère l'instance du FuzzyLookup via le connection Manager

string packageName

= (string)Dts.Connections["PackageTest.dtsx"].AcquireConnection(null);

Package package = application.LoadPackage(packageName.ToString(), null);

CManagedComponentWrapper fuzzyLookup

= ((MainPipe)((TaskHost)package.Executables["DFT"]).InnerObject)

.ComponentMetaDataCollection["FL"].Instantiate();

//On lui affecte la valeur d'une variable en readonly

int MinSimilarity = (int)Dts.Variables["MinSimilarity"].Value;

fuzzyLookup.SetComponentProperty("MinSimilarity", MinSimilarity);

//On sauvegarde (cela aurait pu être en SQL...)

application.SaveToXml(packageName, package, null);

Dts.TaskResult = (int)ScriptResults.Success;

Voilà.

[PowerPivot] Could not load file or assembly "Microsoft.AnalysisServices.Integration.dll"

2010-05-13T16:16:00.005+02:00

Ce problème est signalé sur Connect, en RTM et marqué comme non résolu. Je viens de le rencontrer pour la seconde fois dans la même situation: à la fin de l'installation de PowerPivot en mode ExistingFarm sur une instance SQL existante. Dans les deux cas une réparation (Repair) de l'installation, qui réinstalle les Client Tools semble remettre le système dans un état fonctionnel. J'espère pouvoir trouver plus d'informations prochainement...

[SSAS] AMO et Powershell

2010-04-30T18:49:00.010+02:00

Pour répondre à la question suivante "Comment scripter ce traitement qui se fait très bien sur ma base AS avec Management Studio?" vous pensez immédiatement que la réponse sera de se coltiner du XMLA exécuté avec ASCmd. Mais il y a une alternative plus sympa: pourquoi ne pas utiliser AMO avec.... PowerShell! Pour, par exemple, copier-coller une base AS - question à laquelle j'ai répondu il y a quelques minutes, j'ai mis au moins 2 minutes à écrire ce script mo-nu-men-tal:

$srv=New-Object Microsoft.AnalysisServices.Server
$srv.connect(".\SQL2008R2")
$olddb=$srv.Databases.FindByName("Adventure Works DW 2008")
$newdb=$olddb.Clone()
$newdb.Name="Copie AW"
$newdb.ID="Copie AW"
$srv.Databases.Add($newdb)
$newdb.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)

Globalement n'importe quel code AMO en C# ou VB peut être traduit en PowerShell, et s'y j'y arrive vous pouvez le faire :). On peut y penser pour des procédures de déploiement par exemple, mais vous pouvez imaginer tout un tas de scenarii d'usage pour remplacer du XMLA amoureusement tapé à la main. Powershell et AMO c'est bon,mangez en.

[MDS] Master Data Services, pourquoi et comment?

2010-04-29T13:45:00.010+02:00

Cela fait quelques mois que l'on parle de l'ajout de Master Data Management dans SQL Server, au travers de Master Data Services (issu du rachat de Stratature). Cependant il semble que le principe et l'utilité du MDM dans une entreprise ne soit pas encore une notion bien appréhendée, d'où l'utilité de ce petit article.

Pourquoi du Master Data Management?

Dans une entreprise, les applications horizontales (Comptabilité, GRH, Ventes, Finance...) disposent souvent de bases de données séparées. Si beaucoup de ces données sont spécifiques, un certain nombre d'entre elles sont communes à l'ensemble de l'organisme et donc dupliquées dans les différents systèmes: ce sont les Master Data. (Les données de ce type les plus connues sont les Clients, les Produits vendus par l'entreprise...)
Comme ces données sont dupliquées et stockées dans des systèmes hétérogènes, il y a un fort risque d'incohérences, et il est difficile d'extraire rapidement une vision unifiée de ce type de données.

Cas extrême: un client d'une entreprise la contacte car il ne reçoit plus ses factures. Le service client vérifie qu'il possède bien la bonne adresse pour ce client dans sa base CRM. Cependant le service de facturation ne possède pas la même vision de cet attribut du client dans l'application de facturation: il y a incohérence difficile à tracer et à résoudre. Sans compter l'image désastreuse si ce cas est généralisé.

Etant très souvent amené à concevoir et alimenter des Data Marts relationnels, la race des consultants BI - dont je fais partie - a souvent maille à partir avec ce genre de problèmes, dont l'entreprise n'était généralement pas consciente avant la centralisation entreprise par les différents Data Marts.

C'est donc du Data Warehousing?

Non mais le processus est très lié. Sans MDM, l'alimentation de chaque Data Mart doit se faire en prévoyant l'application de règles métier strictes en cas de problèmes de cohérence dans les Master Data. C'est un processus lourd qu'il serait souhaitable de ne pas recommencer à chaque Data Mart... L'idée est donc que le MDM devienne la source de Master Data pour toute application désirant connaître la version corporate de la réalité pour les données importantes.

Pour insister sur la parenté avec les entrepôts de données les Master Data sont historisées et représentées conceptuellement sous forme d'entités, disposant d'attributs et de hiérarchies. Ces concepts sont effectivement très proches du Data Warehousing classique, à ceci près que MDS gère aussi la définition de règles de validation dans le modèle, ce qui n'est pas typiquement le cas d'un DW.

Et techniquement?

Master Data Services est avant tout basé sur une base de données SQL Server. Les Master Data sont stockées dans cette base dédiée, dont les tables sont générées automatiquement lors de l'import de données.
Tout le cycle de vie des données (import, versionning, validation, notification, publication) est géré en SQL(le stockage comme le code) bien que les tables au nom doux comme [tbl_3_12_MS] ne doivent pas être utilisées directement: une API est disponible via WCF, API qu'utilise le frontal web de définition d'entités installé avec MDS.

La donnéee stockée peut aussi être récupérée via des vues, qui permettront entre autres d'alimenter des Data Marts.

Conclusion

Master Data Services est le quatrième "Services" (Après Integration, Analysis et Reporting, non Notification ne compte pas!) et de mon point de vue la quatrième "brique BI" de SQL Server. Il va permettre de décorréler la gestion des données sensibles de l'alimentation de Data Marts, tant cette gestion revêt une importance bien plus grande au sein des entreprises.

Intercalé entre les sources et les bases d'analyse, il va permettre aux projets BI de se concentrer sur leur but: fournir des outils d'analyse appropriées sans perdre du temps sur la redéfinition de règles d'alimentation en systèmes hétérogènes.

Reste maintenant à manoeuvrer les leviers nécessaires pour vaincre les réticences politiques qui ne manqueront pas de se faire jour, mais prenons le pari qu'avec une architecture ouverte, reconnue (Stratature), intégrée et une préoccupation du MDM de plus en plus prégnante dans les entreprises, cette nouvelle brique de SQL Server ne tardera pas à s'imposer...

[PowerPivot] Clarifications techniques

2010-04-27T16:01:00.003+02:00

Cela fait quelques fois que l'on me pose des questions sur PowerPivot qui me semblent sensées, bien que très éloignées de la réalité du produit. Une de celles que j'aime beaucoup tient à la différence entre un cube Gemini et un .cub généré par AS, qui implique des questions telles que:
- "Puis-je me connecter en ADOMD.NET sur un cube Gemini en spécifiant "Data Source=fichier.xslx"?
- ou encore "Je peux brancher Procla sur un Excel?"

La réponse est non pour une raison simple: ce n'est pas le même format. Circulez. Rien à voir. Si vous avez lu ce vieux post de Mosha, revenu d'outre-Bing, vous le sauriez.

En gros, PowerPivot c'est quoi:
- une nouvelle version d'Analysis Services dont il existe deux versions: une standalone, mono-utilisateur (celle de l'addin Excel) et une serveur, multi-utilisateurs, que l'on peut intégrer dans Sharepoint.
- et donc un format de stockage des cubes différents, pour s'adapter au nouveau mode de fonctionnement.

Donc si on se résume on a bien deux grands types de moteurs et de stockage, une partie serveur et une partie client standalone.

SSAS "classique"
- Serveur: SSAS Engine classique (msmdsrv)
- Client standalone: cube engine de OLE DB for OLAP (msmdlocal) qui lit des .cub

SSAS Vertipaq
- Serveur: SSAS en intégré Sharepoint
- Client standalone: PowerPivot engine pour Excel qui lit les CustomData/item1.data des fichiers Excel

La conséquence c'est l'incompatibilité entre les différents modes de stockage (je préfère parler de représentation) et les serveurs. Pas la peine d'essayer de monter du Vertipaq dans votre vieux Local Cube Engine...
A terme il serait évidemment souhaitable que ce fonctionnement soit transparent et que l'on puisse indistinctement extraire des cubes Vertipaq en .cub puis les remettre dans un serveur AS classique...

En gros ce que préconise Chris Webb dans son billet: http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!6404.entry?sa=705008310

[Releases] SQL Server 2008 R2 en RTM!

2010-04-22T11:00:00.014+02:00

Annoncé aujourd'hui sur le site produit US SQL Server 2008 est passé en RTM.

Vous pouvez donc dès à présent l'essayer sur Technet et MSDN.

Ensuite les dates de sortie sont figées: le 3 mai pour les abonnés MSDN et Technet, le 13 pour le reste.Ensuite des précisions sur les éditions sorties: d'abord les deux éditions Core restent:

Standard
Enterprise

ainsi, bien sûr, que la Web, Workgroup, Developer, Express et Compact. Deux éditions spécialisées (appliances) sont ajoutées:

Data Center
Parallel Data Warehouse

Enfin les nouvelles features de R2 sont disponibles selon les éditions:

Standard intègre la compression des backups , les nouveautés de Reporting Services (Maps...), StreamInsight Standard et peut s'intégrer dans les outils de gestion multi-instance d'une édition supérieure
Enterprise ajoute l'intégration PowerPivot dans MOSS, Master Data Services, la gestion multi-instances (moins de 25) ainsi que des nouveaux outils de développement et déploiement T-SQL.
Data Center permet de gérer un nombre illimité de processeurs et de mémoire (régression par rapport à 2008, Enterprise semble maintenant limité à 8 sockets et 2 To de RAM), de manager plus de 25 instances, et ajoute StreamInsight Premium (sans limite de nombre d'events)
Parallel Data Warehouse est une Data Center utilisant les composants de processing massivement parallèles du projet Madison (issu du rachat de DatAllegro) dont le but avoué est de permettre d'approcher des tailles de DW de l'ordre du PetaOctet... ça laisse rêveur.

Plus de précisions à suivre bientôt, pour plus d'infos n'hésitez pas à vous rendre sur http://www.sqlserverlaunch.com/ (en anglais).

Source principale: http://blogs.technet.com/microsoft_blog/archive/2010/04/21/SQL-Server-2008-R2-Helping-Customers-Get-More-Value-Out-of-Their-Data.aspx

[Autres] Predixion Software?

2010-04-21T10:41:00.008+02:00

J'en ai déjà parlé mais Jamie McLennan, le principal responsable du développement du Data Mining dans SQL Server 2000 à 2008 a quitté Microsoft fin d'année dernière pour co-fonder Predixion Software, une société au nom évocateur dont l'équipe de dev se situe à... Redmond et compte parmi ses membres Bogdan Crivat, autre ancien de l'équipe DM (connu principalement pour avoir développé l'addin Cloud pour Excel).

Si on rajoute que le comité de direction compte Stuart Frost (ancien de DatAllegro, actuellement chez Microsoft) il n'est nul besoin de préciser que Predixion semble se développer avec a minima la bénediction d'un petit éditeur de logiciel local basé à Redmond :)

En regardant un peu sur leur proto site web, on peut noter que la plupart des pistes de développement tournent autour

- du Cloud et du Data Mining

- de l'ajout de nouvelles visualisations (SL...)

- de l'intégration de modèles de mining dans Sharepoint

soit des features d'intégration de modèles plus que de développement de nouveaux algorithmes, chose que j'avais imaginé en premier lieu au vu du nom de la société

Tout ça pour dire que ce que va produire Predixion est à mon sens très intéressant à suivre, tout comme la ligne directrice du Data Mining en interne chez MS pour SQL Server Denali.

[T-SQL] Déconnecter tous les utilisateurs

2010-04-21T09:58:00.003+02:00

Là encore rien de neuf mais cela fait deux fois qu'on me le demande en deux jours donc j'imagine que ce n'est pas évident...

La manière la plus simple de déconnecter tous les utilisateurs d'une base SQL Server c'est de passer la base en mode SingleUser.

Ceci dit SQL n'est pas chien et lorsque l'on lui demande de procéder à ce genre d'éjection sauvage, il se comporte par défaut en gentleman et attend que toutes les transactions actives soient commitées.

Il faut donc le forcer à être un petit peu moins poli et ordonner le rollback de toutes les transactions actives.

ALTER DATABASE [Base] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Ici un traitement nécessitant d'être seul sur la base

--...

--Puis retour à la normale

ALTER DATABASE [Base] SET MULTI_USER

Voilà rien de révolutionnaire mais si ça peut aider...

[SSAS] Union DMX (suite)

2010-04-20T23:41:00.002+02:00

Juste un peu d'autocongratulation mal placée pour dire que l'on m'a signalé que mon "magnifique" pseudo opérateur Union en DMX était référencé sur ASSP dans les Additional Resources. http://asstoredprocedures.codeplex.com/wikipage?title=Additional%20Resources

Pour plus de ressources ou discuter sur le sujet n'hésitez pas à me contacter avec le formulaire ad-hoc!

A bientôt!

[SSAS] Data Mining avec PowerPivot

2010-04-20T18:20:00.001+02:00

Cela fait quelques temps que je voulais parler de l'intégration de deux features de SSAS liées à Excel: le Data Mining et PowerPivot, ou comment utiliser des données issues de PowerPivot avec les addins Excel de Data Mining (Table Tools et Data Mining Addin, sortis en 2007).

L'excellent Kasper de Jonge a sorti un super webcast résumant bien la technique. Le doublonnage d'informations n'étant pas mon dada je vais juste me contenter de vous donner la vidéo et de résumer le contenu pour les non anglophones.

Ceci dit - certes je suis un peu overbooké mais j'ai le temps de pondre un peu de réflexion - j'ai eu l'idée une petite feature qu'il a sûrement oublié d'aborder en fin d'article: combiner les expressions PowerPivot avec du DMX grâce aux fonctions Excel type DMPREDICT.

Alors voici l'objet:

Les addins Data Mining ne fonctionnent pas - encore? - sur des PivotTables (si vous avez fait du VSTO vous savez sûrement que la PivotTable est un objet très "à part" dans le modèle d'Excel), donc le but de l'affaire est de faire un Convert to formula d'une PivotTable d'analyse (qui va la convertir en sheet "Classique"), les expressions venant du cube étant alors spécifiées en tant que fonction CUBEMEMBER/CUBEVALUE qui prennent en paramètre des expressions "MDX" (notez les guillemets).

Dès lors utiliser les addins ne posent plus aucun problème, puisque vous êtes confrontés à des données "brutes", dont l'originie PowerPivotesque ne gène nullement l'analyse. A vous les "Analyze Key Influencers" et autres joyeusetés. Nota bene bene bene: les addins DM ne sont dispos qu'en... 32 bits donc oubliez le PowerPivot 64bits sur Excel 64bits. Espérons que cela changera...

Le tout petit rajout...

Bon en gros vous savez sûrement qu'il est possible d'appeller du DMX indirectement depuis une sheet Excel, basiquement cela a déjà été décrit par Bogdan Crivat (dont le départ chez Predixion fera aussi l'objet d'un post lorsque-j'aurai-plus-de-temps) dans ce post et qui peut être résumé ainsi:

La fonction Excel DMPREDICT permet de faire une requête de prédiction de type "simple" sur un modèle de mining dans Excel, elle a ce pattern:

DMPREDICT
(
"NomDeMaConnexionSSAS",
"NomDeMonModele",
"NomDeMaSortieDeModele",
"NomParametre1", "ValeurParametre1",
...,
"NomParametreN","ValeurParametreN"
)

Il est donc parfaitement possible d'effectuer une requête DMX sur des données issues de PowerPivot et mises à plat en mode convert to formulas... et donc de marier DAX et DMX...

[T-SQL] ROW_NUMBER() OVER sans ORDER BY

2010-04-20T16:43:00.003+02:00

Question bizarre et pourtant assez connue: comment numéroter avec ROW_NUMBER() sans effectuer de tri sur le résultat, ce qui peut être utile/souhaitable dans tout un tas de contextes.
MSDN vous dit bien que ROW_NUMBER() OVER attend un ORDER BY...

Bien sûr mais si vous placez une constante dans le ORDER BY, l'optimiseur comprend que vous voulez une simple numérotation des résultats sans tri.

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0) ) AS[Index]
FROM [MaTable]

Et voilà!

[TD 2010] Webcasts

2010-03-13T22:06:00.002+01:00

Les Webcasts des TechDays 2010 sont en ligne à cette adresse: http://www.microsoft.com/france/vision/mstechdays10/. Pour ma petite personne vous trouverez ci-dessous les deux sessions que j'ai eu le plaisir d'animer:

Celle sur la mise en place de cross-selling sur les sites e-commerce:

Et celle sur le CEP avec StreamInsight:

A bientôt et bon visionnage!

Local Mining Models depuis des modèles serveur

2010-02-23T18:19:00.011+01:00

Je me suis une fois posé cette question simple: est il possible, à la manière d'un CREATE GLOBAL CUBE, de créer des modèles de mining locaux stockés dans des .cub (destinés à des applications embarquées) depuis des modèles serveur déjà développés.

Les applications seraient énormes pour des terminaux mobiles déconnectés, dont on mettrait à jour périodiquement un petit modèle d'analyse généré en central.

La réponse au problème est oui, d'où ce post, modulo un petit peu de travail. J'ai fait le boulot sous SSIS pour plus de clarté mais l'intégralité peut se faire depuis du code .NET.

1) Récupérer le script ASSL de création du modèle serveur

Dans une script task, un brin d'AMO suffit à récupérer ce script, grâce à l'objet Scripter utilisé par l'ami Management Studio.

 // Connexion au serveur
Server svr = new Server();
svr.Connect(---);

//Recuperation de la MiningStructure
MajorObject[] source = new MajorObject[1];
source[0] = (MajorObject)svr.Databases["Adventure Works DW 2008 EE"].MiningStructures["Targeted Mailing"];

//Instanciation d'un scripter
Scripter scr = new Scripter();
XmlWriterSettings settings = new XmlWriterSettings();
settings.OmitXmlDeclaration = true;

//Ecriture en mémoire depuis un XMLWriter
StringBuilder sb = new StringBuilder();
XmlWriter writer = XmlWriter.Create(sb,settings);
scr.ScriptCreate(source, writer, true);
writer.Flush();
writer.Close();
svr.Disconnect();

//Affectation de la variable;
Dts.Variables["TempXMLAHolder"].Value = sb.ToString();
Dts.TaskResult = (int)ScriptResults.Success;

2) Retravailler le XMLA avec XPath

Le XMLA généré n'est pas très utilisable: il comprend encore des bindings aux DSV de la source, ainsi que des noms d'objets invalides. Un peu de DOM/XPath suffit à retravailler tout cela modulo la connaissance basique de XMLA.
De plus les modèles de mining embarqués ne supportent que les algos de clustering et de decision trees (les algos 2000), il faut donc supprimer les autres.

 string xmla = Dts.Variables["TempXMLAHolder"].Value.ToString();
string asDbName = Dts.Variables["SsasDatabaseName"].Value.ToString();

//Chargement du doc XML
XmlDocument doc = new XmlDocument();
doc.Load(new StringReader(xmla));
XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable);
mgr.AddNamespace("Ns", @"http://schemas.microsoft.com/analysisservices/2003/engine");

//Changement de la base de données
doc.SelectSingleNode("/Ns:Create/Ns:ParentObject/Ns:DatabaseID", mgr).InnerText = asDbName;

//Suppression du binding de la MS à la DSV
XmlNode n = doc.SelectSingleNode("/Ns:Create/Ns:ObjectDefinition/Ns:MiningStructure/Ns:Source", mgr);
n.ParentNode.RemoveChild(n);

//Suppression des MiningModels non conformes
XmlNodeList l = doc.SelectNodes("/Ns:Create/Ns:ObjectDefinition/Ns:MiningStructure/Ns:MiningModels/Ns:MiningModel", mgr);
string algo = string.Empty;
for (int i = 0; i <>; i++)
{
  algo = l[i].ChildNodes[2].InnerText;  
  if (algo != "Microsoft_Decision_Trees" && algo != "Microsoft_Clustering")
  {
    bool b = true;
    Dts.Events.FireInformation(0,
      string.Empty,
      "Le modèle doit être de type MDT ou MC, modèle de type " + algo + " supprimé.",
      string.Empty,
      0, ref b);      
     l[i].ParentNode.RemoveChild(l[i]);  
    }
  }
  Dts.Variables["TempXMLAHolder"].Value = doc.OuterXml;
  Dts.TaskResult = (int)ScriptResults.Success;

3) Créer un cube local et appliquer l'ASSL dessus

Ceci fait il ne reste qu'à créer un cube local, grâce à la petite ruse de Chris Webb.
Puis à appliquer le script XMLA créé à ce cube (une simple AdomdCommand XMLA du script issu du Scripter AMO et retravaillé en XML).

On dispose alors... de modèles de mining dans un cube local!

4) Entrainer le modèle local

Mais il reste encore à les entraîner. Inutile de préciser que les tâches SSIS classiques sont inopérantes. Elles envoient des erreurs exotiques aidant à comprendre que leur usage devra être contourné.
Pour l'alimentation je suis passé par un paramètre DMX de type DataTable dans un INSERT INTO MINING STRUCTURE comme expliqué par Jamie McLennan dans un de ses posts pré-départ. Cette DataTable est dérivée du très oldschool ADO Recordset de SSIS - beuark - dans un script dédié. Modulo l'ordonnancement des colonnes pour la jointure naturelle voilà un modèle entraîné.

4) Action!

Il suffit alors de l'utiliser, en ADOMD.NET avec une chaîne de connexion pointant sur le fichier .cub en tant que Data Source et voici la "belle" application:

Le package complet est disponible ici et vous pouvez le tester. Il ressemble à cela:

En espérant avoir suscité quelques scénarii de mining embarqué...

A bientôt!

Recherche Google-Like dans une dimension en Reporting Services

2010-02-22T15:07:00.015+01:00

Je suppose que cela a déjà été fait par un certain nombre de pontes et de moins-pontes mais j'ai trouvé l'application marrante. On me demande ce matin de disposer d'un champ libre dans un rapport dans lequel on peut rentrer un certain nombre de mots qui seront des critères de recherche permettant de séléctionner des membres selon leur caption dans un second paramètre en cascade - le tout en MDX est-il besoin de le préciser.
En gros je tape vélo dans une TextBox et dans une combo j'ai tous les membres avec le label vélo. Si je tape "vélo pneu" je récupère tous les produits possédant vélo OU pneu dans leur nom affichable.

Le côté sympa de RS pour éviter un parsing compliqué est de pouvoir disposer de paramètres multivalués en saisie libre, ce qui donne à peu près ceci:

Une fois ceci fait on récupère un objet de type Array, sur lequel on peut appliquer la fonction Join en VBA dans le binding de variable RS.


="{"+
"Filter"+
"("+
 "[Product].[Product Categories].Members"+
 ",VBA!InStr([Product].[Product Categories].CurrentMember.Member_caption,'"+
 Join
 (
  Parameters!Filtre.Value,
  "')),"+
  "Filter"+
  "("+
   "[Product].[Product Categories].Members,"+
   "VBA!InStr([Product].[Product Categories].CurrentMember.Member_caption,'"
 )+
"'))}"

Résultat: je peux faire des recherches dans mon second paramètre en fonction de mots issus du premier. Bon ça n'est pas encore Google (ou Bing :) ) parce que les recherches y sont ordonnées (plus les résultats comprennent de tokens satisfaisants, plus ils sont hauts). Qu'à cela ne tienne, un petit Order MDX plus tard:


="Order"+
"("+
 "{"+
 "Filter"+
 "("+
  "[Product].[Product Categories].Members"+
  ",VBA!InStr([Product].[Product Categories].CurrentMember.Member_caption,'"+
  Join
  (
   Parameters!Filtre.Value,
   "')),"+
   "Filter"+
   "("+
    "[Product].[Product Categories].Members,"+
    "VBA!InStr([Product].[Product Categories].CurrentMember.Member_caption,'"
  )+
 "'))}"+
 ","+
 "IIF(VBA!InStr([Product].[Product Categories].CurrentMember.Member_caption,'"+
 Join
 (
   Parameters!Filtre.Value,
   "')=0,0,1)+IIF(VBA!InStr([Product].[Product Categories].CurrentMember.Member_caption,'"
 )+
 "')=0,0,1)"+
",BDESC)"

...on a un résultat ordonné. Comme je l'ai dit rien d'inédit mais c'est sympa à faire.

PS: Evidemment tout cela serait bien plus simple avec une fonction SSAS dans un assembly dédié mais la contrainte est de ne pas utiliser la procédure magique.
PS2: A quand VBA!Split en MDX!!!

Réentrainement incrémental dans Analysis Services Data Mining

2010-02-12T15:00:00.006+01:00

Une question qui m'est assez souvent posée: la possibilité ou non de procédéer à un apprentissage incrémental dans Analysis Services.
La réponse est nativement non contrairement au fait qu'en AMO les modèles disposent d'une propriété AllowIncrementalInsert qui peut être à true...
C'est un objectif à terme mais aujourd'hui ce n'est pas le cas.

Il faut donc ruser et dans ce type de problématique SSIS est encore une fois votre ami.

L'idée générale est d'activer le DrillThrough dans le modèle considéré et à chaque apprentissage de disposer de deux sources unionées:
- Les nouvelles données récupérées via une OLE DB Source
- Les anciennes données récupérées via une source OLE DB sur AS, avec une requêtre DMX du style

SELECT * FROM [Modèle].CASES

Voilà n'hésitez pas pour toute question.
A bientôt!

StreamInsight, le licensing

2010-02-12T11:51:00.008+01:00

Une question est souvent revenue aux TechDays: le mode de licensing de StreamInsight et son intégration dans SQL Server 2008 R2.

Les développeurs ont éclairci ce point récemment.

En clair, StreamInsight existera en deux éditions, nommées pour le moment Premium et Standard différentes par le nombre d'opérations concurrentes autorisées.

1)La version Standard, qui permet de traiter jusqu'à 5000 évènements par seconde avec une latence tolérée supérieure à 5 secondes, sera livrée avec SQL Server 2008 R2 en versions Standard et Entreprise

2)La version Premium, pour des latences les plus faibles et les nombres d'évènements par seconde les plus élevés, uniquement disponibles sous SQL Server 2008 R2 en version Datacenter.

[TD2010] Merci!

2010-02-11T17:49:00.003+01:00

Un petit mot pour remercier ceux qui ont assisté à mes - finalement - deux sessions de lundi et mercredi aux TechDays, ainsi plus généralement qu'à tous ceux croisés Porte Maillot. En espérant vous avoir montré quelques scénarii intéréssants!
Je posterai les webcasts dès que ceux ci seront dispos.

[Jamie Thomson] Stencils Visio pour SSIS

2010-02-07T12:23:00.000+01:00

Jamie Thomson le signalait: il existe des stencils SSIS pour vos diagrammes de specs/docs sous Visio.

Il les a uploadé sur son SkyDrive, vous pouvez les trouver ici. Je viens de m'en servir, sympa :)

"Programmez!" de Février

2010-01-31T12:46:00.003+01:00

Pour ceux qui souhaiteraient plus d'informations sur le sujet de l'interaction du E-Commerce et du Data Mining sur plateforme Microsoft, j'ai écrit un petit article dans le magazine Programmez! de février, disponible en ce moment.

Si vous souhaitez assister à la session de la semaine prochaine, cela en constitue une bonne introduction. Bonne lecture!

A bientôt!

PS: A ne pas rater dans ce même numéro l'excellent dossier "Microsoft 2010".

TechDays 2010

2010-01-15T08:12:00.008+01:00

Evidemment je serai aux TechDays 2010, l'évènement IT gratuit de l'année, les 8-9-10 février au Palais des Congrès à Paris.

J'animerai même une session sur l'implémentation du cross-selling dans le e-commerce avec du Data Mining sous Analysis Services. Ce sera Mercredi 10 à 11h. D'où le charmant petit logo sur la droite...

Au programme de ces TechDays pour SQL Server: R2,R2,R2! Beaucoup de PowerPivot, une pincée de StreamInsight et de Master Data Services et du Reporting Services.

Je vous encourage à aller visiter le site officiel et à vous y inscrire avec votre LiveID.

Edit: Merci à Jinroh pour avoir corrigé mon erreur de date :)

[SSAS] IgnoreUnrelatedDimension et Drillthrough

2010-01-11T15:36:00.008+01:00

Je suis tombé sur ce que je pense être un petit bug dont je n'avais jamais entendu parler... et qui m'a bloqué une bonne demi journée! Peut être que vous le connaissez et que ça vous paraîtra trivial mais dans mon cas j'étais assez étonné.

Mettez vous dans le cas d'un cube disposant de deux MeasureGroups, un pour le reporting financier sur des indicateurs, l'autre sur l'analyse d'activité plus globale.
Deux particularités:
1) Une des dimensions de l'analyse d'activité dispose d'un DefaultMember qui n'est pas (All) pour complaire aux demandes fonctionnelles.
2) Le groupe de mesure finance étant lié à peu de dimensions a été paramétré en IgnoreUnrelatedDimension=false pour donner un affichage plus user friendly. Il dispose d'une action de Drillthrough.
Lorsque je tente un Drillthrough sur les cellules de certaines requêtes sur le MG financier rien ne s'affiche (voir plus bas).

Qu'est ce qu'IgnoreUnrelatedDimension?

En MDX lorsque l'on effectue analyse une mesure par les membres d'une dimension n'ayant aucun lien avec le groupe de la mesure (pas de relation dans le Dimension Usage) c'est généralement le niveau (All) - ou le membre par défaut si différent - de la dimension qui est affiché, répété sur tous les membres.

Pourquoi? Parce que par défaut les groupes de mesure appliquent le comportement de la fonction MDX ValidMeasure - qui positionne la cellule en cours sur le niveau (All) des dimensions non reliées au MG -, c'est le sens du setting IgnoreUnrelatedDimension, par défaut à True sur un MG.

Parfois on peut souhaiter éviter cette répétition, et que les hiérarchies non reliées n'affichent tout bonnement rien. On affecte donc IgnoreUnrelatedDimension à False.

Pour reproduire cette bizarrerie avec AdventureWorks

Pour simuler ce comportement dans AdventureWorks, mettez par exemple la hiérarchie "Product.Category" avec un DefaultMember bidon. Processez la dimension.
Puis mettez faites une requête:
Date.Fiscal.Year en lignes, Measure.Amount en colonne et Product.Category.All en slicer. Faites un Drillthrough sur n'importe qu'elle cellule... c'est vide.

Retournez dans la dimension Product, remettez la catégorie en DefaultMember par défaut et miracle ça marche.

Plus d'infos

Ce bug est signalé sur Connect à l'adresse suivante, et non résolu à ce jour. Je suis tombé dessus par ce post de Raymond-Lee sur MSDN alors que je ne soupçonnais pas du tout mon MeasureGroup d'être responsable de ça...

Plus globalement tout ce qui tourne autour de ValidMeasure mériterait à mon sens un peu plus d'investigation, que ce soit sur les impacts en termes de performance - il semble bon d'après Mosha d'utiliser ValidMeasure dans les cas de membres calculés sollicitant la feature de reconnexion au niveau (All) mais après? - que sur les éventuels problèmes similaires à celui ci - j'imagine qu'il y en a - et difficilement trouvables.

A bientôt.

Good bye Mosha... and good luck!

2009-12-30T16:40:00.002+01:00

Mosha Pasumanski l'annonce sur son blog: il ne mènera pas de front son travail sur Bing et ses activités sur Analysis Services. Il arrête donc de se consacrer à la BI et son blog - grâce auquel chacun a pu s'initier au MDX puis se perfectionner - quoique toujours en ligne ne traitera plus de ces sujets...

So good bye Mosha!

PS: quelqu'un pour reprendre MDX Studio?

[SSAS] Les réglages de mémoire (LowMemoryLimit, TotalMemoryLimit...)

2009-12-30T15:15:00.004+01:00

Quelques rappels je pense utiles sur le paramétrage et le monitoring de cette chose si mal connue qu'est la mémoire sous Analysis Services. L'information existe certes mais elle est suffisament éparse pour perdre quelque peu le débutant et beaucoup de contre-vérités subsistent.

Les réglages serveur

Il existe deux "limites" de mémoire pour Analysis Services, réglables dans les propriétés serveur. La LowMemoryLimit et la TotalMemoryLimit - aussi appellée improprement mais peut être plus justement HighMemoryLimit dans certains ouvrages...

Leur valeur est soit un pourcentage de la mémoire du système (par défaut LML=65% et HML=80%) si elle est en dessous de 100, une valeur absolue en octets sinon.

Pour vérifier leur valeur effective, il suffit de regarder les compteurs MSAS2008:Memory.MemoryLimitLowKB et MSAS2008:Memory.MemoryLimitHighKB.

Effet de ces limites

Ce ne sont pas des limites - le serveur peut allègrement les dépasser pour exécuter une requête ou un processing - mais plutôt des caps, des niveaux qui déterminent le mode de nettoyage à appliquer par le serveur sur sa mémoire de travail. Il est donc fréquent de voir des serveur AS dépassant la HML.

Dans une situation normale, en dessous de LowMemoryLimit, le serveur ne nettoie rien ou presque. Lorsque la mémoire utilisée (MSAS2008:Memory.MemoryUsageKB) dépasse la LowMemoryLimit le serveur passe en mode LowMemoryPressure ce qui a pour effet de démarrer le nettoyeur pour supprimer certaines ressources.

Ressources supprimables par le nettoyeur

Grosso modo le nettoyeur va surtout s'occuper supprimer du cache. Il applique un algorithme de sélection qui va effacer les éléments les moins requêtés et les plus faciles à supprimer. Pour savoir si cette tâche est en cours il est possible de voir dans PerfMon le compteur MSAS2008:Memory.CleanerCurrentPrice dépasser 0.

Et quand on dépasse la HighMemoryLimit?

La le nettoyeur passe en mode aggressif (le serveur est alors en HighMemoryPressure) il supprime à tout va tout ce qui est supprimable donc basiquement le cache. Si cela ne suffit pas à descendre en dessous de HML- ce qui est alors souvent le cas - il commence à demander l'arrêt des processings et requêtes en cours car il ne possède pas d'accès direct à leur mémoire et ne peut donc les "nettoyer".
Il s'agit alors de la seule solution restante pour redescendre dans de saines limites.

C'est la raison du fameux message:
Server: The operation has been cancelled due to memory pressure.

Et qu'est ce c'est que ce "PreAllocate"?

PreAllocate est un setting d'Analysis Services conçu pour pallier à des déficiences d'allocation de mémoire de Windows Server 2003. Basiquement il s'agit de préallouer un volume de mémoire fixé à SSAS pour éviter les lenteurs d'allocation. Il ne sert plus à rien - voire même empire la situation - sous Windows Server 2008 (voir à ce sujet ce très bon bench).

Pour finir pour savoir si vos requête arrivent bien à mettre et à taper en cache - ce qui veut dire que tout fonctionne bien finalement... - rien d 'équivalent à l'excellentissime MDX Studio. Et si vous êtes plus intéressés par le sujet mémoire, un petit coup d'oeil au PerformanceGuide ne fait jamais de mal.

A bientôt!

[SSAS] Afficher une Parent Child sur un seul niveau avec MDX

2009-12-23T14:28:00.031+01:00

Curieuse demande en apparence, mais finalement assez compréhensible. Lorsque l'on utilise une hiérarchie - ce qui est très pratique pour certains états - il est fréquent de vouloir aussi disposer des informations sous une forme non-hiérarchisée, une sorte de liste, et que cette information soit disponible sous Excel 2007.

C'est particulièrement le cas fonctionnellement dans le domaine du reporting financier et plus techniquement dans tous les domaines où tous les niveaux hiérarchiques peuvent porter des données, donc des dimensions généralement en Parent-Child.

Pour générer une liste de tous les attributs d'une Parent-Child à plat, rien de plus simple: la hiérarchie d'attributs sur laquelle s'appuie la Parent-Child le fait nativement.
Par exemple la hiérarchie Employee (sans S) d'AdventureWorks liste bien tous les Employés... mais lorsqu'on y associe une mesure elle n'est capable d'afficher que les DataMember, autrement dit les mesures associés à ce membre précis et non la valeur aggrégée que la hiérachie rapporte.

Par exemple l'on regarde les valeurs de la Parent-Child (Employees) on constate:
1) Que Ken Sanchez et Brian Welcker n'apparaissaient pas dans la hiérarchie d'attributs: ils n'ont pas de faits associés.
2) Qu'Amy Alberts elle apparaît bien à plat mais avec une valeur fausse: la valeur affichée est en effet celle de son DataMember et pas l'aggrégation de celui ci et de ses enfants.

L'idée est donc de recopier dans la hiérarchie d'attributs la valeur de la Parent-Child.
L'instruction LinkMember permet de récupérer un membre d'une hiérarchie à partir d'un membre d'une autre du moment qu'ils partagent la même clé (ce qui est le cas ici). Certes ce n'est pas le top niveau performance (même si dans la même dimension c'est légèrement mieux), mais de toute manière cela ne concerne pas des dimensions de cardinalité trop élevée vues les applications métier.

On peut réaliser cette affectation via un SCOPE dans le script MDX, de cette manière:

SCOPE
(
[Employee].[Employee].[Employee].Members
);
This = LinkMember([Employee].[Employee].CurrentMember,[Employee].[Employees]);
END SCOPE;

Le résultat est tout bonnement enthousiasmant et l'effet immédiat:

Les noeuds sans données apparaissent (Welcker, Sanchez...) ceux composites sont corrigés (Amy Alberts). Bref tout semble parfait. Sauf qu'il y a un hic.
Si l'on re-regarde la Parent-Child, qui s'appuie sur cette hiérarchie d'attributs, on constate que les chiffres sont complétement faux. En effet, la Parent-Child tire ses données... de la hiérarchie d'attributs... qui elle même tire ses données de la Parent-Child.

Par exemple Amy Alberts est égale à son Data Member plus la somme de ses enfants. Son DataMember vaut 732.078,44 - voir premier screenshot - et la somme de ses enfants 14.803.867,81 - merci calc.exe -, le tout faisant 15.935.546,26, la valeur affichée dans la hiérarchie initialement.
Or ici on a:
15.935.546,26 (la valeur réaffectée dans la hiérarchie d'attributs, que SSAS considère à tort comme le DataMember)
+ 14.803.867,81 (la somme des enfants)
=30.339.814,07

Il faut donc verrouiller la ParentChild avant de faire la modification de la hiérarchie d'attributs, ce que l'on peut faire avec l'instruction Freeze.
Le script précédent devient alors:

FREEZE([Employee].[Employees].Members);
SCOPE
(
[Employee].[Employee].[Employee].Members
);
This = LinkMember([Employee].[Employee].CurrentMember,[Employee].[Employees]);
END SCOPE;

Du coup le résultat devient beaucoup plus acceptable:

La hiérarchie peut donc maintenant faire un rapport Excel à mettre à disposition via Excel Services par exemple, permettant d'analyser des membres de niveaux différents dans la même colonne. C'est l'idée générale qui permet de faire des rapports sur des statistiques, des comptes d'exploitation choisis et mis à plat, pour ne prendre que des exemple communs utilisant les Parent-Child...

En espérant vous avoir donné des idées, mais attention cependant, il s'agit clairement d'une manip (les parent-child et leur hiérarchie d'attributs sont très liées) dont je n'ai pas eu le temps de tester extensivement toutes les implications. Si vous constatez un comportement étrange n'hésitez pas à le mettre en commentaire.

A bientôt!

PS: avec Excel 2010 ceci serait obsolète: les PivotTables disposent d'un Set Designer, il suffit alors de se créer un jeu contenant les employés souhaités. Mais 2007 est encore là pour quelques années...

[SSAS] Réhabilitation des ParentChild (ou pourquoi elles ne sont pas le mal absolu)

2009-12-16T11:06:00.010+01:00

Je discutais l'autre jour avec quelques confrères lorsque j'ai entendu cette phrase au milieu d'un brouhaha technique.

"... les ParentChilds il faut que vous les enleviez: question performance c'est nul, fonctionnellement ça ne sert à rien. Analysis Services n'est pas fait pour ça, il les ont laissées pour s'adapter à certains types d'architectures existantes. Il suffit de lire le Performance Guide."

Je n'aime pas ce genre d'affirmation pontifiante. D'autant plus quand, comme toute assertion de ce type, elle s'inspire de faits et documents réels en les dénaturant.

Je me propose donc modestement de remettre un petit peu de clarté la dedans.

"Les ParentChild, question performance c'est nul"

Les faits: dans une hiérarchie ParentChild, les aggrégations ne sont créées que pour l'attribut clé et le membre de All de la hiérarchie. Dit autrement des aggrégations ne sont pas calculées sur les niveaux intermédiaires.

Réponse: Le coupable serait donc l'absence de calcul d'aggrégats. Ceux ci sont certes très utiles et jouent un rôle crucial. Mais considérer qu'ils constituent l'unique intérêt de l'OLAP et que leur absence est dans tous les cas catastrophique en terme de performances témoigne d'une certaine méconnaissance de ce genre de systèmes. L'idée de l'OLAP est certes de précalculer mais avant cela de se baser sur un formalisme de stockage spécifique permettant rapidement de récupérer les informations. Un cube même sans aggrégats est bien plus efficace qu'une base relationnelle pour les calculs multidimensionnels habituels.

Une ParentChild n'est donc pas en tous les cas catastrophiques en termes de performances... Tout dépend de la volumétrie!
Plus troublant dans certains cas une utilisation extensive du HideMemberIf dans des hiérarchies naturelles - pour permettre de disposer de hiérarchies irrégulières sur de nombreux niveaux - fait qu'une ParentChild peut être plus performante. (cf ce post d'Ajit Singh pourtant grand pourfendeur des P/C sur SSAS-Info).

"Il suffit de lire un peu la documentation enfin!"

Les faits: le Performance Guide déconseille d'utiliser les ParentChilds et recommande de passer à un design naturel... ...mais sur de grosses volumétries et sur beaucoup de dimensions et dans le cas ou fonctionnellement l'alternative est possible, comme visible ci dessous:

Réponse: il s'agit simplement d'un raccourci sur le propos de cet excellent guide. L'extrait est ci-dessous.

If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy.

Analysis Services Performance Guide

"Fonctionnellement ça ne sert à rien."

Les faits: Je crois que c'est l'assertion la plus stupide. Cela ne se base sur rien et vient simplement d'un bouche à oreilles général.

Réponse: Il est facile de trouver quelques cas où la modélisation sans ParentChild est fastidieuse. Même Mosha en convient, au début de ce post.

1) Un bien connu est une hiérarchie opérationnelle récursive: les faits ne se situent pas au niveau le plus bas mais potentiellement partout (au niveau du manager, d'un membre de son équipe...). Les règles de Rollup sont de plus potentiellement compliquées.
En Ragged c'est un enfer à gérer. En ParentChild cela se fait simplement. Et il est rare de disposer de plusieurs centaines de milliers de membres dans ce genre de dimension...

2) Un autre exemple est une hiérarchie de type plan de compte, sur le même modèle ou chaque élément de fait peut référencer un niveau qui n'est pas le plus bas. Dans ce scénario de plus, il est fréquent que les utilisateurs veuillent utiliser les indicateurs de plusieurs niveaux dans la même colonne d'un rapport Excel, et donc disposer de deux représentations: sur un niveau et multi-niveau: le ParentChild est la seule manière d'arriver à cela simplement. De plus là aussi on atteint rarement une volumétrie énorme!

D'ailleurs AdventureWorks contient deux exemples identiques... modélisés en ParentChild -Organization et Account. Pourquoi diable les montrer dans des exemples fonctionnels si comme certains l'affirment "le ParentChild n'est là que pour la compatibilité avec des existants ou des habitudes".

En conclusion les P/C sont moins performantes que les hiérarchies naturelles sur des grosses volumétries de par leur lacune dans le calcul des aggrégats. Elles sont cependant une nécessité fonctionnelle dans certains cas et il est absurde de s'empêcher de les utiliser sous des prétextes fallacieux.

[SSAS] Utiliser les viewers Data Mining dans un BackOffice Winforms

2009-12-07T21:44:00.004+01:00

Petit post très court pour donner des idées. Pour intégrer les viewers de modèles de Data Mining disponibles dans Visual Studio au sein d'Excel, les développeurs de Redmond ne les ont pas recodés. Ils ont simplement profité du fait que ceux ci sont disponibles en tant que composants Winforms et que vous pouvez faire de même, à savoir les réutiliser dans vos applications.
Il suffit pour cela de les ajouter à la Toolbox Winforms, en allant chercher l'assembly Microsoft.AnalysisServices.Viewer.dll qui doit se trouver dans le dossier Common7\IDE\PrivateAssemblies de votre installation de Visual Studio.

A vous les jolis backoffices!
A bientôt!

[SSAS] Assembly ExcelMDX sur le serveur SSAS?

2009-11-25T12:55:00.002+01:00

Une petite note très rapide sur l'assembly ExcelMDX: on m'a encore demandé pourquoi il na marchait pas sur le serveur de production d'une entreprise...
ExcelMDX comme son nom l'indique est associé à... Excel.
Il nécessite Excel pour fonctionner ce qui est rarement - qui a dit jamais? - le cas pour une configuration de production. Alors oui il apporte quelques fonctions salutaires au MDX mais il vaut mieux ne jamais l'utiliser sous peine de faire face à des suprises au déploiement.

C'est un choix d'architecture pour le moins... étonnant.

Pour un utilisateur de MDX la seule solution est de remplacer les fonctions ExcelMDX: en se débrouillant avec le MDX existant ou en codant une procédure stockée managée.

A bientôt!

PS: Le lien de cette bizarrerie sur Connect, posté initialement par Greg Galloway. Le walkthrough MSDN de création de procédures stockées si cela peut vous aider. Merci à Gurvan et à son blog de haut vol de m'y avoir fait repenser.

[SSAS] L'opérateur UNION manquant en DMX

2009-11-23T16:31:00.008+01:00

Il vous est peut être arrivé de vouloir intégrer un Forecast généré avec Analysis Services Data Mining dans une application. C'est très joli à présenter, et ça l'est encore plus au sein d'un rapport Reporting Services.

Malheureusement, l'instruction PredictTimeSeries() qui gouverne la sortie d'un modèle de type TimeSeries ne permet que d'effectuer des sorties de données passées OU futures mais pas les deux mixées... Or l'ami Reporting Services ne supporte qu'un DataSet par DataRegion donc impossible de réaliser nativement une belle courbe avec le réel suivi du forecast...

Jamie Mc Lennan l'architecte du produit, propose sur son blog de réaliser une SP managée pour rempalcer PredictTimeSeries. Solution efficace et que j'ai déjà testé, mais qui masque à mon avis un problème de DMX, le manque d'opérateur UNION en n'adressant qu'une seule problématique induite.

En codant un petit bout de C# on arrive pourtant facilement à obtenir un pseudo opérateur union parfaitement fonctionnel, d'abord en écrivant une méthode d'exécution de code DMX renvoyée sous forme de DataTable:

private DataTable ExecuteQuery(string query)
{
    using (AdomdCommand cmd = new AdomdCommand(query))
    {
        AdomdDataReader dr = cmd.ExecuteReader();
        DataTable destinationDt = new DataTable();
        DataTable schemaDt = dr.GetSchemaTable();
        foreach (DataRow schemaDr in schemaDt.Rows)
        {
            destinationDt.Columns.Add((string)schemaDr["ColumnName"], (Type)schemaDr["DataType"]);
        }

        DataRow currentDr = null;
        while (dr.Read())
        {
            currentDr = destinationDt.NewRow();
            for (int i = 0; i < dr.FieldCount; i++)
            {
                currentDr[i] = dr[i];
            }
            destinationDt.Rows.Add(currentDr);
        }
    }
    return destinationDt;
}

Qu'il suffit ensuite d'utiliser avec cette méthode:

private DataTable ForgeUnionDMX(params object[] dmxQueries)
{
    DataTable t1 = this.ExecuteQuery(dmxQueries[0].ToString());
    for (int i = 1; i < dmxQueries.Length; i++)
    {
        t1.Merge(this.ExecuteQuery(dmxQueries[i].ToString()));
    }
    return t1;
}

Malheureusement, et c'est le seul point que je n'ai pas réussi à résoudre, il semble qu'Analysis Services soit incapable de reconnaitre les procédures stockées acceptant un paramètre de type params (ParamArray en VB).
Ce n'est donc pas cette méthode qu'il faut exposer mais plutôt autant d'alias qu'on souhaite accepter de paramètres...

public DataTable UnionDMX(string dmxQuery1, string dmxQuery2)
{
  return this.ForgeUnionDMX(dmxQuery1, dmxQuery2);
}
public DataTable UnionDMX(string dmxQuery1, string dmxQuery2, string dmxQuery3)
{
  return this.ForgeUnionDMX(dmxQuery1, dmxQuery2, dmxQuery3);
}

Sale et dommage, mais inéluctable semble-t-il: Greg Galloway et Darren Gosbell ont fait de même à de nombreux endroits dans le projet ASSP.

Voilà à vous de coller ça dans un projet C# et de l'essayer: vous allez voir c'est très joli et cela constitue un argument assez immédiat en faveur de ce type d'applications

A bientôt.

[SQL] R2 CTP3 Go Go Go!

2009-11-10T11:19:00.004+01:00

SQL Server 2008 R2 November CTP est disponible! Go!

Cette version est feature complete. Pour un résumé des nouveautés, allez sur l'excellent post de Teo Lachev.

Bon test!

[SQL] On ne parle que de Gemini, on oublie StreamInsight!

2009-11-10T10:46:00.006+01:00

C'est vrai, depuis quelques mois on ne parle que de Gemini ou plutôt PowerPivot la fonctionnalité de D-OLAP à la sauce Microsoft introduite dans Excel 2010 sur 2008 R2. Mais c'est oublier une autre nouveauté à mon sens presque plus critique: StreamInsight, le moteur de CEP (Complex Event Processing).

CEP kézako?

Le CEP en fait est facilement compréhensible par son nom: il s'agit de gestion d'évènement complexes. Qu'est ce qu'un évènement complexe? Typiquement une composition d'évènements simples détectables dans des bases de données, sur des senseurs...

Un senseur qui passe dans le rouge est facile à détecter, la réaction est facile à coder. La programmation à base d'évènements simples est la base du développement moderne. En revanche définir, détecter et traiter des compositions d'évènements est bien plus complexe car généralement orienté business.

Ce sont des règles métier qui lui donnent une cohérence, une existence. Lorsque l'évenement est détecté, il déclenche un processus de notification, d'action.
Ces règles métier sont appelées Query Logic sous StreamInsight et définies en LINQ.

Un moteur de CEP comme StreamInsight fournit donc des interfaces d'E/S et les librairies .NET nécessaires pour écrire d'une part les Streams d'entrée et de sortie et d'autre part la Query Logic de ces évènements complexes.

Des exemples?

Oui, autant que vous le souhaitez! LINQ de part son requêtage aussi puissant que SQL permet de définir des aggrégations et donc des KPI: il est possible de réagir en temps réel, sans passer par une phase de retraitement des données, au changement d'un KPI comme celui-ci

var MoyenneCA = from Groupe in InputStream.Snapshot()
select new { avg = Groupe.Avg(Mesure => Mesure.CA))};

Encore mieux: il est possible de combiner les sources des Streams et d'interroger un modèle de Data Mining pour vérifier la conformité en temps réel, et sans stocker de données!

Et on peut tester?

Bien sûr! Pour le moment je n'ai testé que des choses très simples (Streams SQL qui envoient à un modèle de DM, Streams fichier qui sont traitées par des KPI simples...) mais à vous de jouer pour des choses encore plus techniques!
SQL Server 2008 R2 August CTP est disponible sur Technet et n'attend que vous!

Bon test et à bientôt!

[SSAS] VisualTotals et Excel 2007

2009-11-08T17:55:00.002+01:00

Je voulais rappeler quelques informations au sujet de la gestion des VisualTotals sous Excel 2007. Pour rappel un VisualTotal - ou "total visuel" qui dit bien ce que c'est - consiste simplement à afficher comme total d'un niveau aggrégé une valeur dépendante de la sélection.

En clair, dans une PivotTable, si je n'affiche que les produits A, B et C dans ma dimension Produit avec la mesure NbVentes, j'aurai au niveau supérieur la somme des ventes des produits A, B et C et non pas de tous les produits de même niveau que A, B et C. Ce qui peut être voulu ou pas selon le type de rapports.

Cette fonctionnalité n'est pas désactivable en 2007, alors qu'elle l'était en 2003. C'est un effet de bord d'un changement de mode de requêtage qui intervient pour résoudre les problèmes de multisélection dans les filtres.

En AS 2000, pour filtrer (slicer) sur un set (par exemple quelques pays) il n'est pas possible d'écrire cela:

SELECT
...
WHERE ({[Pays].[Pays].[Pays].&[UK],[Pays].[Pays].&[FR]})

La multiselection dans le slicer n'est pas supportée. C'est une fonctionnalité 2005 et supérieure. En 2003 (PivotTables v10), les filtres sont donc implémentés à la "manière 2000", - c'est pareil dans ProClarity en fait - les filtres sont générés dans des membres calculés et positionnés dans le slicer.

WITH MEMBER [Pays].[MonFiltre] AS
Aggregate 
Aggregate ({[Pays].[Pays].[Pays].&[UK],[Pays].[Pays].&[FR]})
...
SELECT
...
WHERE ([Pays].[MonFiltre])

(Ce qui implique de fameux problèmes avec le CurrentMember - le CurrentMember de ma hiérarchie Pays est où dans le cas précédent? C'est un set! - bien explicités par Mosha dans son fameux papier).
Le fait d'autoriser cette multisélection dans le slicer n'a pas été exploité dans Excel 2007. Pour permettre des fonctionnalités de filtrage plus avancés en déléguant correctement les tâches de filtrage au moteur AS, Excel utilise les sous-requêtes (subselect) .La multisélection est donc gérée par un sous cube là aussi une nouvelle fonctionnalité introduite après 2000:

SELECT
...
FROM
(
SELECT ({[Pays].[Pays].[Pays].&[UK],[Pays].[Pays].&[FR]}) ON 0
FROM [MonCube]
)

L'effet de bord de gérer cette multisélection dans un sous-cube est donc de ne générer que des totaux visuels (VisualTotals): on ne ramène jamais les totaux du cube.

Pour faire cela, si vous en avez un besoin impérieux il existe une seule solution: créer des PivotTables en version précédente.

Cela peut se faire de trois manières:
- En VBA en créant une Macro qui créera la PivotTable en version 10
- En VSTO sur le même modèle (recréer un bouton "créer PivotTable" pour forcer l'utilisation de la version précédente, je l'ai fait, c'est assez élégant).
- En utilisant le mode de compatibilité d'Excel, qui va créer la PivotTable en version précédente
: attention cependant si vous enregistrez la feuille en compatibilité vers le mode standard, les PivotTables seront marquées pour mise à jour, et mises en version 12 au prochain Refresh, c'est à vous de l'interdire en... VBA en utilisant la propriété UpgradeOnRefresh des PivotTables.
(Merci au blog des développeurs d'Excel pour cette précision qui m'a évité de devenir fou :)

En espérant que ce post ait pu être utile à quelques uns.
A bientôt!

[SSAS] 2008 et les précédences de calcul

2009-10-27T10:23:00.006+01:00

Il semble vraiment que la tendance amorcée depuis 2005 avec une simplification des règles d'évaluation des calculs MDX se perpétue en 2008.
Tout cela remettant en cause pas mal de modes de développement choisis - à bon ou mauvais escient - sur les versions précédentes. La migration 2005/2008 peut donc poser des problèmes à certains dans des cas assez particuliers, sur lesquels je voulais attirer votre attention et dont je possède un exemple assez frais.

Si vous parcourez BOL (http://msdn.microsoft.com/fr-fr/library/ms143742.aspx) vous tomberez sur ceci:

Si un cube contient une dimension avec des formules de niveau personnalisées ainsi que des dimensions avant et après avec des formules de membre personnalisées et/ou des opérateurs unaires, il se peut qu'il retourne des résultats différents de ceux des versions précédentes de Analysis Services. Cette situation se produit car le calcul des règles de précédence a changé.

Intéressant. En se plongeant dans les articles de BOL sur le SOLVE_ORDER et dans l'excellent AS Unleashed, on constate en effet un gros effort de documentation de ces règles de précédence et de calcul, qui contraste avec le peu de différences entre les versions de documentation sur les autres aspects du moteur.

Pour résumer il semble que les bidouillages à base de CALCULATION_PASS et de SOLVE_ORDER voient leurs instants comptés dans le script MDX du cube, au bénéfice de l'ordonnancement de celui-ci. Cela se confirme par la dépréciation des CELL CALCULATION au profit des assignments qui eux ne disposent pas de propriétés d'ordre modifiables.
Certes il n'est pas vrai que l'ordre du script soit toujours l'ordre d'évaluation - le MDX n'est pas du C#, cf l'utilité de FREEZE - mais il bénéficie au moins d'une prédictivité plus évidente.

Dans le cas de combinaisons avec des HighestPass (Unary, Custom Rollup) l'ordre de résolution des calculs est maintenant plus clair: entre une CR et un ensemble de calculs donné du script on comprend désormais quelles sont les règles de précédence, même si elles sont parfois troublantes. Il reste possible de jouer avec le SOLVE_ORDER dans la CustomRollupPropertyColumn mais là encore l'ordre n'est pas garanti (en effet le SOLVE ORDER n'est pas le seul critère d'évaluation, cf http://msdn.microsoft.com/en-us/library/ms145539.aspx).

Ces changements peuvent parfois poser des problèmes: le fonctionnement peu clair de 2005 obligeait à des développement empiriques - comme celui sur lequel je travaille en ce moment.
L'ordre de calcul entre deux expressions MDX et une C.R.Formula était pour le moins étrange: en 2005 la rollup s'évaluait au beau milieu des calculs de script sans que l'on en comprenne la raison.
Une fois migrée en 2008, la rollup s'évalue dans mon cas avant conformément aux règles énoncées et cela peut poser des problèmes si on considérait la précédence 2005 comme acquise.

La réponse de Microsoft à ce sujet est de tenter de ne jamais mélanger assignments, membres calculés et CustomRollup sur un même calcul car même mieux documentées ces règles peuvent avoir un comportement défiant la logique initiale.

[SQL] "Performance counter registry hive consistency check" failed.

2009-10-12T18:15:00.010+02:00

Vous aurez peut être cette erreur un jour en installant SQL Server 2008 via le gentil SCC (System Configuration Checker). Je l'ai déjà eue plusieurs fois, et une dernière ce matin qui m'a donné envie de rappeller les causes.

Basiquement cette erreur est liée à Perfmon et à la disponibilité des compteurs de performances. La recommandation faite par MS est une réparation fastifieuse de la base de compteurs, alors que le problème vient souvent d'un des deux points suivants.

1) Clé de registres incohérentes

Possiblement généré par pas mal d'installations (ou un développeur aimant s'amuser dans le registre). Dans le registre sont en effet listés l'intégralité des compteurs de perfmon, précisément dans cette clé: HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Windows NT\ CurrentVersion\ Perflib\

Et encore plus précisément dans deux valeurs qui sont Counter et Help.
Si vous regardez ces deux valeurs (du texte très long) les compteurs sont numérotés et ordonnés.

Normalement la dernière valeur d'id de compteur de Counter et Help sont respectivement reportées dans les valeurs LastCounter et LastHelp de la clé: HKEY_LOCAL_MACHINE\ SOFTWARE\ Microsoft\ Windows NT\ CurrentVersion\ Perflib\ 009\

Si cela n'est pas le cas, à vous de corriger ces dernières! Si ette erreur ne se présente pas, passez au point suivant.

2) Nom de clé non adapté à la locale

La clé précédente se termine par 009. Cela correspond à une installation anglaise (en-us) de Windows. Et si ce n'est pas votre cas? Sur une installation française par exemple, ce dossier se nomme 00C. A quoi cela correspond? Tout simplement à la conversion en héxa de la Culture sur 3 caractères. Il faut impérativement que ces deux valeurs correspondent.
Avec ce petit bout de code C# vous pouvez le récupérer facilement.

string culture = string.Format("{0,3}", CultureInfo.InstalledUICulture.Parent.LCID.ToString("X")).Replace(" ", "0");

Il ne vous reste plus qu'à réaliser un backup de la clé 009 et de la restaurer sous un nom approprié!
En espérant vous avoir aidés.

A bientôt!

Nouvelle(s) expérience(s)

2009-10-10T17:52:00.002+02:00

Voilà, de retour de vacances, ce qui explique l'inactivité bloggesque de ces dernières semaines que je vais m'employer à réparer. De plus, je démarre dans une nouvelle structure dont je ne manquerai pas de parler dans les prochains mois, je vous tiendrai au courant. Une page se tourne et une expérience plus que sympathique se profile...

Merci pour vos mails et le temps que vous passez à lire ma prose!

A très vite.

[Biblio] "Expert Cube Development with AS", un must!

2009-09-10T09:45:00.004+02:00

Quand on vous annonce que Chris Webb écrit un bouquin avancé sur AS associé à Alberto Ferrari et Marco Russo (les créateurs de SqlBI) il y a de quoi s'attendre à quelque chose d'intéressant.
Le résultat est tout bonnement... bluffant.

Il existe des dizaines de bouquins techniques sur AS, quelques uns sont bien, certains très bien (j'en ai déjà parlé). Le problème c'est qu'ils font un bon millier de pages et perdent souvent du temps sur des notions déjà connues. Ils abordent des sujets intéressants, voire passionnants mais qui ne servent pas à grand chose en mission. Ils sont un peu "geek" en somme.

Il existe aussi pas mal de bouquins sur la gestion de projets BI, mais ils ont le défaut d'être souvent un peu dogmatiques et relativement déconnectés de la technique. Ils sont un peu trop "chef de projet".

En DataWarehousing il y a Kimball et Inmon, et leurs très gros pavés de design absolument essentiels, mais dans lesquels chacun doit faire son marché. Là aussi, peu d'interaction avec la technique pure, parfois un côté un peu "biblique". Ils sont un peu trop "prof".

Chris Webb et Ferrari/Russo ont réussi l'exploit d'écrire un bouquin de "consultants". Un livre calqué sur le développement d'un projet, du design du DW au tuning des bases OLAP. Pour chaque chapitre, les trois "vues" ci dessus sont abordées: la technique, la gestion du projet et la théorie mais uniquement dans le but de satisfaire un objectif unique, réussir son projet, c'est à dire être en mesure d'utiliser l'outil au meilleur de ses possibilités, dans un temps minimal, pour apporter les réponses demandées.

On pouvait craindre un énième livre sur AS comme il en trône sur la bibliothèque de tous les consultants: il n'en est rien! Ce livre a été pensé et écrit pour être utile au jour le jour au public qu'il cible, le consultant BI MS, et à mon sens c'est une réussite. Il fait seulement 400 pages, coute 35€, il se lit en quelques trajets de métro et vous apprendrez énormément avec.
Gardez le avec vous - tiens il est juste à côté de moi là! - il sera amené à vous servir énormément.

Le seul inconvénient de ce livre finalement, c'est qu'il est d'un tel niveau qu'il vous enlève toute velléité d'écrire un bouquin sur le sujet un jour... On comprend ce que doit ressentir Dany Brillant devant une vidéo de Frank Sinatra.

[SQL] Pourquoi le Change Data Capture va changer notre vie

2009-08-21T12:09:00.005+02:00

Rien que ça... Mais je parierais que ça n'est pas immérité. Avec les latences dans les adoptions de nouvelles technos ma "prédiction" ne s'avèrera peut être vraie que dans 2 ou 3 ans (oui on bosse encore beaucoup et majoritairement en 2005!).
Mais bon, reprenons depuis le début...

"C'est quoi le CDC ou Change Data Capture?"

C'est un mécanisme de tracking de modification de contenu de tables. Basiquement le CDC crée des tables de suivi des modifications DML (INSERT, UPDATE, DELETE) effectués dans une table de données. Ces tables sont nourries par des jobs qui scannent le log de la base, comme visible sur cette belle slide de formation ci-dessous.

"Change Data Capture" n'est pas le nom d'un produit. C'est une désignation de ce genre de feature. Ce processus existe par exemple chez Oracle (depuis 9i si mes souvenirs Oracliens sont bons, mais n'hésitez pas à me démentir), sous Infosphere, Sybase.... et généralement suivant le même principe (Un RDBMS maintient un Tran Log, pourquoi ne pas s'en servir après tout?)
Il a été introduit en environnement Microsoft sous SQL Server 2008.

"Mais ça va servir à quoi en Data Warehousing? On a déjà SSIS!"

Le CDC n'a pas pour but de remplacer SSIS mais de faciliter les alimentations de Data Warehouses, en ce qui concerne les changements dans les sources. Soyons clair: SSIS est un fantastique outil qui permet de gérer des workflows complexes, d'implémenter des alimentations massivement multi-sources, des règles métier tordues et des SCD bizarroïdes, mais on génère un énorme overhead dans la gestion de la recherche des modifications sur les sources.

Par exemple on ne peut pas toujours compter sur une datation des lignes modifiées en production. On passe très - trop - souvent par une remontée intégrale sur le serveur ETL des référentiels, et des comparaisons avec le DW pour tracker les modifications.

Dans des cas plus sympathiques les modifications dans cette base de production sont trackées dans un champ de type DateTime qui stocke le dernier Update par exemple, et il suffit de récupérer les lignes déjà modifiées.
Soit ça marche déjà mieux. Mais c'est limité!

Et si vous voulez récupérer des modifications intermédiaires? Vous implémentez une gestion par trigger lentissime, à vidanger soit même...
Le CDC résout ces problèmes sans vous demander une once d'implémentation.

"Oui... enfin techniquement on doit bien faire quelque chose non?"

Je vous l'accorde. Il faut quand même faire quelques petites choses .
1) Activer CDC sur la base concernée

EXEC sp_cdc_enable_db
GO

2) Activer CDC pour une table à suivre

EXEC sys.sp_cdc_enable_table
@source_schema = 'MonSchéma'
, @source_name = 'MaTableASuivre'
, @role_name = NULL /*Rôle pour restreindre l'accès aux tables de tracking, Null si pas de restriction*/
, @capture_instance = 'NomDeCaptureInstanceDeMaTableASuivre'
, @supports_net_changes = 1 /*Suivi des modifications intermédiaires, 1 pour oui*/
, @captured_column_list = 'MaColonne1, MaColonne2'
, @filegroup_name = 'FileGroupPourStockerMaTableDeTracking

Et c'est terminé. Le CDC est en route.

"C'est stocké où? On récupère ça comment?"

Vous avez vu ci-dessus: on spécifie un FileGroup. Tout simplement parce que c'est stocké dans une table par table tracée, et que cette table de tracking, nommée _CT est stockée sur ce FileGroup dans un schéma dédié au cdc. Il est plus que conseillé de ne pas utiliser le FileGroup de données pour le CDC !
Après on peut soit récupérer les infos directement... mais ça n'est pas la manière la plus aisée :)
Soit utiliser deux fonctions de table:
- fn_cdc_get_all_changes_MaCaptureInstance qui récupère tous les changements (U,I,D) entre deux LSN
- fn_cdc_get_net_changes_MaCaptureInstance qui récupère le dernier état de chaque tuple entre deux LSN

"LSN? Quel rapport avec le log?"

On l'a dit quelques lignes au dessus: le CDC marche sur le log donc les modifications sont tracées par LSN. Le LSN, ou Log Sequence Number est la valeur unique associée à chaque enregistrement dans le log.
Mais rassurez vous! On peut facilement faire correspondre une date à un LSN, en demandant à la fonction sys.fn_cdc_map_time_to_lsn de renvoyer le LSN le plus proche d'un DateTime.

SELECT @lsn = sys.fn_cdc_map_time_to_lsn (
'smallest greater than or equal'
, '2009-08-21 00:00:00.000'
);

"Ca m'inquiète ce lien avec le Log... alors on est obligés d'être en Full Recovery?"

Non. Mais c'est une bonne question. Cette forte intrication entre le Log et CDC implique quelques avertissements. Lorsque l'on est en Simple ou en Bulk, un mode de log plus verbeux est employé pour les opérations concernées par le CDC.
De plus le CDC empêchent de vider le Log tant que des informations concernées par la capture n'ont pas été lues. Ce qui désactive le SHRINK et donc l'AutoShrink et peut faire grossir des Logs en Simple.

"Et les performances dans tout ça?"

Alors forcément il y a un impact. Le nier dénoterait un tempérament marchand de tapis que je récuse :) Il faut absolument passer par le paramétrage de certains aspects du CDC

- Choisir les bons attributs: pas toutes les colonnes!
- Tuner les jobs de capture: Jouer avec le paramètre maxtrans pour déterminer le nombre de tran examinées par scan, ainsi qu'avec le nombre de scans du log avant une pause avec maxscans et la durée de ces pauses avec pollinginterval.

et beaucoup d'autres conseils et arguments exposés dans cet excellent document publié sur MSDN, le guide du tuning du Change Data Capture du MTE allemand Steffen Krause.

Conclusion?

Potentiellement, le CDC révolutionne le DataWarehousing en économisant du temps d'ETL (généralement 70-80% du projet BI). Ce temps gagné ne l'est pas en vain: toute économie sur des tâches techniques et redondantes permet de se consacrer plus profondément à la problématique métier, et c'est ce qui prime dans cette activité, plus encore qu'ailleurs, non?

A bientôt!