Wrong records in cube after Delta

[Former Member]

Hi experts,

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?

Thanks!

[Former Member · Accepted solution]

Hi Erica,

In the ODS, your status is being over written, hence you are getting the latest status only.

But in the cube which gets loaded from the ODS, you will have data for all the status as data gets loaded via delta load.

Say Initially for record 1 you have status E0001.

Your ODS will have E001.

And the cube will have E001.

Now say the status is changed to E002.

Your ODS will have E002.

But the cube will have two records with two different request ids for status E001 and E002.

In these cases you can check if you can use the contact status as navigation attribute or also you can investigate if the cube should have the latest data only, in that case if the data should be loaded from the ods to cube via full load deleting the previous requests.

Thanks.

[Former Member]

Hi Erica,

You did the supress results on Report which is not the global change your users are looking for.

Please do compression of your aggregation with 0 elimination.

Regards,

Mahesh

[ram_pandey]

Hi Erica,

InfoCube records are additive. Records are not deleted from a cube, instead the value of all the key figures for a deleted record is set to 0. For example when your Status was 0001, the 'activity' value was 1. When the new status value 0003 came into the system, the value of activity for Status 0001 was negated by sending activity value as -1. Now when this record is seen in a BEx report the value of activity would be aggregated and you get the result as -1 + 1 = 0. Like I said, an InfoCube contains aggregated data. If you look carefully, all the key figure values for staus 0001 would have negative values for key figures for one record and positive value for the other so that the aggregated value of all the key figures would be 0.

Whereas in ODS you can delete or overwrite the records, therefore you only see the latest record in the ODS.

[Former Member]

I found the solution. I supressed the zero's in the query options. So now, only the status E0001 is shown.

Thanks for the help.

Edited by: Erica de Boer on Nov 20, 2008 1:02 PM

[Former Member]

Your idea:

( The thing is; the user only wants to see the records with status E0001 when that is the only status in the Cube. So if a records has status E0003 too, the user doesn't want to see that. )

I think your method is not right,because if you have E0001 in the cube,and then a E0003 be deltaed into the cube,you also can get the E0001 record,you'll be wrong.

If you make the status as a navigation attribute ,it will be a new status all the time.

[Former Member]

No, if the status changes from E0001 to E0003, the activity of status E0001 goes from 1 to -1. So, that's a 0.

If I supress the activity on 0, you only see the records with activity 1, which is the correct record.

[former_member184494]

Erica,

Compress your cube with zero elimination and these records should ideally go away..

Arun