StoredashboardETLFeed

StoredashboardETLFeed reads data from orderdb, transforms the data in a structured model, and copies the data to Redshift.

Redshift is Amazon’s fully managed, petabyte-scale cloud-based data warehouse service. 

Transformation Process

The steps involved in the transformation process are:

  1. Delete the previously created files by the feed.
  2. Read the last successful feed runtime from the feed run history table. If the feed is a fresh run, the feed runtime would be one (1) day, by default.
  3. Look for any changes in the table schema of the source JSON file. If there are table structure changes, the feed alters the schema in Redshift. If the feed is a fresh run, it creates all the tables in Redshift before loading the data.
  4. Read the data from the order table (MongoDB) and transform it into a structured format. An order document is transformed into the following tables:
    • ordersummary
    • orderderails
    • orderpromotion
    • orderproducts
    • orderpayment
    Note: The structured data is initially saved as .csv files in the output folder.
  5. Create a manifest file in each folder based on the files. Upload the files which are available in the output folders to the given S3 path.
  6. Trigger Redshift’s COPY statement to load the data from S3 to Redshift.

    Note: The S3 details and Redshift cluster details are passed in the Job params. The order DB and feed details are read from the feed’s service config feature.

reportAthenaETLFeed

The feed reportAthenaETLFeed extracts MongoDB document of Order service from omsdb and transform it into the structured .csv files. It uploads the .csv files into AWS S3.

The steps involved in the transformation process are:

  1. Delete the previously created files in the output location.
  2. Parse the serviceConfig field of the Order and Feed services to fetch the Order and Feed DB details. While creating the feed, you have to specify the Feed and Order services in the serviceConfig field.
  3. Read the last successful run time from the feed run history table of the Feed DB. If the feed is run for the first time, extract the last run time from the job parameter or extract the previous day as last run time.
  4. Using the last run time from the step 3, the feed job queries the OMS DB and fetches the orders created and updated since the last run time till the current feed run time, and transforms it into the .csv files with the specific fields. There are five .csv files created for this feed.
  5. Check whether the job created the new files or not. If no orders were created or updated in the specific period, indicates no files were created. In this case, you should abort the job and wait for the next run. If the new files were created, you upload all the files to AWS S3 and wait for the next feed run.

CSV Files

The CSV files are:

Order Summary

The file name is:

ordersummary_<_YYYYMMDDHHmmSS>_<IDX>

The following table lists fields of the file:

Field NameDesccription
uploadtimeSpecifies the time when the file was created.
orderidIndicates the unique ID of the order.
collectionidIndicates the collection ID under which the order was created.
createdtimeContains the time when the order was created.
updatedtimeSpecifies the time when the order was last updated.
createdbyIndicates the user who has created the order in theAdmin console.
storeidIndicates the store in which the order was placed.
useridSpecifies end user who has created the order.
totalsaleContains the total sale of the order calculated from Math.
totaldiscountIndicates the total discount of the order calculated from Math.
totaltaxIndicates the total tax of the order calculated from Math.

Order Details

The file name is:

orderdetails_<_YYYYMMDDHHmmSS>_<IDX>

The following table lists fields of the file:

Field NameDescription
uploadtimeSpecifies the time when the file was created.
orderidSpecifies the unique ID of the order.
collectionidSpecifies the collection ID under which the order was created.
createdtimeSpecifies the time when the order was created.
updatedtimeSpecifies the time when the order was last updated.
createdbySpecifies the user who has created the order in the Admin console.
storeidIndicates the store ID in which the order was placed.
useridSpecifies the end user who has placed the order.
firstnameIndicates the first name of the user.
lastnameIndicates the last name of the user.
itemidIndicates the ID of the item in the order.
productidContains the ID of the product from the catalog.
productnameIndicates the name of the product.
skuSpecifies the SKU ID of the product.
quantityIndicates the quantity placed.
styleContains the style type.
colorIndicates the color of the product placed.
fitIndicates the fit type of the product.
sizeSpecifies the size of the product placed.
categoryidIndicates the category ID under which the product was placed.
vendorSpecifies the vendor name of the product.
itemstatusIndicates the status of the item placed.
paymentidIndicates the payment ID in the order.
paymentmodeIndicates the mode of payment.
paymentvalueIndicates the total amount in the payment.
paymenttypeIndicates the payment type.
currencySpecifies the currency type of the payment.

Order Payment

The file name is:

orderpayment_<_YYYYMMDDHHmmSS>_<IDX>

The following table lists fields of the file:

Field NameDescription
uploadtimeSpecifies the time when the file was created.
orderidContains the unique ID of the order.
collectionidIndicates the collection ID under which the order was created.
createdtimeIndicates the time when the order was created.
updatedtimeIndicates the time when the order was last updated.
createdbyIndicates the user who has created the order in Admin console.
storeidIndicates the store in which the order was placed.
useridSpecifies the end user who has created the order.
paymentidContains the payment ID in the order.
paymentmodeIndicates the mode of payment.
paymentvalueSpecifies the total amount in the payment.
paymenttypeIndicates the payment type.
paymentstatusContains the payment status.

Order Product

The file name is:

orderproduct_<_YYYYMMDDHHmmSS>_<IDX>

The following table lists fields of the file:

Field NameDescription
uploadtimeSpecifies the time when the file was created.
orderidSpecifies the unique ID of the order.
collectionidIndicates the collection ID under which the order was created.
createdtimeSpecifies the time when the order has been created
updatedtimeIndicates the time when the order was last updated
createdbyIndicates the user who created the order in admin.
storeidIndicates the store in which the order was placed.
useridIndicates the end user ID who has created the order.
firstnameIndicates the first name of the user.
lastnameIndicates the last name of the user.
itemidIndicates the ID of the item in the order.
productidSpecifies the ID of the product from the catalog.
productnameSpecifies the name of the product.
skuSpecifies the SKU ID of the product.
quantityContains the quantity placed.
styleIndicates the style type.
colorSpecifies the color of the product placed.
fitIndicates the fit type of the product.
sizeContains the size of the product placed.
categoryidSpecifies the category ID under which the product was placed.
vendorContains the vendor name of the product.
unitpriceSpecifies the price of the product per unit.
salepriceContains the sale price in the order.
modeIndicates the payment mode.
totalsaleIndicates the total sale of the product.

Order Promotion

The file name is:

orderpromotion_<_YYYYMMDDHHmmSS>_<IDX>

The following table lists the fields of the file:

Field NameDescription
uploadtimeIndicates the time when the file has been created.
orderidSpecifies unique ID of the order.
collectionidIndicates the collection ID under which the order was created.
createdtimeContains the time when the order was created.
updatedtimeIndicates the time when the order was last updated.
createdbyContains the user who has created the order in Admin console.
storeidIndicates the store in which the order was placed.
useridIndicates the end user who has created the order.
firstnameIndicates the first name of the user.
lastnameSpecifies the last name of the user.
totalordersaleIndicates the total sale value of the order.
totalordertaxContains the total tax of the order.
totalorderdiscountIndicates the total discount in the order.
offeridSpecifies the offer ID.
promocodeIndicates the promocode used.
promonameSpecifies the promotion name.
promolevelIndicates the promotion level.
modeIndicates the mode.
totaldiscountIndicates the total discount by the promotion.

 

Note: For detailed information about Feeds microservice, see here.

 

 

Revision History

2019-01-24 | Ananthi – Content updated for February 2020 release.
2019-05-08 | Ananthi – Minor copyedits.
2019-05-01 | PLK – Content copyedited.
2019-04-17 | MA – Content uploaded.