MDX – Division par 0,Null et fonction Divide()

Intro

De temps en temps je dois faire du MDX…
Et à chaque fois que je me lance dans cette aventure périlleuse d’écriture de requête multi dimensionelle, je me dis que j’aimerais prendre du temps pour vraiment tout connaitre sur ce langage. Et espérer qu’un jour je puisse le maîtriser.

C’est pourquoi je vais essayer de publier des posts (enfin) sur ce sujet.
Mon premier sujet la division par 0, Null et la fonction Divide()

Règle de base

La division par 0 n’est pas possible et quand on fait de l’informatique on a la valeur NULL qui vient semer le trouble. Selon le language, les fonctions il faut prévoir ces cas.

Comment le moteur OLAP fonctionne et comment gère t-on ce cas en MDX ?

Bonne question !

Premièrement règle d’or  0 = null  pour le moteur AS.

WITH MEMBER [Measures].[NullValue] AS Null
MEMBER [Measures].[ZeroValue] AS 0
MEMBER [Measures].[CheckZeroEqualToNull?] AS
IIF( [Measures].[ZeroValue] = [Measures].[NullValue], true, false) SELECT
{
[Measures].[NullValue],[Measures].[ZeroValue],
[Measures].[CheckZeroEqualToNull?]
} ON COLUMNS
FROM [MyKioub]

Résultat de la requête :

mdx_zero_null_true

Conclusion : AS considère les valeurs nulles comme une valeur à 0.

Par conséquent si il fallait vérifier le dénominateur d’une division il suffirait de vérifier le cas du 0 uniquement…

Application

Les requêtes ci-dessous présentent les différents cas que l’on peut rencontrer.
En sachant qu’avec SSAS 2008 les vérifications sont faites  avec des IF.
Tandis qu’avec SSAS 2012 on a une toute nouvelle fonction DIVIDE().

WITH MEMBER [Measures].[Value] AS 100
MEMBER [Measures].[NullValue] AS Null
MEMBER [Measures].[ZeroValue] AS 0
MEMBER [Measures].[100 / 0] AS
 [Measures].[Value]/[Measures].[ZeroValue] ,Format_String = '#.#0'
MEMBER [Measures].[100 / Null] AS
 [Measures].[Value]/[Measures].[NullValue] ,Format_String = '#.#0'
MEMBER [Measures].[Null / Null] AS
 [Measures].[NullValue]/[Measures].[NullValue] ,Format_String = '#.#0'
MEMBER [Measures].[0 / Null] AS
 [Measures].[ZeroValue]/[Measures].[NullValue] ,Format_String = '#.#0'
MEMBER [Measures].[Null / 0] AS
 [Measures].[NullValue]/[Measures].[ZeroValue] ,Format_String = '#.#0'
MEMBER [Measures].[0 / 0] AS
 [Measures].[ZeroValue]/[Measures].[ZeroValue] ,Format_String = '#.#0'
MEMBER [Measures].[0 / 100] AS
 [Measures].[ZeroValue]/[Measures].[Value], Format_String = '#.#0'
MEMBER [Measures].[Null / 100] AS
 [Measures].[NullValue]/[Measures].[Value], Format_String = '#.#0'
MEMBER [Measures].[Check 100 / 0] AS
 IIF([Measures].[ZeroValue] = 0, Null,  [Measures].[Value]/[Measures].[ZeroValue]),Format_String = '#.#0'
MEMBER [Measures].[Check 100 / NullEqual0] AS
 IIF([Measures].[Null] = 0, Null,  [Measures].[Value]/[Measures].[ZeroValue]),Format_String = '#.#0'
MEMBER [Measures].[Check 100 / Null] AS
 IIF(IsEmpty([Measures].[NullValue]), Null,  [Measures].[Value]/[Measures].[ZeroValue]),Format_String = '#.#0'
MEMBER [Measures].[DivFunct] AS
 DIVIDE([Measures].[NullValue],[Measures].[Value] )

SELECT {
 [Measures].[Value],
 [Measures].[NullValue],
 [Measures].[ZeroValue],
 [Measures].[Null / Null],
 [Measures].[0 / Null],  [Measures].[Null / 0],
 [Measures].[0 / 0],
 [Measures].[100 / 0],
 [Measures].[Check 100 / NullEqual0],
 [Measures].[100 / Null],
 [Measures].[Check 100 / 0],
 [Measures].[Check 100 / Null],
 [Measures].[0 / 100],
 [Measures].[Null / 100],
 [Measures].[DivFunct]} ON COLUMNS,
, [MyDim].[MyAttribute].&[MyMember] ON ROWS
FROM [MyKioub]

Résultats:

MDX_Test_Divide

La fonction DIVIDE()

Cette fonctione est disponible depuis la version 2012 de SQL SERVER, et possèdes 3 paramètres :
Divide (<numerator>, <denominator> [,<alternateresult>])

WITH MEMBER [Measures].[Value] AS 100
MEMBER [Measures].[NullValue] AS Null
MEMBER [Measures].[ZeroValue] AS 0
MEMBER [Measures].[100 / 0] AS
DIVIDE([Measures].[Value],[Measures].[ZeroValue]) ,Format_String = '#.#0'
MEMBER [Measures].[100 / Null] AS
DIVIDE([Measures].[Value],[Measures].[NullValue]) ,Format_String = '#.#0'
MEMBER [Measures].[Null / Null] AS
DIVIDE([Measures].[NullValue],[Measures].[NullValue]) ,Format_String = '#.#0'
MEMBER [Measures].[0 / Null] AS
DIVIDE([Measures].[ZeroValue],[Measures].[NullValue]) ,Format_String = '#.#0'
MEMBER [Measures].[Null / 0] AS
DIVIDE([Measures].[NullValue],[Measures].[ZeroValue]) ,Format_String = '#.#0'
MEMBER [Measures].[0 / 0] AS
DIVIDE([Measures].[ZeroValue],[Measures].[ZeroValue]) ,Format_String = '#.#0'
MEMBER [Measures].[0 / 100] AS
DIVIDE([Measures].[ZeroValue],[Measures].[Value]), Format_String = '#.#0'
MEMBER [Measures].[Null / 100] AS
DIVIDE([Measures].[NullValue],[Measures].[Value]), Format_String = '#.#0'

SELECT {[Measures].[Value],
[Measures].[NullValue],
[Measures].[ZeroValue],
[Measures].[Null / Null],
[Measures].[0 / Null],
[Measures].[Null / 0],
[Measures].[0 / 0],
[Measures].[100 / 0],
[Measures].[100 / Null],
[Measures].[0 / 100], [Measures].[Null / 100] } ON COLUMNS
, [MyDim].[MyAttribute].&[MyMember] ON ROWS
FROM [MyKioub]

Résultat :
DIVIDE_MDX

Si on ne spécifie pas de troisième argument  « alternateresult » dans la fonction DIVIDE() alors cette dernière retournera toujours « (null) ».
Vous pouvez donc remplacer ce comportement par défaut en spécifiant  une chaine de caractère ou un nombre positif.

Lien : FR – http://technet.microsoft.com/fr-fr/library/ms145626.aspx
EN – http://technet.microsoft.com/en-us/library/ms145626.aspx

Mosha wrote : http://sqlblog.com/blogs/mosha/archive/2007/01/28/performance-of-iif-function-in-mdx.aspx

CWebb wrote :http://cwebbbi.wordpress.com/2013/07/26/new-mdx-divide-function/

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