10 Façons d'améliorer les performances de Power Pivot - mannequins

Lorsque vous publiez des rapports Power Pivot sur le Web, vous avez l'intention votre public la meilleure expérience possible. Une grande partie de cette expérience consiste à s'assurer que la performance est bonne. Le mot performance (en ce qui concerne les applications et les rapports) est généralement synonyme de rapidité - ou de rapidité avec laquelle une application effectue certaines actions telles que l'ouverture dans le navigateur, l'exécution de requêtes ou le filtrage.

1Limite le nombre de lignes et de colonnes dans vos tables de modèles de données.

Une influence énorme sur les performances de Power Pivot est le nombre de colonnes que vous amenez, ou import, dans le modèle de données. Chaque colonne que vous importez est une dimension supplémentaire que Power Pivot doit traiter lors du chargement d'un classeur. N'importez pas des colonnes supplémentaires "juste au cas où" - si vous n'êtes pas sûr d'utiliser certaines colonnes, ne les faites pas entrer. Ces colonnes sont assez faciles à ajouter plus tard si vous trouvez que vous en avez besoin.

Plus de lignes signifient plus de données à charger, plus de données à filtrer et plus de données à calculer. Évitez de sélectionner une table entière si vous n'êtes pas obligé de le faire. Utilisez une requête ou une vue dans la base de données source pour filtrer uniquement les lignes à importer. Après tout, pourquoi importer 400 000 lignes de données quand vous pouvez utiliser une simple clause WHERE et en importer seulement 100 000?

2Utilisez des vues au lieu de tables.

En parlant de points de vue, pour les meilleures pratiques, utilisez les vues autant que possible.

Bien que les tableaux soient plus transparents que les vues - vous permettant de voir toutes les données brutes non filtrées - ils sont fournis avec toutes les colonnes et lignes disponibles, que vous en ayez besoin ou non. Pour garder votre modèle de données Power Pivot à une taille gérable, vous êtes souvent obligé de prendre l'étape supplémentaire de filtrer explicitement les colonnes dont vous n'avez pas besoin.

3Évitez les relations à plusieurs niveaux.

Le nombre de relations et le nombre de couches de relations ont un impact sur les performances de vos rapports Power Pivot. Lors de la construction de votre modèle, suivez les meilleures pratiques et disposez d'une table de faits unique contenant principalement des données numériques quantitatives (faits) et des tableaux de dimensions qui se rapportent directement aux faits. Dans le monde de base de données, cette configuration est un schéma en étoile, comme indiqué.

Évitez de créer des modèles dans lesquels les tables de dimension se rapportent à d'autres tables de dimension.

4Laissez les serveurs de base de données back-end faire le craquement.

La plupart des analystes Excel qui découvrent Power Pivot ont tendance à extraire les données brutes directement des tables de leurs serveurs de bases de données externes. Une fois les données brutes dans Power Pivot, elles construisent des colonnes et des mesures calculées pour transformer et agréger les données selon les besoins.Par exemple, les utilisateurs génèrent généralement des données sur les revenus et les coûts, puis créent une colonne calculée dans Power Pivot pour calculer les bénéfices.

Alors pourquoi Power Pivot fait-il ce calcul quand le serveur back-end aurait pu le gérer? La réalité est que les systèmes de base de données back-end tels que SQL Server ont la capacité de façonner, agréger, nettoyer et transformer des données beaucoup plus efficacement que Power Pivot. Pourquoi ne pas utiliser leurs puissantes capacités pour masser et façonner les données avant de les importer dans Power Pivot?

Plutôt que d'extraire des données de table brute, envisagez de tirer parti des requêtes, des vues et des procédures stockées pour effectuer autant de tâches d'agrégation et de calcul de données que possible. Cet effet de levier réduit la quantité de traitement que Power Pivot devra faire et améliore naturellement les performances.

5 Prenez garde aux colonnes avec des valeurs non distinctes.

Les colonnes ayant un nombre élevé de valeurs uniques sont particulièrement dures sur les performances de Power Pivot. Les colonnes telles que l'ID de transaction, l'ID de commande et le numéro de facture sont souvent inutiles dans les rapports et les tableaux de bord Power Pivot de haut niveau. Donc, à moins qu'ils ne soient nécessaires pour établir des relations avec d'autres tables, laissez-les hors de votre modèle.

6Limite le nombre de segments dans un rapport.

