Discussion :

[oussema.esp]

Bonjour,

Je débute avec MICROSOFT BI (SUITE SQL SERVER 2012) et j’ai un problème avec les agrégations.

Je vais commencer par expliquer la situation et entamer par le problème.

Je travaille sur un projet BI dans le secteur boursier qui consiste à réaliser des tableaux de bord pour analyser la performance des investissements des clients en bourse.
->Ceci en calculant principalement le rendement d’investissement.

Pour ce faire j’ai commencé par la conception du DW ainsi que son alimentation avec SSIS.

Par la suite j’ai commencé la création du cube OLAP avec SSAS et c’est dans cette partie que je me bloque.

En fait j’ai créé mon cube avec succès mais le problème est lors de navigation ( Date -> Mois -> Année ) .

Pour mieux comprendre je vais donner un exemple simplifié de mon projet :

Je vais commencer par décrire mon Cube qui contient :

- Dim ValMob (désigne les placements dont lesquelles les clients peuvent investir);

- Dim Client (désigne les clients qui investit);

- Dim Temps ;

- Dim Secteur (désigne les secteurs des placements) ;

- Fact (Dim ValMob, Dim Client, Dim Temps, Dim Secteur, rendement) (contient le rendement d’investissement d’un client d’une ValMob en une date (Jour J)


Prenant un cas concret (pour les valeurs je vais mettre n’importe quoi) :

- Dim ValMob( ActionX, ActionY,…)

- Dim Client (ClientA, ClientB,…)

- Dim Temps (2015-02-23, 2015-03-24,…)

- Dim Secteur (Sector1, Sector2,...)

Fact (

Client | Date | ValMob | Secteur | Rendement

ClientA | 2015-02-23 | ActionX | Sector1 | 0.001
ClientA | 2015-02-23 | ActionY | Sector1 | 0.002
ClientA | 2015-02-23 | ActionZ | Sector1 | 0.001
ClientB | 2015-02-23 | ActionX | Sector2 | 0.003
ClientB | 2015-02-23 | ActionW | Sector1 | 0.005
ClientA | 2015-03-23 | ActionX | Sector1 | 0.004
ClientA | 2015-03-23 | ActionY | Sector2 | 0.007
ClientA | 2015-03-23 | ActionZ | Sector1 | 0.001
ClientB | 2015-03-23 | ActionX | Sector1 | 0.003
ClientB | 2015-03-23 | ActionW | Sector2 | 0.005

)

Jusqu'ici ya pas de problèmes.

Maintenant si je veux naviguer dans mon cube par mois ou Année et non par jour (En utilisant les hiérarchies de la dimension temps), le cube par défaut va sommer les rendements.

Par exemple pour le mois Mars 2015 (2015-03-01) le rendement sera calculé par la somme des rendements durant les jours du mois :

Rendement (Mois de mars 2015) = Rendement Jour (j1) + RendementJour (j2) + … + Rendement Jour (j Fin mois)

Dans notre exemple : Rendement (Mars) = 0.004 + 0.007 + 0.001 + 0.003 + 0.005 = 0.020

Alors que dans mon cas le rendement doit être calculé par le Produit(Multiplication) des rendements et non la Somme :

Rendement = [(1 + 0.004) * (1 + 0.007) * (1 + 0.001) * (1 + 0.003) * (1 + 0.005)] – 1 = (Valeur)

Et c’est dans cette partie que je me bloque. Je cherche depuis quelques semaines mais sans résultat.

Je vous remercie.

[Invité]

Bonjour,

As-tu regardé du coté des Unary Operator ? Ils permettent de customiser l'opération d'aggrégation il me semble.

[rw72000]

Bonjour,

Pour compléter, voir le post de Mosha :

http://sqlblog.com/blogs/mosha/archi...on-in-mdx.aspx

L'idée est de créer une mesure calculée dans le cube qui reprendrait ce principe.

[oussema.esp]

Bonjour,

Je vous remercie pour vos réponses.

J'ai déjà vu le post de Mosha avant d'ouvrir la discussion.

J'ai essayé la solution mais ça m'a donné des chiffres incorrects et comme je viens juste de débuter avec MDX (suite à ce problème), j'arrive pas à détecter ni à comprendre d'où vient l'erreur (PS : Erreur de calcul et non de compilation).

Vu que je travaille sur des chiffres compliqués je vais essayer la solution avec d'autres simples pour comprendre le mécanisme et le comportement de code.

Mais je veux savoir si c'est la bonne piste ou pas. Ou s'il y a d'autres pistes à suivre. (Parce qu'on travaillant sur des rendements voire des chiffres compliqués (0.0002547...) et en appliquant le log puis la puissance on ne risque pas d'avoir des chiffres Out of Range ou des erreurs de calculs ? )

D'autre part ya des rendements négatifs donc je suppose que je dois trouver une autre piste vu que le log doit être appliqué seulement sur des chiffres positifs.

Merci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
CALCULATE;
 
CREATE MEMBER CURRENTCUBE.[Measures].[R] AS [Measures].[RENDEMENT];
CREATE MEMBER CURRENTCUBE.[Measures].[RLog] AS (VBA![Log]([Measures].[R]));
CREATE MEMBER CURRENTCUBE.[Measures].[SumRLog] AS Sum(DESCENDANTS([Temps].[Hiérarchie].CurrentMember,,Leaves), [Measures].[RLog]);
CREATE MEMBER CURRENTCUBE.[Measures].[ProductOfR] AS 10^[Measures].[SumRLog]

[VITALTH]

Pour l'erreur de calcul et l'Out of range je ne sais pas.
En revanche pour les ln négatif je te suggère de fonctionner comme tel :

En SSIS tu rajoutes 2 mesures (qui ne seront pas visible dans ton cube pour l'utilisateur). Une valeur qui prend 0 ou 1 (si ton rendement est négatif tu mets 1 sinon 0) (tu l'appelles signes) et une autre qui est la valeur absolu de ton rendement).

