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
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.
Figure 2. Selecting From OData Feed.
Enter the URL for the AdventureWorks OData source and click OK.
Figure 3. Specifying an OData source.
Notice that the Power Query Navigator has a popup for peeking at the data.
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.
Figure 5. Query Editor
After clicking Apply & Close, the data was loaded into the spreadsheet.
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.
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.