Question Re: Aggregation in Cube in Technology Q&A

Aggregation in Cube

Former Member — Sat, 08 Mar 2014 13:47:25 GMT

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.

Re: Aggregation in Cube

Former Member — Sat, 08 Mar 2014 17:24:44 GMT

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.

Re: Aggregation in Cube

Former Member — Tue, 11 Mar 2014 05:10:03 GMT

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,

Re: Aggregation in Cube

Former Member — Tue, 11 Mar 2014 05:53:44 GMT

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.

Re: Aggregation in Cube

former_member182998 — Tue, 11 Mar 2014 10:32:32 GMT

Hello Madhusudan,

Asn Suggested by Ramesh, the data in the Infocube F table is shown or displayed request wise. This is why you get that behaviour . I think you aggregated the records as per the key fields. that is basically aggregation.. a concept that is used for optimizing the Query performance.

Please refer below link, which is on Aggregates., and refresh your concept on that

Performance Tuning for Queries with Aggregates:... | SCN

It has similar type of Example as you mention above I( Country Vs Sales)

Hope this Helps...!

Regards

Re: Aggregation in Cube

Former Member — Tue, 11 Mar 2014 11:33:57 GMT

Hi Both,

Please i think u are moving away from the topic what i really need is about Aggregation that happens in CUBE and not regarding Aggregates .

As in DSO data gets overwritten and like wise in Cube it gets aggregated i wanted information about this aggreation that occurs in CUBE rather than the aggregates CONCEPT.

thanks,

Madhu

Re: Aggregation in Cube

Former Member — Tue, 11 Mar 2014 12:05:30 GMT

hi madhusundhan,

we have only concept called aggreates in infocube, there is nothing like aggregation happens infocube, when data loading. it only summerizes the data. There is lot difference between summerization and aggreagation.

Thanks,

Ramesh.

Re: Aggregation in Cube

Former Member — Tue, 11 Mar 2014 12:14:55 GMT

Hi Ramesh,

yea i wanted the same to know about the summerization of data in CUBE when where and how it happens

Thanks,

Madhu

Re: Aggregation in Cube

Former Member — Tue, 11 Mar 2014 12:25:17 GMT

hello madhusudhan,

1) if you load data into infocube the data will be stored in F fact table. Since F fact table is partitioned based on Request ID , the data is summerized automatically during data load .

2) Next if the data is loaded again the data will not be summrized in the cube, But when u report on the cube, the data in the report is summerized ( This is done by OLAP processor).

3) if you wanna summerize all the data , you need to compress the cube (compression transfers the data from F fact table to E fact table (on E fact Table you can manually partion using 0calmonth, 0fiscvar) ) and it deletes all the request's and summerizes the data and saves it E fact table.

4) If you report on cube after compression, the data will be summerized data due to compression (Here no olap process is used).

5) Hence Burden on OLAP processor is reduced using compression (In ordr to summerize the data)

Thanks,

Ramesh.

Re: Aggregation in Cube

Former Member — Wed, 12 Mar 2014 05:15:38 GMT

Hi Ramesh,

Thank u so much for ur explaination.

Madhu

Re: Aggregation in Cube

Former Member — Thu, 13 Mar 2014 07:47:38 GMT

hi madhisudhan,

it the above answers you find correct, Click which one you think is the correct answer, so that it can be helpfull to others .

thanks,

ramesh.