Ensuite en SSAS :
tu fais la manip avec les ln non pas sur ton rendement mais sur la valeur absolu de ton rendement (pas de pb de ln < 0).
tu calcules également une mesure calculé appelé signe produit qui est calculé comme suit (si modulo de signes = 2 alors 1 sinon -1)

Puis ton calcul est le produit des 2 mesures précédentes.

[VITALTH]

Si je puis me permette : maintenant que l'idée de SSIS est lancée : tu n'es pas obligée de faire les membres super compliquée Mosha Pasumansky.

Dans ta table de fait tu remplaces ton rendement par le logarithme ou le logarithme népérien (tout dépends de l'élévation à la puissent (e^ ou 10^) d'un nombre en MDX) de la valeur absolu de ton rendement (appelons le logRend) = ln(abs(rend))
Tu gardes ton membres signe = case when rend > 0 then 0 else 1 end

(A la limite tu n'es même pas obligé de le faire en SSIS mais en remplacçant dans ta source de donnée ta table ou ta requête par une requête calculée).

Puis en SSAS tu crées deux mesures
[MEASURES].[LOGREND] pour la somme des logRned
[MEASURES].[SIGNE] pour la somme des mesures.

Puis tu créé un champs calculé qui est ta multiplication comme suit :
iif(MOD[MEASURES.SIGNE],2) = 0, 1, -1) * e^([MEASURES].[LOGREND]).

Je pense que ça doit marcher.

[oussema.esp]

Bonjour,

Merci beaucoup VITALTH pour votre réponse et je vous félicite pour votre raisonnement.

C'est génie comme solution et je pense aussi que ça doit marcher. Je l’espère bien.

Je la mettrai en place et je vous tiendrai au courant de l'avancement.

Néanmoins je pense que je dois régler avant tout un petit problème pour pouvoir avancer, parce que au niveau SQL lorsque j'applique le LOG sur ma colonne rendement dans ma table un message d'erreur apparaîtra : Msg 3623, Level 16, State 1, Line 1 Une opération en virgule flottante non valide s'est produite.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT LOG(ABS(RENDEMENT)) FROM [PortFolioDW].[dbo].[FactPortfolioReturn]

J'essayerai de fixer l'erreur et je vous tiendrai au courant.

Merci.

[VITALTH]

Merci bcp pour le compliment.

Pour le problème de virgule flottante, vu de l'extérieur j'opterai pour un problème de précision. Je me demande dans quel mesure vous n'avez pas à appliquer la fonction ROUND

[oussema.esp]

Derien.

C'est ce que je pense aussi.

Mon rendement était du type NUMERIC(18,8) alors j'ai changé le type en NUMERIC(10,4).

Puis j'ai appliqué le ROUND sur le RENDEMENT mais ça n'a pas marché toujours la même erreur.

Mais enfin j'ai détecté d'où elle vient , il y a des rendements qui sont égaux à 0 et donc c'est logique parce qu'on on ne peut pas appliquer le log sur 0.

C'est bon je vais régler ça au niveau SSIS et je commencerai à mettre en place la solution SSAS.

Merci.

[VITALTH]

Ah oui le 0 je n'y ai pas pensé.

Je pense qu'il va te falloir une colonne supplémentaire qui te donne le nombre de 0. Si tu as 0 dans ton rendement tu ajoutes 0 dans la colonne LOG(ABS(RENDEMENT)) mais tu ajoutes 1 dans cette colonne supplémentaire.

De tel façon qu'en SSAS (en MDX) si tu as plus de 0 dans ta mesure [MEASURE].[NBRE_ZERO] tu mets ton résultat à 0.

[oussema.esp]

Oui c'est une solution mais je vais procéder d'une autre manière plus simple.

En fait la formule que je dois utiliser pour calculer le Rendement Mensuel est égal : (1+R1)*(1+R2)*(1+R3)*.......*(1+Rn)

