Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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/
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/.
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 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/
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!
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.
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
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:
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:
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:
Have you managed to connect?
In Power Query I am not able to find a SHA1/SHA256 hash function to generate oauth_signature
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 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.
@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