I have the following problem.
We load a delta from CRM to an ODS in BW for the Customer Contacts. A Customer Contact can have different statusses; E0001 (OPEN) and E0003 (CLOSED). And a number of Activities.
When the ODS had loaded the Delta, we have a Delta load to the cube.
In many cases, a Customer Contact begins with status E0001-> OPEN, and changes to E0003 -> Closed. Also, the number of Activities changes.
So, therefore the data in the ODS and Cube changes from status E0001 to E0003.
The user has a query on this cube. They would like to see the contacts with status E0001 only. The problem is, the query shows Customer Contacts with status E0001 when the same Contact has status E0003 in CRM! So the status is wrong!
When I look into the cube for the Customer Contact who should be closed (E0003), I see 3 lines of records in it.
- 1 line with status E0001 and activity 1
- 1 line with status E0001 and activity -1
- 1 line with status E0003 and activity 3.
The Customer Contacts who are closed in the query, and CRM only have 1 line in the Cube!
I checked the ODS. That one has only 1 line with the right status; E0003.
When I look in the query for contacts with status E0001 and E0003 it shows the Customer Contacts double.;
- 1 line with status E0001 and activity unknown
- 1 line the status E0003 and activity 1
How come the cube shows both statusses? When the Customer Contact must have status E0003 only, as it is in the ODS and CRM.
How can I fix this? Shouldn't the Cube delete the records with status E0001 when the ODS only has a records with status E0003?