PowerPivot OData Feeds filtern

Wenn jemand PivotTabellen erstellen möchte, welche sich über mehrere unabhängige Datenquellen verteilen ist PowerPivot ein geniales Werkzeug.

SharePoint kann über die REST-Schnittstelle eingebunden werden, welches das OData Protokoll implementiert. Dazu muss lediglich die WCF Data Services Bibliothek – die Implementierung von OData über WCF – installiert werden. Diese Schnittstelle ist über http://<sharepoint_server>/_vti_bin/listdata.svc erreichbar.

Eigentlich dachte ich, dass dieses Protokoll voll und ganz von PowerPivot unterstützt wird (zumindest wird einem das immer so erklärt). Leider gibt es einen Bereich, welcher nicht unterstützt wird:
Das Vorfiltern der Daten über den QueryString ist leider nicht möglich. Dies ist dann nützlich, wenn man vermeiden möchte, dass die komplette Liste ausgelesen wird (und anschließend in der Excel-Tabelle gespeichert wird). Die Excel Datei kann so gut und gerne 20 bis 50 MB und größer werden, je nach Anzahl an Elementen.

OData bietet für das Filtern, Sortieren und Selektieren QueryString-Parameter an.
Um z.B. alle Buchungen aus dem Jahr 2012 zu filtern kann folgendes geschrieben werden:
…/listdata.svc/Buchungen?$filter=year(Erhalten) eq 2012

Leider mag PowerPivot QueryStrings ganz und gar nicht! Und mit ganz und gar nicht meine ich, dass diese einfach ignoriert werden. Dass ich nicht der Einzige mit diesem Problem ist sieht man an folgendem Bug Report: https://connect.microsoft.com/SQLServer/feedback/details/724823/import-odata-feed-datasource-into-powerpivot-without-filter

Da ich aber nicht so lange warten wollte, bis dieses Feature in PowerPivot Einzug findet habe ich eine Quick & Dirty Lösung zusammengebastelt, welche funktioniert, aber nicht wirklich schön ist.

Eigentlich sind mir zwei Lösungen eingefallen, die Eine greift in die IIS-Konfiguration von SharePoint ein (der geneigte Leser soll selbst entscheiden, ob dies eine gute Lösung ist), die Andere dient als Gateway zum OData-Feed von SharePoint.
Die zweite Lösung ist etwas komplexer – nicht sehr viel komplexer – aber genug, um aus dem Artikel zwei zu machen. Ich habe zurzeit nicht vor auf diese Lösung genauer einzugehen, es sei den ein Leser ist an der zweiten Lösung interessiert, dann werde ich einen weiteren Blog-Eintrag posten.

Quick & Dirty Lösung: Eingriff in die IIS-Konfiguration von SharePoint

Okay, das Ändern der IIS-Konfiguration von SharePoint ist eigentlich ein absolutes NoGo (außer in den von der SharePoint Dokumentation ausdrücklich erlaubten Fällen), die Lösung hat sich aber als recht einfach und wirkungsvoll erwiesen.

Ich bediene mich hier einfach dem URL Rewriting Modul, welches einem im IIS ab Version 7 zur Verfügung steht (muss aber nachinstalliert werden: http://www.iis.net/download/URLRewrite). Eine “User-friendly URL” Rule später funktioniert das Ganze auch schon. Zu beachten ist allerdings, dass der Webserver-Prozess recycelt wird (nicht der App-Pool, geht also etwas schneller), nachdem Änderungen am URL-Rewriting vorgenommen wurden, das Ganze ist in Produktion also mit Vorsicht zu genießen.

Die URL-Regel in meinem Fall sieht beispielsweise so aus:
image

Im Grunde wird nichts Anderes gemacht, als dass die URL
http://portal/projects/buchungsportal/_vti_bin/listdata/BuchungenKundeImJahr/2012
über einen URL Match
^projects/buchungsportal/_vti_bin/listdata/BuchungenKundeImJahr/([^/]+)/?$
umgeleitet wird zu
projects/buchungsportal/_vti_bin/listdata.svc/Buchungen?$filter=InhaltstypID%20eq%20’0x010100F72541062D58004C81F84CFC226B954702007BDDD584C443764C8A87B2F22748C1AF’%20and%20year(Erhalten)%20eq%20{R:1}

Das Jahr ist also variabel und wird in {R:1} eingefügt, der Inhaltstyp ist immer derselbe, muss also nicht über die URL bestimmt werden (könnte aber ohne Weiteres gemacht werden).

Ein rascher Test mit PowerPivot beweist, dass das Rewriting funktioniert:
image

image

Es kommen auch wirklich nur die Buchungen zurück, welche nach der InhaltstypID und dem Jahr gefiltert wurden.

Tagged

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: