HAVING en MDX, une clause méconnue et pourtant performante

Le MDX a une clause HAVING. Je sais ça peut paraître une nouveauté pour certains – je ne l’ai pas appris immédiatement loin s’en faut alors qu’elle existe depuis 2005. La seule documentation réellement utilisable sur cette clause est un papier de William Pearson datant de 2007 dans lequel celui-ci détaille une équivalence fonctionnelle entre HAVING et Filter().

Dit autrement, HAVING peut – pour lui – se voir comme un Filter() appliqué sur un Axis, ces deux notations étant alors interchangeables

{
   Filter
   (
      [Dimension].[Level].Members,
      [Measures].[Measure] > 42
   )
} ON 0
{
   [Dimension].[Level].Members
}
HAVING [Measures].[Measure] > 42
ON 0

Si HAVING est effectivement utile pour réaliser des filtrages, elle présente un intérêt évident par rapport à un Filter(). Non qu’elle soit “block-computationable” , ce n’est pas le cas. En revanche elle s’exécute après l’auto-existence des axes et des NON EMPTY. Et ça ça peut être intéressant.

Prenons un exemple bidon: je souhaite afficher la liste des ventes par jour de plus de 10000$. Ecrivons d’abord la requête de base.

SELECT
{
   [Measures].[Internet Sales Amount]
} ON 0
,
NON EMPTY
{
   [Product].[Product].[Product] 
   * 
   [Date].[Date].[Date]
} ON 1
FROM [Adventure Works];

Cette requête MDX, passée au MDX Studio, s’exécute en 200ms et affiche un Cells Calculated de 23797 avec 1 NON EMPTY, et 1 Sonar Subcube. En gros on a appliqué NON EMPTY sur l’Axe, ce qui a renvoyé 23000 cellules, et généré une requête au Storage Engine.

Si on passe l’Axis 1 sous un Filter() pour les fameux 10000$, le Cells Calculated passe à… 869965, et le temps d’exécution explose. Normal, vous connaissez le fonctionnement du Filter: il a itéré sur toutes les combinaisons – produit cartésien oblige – des dates et des produits sans « Exister » avant le Set sur la mesure.

Time              : 2 sec 307 ms
Calc covers       : 0
Cells calculated  : 869965
Sonar subcubes    : 1
NON EMPTYs        : 1

Tout à fait logiquement, en appliquant un NonEmpty() sur le set avant d’appliquer le Filter, on retombe sur 23000 cellules, et on a cette fois ci 2 Sonar Subcubes et 2 NON EMPTY (Celui de la fonction NonEmpty(), et celui du NON EMPTY sur l’axe).

SELECT
{
   [Measures].[Internet Sales Amount]
} ON 0,
NON EMPTY
{
   Filter
   (
      NonEmpty
      (
         [Product].[Product].[Product]
         * [Date].[Date].[Date]
         ,[Measures].[Internet Sales Amount]
      )
      ,[Measures].[Internet Sales Amount]>10000
   )
} ON 1
FROM [Adventure Works]

Time : 135 ms
Calc covers : 0
Cells calculated : 23935
Sonar subcubes : 2
NON EMPTYs : 2

Alors quel est l’intérêt du HAVING? Il vous permet de filtrer l’axe après l’application de l’ordre NON EMPTY sur les Axis, et donc de vous éviter un NonEmpty (Exists) supplémentaire et inutile dans la clause Filter. Pas le truc de l’année, mais si ça permet d’économiser du temps de cerveau disponible…

SELECT
{
   [Measures].[Internet Sales Amount]
} ON 0
,NON EMPTY
{
   [Product].[Product].[Product] 
   * 
   [Date].[Date].[Date]
}
HAVING [Measures].[Internet Sales Amount] > 10000 
ON 1
FROM [Adventure Works];

HAVING peut donc se révéler très utile et rapide pour faire un filtrage (type Filter) post-application des NON EMPTY des axes, dans un rapport RS par exemple, et sans vouloir s’embêter à écrire soi-même la logique d’Exists/NonEmpty spécifique à ceux-ci.

A bientôt!

Techdays 2013, épilogue

TD13

Bonjour à tous, un petit peu crevé après cette préparation éreintante de session qui, d’après les premiers retours, vous a pas mal plu! Je vous rassure on s’est bien amusés avec le grand – autant par les New Rock que par le talent – Romu aussi. Sur un plan plus perso la préparation quelque peu tardive m’a empêché de venir aux sessions qui m’intéressaient mais avec Romuald nous avions des slides à finir à la force du poignet (oui je sais Aurélien ceci est une trèèès mauvaise blague, et sache que toute la salle était avec toi, bon rétablissement mon poulet!).

Merci en tous les cas aux présents, je pense à Pascale, Flo, David, Fab, Rom,  Dominique et j’en oublie, ainsi qu’à tous ceux qui sont venus nous faire leur retours à la sortie, ça fait chaud au coeur, vraiment!

Démos, Director’s Cut

Bon comme on a été un petit peu à la bourre je voulais revenir sur quelques démos histoire que les passages peu clairs le soient un peu moins. On va faire ça dans l’ordre, et ça concerne surtout la partie DaxMD, aussi connu sous le nom de PowerView for Multidimensional Models, CTP publique d’une version du moteur OLAP supportant le DAX, disponible ici. Au passage et ce depuis la session, David Joubert a posté un excellent test de la conversion de modèles tabulaires vers du MD qui fait un peu le même job que certaines de nos démos.

CSDL Metadata

On vous l’a dit, la raison pour laquelle on peut envoyer du DAX à un modèle multidimensionnel – qui permet à PowerView de s’y connecter – dans cette CTP tient en deux raisons

  • Un Formula Engine DAX côté multidimensionnel, capable d’interroger un SE MOLAP
  • Des métadonnées au format CSDL, le format normé de Microsoft introduit avec Entity Framework qui permet de décrire un modèle de données. Pour étendre le CSDL aux données d’analyse, Microsoft a publié une extension au CSDL nommée CSDL-BI.

Pour requêter les métadonnées CSDL, un Rowset XMLA (ou une DMV) existe. Il suffit de passer le nom de la base, du cube (oui car en multidimensionnel on peut avoir plusieurs cubes!) dans le champ… PERSPECTIVE_NAME (Oui bon c’est une CTP hein), et un champ non documenté, VERSION, qui dit que… qui dit 2.0. (Je vous ai déjà dit que c’est une CTP?) Voilà.

<Envelope xmlns=”http://schemas.xmlsoap.org/soap/envelope/”>
       <Body>
              <Discover xmlns=”urn:schemas-microsoft-com:xml-analysis”>
                     <RequestType>DISCOVER_CSDL_METADATA</RequestType>
                     <Restrictions>
                           <RestrictionList>
                                 <CATALOG_NAME>AdventureWorksDW2012Multidimensional-EE<CATALOG_NAME>
                                 <VERSION>2.0</VERSION>
                                 <PERSPECTIVE_NAME>Adventure Works</PERSPECTIVE_NAME>
                           </RestrictionList>
                     </Restrictions>
                     <Properties>
                           <PropertyList>
                                 <FORMAT>Tabular</FORMAT>
                           </PropertyList>
                     </Properties>
              </Discover>
       </Body>

PowerView comprend ça. Pas vous. C’est normal. Mais ça vous montre qu’on a dorénavant une couche de métadonnées dans SSAS (ce qui n’était pas le cas avant Tabular).

Drill-through en MDX, en DRILLTHROUGH ou en DAX?

Pour faire des extractions de détail en MDX, on a basiquement deux solutions. Faire des requêtes MDX bien barbares, ce qui est assez lent, mais pas compliqué à écrire (et au passage ce que génère Excel PivotTable ou SSRS au Designer).

