THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Power Query and OData

I’ve observed that not very many data professionals are familiar with OData. It’s an open data access protocol built on AtomPub and JSON. It provides a RESTful means of retrieving data, which is what this post is about.

One of the things that makes OData important is that it is recommended by the Open Government Data Initiative. Although this post uses the familiar SQL Server AdventureWorks database, remember, OData is open. It’s not limited to SQL Server or even SQL databases in general.

A small subset of AdventureWorks is published at http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

image

Figure 1. AdventureWorks subset published at OData.org

Open Excel that has the Power Query add-in installed and select the POWER QUERY tab. Select From Other Sources and then choose From OData Feed.

image

Figure 2. Selecting From OData Feed.

Enter the URL for the AdventureWorks OData source and click OK.

image

Figure 3. Specifying an OData source.

Notice that the Power Query Navigator has a popup for peeking at the data.

image

Figure 4. Peeking at the data.

Double-click CompanySales to open the Query Editor. You can rename and delete columns as well as several other changes you might want to check out.

image

Figure 5. Query Editor

After clicking Apply & Close, the data was loaded into the spreadsheet.

image

Figure 6. CompanySales from OData loaded into Excel.

Notice how the query indicates when it was last updated. It’s important to understand this isn’t just a static one time download. This is a data model in Excel that is connected to a live data source. Go to the DATA tab and select Refresh All.

image

Figure 7. Notice that the last updated time has changed to reflect the update.

By taking advantage of new data management features in Excel, you’re no longer stuck with static, stale spreadsheets.

Published Saturday, March 29, 2014 1:21 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Rafi said:

Yes, that is true, but the key here is how do I create a ODATA publications of my own data? How do I limit who can access the data I expose with OData?

March 31, 2014 6:22 AM
 

Ian Yates said:

@Rafi

ASP.Net WebAPI OData...  It demos really well for simple models where you just want to take a database (with Entity Framework layer) and just expose it.  

However it can a bit of a learning curve if you want to really customise your service.  Very powerful but be prepared to throw away your code and try various approaches.  It's come a long way over the past 18 months and is now MS' preferred way of exposing OData as the old WCF data services won't be updated to support OData v4.

April 1, 2014 10:06 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement