ℹ️  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'

M.Lambert

Hey! I am trying to connect some datasets to PowerBi but when I mark the box to allow publishing, is asking me for a schema.

Can anyone help, please?

Henry Simms

I also couldn't figure out what the Schema does in this context. I've raised the question with our engineering team to find out what the Schema does because it's not mentioned in the documentation.

Nevertheless I was able to get publishing to OData working just be entering any text value as the Schema, without any whitespace or special characters (you could just use the External Label value).

Doing this allowed me to publish datasets from multiple spaces which appeared as data sources in PowerBI when properly connected. I didn't need to specify the Schema name anywhere, just the external label for the environment ("default" in my case):

M.Lambert

thanks Henry

M.Lambert

I now managed to link a dataset to odata but I am struggling to find the URL to access on Power Bi. Do I need sign up on their website? I don't seem to find a place to do it. I also have the API Key but no sure what to do with it 🙄

Henry Simms

If you have published a Dataset to OData, you can check it's accessible, and test the API token (which will be used to authenticate your user with the OData service) as follows:

Browse to the OData API metadata

In a browser, navigate to <url you usually use for Data Studio>/odata/<Data Studio environment external label>.

In my example, Data Studio is access through https://local.datastudio and I use the Default layout, so I browse to https://local.datastudio/odata/default/ to see a login prompt

Authenticate with username and API key

Here, you will enter your Data Studio username (typically your email address) and the API key you created with the "OData operations" permissions. If this is set up correctly, you will see the list of Datasets published to the OData service. In my case I have eight:

If you've reached this point you can start consuming these published Datasets in an OData client. Different organisations will have different tools available, but common tools available to business users that consume OData include Excel, PowerBI, Tableau, Qlik Sense and Google Data Studio. Each tool will have instructions on how to connect to OData feeds - the protocol is standardized and many services make their data available in this way.