[HOWTO] Load data from OData API into Excel

While I was working with Excel of Office 365 I wanted to load and evaluate data from one of our Odata services we implemented. Suddenly I faced a strange issue:

consume-odata-in-excel-1

We couldn’t get data from the Data Model. Here’s the error message we got:
Either the dimension with the ID of ‘Jobs_deaddead-dead-dead-dead-deaddeaddead’ does not exist in the database with the ID of ‘deaddead-dead-dead-dead-deaddead1234’, or the user does not have permission to access the object.

I couldn’t receive all the data of the endpoint I specified. Strange thing is, I was able to receive the data from all other tables, except for the one I needed. In spite of that I already was able to get data from other tables, I still made sure I entered the correct credentials and connected to the correct server.
I did some research but first I didn’t find a solution for this problem, but after a while I found the little hidden wonder. In the meantime, I still can’t tell you why this happened, but I can tell you how to solve it. There are 2 ways you can consume data from an Odata service in Excel. One of them leads to this issue. To get the data you want safely, just follow the steps below:

  1. Go to the data tab
  2. Instead of clicking From Other Sources click on New Query
  3. In the opened menu, go to From Other Sources
  4. Click on From Odata Feed
    consume-odata-in-excel-2
  5. Enter your URL
  6. Enter your Credentials in the correct tab
  7. Choose which data to load
  8. Click load

It’s just as easy as that. Now you can work with your data as you like.

Trackbacks

  1. […] How to solve this issue is pretty easy. I wrote already another blogpost about that, you can read it here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: