Bytes Matters: 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.

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).

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

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 *.

Now compile the project and Browse the .SVC

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

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.

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

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.