Tag : Hiérarchie

Rapport Flexibilité, avec contrôle

XLCubed/ / Rapports et analyses

Parfois, nous voulons laisser les utilisateurs modifier la structure d'un rapport, mais nous voulons régir exactement ce qu'ils peuvent et ne peuvent pas faire. Bien que les grilles puissent être limitées à un niveau granulaire pour activer et désactiver des fonctionnalités, cette approche nécessite encore un certain degré de connaissance du produit de la part de l'utilisateur.

XLCubed fournit la formule XL3SetProperty(), qui permet la manipulation de nombreux objets de base tels que les grilles, les tranches et les petits multiplicateurs. Cela signifie que les utilisateurs de rapports peuvent disposer de simples sélections de trancheurs pour modifier la structure d'un rapport, ce qui est affiché dans un graphique, ou pour varier le type de graphique. Cela donne de la flexibilité au sein du rapport, mais ne nécessite aucune connaissance du produit de la part de l'utilisateur final, ce qui peut être crucial lors de la diffusion de rapports web à grande échelle.

Un exemple courant d'utilisation est celui où la hiérarchie à visualiser dans une grille doit changer en fonction de la mesure qu'un utilisateur sélectionne (selon la structure du cube, certaines mesures peuvent ne pas être applicables à toutes les hiérarchies). En général, cela doit être traité dans deux grilles, mais nous pouvons utiliser XL3SetProperty pour rassembler tout cela, et aussi pour donner le choix à l'utilisateur sur la vue Small Multiple Chart associée.

Le rapport final publié est présenté ci-dessous :

 

S1

 

Si l'utilisateur sélectionne une mesure "Internet", nous affichons la Géographie du client sur les lignes, alors qu'une mesure "Revendeur" devrait afficher le Type de revendeur sur les lignes. La même logique s'applique au tableau des petits multiples. Dans la capture d'écran ci-dessous, l'utilisateur a sélectionné la mesure "Bénéfice brut du revendeur" et le type de graphique "Colonne empilée". Vous pouvez voir que la hiérarchie des lignes a été modifiée, tout comme la répartition dans les différents graphiques, ce qui permet à l'utilisateur de modifier facilement sa vue des données à l'aide de simples boutons de sélection.

 

S2

 

Elle est mise en œuvre par le biais des points clés suivants :

  • Un tableau de recherche dans Excel pour déterminer la hiérarchie applicable à chaque mesure
  • Une liste Excel indiquant les types de graphiques disponibles - elle est utilisée dans le trancheur de types de graphiques
    • Le trancheur de cartes produit sa sélection dans la cellule $AG$10
  • Le trancheur de mesures est directement lié à la grille et au petit multiple, mais il transmet également sa sélection à une cellule Excel ($A$B4)
  • Un vlookup détermine la hiérarchie à utiliser en fonction de la mesure sélectionnée
  • Trois formules XL3SetProperty() contrôlent désormais ce qui est affiché en fonction des sélections de l'utilisateur :
    • $AB$7 - fixe les lignes de la grille
    • $AB$8 - fixe les petites colonnes multiples
    • $AB$7 - définit le type de graphique

 

Formules

 

Cette approche donne un accès approfondi aux principaux objets de rapport XLCubed et permet une flexibilité contrôlée dans les rapports fournis par le web et les téléphones portables. Aucune programmation n'est nécessaire, juste une compréhension de niveau intermédiaire d'Excel lui-même et de XLCubed.

Ce n'est qu'un exemple de ce que cette approche peut réaliser - elle n'est vraiment limitée que par l'imagination. Voir XL3SetProperty() pour plus de détails, ou contactez-nous si vous souhaitez obtenir l'exemple de cahier de travail.

Parent-Child Dimensions in Analysis Services – Performance Walkthrough

XLCubed/ / Données

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.