search

ivylabs / suitecrm-data-integration

6 stars 5 forks source link

Line Items lose PRODUCT_TK when deleted #170

Closed harrisward closed 3 years ago

harrisward commented 3 years ago

Describe the bug When a line item fact (F_SALES & F_INVOICE_MANAGEMENT records) is deleted in the source system the PRODUCT_ID is set to NULL. Because of this, when the updated fact is loaded into the DWH the PRODUCT_ID of NULL results in a PRODUCT_TK of 0 also (no valid dimension records for the fact exists).

We want to retain the existing PRODUCT_TK of the fact before it was set to deleted.

**Solution File TBD

To Reproduce Steps to reproduce the behavior:

  1. Create an Invoice with a Product Line
  2. Run the ETL
  3. Check the Invoice Report. The Invoice Line Item will have a product set
  4. Delete the Invoice Line Item from the invoice
  5. Run the ETL
  6. Check the Invoice Report. The Invoice Line Item is "Not Set" (This means the fact record is assigned to the 0 PRODUCT)TK)

Expected behavior The Fact record is assigned to the correct PRODUCT_TK even after the record is deleted in the source System

Screenshots NA

Additional context NA

fraserward commented 3 years ago

Updated Sales and Invoice Facts and F_SALES DDL SQL

Pull Request: https://github.com/ivylabs/suitecrm-data-integration/pull/172

johnM2401 commented 3 years ago

Hey @harrisward This ticket looks good to me. However, it looks like I don't have permission to move this into "Done"

Would you be able to give Ross and I permission for this board, when you get a chance?