Aggregation in Cube

[Former Member]

HI,'

Am loading flat file to info cube with delta update say

Customer     product     Category     Qty      Price

c001               p01             Cat1     10          1000

c001               p02               Cat2     20          2000

and perform a full load the the same replicates in cube and then i edit the flat file and run the info package and DTP with delta say am loading

C001    p01   cat1 20 2000

so now the records should be aggregated right? tell me if am wrong am not getting the concept right make it clear to me

i expected the data in cube would be

c001               p01             Cat1     30         3000

c001               p02               Cat2     20          2000

But the result was

c001               p01             Cat1     10          1000

c001               p02             Cat2     20          2000

C001              p01             cat1      20           2000

am really confused so make it clear when does aggregation happen but i found another thing that if i have data like

c001               p01             Cat1     10          1000

c001               p02             Cat2     20          2000

C001              p01             cat1      20           2000

initially in the flat file and if am loading the above 3 records for the first time into cube then the records are getting aggregated and result appears as

c001               p01             Cat1     30         3000

c001               p02               Cat2     20          2000

am really not getting the concept please help me out

Thanks,

Madhu Nair.

[Former Member]

hi madhusudhan,

                             if you load data into infocube data is stored in F Fact Table. F fact table is default partitioned by Request id. The Request are different so it is not summerized and if you load in same flat file the data is summerized.

                             If you compress the cube, The data will be Transferred from F fact table to E fact table and all the request id's are deleted and data is summerized and you will get the expected summerized data.

Thanks,

Ramesh.

[Former Member]

Hi Ramesh,

If that is the case as u have mention in real time i need may not send the a record with same key combination but different key figure values through flat file in on same day that is in same PSA request.

if so then what is the use of aggregation concept in Cube . I would like to know when actually aggregation comes into picture is it only when i compress the data and is it in such a way the we always compress data before reporting.

see if i have 3 records with same key combination with different request in psa and if i do a full load to cube then say if there are two requests hence 6 records and when i do full load to cube 6 records appear in cube even its a load with single request in the DTP with full update.

so my dbt is weather as u said data id partioned according to the request is this refer to the request in psa or the delta request my consideration is it is partioning based on PSA req.

Please clear my dbts

Thanks,

[Former Member]

hi madhusudhan,

1) First thing is, If you have not compressed the cube, IN the Report OLAP processor summerizes the data and shows you the summarized data in the Report, If you compress the infocube, The data is summerized at infocube level (which removes burden on OLAP processor) so that you can get the summerized data in the Report .

2) Aggregates is different concept, Aggregates makes use of data to be accesed from infocube easily from  Report. If you have created aggregates for an InfoCube and entered data for them, the OLAP processor automatically accesses these aggregates.

Aggregates (SAP Library - Performance Optimization)

3) I have Explained you previously in the context of your above Example. (So that you can understand       about compression) .

4) F fact table in Infocube is  partitioned based on Request id .

5)Compression and Aggregation is Completely Different concept.

Compression data i stransferred from F fact Table to E fact Table and Request id's are deleted.

Aggregates is nothing but  it consolidates and stores a subset of InfoCube data into a database. This subset of data is redundant and persistent. When we execute a query on an InfoCube with appropriate aggregates, the query reads the summarized data directly from the database thus eliminating the need to perform any processing related to summarization  during query runtime.

if you find any difficulty you can ask me ...

Thanks & Regards,

Ramesh.