Des requêtes Excel plus rapides pour Power BI et Azure Analysis Services

Si vous constatez que certaines requêtes sont lentes lorsque vous rapatriez, dans Excel, des données depuis PowerBI.com, Azure Analysis Services ou Analysis Services tabulaire, alors ce billet devrait vous intéresser.

Continuer à lire "Requêtes Excel plus rapides pour les services de Power BI et d'analyse azurée"

DAX Performance tips– lessons from the field

XLCubed supporte une interface glisser/déposer pour la création de rapports par rapport aux services d'analyse tabulaire depuis la première version du nouveau moteur. Il permet aux utilisateurs de créer facilement des rapports qui exécutent des requêtes DAX sur le cube, et nous avons souvent constaté de très bonnes performances chez les clients lorsque le MDX contre Tabular était la cause de longs rapports.

Ainsi, lorsque nous avons été contactés à SQL Pass à Seattle par des participants qui avaient un problème de performance du SSAS Tabular, nous étions optimistes quant à notre capacité à les aider.

Dans ce cas, l'entreprise voulait récupérer des milliers de lignes du cube au niveau transactionnel, et la première approche avait été d'utiliser des tableaux croisés dynamiques dans Excel. Pour atteindre le niveau le plus bas, ils ont croisé les niveaux les plus bas de toutes les hiérarchies sur la section des lignes, ce qui a donné le bon résultat, mais la performance était terrible, plusieurs requêtes prenant 20 minutes ou plus et d'autres ne revenant pas du tout.

Nous espérions que l'utilisation d'une table XLCubed fonctionnant en DAX serait la solution et avons créé le même rapport dans le concepteur. Malheureusement, si les performances étaient un peu meilleures, elles étaient encore loin d'être acceptables ; le modèle était grand, et le nombre de colonnes combiné à leur cardinalité signifiait que beaucoup de travail était effectué sur le serveur.

Le générateur DAX de XLCubed essayait de croiser toutes les valeurs de chaque colonne, ce qui avait bien fonctionné pour nos autres clients. Mais lorsqu'il y a une douzaine de colonnes incluant l'ID de la transaction, les choses ne se passent pas si bien. Le DAX en lui-même n'est pas une solution miracle et les modèles tabulaires SSAS peuvent rencontrer des problèmes de performance sur des données de bas niveau - nous avions besoin d'une nouvelle approche.

Après quelques recherches, nous avons discuté de la question et de nos réflexions avec nos amis de SQLBI et avons déterminé qu'au lieu d'utiliser la fonction cross-join, nous voulions une option permettant d'utiliser Summarize(), car celle-ci n'utilise que les lignes de la base de données et peut accéder aux colonnes liées au tableau récapitulatif qui sont nécessaires pour le rapport.

Comme le rapport du client contenait l'ID de la transaction, le résultat n'a pas été agrégé, même si nous avons utilisé la fonction de résumé. Mais nous voulions également ajouter un véritable rapport de transaction, en utilisant la fonction Related().

Enfin, SQL 2016 ajoute quelques nouvelles fonctions, SummarizeColumns() et SelectColumns(), qui sont toutes deux utiles pour ce type de rapport, mais offrent de meilleures performances que les anciens équivalents.

Le résultat final dans XLCubed est une nouvelle option pour les tables DAX permettant aux utilisateurs de définir le type de rapport qu'ils veulent exécuter, et quelques changements internes afin que XLCubed utilise automatiquement la fonction DAX la plus efficace là où elle est disponible.

Une version bêta a été envoyée aux utilisateurs professionnels et les résultats ont été fantastiques. Le rapport qui avait duré plusieurs minutes s'est maintenant terminé en quelques secondes, et 20 minutes sont passées à 15 secondes - nous avons eu des utilisateurs très heureux !

Les changements seront apportés dans la prochaine version de XLCubed afin que tous nos clients puissent bénéficier des améliorations. C'est toujours agréable quand une demande d'un client contribue à améliorer le produit pour tout le monde.

Un exemple de changement de syntaxe est présenté ci-dessous

Avant :

 