SELECT
{
	[Measures].[Reseller Sales Amount]
}
ON 0
,
NON EMPTY
{
	[Date].[Date].[Date]
	*[Geography].[City].[City]
	*[Product].[Product].[Product]
	*[Reseller].[Reseller].[Reseller]
	*[Sales Territory].[Sales Territory Region].[Sales Territory Region]
}
DIMENSION PROPERTIES [Reseller].[Reseller].[Bank Name]
ON 1
FROM [Adventure Works]

Quand on est bon, on peut faire des requêtes de DRILLTHROUGH sur une cellule. Les vieux de la vieille du RS 2000 faisaient ça avec de l’OLE DB for OLAP dans SSRS (c’est faisable aussi avec le provider AS, mais en mode mining, éditeur coupé). Avantage: lent à la première exécution, mais monte bien en cache.

DRILLTHROUGH
SELECT
{
 [Measures].[Reseller Sales Amount]
}
ON 0
FROM [Adventure Works]
RETURN
 [$Date].[Date]
 ,[$Geography].[City]
 ,[$Product].[Product]
 ,[$Reseller].[Reseller]
 ,[$Sales Territory].[Sales Territory Region]
 ,[Reseller Sales].[Reseller Sales Amount]

Dans la session on vous a montré qu’on peut aussi faire ça en DAX. pour de l’extraction de détail, ça envoie sacrément (les temps de réponse sont en moyenne 10 fois meilleurs sur notre petite base d’exemple).

En plus cela semble constant au vu de nos tests contrairement aux autres modes d’accès.

Evaluate
(
	Summarize
	(
		'Reseller Sales'
		,'Date'[Date.Key0]
		,'Date'[Date]
		,'Geography'[City.Key1]
		,'Geography'[City.Key0]
		,'Product'[Product.Key0]
		,'Product'[Product]
		,'Reseller'[Reseller.Key0]
		,'Reseller'[Reseller]
		,'Reseller'[Bank Name]
		,'Sales Territory'[Sales Territory Region.Key0]
		,'Sales Territory'[Sales Territory Region]
		,"Sales Amount",'Reseller Sales'[Reseller Sales Amount]
	)
)
Order By
	'Date'[Date]
	,'Geography'[City.Key0]
	,'Product'[Product]
	,'Reseller'[Reseller]
	,'Reseller'[Bank Name]
	,'Sales Territory'[Sales Territory Region]

Vous noterez au passage les splendides Key0, Key1 dans la requête. Kézako? Et bien si vous vous y connaissez en member properties vous savez que les Key0…KeyN en MDX sont les KeyColumns d’un attribut. Pour développer DaxMD – de la même manière qu’il a du être difficile de faire rentrer le concept de DefaultMember dans un PowerPivot – cela n’a pas du être une mince affaire pour les équipes de Redmond de faire comprendre l’idée de clés composites et de NameColumn à du DAX . Moyennant quoi dès que la NameColumn est différente de la KeyColumn dans la Dimension, on doit spécifier cette dernière, sans quoi on prend un beau message d’erreur:

Column [Truc] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Bon à savoir…

Rapport de drill en SSRS

Pour la suite de la démo, Romu a montré l’inclusion de ce code dans un Rapport SSRS  pour lequel il faut utiliser le mode DMX pour taper la requête (comme dans ce vieux post sur le Drillthrough). Les paramètres quant à eux sont évidemment gérés (Syntaxe @Paramètre) et le sous rapport envoyé grâce à un JumpToReport sur le camemb… l’histogramme.

DAX DrillThrough Action dans SSAS

Enfin la cerise sur le gâteau concernait l’exécution de ce code dans une action de type Rowset côté AS. L’idée étant d’avoir un Drill en DAX sur un cube. Une action de Rowset a une expression de type chaîne de caractère qui va être évaluée dans un bloc XMLA <Command>… qui contient normalement du MDX. Mais dans DaxMD, les Command peuvent être en DAX (oui c’est le principe), donc on peut y mettre la requête DAX précédente.

Problème: comme pour le DrillThrough on Calculated Members de Mosha on doit contextualiser la cellule (si je clique sur une cellule, la requête DAX doit être filtrée sur les dimensions autour). Dans son post, Mosha explique que:

In order for drillthrough to address the correct cell, we need to put current cell coordinates into the SELECT clause. One way of doing it would be to write long MDX string concatenation, using <hierarchy>.CurrentMember.UniqueName subexpression for every hierarchy in the cube. However, this is tedious and error-prone approach. There are hundreds of hierarchies, new ones can be added, old ones removed or renamed etc. Instead, we can just call stored procedure which will enumerate all the current coordinates (note that we could’ve done the same with the RETURN clause too).

Procédure que l’on ne possède pas ici (Mosha utilise celle de ASSP qui renvoie du… MDX). Ce que j’ai montré pendant la session c’est le principe de la chose appliqué sur une seule hiérarchie que les plus courageux d’entre vous finiront. (mais si un candidat se présente pour écrire une stored proc… :)). La partie importante de la requête est dans le Filter, qui va désactiver le filtre si on est au niveau All en générant une clause en DAX depuis du MDX. Oui je sais c’est tordu:

"Evaluate
(
	CalculateTable
	(
		Summarize
		(
			'Reseller Sales'
			,'Date'[Date.Key0]
			,...
		)
		,'Sales Territory'[Sales Territory Region.Key0] = "+[Sales Territory].[Sales Territory Region].CurrentMember.Properties('Key0')+"
        ||"""+[Sales Territory].[Sales Territory Region].CurrentMember.Name+"""="""+[Sales Territory].[Sales Territory Region].DefaultMember.Name+"""
    )
)"

Conclusion

Voilà c’était à peu près tout. Merci à tous pour votre présence, à vos commentaires si vous voyez une erreur. Quant au contenu, ce sera avant Vendredi sur mon SkyDrive, j’éditerai le post.

A bientôt!

Techdays 2013: une petite dose d’Analysis Services pour démarrer l’année!

Bonjour à tous!

Cette année si vous n’aviez pas assez vu de MDX, de DAX avec David Joubert aux JSS, vous allez pouvoir venir aux Techdays 2013, voir ce que valent nos deux moteurs de Redmond au banc d’essai! Nous animerons en effet avec l’exceptionnel Romuald Coutaud et le non moins exceptionnel Aurélien Koppel de Microsoft une session qui vise à pousser les deux moteurs dans leurs retranchements fonctionnels et techniques afin que vous soyez sûr en repartant:

  1. De faire votre prochain projet avec Analysis Services
  2. De choisir le moteur qui satisfera vos utilisateurs.

Alors on vous attend, préparez des questions qui piquent, potassez votre MDX Cookbook, on se voit le mardi 12 à l’heure du gouter (ou de l’apéro, comme vous préférez!)

Allez vous inscrire, c’est par ici!

Conversion de temps: encore un usage sympa des SCOPE Assignments

Je bondis souvent lorsque l’on s’avise de me dire que “Tabular c’est génial, après tout les SCOPEs et les many-to-many ça ne sert que très rarement”. Effectivement si on voit SSAS comme un couche sémantique bêbête à la Business Objects sur une BdD relationnelle en étoile, que l’on fait en somme de la BI du 20ème siècle pourquoi pas. Mais lorsque l’on souhaite faire quelque chose de plus user-friendly, faire de son cube un vrai hub de données, presque une application à part entière alors je ne vois pas comment faire sans SCOPE (pensez aux Date Tool, aux discrétisations dynamiques comme les tranches d’âge) ou sans Many To Many (Conversion de devises, gestion des SCD…). Mais bon on n’est pas là non plus pour faire du tabular bashing, donc revenons à l’essentiel.

Il y a quelques semaine j’étais de passage chez un client ISV avec une problématique tellement rigolote que je me suis étonné de ne pas l’avoir rencontrée jusqu’alors: leurs données de fait sont datées en GMT mais les utilisateurs du cube sont dans le monde entier et souhaiteraient voir les données de production de leur zone dans… leur fuseau horaire.

Et ça n’est pas compliqué du tout. On a besoin de deux choses

  • Une dimensions Temps à la granularité heure
  • Une dimension technique (i.e. sans relation avec les groupes de mesure) qui schématise les fuseaux horaires.

Pour le premier point je ne vous ferai l’insulte ni de vous expliquer pourquoi on en a besoin, ni comment on le met en place Sourire. Pour le second, un simple requête en mode Table Value Constructor fait très bien l’affaire.

CREATE VIEW [dbo].[_DimUtc]
AS
WITH GmtCte
AS
(
	SELECT -12 UtcKey
	UNION ALL
	SELECT UtcKey + 1
	FROM GmtCte
	WHERE UtcKey <= 13
)
SELECT
	UtcKey,
	'UTC'
	+
	CASE
		WHEN UtcKey > 0 THEN '+'
		ELSE ''
	END
	+
	CASE
		WHEN UtcKey <> 0
		THEN CONVERT(VARCHAR,UtcKey)
		ELSE ''
	END	UtcName
FROM GmtCte
GO

Bon ça ne gère pas les demi fuseaux mais l’idée est là. On dispose bien d’un beau libellé et la clé va servir à générer le pseudo DATEADD. Une fois la dimension montée dans un cube simplifiée on a le Dimension Usage suivant:

DU

Ne reste plus qu’à écrire le SCOPE. Il spécifie que lorsqu’un UTC est sélectionné, ce dernier décale les membres heure de la dimension temps grâce à ParallelPeriod. Les chiffres agrégés sont modifiés grâce à la hiérarchie.

Scope
(
    [Date].[Hour].[Hour],
    [Utc].[Utc].[Utc]
);
    this =
    (
        ParallelPeriod
        (
            [Date].[Calendar].[Hour],
            -[Utc].[Utc].CurrentMember.Member_Key
        ),
        [Utc].[Utc].DefaultMember
    );

End Scope;

Le résultat est alors ce que l’on souhaitait, et plutôt performant:

UTC3

Voilà en espérant que cela vous serve.

A bientôt!

Bonus: Petite friandise: on peut même décider de mettre en place un DefaultMember dynamique: pour cela une petite fonction en .NET à déployer sur le serveur SSAS peut suffire. Il est alors possible de l’appeler dans le script pour mettre à jour le membre par défaut de la dimension UTC. 

public int GetCurrentTimeZoneOffset()
{
   return TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
}

Merci aux participants de notre session aux JSS2012!

Merci à tous ceux qui ont eu le courage de venir suivre notre session de DAX et MDX aux Journées SQL Server! Merci surtout au grand David Joubert, Marco Juberto du DAX avec qui je me suis bien marré et vous aussi je l’espère, en plus d’apprendre quelques trucs.

La salle était pas mal remplie pour une 400, avec pas mal de collègues et de copains en guest star (Florian Eiden, Charles-Henri Sauget, Thomas Ricquebourg, Aurélien Koppel, Romuald Coutaud, David Tang…)

Plus globalement sur l’event que je n’ai pas eu le temps de suivre (qui a dit qu’on était en retard dans les répétitions 🙂 ?) le ressenti des gens j’ai pu croiser est bon voire très bon, l’organisation s’affine petit à petit, et tout le monde s’accorde à dire que l’on tient là l’évènement de référence sur SQL Server. Longue vie aux JSS, et félicitations aux nombreux speakers (et les copains de la FC.BI qui dort un peu en particulier).

PS: Comme je suis un type sympa, vous pouvez retrouver les slides et les démos dans ce dossier sur mon SkyDrive.

A bientôt, aux TechDays?

Passez me dire bonjour aux Journées SQL Server!

Bonjour!

Comme dit précédemment, je co-animerai une passionnante session sur DAX et MDX aux Journées SQL Server avec David Joubert, centrée sur l’utilisation de DAX dans Analysis Services Tabular, et plus particulièrement dans des usages précédemment réservés à MDX.

La session aura lieu le mardi 11 décembre à 14h au Centre de Conférences Microsoft à Issy les Moulineaux.

Ce sera l’occasion pour vous de découvrir DAX, de voir comment il peut vous aider à résoudre de manière élégante des problématiques lourdes en MDX (Attribute Overwriting, multi-sélection), sans pour autant négliger les usages intéressants et toujours exclusifs au MDX. Venez avec vos questions, ça va être sympa 🙂

Pour ça rien de plus simple: allez vous inscrire, et n’hésitez pas aussi à venir aux sessions de mes petits camarades d’Infinite Square:

Thomas Ricquebourg le matin même sur MDS, DQS et SSIS

Charles-Henri Sauget sur de la modélisation dimensionnelle avec l’immmmense Florian Eiden le lundi

Patrice Harel la veille aussi sur du processing SSAS

Image

Alors on se retrouve aux Journées SQL Server?

Bonne soirée!

Save the date pour les Journées SQL Server!

La date est officielle ! La seconde édition des Journées SQL Server aura lieu les 10 et 11 décembre 2012 au

Centre de Conférences Microsoft
39 quai du Président Roosevelt
Issy-Les-Moulineaux

Vous pouvez dès à présent bloquer la date dans vos agendas pour participer à la plus grande conférence SQL Server en France.

Les inscriptions seront ouvertes dans quelques semaines, encore un peu de patience. En attendant rendez vous sur la page Facebook des JSS pour plus d’informations!

Le MDX c’est facile. Enfin presque.

Le MDX c’est facile. Bon il faut avouer que même si j’adore ça il faut le dire vite. Dans ma mission actuelle, en dissertant sur les différents modes de gestion du multiselect selon les clients – SESSION SET de l’OWC, Set dans le slicer, Aggregate de Proclarty, SUBCUBE d’Excel 2007-2010… – je me suis rendu compte qu’expliquer comment écrire un membre universel peut être complètement anti intuitif, même pour quelque-chose aussi simple qu’un… Count.

Voici ma requête. Elle affiche le nombre de produits distincts vendus par Pays et Couleur de produits, en filtrant sur les produits de type Vélo et les clients Mâles (dans le slicer) et les Produits de la gamme « Montagne » pour les années de vente 2005 et 2006 dans un subcube.

SELECT
{
     [Measures].[MonCount]
}
 ON 0,
{
    [Customer].[Customer Geography].[Country]
    *
    [Product].[Color].Members

} ON 1
FROM
(
    SELECT
    {
        [Date].[Calendar Year].&[2005],
        [Date].[Calendar Year].&[2006]
    }
    *[Product].[Product Model Lines].[Product Line].&[M]
    ON 0
    FROM [Adventure Works]
)
WHERE
(
    [Product].[Category].&[1]
    ,[Customer].[Gender].&[M]
)

Premier essai : un compte de tous les produits.

Count
(
    [Product].[Product].[Product]
)

Ca ne marche pas terrible: le compte est dé-corrélé de tout contexte car les membres calculés n‘auto-existent pas les sets. Cela affiche la même valeur partout, à savoir le nombre de produits de la dimension Produit. Amélioration : on ajoute donc un Existing. Existing, selon MSDN, force « la prise en compte des coordonnées courantes » de requête pour le jeu de produits. On constate que cela fonctionne pour les slicing par les hiérarchies de la même dimension, ainsi que le slicer ou clause WHERE).

Count
(
    Existing [Product].[Product].[Product]
)

C’est mieux, mais le filtrage par Produit.Modèle du sous-cube n’est pas pris en compte. Nous verrons pourquoi dans quelques lignes.
Une des solutions à ce problème de non prise en compte du sous-SELECT est de le faire en deux passes, en existant d’abord le jeu de produit dans un Dynamic Set – les jeux sont auto-existés en MDX dans le contexte de la requête et donc du subselect – le sous cube est appliqué sur le jeu. C’est Hilmar Buchta qui a le premier abordé cet usage intéressant des jeux nommés dynamiques, introduits en SQL 2008.

