Faites le tour des nouveautés des technologies Data de Microsoft en une présentation

—disclaimer: ceci est un post business lié à AZEO. Sorry for inconvenience —

AzeoL’été sera chaud ! En effet, c’est une pluie d’annonces qui est tombée ces derniers mois.

Entre la conférence /Build, la PASS Business Analytics Conference (PASS BAC) et la conférence Ignite, Microsoft a dévoilé un nombre important d’évolutions, de nouveaux produits ou de nouvelles fonctionnalités.

Alors forcément, pour un consultant (et un MVP) comme moi, ça se traduit par une montagne de travail afin d’ingérer tout cela, tester, me faire une opinion, voire comment ça se combine, etc. Bref, intégrer tout cela dans ma boîte à outils de Data Architect.

Et si pour moi c’est du boulot, j’imagine que c’est pareil pour vous. Surtout quand vous n’avez pas de temps pour de la veille technologique. Comme c’est le cas chez de nombreux clients.

J’ai commencé à délivrer quelques démos ciblées avec mon équipe (merci Ratana pour Datazen, Fabien pour AzureML et Galla pour Power BI) mais je comprends les demandes de voir « tout d’un coup ».

C’est pourquoi je prépare une présentation assez large sur les nouveautés de l’offre Data de Microsoft pour les aider à y voir plus clair.

Le format est une demi-journée pour survoler tout. La prochaine est planifiée le :

Vendredi 3 juillet – 14h – 17h30

 

Au programme de cette session :

  • Nouveautés Power BI
  • Nouveautés Excel 2016
  • Datazen, la solution de dasboarding On-Prem, Mobile-First
  • Nouveautés Azure
    • Azure SQL v12
    • Azure Data Lake & Azure Datawarehouse
    • Big Data / Hadoop
    • Azure Machine Learning
    • Azure Data Factory
    • Azure Stream Analytics pour le temps réel
  • Nouveautés SQL Server 2016
    • Stretch Tables
    • Operational Analytics in-memory
    • Polybase
    • Analytics avec R
    • SSRS 2016
    • SSIS 2016
    • SSAS 2016
    • Nouveautés MDS & DQS

Un agenda bien rempli… VOus vous doutez bien que ce n’est qu’un survol. Cette session peut être plus courte ou plus ciblée au besoin.

Si vous êtes intéressé, prenez juste contact avec moi (jean-pierre.riehl@azeo.com) et nous verrons si on peut vous inclure lors de l’une de nos présentations planifiées ou si l’on vient vous voir directement.

SQL Server 2016 (rapide) What’s New ?

imageBon, en cette semaine très chargée (Marco Russo à Paris, Sélection des speakers du SQLSaturday Paris, Apéro Data, SharePoint Saturday, …), je vais quand même poster une petite news sur SQL Server 2016 !

 

Et quoi de mieux que de vous renvoyer vers les What’s New officiels ?

 

SQL Server 2016 Release Notes

https://msdn.microsoft.com/en-us/library/dn876712(v=sql.130).aspx

What’s New in Analysis Services

https://msdn.microsoft.com/en-us/library/bb522628(v=sql.130).aspx

What’s New in Reporting Services

https://msdn.microsoft.com/en-us/library/ms170438(v=sql.130).aspx

What’s New in Integration Services

https://msdn.microsoft.com/en-us/library/bb522534(v=sql.130).aspx

imageIl n’y a pas encore tout en effet. Quid de Power Query dans SSIS par exemple… Pour cela, il faudra attendre la sortie de la nouvelle version de SSDT-BI. Quand ? Hummm…bientôt ?

 

Pour une vision (quasi) complète des fonctionnalités de SQL Server 2016, consultez la Product Sheet, très bien faite :

Read the SQL Server 2016 datasheet

Je rempile au GUSS

Je sais. Cet article arrive en retard par rapport à l’actualité qu’il relate, mais le fond reste un sujet présent.

guss-logo-blanc-simpleTous les ans a lieu l’exercice démocratique qui décide de l’équipe qui dirigera le GUSS. Les élections ont eu lieu entre le fin février-début mars.

Peu de votants, très peu, trop peu.

Je suis très satisfait de l’engagement de la communauté Data française, de ce que nous avons construit ces 4 dernières années (et de ce que nous allons construire les 4 prochaines). Mais fait est que sur le sujet des élections, c’est un constat d’échec.

Alors, oui il y a des votants et des résultats qui font de cette association une structure démocratique. Mais je regrette qu’il n’y ait pas plus de retours de votre part, qu’ils soient positifs ou négatifs.

Nous sommes 9, vous êtes plus de 1500…

Quid si on décidait de ne pas reconduire les JSS ? Quid si on décidait vendre vos emails à des sociétés de phoning ? Je jette des pavés dans la mare (même si je tiens à vous rassurer sur ces points, ce n’est pas au programme) mais c’est pour souligner l’importance de vous intéresser au Board du GUSS et aux idées de ceux qui le composent. Car c’est vous qui devez façonner la communauté.

Le Board, notre organisation, c’est pour être autre chose qu’une simple bande d’experts délivrant du contenu pour Microsoft. Sinon, nous le ferions de façon plus individualiste, sans indépendance. Ce qu’on aime c’est grandir avec vous : Connect, Share, Learn.

Mais (r)assurez nous de le faire de la bonne façon, c’est à dire celle qui vous va le mieux.

En écrivant tout cela, je me dis que c’est peut-être vain, que vous faites confiance, que vous vous laissez porter. Après tout, vaille que vaille, ça ne changerait pas beaucoup votre quotidien une vie sans le GUSS. Je ne suis pas fataliste, au contraire. Quand je vois 3 nouvelles postulantes au Board, je me dis que c’est un excellent message.

Le renouvellement, on en a besoin. Je le rappelle sans cesse, nous faisons cela à titre gratuit et sur notre temps personnel (même si je remercie la souplesse d’AZEO à certains moments). Il n’y a pas de promotion à attendre, c’est un don, pas un investissement. En conséquence, quand des changements dans notre vie/carrière surviennent, on a moins de temps ou d’énergie à consacrer à la communauté et donc autant céder sa place à d’autres qui sont dans un momentum où ils en ont à revendre. C’est la pérennité de cette communauté qui est en jeu.

C’est ainsi que Florian et Alexandre quittent le Board et que Galla, Isabelle et Sarah le rejoignent.

-2 +3, ça ne tombe pas juste ?

Philippe, par la dure loi des votes (à 1 près Mme Chombier !), ne fait pas partie des 9 élus Triste. Qu’a cela ne tienne, son énergie et sa motivation restent à nos côtés, notamment sur la rédaction du Livre Blanc et l’organisation de prochain SQLSaturday.

Et moi dans tout cela ?

Eh bien je suis heureux de rester à bord et d’apporter mes idées et mes compétences. Je vais oeuvrer pour que ce qui est en place continue (webcasts, afterworks, SQLSaturday, JSS) et s’améliore encore. Je vais aussi travailler à mettre en place plus de choses et monter le niveau pour enrichir d’autant plus cette belle communauté : la #SQLFamily.

Allez, fini la séquence « Divan« , c’est parti pour la suite : rameuter des speakers et des sponsors pour le SQLSaturday et préparer la venue de Marco Russo.

International Speaker – Saison 2

BIconnectionA l’instar de mon ami Florian qui rentre du SQL Rally Nordic, je signe à nouveau pour une série de sessions internationales.

