PowerPivot pour Excel : pagination et projections

Au cours de ce billet, nous nous intéressons à nouveau à l’extension PowerPivot pour Microsoft Excel 2010, et, plus particulièrement, à la façon dont cette dernière gère la pagination sur des ensembles de données volumineux. Dans ce contexte précis, nous vous proposons de voir également comment nous pouvons optimiser les projections, à savoir la sélection d’un sous-ensemble des propriétés d’un enregistrement au sein d’un ensemble de données.

Pour cela, nous nous servons au cours de ce billet d’un utilitaire appelé Fiddler, un proxy de débogage Web qui permet d’inspecter le trafic http entrant et sortant de notre machine de façon à mesurer l’impact sur les requêtes au service de données OGDI (Open Data Government Initiative) et les réponses associées.

Pour façon à illustrer notre propos, nous nous appuyons sur l’ensemble de données relatif à la liste des arbres remarquables du site Open Data de la Mairie de Paris et proposé en consommation sur l’instance de test OGDI mise à disposition par Microsoft France à l’adresse https://ogdifrancedataservice.cloudapp.net/v1/frOpenData/Arbres. Cet ensemble de données est particulièrement intéressant pour ce billet dans la mesure où il possède plus de 100 000 enregistrements.

Gestion de la pagination

Après installé l’extension (si ce n’est déjà fait), depuis un nouveau classeur Excel, sélectionnez l’onglet PowerPivot et cliquez sur le bouton PowerPivot Window. Dans la fenêtre PowerPivot pour Excel, cliquez sur le bouton orange A partir du flux de données.

Dans la fenêtre Assistant Importation de tablequi s’ouvre, entrez l’URL du jeu de données des arbres de Paris et cliquez sur le bouton Suivant.

Cliquez sur Suivant. Avant de cliquer sur Terminer, n’oubliez pas de lancer Fiddler (ou tout autre analyseur de paquets avec lequel vous êtes déjà familier). Cliquez maintenant sur Terminer.

Toute une série de requêtes s’affiche à présent dans Fiddler :

On peut légitiment se demander pourquoi plusieurs requêtes sont ainsi envoyées par l’extension PowerPivot alors que l’on a tout simplement demandé un seul ensemble de données.

La réponse se trouve à la fois dans la conception même des services Cloud utilisés par défaut par le service de données OGDI et dans les caractéristiques du protocole utilisé par celui-ci.

Par défaut, la solution OGDI stocke les catalogues (d’ensembles) de données via le service de tables, un service de stockage NoSQL de la plateforme Windows Azure adapté pour des quantités importantes de données qui nécessitent une structure supplémentaire et vis-à-vis d’applications qui doivent utiliser les données dans le détail via des requêtes. Le service de données OGDI constitue dans la pratique un frontal du service de table utilisé pour le stockage du ou des catalogues.

