Performance tuning

[Former Member]

Hi Friends,

Can anyone explain how Line item Dimension work with a real example and when do we create this.

I am confused between Aggregates and Partitioning.Can i get the difference in which scenario Partitioning and Aggregates are created.

Please clear these.

Thanks,

Sahasra

[Former Member · Accepted solution]

line item dimension http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/005f3197-d3da-2e10-1a94-a5c62342f...

aggregate stores summarized data before the query runs so when query runs it reads the summarized data and and the result is quicker

[Former Member]

Hi,

just to add to above post , aggregates are created physically which means they occupy database space. so same data is stored twice ( once in cube and another in aggregates ( at aggregated level though ) . while in partioning the data remains in cube only.

Regards,

Neeraj.

[ravi_chandra3]

Hi

Line Item Dimension is nothing but suppose if u have a characteristic that generates large no. of entries(means that generate large no. of values) then in that case ur dimension table grows up drastically leading to the size of dimension table greater than 20% of the fact table which is against the Thumb rule. So in this case what we do is make that characteristic which produces large no. of entries as a line item dimension so what happens here is that dimension table won't get generated instead the SID takes the role of dimension table and SID table is directly connected to the fact table.

Eg for line item dimension is 0Mat_no. etc..,

Aggregates : Aggregates are nothing but the baby infocube and these Aggregates are created on the top of the parent infocube. We can create any no. of aggregates on the top of the infocube.

Aggregates are created based on the characteristic, navigational attribute etc..

Partition : Partition are nothing but the partitioning(breaking ) the infocube based on 0CALMONTH and 0CALFISCPER. We can partition the infocube only on these time characteristic.

Hope u got it,

Thanx & Regards,

RaviChandra

[Former Member]

Thank you.

Can you explain me why we create Partitions?

Regards,

Sahasra

[raghu_ram]

Hi Sahasra,

We use partitioning to split the total dataset for an InfoProvider into several, smaller, physically independent and redundancy-free units.

This separation improves system performance when you analyze data delete data from the InfoProvider.

Regards,

Raghu

[anindya_bose]

Hi Friend

You can get very good information about partition and its advantage in SAP certification material BW 360 .

Also have a look at help.sap.com links

http://help.sap.com/saphelp_nw04/helpdata/en/33/dc2038aa3bcd23e10000009b38f8cf/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/0a/cd6e3a30aac013e10000000a114084/content.htm

Regards

Anindya

[Former Member]

Hi,

We generally partition the cube for the better query execution performance and data loading performance. You can get the step wise process of partitioning a cube in following link :-

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/404544e7-83c9-2e10-7b80-a24d5099ce3f

Navesh

[Former Member]

Thanks Aindhya.

I have one more question in which scenario do we create Indexes and how do they work.

Help is appreciated.Assigned points

Regards,

Sahasra

[anindya_bose]

Hi Sahasra

By creating index basically you store the memory location of a particular record.

Let's say you have company code in one of the DSO. Comp Code ranges from 1000 - 2000.

Now if you create Index on Comp Code , it creates a different table with the memory location for each comp codes.

Comp Code | * Memory Location ( Row number)*

1000 | 9,112,34,789 ,45678

2000 | 86,112,36,67008

3000 | 3056,798,100,234

and so on.

Now when you run a query with filter value Comp Code 1000 , Database system already knows which are the row number where you have comp code 1000. So, instead of going to all the rows one by one , it will only go row 9,112,34,789 ,45678

and fetch the records. This way database read time is much faster than sequential search of the whole table.

In BI dimension/fact tables are also indexed automatically . Index plays critical role in optimizing queries on DSO. If you can build right index for the query , query response time ( and variable pop up) can be much faster. For BI in general once you build the index you do not need to do anything else. System is intelligent enough to use those index wherever required.

Look at the How to guide. This gives some information about how Index helps. It explains how ABAP program can use index to make data retrieval faster but in case of BI you just need to create the index. Rest BI system can handle.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/50509a26-8186-2d10-bca9-dc1284e24...

Regards

Anindya

[Former Member]

Thanks Anindya.

Regards,

Sahasra

[Former Member]

Thanks Anindya.

I do not have BW 360 ..Request you finally give me one real time scenario when we create Partitioning.

Regards,

Sahasra

[anindya_bose]

Mostly in case of reporting on DSO index helps in faster data retrieval.

If you have a DSO like this

Key Fields

Comp Code

Material

Plant

CalMonth

Product Line

and Some Data Fields...

Now if your report needs to display some Amount /Quantity as per Product Line and Comp Code, then creating Index on Product Line and Comp Code will make your query much faster. But you can only realize the difference of having and not having index when you have large data set in the InfoProcider ( like 5 m data in one DSO)... For smaller data set improvement of performance is not that much.,

There are sites where from you can download certification books ( just an information)

Regards

Anindya

[anindya_bose]

Hi Sahasra

if you are reporting for a Telecom Operator then "Customer Number" would have lots of values.

In that case if you use only Customer Number in Customer dimension and mark that dimension as Line Item dimension your loading and reporting performance would be much better.

Because instead of dimension id , SIDs will be used as Dimension Ids. So you do not need to generate DIM table. Characteristics can be directly connected to SID.

Also check this threads...

For aggregates check this

In one line : aggregates are useful when you have detailed data in cube ( daily) but report is on summarized ( monthly )data.

by creating aggregate you are summarizing data before the query runs, when query just read the summarized data and show the result fast.

Regards

Anindya

Edited by: Anindya Bose on Jan 18, 2012 9:14 AM

[Former Member]

Thanks Anindya.very good explanation

I have assigned points..

Can you please give some idea in which scenario we create Partitioning and the limitations of partitioning.

Regards,

Sahasra