Deciding which characteristic to use as Line Item Dimension

[former_member220628]

Hi all,

I 've spent some time reading about line item dimensions but I cannot decide which of the following three characteristics should be chosen as LID in my scenario:

A Document Number  can have different Revision Numbers and different Document Parts and for the combination of these three, many other characteristics exist.

Currently, I've put the Doc No in one LID and the Rev No and Doc Part in one dimension along with their other characteristics but I have not seen any improvements in the run-time of my queries.

Considering that the deepest level of detail in my scenario is not Doc No but the combination of Doc No ,Doc Ver and Doc Part, what do you suggest?

Thanks

[RamanKorrapati · Accepted solution]

Before creating LID, Have you checked info cube dimensions % details thru Report - SAP_INFOCUBE_DESIGNS?

if dimension tables occupies more than 20%  of the fact  table theny only we need to use LID.

if not then in such cases LID creations won't increase report performance.

Have you debugged your query thru RSRT?

please debug and find the performance impact area.

General points which we apply to increase report performance

1. Creating aggregates

2. Compressing the cube data

3. Generate indexes.

4. if we have huge data then better to use partitions on calyear/calmoth based.

5. SPO if we are on bw 7.3 above

[former_member220628 · Accepted solution]

Thanks Raman.

Yes size of the table containg Doc Ver and Doc Part is already above 20% and I 'm not sure if it's OK to have these two in separate dimensions as well and make the as Line Item.

As for the tips, I have around 517,000 records in my cube and indexes are OK. But considering that the number of records is not huge, do I still need to use compression,partitions etc?

According to RSRT statistics, the huge percentage of execution time is spent on OLAP processing because I have some key figures (like averages) in my query and it is not possible to create these key figures in the cube in order to decrease the load on the front end so I have to put up with the high OLAP processing time.

And having read many articles on performance , I came across the LID thing and wanted to give it a try.

[former_member186399 · Accepted solution]

Hello,

If your RSRT stat is showing a lower DB time and higher OLAP time, there might be a very marginal improvement or no improvement if you do  DB level optimisation (by partition, compression or even LID) .

If any of your calculations can be moved at the cube level you can get an improvement in the report timing. If the calculation has to be on run time , then using an APD to store the calculated idea will be another option

Regards

Gajesh

[former_member220628 · Accepted solution]

Thank you.

I do not know much about APD but will spend some time to read about it.

As for the DB time, yes it is very low fortunately.

Just one more thing, if the DB time was high, would making Doc Ver and Part as LIDs help?

I mean why did you suggest that it is not necessary to make them LIDs as well? The table size of the dimension containing these two is 50% of the fact table so the variety of data must be high and if there were DB time issues, having two more LIDs would help. Right?

[RamanKorrapati · Accepted solution]

Hi Parv,

Data volume is not much high.

But LID(Doc num)/Partition concepts will improve some what better performance.

Compression also surely improve the performance.

if there are any key figs which give 0 result then better to suppress them at bex level while displaying.

please check useful OSS Notes

1879725  -  Query Memory Consumption

1681396    - Query Performance

Have any hierarchies at bex level?

is this is old report or new one?

if its old then haven't faced performance problem in the past?

Have checked data performance(for same report) at dev system?

Thanks

[former_member186399 · Accepted solution]

Hello,

See if both of the characteristics are put in one dimension there combination may be numerous and therefore resulting in 50% entries. Now if you keep them separately I don't think individually they will have that much of variety. So by keeping them different dimension will solve your issue.

Also document version and part number are repetitive in nature ( for eg 000, 001 etc ) and therefore making them LID will not provide you any benefit.

Regards

Gajesh

[former_member220628 · Accepted solution]

Thanks Raman.

Making Doc No as LID did not improve the performance much (maybe because the DB time was already low) and according to Gajesh, since Dov Ver and Part are highly repetitive , making them as LIDs will not help.

And as for the notes and compression, I will spend some time on them. Thanks.

And about the report, there are no hierarchies; it's not new and the performance has always been unacceptable and the number of records in Dev is only 8 thousand so the report runs faster there.

Anyway ,thank you both

[former_member186399]

Hello Parvaneh,

Making doc no as line item dimension is certainly going to help, if for every record you have a specific doc no.

The simple way to check which character to be made as a line item dimension is to load the data and then check in SAP_INFOCUBE_DESIGNS program. it will show the ratio of the dimension table size to the fact table size. Anything above 10 % qualifies to be looked into .As a standard, the ratio of rows in Dimension table to rows in Fact table should not be more than 10%.If it is more than 10% then you will see the line highlighted in red colour in this report and you should consider redesigning that particular dimension.

Regards

Gajesh

[former_member220628]

Thanks Gajesh

After making Doc No as LID, the dimension table size decreased which is what I expected.

Shall I put Dov Ver and Doc Part in two different dimensions  and make them Line Item as well? Is it OK to have three LIDs in a cube?

(The current size of the dimension table containing these two is above 20%)

[former_member186399]

Dear Parvaneh,

I think you need not make them as a LID, rather make them into different dimension. I don't think you are going to get huge variety of data in the version and part number.

Please check and revert back

Regards

Gajesh