Difference between revisions of "Opentaps Analytics Data Loader"
From Opentaps Wiki
Jump to navigationJump to search (→CHANNEL_DIMENSION) |
(→SALES_TEAM_DIMENSION) |
||
| Line 109: | Line 109: | ||
==== SALES_TEAM_DIMENSION ==== | ==== SALES_TEAM_DIMENSION ==== | ||
| − | This table is for the sales teams of your company. | + | This dimension table is for the sales teams of your company. |
{| border="1" cellspacing="0" cellpadding="5" | {| border="1" cellspacing="0" cellpadding="5" | ||
Revision as of 16:05, 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
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
| 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) |
