[SSAS] VisualTotals et Excel 2007

Je voulais rappeler quelques informations au sujet de la gestion des VisualTotals sous Excel 2007. Pour rappel un VisualTotal – ou « total visuel » qui dit bien ce que c’est – consiste simplement à afficher comme total d’un niveau aggrégé une valeur dépendante de la sélection.

En clair, dans une PivotTable, si je n’affiche que les produits A, B et C dans ma dimension Produit avec la mesure NbVentes, j’aurai au niveau supérieur la somme des ventes des produits A, B et C et non pas de tous les produits de même niveau que A, B et C. Ce qui peut être voulu ou pas selon le type de rapports.

Cette fonctionnalité n’est pas désactivable en 2007, alors qu’elle l’était en 2003. C’est un effet de bord d’un changement de mode de requêtage qui intervient pour résoudre les problèmes de multisélection dans les filtres.

En AS 2000, pour filtrer (slicer) sur un set (par exemple quelques pays) il n’est pas possible d’écrire cela:

SELECT

WHERE ({[Pays].[Pays].[Pays].&[UK],[Pays].[Pays].&[FR]})

La multiselection dans le slicer n’est pas supportée. C’est une fonctionnalité 2005 et supérieure. En 2003 (PivotTables v10), les filtres sont donc implémentés à la « manière 2000 », – c’est pareil dans ProClarity en fait – les filtres sont générés dans des membres calculés et positionnés dans le slicer.

WITH MEMBER [Pays].[MonFiltre] AS
Aggregate

Aggregate ({[Pays].[Pays].[Pays].&[UK],[Pays].[Pays].&[FR]})
...
SELECT
...
WHERE
([Pays].[MonFiltre])

(Ce qui implique de fameux problèmes avec le CurrentMember – le CurrentMember de ma hiérarchie Pays est où dans le cas précédent? C’est un set! – bien explicités par Mosha dans son fameux papier).
Le fait d’autoriser cette multisélection dans le slicer n’a pas été exploité dans Excel 2007. Pour permettre des fonctionnalités de filtrage plus avancés en déléguant correctement les tâches de filtrage au moteur AS, Excel utilise les sous-requêtes (subselect) .La multisélection est donc gérée par un sous cube là aussi une nouvelle fonctionnalité introduite après 2000:

SELECT

FROM
(
SELECT
({[Pays].[Pays].[Pays].&[UK],[Pays].[Pays].&[FR]}) ON 0
FROM [MonCube]
)

L’effet de bord de gérer cette multisélection dans un sous-cube est donc de ne générer que des totaux visuels (VisualTotals): on ne ramène jamais les totaux du cube.

Pour faire cela, si vous en avez un besoin impérieux il existe une seule solution: créer des PivotTables en version précédente.

Cela peut se faire de trois manières:
– En VBA en créant une Macro qui créera la PivotTable en version 10
– En VSTO sur le même modèle (recréer un bouton « créer PivotTable » pour forcer l’utilisation de la version précédente, je l’ai fait, c’est assez élégant).
– En utilisant le mode de compatibilité d’Excel, qui va créer la PivotTable en version précédente
: attention cependant si vous enregistrez la feuille en compatibilité vers le mode standard, les PivotTables seront marquées pour mise à jour, et mises en version 12 au prochain Refresh, c’est à vous de l’interdire en… VBA en utilisant la propriété UpgradeOnRefresh des PivotTables.
(Merci au blog des développeurs d’Excel pour cette précision qui m’a évité de devenir fou🙂

En espérant que ce post ait pu être utile à quelques uns.
A bientôt!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s