Cube Data issue ... in BEx reporting

[Former Member]

i am loading data in a PsA -> ODS - > Cube Delta reacord are filling daily .

1st Day - > one record is moved to cube

2nd Day same record is updated in R/3 ( There is a change in status field of that record )

records loaded to BI , in ODS it overwrite the exitsing previous record & update with New one -- Correct

Cube ; it makes 3 records 20 ( old ) , -20 ( New ) , +50 ( New update value ) . - Correct .

BUt when i am Displaying the data in BEx query - it shows me count = 3 record 20,-20,50 ( Incorrect )

From my view it has to show only Update entry ( 50 ) i.e count = 1 ( No of records) .

Pl suggest .

Thanks Regards

Sanjeev Kumar

Edited by: skarya2003 on May 24, 2011 11:08 PM

[Former Member · Accepted solution]

Hi -

It seems like your Count KF is mapped or configured incorrectly. Did you have Count KF in DSO?

If you do not have any Count KF in Cube then just compress cube data at Manage screen then everything will be fine. Thanks!

Regards,

Anesh B

[Former Member]

Hi,

Do you want to see the Row count in the Cube as it is in the DSO?

Thanks,

Shubham

[Former Member]

Create Key figure - Row Count in the Cube.

Link it with the Recordmode in the DSO.

Put the below in the transformation as formula for ROW COUNT -

IF( RECORDMODE = 'D' OR RECORDMODE = 'R' OR RECORDMODE = 'X', 1-, 1 )

Load the full data from the DSO to the Cube and then setup the Delta from the Change Log table.

Then you will be use the "Row Count" key figure in the Cube same as you do in the DSO in all your reports.

Thanks,

Shubham

Edited by: Shubham Simlote on May 30, 2011 12:55 PM

[Former Member]

Hi,

It is false if there 3 records (20,-20,50)in your infocube.

DTP is set with Delta between DSO and Infocube.

If a record in souce system was changed with 50, then 2 entries will be written in Change log of DSO,

one is 20- with recordmode 'X'--NEW IMAGE

the other ist 50 with recoredmode '' --AFTER IMAGE.

This two entries will be transfered to Infocube, but only one entry will be written in infocube.

One is 20 (old)

the other is 30(new)

Then you cann collapse them..

[Former Member]

KF ( counter ) in Cube is set a constant ( 1) . which is wrong .

As Cube is spilting 1 update record to 2 ( New & After image ) record which correct . As counter is set to contant = 1 then it giving each record to 1 irrespective of New / After images .

So in report 20 , 20 , 50 counted as three record .

Correct Solution : KF in infocube must be Mapped with ODS to get or images . So i mapped counter KF from ODS Counter KF .

problem is solved

Records become 20 --> 1

20 ---> -1

50----> 1

Overall total in report = 1 . which is correct .

[Former Member]

KF ( counter ) in Cube is set a constant ( 1) . which is wrong .

As Cube is spilting 1 update record to 2 ( New & After image ) record which correct . As counter is set to contant = 1 then it giving each record to 1 irrespective of New / After images .

So in report 20 , 20 , 50 counted as three record .

Correct Solution : KF in infocube must be Mapped with ODS to get or images . So i mapped counter KF from ODS Counter KF .

problem is solved

Records become 20 --> 1

20 ---> -1

50----> 1

Overall total in report = 1 . which is correct .

[former_member183777]

Typically if you write a BEx query the addition of all three records will give you the correct result. If you have a counter KF then it should be set to -1. If you need to count some other stuff, then you can build KF using exception aggregation.

Akso when you do a rollup / compression of requests in the cube, you can select the option to eliminate 0 records. At that time the individual reords are collapsed and all records where all KFs add up to 0 will be eliminated from the cube.