Petite question SSAS d’un dba éclairé, une belle introduction au Many to Many !

Fin Août, j’ai reçu par mail une question très bien formulée :

Je me permets de vous adresser ce mail (obtenu à partir du site GUSS) car je suis à la recherche d’une bonne âme susceptible de m’aider avec SSAS. Je suis dba SQL serveur depuis plus de 10 ans mais je n’ai, jusqu’à présent, pas eu l’occasion de travailler avec SSAS. J’ai réalisé un 1er cube de vente actuellement en production, je dois le modifier et pour le moment je suis dans une impasse.

Si vous avez l’opportunité de transmettre mon mail à quelqu’un qui pourrait m’aider, voici un résumé de mon problème.

L’objectif est d’obtenir un résultat comme celui-ci :

A partir du schéma suivant :

Et les relations suivantes :

J’obtiens alors le résultat correct ci-dessous :

Mais dès que j’ajoute une date, soit d’échéance, soit de règlement, je me retrouve avec l’ensemble des dates de ma dimension date…

 

Ce à quoi je réponds :

Bonjour à vous,

Excellente initiative de commencer SSAS! Passées les premières frustrations, c’est un outil vraiment passionnant 😉

Pour votre problème, vous êtes, à mon sens, dans une problématique “Many to Many”, dont les gens de SQLBI parlent très bien dans ce PDF (c’est la bible sur le sujet).

D’abord, histoire de bien partir du même modèle, voici ce que j’en comprends pour la base:

Avec déjà 2 subtilités :

• la roleplaying sur la dimension date (une même table physique qui définit une même dimension dans SSAS, mais qui est utilisée 3 fois de manière indépendante dans un même cube).
• la dim_factures issue de la table de fait Facture. Pensez bien à construire cette dimension dans la solution, et à l’inclure dans le cube (si ce n’est pas déjà le cas). La spécificité tient ici à l’utilisation d’une relation de type Fait au niveau du cube pour sa relation avec Ft_Facture (et pas les autres), c’est une optimisation puisque tout est bien dans la même table au niveau sous-jacent.

Ensuite, pour vous répondre, il va vous falloir définir des relations Many to Many sur vos dimensions dates existantes (et non créer une dimension date supplémentaire), avec les indications suivantes, soit dans votre cas pour toutes les cases grises :

Bon courage, et n’hésitez pas à me faire part de vos remarques 🙂

++

Ce à quoi il me répond :

Merci encore pour votre aide. Maintenant, j’ai un cube qui semble fonctionner. Je dois encore contrôler les chiffres mais il ressemble à ça:

Par contre, j’ai juste une petite question au sujet de la “RolePlaying dimention”. Comme les dimensions date d’échéance et date de facturation utilisent la même et unique dimension “time”, comment faire pour différencier les deux au niveau des “labels” ? Par exemple, l’année de facturation et l’année des échéances apparaissent toutes les deux avec le label “Année” (idem sous Excel) ce qui n’est pas pratique pour le reporting.

 

Je réponds alors :

Magnifique tout ça 🙂

Surtout, pratiquez une recette exhaustive et des tests réguliers sur toutes les mesures impactées par le many to many. Si cette méthode est robuste techniquement, elle peut facilement partir en sucette par effet de bord d’autres modifications.

Malheureusement pour le roleplaying, pas de magie côté SSAS, l’opération de renommage des attributs n’est juste pas possible au niveau du cube. En théorie, dans Excel, le problème est mitigé, avec le nom de la dimension qui est rappelé devant le nom de l’attribut.

Par contre, si ces modifications sont critiques, je vous encourage à retourner sur une gestion du roleplaying par des vues au niveau de la base SQL (CREATE VIEW DimDateReglement AS SELECT … AS …_Reglement FROM DATE), que vous chargerez alors dans SSAS comme des dimensions distinctes au niveau de la solution. L’inconvénient étant évidemment qu’une modification de structure de la table sous-jacente demande 3 fois plus de travail pour être impactée dans le cube (plus l’impossibilité de définir des clefs étrangères, mais perso je ne les pose jamais de toute façon!).

Un peu plus tard, j’ajoute :

Et je rebondis sur votre tableau de relations.

Les relations many to many sont très gourmandes en performance. Il est donc conseillé de ne s’en servir que lorsque c’est strictement nécessaire.

Donc, si les dimensions consultants, entités, langages, marchés, prestations et/ou sociétés peuvent être ajoutées aux tables de fait des règlements et des échéances sans changer leur granularité (si ajouter leurs IDs respectives à chaque table de fait ne change pas le nombre de lignes dans chacune), il faut le faire. Or, j’ai l’impression que ces éléments sont au niveau de la facture, donc à un niveau de granularité supérieur aux règlements ou aux échéances. Ils pourraient donc être ajoutés, contrairement aux dates des règlements et dates d’échéances qui sont respectivement à des niveaux de granularité différents de la facture et donc ne peuvent exister que dans leur table de fait propre et nécessitent un M2M pour être propagées ailleurs.

Vous échangerez du stockage (+de colonnes sur vos tables de fait) contre du temps de processing et de requêtage (moins de many to many et plus de relations standards). A mon sens un très bon deal.

Enfin, n’oubliez pas d’exécuter le wizard de calcul d’agrégation pour chacun de vos groupes de mesure (en laissant toutes les options par défaut). C’est un oubli courant qui pourtant fait toute la valeur de SSAS.

Pour conclure :

Et voilà, une excellente attitude d’un pro avec 10 ans d’expérience qui n’hésite pas à demander de l’aide quand il appréhende un sujet connexe complexe. Et, je l’espère également, des astuces pour vous aider à comprendre le pourquoi et le comment des Many to Many. Pour creuser, évidemment le Many to Many Revolution des compères italiens, et cet article récent de Patrice Harel.