Difference between revisions of "Opentaps Analytics Data Loader"

From Opentaps Wiki
Jump to navigationJump to search
(→‎Data Model Reference)
(→‎CHANNEL_DIMENSION)
Line 95: Line 95:
 
==== CHANNEL_DIMENSION ====
 
==== CHANNEL_DIMENSION ====
  
−
{| border="1" cellspacing="5" cellpadding="2"
+
{| border="1" cellspacing="0" cellpadding="5"
−
| Field name || Type || Description
+
!width="200"|Field name
 +
!width="200"|Type
 +
!width="500"|Description
 
|-  
 
|-  
−
| CHANNEL_ID || String || The Channel identifier
+
| CHANNEL_ID || String (60) || The Channel identifier
 
|-
 
|-
−
| CHANNEL_NAME || String || The Channel name
+
| CHANNEL_NAME || String (100) || The Channel name
 
|}
 
|}
  
 +
==== SALES_TEAM_DIMENSION ====
  
−
* '''SALES_TEAM_DIMENSION''' : SALES_TEAM_ID, SALES_TEAM_NAME
+
{| border="1" cellspacing="0" cellpadding="5"
−
* '''SALES_REP_DIMENSION''' : SALES_REP_ID, SALES_TEAM_ID, SALES_TEAM_NAME, FIRST_NAME, LAST_NAME
+
!width="200"|Field name
−
* '''CUSTOMER_DIMENSION''' : CUSTOMER_ID, CUSTOMER_NAME, COMPANY_NAME, FIRST_NAME, LAST_NAME, ATTN_NAME, STREET_ADDRESS_LINE1, CITY, STATE, REGION, POSTAL_CODE3DIGIT, POSTAL_CODE, COUNTRY, PHONE_COUNTRY, PHONE_AREA_CODE, PHONE_NUMBER, PRIMARY_EMAIL, OTHER_EMAIL, CUSTOMER_TYPE, SOURCE, INDUSTRY, SALES_TEAM_NAME, CREATED_DATE_AND_TIME
+
!width="200"|Type
−
* '''PRODUCT_DIMENSION''' : PRODUCT_ID, SKU, UPC, PRODUCT_NAME, BRAND, CATEGORY, SIZE, WIDTH, LENGTH, HEIGHT, WEIGHT, DIAMETER, PRODUCT_TYPE, SUPPLIER_NAME, ORG_SUPPLIER_ID, EST_BASE_CURRENCY_COST, STYLE, COLOR
+
!width="500"|Description
−
* '''ORDER_ITEM_FACT''' : ORDER_ID, ORDER_ITEM_SEQ_ID, CUSTOMER_ID, CHANNEL_ID, SALES_REP_ID, SALES_REP_TEAM_ID, ORDER_DATE, PRODUCT_ID, QUANTITY, GROSS_AMOUNT_BASE_CURRENCY, NET_AMOUNT_BASE_CURRENCY, DISCOUNT_AMOUNT_BASE_CURRENCY, RESOLUTION_STATUS, RETURNED_QUANTITY, RETURNED_GROSS_AMOUNT_BASE_CURRENCY, RETURNED_DISCOUNT_AMOUNT_BASE_CURRENCY, RETURNED_NET_AMOUNT_BASE_CURRENCY
+
|-
−
* '''RETURN_ITEM_FACT''' : RETURN_ID, RETURN_ITEM_SEQ_ID, CUSTOMER_ID, CHANNEL_ID, ORDER_DATE, RETURN_DATE, PRODUCT_ID, QUANTITY, GROSS_AMOUNT_BASE_CURRENCY, NET_AMOUNT_BASE_CURRENCY, DISCOUNT_AMOUNT_BASE_CURRENCY, RESOLUTION_STATUS
+
| SALES_TEAM_ID || String (60) || The Sales team identifier
 +
|-
 +
| SALES_TEAM_NAME || String (100) || The Sales team name
 +
|}
 +
 
 +
==== SALES_REP_DIMENSION ====
 +
 
 +
{| border="1" cellspacing="0" cellpadding="5"
 +
!width="200"|Field name
 +
!width="200"|Type
 +
!width="500"|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 ====
 +
 
 +
{| border="1" cellspacing="0" cellpadding="5"
 +
!width="200"|Field name
 +
!width="200"|Type
 +
!width="500"|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, given as "YYYY-MM-DD hh:mm:ss"
 +
|}
 +
 
 +
==== PRODUCT_DIMENSION ====
 +
 
 +
{| border="1" cellspacing="0" cellpadding="5"
 +
!width="200"|Field name
 +
!width="200"|Type
 +
!width="500"|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) ||
 +
|-
 +
| HEIGHT || Numeric (18,6) ||
 +
|-
 +
| WEIGHT || Numeric (18,6) ||
 +
|-
 +
| DIAMETER || Numeric (18,6) ||
 +
|-
 +
| PRODUCT_TYPE || String (100) ||
 +
|-
 +
| SUPPLIER_NAME || String (100) ||
 +
|-
 +
| ORG_SUPPLIER_ID || String (60) ||
 +
|-
 +
| EST_BASE_CURRENCY_COST || Numeric (18,6) ||
 +
|}
 +
 
 +
==== ORDER_ITEM_FACT ====
 +
 
 +
{| border="1" cellspacing="0" cellpadding="5"
 +
!width="200"|Field name
 +
!width="200"|Type
 +
!width="500"|Description
 +
|-
 +
| ORDER_ID || String (60) ||
 +
|-
 +
| ORDER_ITEM_SEQ_ID || String (100) ||
 +
|-
 +
| CUSTOMER_ID || String (60) ||
 +
|-
 +
| CHANNEL_ID || String (60) ||
 +
|-
 +
| SALES_REP_ID || String (60)||
 +
|-
 +
| SALES_REP_TEAM_ID || String (60) ||
 +
|-
 +
| ORDER_DATE || String (Date) || Format is ''YYYY-MM-DD''
 +
|-
 +
| PRODUCT_ID || String (60) ||
 +
|-
 +
| QUANTITY || Numeric (18,6) ||
 +
|-
 +
| 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) ||
 +
|-
 +
| 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 ====
  
−
Notes:
+
{| border="1" cellspacing="0" cellpadding="5"
−
* Fact tables IDs must match the corresponding IDs in the dimension tables
+
!width="200"|Field name
−
* Dates should be formatted as ''YYYY-MM-DD''
+
!width="200"|Type
 +
!width="500"|Description
 +
|-
 +
| RETURN_ID || String (60) ||
 +
|-
 +
| RETURN_ITEM_SEQ_ID || String (100) ||
 +
|-
 +
| CUSTOMER_ID || String (60) ||
 +
|-
 +
| CHANNEL_ID || String (60) ||
 +
|-
 +
| ORDER_DATE || String (Date) || Format is ''YYYY-MM-DD''
 +
|-
 +
| RETURN_DATE || String (Date) || Format is ''YYYY-MM-DD''
 +
|-
 +
| PRODUCT_ID || String (60) ||
 +
|-
 +
| QUANTITY || Numeric (18,6) ||
 +
|-
 +
| 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) ||
 +
|}

Revision as of 13:17, 12 April 2011

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

Field name Type Description
CHANNEL_ID String (60) The Channel identifier
CHANNEL_NAME String (100) The Channel name

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, given as "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)
HEIGHT Numeric (18,6)
WEIGHT Numeric (18,6)
DIAMETER Numeric (18,6)
PRODUCT_TYPE String (100)
SUPPLIER_NAME String (100)
ORG_SUPPLIER_ID String (60)
EST_BASE_CURRENCY_COST Numeric (18,6)

ORDER_ITEM_FACT

Field name Type Description
ORDER_ID String (60)
ORDER_ITEM_SEQ_ID String (100)
CUSTOMER_ID String (60)
CHANNEL_ID String (60)
SALES_REP_ID String (60)
SALES_REP_TEAM_ID String (60)
ORDER_DATE String (Date) Format is YYYY-MM-DD
PRODUCT_ID String (60)
QUANTITY Numeric (18,6)
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)
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)
RETURN_ITEM_SEQ_ID String (100)
CUSTOMER_ID String (60)
CHANNEL_ID String (60)
ORDER_DATE String (Date) Format is YYYY-MM-DD
RETURN_DATE String (Date) Format is YYYY-MM-DD
PRODUCT_ID String (60)
QUANTITY Numeric (18,6)
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=7037"