Opentaps Analytics Data Loader

From Opentaps Wiki
Revision as of 16:18, 12 April 2011 by Sichen (talk | contribs) (→‎CUSTOMER_DIMENSION)
Jump to navigationJump to search

Uploaders

opentaps and OFBiz

analytics-upload component help to gather and upload analytical data to server. A few steps should be done to install it with opentaps or OfBiz instance.

  1. Checkout component into hot-deploy. Use svn co svn://svn.opentaps.org/opentaps/modules/analytics-upload/versions/1.5/trunk analytics-upload
  2. Adjust settings in config/analytics-uploader.properties. Webkey should be the one for your store in opentaps Analytics.
  3. Note there are two scripts value in configuration file, default one to use with opentaps instance and another for OfBiz.
  4. Start instance and run analytics.uploadAnalyticalData service.

API

Data Loading is done via an HTTP POST servlet.

The following parameter are required:

  • userlogin
  • password
  • webkey

Then the data to load should be given as one parameter per field using the format idx.tablename.fieldname=value where idx is a string used both to order the import (if mutliple record depend on each other via a FK) and discriminate which fields are in the same DB row.

Possible HTTP error codes are:

  • 400 Bad Request : missing parameter, invalid field name, bad format, no data to load
  • 401 Unauthorized : authentication failed
  • 404 Not Found : given webkey does not correspond to an existing Store
  • 403 Forbidden : tried to modify a table or field that cannot be modified
  • 409 Conflict : found the same rows defined twice
  • 500 Internal error : other exception during import

Examples

The following examples are using the curl command line utility to perform the HTTP POSTs.

Posting data in the CHANNEL_DIMENSION: 

 curl -i -d userlogin=admin -d password=opentaps -d webkey=thisisatest1234 -d "0.channel_dimension.channel_id=MY_CHANNEL" -d "0.channel_dimension.channel_name=My Channel" http://127.0.0.1:8080/analytics/control/loaddata

Returned Success message would be like: 

 HTTP/1.1 200 OK
 ...
 <html>
 <head>
 <title>Dataload: success</title>
 </head>
 <body class="success">
 Successfully loaded 1 rows
 </body>
 </html>

Multiple rows can be inserted in the same request, using the IDX part to order the insertion in case a row needs to reference to a previously loaded row: 

 curl -i -d userlogin=admin -d password=opentaps -d webkey=thisisatest1234 \
  -d "0.sales_team_dimension.sales_team_id=MY_TEAM" -d "0.sales_team_dimension.sales_team_name=My Sales Team" \
  -d "1.sales_rep_dimension.sales_rep_id=MY_SALESREP" -d "1.sales_rep_dimension.sales_team_id=MY_TEAM" \
  http://127.0.0.1:8080/analytics/control/loaddata

Example with bad authentication: 

 curl -i -d userlogin=admin -d password=opentapse -d webkey=thisisatest1234 -d "0.channel_dimension.channel_id=MY_CHANNEL" -d "0.channel_dimension.channel_name=My Channel" http://127.0.0.1:8080/analytics/control/loaddata

 HTTP/1.1 401 Unauthorized
 ...
 <html>
 <head>
 <title>Dataload: error</title>
 </head>
 <body class="error">
 Authentication failed.
 </body>
 </html>

Example of Error when trying to add data in an in-existing field: 

 curl -i -d userlogin=admin -d password=opentaps -d webkey=thisisatest1234 -d "0.channel_dimension.channel_id=MY_CHANNEL" -d "0.channel_dimension.channel_description=My Channel" http://127.0.0.1:8080/analytics/control/loaddata

 HTTP/1.1 500 Internal Server Error
 ...
 <html>
 <head>
 <title>Dataload: error</title>
 </head>
 <body class="error">
 Data Load error: Error processing the SQL Query
 Caused by: Unknown column 'CHANNEL_DESCRIPTION' in 'field list'
 </body>
 </html>

Data Model Reference

The following tables and fields can be given:

CHANNEL_DIMENSION

This dimension table is for the sales channels of your company (ie, Phone, Online, Catalog, Store.)

Field name Type Description
CHANNEL_ID String (60) Unique ID of the sales channel
CHANNEL_NAME String (100) Descriptive name of the sales channel

SALES_TEAM_DIMENSION

This dimension table is for the sales teams of your company.

Field name Type Description
SALES_TEAM_ID String (60) Unique ID of your sales team.
SALES_TEAM_NAME String (100) Descriptive name of your sales team.

SALES_REP_DIMENSION

This dimension table is for the sales representatives of your company.

Field name Type Description
SALES_REP_ID String (60) Unique ID of a sales representative.
SALES_TEAM_ID String (60) ID of the sales team for the sales representative. References SALES_TEAM_DIMENSION
SALES_TEAM_NAME String (100) Descriptive name of the sales team.
FIRST_NAME String (100) First name of the sales representative
LAST_NAME String (100) Last name of the sales representative

CUSTOMER_DIMENSION

This dimension is for your company's customers. Currently the table supports address line, so use it for the most meaningful one for your business, ie the customer's home address.