Après l’Angleterre et l’Allemagne l’an dernier, j’ai décidé de continuer dans cette « carrière » de speaker international. En premier lieu parce que c’est sympa de retrouver tous nos confrères internationaux et amis (#SQLFamily) lors de ces événements très conviviaux. Mais comme j’ai pu le dire de nombreuses fois ici, j’aime aller à ces événements pour partager et confronter mon point de vue avec les meilleurs (Intelligence is relative, méditez là dessus) et ainsi avoir une meilleure lecture (et donc une meilleure implémentation) des usages et technologies.

Cette année, j’ai la chance de pouvoir speaker au Portugal et en Ecosse.

 

SQLSaturday Lisbon 2015

imageCertainement l’un des plus important SQLSaturday en Europe depuis quelques années déjà. Peut-être même un des plus gros événements SQL du continent après le SQLBits.

Le SQLSaturday est piloté par Niko Neugebauer en personne. Pour ceux qui ne le connaisse pas c’est certainement le meilleur représentant de ce qu’est la communauté en Europe, la #SQLFamily – et accessoirement l’un des meilleurs experts au monde sur Columstore Index .

Inscrivez-vous !

Samedi 16 mai 2015

Lisbonne

Comme l’an passé, j’ai proposé à quelques confrères de m’accompagner. Florian présentera une session sur Azure Machine Learning mais je motive d’autres frenchies à venir en simples visiteurs.

Pour la part je présenterai une session technique sur la Data Management Gateway (cf. plus loin).

L’événement attire des speakers du monde entier (dont la France comme vous avez pu le constater) et sera 100% en anglais pour renforcer son côté international. C’est donc une conférence de très haut niveau et gratuite qui est à votre portée. En plus, avec le pont de l’ascension, ça vous donne l’occasion de vous évader un peu et de visiter Lisbonne qui est, selon Niko, la plus belle ville d’Europe…après Paris indeed.

Alors vous êtes avec nous le 16 mai prochain ?

SQLSaturday Edinburgh 2015 – BI Edition

imageCe SQLSaturday, un peu moins couru, se focalise sur la BI depuis l’année dernière. Il est organisé par Jen Stirrup. Je ne présente pas Jen, figure emblématique de la communauté Data (Microsoft) dans le monde, digne représentante de l’Europe dans les instances dirigeantes du PASS, speaker au 2 derniers SQLSaturday Paris et pour finir militante pour les Women In IT.

Inscrivez-vous !

Samedi 13 juin 2015

Edimbourg

Cette année, le SQLSaturday Edinburgh mélange les audiences connexes à la BI en y incluant Excel, CRM, Access, C#, etc. Je ne peux qu’approuver à double-titre. C’est d’abord enrichissant d’aller au-delà de notre base habituelle, à l’instar de ce que nous faisons au GUSS en collaborant avec les communautés SharePoint, Azure ou Agile. Et puis avec Power BI et le développement de la Self-Service BI, il est naturel d’accueillir largement les professionnels Excel ou VBA (et enfin les initier aux mindsets Data Clignement d'œil).

Je présenterai à Edinburgh la même session qu’au SQLSaturday Lisbon sur la Data Management Gateway.

PS :Pour ceux qui connaissent l’un de mes hobby, vous ferez vite le lien sur le choix du pays

Et ma session alors ?

Data Management Gateway Deep Dive.

Data Management Gateway (and also AS Connector) is what make modern Microsoft BI stack hybrid. Power BI and Azure Data Factory use that component to interact with On-Prem Data assets.
That session is a Deep dive into the DMG and the hybrid architecture involved by Power BI and ADF. How does it work ? Security, Firewall, Certificates, Multiple gateways, Admin delegation, Scale out, Disaster Recovery…. All that topics will be covered during that technical session.

L’idée est de voir en détail ce composant discret mais indispensable pour mettre en oeuvre Power BI à l’échelle d’une équipe ou de l’entreprise. Comment l’implémenter et dérouler des scénarios exotiques (et pourtant la réalité du terrain)…

Et après?

Alors c’est tout ce que j’ai prévu pour cette tournée européenne 2015. Je regrette de ne pouvoir aller chez nos copains allemands (Oliver, Tillman et Kostja) car ça tombe en même temps que Édimbourg. Je regrette que le SQLSaturday Dublin ait été annulé. Et enfin, je serai bien allé à l’un des événements nordiques (SQL Rally ou SQLSaturday Oslo ou SQLSaturday Copenhaguen).

Mais il ne faut pas avoir les yeux plus gros que le ventre et se concentrer également sur les événements en France. Il y a le SQLSaturday Paris en septembre et plus proche la table ronde avec Marco Russo le 28 mai et une surprise annoncée bientôt.

imageD’autant plus que je fais ce petit RoadTrip sur mes fonds personnels et que ce n’est pas donné.

Ce qui ne m’a pas empêché enfin de postuler pour être speaker au prochain PASS Summit à Seattle en octobre.

10 requêtes MDX utiles (reloaded)

Disclaimer : j’ai écrit cet article en 2010 et il était hébergé sur le site de mon ancienne boite, Bewise. Malheureusement, le site de Bewise n’existe plus et comme on me demande encore cet article, je le reposte ici.

 

Introduction

Dans cet article, nous allons étudier quelques cas simples nécessitant d’avoir recours à une requête MDX. Il est destiné aux développeurs décisionnels qui pourront s’en aider pour créer des membres calculés ou concevoir des rapports plus complexes par exemple.
Vous devez connaître le MDX pour cet article. Le langage MDX est le langage de requêtage des bases de données multidimensionnelles (les cubes pour faire simple). Il est utilisé par SQL Server Analysis Services (et dans sa forme « standard » par Hyperion). Le but de cet article n’est pas de vous apprendre le MDX. L’apprentissage de ce langage prend du temps car il est assez complexe (on doit penser en n-dimensions).
J’irai donc à l’essentiel en proposant des « uses cases » concrets et opérationnels.

 

Quelques rappels MDX

Juste quelques rappels pour la bonne lecture de cet article :

A la base, un SELECT

La base d’une requête MDX est une instruction SELECT. Il est bien plus complexe qu’en SQL mais nous allons essayer de le formater toujours de cette façon :

SELECT
{<Liste de mesures>} on 0,
<dimension1> * <dimension2> * <dimension-n> on 1
FROM [Cube]
WHERE { <filtre> }

 

Set et tuple

Un Set pointe un ou plusieurs membres de dimension. Sa notation utilise les accolades {}.
Ex : { [Date].&[1234] } ou { [Géo].[Pays].[France] , [Géo].[Pays].[Allemagne] ]

Un tuple est le croisement de dimensions et d’une mesure. On peut l’assimiler à une cellule du cube. Sa notation utilise les parenthèses ().
Ex : ([Date].&[1234], [Measures].[Qté Vendue])

 

Membre calculé

Pour nos requêtes nous aurons besoin de membres calculés. Evidemment, nous n’allons pas les créer dans le cube mais les injecter dans notre session.
Pour cela, il faut utiliser la syntaxe suivante :

WITH MEMBER [xxx] AS <MDX statement>
SELECT …

 

La base de données d’exemple

Je me base sur la base Adventure Works que vous pouvez trouver sur CodePlex. Il existe un DatawareHouse (AdventureWorks2008DW) et un projet Analysis Services qui vous permettra de déployer le cube.
Ce projet est complet mais complexe car il fait intervenir toutes les notions et subtilité de SSAS. Sans rentrer dans le détail, il vous permettra d’avoir un cube à disposition sans avoir à maîtriser toutes ces notions.
Les téléchargements sont à cette adresse : http://sqlserversamples.codeplex.com/#ssas

 

Les requêtes

PROPERTIES()

Utilisation :

Properties() est sans doute la fonction MDX que j’utilise le plus souvent avec CurrentMember ; principalement parce que l’un ne va pas sans l’autre. Properties permet de récupérer une propriété sur un membre de dimension.

On distingue les propriétés natives (dites intrinsèques), inhérentes à tout membre comme son nom unique, sa valeur, etc. mais aussi les propriétés définies à la construction du cube. On peut ainsi récupérer un autre attribut ou une valeur liée au membre.

clip_image002

Exemple :

Dans le premier exemple, on récupère l’attribut Day Of Year pour lier le n° du jour dans l’année à la date remontée sans faire de croisement de dimension.

WITH MEMBER JourDansAnnee AS
[Date].[Calendar].CurrentMember.Properties("Day Of Year")
SELECT
{[JourDansAnnee], [Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].AllMembers ON 1
FROM [Adventure Works]

Dans le second exemple, on récupère une propriété intrinsèque qui est la valeur du membre, fortement typée ; ici, on ramène la date.

WITH MEMBER [DateTypée] AS
[Date].[Calendar].CurrentMember.Properties("Member_Value", TYPED)
SELECT
{[DateTypée], [Measures].[Internet Sales Amount]} ON 0,
[Date].[Calendar].AllMembers ON 1
FROM [Adventure Works]

Evidemment pour ce second exemple, nous aurions pu écrire :

[Date].[Calendar].CurrentMember.MEMBER_VALUE 

L’usage le plus fréquent que j’en fais est dans la construction d’un paramètre dans Report Builder. En effet, il faut non par récupérer le label du membre mais son nom unique pour l’injecter dans la requête MDX. J’utilise donc la propriété MEMBER_UNIQUE_NAME de cette façon :

WITH
MEMBER [ParameterCaption] AS [Date].[Calendar].CurrentMember.NAME
MEMBER [ParameterUniqueName] AS [Date].[Calendar].CurrentMember.UNIQUENAME
SELECT NON EMPTY {[ParameterCaption],[ParameterUniqueName]} ON 0,
[Date].[Calendar].AllMembers ON 1
FROM [Adventure Works]

 

Note : On a toujours tendance à naturellement tout définir comme attribut d’une dimension, pensez que si vous avez uniquement besoin d’une valeur pour de l’affichage, configurez là comme une propriété (cf. l’article suivant : http://blog.djeepy1.net/2010/05/21/tuning-des-attributs-de-dimension-dans-analysis-services/)

 

PARALLELPERIOD

Utilisation :

La fonction ParallelPeriod permet de récupérer un Set particulier dans une dimension temps. Plus précisément, ParallelPeriod permet de récupérer les membres équivalents à un Set donné mais sur une période antérieure ou future. Par exemple, si je suis sur janvier et février 2008, je peux récupérer janvier et février sur une autre année.

Cette fonction est spécifique aux dimensions temporelles qu’Analysis Services traitent de façon spéciale. Elle fonctionne sur les hiérarchies définies par l’utilisateur.

On lui passe les arguments suivants :

PARALLELPERIOD( Niveau, Nb de période à remonter, Set)

  • Le niveau dans la hiérarchie, l’année par exemple
  • Le nombre de saut à faire sur ce niveau (chiffre négatif pour aller dans le futur)
  • Les éléments que l’on souhaite transpose

Exemple :

Dans cet exemple, on récupère le même jour mais sur le trimestre précédent (1er avril à 1er janvier) :

SELECT
NON EMPTY {} ON 0,
PARALLELPERIOD([Date].[Calendar].[Calendar Quarter], 
   1, [Date].[Calendar].[Date].&[20070401]) on 1
FROM [Adventure Works]

Dans cet autre exemple, on récupère le tuple correspondant au même jour l’année précédente :

WITH MEMBER [N-1] AS
( 
    PARALLELPERIOD([Date].[Calendar].[Calendar Year], 
    1, [Date].[Calendar].CurrentMember), [Measures].[Sales Amount]
)
SELECT
{[Measures].[Sales Amount], [N-1]} ON 0,
[Date].[Calendar].[Date].AllMembers ON 1
FROM [Adventure Works]

Cet exemple est le plus utilisé pour faire des calculs de progression sur des périodes ou des KPI.

Dans le même genre :

Quand on traite avec d’autres dimensions qui ne sont pas temporelles, on peut utiliser la fonction Cousin qui permet de naviguer de la même façon dans une hiérarchie.

 

YTD et PERIODSTODATE

Utilisation :

YTD est l’acronyme de Year To Date. Encore une fois, cette fonction est spécifique aux dimensions de temps.

Cette fonction a pour but de ramener un Set contenant l’ensemble des membres sur un niveau d’une hiérarchie jusqu’à un certain membre en commençant par le premier membre de l’année. Par exemple, elle ramènera l’ensemble des dates entre le 1er janvier et une date donnée.

On utilise principalement cette fonction pour faire des cumuls ; et souvent en conjonction avec ParallelPeriod pour faire des comparaisons date à date (comme un chiffre d’affaire à date et son N-1).

PeriodsToDate est la version générique de cette fonction qui permet de la généraliser à toute échelle de temps.

Exemple :

Dans cet exemple, on ramène l’ensemble des dates entre le 1er janvier et le 8 mai 2006

SELECT
NON EMPTY {} ON 0,
YTD([Date].[Calendar].[Date].&[20060508]) ON 1
FROM [Adventure Works]

Dans cet autre exemple, on calcule le cumul des ventes sur les semaines de la dimension :

WITH MEMBER [SalesYTD] AS
AGGREGATE(
   YTD([Date].[Calendar Weeks].CurrentMember),
   [Measures].[Sales Amount]
)
SELECT {[Measures].[Sales Amount], [SalesYTD]} ON 0,
[Date].[Calendar Weeks].[Calendar Week].AllMembers ON 1
FROM [Adventure Works]

 

Dans le même genre :

On trouve aussi les fonctions MTD et QTD respectivement pour Month To Date et Quarter To Date qui, vous l’aurez compris font la même chose sur un mois ou un trimestre.

 

TAIL et HEAD

Utilisation :

Ces fonctions permettent de récupérer les n-premiers ou les n-derniers éléments d’un Set. On peut l’utiliser pour récupérer les 6 derniers mois par exemple.

Exemple :

SELECT
NON EMPTY {} ON 0,
TAIL([Date].[Calendar].[Date].Members, 6) ON 1
FROM [Adventure Works]

Dans le même genre :

On trouve tout un wagon de fonctions permettant de manipuler les ensembles de membres. On peut citer Subset, Lag, PrevMember, ou Filter que nous verrons plus loin.

 

ORDER

Utilisation :

Comme son nom l’indique, cette fonction permet d’ordonner un ensemble de membres. On peut ordonner par une des propriétés de l’attribut comme sa valeur ou bien par des valeurs du cube.

On utilise souvent cette fonction en combinaison avec Head ou Tail pour construire des Set dynamiques comme le Top 10 des clients.

Attention quand on travaille avec des membres dans une hiérarchie, l’ordre respectera le placement des membres dans celle-ci. Il faut préciser le paramètre BASC ou BDESC pour s’en affranchir.

Exemple :

Dans cet exemple, on renvoie les ventes par date décroissante

SELECT
{[Measures].[Internet Sales Amount]} on 0,
ORDER([Date].[Calendar].AllMembers, [Date].[Calendar].CurrentMember.Value, BDESC) on 1
FROM [Adventure Works]

Dans cet autre exemple, on retourne la liste des promotions par chiffre d’affaires réalisé en 2008.

SELECT
{[Measures].[Sales Amount]} on 0,
ORDER([Promotion].[Promotion].AllMembers, [Measures].[Sales Amount], DESC) on 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2008]

Dans le même genre :

On peut citer HIERARCHIZE qui permet d’ordonner des membres en respectant une hiérarchie.

 

UNKNOWNMEMBER

Utilisation :

L’UnkownMember, ou membre inconnu, est utilisé pour gérer les valeurs NULL du DatawareHouse (l’absence de valeur d’un point de vue métier). Par exemple, si des lignes de vente n’ont pas de produit associé (ProductKey = NULL), elles seront reliées aux « Produits inconnus » qui sera ce membre technique de la dimension produit.

clip_image004

Ce membre un peu particulier reste un membre à part entière des dimensions qui remonte dans les requêtes (et qui peut être perturbant ou fausser des chiffres).

Exemple :

Dans cet exemple, on récupère explicitement les ventes associées à aucun produit

SELECT
{[Measures].[Sales Amount]} ON 0,
[Product].[Product].UNKNOWNMEMBER ON 1
FROM [Adventure Works]

Dans cet autre exemple, on souhaite exclure les ventes non affectées par exclusion de l’UnknownMember du Set sur l’axe 1.

SELECT
{[Measures].[Sales Amount]} ON 0,
{[Product].[Product].AllMembers - [Product].[Product].UNKNOWNMEMBER} ON 1
FROM [Adventure Works]

 

Dans le même genre :

Il n’existe pas de raccourci pour obtenir le membre racine (aussi appelé « All Member » – à ne pas confondre avec la fonction AllMembers). On utilisera une autre expression qui récupère le premier élément de la dimension :

SELECT
{[Measures].[Sales Amount]} ON 0,
[Product].[Product].Members.Item(0) ON 1
FROM [Adventure Works]

STRTOSET

Utilisation :

Cette fonction permet de convertir une chaîne de caractère en un Set utilisable dans une requête. On l’utilise notamment pour injecter des paramètres dans une requête.

Attention à ne pas abuser de cette fonction. Il m’est arrivé de voir tout un jeu de membres calculés dans une solution basés sur des conversions de chaînes, ce qui dégrade les performances.

Exemple :

Dans cet exemple, on injecte dynamiquement le n° du mois dans la requête. La fonction STRTOSET transformera la chaîne de caractère au membre correspondant dans la dimension date.

SELECT
{[Measures].[Sales Amount]} ON 0,
[Sales Territory].[Sales Territory Country].Members ON 1
FROM [Adventure Works]
WHERE STRTOSET("[Date].[Calendar].[Month].&[2008]&[" + @NumMois + "]")

Dans le même genre :

Il existe le parfait inverse à STRTOSET qui transforme un ensemble de membre en chaîne de caractères : c’est SETTOSTR. Dans le même style on citera SETTOARRAY, STRTOMEMBER, TUPLETOSTR, etc.

 

FILTER

Utilisation :

Filter permet, comme son nom l’indique, de filtrer un ensemble de membre en fonction d’une expression.

Contrairement à ce que l’on peut imaginer, la clause WHERE en MDX permet de pointer un sous-cube ou un slice dans le cube, c’est pourquoi elle n’est pas de la forme Dimension = Valeur mais attend un Set. En revanche, la fonction FILTER fonctionne quasiment comme la clause WHERE en SQL.

On lui passe les arguments suivants :

FILTER( Set de base, Condition)

  • Le Set de base est l’ensemble de membres sur lequel s’effectue la recherche ; on prend souvent AllMembers.
  • La condition, une expression qui doit renvoyer vrai ou faux. La condition peut porter sur une mesure du cube ou bien sur une propriété de dimension

Exemple :

Dans ce premier exemple, on récupère les ventes pour un produit donné. On utilise souvent ce type de requête de façon paramétrée avec des dimensions à forte cardinalité (une référence par exemple) :

SELECT
{[Measures].[Sales Amount]} ON 0,
FILTER(
   [Product].[Product].Members,
   [Product].[Product].CurrentMember.Name = "Casque" ) ON 1
FROM [Adventure Works]

Dans cet autre exemple, on filtre sur la valeur d’une mesure, ici les revendeurs avec un CA de plus de 100 000$ :

SELECT
{[Measures].[Reseller Sales Amount]} ON 0,
FILTER(
    [Reseller].[Reseller].Members,
    [Measures].[Reseller Sales Amount] > 100000) ON 1
FROM [Adventure Works]

Dans le même genre :

Dans le cas où l’on filtre sur une mesure, on peut privilégier l’utilisation de fonctions comme TOPCOUNT, TOPSUM ou TOPPERCENT pour ramener la fourchette haute d’un Set (cf. aussi les fonctions TAIL/HEAD présentées ci-dessus).

 

AGGREGATE

Vous utilisez implicitement la fonction AGGREGATE en permanence sans le savoir. Le simple fait de requêter un tuple appelle la fonction d’agrégation.

Ecrire cette requête…

SELECT ([Measures].[Sales Amount]) ON 0
FROM [Adventure Works]

…implique qu’on agrège les ventes sur toutes les dimensions.

On peut la traduire par le tuple suivant :

SELECT
(
[Measures].[Sales Amount],
[Date].[Calendar].[All Periods],
[Product].[Product Categories].[All Products],
...
) ON 0
FROM [Adventure Works]

On peut remplacer un membre par un Set mais tout change quand on passe le tuple formé en tant que membre calculé. On obtient l’erreur suivante : « la fonction attend une expression de chaîne ou numérique pour l’argument. Une expression d’ensemble de tuples a été utilisée. »

WITH MEMBER [Ventes] AS
(
 {
  [Date].[Calendar].[Calendar Year].&[2005],
  [Date].[Calendar].[Calendar Year].&[2006]
 },
 [Measures].[Sales Amount]
)
SELECT {[Ventes]} ON 0
FROM [Adventure Works]

 

clip_image006

Dans un membre calculé, il faut préciser explicitement l’agrégation via la fonction AGGREGATE qui utilisera la fonction d’agrégation définie pour la mesure dans le cube.

WITH MEMBER [Ventes] AS
AGGREGATE(
 {
   [Date].[Calendar].[Calendar Year].&[2005],
   [Date].[Calendar].[Calendar Year].&[2006]
 },
 [Measures].[Sales Amount]
)
SELECT {[Ventes]} ON 0
FROM [Adventure Works]

 

Dans le même genre :

AGGREGATE s’appuie sur ce qui est défini comme fonction d’agrégation dans le cube. Mais on peut vouloir forcer une somme avec SUM, une moyenne avec AVG (c’est d’ailleurs le seul moyen de faire une moyenne, via un membre calculé) ou un comptage avec COUNT. Evidemment, il en existe bien d’autres.

 

EXISTS et NONEMPTY

Utilisation :

Un besoin fréquent est de récupérer les membres d’une dimension pour lesquels il existe des valeurs. C’est souvent le cas quand on veut remplir un paramètre (dans Report Builder par exemple) en limitant les valeurs à un certain contexte.

Il y a de nombreuses façons de le faire dont certaines ont été évoquées ici mais les fonctions EXISTS et NONEMPTY remplissent aussi ce rôle et surtout seront utilisables dans un membre calculé.

Exemple :

Dans cet exemple, on récupère la liste des dates pour lesquelles une vente particulière existe (ici un produit en particulier).

SELECT {} ON 0,
EXISTS(
[Date].[Date].Members,[Product].[Product Categories].[Product].&[348],
"Internet Sales"
) ON 1
FROM [Adventure Works]

 

On retrouve le même résultat avec NONEMPTY.

SELECT
NONEMPTY(
[Date].[Date].Members,
([Measures].[Sales Amount],[Product].[Product Categories].[Product].&[348])
) ON 0
FROM [Adventure Works]

 

Enfin, on peut aussi « slicer » le cube avec une clause WHERE mais là on exclut la possibilité de créer un membre calculé directement dans le cube.

SELECT {} ON 0,
{[Date].[Date].Members} ON 1
FROM [Adventure Works]
WHERE {[Product].[Product Categories].[Product].&[348]}

 

 

Conclusion

En passant en revue des fonctions MDX, nous avons pu voir de nombreuses requêtes utiles pour explorer un cube ou créer des membres calculés. Evidemment, la spécificité des métiers à analyser fait qu’il est difficile d’avoir un jeu précis de requêtes. Néanmoins, une bonne connaissance du catalogue de fonctions MDX permet de maitriser la navigation dans les dimensions et les mesures.

Dans la réalité, on s’appuie bien évidemment en premier lieu sur les designers de requête comme celui de Report Builder qui inclut la gestion des paramètres. Mais il est de temps en temps nécessaire de rentrer dans le code MDX généré pour l’optimiser, l’arranger ou le simplifier.

La notion de performance n’a pas été abordée dans cet article. En effet, il y a souvent de nombreuses façons d’écrire les requêtes MDX et une des conséquences peut être les performances meilleures ou moins bonnes avec l’une ou l’autre écriture.

Mais notez bien que, comme pour le moteur SQL, il y a une phase de parsing et d’optimisation qui conduisent souvent à une écriture commune et optimisée par le moteur OLAP lui-même. C’est pourquoi il faut toujours prendre en compte l’aspect lisibilité et maintenabilité d’une requête ; déjà que ce langage est complexe.

Liaison directe entre Power Query et Power Pivot avec Excel 2010

Disclaimer : cet article a été écrit par Fabien Adato de mon équipe chez AZEO. Merci à lui

Il est assez rare de travailler chez des clients possédant déjà des licences Excel 2013 voir Office 365 + Power BI. Or, il faut savoir que Power Query et Power Pivot sont aussi compatibles et fonctionnent plutôt bien avec Excel 2010 en 32 et 64 bit.

Seulement comparé à 2013, le chargement des données d’une requête Power Query vers une table Power Pivot n’est pas aussi évidant avec 2010, en effet le bouton « charger dans le modèle de données » n’existe pas !

image

Cela oblige à passer par une feuille Excel classique…limitée à 1 million de lignes.

 

Mais il est possible de charger les données des requêtes directement dans Power Pivot sans passer par un chargement des données dans un onglet. Pour cela, une fois que la requête à charger est créée, il faut :

  1. Décocher le chargement dans la feuille de calcul (non obligatoire mais conseillé pour réduire la taille du fichier Excel)
  2. Ouvrir la fenêtre Power Pivot
    clip_image002
  3. Aller dans l’onglet « Conception » puis cliquez sur « Connexions existantes »
    clip_image004
  4. Sélectionner la requête Power Query qui apparait dans liste « Connexions au classeur » et cliquer sur ouvrir
    clip_image006
  5. Et ensuite le process est le même que pour un ajout de table Power Pivot classique
    Suivant -> Suivant –> Terminer
    clip_image008
    A la fin de l’importation des données, cliquer sur « Fermer »

 

La table apparait alors dans l’interface.

 

Mon test a été effectué avec les versions :

  • Excel 2010 Professionnel Plus 14.0.7128.5000 64 bits
  • Power Query v2.11
  • Power Pivot version 11.0.3000.0

2 717 607 lignes ont été chargées sans problème (soit bien au-dessus de la limitation de 1 million de lignes d’une feuille Excel).

 

Par contre pour rafraichir les données, il faut utiliser le bouton « Actualiser » de la fenêtre Power Pivot :

clip_image010

 

Note : La modification des requêtes source Power Query (modification du code M) entrainent une erreur lors du rafraichissement de la table.

“OLE DB or ODBC error: The query ‘Requete’ or one of its inputs was modified in Power Query after this connection was added. Please remove and re-add the connection. This can be done by disabling and re-enabling download of ‘Requete’ in Power Query..”

 

Pour corriger ce problème :

Marco Russo à Paris pour un Workshop DAX

image

Même si je suis très heureux que mon équipe et moi formions nos clients à Power Pivot depuis 2012, quand Marco débarque à Paris pour donner en personne un de ses workshops, je m’incline devant « Il Maestro« .

 

Mastering DAX Workshop

avec Marco Russo

27 au 29 mai – Paris

Et quand Marco demande si la communauté française peut lui donner un coup de pouce pour l’organisation, c’est évidemment avec un grand OUI que nous y répondons favorablement.

Pourquoi ? Vous le demandez ? Parce qu’il est l’un des meilleurs experts DAX et Power Pivot au monde (pour ne pas dire le meilleur avec son compère Alberto) et parce qu’il incarne parfaitement l’esprit communautaire qui anime le GUSS.

Et plus pragmatiquement parce qu’il offre une réduction de 10% aux membres du GUSS et qu’il a souhaité organiser un afterwork gratuit où vous pourrez lui poser toutes vos questions.

 

Inscrivez-vous en profitant de 10% de réduction

image

Alors c’est quoi ce coup de main ?

Hormis un peu de relaying sur nos canaux, nous aidons Marco dans sa logistique car contrairement à ce qu’on pourrait penser, la ville la plus visitée du monde a du mal à parler anglais…

Aussi, le monde de la formation en France est un marché extrêmement subventionné et nombre sont d’entre vous qui ne peuvent accéder à une formation si elle ne rentre pas dans un plan de formation d’entreprise.

Le GUSS est organisme de formation et peut donc faire l’intermédiaire entre votre service RH et Marco.

Un bon retour sur investissement

imageComme je le disais plus haut, nous sommes en train d’organiser un événement communautaire avec comme Guest Star Marco en marge de son Workshop.

Le format sera à mi-chemin d’un afterwork et d’une table ronde.

  • Afterwork pour le côté convivial d’un échange autour d’un verre.
  • Table ronde car l’idée est de profiter des connaissances de Marco pour débattre et apprendre sur la modélisation tabulaire et les patterns DAX.

Tabular Modeling Rountable

avec Marco Russo

jeudi 28 mai – 19h00Paris

Rien n’est encore calé mais il est fort probable que cet événement soit limitée en nombre de personne donc suivez de près l’actualité du GUSS (le mieux étant de suivre @GUSS_FRANCE sur Twitter).

Et pour finir cet article…

A titre personnel c’est un grand honneur de collaborer avec Marco depuis quelques temps déjà. C’est quelqu’un de talentueux et d’éclairé, on peut l’appeler Monsieur! C’est donc avec plaisir que j’ai pris cette tâche (avec Galla pour m’aider).

 

image