SET [QueryContextProducts]
AS
[Product].[Product].[Product]

MEMBER [Measures].[MonDistinctCount]
AS
Count
(
    Existing [QueryContextProducts]
)

Cependant là encore le slicing par pays n’est toujours pas visible: il faut donc cette fois utiliser l’opérateur Exists, qui fait une requête au niveau d’un groupe de mesure, pour évaluer l’indicateur sur le MeasureGroup des ventes qui relie le produit aux autres dimensions de la requête.

Count
(
    Exists
    (
        [Product].[Product].[Product]
        ,,"Internet Sales"
    )
) 

L’Exists n’applique cependant pas les coordonnées courantes sur l’axe Produit – ce que fait EXISTING comme nous l’avons vu – ce qui fait que l’indicateur ne se ventile pas par Couleur, et n’applique pas le filtre sur la gamme du slicer: les 55 produits vendus en Australie correspondent bien à 55 valeurs distinctes de ProductID dans la table de faits, mais le filtrage a posteriori par couleur en lignes, par gamme et par catégorie en filtres n’est pas pris en compte.
C’est logique MSDNement parlant: Exists marche comme ça. A noter qu’Exists sur un groupe est équivalent à un NonEmpty sur une des mesures du même groupe (bon sauf si cette mesure à un NullProcessing à Preserve mais ne chipotons pas).

En rajoutant l’opérateur Existing nous allons maintenant forcer l’application de ces fameuses « coordonnées courantes » du Produit sur le Set sorti par Exists, mais nous retombons dans le même problème que plus haut: celles fixées dans le sous-cube ne sont pas considérées. C’est là aussi by-design: comme le dit Mosha dans un post sur SDN: « EXISTING operator takes into account current coordinate. The whole difference between WHERE clause and subselect, is that WHERE clause sets current coordinate, while subselects merely do top level Exists with Axis and apply visual totals« . Bon OK, dommage que cela ne soit pas dans la doc… Les chiffres ci-dessous ne prennent donc pas en compte le filtrage sur [Product Model Lines], et sont là encore faux.

Count
(
    Existing
    Exists
    (
        [Product].[Product].[Product]
        ,,"Internet Sales"
    )
) 

A noter que du coup ce membre est équivalent à celui ci-dessous, dans lequel l’application du Model Line est précisée mais inutile : le CurrentMember de cette hiérarchie est toujours positionné sur le (All) car si le Subcube applique bien Exists et VisualTotals, il n’altère pas les coordonnées courantes. (Mis en lumière par Mosha dans plusieurs de ses vieux articles, comme Multiselect Friendly MDX Calculations ou Default members, MDX Scripts, Security, KPIs and Perspectives.)

Count
(
    Exists
    (
        [Product].[Product].[Product]
        ,([Product].[Color].CurrentMember,[Product].[Product Model Lines].CurrentMember)
        ,"Internet Sales"
    )
) 

Il en résulte que la seule solution viable et universelle, qui prend en compte tous les filtrages est celle-ci : un Count sur un Existing Exists (ou NonEmpty) appliqué sur un Dynamic Set. Je vous l’accorde, on a vu plus straightforward. A noter aussi que l’ordre d’application importe peu fonctionnellement, on aurait pu faire un Exists d’Existing, mais Chris Webb a montré en 2009 que l’ordre utilisé ci-dessous est le plus rapide. La raison n’est pas connue mais une des hypothèses considérées, à défaut d’avoir le nez dans le moteur, est qu’Exists est Set-Based donc moins impacté par la cardinalité du Set qu’Existing qui semble plus heureux sur une volumétrie réduite.

Count
(
    Existing
    Exists
    (
        [QueryContextProducts]
        ,,"Internet Sales"
    )
) 

Alors simple le MDX non? Des objections?

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

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!

[SSAS] Quelques rappels de Multiselect, Sets et AutoExists

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]
AS
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]
AS
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]
AS
{
   [Date].[Date].[Date]
}

MEMBER [Measures].[Nb Days In Year]
AS
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!