Odata Slash PowerPivot: it rocks!

Soms gaat er achter een URL een hele wereld schuil. Dit is zeker het geval
met OData. OData staat voor het Open Data Protocol en is door Microsoft
gedeponeerd. Het is een “Open specificatie belofte” wat betekent dat Microsoft
diegene die een implementatie verzorgt, nooit voor de rechter gedaagd zal worden ( http://www.microsoft.com/interop/osp/default.mspx ).

HTTP Slash Slash

OData biedt de mogelijkheid om data:

  • op een uniforme manier aan te bieden en te wijzigen (REST)
  • via standaarden toegankelijk te maken (HTTP, ATOM, JSON)
  • op eenvoudige wijze te doorzoeken en te filteren is (queries a la LINQ)
  • een zelfbeschrijvende interface te geven ($metadata)

Meer informatie hierover is te vinden op http://www.odata.org/

OData

Voorbeeldje? Vul het volgende eens in, in de browser:

http://transit.cloudapp.net/DevTransitODataService.svc/Arrivals?$filter=%20RouteType%20ne%20’Bus’&$skip=5&$top=2

Dit geeft twee records uit een enorme lijst van ‘aankomsttijden’ waarbij de
eerste vijf overgeslagen worden. En wil je weten hoeveel records er live
aanwezig zijn? Selecteer in de browser dan eens:

http://transit.cloudapp.net/DevTransitODataService.svc/Arrivals/$count

Wil je de interface weten van deze openbare service? Type dan in:

http://transit.cloudapp.net/DevTransitODataService.svc/$metadata

Nu zijn dit GET opdrachten om informatie op te halen. OData ondersteunt ook
de POST, DELETE en UPDATE voor de andere CRUD opdrachten.

En ook kan de serverbelasting gereguleerd worden. Aan de serverkant wordt dan
de data in batches opgestuurd zodat de ontvanger bij iedere batch een
skiptoken krijgt om de volgende batch op te kunnen halen.

OData heeft geen ingebouwde security anders dan filtering maar laat dit over
aan het communicatie protocol (HTTP/HTPPS) en de security van de webservice / webserver.

Hoewel OData zeker geen silver bullet is voor iedere vorm van
dataoverdracht, vind ik het zeer veelbelovend voor Self Service BI, samen met Excel PowerPivot. Gebruikers kunnen nu zelf los op de data en dus is er minder vraag naar voorgebakken rapportages.

Excel PowerPivot

PowerPivot is een gratis plugin voor Excel met een verbeterde
Pivot table en bijbehorende Pivot grafieken. Het meest extreme is dat hierbij
enorme hoeveelheden data (en dan bedoel ik ook echt enorm; ik heb een voorbeeld van 100 miljoen regels !!! gezien) te doorzoeken wat nog steeds redelijk performt. En PowerPivot kan uit vele bronnen data inlezen waarbij deze via eigen relaties aan elkaar te koppelen zijn. Zo is data te combineren uit bv. SQL Server, Azure, Oracle, IBM DB2 en Access.

Maar de data kan ook via OData opgehaald worden. Hierbij wordt zelfs de
automatische paging gepareerd om netjes alle data op te halen.

We roepen dus een OData feed aan:

OData feed in Excel PowerPivot

En zo kunnen we het aantal incidenten fraai visualiseren van de hiervoor
getoonde publieke service:

Excel PowerPivot Graph

LET OP: wat nu volgt is een CSI-achtige uiteenzetting
rond een tekortkoming in de OData service aanroep van PowerPivot. Wil je direct naar de oplossing? Druk dan enkele malen op Page-Down…

En nu wordt interessant…

Zodra je een OData service aanbiedt, ga je nadenken over de beveiliging (..).
In de beslissing neem je dan mee wie wat mag zien en met welke tooling je de
data wilt ontsluiten.  Ik heb al eens geblogged over het gebruik van een APIKey maar deze is niet toe te passen bij PowerPivot. PowerPivot ondersteunt wel Basic authentication en Windows authentication.

SSPI and Basic Authentication

Voor ons huidige project hebben we een eigen Basic Authentication
geïmplementeerd zoals beschreven in OData and Authentication – Part 6 – Custom Basic Authentication

Voor wie dit ook wilt implementeren een aantal tips:

  • Hoewel er over Basic Authentication gesproken wordt, moet de webservice met anonimous geconfigureerd worden in de web.config en in IISBasic authentication hoeft niet actief te zijn op IIS om de oplossing te laten werken
  • het voorbeeld van de web.config uitbreiding is voor IIS7+ geschreven
  • De naam van de module kan beter iets anders geschreven worden. Er kan standaard al een (IIS)module voor Basic Authentication actief zijn en die heet al BasicAuthenticationModule. Dit kan een naam-conflict geven
  • Basic Authentication is een prima authenticatie vanwege de eenvoudige implementatie en de goede testbaarheid
  • Basic Authentication is pas een prima authenticatie als dit gecombineerd wordt met SSL. De wachtwoord gaan onbeschermd over de lijn dus alle communicatie moet versleuteld worden

Maar we voeren de spanning verder op en nu wordt het nogal technisch. We
duiken hier de http-vraag-en-antwoord-spelletjes in en we gebruik hiervoor Fiddler. Fiddler werkt stiekem als proxy voor al het netwerkverkeer op de PC en ziet dus alle requests en bijbehorende responses voorbijkomen. Hiermee kan ik prima controleren wat er allemaal gecommuniceerd wordt…

Onze service is beschikbaar als http://IPAddress/powerusageservice.svc
en deze wordt voornamelijk getest via de browser. Dit gaat echt prima, ook bij
Basis Authentication. Want als de browser een GET uitvoert op de server, zal de
server  in eerste instantie een 401 (Unauthorized) geven. Maar in de header van
de response wordt een WWW-Authenticate: Basic teruggegeven.

401 Unauthorized

Dit wordt dan door de browser opgepakt en die zal een inlogschermpje
geven. Deze authentication challenge zal dan gevolgd worden met een
nieuwe aanroep en met een 200 OK als antwoord. De gebruiker is dan eindelijk
ingelogd. Tussendoor wordt een 307 Temporary Redirect door de server afgegeven zodat de browser naar het juiste adres blijft wijzen. Want de WCF service ziet graag een extra slash (/) achter de URL (http://IPAddress/powerusageservice.svc/).

Het is dus belangrijk dat de browser de eenmaal ingevoerde credentials blijft
doorgeven, ook bij een redirect.

307 Temporary Redirect

Nu komen we bij de uitsmijter: wat gebeurt er als we dezelfde actie uitvoeren
met Excel PowerPivot. Zien wij daar dezelfde reeks? Terug dus naar Excel
PowerPivot. Als ik dezelfde URL http://IPAddress/powerusageservice.svc
en credentials invul bij Excel dan krijg ik bij het testen van de connectie
steeds een 401…

Feed zonder slash

Waarom??? Het was even zoeken maar Fiddler gaf uiteindelijk het juiste
antwoord:

307 Temporary Redirect zonder authentication

We zien hier wederom een nette 401 Unauthorized met daarna de 307
Temporary Redirect. En we zien ook de daarop volgende aanroep  met het grote
verschil dat Excel PowerPivot vergeet om bij de redirect ook de credentials mee
te sturen naar de nieuwe lokatie… Oops! En dat resulteert in de 401
Unauthorized.

Dus wat is nu de oplossing? Dit is te simpel! De service URL moet gewoon
alvast uitgeschreven worden met de extra slash zodat de 307 Temporary Redirect niet nodig is. Dan zal de 401 Unauthorized voor http://IPAddress/powerusageservice.svc/
direct met een 200 OK gevolgd worden.

OData feed in Excel PowerPivot met slash

Conclusie:

OData is echt een interessante mogelijkheid om data als services aan te
bieden. Excel PowerPivot kan hier zeer goed gebruik van maken om enorme
hoeveelheden data simpel te visualiseren. Door een onhandige implementatie van de HTTP 307 Redirect bij een aanroep met Basic Authentication als beveiliging kan de eerste kennismaking flink tegenvallen. Schrijf dus de service URL dus altijd uit, met een slash achter de .svc zoals http://IPAddress/powerusageservice.svc/