Opentaps Analytics Data Loader

From Opentaps Wiki
Revision as of 16:03, 12 April 2011 by Sichen (talk | contribs) (→‎CHANNEL_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 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

Field name Type Description
SALES_TEAM_ID String (60) The Sales team identifier
SALES_TEAM_NAME String (100) The Sales team name

SALES_REP_DIMENSION

Field name Type Description
SALES_REP_ID String (60) The Sales representative identifier
SALES_TEAM_ID String (60) The Sales team identifier, references SALES_TEAM_DIMENSION
SALES_TEAM_NAME String (100) The Sales team name
FIRST_NAME String (100) The Sales representative first name
LAST_NAME String (100) The Sales representative last name

CUSTOMER_DIMENSION

Field name Type Description
CUSTOMER_ID String (60) The Customer identifier
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) The address street
CITY String (60) The address city
STATE String (60) The address state
REGION String (60) The address region
COUNTRY String (100) The address country
POSTAL_CODE3DIGIT String (3) The address first 3 digits of the postal code
POSTAL_CODE String (60) The address postal code
PHONE_COUNTRY String (60) The phone number country code
PHONE_AREA_CODE String (60) The phone number area code
PHONE_NUMBER String (60) The rest of the phone number
PRIMARY_EMAIL String (255) The Customer primary email address
OTHER_EMAIL String (255) The Customer other email address
CUSTOMER_TYPE String (60) The Customer type
SOURCE String (60) The source of the Customer
SALES_TEAM_NAME String (255) The name of Sales Team associated to the Customer
CREATED_DATE_AND_TIME Timestamp The time and date, 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=7043"