Connect to MWS - Amazon Seller Central API

yossifisch,

You can get started to connect to Amazon MWS using "Get Data->Web" entry in Power BI Desktop, enter header parameters following the guide in this article in the following screenshot , and you may also need to pass some parameters in your URL as described in this similar blog.



There is also a similar blog about how to connect to API in Power BI Desktop for your reference:

http://angryanalyticsblog.azurewebsites.net/index.php/2016/05/16/api-strategies-with-power-bi/


Regards,
Lydia

I posted the following idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33137476-get-data-from-amazon-marketplace-web-services-mws

If you think it makes sense, please vote!

Hello,

did you find a solutions for this Problem? Does the API work for you?

Hello,

did you find a solutions for this Problem? Does the API work for you?

I have the same issue. I have spent days trying to figure out how to get my data over to PowerBI or even SSMS. But there is little to no information.
I am a BI Developer and I have worked with PowerBI, Tableau, Cognos, SSMS, SSIS, and SSRS. 
Maybe we can complete our own research and work on this together.
I found a code that encrypts to SHA256 using Python like is required for the signature. But even after obtaining the signature I am not able to bring the data in. I get an error. 
Here are my exact steps. Please look at them and see if you can spot an error on my part. I've tried to dissect it and complete the steps as described on MWS and Python, but maybe I've been looking at it for too long. 

1. I wanted to check if it is possible to connect to MWS
I downloaded PostMan, and created a request.

2. I went on MWS Scratchpad, filled out all information to get an OrderList returned. 
The scratchpad provided
-The response
-HTTP Post
-String To Sign
GET 
webservices.amazon.com/onca/xml/Orders/2013-09-01
AWSAccessKeyId=XXXXXXXXXXXXXXXXXXXXX&Action=ListOrders&CreatedAfter=2017-08-01T05%3A00%3A00Z&MarketplaceId.Id.1=ATVPDKIKX0DER&MarketplaceId.Id.2=A2EUQ1WTGCTBG2&SellerId=XXXXXXXXXXXXX&SignatureMethod=HmacSHA256&SignatureVersion=2&Timestamp=2017-12-03T15%3A19%3A40Z&Version=2013-09-01

3. I then took the string to sign and using Python3.6, I created a hash with the following code
>>>import hashlib, hmac, base64
>>>m = bytes('GET
webservices.amazon.com/onca/xml/Orders/2013-09-01?AWSAccessKeyId=XXXXXXXXXXXXXXXXXXXXX&Action=ListOrders&CreatedAfter=2017-08-01T05%3A00%3A00Z&MarketplaceId.Id.1=ATVPDKIKX0DER&MarketplaceId.Id.2=A2EUQ1WTGCTBG2&SellerId=XXXXXXXXXXXXX&SignatureMethod=HmacSHA256&SignatureVersion=2&Timestamp=2017-12-03T15%3A19%3A40Z&Version=2013-09-01', 'utf-8')
>>>s = bytes('SECRET KEY', 'utf-8')
>>>hash = hmac.new(s, m, hashlib.sha256)
>>>base64.b64encode(hash.digest())

In simpler terms
>>>import hashlib, hmac, base64
>>>m = bytes('String To Sign', 'utf-8')
>>>s = bytes('SECRET KEY', 'utf-8')
>>>hash = hmac.new(s, m, hashlib.sha256)
>>>base64.b64encode(hash.digest())

***Maybe it's the way I formated the string to sign. I formated it this way because the way MWS had the string to sign with the spaces and in the new lines did not code correctly in Python. It came back with errors on the spaces for the new lines. So I removed the spaces, and added a ? between the version date and the AWSAccessKeyID***

There is a 15min time allowance between your timestamp and the time you send the request. so I made sure to stay withing the timeframe in my signature.

4. I took the hash obtained in python, and added this to the code as the signature as "&Signature=hash" and edited the first part of the link to fit the recommendations form WMS online docs.

Final Query
HTTPS://mws.amazonservices.com/Orders/2013-09-01?AWSAccessKeyId=XXXXXXXXXXXXXXXXXX&Action=ListOrders&CreatedAfter=2017-08-01T05%3A00%3A00Z&MarketplaceId.Id.1=ATVPDKIKX0DER&MarketplaceId.Id.2=A2EUQ1WTGCTBG2&SellerId=XXXXXXXXXXXXXXXX&SignatureMethod=HmacSHA256&SignatureVersion=2&Timestamp=2017-12-03T15%3A19%3A40Z&Version=2013-09-01&signature=nZm/TE7jnf/BPry7HO+0it8746Wp9zgItcVfTEEgg0=

I entered in both PostMan and PowerBI and it does not work.

I do feel like this is very close, but I might be missing a step or I might have some syntax error.

If you have any knowledge on any part or this process please help.

Here are some recourses I used to comple this

PowerBI Desktop

Postman

https://www.getpostman.com/

MWS Scratchpad
https://mws.amazonservices.com/scratchpad/index.html

MWS Developer Docs Different Sections
Creating a Query String - http://docs.developer.amazonservices.com/en_US/dev_guide/DG_QueryString.html
Signing a Query Request -http://docs.developer.amazonservices.com/en_US/dev_guide/DG_SigningQueryRequest.html
Version 2 signing process- http://docs.aws.amazon.com/general/latest/gr/signature-version-2.html
Required Parameters for ListOrders Request - http://docs.developer.amazonservices.com/en_US/orders-2013-09-01/Orders_ListOrders.html

Python
Hashlib - https://docs.python.org/3/library/hashlib.html
Base64 - https://docs.python.org/3/library/base64.html#module-base64
All other python stuff I read on forums

ZappySys has Amazon MWS connector which allows you to query using SQL language and import MWS data to Power BI / other apps (excel, SSRS, SQL Server). Hope this helps.

https://zappysys.com/blog/import-amazon-mws-data-power-bi/

ODBC Connection Credentials for Amazon MWS API (Access Key, Secret Key, URL Endpoint)

Amazon MWS API Pagination Example – Preview SQL Query Result

Load Amazon MWS data in Power BI – Create Amazon Sales Dashboard

I think there are easier solutions for this issue, one that solved my problems was using one of the hundreds of web connectors available, but after some research, I ended up with this one,  https://windsor.ai/power-bi-amazon-report-dashboard-template/.

I decided on them because of customer support, their pricing is not too far from other platforms but you can always try their free plan.

It's pretty complicated to set up An API and you will need authentication tokens and keys.

The best option for you will be to connect amazon mws to power bi with a third-party connector such as windsor.ai.

This step by step article will help you connect you amazon mws data to power bi in minutes.

https://windsor.ai/connect-amazon-mws-to-power-bi/.