Saturday, May 29, 2010

Exposing Data using OData protocol and Consuming it using Excel [Power Pivot]

 

MS Renamed  ADO.NET Data Service => WCF Data Service this service exposed using OData Protocol  OData means Open Data protocol . here is the some of the MS products uses this OData including: SharePoint 2010 (Lists), SQL Server 2008 R2, PowerPivot, Windows Azure Table Storage

First Create ASP.NET Empty Web application project.

image

Now we need to add or create our Model Here i am going to use LINQ to SQL. We can use ADO.NET Entity Data Model as well. Now Select LINQ to SQL Classes template and add to our ASP.NET project. LINQ To SQL Template provides Designer surface where you can drag and drop the DB tables which you wanted to expose as WCF Data Services ( OData).

image

Once Model is created next we need to WCF Data Service template to our project.

image

Next we need to open WCFdataService1.cs file and Add our LINQ TO SQL DataContext class  and Edit the SetEinittySetAccessRule config .

Here i wanted to expose all the Entity so i provided *.

image

Now compile the project and Browse the .SVC

image

Here below query shows how to access all the records from Departments.

image

Now we have successfully exposed our WCF Data Service (OData) . Next we will consume this Data service from Excel using Power Pivot Add-in. first we need to download Power Pivot and install.

Now fire up Excel 2010 you will see Power Pivot Ribbon tab click that.

image

Next Click Power Pivot window and click From Data Feeds ( Our services expose Atom pub)

image

Next Edit friendly name for your Data Feed and provide the Data Feed URL in my case the service is Hosted and running in my local box so my URL will be http://localhost:61411/WcfDataService1.svc. to verify the service is running  you can click Test Connection button and check.

image

Once you have done that then click next . at this point Excel power pivot connects  service  and gets all Data.

image

Next select the Tables you wanted to import and click Finish .

image

Here is the final result .it tells how may rows are in each tables.

image

Now we have all the Data in Excel . Since most of them knows how to work on excel  they can do all BI related stuff.

image

You can do all Filter , sorting etc…

Nandri(Thanks)

Sreenivasaragavan.

No comments: