[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!
 

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s