ℹ️  Importing your Datasets into Excel or Tableau using OData

Josh Boxer

You can easily get your data from Data Studio into Excel or Tableau using OData.

OData (odata.org) is “an open protocol that allows the consumption of data via a simple and standard RESTful API”. If you choose to turn on OData in Data Studio (v2.2.3), you will be able to link your datasets (including snapshots) to any BI tools like Tableau, Power BI or Excel in minutes.

To use any Data Studio APIs you will need your Username and API key

For added security, there is an OData setting that needs to be enabled in each Space and each Dataset that you wish to use share via API:

Once you have your API key and enabled OData settings you are ready to begin importing your data into your external tool. To do this in either Excel or Power BI:

1.      click Get data (or New Source) and select OData feed as the source type

2.      Enter your OData feed URL: http://{server name}:{port}/odata/{environment}, username and API key for password

3.      Select any Datasource that has been enabled:

4.      Import it into your workbook or Power BI report:

ℹ️

Pete Robson

Hi Josh,

I'm currently stuck on step 3 - my space, datasets, workflow and resulting snapshot have all been set up to publish to ODBC & OData. After linking the feed in PowerBI/logging in, the datasource that I'm looking for isn't in the menu. Is there something else that needs to be done to make it appear? I've refreshed the menu but still no luck.

Thanks

Pete

Josh Boxer

Sounds like you might be using an older version, but you might have this filter option which shows which Datasets have been Published for OData:

I see these Datasets in PowerQuery:

note the count equals 3, I assume you have the list expanded? If you can share a screengrab from PowerQuery

Pete Robson

Thanks Josh - I've got the same view as you and can currently see 6 options in the expanded list and denoted as [6]. Not too sure why it's not showing my latest addition?

Henry Simms

Hi @Pete Robson , you can check if your Odata client is to blame by entering the odata URL into a browser, eg:

You'll be prompted for login credentials, which will be your username and API key, and you should then see a XML document displaying the names of the datasets you can access. In my example above, there are two datasets.

If this view doesn't display your latest dataset either, double-check that the user who's API key you're using has the correct access to it in Data Studio

Pete Robson

@Henry Simms Thanks that's sorted it! The user wasn't set to have access to the space where the datasets are held. Have now updated and the new sets are all pulling through.

Thanks very much both for taking the time to assist.

Sarah

Hi Josh. We use OData to pull our reports into Excel which works really well for us. However one of my Data Stewards has recently noticed that dates seem to pull through as strings rather than dates, even though they are dates in Aperture. She would like to be able to filter the dates in Excel but as it's not recognising them as dates this is proving a bit cumbersome. I wondered if there is anything I can do about this or if it's just a limitation of the way OData works? Thanks in advance!

Josh Boxer

Hi Sarah, Excel PowerQuery pulls everything through as text, but it is fairly easy to update. Before clicking Load on a dataset for the first time, you need to 'Detect Data Type' (or manually select Date) on the Transform tab:

Sarah

Thanks Josh! I hadn't tried using the PowerQuery Editor before to pull data through - I was just using Get Data - OData. I've now noticed something really strange that's stopping me getting as far as detecting the data type. When I go to Recent Sources and choose my OData feed I can see all the data sources that should be available to select:

However once I've selected one I get an error saying "The key didn't match any rows in the table":

and when I click Edit Settings the data source has disappeared (you can see that all sources starting DF have disappeared):

I wondered if you have any ideas about what's going on here? I'm getting similar errors on queries that are already set up and refreshing correctly - somehow they are working and pulling new data through but the query editor doesn't recognise the data source that's being used.

Josh Boxer

Hi Sarah, I think that first list is cached/out of date as Microsoft seems to show the error when the source is not available. Have these Datasets been renamed and are they still enabled for OData in Data Studio? Maybe try connecting to one of the tables/Datasets from a brand new Workbook and see if that is successful

https://blog.crossjoin.co.uk/2020/04/06/understanding-the-the-key-didnt-match-any-rows-in-the-table-error-in-power-query-in-power-bi-or-excel/
"You’re probably getting this error because your Power Query query is trying to connect to a table or worksheet or something in your data source that has been deleted or been renamed"
or
"This error is caused by a name being referred to in your query for specific table or column names that are no longer present in your source data"

Sarah

Thanks Josh. I haven't renamed these Datasets and they're all still enabled for OData and are refreshing correctly when we run the queries. All very strange!

Josh Boxer

Guessing a little here as nothing has changed on the Data Studio side and not an Excel expert, but you can clear the cache
In Excel you will find the option under Data > Get Data > Query Options > Data Load > Data Cache Management Options > Clear Cache.

Sarah

Thank you so much, that has worked and we now have dates in our reports! 😄

James T Sidebotham

@Josh Boxer Hi Josh, I am trying this and hit a snag. I got the API key, and when I validate the key per Henry suggestion above, I do get to see the XML. However, when trying to connect in Excel, I get "We couldn't authenticate with the credentials provided. Please try again". I made sure the URL was correct, my user name was correct and the API key for the password was correct. Any suggestions? Thanks

Josh Boxer

Hi James, since you managed to load in the browser you confirmed the credentials are correct and Aperture is working as expected. If you google the Excel issue one thing mentioned is special characters in the URL. Otherwise there is not much to go on, if you can locate the Excel log file it might have more information on the reason for the error message.

James T Sidebotham

I got it working by going into 'Data Source Settings' and changing the ODATA URL permissions to 'PUBLIC'