Les hiérarchies parents-enfants conviennent bien à de nombreuses structures de données telles que les comptes ou les employés, et si elles peuvent accélérer le développement dans certains cas, elles peuvent aussi causer des problèmes de performance dans les grands cubes.
Nous voyons souvent des clients qui ont ce type de problèmes de performance et nous avons pensé qu'il valait la peine de partager une technique simple pour modifier la structure des dimensions afin d'améliorer la vitesse de requête.
Le problème
Souvent, des hiérarchies parents-enfants sont créées, car c'est la structure utilisée dans la source relationnelle, et elles semblent donc bien adaptées pour modéliser les membres. Dans de nombreux cas, cependant, les données ne se trouvent qu'au niveau de la feuille de la hiérarchie, ce qui signifie que le lien parent-enfant n'est pas vraiment nécessaire.
Les problèmes de performance se produisent parce qu'aucun agrégat n'est créé pour les dimensions parent-enfant, comme le précise le guide de performance des Services d'analyse :
Hiérarchies parents-enfants
Les hiérarchies parents-enfants sont des hiérarchies à nombre variable de niveaux, déterminées par une relation récursive entre un attribut enfant et un attribut parent. Les hiérarchies parents-enfants sont généralement utilisées pour représenter un plan comptable financier ou un organigramme. Dans les hiérarchies parents-enfants, les agrégations sont créées uniquement pour l'attribut clé et l'attribut supérieur, c'est-à-dire l'attribut Tous, sauf s'il est désactivé. Il faut donc éviter d'utiliser des hiérarchies parents-enfants qui contiennent un grand nombre de membres aux niveaux intermédiaires de la hiérarchie. En outre, vous devez limiter le nombre de hiérarchies parents-enfants dans votre cube.
Si vous vous trouvez dans un scénario de conception avec une grande hiérarchie parents-enfants (plus de 250 000 membres), vous pouvez envisager de modifier le schéma source pour réorganiser une partie ou la totalité de la hiérarchie en une hiérarchie utilisateur avec un nombre fixe de niveaux. Une fois que les données ont été réorganisées dans la hiérarchie des utilisateurs, vous pouvez utiliser la propriété "Cacher le membre si" de chaque niveau pour cacher les membres redondants ou manquants.
Le guide de performance suggère de réorganiser la hiérarchie pour améliorer les performances, mais ne dit pas comment.
La solution
Cet article présente les étapes nécessaires pour modifier votre structure hiérarchique parents-enfants afin d'avoir de vrais niveaux, de sorte que les agrégations fonctionnent et que vos performances soient aussi bonnes que celles que vous attendez des hiérarchies normales.
Ce processus est connu sous le nom d'aplatissement ou de normalisation de la hiérarchie parent-enfant.
Tout d'abord, examinons les données de notre source relationnelle.
[spoiler intro="Code" title="Sql Create Script"]
CREER TABLE [dbo] [Produits](
[ProductID] [int] NON NUL,
[ParentID] [int] NUL,
[Nom] [varchar](50) NON NUL,
CONSTRAINT [PK_Products] CLÉ PRIMAIRE CLUSTERED ([ProductID] ASC)
)
GO
insérer dans Products(ProductID, ParentID, Name) les valeurs(1, NULL, 'All')
insérer dans les valeurs de Products(ProductID, ParentID, Name)(2, 1, 'Fruit')
insérer dans Products(ProductID, ParentID, Name) les valeurs(3, 2, 'Red')
insérer dans Products(ProductID, ParentID, Name) les valeurs(4, 3, 'Cherry')
insérer dans les valeurs de Products(ProductID, ParentID, Name)(5, 3, 'Strawberry')
insérer dans Products(ProductID, ParentID, Name) les valeurs(6, 2, 'Yellow')
insérer dans les valeurs de Products(ProductID, ParentID, Name)(7, 6, 'Banana')
insérer dans les valeurs de Products(ProductID, ParentID, Name)(8, 6, 'Lemon')
insérer dans Products(ProductID, ParentID, Name) les valeurs(9, 1, 'Meat')
insérer dans les valeurs de Products(ProductID, ParentID, Name)(10, 9, 'Beef')
insérer dans les valeurs de Products(ProductID, ParentID, Name)(11, 9, 'Pork')
[/spoiler]
Pas une grande dimension, mais suffisamment pour démontrer la technique. Comme vous pouvez le voir, mes vrais produits sont tous au niveau des feuilles.
La stratégie est assez simple :
- Créer une vue pour séparer les membres en différents niveaux.
- Créer une nouvelle dimension en utilisant ces niveaux réels.
- Configurer la dimension pour qu'elle apparaisse comme la dimension parent-enfant originale, mais avec les performances d'une dimension normale.
Créer la vue
Nous voulons créer une vue dénormalisée des données. Pour ce faire, nous relions le produit à lui-même une fois pour chaque niveau. Cela signifie que nous devons connaître la profondeur maximale de la hiérarchie, mais celle-ci est souvent fixe, et nous intégrerons quelques niveaux supplémentaires pour la sécurité.
Les astuces sont ici :
- Utilisez coalesce() pour que nous obtenions toujours l'ID de niveau le plus bas sous les feuilles, jamais un NUL. Cela nous permet de rejoindre la table des faits au niveau le plus bas de notre hiérarchie.
- Laissez les colonnes Nom nulles en dessous des feuilles, cela nous permettra d'arrêter la hiérarchie au niveau de la feuille correcte dans chaque partie de la hiérarchie.
[spoiler intro="Code" title="Sql View Script"]
créer une vue dbo.ProductsFlattened
comme
sélectionnez P1.ProductID comme Level1ID,
P1.Nom comme Level1Name,
coalescent(P2.ProductID, P1.ProductID) comme Level2ID,
P2.Nom comme Level2Name,
coalescent(P3.ProductID, P2.ProductID, P1.ProductID) comme Level3ID,
P3.Nom comme Level3Name,
coalescent(P4.ProductID, P3.ProductID, P2.ProductID, P1.ProductID) comme Level4ID,
P4.Nom comme Level4Name,
coalescent(P5.ProductID, P4.ProductID, P3.ProductID, P2.ProductID, P1.ProductID) comme Level5ID,
P5.Nom comme Level5Name
de dbo.Products P0
à gauche rejoindre dbo.Products P1
sur P0.ProductID = P1.ParentID
à gauche rejoindre dbo.Products P2
sur P1.ProductID = P2.ParentID
à gauche rejoindre dbo.Products P3
sur P2.ProductID = P3.ParentID
à gauche rejoindre dbo.Products P4
sur P3.ProductID = P4.ParentID
à gauche rejoindre dbo.Products P5
sur P4.ProductID = P5.ParentID
où P0.ParentID est nul
[/spoiler]
On a le droit de faire tourner ça :
Nous pouvons évidemment actualiser cette vue pour créer d'autres niveaux selon les besoins, mais 5 suffisent pour l'instant.
La dimension
Ensuite, nous allons à BIDS, et nous ajoutons la vue à notre vue de la source de données, puis nous ajoutons une nouvelle dimension basée sur la vue.
Les étapes clés pour créer correctement la dimension sont les suivantes :
- Définissez l'attribut clé à Level5ID, et le nom à Level5Name.
- Créez un attribut pour chaque ID de niveau, et pour chacun d'eux, définissez la colonne Nom de manière appropriée.
- Créez une hiérarchie en utilisant ces attributs dans l'ordre.
- Pour chaque attribut, définissez AttributeHierarchyVisible to False.
- A chaque niveau de la hiérarchie, mettez HideMemberIf à NoName.
- Établir les relations d'attributs entre les niveaux.
Vous devriez aboutir à ce qui suit :
Structure des dimensions
Relations d'attributs
Si vous parcourez la dimension, vous verrez qu'elle ne va jamais jusqu'au niveau 5, même si elle existe. Cela est dû au fait que nous avons configuré l'option de masquage des membres et que nous avons renvoyé des NULL à notre avis.
Conclusion
Et c'est fait, vous pouvez maintenant rejoindre vos tableaux d'information au niveau le plus bas, construire votre cube comme d'habitude et bénéficier des avantages de l'agrégation en termes de performance !
Voir aussi
Un outil permettant d'atteindre le même résultat est disponible auprès du Codeplex, nous ne l'avons pas personnellement essayé mais il pourrait bien être un gain de temps. Il fonctionne de manière similaire à l'exemple ci-dessus, mais il est souvent utile de comprendre comment quelque chose fonctionne, même si vous choisissez de l'automatiser.