Le service de tables Windows Azure dispose d’une règle de pagination qui fait qu’une requête sur une table retourne les données dans des pages de 1000 enregistrements au maximum (ou peut-être moins, comme d’autres événements peuvent déclencher un saut de page, tels que la taille des données transférées ou un délai d'attente (timeout)).

Mais si une limite de 1000 enregistrements est fixée par conception, comment fait-on pour obtenir le résultat complet d’un ensemble de données ou d’une requête comprenant plus de 1000 enregistrements?

Avec l'utilisation de l’API REST pour accéder aux tables, ce qui est le cas du service de données OGDI, le service de tables Windows Azure retourne les résultats avec un jeton/clé de continuation.

La présence de ce jeton de continuation n'est pas évidente, dans la mesure où ce dernier n'est pas exposé explicitement par l'API mais l’est sous forme d’en-têtes de réponse http, en l’occurrence x-ms-continuation-NextPartitionKey et x-ms-continuation-NextRowKey.

Si vous soumettez à nouveau la même requête avec ce jeton de continuation, c'est-à-dire les en-têtes http x-ms-continuation-NextPartitionKey et x-ms-continuation-NextRowKey avec leur valeur courante, le traitement au niveau de la table reprendra où la requête précédente l’avait laissé ; la réponse correspond à la page suivante.

L'API proposée par le SDK Windows Azure sait comment gérer cette pagination et la continuation de la requête sans avoir à vous en préoccuper, en laissant un objet CloudTableQuery exécuter la requête pour vous. AsTableServiceQuery est une fonction d'extension qui renvoie un nouvel objet CloudTableQuery qui encapsule l'objet de la requête originale avec le code pour gérer les continuations.

Tout ceci est très bien mais le service de données OGDI expose les données via le protocole ouvert de donnée OData (Open Data protocol). Comment dès lors gérer le jeton de continuation sous une forme interopérable avec des consommateurs conformes OData comme l’extension PowerPivot pour Excel ?

La réponse se trouve tout simplement dans la spécification du protocole OData. En effet, la section § 2.1 Retrieving feeds, Entries and service document introduit la notion de liste partielle d’enregistrements :

When a Collection is too large to be returned in a single response, servers can return a partial list of entries as defined in AtomPub. A feed for a partial list contains subset of the entries of the Collection and a link to the next (potentially partial) list.

…

Comme décrit dans la spécification, la prise en charge des listes partielles correspond à la présence d’un élément de type link avec l’URL de la prochaine requête comprenant dans la chaîne de requête (query string) l’option de requête $skiptoken. La valeur d'une option de requête $skiptoken st un jeton opaque de format libre qui doit identifier le point de départ de l’ensemble de données identifié par l'URI contenant le paramètre $skiptoken. Comme la valeur $skiptoken identifie un index dans un ensemble de données, l’URL du service de données contenant un $skiptoken identifie un sous-ensemble des enregistrements de l’ensemble de données.

Le lien avec le jeton de continuation du service de table Windows Azure est donc évident. Il suffit d’intégrer les en-têtes x-ms-continuation-NextPartitionKey et x-ms-continuation-NextRowKey et leur valeur respective comme valeur de l’option de requête $skiptoken.

$skiptoken='%26NextPartitionKey=1!48!MDIyYzYxNWEtYzM5YS00ZTQyLWIwNDAtMTAyYThhNGE4NmFl%26NextRowKey=1!48!NGIyNjI4MGQtMDZkYy00N2U4LWE4OGItN2VlMDllZDNmZGUy'

Dès lors, si l’ensemble de données requêté contient plus de 1000 enregistrements, le résultat de la (première) requête http contient au maximum 1000 enregistrements, et le flux contient à la fin des résultats de cette première requête http un élément de type link contenant l’URL pour obtenir les enregistrements suivants, et ainsi de suite jusqu’à ce que l’on ait parcouru tous les résultats.

Ceci permet de retourner au niveau de PowerPivot l’ensemble de la liste des arbres remarquables, à savoir les plus de 100 000 enregistrements.

Optimisation de la projection

Si vous voulez faire à présent de la projection, c’est-à-dire ne sélectionner que les propriétés qui vous intéressent pour les enregistrements d’un ensemble de données, l’extension PowerPivot vous propose un assistant de sélection des propriétés que vous voulez sélectionner. Utilisons cet assistant pour ne sélectionner, par exemple, que la circonférence et la hauteur des arbres.

Dans l’Assistant Importation de table, cliquez sur Afficher un aperçu et filtrer.

Cliquez sur OK, puis sur Terminer.

Si vous observez à nouveau dans Fiddler les requêtes envoyées au service de données OGDI par l’extension PowerPivot, vous pouvez vous rendre compte que les requêtes envoyées ne comportent pas de filtre et qu’en conséquence, les résultats retournés dans la réponse contiennent de facto toutes les propriétés des enregistrements et pas seulement celles que l’on a sélectionnées dans l’Assistant Importation de table comme on pourrait s’y attendre.

En l’état, le filtrage s’opère donc côté client et non côté serveur au niveau du service de données OGDI ! Ce qui implique à l’évidence une plus grande consommation de bande passante et donc par corolaire un temps de téléchargement plus long...

Ce problème revêt encore plus d’importance lorsque les enregistrements disposent de beaucoup de propriétés et que l’on ne souhaite au final que très peu de propriétés. En somme, plus le ratio nombre de propriétés souhaitées / nombre total de propriétés est proche de 0, plus cette approche est problématique.

Une fois encore, cela suppose de s’intéresser aux clauses (de filtrage) définies dans la spécification du protocole OData et comme décrites dans la partie OData: URI Conventions. En particulier, la présence d'une option de requête $select permet de spécifier que la réponse du service de données doit retourner seulement un sous-ensemble des propriétés normalement retournées sans celle-ci, et comme identifiées par la valeur de cette clause. Considérons dont l’impact de la clause suivante :

$select=circonfere,hauteur_m

Et précisons celle-ci directement dans l’URL de l’Assistant Importation de table:

Cliquez sur Suivant puis sur Afficher un aperçu et filtrer. Seules les propriétés attendues sont retournées.

L’utilisation de Fiddler confirme qu’avec notre clause $select dans la requête envoyée par l’extension PowerPivot au service de données OGDI, les enregistrements retournés ne comprennent que les propriétés que l’on a demandé. De plus, on peut se rendre compte que la taille de la réponse est presque 2 fois moindre qu’avec la requête précédente ; ce dont personne ne se plaindra.

C’en est terminé pour ce billet sur l’extension PowerPivot ou plus exactement sur la prise en compte des options de requête OData $skiptoken et $select pour respectivement gérer la pagination et optimiser les requêtes de projection sans passer par l’assistant de filtrage. Le service de données OGDI prend également en charge les options de requête $top, $skip, $filter et $format telles que décrites dans la partie OData: URI Conventions. Le site Web interactif permet de tester dynamiquement ces deux dernières options de requête.

Nous espérons que ces éléments vous permettrons de faire un usage optimal du service de données OGDI.