Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yossifisch
Advocate I
Advocate I

Connect to MWS - Amazon Seller Central API

Hi,

 

I was wondering if it's possible to connect to Power BI to the Amazon API (MWS) for sellers. If yes, where would I begin on Power BI?

The API documentation is at: https://developer.amazonservices.com/

11 REPLIES 11
adelheni
Helper III
Helper III

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/.

 

adelheni
Helper III
Helper III

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.

 

 

ZappySys
Helper I
Helper I

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)

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

 

Amazon MWS API Pagination Example - Preview SQL Query Result

Amazon MWS API Pagination Example – Preview SQL Query Result

 

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

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

yossifisch
Advocate I
Advocate I

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

 

If you think it makes sense, please vote!

CindySandjo
New Member

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...

 

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

It absolutely works with Postman. Here's how in roughly 2 steps:

1) In Postman, define Body as such:

Postman Body.png

 

To save you from typing, here's the code. Be sure to replace keys/secret with your own.

AWSAccessKeyId:YOURACCESSKEY
Action:GetReportRequestCount
Merchant:YOURMERCHANTKEY
MWSAuthToken:amzn.mws.e6afcd81-0714-a5fa-223e-656b6e2e4a1e
SignatureVersion:2
Timestamp:{{timestamp}}
Version:2009-01-01
Signature:{{signature}}
SignatureMethod:HmacSHA256

 

2) Define Pre-request script as follows:Postman Script.png

 

To save you from typing, here's the code. Be sure to replace keys/secret with your own.

var CryptoJS = require('crypto-js');

var timestamp = new Date().toISOString();
pm.environment.set("timestamp", timestamp);

var post = "POST\nmws.amazonservices.com\n/\nAWSAccessKeyId=YOURACCESSKEY&Action=GetReportRequestCount&MWSAuthToken=amzn.mws.e6afcd81-0714-a5fa-223e-656b6e2e4a1e&Merchant=YOURMERCHANTID&SignatureMethod=HmacSHA256&SignatureVersion=2&Timestamp=" + encodeURIComponent(timestamp) + "&Version=2009-01-01";

var hash = CryptoJS.HmacSHA256(post, "YOUR SECRET KEY");
var hashInBase64 = CryptoJS.enc.Base64.stringify(hash);
pm.environment.set("signature", hashInBase64);

 

 

The response from MWS:Postman response.png

 

@CindySandjo @hawdong 

Have you managed to connect?

In Power Query I am not able to find a SHA1/SHA256 hash function to generate oauth_signature

powerbipowerbi
Regular Visitor

Hello,

 

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

powerbipowerbi
Regular Visitor

Hello,

 

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

I was hoping to get a more laymen explanation. I am not a professional programmer.

The article provided by @v-yuezhe-msft doesn't seem to address SHA encryption that is required by MWS.

 

I am surprised there is so little information out there on this topic, I think mine is the only post on this forum.

v-yuezhe-msft
Employee
Employee

@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.

1.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors