Add Spark to Your OData: Consuming Data Services in Excel 2010 Part 1

OData is an open REST-ful protocol for exposing and consuming data on the web. Also known as Astoria, ADO.NET Data Services, now called WCF Data Services in .NET. I’ve written about it in the context of .NET many times on this blog. However there are SDKs available for other platforms like JavaScript and PHP.

With the release of .NET Framework 3.5 Service Pack 1, .NET developers could easily create and expose data models on the web via REST. The simplicity of the service, along with the ease of developing it, make it very attractive for CRUD-style data-based applications to use as a service layer to their data. Check out some of the articles I’ve written before on OData. If you’re just getting started, I’d suggest reading Using ADO.NET Data Services first.

Now with .NET Framework 4, there are some new enhancements to data services (like my favorite) and as the technology matures, more and more data providers are popping up all over the web. Codename “Dallas” was announced at MIX 2010 this year which is a service that allows you to subscribe to OData feeds from a variety of sources. Check out the catalog here. You can consume these feeds directly in your own applications or you can use the PowerPivot (a.k.a “Gemini”), an Excel Add-In, to analyze the data easily.

In this post I’ll show you how to create a data service with Visual Studio 2010, consume its OData feed in Excel using the PowerPivot client, and analyze the data using a new Excel 2010 feature called Sparklines. Next article I’ll show you how you can write your own Excel client code to consume and analyze OData sources from your Line-of-Business systems like SQL Server and SharePoint.

Creating a Data Service for AdventureWorks using Visual Studio 2010

Let’s quickly create a data service using Visual Studio 2010 that exposes the AdventureWorksDW database (data warehouse). You can download the database here.  Create a new Project and select the Web node and then choose ASP.NET Empty Web Application. If you don’t see it, make sure your target is set to .NET Framework 4. This is a new handy project template in to use in VS2010 especially if you’re creating data services.

Click OK and the project is created. It will only contain a web.config. Next add your data model. I’m going to use the Entity Framework so go to Project –> Add New Item, select the Data node and then choose ADO.NET Entity Data Model. Click Add and then you can create your data model, in my case I generated it from the AdventureWorksDW database.

Next we need to add the WCF Data Service (formerly known as ADO.NET Data Service in Visual Studio 2008). Project –> Add New Item, select the Web node and then scroll down and choose WCF Data Service. This item template is renamed for both .NET 3.5 and 4 Framework targets so keep that in mind when trying to find it:

Now you can set up your entity access. For this example I’ll allow read access to all my entities in the model:

 Public Class AdventureWorksService
    Inherits DataService(Of AdventureWorksDWEntities)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(ByVal config As DataServiceConfiguration)
    ' TODO: set rules to indicate which entity sets and service operations are visible, updatable,etc.
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead)
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2
    End Sub
End Class 

We could add read/write access to implement different security on the data in our model or even add additional service operations depending on our scenario, but this is basically all there is to it on the development side of the data service. Depending on your environment this can be a great way to expose data to users because it is accessible anywhere on the web (i.e. your intranet) and doesn’t require separate database security setup. This is because users aren’t connecting directly to the database, they are connecting via the service. Using a data service also allows you to choose only the data you want to expose via your model and/or write additional operations and business rules.

We could deploy this to a web server to host for real or we can keep it here and test consuming it locally for now. Now let’s see how we can point PowerPivot to this service and analyze the data.

Using PowerPivot to Analyze OData

There are a lot of good video tutorials on the PowerPivot site that are aimed at power users so I suggest you have a look. I’m not a power user of Excel per se but I am a developer and I can tell you after I installed PowerPivot I was consuming feeds in about 2 minutes, so it’s really easy to get started. PowerPivot isn’t just about OData feeds but rather a consumer of many kinds of BI data sources, many more than what you get out of the box with Excel. For instance, we could have just as easily connected directly to SQL Server or Access data or loaded data from Reporting Services, a text file, or other spreadsheet.

To start consuming our data service, first make sure the service is running. If you just built it in VS then you can just start the debugger to test it by hitting F5. This will open the browser and you can drill into your data service from there. The URL to the data service will look something like https://localhost:1234/AdventureWorks.svc/ when in development.

Now open Excel and select the PowerPivot tab and click the PowerPivot Window button to open it. This will open a second Workbook that can you use to collect the data from multiple sources and set up relationships between them. On the Home tab select From Data Feeds and then choose From Other Feeds. This will open the Table Import Wizard where you specify the URL to your data service.

 

Click Next and now we can pull in a couple tables and do some analysis. Let’s pull in the DimEmployees and FactSalesQuotas tables. Click Finish. Now we need to set up a relationship between them on EmployeeKey. You do this by selecting the Table tab and then clicking the Create Relationship button on the Ribbon. Set Table equal to FactSalesQuotas and Related Lookup Table to DimEmployees and then both columns to EmployeeKey and click the Create button.

