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?


Thanks for your feedback! Did this solve your issue?

Comments (optional):


Thanks for your feedback!
We're glad it was helpful but sorry it didn’t solve your issue. If you need assistance, click Chat with Support below.
We’re sorry to hear that. Please tell us why.

 I don't like how this works.

 The answer is confusing.

 The answer didn't match what I was searching for.

Additional Comments (optional):


Thanks for your feedback! If you need assistance, click Chat with Support below.
Thanks for your feedback. Help us make our products even better by sharing details in our Idea Banks or our online Community.
Thanks for letting us know. We'll work on clarifying the information in the article. If you need assistance, click Chat with Support below.
Thanks for letting us know. We'll work on updating the search engine to return more relevant results.