[SQL2K5][MDX] Questionnement général sur les perfs du MDX

**Jinroh77** · 06/08/2008, 09h20

Bonjour,
Je dois réaliser quelques rapports dont les données seront alimentées par deu MDX directement sur le cube.
Selon une liste de quelques paramètres (6-8) je dois afficher :
Un premier code produit, nom du produit, code client, numéro client, département client, autre code de regroupement client, nom du regroupement, branche métier, date 1, Etat d'avancement du projet, Date 2 et enfin des mesures de CA (5).

Pour le moment j'ai mis une seule simple mesure et je rajoute mes dimension au fur et à mesure.
Pour 1 dimension, produit, c'est ok.
Je rajoute un croisement ( * ) avec le client ça passe encore.
Je rajoute ensuite le département, les rallentissement d'interrogation commencent à être sérieux..
Qu'est ce que ça va devenir lorsque je rajoute la dimension de regroupement, branche métier etc.... En gros je rajoute à chaque fois une nouvelle dimension (croisement de tables) et les perfs prennent un sacré coup.

Rien que le temps de validation de la query est très long

En SQL, on fait attention au type de jointure et c'est parti. Le MDX me parait beaucoup plus long pour ce genre de chose.

Si l'on croise 1, 2 dim avec quelques mesures c'est ok et très pratique, mais si on commence à avoir besoin d'afficher x dimension de détails les temps me semblent exploser.

**clementratel** · 06/08/2008, 14h20

Bonjour,

Où exécutes-tu ces requêtes ? Dans SSMS ou SSRS ?
SSRS n'est pas requêteur OLAP donc faire un rapport faisant intervenir plus de 2 dimensions dans SSRS sera fastidieux voir impossible. C'est un peu comme utiliser un maillet pour planter un clou : ça va pour un clou ou deux à la rigueur mais après il te faut un marteau. Des outils de reporting OLAP existent : ProClarity par exemple. En effet SSRS transforme les requêtes MDX en requêtes SQL sous le manteau. Avec SSRS base toi plutôt sur ton SGBDR et fait du SQL.

a+, =)
-=Clement=-

**Jinroh77** · 06/08/2008, 15h06

Ces requêtes en MDX sont pour alimenter des dataset pour RS.
On est bien d'accord qu'il vaut mieux passer par du SQL pour ce genre d'interrogation.
malheureusement ils veulent absolument passer par l'interrogation du cube ici. Le croisement des données étant simplifié....

**Reskibil** · 06/08/2008, 15h40

Heu je suis moyennement d'accord. Tout dépend du cube que tu interroge, de la volumétrie, de l'optimisation du cube, de la machine etc...

Pour ma part, sur un serveur cassant pas des briques, j'obtiens des perf tout à fait honorables sur les MDX meme avec plusieurs dimensions. Une chose que j'ai noté avec le MDX, c'est que la façon dont va être tournée la requête peut jouer sur les perf.

Pour tester, on avais meme comparé entre SQL et MDX les requetes équivalentes et le MDX l'emportait dans pas mal de cas, notamment grâce aux aggregats. Notre base n'étant pas forcément optimisée pour les requetes (on ne s'en sert au final que pour générer le cube) et n'étant pas des dba, il y a certainement des points qui pourraient etre améliorés mais je le redis, le MDX, que ce soit dans SSMS ou SSRS n'est pas forcément plus lent que du SQL.

Un petit conseil, si tu as besoin de craiser plusieurs dimensions, rien ne t'empeche de faire des vues regroupant tous les éléments dont tu as besoin (en SQL pour le coup) et d'en faire des dimensions. Pour ma part, c'est comme ca que je fais si je dois croiser 4 ou 5 dim (voire plus). Mais la encore, ca depend de l'environnement. Tous les process longs sont fait pendant la nuit et ma base n'est pas une base de production, juste un datawarehouse dont les données changent 1 fois par jour.

**Jinroh77** · 06/08/2008, 15h52

Il y a très certainement des optimisation à faire sur le cube de notre côté. Ce pauvre est composé de quelques 40 dimensions et plus de 150 mesures....

Voila à peu près une partie du script MDX que j'aimerais charger.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH MEMBER [Measures].[Ca Propose] AS (
[Measures].[Proposition - Total Ca Produit HT]
+ [Measures].[Proposition - Total Ca Frais HT]
)
 
MEMBER [Measures].[Ca Planifie] AS (
[Measures].[Proposition - Ca Produit HT Planifie]
+ [Measures].[Proposition - Ca Frais HT Planifie]
)
MEMBER [Measures].[Ca Realise] AS (
[Measures].[Proposition - Ca Produit HT Preste]
+ [Measures].[Proposition - Ca Frais HT Preste]
)
MEMBER [Measures].[Ca Facture] AS (
[Measures].[Proposition - Ca Produit HT Facture]
+ [Measures].[Proposition - Ca Frais HT Facture]
)
 
