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?
