Difference between revisions of "Opentaps Analytics Data Loader"

From Opentaps Wiki
Jump to navigationJump to search
(→‎opentaps and OFBiz)
(→‎API)
Line 12: Line 12:
  
 
== API ==
 
== 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 ===
 
=== 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" <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
 +
 +
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" \
 +
  <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
 +
 +
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" <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
 +
 +
  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" <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
 +
 +
  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 ===
 
=== Data Model Reference ===
 +
 +
The following tables and fields can be given:
 +
* '''CHANNEL_DIMENSION''' : CHANNEL_ID, CHANNEL_NAME
 +
* '''SALES_TEAM_DIMENSION''' : SALES_TEAM_ID, SALES_TEAM_NAME
 +
* '''SALES_REP_DIMENSION''' : SALES_REP_ID, SALES_TEAM_ID, SALES_TEAM_NAME, FIRST_NAME, LAST_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
 +
* '''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
 +
* '''MAP_LOCATION_DIMENSION''' : CITY, STATE, REGION, COUNTRY, LATITUDE, LONGITUDE
 +
* '''CUSTOMER_HISTORY_FACT''' : CUSTOMER_ID, AS_OF_DATE, LIFETIME_NUMBER_OF_ORDERS, LIFETIME_NUMBER_OF_RETURNS, LIFETIME_ORDER_TOTAL, LIFETIME_RETURN_TOTAL, DAYS_SINCE_LAST_ORDER, NUMBER_OF_ORDERS_LAST_12_MONTH, NUMBER_OF_RETURNS_LAST_12_MONTH, TOTAL_ORDERS_LAST_12_MONTH, TOTAL_RETURNS_LAST_12_MONTH, LIFETIME_NET_ORDER_VALUE, LIFETIME_GROSS_PROFIT, RECEIVABLES_BALANCE, OVERDUE_RECEIVABLES_BALANCE, RECENCY_SCORE, FREQUENCY_SCORE, MONETARY_SCORE, RFM_SCORE, RECENCY_SCORE_CHANGE, FREQUENCY_SCORE_CHANGE, MONETARY_SCORE_CHANGE, RFM_SCORE_CHANGE
 +
* '''CUSTOMER_HISTORY_FACT_VERIFICATION''' : CUSTOMER_ID, AS_OF_DATE, EXPECTED_LIFETIME_NUMBER_OF_ORDERS, EXPECTED_LIFETIME_NUMBER_OF_RETURNS, EXPECTED_LIFETIME_ORDER_TOTAL, EXPECTED_LIFETIME_RETURN_TOTAL, EXPECTED_LIFETIME_NET_ORDER_VALUE, DELTA_LIFETIME_NUMBER_OF_ORDERS, DELTA_LIFETIME_NUMBER_OF_RETURNS, DELTA_LIFETIME_ORDER_TOTAL, DELTA_LIFETIME_RETURN_TOTAL, DELTA_LIFETIME_NET_ORDER_VALUE, MAX_ABS_DELTA
 +
* '''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
 +
* '''DASHBOARD_TOP_CUSTOMERS_FACT''' : CUSTOMER_ID, CUSTOMER_NAME, SALES_VALUE
 +
* '''DASHBOARD_CUSTOMER_LOCATION_FACT''' : CITY, STATE, COUNTRY, NUMBER, LATITUDE, LONGITUDE
 +
* '''DASHBOARD_SALES_OVER_TIME_FACT''' : CATEGORY, DATE, SOLD_VALUE
 +
* '''DASHBOARD_SALES_BY_CATEGORY_COUNTRY_MONTH_FACT''' : PRODUCT_CATEGORY, MONTH_NAME, CUSTOMER_COUNTRY, SOLD_PRICE
 +
 +
 +
Notes:
 +
* Fact tables IDs must match the corresponding IDs in the dimension tables
 +
* Dates should be formatted as ''YYYY-MM-DD''

Revision as of 16:13, 11 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 : CHANNEL_ID, CHANNEL_NAME
  • SALES_TEAM_DIMENSION : SALES_TEAM_ID, SALES_TEAM_NAME
  • SALES_REP_DIMENSION : SALES_REP_ID, SALES_TEAM_ID, SALES_TEAM_NAME, FIRST_NAME, LAST_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
  • 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
  • MAP_LOCATION_DIMENSION : CITY, STATE, REGION, COUNTRY, LATITUDE, LONGITUDE
  • CUSTOMER_HISTORY_FACT : CUSTOMER_ID, AS_OF_DATE, LIFETIME_NUMBER_OF_ORDERS, LIFETIME_NUMBER_OF_RETURNS, LIFETIME_ORDER_TOTAL, LIFETIME_RETURN_TOTAL, DAYS_SINCE_LAST_ORDER, NUMBER_OF_ORDERS_LAST_12_MONTH, NUMBER_OF_RETURNS_LAST_12_MONTH, TOTAL_ORDERS_LAST_12_MONTH, TOTAL_RETURNS_LAST_12_MONTH, LIFETIME_NET_ORDER_VALUE, LIFETIME_GROSS_PROFIT, RECEIVABLES_BALANCE, OVERDUE_RECEIVABLES_BALANCE, RECENCY_SCORE, FREQUENCY_SCORE, MONETARY_SCORE, RFM_SCORE, RECENCY_SCORE_CHANGE, FREQUENCY_SCORE_CHANGE, MONETARY_SCORE_CHANGE, RFM_SCORE_CHANGE
  • CUSTOMER_HISTORY_FACT_VERIFICATION : CUSTOMER_ID, AS_OF_DATE, EXPECTED_LIFETIME_NUMBER_OF_ORDERS, EXPECTED_LIFETIME_NUMBER_OF_RETURNS, EXPECTED_LIFETIME_ORDER_TOTAL, EXPECTED_LIFETIME_RETURN_TOTAL, EXPECTED_LIFETIME_NET_ORDER_VALUE, DELTA_LIFETIME_NUMBER_OF_ORDERS, DELTA_LIFETIME_NUMBER_OF_RETURNS, DELTA_LIFETIME_ORDER_TOTAL, DELTA_LIFETIME_RETURN_TOTAL, DELTA_LIFETIME_NET_ORDER_VALUE, MAX_ABS_DELTA
  • 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
  • DASHBOARD_TOP_CUSTOMERS_FACT : CUSTOMER_ID, CUSTOMER_NAME, SALES_VALUE
  • DASHBOARD_CUSTOMER_LOCATION_FACT : CITY, STATE, COUNTRY, NUMBER, LATITUDE, LONGITUDE
  • DASHBOARD_SALES_OVER_TIME_FACT : CATEGORY, DATE, SOLD_VALUE
  • DASHBOARD_SALES_BY_CATEGORY_COUNTRY_MONTH_FACT : PRODUCT_CATEGORY, MONTH_NAME, CUSTOMER_COUNTRY, SOLD_PRICE


Notes:

  • Fact tables IDs must match the corresponding IDs in the dimension tables
  • Dates should be formatted as YYYY-MM-DD
Retrieved from "https://docs.opentaps.org/docs/index.php?title=Opentaps_Analytics_Data_Loader&oldid=7034"