Et donc c'est le produit de (1+R) que je dois calculer alors je vais stocker directement (1+R) et voila le tour est jouée si le Rendement est égal à 0 alors je vais stocker LOG(1+0)=LOG(1)=0.

Il reste tout de même la possibilité d'avoir des exceptions.

[VITALTH]

Ok je pensais que c'était ton calcul de 1 + Rendement = 0

(Cela dit je ne suis pas sur que ce que je viens de marquer ait un sens dans votre modélisation)

[oussema.esp]

Si une bonne remarque que suite à elle je viens de penser si c'est vraiment le cas et le rendement de 1 + R sera égal à 0 (si rendement =-1) et donc on revient au même problème!

Je vais faire une condition lors de l'alimentation SI (1 + R)=0 alors Log Rend=0 normalement ça marchera.

[oussema.esp]

Rebonjour,

c'est bon je viens de terminer l'alimentation de ma table FACT avec succès.

Seulement j'ai un souci à propos de signe pour la partie SSAS.

Je n'ai pas compris ce que vous voulez me dire par ce test iif(MOD[MEASURES.SIGNE],2) = 0, 1, -1).

En fait normalement le signe de mon rendement sera le produit des signes stockés et comme je peux pas faire le produit sur ma mesure Signe j'ai pensé à faire Count sur seulement les signes négatifs (-1) puis tester sur le modulo comme suit RendementMensuel=IIF(MOD(COUNT(Mesure.Signe-1),2)=0,1,-1)*EXP(Mesures.LogRend).

Est-ce faisable? ou il y a d'autre solution?

Merci.

[VITALTH]

C'est exactement ce que je proposais.
Le problème est que MDX ne connait pas le modulo mais ce n'est pas grave car il connait la partie entière.

Donc on peut remplacer :
MOD([MEASURE].[SIGNE],2) par (MEASURE].[SIGNE]-(Int(MEASURE].[SIGNE]/2)*2))

[oussema.esp]

Bonjour,

Merci beaucoup pour vous tous et plus particulièrement VITALTH. C'est bon ça marche très bien, problème résolu.

Seulement j'ai un souci, non pour l’agrégation en tant que Multiplication, mais pour le COUNT de seulement les signes négatifs (=-1). Je n'arrive pas à appliquer le FILTER.

Pour le moment tout mais signes sont positifs soit 1 soit 0 mais on sait jamais.

Merci.

[fatimekouar]

svp ça fait 2 semaines que je galère à déployer le cube OLAP à chaque fois il m affiche connexion impossible sachant que j ai autoriser Analysis services assistant de déploiement à communiquer à travers le parfeu mais tjrs le mm problème comment tu as fait pour le déployer
ps: je travaille avec la version gratuite de sql server data tools

[oussema.esp]

svp ça fait 2 semaines que je galère à déployer le cube OLAP à chaque fois il m affiche connexion impossible sachant que j ai autoriser Analysis services assistant de déploiement à communiquer à travers le parfeu mais tjrs le mm problème comment tu as fait pour le déployer
ps: je travaille avec la version gratuite de sql server data tools

Bonjour,
Tu peux nous donner plus de détails ?
le message d'erreur c'est quoi exactement ?

sinon tu peux suivre ce tuto : http://www.codeproject.com/Articles/...-Analysis-Serv

[fatimekouar]

Bonjour,
Tu peux nous donner plus de détails ?
tu as créé un utilisateur SSAS?
le message d'erreur c'est quoi exactement ?

sinon tu peux suivre ce tuto : http://www.codeproject.com/Articles/...-Analysis-Serv

mercii bc voila le msg d'erreur
Erreur 4 Le projet n'a pas pu être déployé sur le serveur «*localhost*» en raison des problèmes de connectivité suivants*: Impossible d'établir une connexion. Vérifiez que le serveur fonctionne.. Pour vérifier ou mettre à jour le nom du serveur cible, cliquez avec le bouton droit sur le nom du projet dans l'Explorateur de solutions, sélectionnez Propriétés du projet, cliquez sur l'onglet Déploiement, puis entrez le nom du serveur. 0 0

[VITALTH]

Bonjour,
Pour répondre à votre problème, je vais poser plusieurs questions. Elles vont peut être vous paraitre dérisoire mais c'est pour localiser le problème :
- Avez vous déjà réussi au moins une fois à déployer votre cube.
- Etre vous sur d'avoir une instance Analyses Services installé sur votre machine ? ou avez vous déployé votre cube sur un serveur distant ?
- Avez vous un compte de service qui exécute le service Analyses Services ?
- Avez vous les droits suffisants sur la base de données distantes/locales où est déployé votre cube ?
- Quand l'erreur se produit, que dit l'observateur d'évenement du serveur où est déployé votre cube ?

Essayez de répondre à toute mes questions, on va essayer d'établir un diagnostique.