10 façons daméliorer les performances de Power Pivot

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

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

Une influence énorme sur les performances de Power Pivot est le nombre de colonnes que vous apportez ou importez 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 de colonnes supplémentaires « juste au cas où » — si vous n'êtes pas certain d'utiliser certaines colonnes, ne les importez pas. Ces colonnes sont assez faciles à ajouter plus tard si vous en avez besoin.

Plus de lignes signifie 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 que vous devez importer. Après tout, pourquoi importer 400 000 lignes de données alors que vous pouvez utiliser une simple clause WHERE et n'en importer que 100 000 ?

2Utilisez des vues au lieu de tables.

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

Bien que les tableaux soient plus transparents que les vues - vous permettant de voir toutes les données brutes et non filtrées - ils sont fournis avec toutes les colonnes et lignes disponibles, que vous en ayez besoin ou non. Pour conserver 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.

Les vues peuvent non seulement fournir des données plus propres et plus conviviales, mais également aider à rationaliser votre modèle de données Power Pivot en limitant la quantité de données que vous importez.

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 tables de dimensions qui se rapportent directement aux faits. Dans le monde des bases de données, cette configuration est un schéma en étoile, comme illustré.

Évitez de créer des modèles où les tables de dimension sont liées à d'autres tables de dimension.

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

La plupart des analystes Excel qui découvrent Power Pivot ont tendance à extraire des données brutes directement des tables de leurs serveurs de base de données externes. Une fois les données brutes dans Power Pivot, ils créent des colonnes et des mesures calculées pour transformer et agréger les données selon les besoins. Par exemple, les utilisateurs extraient 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 faire faire ce calcul à Power Pivot alors que le serveur principal aurait pu le gérer ? La réalité est que les systèmes de base de données principaux tels que SQL Server ont la capacité de façonner, d'agréger, de nettoyer et de transformer les 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 brutes, envisagez d'exploiter les requêtes, les vues et les procédures stockées pour effectuer autant de travail d'agrégation et d'analyse de données que possible. Cet effet de levier réduit la quantité de traitement que Power Pivot devra effectuer et améliore naturellement les performances.

5Attention aux colonnes avec des valeurs non distinctes.

Les colonnes qui ont un nombre élevé de valeurs uniques sont particulièrement difficiles 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 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 en dehors de votre modèle.

6 Limitez le nombre de segments dans un rapport.

Le slicer est l'une des meilleures nouvelles fonctionnalités de business intelligence (BI) d'Excel ces dernières années. À l'aide de slicers, vous pouvez fournir à votre public une interface intuitive qui permet un filtrage interactif de vos rapports et tableaux de bord Excel.

L'un des avantages les plus utiles du slicer est qu'il répond aux autres slicers, fournissant un effet de filtre en cascade. Par exemple, la figure illustre non seulement qu'un clic sur Midwest dans le segment Region 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 le 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 du 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 des segments uniquement sur les champs de dimension.

Les segments liés à des colonnes contenant de nombreuses valeurs uniques entraîneront souvent un impact plus important sur les performances que les colonnes ne contenant qu'une poignée de valeurs. Si un segment contient un grand nombre de tuiles, envisagez plutôt d'utiliser une liste déroulante Filtre de tableau croisé dynamique.

Sur une note similaire, assurez-vous de tailler les types de données de colonne à droite. Une colonne avec peu de valeurs distinctes est plus légère qu'une colonne avec un grand nombre 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 segments.

La désactivation du comportement de filtre croisé d'un segment empêche essentiellement ce segment de modifier les sélections lorsque d'autres segments sont cliqués. Cela évite à Power Pivot d'évaluer les titres dans le slicer désactivé, réduisant ainsi les cycles de traitement. Pour désactiver le comportement de filtrage 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 de colonnes calculées.

Utilisez des mesures calculées au lieu de 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 intrinsèquement avec d'autres colonnes du modèle, elles calculent chaque fois que le tableau croisé dynamique est mis à jour, qu'elles soient utilisées ou non. Les mesures calculées, en revanche, ne sont calculées qu'au moment de la requête.

Les colonnes calculées ressemblent aux colonnes normales dans la mesure où 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.

10Mise à niveau vers Excel 64 bits.

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

Power Pivot charge l'intégralité du modèle de données dans la RAM chaque fois que vous l'utilisez. Plus votre ordinateur a de RAM, moins vous voyez de problèmes de performances. La version 64 bits d'Excel peut accéder à une plus grande partie de la RAM de votre PC, garantissant qu'il dispose des ressources système nécessaires pour analyser des modèles de données plus volumineux. En fait, Microsoft recommande Excel 64 bits pour toute personne travaillant avec des modèles composés de millions de lignes.

Mais avant de commencer à installer précipitamment Excel 64 bits, vous devez répondre à ces questions :

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

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

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

Vos autres compléments cesseront-ils de fonctionner ?