Now we can party on this data. Let’s create a PivotTable and a couple charts to look at the sales quotas for employees. From PowerPivot’s Home tab, select the PivotTable button and select Chart and Table (horizontal). This will flip you over to your workbook where you can choose a worksheet to put it. A task pane will open but depending on what version you have installed it may still say “Gemini Task Pane”. This should look relatively familiar though if you have ever created a pivot table and chart in Excel. Select the appropriate fields to report on and viola, you have your data analysis against an OData service. (click image to enlarge)

Analyze with Sparklines in Excel 2010

One of my favorite features of Excel 2010 is sparklines. Sparklines are little graphics in a cell that give a visual indication to what’s happening with the data in that row. For instance, we could add trend lines for each of the rows in the pivot table to quickly see the yearly performance for each individual employee. Instead of creating a filtered chart that you have to flip through, you can immediately see the visualization inline.

To add sparklines, select the Insert tab on the Ribbon, then choose the type of sparkline you want to create. Next select the data range and the location. In this example I chose the Line sparklines and am displaying the high and low points. Note that by default each sparkline is independent of each other, meaning you’re just seeing the trend of data in that row only.  If you want to see how data compares across rows you can play with the Axis min and max properties.

For more information on sparklines see the Excel Team blog.

In the next article-size post I’ll show you how you can create your own Excel client code to consume and analyze data via an OData service exposed by SharePoint 2010. I’ll show you how to write code to call the service, perform a LINQ query, data bind the data to an Excel list object and generate charts. I’ll also show you how you can add these cool sparklines in code. Until next time…

Enjoy!

UPDATE 4/21: Read part 2 - Add Spark to Your OData: Consuming Data Services in Excel 2010 Part 2

Comments

• Anonymous
  April 21, 2010
  When I imported odata to excell, I found no navigation fields! How can I deal without them?

• Anonymous
  April 21, 2010
  Hi Boyd, You need to expose the relationship keys in your model then you can add the relationships in PowerPivot by selecting the Table tab and then clicking the Create Relationship button on the Ribbon. If you are creating an EF model in VS2010, make sure you include the foreign key columns in the model when stepping through the Entity Model Wizard. If you are using an older version of EF that doesn't support exposing the FKs then you need to find a column or (combination of columns) that can identify the row. Depending on your data you can create a column in PowerPivot that is a combination of values for each data set and then relate those together. See: http://www.powerpivotblog.nl/combine-two-files-or-fact-tables-in-powerpivot-using-relationships-relationships-explained HTH, -B

• Anonymous
  June 10, 2010
  I can't setup feed connection to my Entity Framework WCF service. ============================ Error Message: ============================ Cannot connect to the specified feed.  Verify the connection and try again. Reason:The data feed URL  that was provided,"[myservice url]/Service1.svc", points to a feed that is not valid.. ============================ Call Stack: ============================   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.UpdateDataFeedParameters(ConnectionStringBuilder connBuilder, IDataSource dataSource, Boolean checkFeedValid)   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.GetCurrentConnectionString(Boolean checkFeedValid)   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl) ============================

• Anonymous
  February 17, 2011
  Is there a way to implement security on this?

• Anonymous
  February 18, 2011
  Hi Jamie, Yep, you control access to entities on the data service using  config.SetEntitySetAccessRule() in the InitializeService. Also you have the same security as any web service hosted in IIS/ASP.NET. See: msdn.microsoft.com/.../system.data.services.idataserviceconfiguration.setentitysetaccessrule.aspx openlightgroup.net/.../Simple-Example-To-Secure-WCF-Data-Service-OData-Methods.aspx HTH, -B

• Anonymous
  April 13, 2011
  Thanks for your article

• Anonymous
  November 25, 2013
  Hi, Can I setup a 2 way communication, so after reading the feed into Excel sheet, can I also update the feed, because I have setup Odata service to handle PUT/ POST/DELETE too. How can I update data from Excel? Regards, Vaibhav

• Anonymous
  September 17, 2016
  For hottest informatioon you have to go to see web and on the web I found this web page as a finest web page for most recent updates.

• Anonymous
  March 21, 2017
  Cdata Software has developed The OData Excel Add-In, which is a powerful tool that allows you to connect with live OData services directly from Microsoft Excel. Use Excel to search, aggregate, read, write, and update live OData feeds directly. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!The Excel Add-In for OData provides the easiest way to connect with OData Services . Users simply supply their credentials via the connection wizard to create a connection and can immediately begin working with live OData Services tables of data. The Excel Add-In is completely self-contained; no additional software installation is required.For more info visit: https://www.cdata.com/drivers/odata/excel/