Duplicate Entries in Fact Table for same combination of DIMID's

[Former Member]

Hi Experts,

I am facing a very very peculiar issue.

When I am trying to see the contents of the fact table for a particular cube, I can see multiple entries in it for the same combination of dimids with different values for the key figures.

I think it should not be the case as I am under the impression there should be a single entry only for each unique combination of dimids in the fact table.

Upon testing, all the RSRV tests are coming as green.

The database is Oracle and we are on BI 7.0.

Could you please help me out is there are settings involved anywhere or any parameters need to be changed. The cube is 0CCA_C11.

Thanks in advance.

[Former Member · Accepted solution]

Hi, Comfortably Numb!

It seems to me, that you don't have primary index for your cube.

Check the following:

SE11 -> Infocube table -> menu Utilities(M) -> Database object -> Database Utility -> Indexes -> Double click on Primary Index.

The Primary Index must be active and not be prohibited.

best regards

Alexander Kuzmich

[Former Member · Accepted solution]

Hi Alexander,

Thanks for your reply.

I went to the indexes screen. The index '0' for primary key is existing and is active. And it is selected to unique.

Still then multiple records in the fact table for same key..

Thanks.

[Former Member]

"BW does not need to enforce the unique key constraint over the facttable's primary key but can cope with duplicates"

Note 217397 - Indexing Scheme of BW facttables under Oracle

[Former Member]

Hi Alexander,

Thanks once again. I am going through the note and also few other notes.

I will update here with the outputs.

Thanks.

[Former Member]

In cube this is not possible to having DIMID duplication due to Primary field in cube. Can you please elaborate more with your error which helps a lot to reach particular solution.

Edited by: Mahesh Jethwani on Dec 9, 2008 2:37 PM

[Former Member]

Hi,

this is physically not possible as the dimids are the primary key of the fact table. You cannot have a duplicate record for the primary key.

Siggi

[Former Member]

Are you looking at the E and the F table independantly?

And have you included the package DIM in the check of duplicates?

[Former Member]

Hi Siggi/Simon/Mahesh,

Thanks for your kind replies.

I am checking for the F table only. And yes I have included all the dimensions.

Yes I was under the same impression as you have pointed out.

But as I have mentioned I have multiple entries for the same combination.

The following combination gives me back three records in the F table.

KEY 0CCA C11P - 169

KEY 0CCA C11T - 16

KEY 0CCA C11U - 5

KEY 0CCA C111 - 242

KEY 0CCA C112 - 699

KEY 0CCA C113 - 9

KEY 0CCA C114 - 2835

KEY 0CCA C115 - 12

KEY 0CCA C116 - 5

KEY 0CCA C117 - 0

KEY 0CCA C118 - 242

KEY 0CCA C119 - 0

Thanks.

[Former Member]

Hi,

do you have additional line item dimensions which also belong to the key? Check that out.

Siggi

[Former Member]

Hi Siggi,

Thanks once again.

I tested it. No line item dimension present also.

Infact searching I found a thread almost similar.. But there is no solution mentioned either !!

/message/6676161#6676161 [original link is broken]

Thanks.

[Former Member]

Hi,

well I cannot find any relation between that thread and this one, but anyway. Did you think about raising a note at http://www.service.sap.com ?

regards

Siggi

[Former Member]

Hi Siggi,

I think I pasted a wrong url .. sorry about it .. The correct one being ..

Yes I think I should raise it to SAP.

Thanks a lot for answering.

Thanks.

[Former Member]

Normally I would expect 3 records for each CCA11 entry but with a different DIMID for the currency type field (ie a 10, 20 and 00 currency type record)

Can you just check in listcube if you have three records and they correspond to the three you are seeing?

[Former Member]

Thanks Simon.

But actually I am checking for a single currency type only. I understand that it pulls through three different currency types. But still then I am stuck.

Thanks.