ÉVALUER
FILTRE (
    ADDCOLUMES (
        FILTRES DE GARDE (
            CROSSJOIN ( VALEURS ("Client" [Éducation] ), VALEURS ("Produit" [Couleur] )
        ),
        "Unités totales Internet", "Ventes par Internet" [Unités totales Internet],
        "Ventes totales sur Internet", "Ventes sur Internet" [Ventes totales sur Internet]
    ),
    PAS ISBLANC ( [Unités totales Internet] )
)
ORDRE DE
    Client" [Éducation],
    Produit" [Couleur]

Après :

 

ÉVALUER
FILTRE (
    ADDCOLUMES (
        FILTRES DE GARDE (
            RÉSUMÉ ("Ventes sur Internet", "Client" [Éducation], "Produit" [Couleur] )
        ),
        "Unités totales Internet", "Ventes par Internet" [Unités totales Internet],
        "Ventes totales sur Internet", "Ventes sur Internet" [Ventes totales sur Internet]
    ),
    NOT ISBLANK ( [Internet Total Units] ) || NOT ISBLANK ( [Internet Total Sales] )
)
ORDRE DE
    Client" [Éducation],
    Produit" [Couleur]

XLCubed V7 & SQL Server 2012

Le serveur SQL 2012 a récemment été mis à la disposition des fabricants, et chez XLCubed, nous sommes bien placés pour profiter de toutes les nouveautés de 2012.

SQL 2012 fournit de l'intelligence économique sous l'égide de BISM (Business Intelligence Semantic Model). Le BISM existe cependant sous différentes formes :

  • BISM Multidimensionnel
    • (Dernière version des services d'analyse tels que nous les connaissons)
  • BISM Tabulaire
    • Vertipaq en mémoire
    • Demande directe

Pour les outils clients, BISM Multidimensionnel est en grande partie identique à la connexion aux versions existantes des Services d'analyse, le MDX étant le langage d'interrogation. Pour XLCubed, nous pouvons tirer parti de ce que nous avons déjà à cet égard, et la transition est transparente.

Le BISM tabulaire est cependant différent. Si vous choisissez de déployer en mémoire dans Vertipaq, les outils clients peuvent toujours utiliser MDX, et n'ont donc pas besoin de changements importants, si ce n'est pour gérer l'environnement de données tabulaires plutôt que hiérarchiques. Cependant, si le déploiement est Direct Query (par exemple pour la BI en temps réel), le seul langage d'interrogation disponible est DAX.

Il existe des cas d'utilisation optimale pour les différentes options de déploiement, mais il est juste de dire qu'il existe actuellement une certaine confusion dans l'espace sur les mérites relatifs de chacune. Nous essaierons d'apporter un peu de lumière et d'orientation dans les semaines et les mois à venir. En tant que produit, il est important pour nous de soutenir et d'étendre la gamme complète des options de déploiement de la BI 2012, et de les rendre disponibles et accessibles à nos clients. C'est exactement ce que nous avons fait pour la version 7.

XLCubed v7, qui sortira le mois prochain, est un client à la fois pour MDX et DAX, et en tant que tel fournit une interface client cohérente dans Excel et sur le Web qui peut accéder à n'importe lequel des modèles de déploiement SQL 2012 pour la BI. Nous ajoutons également un environnement de reporting SQL relationnel beaucoup plus riche.

Nous sommes très satisfaits des réactions que nous avons reçues jusqu'à présent et si vous souhaitez tester la version bêta, veuillez nous contacter à l'adresse suivante : beta@xlcubed.com .

Nous attendons avec impatience la sortie du produit le mois prochain, et nous le présenterons en avant-première à SQL Server Connections la semaine prochaine à Las Vegas.

 

Solve order shenanigans

Aujourd'hui, je vais tenir un blog sur un problème que nous avons récemment résolu dans un cube de client, une erreur dans le script Mdx qui est très facile à faire si vous ne faites pas attention.

Nous allons présenter un exemple simple dans AdventureWorks (quoi d'autre ?) pour illustrer le problème.

Le client avait déjà ajouté un calcul à son cube pour montrer la croissance d'une année sur l'autre. La formule est la suivante :

Créer le membre CurrentCube [mesures] [Delta à l'année précédente] en tant que
(
    ([Mesures]. [Montant des ventes sur Internet])
    -
    ([Mesures]. [Montant des ventes sur Internet],
        Période Parallèle(
            [Date]. [Calendrier]. [Année civile],
            1,
            [Date]. [Calendrier].Membre actuel
        )
    )
)
/
    ([Mesures]. [Montant des ventes sur Internet],
        Période Parallèle(
            [Date]. [Calendrier]. [Année civile],
            1,
            [Date]. [Calendrier].Membre actuel
        )
    )
, Format_String = "0.00%" ;

(certaines vérifications d'erreurs ont été supprimées pour plus de clarté)

Cette capture d'écran montre quelques grilles XLCubed simples montrant la valeur réelle, et en dessous du pourcentage de variation. J'ai ajouté un calcul Excel pour montrer que les résultats sont conformes aux attentes.

Plus tard au cours du développement du cube, le client a ajouté un membre calculé dans sa dimension Produit, qui donne un total excluant une des catégories de produits.

Pour reproduire cela, je vais ajouter un calcul pour "All Ex Bikes" :

Créer un membre 
CurrentCube. [Produit]. [Catégories de modèles de produits]. [Tous les produits]. [Tous les vélos Ex].
comme
(
    ([Produit]. [Catégories de modèles de produits]. [Tous les produits])
    -
    ([Produit]. [Catégories de modèles de produits]. [Catégorie].& [1])
) ;

Et si nous refaisons le rapport, nous obtenons ce qui suit.

Remarquez la cellule que j'ai mise en évidence. Le calcul "All Ex Bikes" fonctionne bien sur la mesure normale, mais il donne un chiffre totalement faux pour le calcul du pourcentage. Que se passe-t-il ?

Le problème est que, dans la cellule mise en évidence, les services d'analyse doivent tenir compte de deux calculs lorsqu'ils élaborent le résultat.

  • Comparer cette année à l'année dernière
  • Obtenez le "Grand Total", et soustrayez "Vélos"

Comme le nombre de retours est de 1,85 %, nous pouvons voir que le service d'analyse a choisi la deuxième option, "Grand Total" - "Vélos".

Ce que nous voulons vraiment, c'est que le calcul soit fait en obtenant le sous-total, puis en faisant la variation en pourcentage sur cette base.

Heureusement, la solution était simple. Les services d'analyse effectueront les calculs dans l'ordre où ils se trouvent dans le script Mdx. Pour résoudre le problème, nous avons donc simplement déplacé la nouvelle définition de "All Ex Bikes" au-dessus du calcul du pourcentage.

Le nombre de retours correspond maintenant à nos attentes.

L'ordre de passage ou de résolution peut être un sujet complexe, vous devrez donc être prudent.

Dans ce cas, le numéro est totalement faux, donc il était facile à repérer, mais certains insectes seront beaucoup plus subtils, alors attention !

Ranking, Sorting and Filtering

Une fois que les membres du cube ont été réintégrés dans un rapport de grille, nous devons souvent exclure ou modifier l'ordre de l'ensemble des résultats afin de fournir des informations plus significatives. Le langage MDX (Multidimensional Expressions) comprend des opérateurs très utiles pour filtrer (FILTER), trier (ORDER) et classer (TOPCOUNT/BOTTOMCOUNT) les membres des dimensions. Ces fonctions peuvent être très utiles, même pour les utilisateurs chevronnés de XLCubed. C'est pourquoi, dans la version 6, nous avons introduit une nouvelle fonction "Sélections avancées de membres" pour faciliter l'accès à cette partie puissante des services d'analyse de Microsoft.

Grâce à cette nouvelle fonctionnalité, nous pouvons imbriquer et combiner ces opérations pour répondre à des questions commerciales complexes (pour des opérations plus simples, vous pouvez cliquer avec le bouton droit de la souris sur un membre dans la grille et utiliser le menu "Appliquer" pour effectuer un classement, des filtres et un tri simples).

Filtrage

Passons donc en revue un exemple simple de filtrage. Disons, par exemple, que nous voulons trouver les produits au niveau de la clé de produit qui se sont vendus à plus de 25 unités en 2003, premier trimestre, et afficher les chiffres de vente de ces sous-catégories en 2003 et ses trimestres.

  1. Commencez par cliquer sur l'élément du ruban Grille (ou l'élément de menu XLCubed > Design Grid dans Excel 2003 et ci-dessous), et sélectionnez le fichier cube Vente par Internet
  2. Faites glisser la période du calendrier vers les colonnes et le produit vers les lignes. Vous pouvez également faire glisser toute autre hiérarchie vers les en-têtes. Dans l'image d'exemple ci-dessous, les mesures et le client y ont été ajoutés.

  1. Cliquez sur la hiérarchie des produits pour que ses détails apparaissent dans le panneau en bas à droite.
  2. Faites glisser le niveau de la clé produit vers la droite de la boîte de dialogue. Vous pouvez passer de la vue des membres à la vue des niveaux en cliquant sur l'icône Afficher les niveaux ().
  3. Cliquez sur l'onglet Avancé pour afficher le volet de sélection avancée :

  1. Cliquez sur le menu déroulant Membres et choisissez Filtrer le résultat :


  1. Cliquez sur le contrôle d'édition de la période du calendrier dans la grille pour modifier sa sélection en fonction du membre souhaité (2003, 1er trimestre) :

  1. Sélectionnez la case d'option Cette mesure, et sélectionnez Commander la quantité comme mesure souhaitée.
  2. Changez l'opération en >, et tapez 25 dans le champ d'édition à droite :

  1. Cliquez sur OK. Le nouveau filtre est affiché dans l'onglet des sélections avancées :

  1. Cliquez à nouveau sur OK pour lancer le rapport - la grille indique les membres qui correspondent à nos critères :

 

Nous pouvons donc voir les résultats, en filtrant par le premier trimestre 2003, mais en affichant les valeurs pour All Time (ou toute autre période que nous souhaitons utiliser). Nous aurions également pu utiliser le sélecteur de plage :    pour piloter la période en sélectionnant dans une plage Excel et notre grille se rafraîchit automatiquement à chaque changement de valeur de pilotage.

Classement

Ajoutons maintenant un classement pour trouver les 8 produits les moins vendus au niveau de la clé de produit qui se sont vendus à plus de 25 unités au premier trimestre :

  1. Afficher la boîte de dialogue de l'éditeur de la hiérarchie des produits
  2. Cliquez sur l'icône Résultat du classement () dans l'onglet des sélections avancées pour afficher la boîte de dialogue Modifier le classement
  3. Sélectionnez le bouton radio "Bottom" et tapez 8 dans le champ d'édition
  4. Sélectionnez 2003, 1er trimestre pour la hiérarchie de la période du calendrier dans la grille ci-dessous :

Nous avons maintenant le filtre, suivi par le classement :

 

Faire fonctionner la grille : seuls les 8 membres les plus bas sont renvoyés

 

Triage

Trions maintenant le rapport sur une autre dimension - par exemple, l'ordre décroissant des ventes du premier trimestre.

  1. Afficher l'éditeur de hiérarchie pour la hiérarchie des produits en double-cliquant sur l'étiquette du produit dans la grille
  2. Si ce n'est pas déjà visible, sélectionnez l'onglet Avancé
  3. Cliquez sur le bouton Trier le résultat de la barre d'outils ()
  4. Changer la sélection de la période du calendrier pour 2003, premier trimestre :

  1. Cliquez sur le bouton radio Trier par ordre décroissant (9-1)
  2. Cliquez sur OK. Le nouveau tri est affiché dans l'onglet des sélections avancées
Cliquez à nouveau sur OK pour lancer le rapport

 

Joindre les résultats

Il est également possible d'associer différents résultats : combiner les deux ensembles (UNION), exclure des membres (SAUF) et faire revenir des membres communs (INTERSECT).

Nous pourrions donc également ajouter les 10 premiers produits à la grille, en plus des 8 derniers produits. Commencez par ajouter une autre sélection de membres en utilisant le bouton "Ajouter une liste de membres" de la barre d'outils :

Comme auparavant, nous sélectionnons la liste des membres à classer (dans ce cas le niveau de la clé de produit) et nous sélectionnons ensuite l'opération que nous voulons effectuer, un Top 10 :

Il existe plusieurs options pour décider comment combiner les listes, nous nous en tiendrons à "Ajouter" :

 

 

Et nous obtenons les deux résultats combinés :


La fonction "Sélections avancées des membres" offre donc une grande partie de la puissance des services d'analyse de manière simplifiée - pour essayer cette fonction par vous-même, vous pouvez commencer par télécharger XLCubed.

Sql Server “Denali” CTP3 – first impressions…

Microsoft a récemment publié son troisième CTP de Denali : la prochaine version de SQL Server (SQL Server 2011). Voici donc quelques premières réflexions, maintenant qu'il est plus largement disponible.. :

La première chose à examiner est le nouveau mode tabulaire pour les services d'analyse (par opposition au mode multidimensionnel traditionnel, qui est toujours disponible). Il s'agit de la version serveur du moteur VertiPaq, vue pour la première fois dans le module complémentaire PowerPivot, qui fait passer le moteur du statut d'outil personnel/équipe à celui d'outil d'organisation/entreprise.

Cela signifie que les TI vont s'impliquer (et les gens peuvent ne pas être d'accord sur ce qu'ils pensent de cela !), mais que le partage des rapports devrait être plus facile car les données sont centralisées. Dans le passé, le rapport contenait toutes les données, ce qui pouvait donner lieu à de très gros classeurs, ou vous publiiez sur SharePoint, ce que tout le monde n'était pas en mesure de faire.

Les cubes peuvent être interrogés à l'aide de MDX, ce qui est idéal pour un fournisseur d'applications frontales comme nous, et XLCubed fonctionne sans problème avec le CTP. Les fonctionnalités existantes fonctionnent sans problème et, en tant que partenaires Gold de Microsoft, nous travaillons en étroite collaboration avec les versions pour utiliser toutes les fonctionnalités de la RTM.

Nous avons porté quelques cubes existants sur la nouvelle architecture et une première impression est qu'il va falloir supprimer des colonnes ou utiliser des perspectives pour que les choses restent sensées pour les utilisateurs finaux, vous pouvez rapidement vous retrouver avec des centaines d'attributs.

La capacité de créer des hiérarchies était souvent demandée dans PowerPivot, et heureusement, elle existe maintenant. Cela devrait permettre de simplifier de nombreux cubes.

Attribut-tastique

 

Les complexités du MDX dissuadent la plupart des utilisateurs professionnels d'essayer de l'utiliser directement, tandis que les similitudes du DAX avec les fonctions d'Excel signifient qu'il est plus facile pour les utilisateurs de créer des formules à la volée. Nous allons nous pencher sur la meilleure façon de faire connaître ces possibilités aux utilisateurs dans les mois à venir...

Des comptages distincts plus faciles et des calculs de date intégrés sont les candidats évidents, mais il en existe un certain nombre d'autres que nous pensons pouvoir rendre plus accessibles à la majorité des utilisateurs.

C'est certainement une démarche intéressante, et il faut s'habituer à penser en tableaux et en colonnes plutôt qu'en modèle multidimensionnel, ce qui est plus naturel pour certaines personnes.

Il sera également intéressant de voir comment le MDX et le DAX sont intégrés. Le serveur Tabular supporte les deux langues pour les requêtes. Actuellement, en utilisant MDX, vous pouvez utiliser la syntaxe "Avec membre" pour créer des membres envoyés au serveur Tabular. Pourriez-vous déclarer un calcul DAX de la même manière ?

Parent-Child Dimensions in Analysis Services – Performance Walkthrough

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.

PowerPivot, SQL R2, Sharepoint 2010, Office 2010.

Nous utilisons donc PowerPivot depuis un certain temps déjà, et Office 2010 fait partie de nos vies depuis un certain temps. Je vais utiliser ce blog pour répondre à certaines des questions qui reviennent sans cesse dans les conversations avec nos clients :

1. XLCubed fonctionne-t-il avec Excel (Office 14) 2010 ?

a. Oui, nous l'utilisons depuis la première version du CTP et chaque version depuis lors.

2. Puis-je utiliser XLCubed Web avec SharePoint 2010 ?

a. Oui, la publication sur le web et l'intégration des rapports dans votre site SharePoint fonctionnent exactement de la même manière que dans les versions précédentes.

3. XLCubed se connecte-t-il à PowerPivot ?

a. Oui, XLCubed se connecte aux cubes publiés par PowerPivot, et nos outils clients peuvent être utilisés pour construire des rapports et des tableaux de bord à partir de ceux-ci.

4. Puis-je créer des rapports à partir de SQL Server R2 en utilisant XLCubed ?

a. Oui, cela fonctionnera très bien, tout comme vous pouvez construire des rapports à partir de versions antérieures de SQL ou d'autres sources relationnelles. (voici un exemple)

PowerPivot dans le monde réel

L'équipe des services a travaillé à la migration de certains de nos modèles internes et de nos bases de données d'échantillons vers un environnement PowerPivot - en examinant les avantages et les inconvénients, en utilisant le DAX plutôt que le MDX pour effectuer certains calculs. Les résultats ont été variés, il a été intéressant de voir que certaines fonctionnalités que nous avions depuis un certain temps (comme les formules de cube, les trancheurs et les paramètres web) apparaissent de manière similaire dans PowerPivot.

Il est clair que PowerPivot n'est pas un remplacement complet des services d'analyse, mais il a certainement un rôle à jouer dans les solutions tactiques, dans l'analyse des utilisateurs et, selon nous, dans les prototypes de RAD pour les mises en œuvre à plus grande échelle des systèmes d'armes. Il ne s'aventure pas dans le vide laissé par la planification PerformancePoint (comme beaucoup le pensaient au début de 2009) - nous avons décidé d'aborder ce domaine avec la suite XLCubed PM qui utilise en mémoire des cubes OLAP et/ou des services d'analyse.

Essai de certains des outils

Voici quelques jeux de téléchargement que vous pouvez essayer, mais prenez bien note des spécifications et des exigences matérielles pour les jeux MS :

La machine virtuelle du travailleur de l'information 2010

S'inscrire et télécharger l'Office 2010

PowerPivot 32Bit, 64Bit

Évaluation XLCubed

Si vous souhaitez effectuer une évaluation par rapport à vos propres données - contactez l'équipe produit de XLCubed pour des éditions d'évaluation ou si vous souhaitez essayer une preuve de concept ou un prototype sans risque, contactez l'équipe de consultants de XLCubed.

Cube Design – meeting the business needs

 

Suite à notre précédent billet sur notre blog concernant certains des problèmes de performance courants des cube que nous avons rencontrés le mois dernier, j'ai pensé que je devais mentionner certains des problèmes non techniques que nous rencontrons assez souvent. Dans un cas, après avoir fait quelques essais et réglé les problèmes de performance du cube, nous avons dû nous poser la question suivante : le cube fait-il ce qu'il doit faire ? (Bien sûr, nous avons d'abord posé cette question, mais la priorité était de régler les performances actuelles du cube). Est-ce qu'il répond aux exigences de l'entreprise ? Il ne sert à rien d'avoir le cube le plus complexe qui utilise toutes les meilleures fonctionnalités s'il ne peut pas répondre aux questions des utilisateurs.

Dans les rapports, nous avons vu des exemples où les clients ont imbriqué quatre ou cinq attributs pour construire les effets d'une hiérarchie, ou ont lancé d'énormes requêtes puis les ont regardées pour obtenir les données dont ils avaient besoin, ou ont ramené 12 colonnes de données et ont calculé manuellement l'année à ce jour, ou n'ont pas de hiérarchies qui reflètent les regroupements de membres couramment utilisés, ou n'ont pas de noms de membres formatés de la manière dont l'entreprise a besoin. Pour nous, cela n'est pas correct.

Les utilisateurs peuvent sembler ne pas trop s'en soucier s'ils ne savent pas comment le cube pourrait fonctionner ou s'il fonctionne assez vite pour rapporter d'énormes ensembles de résultats qu'ils peuvent manipuler eux-mêmes - mais cela n'annule-t-il pas l'intérêt d'avoir un cube et votre investissement dans celui-ci ? Les consommateurs du cube doivent pouvoir disposer rapidement de données précises et, surtout, appropriées, d'une manière logique.

La conception et la construction du cube consiste à comprendre les besoins de l'entreprise et des utilisateurs, puis à construire le cube et les processus associés, c'est-à-dire avant même de commencer à construire les rapports et à transmettre les informations en utilisant de bonnes pratiques de visualisation des données.

Trop souvent, nous constatons une volonté d'utiliser les dernières technologies, les widgets les plus flashy, les effets 3D et d'ombrage les plus cool sur les rapports, jusqu'aux cubes et aux bases de données, avec toutes les hiérarchies ou types de mesures imaginables, mais sans grande ressemblance avec ce que les utilisateurs ont besoin de voir.

Je ne vous cacherai pas que nous sommes très fiers de nos compétences et de notre expérience pour garantir à nos clients non seulement un excellent système technique, mais aussi un système qui répond à leurs besoins. Si vous souhaitez parler à l'un des membres de l'équipe pour savoir comment il peut vous aider, vous trouverez nos coordonnées ici.

Common Analysis Services Performance Issues

Un petit billet de l'équipe des services ici à XLCubed sur le blog sur certains problèmes de performance avec le SSAS que nous avons encore vu récemment. Avec la puissance de traitement et la mémoire disponibles, il est assez facile de construire un cube rapide - à la fois pour les performances des requêtes et le temps de traitement. Il est également facile d'être laxiste dans la conception du cube, d'ignorer les avertissements et les directives de meilleures pratiques, et de se retrouver avec un cube qui a l'air concis, qui est soigné et intelligent mais qui fonctionne terriblement bien pour les utilisateurs finaux.

Nous en avons rencontré quelques exemples sur des sites de clients au cours du mois dernier, et il y a des problèmes communs qui semblent toujours surgir - les rectifier a normalement un impact très positif. Les trois coupables les plus fréquents que nous voyons sont

Dimensions parents-enfants - Les dimensions parents-enfants sont agréables et faciles à construire et à utiliser. Cependant, comme vous ne pouvez pas construire d'agrégats qui incluent une dimension parent-enfant, cela peut donner un cube peu performant ! Essayez d'aplatir les dimensions et évaluez exactement pourquoi une dimension parent-enfant est nécessaire et utilisée. Ils ne sont pas la seule option..

Opérateurs Unary, Custom-roll ups - nous avons vu des cas où ceux-ci ont été inclus par défaut dans chaque dimension d'un cube. S'ils ne sont pas nécessaires, laissez-les de côté ! Si vous pouvez utiliser un rollup personnalisé ou un opérateur unaire en effectuant un simple travail dans le processus ETL, il est peut-être préférable de le faire d'abord.

Si les performances de votre requête sont mauvaises, essayez de supprimer tous les opérateurs unitaires et les rollups personnalisés, puis testez à nouveau le cube. Comment sont les performances actuelles ? Il devrait être nettement plus rapide - évaluez et examinez la nécessité des opérateurs unitaires et des rollups personnalisés et voyez si le même effet peut être obtenu différemment (par exemple dans la couche ETL)

Données mises en cache ou non - En gros, le cube recalcule-t-il et redemande-t-il sans cesse des chiffres ou peut-il réutiliser les résultats ? Utilisez le profiler pour vérifier les données en cache ou non lorsque vos requêtes sont en cours d'exécution. Nous avons vu tant de fois des requêtes n'utilisant pas le cache parce que l'AS n'avait pas assez de mémoire disponible ou que des opérateurs volatiles comme now() étaient utilisés dans les calcs mdx.

La résolution des problèmes ci-dessus a eu un impact massif - les rapports qui prenaient jusqu'à 3 minutes pour être exécutés ne duraient plus que quelques secondes, les utilisateurs pouvaient commencer à utiliser l'application correctement pour la première fois, mais la correction des performances n'était peut-être qu'une partie de la tâche. Le cube doit bien sûr avoir été conçu pour répondre aux exigences de l'entreprise, mais c'est un autre blog...