How to Import Amazon MWS data in Power BI

Introduction

In our previous blog we saw how to call REST API in Power BI to import data from JSON based REST API services. Now let’s continue on that and learn how to import Amazon MWS data in Power BI using XML driver. To learn more about how to consume Amazon MWS data in ETL using SSIS check this article.

We will read data from Amazon MWS API using ZappySys XML Driver which is part of ODBC PowerPack.

Requirements

  1. Active Amazon Seller Account needed (Make sure you have Professional Plan or higher (around $39/month) to call Amazon MWS API)
  2. You are already registered as a Developer and you obtained AWS Access Key / Secret Key and Seller ID (i.e. Merchant ID) (see next section)
  3. Download and Install ODBC PowerPack
  4. You will also need Power BI Desktop (FREE) installed

About Amazon MWS / API

If you want to sell something on Amazon you can use their e-commerce platform with some monthly fee. You can setup your entire online store / inventory using admin interface.

Amazon Marketplace Web Service (Amazon MWS) is an integrated web service API that helps Amazon sellers to programmatically exchange data on listings, orders, payments, reports, and more. Using these API you can read or write data from your Seller account and integrate it inside your own Systems (e.g. Reporting / ETL / BI tools).

NOTE: Amazon MWS may allow only certain number of requests per minute depending which API you calling so you might want to use each call wisely. We will suggest you various techniques throughout this article so you can avoid errors so read each section carefully. You can find Throttling information on API help page itself (like this one see throttling section. Also this one).

Obtain MWS API Access Key / Secret and Seller ID

Very first thing to call any MWS API is to obtain Access Key, Secret Key and Seller ID (i.e. Merchant ID). To obtain this you first have to register a developer.

Here is where to look for your Merchant ID and Key / Secret.  Here is direct link to User Permissions page.

How to get Amazon MWS developer keys and know your Merchant ID (i.e. Seller ID)

How to get Amazon MWS developer keys and know your Merchant ID (i.e. Seller ID)

Getting Started

Now let’s look at step by step instructions on how to call Amazon MWS API and then import into Power BI. In below steps we will use  ZappySys ODBC PowerPack so make sure it’s installed first.

Using ScratchPad to Test Amazon MWS API

Each MWS API has set of required and optional parameters. You can refer each API page for details. For example ListMatchingProducts API which we will use as an example. To make MWS API testing / learning easy, Amazon provides online testing tool called MWS ScratchPad. We will use this tool to craft API requests and use that information in ODBC driver SQL queries and later import data in Power BI.

  1. Open MWS ScratchPad  by vising https://mws.amazonservices.com/scratchpad/index.html
  2. Select Products API category and pick ListMatchingProducts API
  3. Enter your SellerID (i.e. Merchant ID), Developer AccessKey (i.e. AWSAccessKeyId) and Secret Key we obtained in the previous section. Enter MarketId (e.g. ATVPDKIKX0DER for USA Market) and other parameters as below and Click Submit to see response.
    Using Amazon MWS ScratchPad (API Testing Tool - Created by Amazon)

    Using Amazon MWS ScratchPad (API Testing Tool – Created by Amazon)

  4. Click on the Response Details Tab to extract some important information we will use for ODBC configuration. Notice attributes in red rectangles. They are the ones which we will need in the POST request Body. ZappySys Drivers supply many common parameters automatically but any API endpoint specific parameter must be supplied in the Request Body. For example you dont have to supply SignatureVersion, Timestamp, Signature or SignatureMethod
    Calling Amazon MWS API in Scratchpad Testing Tool (Response Details Tab) - ListMatchingProducts Example

    Calling Amazon MWS API in Scratchpad Testing Tool (Response Details Tab) – ListMatchingProducts Example

This API requires Market ID parameter so enter it as per your Market where you selling. For example USA marketplaceid = ATVPDKIKX0DER. You can lookup correct MarketplaceId by checking this help (endpoints / marketplaces).

Create ODBC DSN Connection for MWS API / Test Query

Once you have necessary details to call your Amazon MWS API, we can create ODBC DSN which will be used as a reusable connection for MWS API data access in apps like Power BI.

So let’s get started.

  1. In your start menu search for ODBC and select Data Source (64 bit):  If you need to access DSN by Service or scheduled Job then make sure you choose 64-bit DSN if caller App is 64 bit App.
    Open odbc

    Open odbc data source

  2. Use the User DSN page and press Add. For DSN access by Service account or automated jobs which runs under different user, choose System DSN Tab, Make sure You create DSN for correct Bitness (i.e. for 64bit Apps create 64 Bit DSN)
    New Data source

    Add new data source

  3. Select ZappySys XML Driver and click Finish. Amazon MWS API is XML format so we selected XML driver however you can choose JSON or CSV Driver instead of XML if you dealing with JSON format.
    Add new ZappySys XML Driver (For XML File / SOAP API)

    Add new ZappySys XML Driver (For XML / SOAP API)

  4. Once you see Driver Configuration UI window open, enter the following settings
  5. ODBC DSN name (e.g. ZS – Amazon MWS API Connection)
  6. Enter MWS API Service URL as below. Notice that we added /Products/2011-10-01 (This was extracted from previous section). You can check full list of Amazon MWS Endpoints here. Because we are connecting to USA region we will use below URL.
  7. Select OAuth from the Connection dropdown and Click Configure Settings
  8. On the Connection UI, Select OAuth Provider Amazon Market Web Service (MWS)
  9. Enter your API Access Key and Secret Key (obtained from here)
  10. Your connection will look like below.
    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)

  11. Also configure Retry options to handle API limit reached error when you call API too often. For Amazon MWS we get Status code 503 when request is throttled so lets narrow down on which error we want to retry.
    Retry Options

    Retry Options

  12. Click OK to save OAuth Connection UI to go back to main UI
  13. Now let’s use some information we gathered from the previous section (Using Scraptchpad Test).
  14. Select POST for HTTP Request Method
  15. Enter Body as below (Change last parameter with your own SellerID and if needed change your MarketPlaceId too if its not USA)
  16. Click on Select Filter to select correct node (in our case it will be Product node as below.
  17. Now Go to Throttling Tab and enter some delay (e.g. enter 20000 for 20sec delay after each request). This will slow down API calls so we don’t face errors about API Limit reached.
  18. You can also consider enabling Data Caching for MWS API as below to avoid API throttling / rate exceeded error. Change Cache Mode to Cache All (Use Cache for Metadata + Data Requests)
    NOTE: MWS may allow only certain number of requests per minute so you might want to use each call wisely. You can find Throttling information on API help page itself (like this one see throttling section. Also this one).
    Enable Caching Options for Metadata / Data for ZappySys ODBC Drivers

    Enable Caching Options for Metadata / Data for ZappySys ODBC Drivers

  19. Now goto Preview Tab and click on Green Play button to test query
    Query Amazon MWS API using ZappySys XML Driver - Preview UI

    Query Amazon MWS API using ZappySys XML Driver – Preview UI

  20. That’s it. We have successfully tested first query to read data from Amazon MWS API

Caching Metadata and Using in SQL Query

We talked briefly about Data Caching but still there will be a time you will face such errors. So lets do another safeguard to reduce total API requests for MWS. Check this article to learn about Caching and META Clause in SQL.

Here are high level steps to Use predefined Schema File so Driver doesn’t call API request to guess metadata.

  1. Go to ODBC Preview window and write desired query you like to use. Click Preview Button to see sample data
  2. Now click Save Metadata of query which we can use later. Save to File in our case (name as mws-query-meta.txt ).
    Create Metadata for ODBC Driver Query - ZappySys API Drivers

    Create Metadata for ODBC Driver Query – ZappySys API Drivers

  3. Now change our query as below
  4. That’s it. Use query like above when we import Amazon MWS data in Power BI in the next section.

Import Amazon MWS data in Power BI (List Orders Example)

Now it’s time to load our data in Power BI and create reports.

  1. Open Power BI Desktop and select the option Get data
    Get data using power bi

    Get data in power bi

  2. Get Data will allow adding the ZappySys ODBC driver. Go to Other and select ODBC.
    select odbc using power bi

    select odbc in power bi

  3. Now it’s time to import data. Basically, there are two modes to import data. Table Mode and Query Mode. Query mode is the most common way. Select your DSN and click Advanced Option to enter custom SQL Query to Import your MWS API data. You can use ODBC DSN Data sources Preview tool to generate SQL Query. For example you can enter query to import Orders for specified date range. We have added some pagination attributes which we will cover in the next section. Click OK to continue.
  4. Use above query for example and paste in Query editor while you Import Amazon MWS data.
    Enter custom SQL to load SOAP Web Service Data in Power BI (ZappySys XML Driver)

    Enter custom SQL to load SOAP Web Service Data in Power BI (ZappySys XML Driver)

  5. On the next screen select Windows Authentication

    Select Authentication for Amazon MWS connection in Power BI data load

    Select Authentication for Amazon MWS connection in Power BI data load

  6. Once data is imported you can use dataset to build your dashboard
    Load Amazon MWS data in Power BI - Create Amazon Sales Dashboard

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

Using Pagination in Amazon MWS API calls

Now let’s look at how to query certain API which allows paginated response. Pagination is fully explained here. Certain APIs like ListOrders , ListOrderItems, ListInboundShipments, ListInventorySupply, GetFullfillmentOrder and few more supports Pagination.

When you call Paginated response next request changes Action with ByNextToken suffix. For example to get orders call ListOrders and next request will be ListOrdersByNextToken

MWS API has multiple pagination patterns so based on which action you calling you have to use correct SQL parameters.

Amazon MWS API has strict throttling limits so make sure you call API accordingly. Calling same API too fast may result in throttling error. You can use WaitTimeMs option to wait after each API call so you do face throttling limit. Refer to MWS API help pages to see exact limit.

Using NextToken for Pagination (ListOrders Example)

Many MWS API returns NextToken attribute in the response which you can pass in the body of next request until no more NextToken found in the response.

Check below example query for ListOrders API. We have enabled pagination by supplying few extra parameters in our original SQL Query.

 

Using NextToken with operations that return HasNext / MoreResultsAvailable

Some API has attribute which tells you to stop pagination (e.g. HasNext). Check below example query for GetReportRequestList. We have enabled pagination by supplying few extra parameters in SQL Query.

Some API calls return slightly different response so rather than HasNext attribute you will see MoreResultsAvailable. In that case change StopIndicatorAttributeOrExpr property to use MoreResultsAvailable property.

Properties you need to change for pagination

Now let’s look at properties which need to be changed to support pagination.

Property Description
Src Change this to correct URL. Look at Scratchpad Response Details Tab and Findout first line after POST and before “?” . e.g. /Orders/2013-09-01  this will be used in your URL.
Example: 
https://mws.amazonservices.com/Orders/2013-09-01
Body (i.e. RequestData) Scratchpad shows HTTP POST section. You can copy that and remove  System supplied parameters and then arrange all in one line to form your Body for request.

Example:

Above HTTP POST can be used as below for your Body in ZappySys Driver. Rest of the parameters are automatically supplied by system. See special placeholder named [$tag$] this gets replaced at runtime when you set EnablePageTokenForBody=True and HasDifferentNextPageInfo=True

Filter Change this parameter according to XML structure in the response.
For example: If you see below response (first xml) in Scratchpad then your Filter will be
$.ListOrdersResponse.ListOrdersResult.Orders.Order[*]
to enable pagination refer to Sample listed here (click Example response at the bottom of that page). In second response and onwards your Filter should be below (See Bold Part).
$ListOrdersByNextTokenResponse.ListOrdersByNextTokenResult.Orders.Order[*]However in Driver we replace ByNextToken with [$tag$]. Like below. Using [$tag$] will automatically pick up the correct filter based on page number. You must set HasDifferentNextPageInfo=True to use [$tag$] feature.$.ListOrders[$tag$]Response.ListOrders[$tag$]Result.Orders.Order[*]Example of first page response

Example of next page response

NextUrlAttributeOrExpr This property defines which attributes indicate Token for next page. You can use this token in Body of next request (see NextUrlSuffix and EnablePageTokenForBody). You must set EnablePageTokenForBody=true to use Extracted token in Body.
StopIndicatorAttributeOrExpr Some APIs like  GetReportRequestList stop pagination based on HasNext attribute value. Use this property to extract HasNext from response by supplying correct Filter expression. You also need to set NextUrlEndIndicator property which defines static value which indicates the last page.
NextUrlSuffix This is used to create a string for NextToken attribute for next page request. If you want to just append extracted token to Body then you dont have to set this but in our case, we have to use &NextToken=EncodedValueOfExtractedToken so we have used &NextToken=<%nextlink_encoded%> expression.
PagePlaceholders This property contains [$tag$] values for first page and next pages. You can define tags for filter, body or header. Each pair must be pipe delimited. First value or pair is [$tag$]  for first request and second value of the pair is [$tag$] for any next request afterwards. Our first request use blank value for [$tag$].
Example:  PagePlaceholders=’body=|ByNextToken;filter=|ByNextToken’
HasDifferentNextPageInfo Set to True – This enables use of [$tag$] inside body, filter, headers to use different values of first request and second onwards requests.
EnablePageTokenForBody Set to True – This enables use of [$tag$] inside body to use different RequestBody values of first request and second onwards requests.

Here is the preview of MWS API query output with Pagination Attributes.

Amazon MWS API Pagination Example - Preview SQL Query Result

Amazon MWS API Pagination Example – Preview SQL Query Result

Import data from MWS Custom Reports (Inventory Report Example)

Now lets look at how to extract data from Custom Report API (You must need ODBC PowerPack v1.1.1 or higher). However reading data from Reports not single step process because report generation is Job style API. Which means you send report request and wait  until its done. Once Report ready you have to read in CSV format or XML. Some Reports only available in CSV format. Check this post to understand how complex it can be to get data. We will make it simple for you to understand this in 3 steps. Basically calling reports requires minimum 3 API calls (see below)

  1. Create a new report request – Cal RequestReport API . It returns ReportRequestId (You can use it in the next step)
  2. Check Report Status see its done – Call GetReportRequestList API (Pass ReportRequestId got in the previous step to get data for only one Report request we care). Keep checking Report Status every few seconds until you get _DONE_ status in response.
  3. Read Report Data – Call GetReport. This API call returns CSV or XML data. So use correct Driver for this step. If CSV data is returned then we must use CSV Driver rather than XML Driver.

Now lets see how to get this 3 steps done in Power BI. In this example, We will get Inventory by calling _GET_MERCHANT_LISTINGS_DATA_ report type.

  1. Create a directory c:\temp
  2. Create new ZappySys XML Driver DSN (User DSN) using same steps we described before. Lets name this DSN as ZAPPY-MWS-API-XML
  3. Create new ZappySys CSV Driver DSN  (User DSN) using same steps we described before (Except the Driver Type should be ZappySys CSV Driver). Lets name this DSN as ZAPPY-MWS-API-CSV
  4. Once you done with ODBC DSN Setup open or create new Power BI Project.
  5. Click on Create Source > from Blank Query
    Power BI - Create New Data source from Query (M Script)

    Power BI – Create New Data source from Query (M Script)

  6. When Query Editor Shows up click on Advanced Editor and paste below code (Its Power BI M Language). If you are unsure how to write Power BI check this tutorial.
    Replace MarketPlaceId and Seller ID in the script (i.e. A1xxxxxx and A2xxxxxx ). See we are using ReportType=_GET_MERCHANT_LISTINGS_DATA_  but you can use any valid report ID from here. You can also pass other optional parameter available for each report Type (e.g. StartDate and EndDate)

    Edit Power BI Query Script - Use custom M Language Script to import data from ODBC or other Relational

    Edit Power BI Query Script – Use custom M Language Script to import data from ODBC or other Relational

    Power BI M Script (New Driver) – Import MWS Custom Report in Power BI

     

    Power BI M Script (Old Driver) – v1.1.2 or older

    If you are using older version – v1.1.2 or older then use below script.

     

  7. Once done click OK.
  8. You may be prompted few times to trust each Query we defined in M Script above. Also you may be prompted to select DSN Credentials (Just Select Windows Credentials)
    Power BI - Run Native Database Query Prompt

    Power BI – Run Native Database Query Prompt

  9. If you get prompted to select DSN Credentials then select as below (Click Windows).
    Select Authentication for Amazon MWS connection in Power BI data load

    Select Authentication for Amazon MWS connection in Power BI data load

  10. After few seconds (Depending on Delay we define in Script – default 15 sec) you will see data loaded.
  11. Once data is loaded click Close & Apply and it will close the query editor and we will see main designer where you can see Dataset and Fields we just created.
    Loading Amazon MWS Reports data in Power BI (List Inventory Example using M Script)

    Loading Amazon MWS Reports data in Power BI (List Inventory Example using M Script)

Conclusion

In this article, we saw how to build sales report by reading data from your Amazon Seller Account. We used simple SQL queries with ZappySys XML Driver to pull data from Amazon MWS API. ZappySys ODBC PowerPack makes it super easy to consume data from virtually any API (JSON / XML or CSV Web Services), no coding required. Download here to get started with ZappySys ODBC drivers.

 

Posted in ODBC PowerPack, Reporting - Microsoft Power BI, XML File / SOAP API Driver and tagged , , .