Difference between revisions of "Opentaps Analytics Data Loader"
From Opentaps Wiki
Jump to navigationJump to search (→Data Model Reference) |
(→Data Model Reference) |
||
| Line 92: | Line 92: | ||
The following tables and fields can be given: | The following tables and fields can be given: | ||
| − | + | ||
| + | ==== CHANNEL_DIMENSION ==== | ||
| + | |||
| + | {| border="1" cellspacing="5" cellpadding="2" | ||
| + | | Field name || Type || Description | ||
| + | |- | ||
| + | | CHANNEL_ID || String || The Channel identifier | ||
| + | |- | ||
| + | | CHANNEL_NAME || String || The Channel name | ||
| + | |} | ||
| + | |||
| + | |||
* '''SALES_TEAM_DIMENSION''' : SALES_TEAM_ID, SALES_TEAM_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 | * '''SALES_REP_DIMENSION''' : SALES_REP_ID, SALES_TEAM_ID, SALES_TEAM_NAME, FIRST_NAME, LAST_NAME | ||
Revision as of 12:25, 12 April 2011
Contents
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.
- Checkout component into hot-deploy. Use
svn co svn://svn.opentaps.org/opentaps/modules/analytics-upload/versions/1.5/trunk analytics-upload - Adjust settings in config/analytics-uploader.properties. Webkey should be the one for your store in opentaps Analytics.
- Note there are two scripts value in configuration file, default one to use with opentaps instance and another for OfBiz.
- 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 | The Channel identifier |
| CHANNEL_NAME | String | The 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
- 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
Notes:
- Fact tables IDs must match the corresponding IDs in the dimension tables
- Dates should be formatted as YYYY-MM-DD
