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