Friday, March 26, 2010

Enabling and Using the OData Protocol with SQL Azure

This post shows you how to enable the OData protocol for specific SQL Azure instances and databases, query OData sources, and display formatted Atom 1.0 data from the tables in Internet Explorer 8 and Excel 2010 PowerPivot tables. A later post will describe using the OData protocol with SharePoint 2010 PowerPivot tables.

• Updated 3/26/2010 with a comparison of PowerPivot for Excel and Tableau (see the  end of the “Working with OData Feeds in PowerPivot for Excel 2010” section.)

OData Background

According to http://www.odata.org:

The Open Data Protocol (OData) provides a way to unlock your data and free it from silos that exist in applications today, making it easy for data to be shared in a manner that follows the philosophy of Open Data. OData enables a new level of data integration and interoperability across a broad range of clients, servers, services, and tools.

Doug Purdy adds more background in his Open Data for the Open Web post of 3/16/2010 to the Microsoft on the Issues blog:

Today at MIX10, Microsoft’s conference for Web developers and designers in Las Vegas, I stood on stage to outline the importance of Web APIs and our commitment to open data for the open Web .  We recently announced The Open Data Protocol (OData) – a set of conventions over the existing Atom Publishing Protocol  (AtomPub) standard.  OData provides a way to unlock and share your data, freeing it from silos that exist in some software applications today.

At the same time, we released OData under the Open Specification Promise (OSP) to allow anyone to freely interoperate with OData implementations, and to start a conversation with the community about rolling the features of the protocol into AtomPub or other appropriate standards, and to see how we can work together to build an ecosystem of open data services.

Many of us at Microsoft believe the OData protocol can help usher in a more open and programmable Web by creating a common funnel to expose rich data, thereby creating a world of customized consumer mash-ups; a world where government data is transparent and accessible to any citizen; a world where you can ask a question and know, “There’s a feed for that.” 

In the last few months, we’ve begun to see  excitement and creativity build around OData – and an impressive list of OData producers/consumers coming online.  One example stems from the 2010 Winter Olympics, where developers created an application built on Windows Azure using an Open Data catalogue from the City of Vancouver.  Through Bing maps, Facebook and data feeds, OData translated raw data into interactive applications designed to help visitors and citizens find parking, events and services and to connect socially with peers. ..   

OData, formerly Project “Astoria,” ADO.NET Data Services and WCF Data Services, debuted in earnest at MIX10 with presentations by:

Pablo Castro: FT12 OData: There's a Feed for That:

There is no shortage of valuable data being generated by applications, reports, tools, websites, etc. Unfortunately, this leaves many of us wishing we could programmatically access the data and logic behind an app, report or website. To break down data silos and increase the shared value of data and its asscoiated business logic through the web, Microsoft has recently announced the Open Data Protocol which enable exposing any data source as a web-friendly data feed. Join this session to understand what the Open Data Protocol (OData) is and how it adds end user and developer value to many of Microsoft's leading products and services (such as SharePoint Server 2010, Microsoft Codename …

and Mike Flasko: FT13 Implementing OData: How to Create a Feed for That:

Data has become a first-class element of the web. The Open Data Protocol (OData) applies web technologies such as HTTP, AtomPub and JSON to enable a wide range of data sources to be exposed on the web in a simple, secure and interoperable way. Whether you have a simple collection of reference data, are building a Rich Internet Application using WCF RIA Services or are building the data platform for a high-end website, this code-heavy session walks through the key technologies and practices available to expose your data and its associated logic as an OData feed. This session covers the basics and quickly progresses to the nuts and bolts of the available OData frameworks. Once your data is available as an OData feed it can be consumed by any of the available OData libraries/tools for Microsoft .NET, Silverlight, AJAX, Java, PHP and Excel.

Obtaining an SQL Azure Account, Generating a Database and Adding an Anonymous Login and User

Question: If Amazon can obtain a U.S. Patent on “One-Click Shopping,” can Microsoft obtain a patent on “One-check” OData enablement?

Follow these steps to create an Anonymous login and data base users:

  1. Once you have an SQL Azure instance with a database or two populated, create an Anonymous login in SQL Server 2008 R2 Management Studio with the following T-SQL template:

    -- ===============================================
    -- Create SQL Login template for SQL Azure Database
    -- ===============================================

    CREATE LOGIN <SQL_login_name, sysname, login_name>
        WITH PASSWORD = '<password, sysname, Change_Password>'
    GO

  2. Then add an Anonymous user to your database in the db_datareader role with this template:

-- =================================================
-- Create User as DBO template for SQL Azure Database
-- =================================================
-- For login <login_name, sysname, login_name>, create a user in the database
CREATE USER <user_name, sysname, user_name>
    FOR LOGIN <login_name, sysname, login_name>
    WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- Add user to the database reader-only role
EXEC sp_addrolemember N'db_datareader', N'<user_name, sysname, user_name>'
GO

SQL Azure Labs and the OData Check Box

David Robinson writes in his SQL Azure announcements at MIX post of 3/19/2010:

We are launching a new site call[ed] SQL Azure Labs. SQL Azure Labs provides a place where you can access incubations and early preview bits for products and enhancements to SQL Azure. The goal is to gather feedback to ensure we are providing the features you want to see in the product. All technologies on this site are for testing and are not ready for production use. Some of these features might not even make it into production – it’s all based upon your feedback. Also please note, since these features are actively being worked on, you should not use them against any production SQL Azure databases.

The first preview on the site is the OData Service for SQL Azure. This enables you to access your SQL Azure Databases as an OData feed by checking a checkbox. It also provides you the ability to secure this feed using the Access Control Services that are provided by Windows Azure Platform AppFabric. You also have the ability to access the feed via Anonymous access should you wish to do so. More details on this can be found at the Data Services Team blog.

  1. Go to the SQL Azure Labs portal page and click the OData Service for SQL Azure link.
  2. Sign in with the Windows Live ID you use for your SQL Azure (and Windows Azure) account.
  3. Fill in your SQL Azure account Password and click Connect (click image for full-size capture) to expand the Configure OData Service page:

    LogIntoSQLAzure600px

  4. Here’s the expanded Configure OData Service page for enabling the OData Service for the SQL Azure instance’s AdventureWorksLTAZ2008R2 database (click image for full-size capture):

    ConfigureODataServices600px

  5. Open the Listbox to select the Anonymous Access User. Alternatively, click the Add User link to add a user with an existing security key.
  6. Click the https://odata.sqlazurelabs.com/OData.svc/v0.1/…/DatabaseName link at the bottom of the page to open a list of the database’s tables in OData’s Atom 1.0 format:

    AdventureWorksAtomTableList600px

Querying OData Collections

The OData protocol includes syntax (called URI operations) for querying OData collections. The OData: URI Conventions SDK topic describes the syntax. For example, the following URI returns all members of the Customers table:

https://odata.sqlazurelabs.com/OData.svc/v0.1/ServerName/AdventureWorksLTAZ2008R2/Customers

To return a single member of a collection, append its 1-based index within parenthesis, as in:

https://odata.sqlazurelabs.com/OData.svc/v0.1/ServerName/AdventureWorksLTAZ2008R2/Customers(1)

to show all property values for the first Customer object:

AdventureWorksAtomCustomers(1)640px

Tip: You must disable IE 8’s Feed Display feature to display OData URIs with the built-in stylesheet for XML files. To do this, choose Tools, Internet Options, click the Content tab and the Feed and Web Slices group’s Settings button to open the eponymous dialog, clear the Turn on Feed Reading View check box, and click OK twice to close the dialogs.

Note: Chris Woodruff has started a OData Primer wiki with an OData does not support Select Many Queries item of 3/22/2010 that explains some query limitations.

Working with OData Feeds in PowerPivot for Excel 2010

SQL Server 2008 R2’s PowerPivot feature for Excel 2010 makes it easy to link with OData feeds and manipulate them as enhanced PivotTables.

After you download and install PowerPivot for Excel 2010, do the following to connect with the database you OData-enabled earlier in this post:

  1. Open a workbook in Excel 2010 and click the PowerPivot tab and PowerPivot Window button to open the PowerPivot Window.
  2. Click the Get External Data group’s From Data Feeds button, and select Other Feeds in the gallery to open Table Import Wizard’s Connect to a Data Source dialog.
  3. Paste the URI for the data feed at the bottom of the Configure OData service feed into the Data Feed URI text box to create a Friendly name, as shown here:

    PowerPivotTableImportWizard600px

  4. Click Next to open the Select Tables and Views, and mark the check boxes for the collections you want to manipulate:

    SelectTablesAndViews640px

  5. Click Preview & Filter to open the Preview Selected table dialog. You can select columns to display and rearrange the table in this dialog:

    PreviewSelectedTable600px

  6. Click OK and Finish to import the selected tables to the PowerPivot instance:

    ImportingSelectedTable640px

  7. Click Close to dismiss the Wizard and display the last table imported in the PowerPivot grid. The following grid has columns rearranged from the default alphabetical sequence:

    ProductsInPowerPivotWindow600px

  8. Click the View group’s PivotTable button to create the PivotTable view. Manipulating the PivotTable is beyond the scope of this post.

• Derek Comingore’s Microsoft PowerPivot Vs. Tableau article of 3/26/2010 for SQL Server Magazine begins:

I wrote a recent article providing an overview of the Tableau Self-Service BI (SSBI) suite and a brief comparison with that of Microsoft PowerPivot (http://www.sqlmag.com/article/business-intelligence/tableau-bi-suite.aspx ). However, since that time even more people in the community are asking me about how the two products stack up against one another. In this extended blog post I will take you on a pound-for-pound comparison of the two SSBI products while explicitly excluding the price factor.

Note: To keep the comparison fair I am not comparing any of the Microsoft BI platform’s features beyond those found in PowerPivot.

Derek’s article is a very detailed comparison of the two BI products.

• Moe Khosravy used Tableau in his The Data Grail: Find –>  Explore -> Publish Online post to the Microsoft Codename Dallas blog on 3/24/2010, as noted in Windows Azure and Cloud Computing Posts for 3/25/2010+.

Posted by Roger Jennings (--rj) at 12:29 PM

Labels: , , , , , , , , , , , , ,

blog comments powered by Disqus