SELECT NON EMPTY {
    [Measures].[Ca Propose]
    ,[Measures].[Ca Planifie]
    ,[Measures].[Ca Realise]
    ,[Measures].[Ca Facture]
    } ON COLUMNS
 
,NON EMPTY {
    ([Affaires].[Affaire].children
        * [Affaires].[Libelle Affaire].Children  ) 
 
    * ([Entites Clientes].[Code Entite].Children
        * [Entites Clientes].[EntiteCliente].Children)
 
   * ([Grands Comptes].[Entite Cliente Mere].Children)
 
    * ([Geographie - Societe].[Code Departement Geo].Children)
 
    } ON ROWS 
 
FROM (SELECT ([Affaires].[Chef De Projet].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Apporteur D Affaires].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Assistante].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Libelle Departement].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Etat Proposition].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Categorie Projet].[All]) ON COLUMNS
 
FROM [MonCube]))))))

Et je n'ai pas encore mis toutes les dimensions que je souhaite.
j'ai également des filtres sur les attributs de ma dimension principale. Tous à All pour le moment mais paramétrés dans mon RS.
Comme cela, même avec seulement les 2 première dimensions, le script ne passe pas (> 3min)

Un équivalent en sql serait :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT 
	aff.Code
	, MAX(aff.Libelle) Libelle
	, MAX(ec.RaisonSociale) RaisonSociale
	, SUM(CaFraisHTPlanifie + CoutProduitHTPlanifie) AS CaPlanifie
FROM 
	T_FAIT_PROPOSITIONS ftprop
	JOIN T_DIM_PROPOSITIONS dprop
		ON dprop.Proposition_Id = ftprop.Proposition_Id
	JOIN T_DIM_AFFAIRES aff
		ON aff.Affaire_Id = dprop.Affaire_Id
	JOIN T_DIM_ENTITES_CLIENTES ec
		ON aff.EntiteCliente_Id = ec.EntiteCliente_Id
 
WHERE 
	ftprop.DateFinValidite_Id IS NULL
--	AND aff.Application_Id = 2
GROUP BY aff.Code
ORDER BY aff.Code

Cette requête, même sans les filtre prend maximum 10s sans aucun cache de disponible (reboot server).

Maintenant mon script MDX est très sûrement à améliorer, je tâtonne seulement dans ce langage.

**Reskibil** · 06/08/2008, 17h05

Là se pose la question :
Soit tu passes du temps à essayer d'améliorer ton MDX, soit tu fais un dataset en SQL (avec eventuellement d'autres en MDX).

Je connais pas ton cube donc je vais pas pouvoir t'aider beaucoup mais en regardant ta requête, il me vient 2 choses :

- Utilise des filter, ca a parfois des effets d'amélioration de perf surprenant.
- Tes dimensions ressemblent étrangement à une hierarchie qui pourrait se faire sur une seule dimension.

**Jinroh77** · 06/08/2008, 17h08

Envoyé par Reskibil

Là se pose la question :
Soit tu passes du temps à essayer d'améliorer ton MDX, soit tu fais un dataset en SQL (avec eventuellement d'autres en MDX).

Je connais pas ton cube donc je vais pas pouvoir t'aider beaucoup mais en regardant ta requête, il me vient 2 choses :

- Utilise des filter, ca a parfois des effets d'amélioration de perf surprenant.
- Tes dimensions ressemblent étrangement à une hierarchie qui pourrait se faire sur une seule dimension.

- Les filter, il faudrait que j'essaye mais je fais devoir filtrer selon des paramètres RS, je ne sais pas trop comment ça fonctionne et mes derniers essais n'étaient pas concluant. A refaire.

-Les dimensions en hiérarchie, ça ne fonctionne pas. les différents attributs de Affaires par exemple ne sont pas forcement reliés entre eux. Les entités cliente ne sont pas directement sur des affaires non plus etc...

**ygrim** · 07/08/2008, 20h10

Envoyé par clementratel

Bonjour,

Où exécutes-tu ces requêtes ? Dans SSMS ou SSRS ?
SSRS n'est pas requêteur OLAP donc faire un rapport faisant intervenir plus de 2 dimensions dans SSRS sera fastidieux voir impossible. C'est un peu comme utiliser un maillet pour planter un clou : ça va pour un clou ou deux à la rigueur mais après il te faut un marteau. Des outils de reporting OLAP existent : ProClarity par exemple. En effet SSRS transforme les requêtes MDX en requêtes SQL sous le manteau. Avec SSRS base toi plutôt sur ton SGBDR et fait du SQL.

a+, =)
-=Clement=-

Je pense que quelque soit l'endroit d'ou tu exécute ta requête, c'est le moteur analysis services qui va la traiter, c'est indépendant de SSRS ou SSMS... Client, serveur.

En ce qui concerne MDX, et comme le dit si bien ReskiBil, tout dépend du cube. L'idée à garder en tête c'est que MDX n'est pas concu pour faire du SQL, de même que SQL n'est pas concu pour faire du MDX. En gle, aller dans le détail de tes tables (plus bas niveau) avec du MDX te causera de gros problèmes de perfs, de même qu'aller dans des agrégations de malade avec SQL te donneras les même performances médiocres.

**clementratel** · 08/08/2008, 09h02

Au temps pour moi, je me suis mal fait comprendre. Si je posais la question de savoir si Jinroh77 exécutait ses requêtes sur SSMS ou SSRS c'est parceque j'ai déjà eu le cas de requêtes qui passent dans SSMS mais pas dans SSRS pour des grands volumes.
Un rapport à 8 paramètres sur plusieurs dimensions, et la requête MDX présentée le confirme, est plus proche d'une analyse que d'un rapport.
Donc j'utiliserais ProClarity ou Excel qui sont meilleurs en analyse, ou SQL dans SSRS si je devais malgré tout sortir un rapport figé.

a+, =)
-=Clement=-

**Jinroh77** · 08/08/2008, 11h12

je pense bien qu'il doit y avoir des choses à faire dans ce cube.
Ils utilisent 8 tables de faits avec toutes les dimensions autour.
Beaucoup sont liées en relation Referenced.

Bref, j'ai amélioré grandement mes temps de calculs en n'utilisant plus les membres..
Je récupère directement mes mesures et je ferais les sommes dans mon RS :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SELECT NON EMPTY {
//    [Measures].[Ca Propose]
//    ,[Measures].[Ca Planifie]
//    ,[Measures].[Ca Realise]
//    ,[Measures].[Ca Facture]
    [Measures].[Proposition - Total Ca Produit HT]
	, [Measures].[Proposition - Total Ca Frais HT]
    , [Measures].[Proposition - Ca Produit HT Planifie]
	, [Measures].[Proposition - Ca Frais HT Planifie]
	, [Measures].[Proposition - Ca Produit HT Preste]
	, [Measures].[Proposition - Ca Frais HT Preste]
	, [Measures].[Proposition - Ca Produit HT Facture]
	, [Measures].[Proposition - Ca Frais HT Facture]
    } ON COLUMNS
 
,non empty {
 
    [Affaires].[Affaire].children
//    filter([Affaires].[Libelle Affaire].Children
//           ,  [Affaires].[Assistante].&[13])    
 
   * [Affaires].[Libelle Affaire].Children
 
    * [Entites Clientes].[Code Entite].Children
 
    * [Entites Clientes].[EntiteCliente].Children
 
//   	* [Grands Comptes].[Entite Cliente Mere].Children
 
  * [Geographie - Societe].[Code Departement Geo].Children
 
    * [Affaires].[Libelle Departement].Children
}
having  [Measures].[Proposition - Total Ca Produit HT]
	+ [Measures].[Proposition - Total Ca Frais HT]
    + [Measures].[Proposition - Ca Produit HT Planifie]
	+ [Measures].[Proposition - Ca Frais HT Planifie]
	+ [Measures].[Proposition - Ca Produit HT Preste]
	+ [Measures].[Proposition - Ca Frais HT Preste]
	+ [Measures].[Proposition - Ca Produit HT Facture]
	+ [Measures].[Proposition - Ca Frais HT Facture] > 0
     ON ROWS 
 
//FROM (SELECT([Affaires].[Chef De Projet].&[16]) ON COLUMNS 
FROM (SELECT ([Affaires].[Assistante].&[13]) ON COLUMNS
FROM (SELECT ([Affaires].[Libelle Departement].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Etat Proposition].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Categorie Projet].[All]) ON COLUMNS
FROM (SELECT ([Affaires].[Application].&[2]) ON COLUMNS
 
FROM [MonCube])))))//)

Avec cela je descend à des temps de l'ordre de la seconde.

Par contre il me reste d'autres problèmes à traiter mais j'en ferais un autre topic.

**clementratel** · 08/08/2008, 11h33

Quand tu dis que tu n'utilises plus les membres, tu parles des WITH MEMBER AS ? Car je vois une autre différence

dans cette requête MDX on voit qu'elle ne renvoie les résultats que pour un Chef de Projet, une Assistante et une Application donnés, alors que dans la première tu passais sur chaque membre de ces dimensions.

a+, =)
-=Clement=-

**Jinroh77** · 08/08/2008, 11h37

Je me suis mal exprimé, c'est effectivement le WITH MEMBER AS que je n'utilise plus.
Pour les filtre dans mes FROM, j'ai juste rajouté un peu plus de filtres pour mes tests.

Par contre maintenant j'aurais besoin de ramener toutes les lignes qui concernent tel chef de projet OU telle assistante.
Malheureusement lorsque je mets ces 2 là mon filtre, il ne prends que les lignes qui sont à la fois pour tel chef de projet ET pour telle assistante.

[SQL2K5][MDX] Questionnement général sur les perfs du MDX

SSAS

Discussions similaires

Partager

Partager