Deleted revenue records still appear in the data warehouse

The OLAP data warehouse for CRM stores information that exists in the database, and it is updated with new, edited, and deleted records each time an ETL refresh is run.  However, when revenue records are deleted from the front end of the database, in some cases those revenue records are not deleted from the data warehouse.
These deleted gifts are appearing in the data warehouse due to a foreign key being disabled on the revenue split table.  The warehouse pulls information from the revenuesplit table so with the constraint being disabled the gift was not being deleted from this table when it was deleted from the revenue table.


To resolve this issue:

Enable the foreign key FK_REVENUESPLIT_REVENUEID.
Reset the ETL


To view a list of the gifts that were deleted but still appear in the revenuesplit table the query below will provide the results:

select RS.REVENUEID, RA.AUDITDATE
from dbo.REVENUESPLIT RS
left join dbo.REVENUE R on R.ID = RS.REVENUEID
left join dbo.REVENUEAUDIT RA on RA.AUDITRECORDID = RS.REVENUEID and RA.AUDITTYPECODE = 2
where R.ID is null
order by RA.AUDITDATE

Environment

 2.91.1001.95

Was this article helpful?