Field name Type Description
CUSTOMER_ID String (60) The ID of the Customer
CUSTOMER_NAME String (255) The Customer name
COMPANY_NAME String (255) The Customer company name
INDUSTRY String (60) The Customer industry
FIRST_NAME String (100) The Customer first name
LAST_NAME String (100) The Customer last name
ATTN_NAME String (100) The address attention name
STREET_ADDRESS_LINE1 String (255) Street of the customer's address
CITY String (60) City of the customer's address
STATE String (60) State of the customer's address
REGION String (60) Region of the customer's address, if relevant
COUNTRY String (100) Country of the customer's address
POSTAL_CODE3DIGIT String (3) First 3 digits of the postal code for the customer's address
POSTAL_CODE String (60) Full postal code for the customer's address
PHONE_COUNTRY String (60) Country code of the customer's phone number
PHONE_AREA_CODE String (60) Area code of the customer's phone number
PHONE_NUMBER String (60) Remaining digits of customer's phone number
PRIMARY_EMAIL String (255) Customer's primary email address
OTHER_EMAIL String (255) Customer's other email address
CUSTOMER_TYPE String (60) Type for the customer. Fill in whatever you want.
SOURCE String (60) Source code of the Customer. Fill in whatever you want.
SALES_TEAM_NAME String (255) The name of Sales Team associated to the Customer, from SALES_TEAM_DIMENSION
CREATED_DATE_AND_TIME Timestamp Time and date when customer was first created. Format is YYYY-MM-DD hh:mm:ss

PRODUCT_DIMENSION

Field name Type Description
PRODUCT_ID String (60) The Product identifier
SKU String (60) The Product SKU code
UPC String (60) The Product UPC code
PRODUCT_NAME String (100) The Product name
BRAND String (100) The Product brand
CATEGORY String (100) The Product category name
SIZE String (100) The Product size
STYLE String (100) The Product style
COLOR String (100) The Product color
WIDTH Numeric (18,6) The Product width
LENGTH Numeric (18,6) The Product length
HEIGHT Numeric (18,6) The Product height
WEIGHT Numeric (18,6) The Product weight
DIAMETER Numeric (18,6) The Product diameter
PRODUCT_TYPE String (100) The Product type
SUPPLIER_NAME String (100) The Product supplier name
ORG_SUPPLIER_ID String (60) The Product supplier organization identifier
EST_BASE_CURRENCY_COST Numeric (18,6) The Product estimated cost

ORDER_ITEM_FACT

Field name Type Description
ORDER_ID String (60) The order identifier
ORDER_ITEM_SEQ_ID String (100) The order item identifier
CUSTOMER_ID String (60) The customer identifier, references the CUSTOMER_DIMENSION
CHANNEL_ID String (60) The channel identifier, references the CHANNEL_DIMENSION
SALES_REP_ID String (60) The sales representative identifier, references the SALES_REP_DIMENSION
SALES_REP_TEAM_ID String (60) The sales team identifier, references the SALES_TEAM_DIMENSION
ORDER_DATE String (Date) The date the order was placed, format is YYYY-MM-DD
PRODUCT_ID String (60) The product identifier, references the PRODUCT_DIMENSION
QUANTITY Numeric (18,6) The quantity of item ordered
GROSS_AMOUNT_BASE_CURRENCY Numeric (18,2)
NET_AMOUNT_BASE_CURRENCY Numeric (18,2)
DISCOUNT_AMOUNT_BASE_CURRENCY Numeric (18,2)
RESOLUTION_STATUS String (25)
RETURNED_QUANTITY Numeric (18,6) The quantity of the item that was returned
RETURNED_GROSS_AMOUNT_BASE_CURRENCY Numeric (18,2)
RETURNED_DISCOUNT_AMOUNT_BASE_CURRENCY Numeric (18,2)
RETURNED_NET_AMOUNT_BASE_CURRENCY Numeric (18,2)

RETURN_ITEM_FACT

Field name Type Description
RETURN_ID String (60) The return identifier
RETURN_ITEM_SEQ_ID String (100) The return item identifier
CUSTOMER_ID String (60) The customer identifier, references the CUSTOMER_DIMENSION
CHANNEL_ID String (60) The channel identifier, references the CHANNEL_DIMENSION
ORDER_DATE String (Date) The date the original order was placed, format is YYYY-MM-DD
RETURN_DATE String (Date) The date the return was performed, format is YYYY-MM-DD
PRODUCT_ID String (60) The product identifier, references the PRODUCT_DIMENSION
QUANTITY Numeric (18,6) The quantity of product returned
GROSS_AMOUNT_BASE_CURRENCY Numeric (18,2)
NET_AMOUNT_BASE_CURRENCY Numeric (18,2)
DISCOUNT_AMOUNT_BASE_CURRENCY Numeric (18,2)
RESOLUTION_STATUS String (25)
Retrieved from "https://docs.opentaps.org/docs/index.php?title=Opentaps_Analytics_Data_Loader&oldid=7048"