search

ivylabs / suitecrm-data-integration

7 stars 5 forks source link

Create Lead Tracking Fact Table and ETL #14

Closed harrisward closed 4 years ago

samus-aran commented 4 years ago

Tested this and the F_LEAD_TRACKING is not pulling anything in even though there is a large amount of data in staging tables.

Screenshot_2020-03-09 localhost 9009 mariadb suitecrm_analytics_dwh_3 phpMyAdmin 4 8 5

samus-aran commented 4 years ago

Not alot to go on for the error log:

samus-aran commented 4 years ago

Trying to process the query in F_LEAD_TRACKING 

SELECT
  id
, parent_id
, date_created
, created_by
, field_name
, data_type
, before_value_string
, after_value_string
, before_value_text
, after_value_text
FROM S_LEADS_AUDIT
WHERE field_name = 'status'
AND (parent_id = '${LEAD_ID}' OR 1 = '${LEAD_ID}')

I'm not understanding the parent_id but I'm assuming that is to ensure it has a link to a LEAD. That doesn't seem to successfully return in my script. The Opportunities Audit SQL excludes this part in the SQL.

samus-aran commented 4 years ago

I updated both the transformations:

to exclude the:

AND (parent_id = '${LEAD_ID}' OR 1 = '${LEAD_ID}')

and now it's running through the successfully.

Screenshot_2020-03-09 localhost 9009 mariadb suitecrm_analytics_dwh_4 phpMyAdmin 4 8 5

samus-aran commented 4 years ago

Just checking a few of the records and you would know better than I would if looks right :)

  1. Screenshot_2020-03-09 localhost 9009 mariadb suitecrm_analytics_dwh_4 F_LEAD_TRACKING phpMyAdmin 4 8 5 So this is the F_LEAD_TRACKING and this is the change log .. Screenshot_2020-03-09_changelog and this is the audit logs Screenshot_2020-03-09 audit fields

harrisward commented 4 years ago

F_LEAD_TRACKING is not being populated. Possibly related to the point Ashley made about the additional SQL clause: AND (parent_id = '${LEAD_ID}' OR 1 = '${LEAD_ID}')

This is usually used for debugging. Need to fix this

harrisward commented 4 years ago

Set the LEAD_ID variable to default to 1 in the transformation which by design will bring in all available records from the staging tables

harrisward commented 4 years ago

Tested and working