La trancheuse est l'une des meilleures nouvelles fonctionnalités de Business Intelligence (BI) d'Excel ces dernières années. Grâce aux trancheurs, vous pouvez fournir à votre audience une interface intuitive qui permet un filtrage interactif de vos rapports et tableaux de bord Excel.

L'un des avantages les plus utiles de la trancheuse est qu'elle réagit aux autres trancheuses, fournissant un effet de filtre en cascade. Par exemple, la figure illustre non seulement que le fait de cliquer sur Midwest dans la région Slicer filtre le tableau croisé dynamique, mais que le segment Market répond également en mettant en évidence les marchés qui appartiennent à la région Midwest. Microsoft appelle ce comportement de filtrage croisé.

Aussi utile que soit la trancheuse, elle est malheureusement extrêmement mauvaise pour les performances de Power Pivot. Chaque fois qu'un segment est modifié, Power Pivot doit recalculer toutes les valeurs et mesures dans le tableau croisé dynamique. Pour ce faire, Power Pivot doit évaluer chaque tuile dans le segment sélectionné et traiter les calculs appropriés en fonction de la sélection.

7Créez les trancheurs uniquement dans les champs de cote.

Les segments liés à des colonnes contenant de nombreuses valeurs uniques génèrent souvent un plus grand impact sur les performances que les colonnes contenant uniquement une poignée de valeurs. Si un segment contient un grand nombre de tuiles, utilisez plutôt une liste déroulante Filtre de tableau croisé dynamique.

Sur une note similaire, assurez-vous de bien dimensionner les types de données de colonne. Une colonne avec peu de valeurs distinctes est plus légère qu'une colonne avec un nombre élevé de valeurs distinctes. Si vous stockez les résultats d'un calcul à partir d'une base de données source, réduisez le nombre de chiffres (après la virgule) à importer. Cela réduit la taille du dictionnaire et, éventuellement, le nombre de valeurs distinctes.

8Désactivez le comportement du filtre croisé pour certains trancheurs.

La désactivation du comportement du filtre croisé d'un segment empêche essentiellement ce dernier de modifier les sélections lorsque d'autres segments sont cliqués.Cela évite à Power Pivot d'évaluer les titres de la trancheuse désactivée, ce qui réduit les cycles de traitement. Pour désactiver le comportement du filtre croisé d'un segment, sélectionnez Paramètres du segment pour ouvrir la boîte de dialogue Paramètres du segment. Ensuite, désélectionnez simplement l'option Indiquer visuellement les éléments sans données.

9Utilisez des mesures calculées au lieu des colonnes calculées.

Utilisez des mesures calculées au lieu des colonnes calculées, si possible. Les colonnes calculées sont stockées en tant que colonnes importées. Étant donné que les colonnes calculées interagissent de manière inhérente avec les autres colonnes du modèle, elles calculent chaque fois que la table pivot est mise à jour, qu'elle soit utilisée ou non. D'autre part, les mesures calculées ne calculent qu'au moment de la requête.

Les colonnes calculées ressemblent à des colonnes régulières en ce sens qu'elles occupent toutes deux de l'espace dans le modèle. En revanche, les mesures calculées sont calculées à la volée et ne prennent pas de place.

10Mettez à niveau vers Excel 64 bits.

Si vous continuez à rencontrer des problèmes de performance avec vos rapports Power Pivot, vous pouvez toujours acheter un meilleur PC - dans ce cas, en effectuant une mise à niveau vers un PC 64 bits avec Excel 64 bits installé.

Power Pivot charge l'ensemble du modèle de données dans la mémoire vive lorsque vous travaillez avec. Plus la RAM de votre ordinateur est importante, moins vous avez de problèmes de performances. La version 64 bits d'Excel peut accéder à plus de RAM de votre PC, en s'assurant qu'il dispose des ressources système nécessaires pour croquer des modèles de données plus volumineux. En fait, Microsoft recommande Excel 64 bits pour tous ceux qui travaillent avec des modèles composés de millions de lignes.

Mais avant de commencer à installer rapidement Excel 64 bits, vous devez répondre aux questions suivantes:

Avez-vous déjà installé Excel 64 bits?

Vos modèles de données sont-ils assez grands?

Avez-vous un système d'exploitation 64 bits installé sur votre PC?

Vos autres compléments cesseront-ils de fonctionner?