"D_PRODUCTS" Table - The Product Category Information can get lost, depending on when the ./run script is used.

[johnM2401]

(This bug has gotten rather long, so please let me know if you would rather I split it up into separate items)

Describe the bug It appears as though there are some issues with how the Product Category information pulls in, depending on which stage of the process the ./run script is used.

These are the issues I've noticed:

• Neither the "PRODUCT_CATEGORY_NAME" nor the "PRODUCT_CATEGORY_DESCRIPTION" columns get updated after the initial pull.
• If a new Product is associated to an existing Category in the CRM, the Category information is not populated in the DWH if the Category already exists in the DWH for another Product.
• If the Product Title is changed in the CRM, the next ./run will set any instances of this Product's "PRODUCT_CATEGORY_NAME" and "PRODUCT_CATEGORY_DESCRIPTION to "Not Set"
• If the Product already exists in the DWH without an associated Category, associating one in the CRM will only populate the "PRODUCT_CATEGORY_ID" value on the next ./run

Solution File D_PRODUCTS

To Reproduce Steps to reproduce the behavior:

For: _"Neither the "PRODUCT_CATEGORY_NAME" nor the "PRODUCT_CATEGORYDESCRIPTION" columns get updated after the initial pull."

1. Create a new Product and Product Category, associate them to one another, but do not populate the Product Category's Description
2. Run the ./run script
3. Navigate to the D_PRODUCTS table in the DWH to see the entry for this Product & Category
4. In the CRM, edit the Product Category Record and modify the contents of the Name and Description fields
5. Run the ./run script again
6. Navigate to the D_PRODUCTS table and see that the neither the PRODUCT_CATEGORY_NAME or PRODUCT_CATEGORY_DESCRIPTION have been updated.

For: "If a new Product is associated to an existing Category in the CRM, the Category information is not populated in the DWH if the Category already exists in the DWH for another Product."

1. Create a new Product and Product Category, associate them to one another.
2. Push this to the DWH with the ./run script
3. Navigate to the D_PRODUCTS table in the DWH to see the entry for this Product & Category
4. In the CRM, create a new Product and associate it to the Product Category created in step 1.
5. Run the ./run script
6. Navigate to the D_PRODUCTS table
7. You will see a new row for this product with the correct PRODUCT_CATEGORY_ID, but neither the Name or Description are populated, which can bring incorrect results in Reports/Data Exporter where Category Name is used as a parameter.

For: _"If the Product Title is changed in the CRM, the next ./run will set any instances of this Product's "PRODUCT_CATEGORY_NAME" and "PRODUCT_CATEGORYDESCRIPTION to "Not Set""

1. Create a new Product and Product Category, associate them to one another.
2. Run the ./run script
3. Navigate to the D_PRODUCTS table in the DWH to see the entry for this Product & Category
4. In the CRM, edit the Product "Title" field
5. Push this to the DWH with the ./run script
6. Navigate back to D_PRODUCTS
7. See that the PRODUCT_CATEGORY_NAME and PRODUCT_CATEGORY_DESCRIPTION fields have changed to "Not Set" for this Product's row, despite the Product Category not being modified.

For: _"If the Product already exists in the DWH without an associated Category, associating one in the CRM will only populate the "PRODUCT_CATEGORYID" value on the next ./run"

1. Go to into the CRM and create both a Product and Product Category, but do NOT associate them to one another.
2. Pull this into the CRM with the ./run script
3. View that this item is in the D_PRODUCTS table, with no associated Category
4. In the CRM, associate the Product to the new Category
5. Pull this into the CRM with the ./run script
6. View that this Product does get a Product Category ID, but no other information is populated

Expected behavior The Product Category information should stay as up-to-date as possible with the CRM, to avoid confusion

Screenshots

Creating the first Product, with Category created & associated at the same time:

Second Product, associating the Existing Category:

New Product created, before association to a Category:

After associating the Category in the CRM:

Which appears to cause the items to appear with no Category in Analytics:

[johnM2401]

Hey @harrisward

Updated this with more information/another item I've noticed Today. As I don't have the permissions to add a Project to this, would you be able to do so for now? (So this can be tracked against the Project board)

Thanks!

[harrisward]

This is part of a wider issue related to history tracking. We need to rethink how we load changes that are related to other table / field changes