Disadvantages of Line-Item Dimensions

[Former Member]

Hello all,

I know about the concept of line-item dimensions. I re-designed a cube and tested the performance. There was just one InfoObject, which was worth it to put into a line-item dimension (dimension had 10% lines of facttable). Nevertheless imho I defined 5 more line-items, because it must be faster (and for testing purposes of course). I copied the query with rszc so I had the same query on both infoproviders. Statistic is up-to-date for both infoproviders. No Aggregates are defined.

Both cubes have exactly the same amount of data in facttable (24 Million; using se38 --> SAP_INFOCUBE_DESIGNS). I executed both queries using rsrt (use no cache). Guess what: The Query using the -line-item-cube was 60s slower (320s. vs. 380s.)

How comes? If line-item is applicable or not apllicable in my case: What can be the reason, that line-item downgrade performance?

Regards,

Christian

PS: Example of a new line-item-infoobject: GTIN/EANUPC. The InfoCube/ Dimension contains only 8.000 distinct GTIN/EANUPC. But the SID-Table if this Infoobject GTIN/EANUPC contains more than 100.000 entries. So, I defined GTIN/EANUPC as line-item dimension. Why can this be slower?

[sander_vanwilligen · Accepted solution]

Hi Christian,

Theoretically line item dimensions contribute to a better query performance since it saves one join. However, there are many other factors and perhaps the constellation of InfoProvider, Query and data which gives a performance downgrade in this case.

In my opinion one test is not sufficient to draw this conclusion. You will need more and different test cases and also on the Production system as a representative environment for performance measurement.

Last but not least, SAP incorporates line item dimensions in the in-memory optimized InfoCube on HANA. So it's a clear statement of direction and confidence of SAP in such an approach.

Best regards,

Sander

[Former Member · Accepted solution]

Hi Sander,

thank you very much for your answer. I am really happy, somebody contributes!

I agree with your answer, that one test is not sufficient. In my (special) case, this query is opened in 7 of 10 cases (so "very" often). So, scientifically one test is not sufficient, but in my case it is enough not to line-items. (tested in production System).

Sander van Willigen wrote:

Theoretically line item dimensions contribute to a better query performance since it saves one join. However, there are many other factors and perhaps the constellation of InfoProvider, Query and data which gives a performance downgrade in this case.

Do you have in idea, what exactly to look for? Any test-Scenario you would test?

Best Regards,

Christian

[sander_vanwilligen · Accepted solution]

Hi Christian,

I have been thinking about your question. I don't recommend defining a Line Item Dimension in your example with GTIN/EANUPC. The Dimension Table contains 8,000 distinct values, which is "only" 0.4% of the Fact Table (24 million records). Why should you use in such cases a Line Item Dimension?

The classical example of a Line Item Dimension is Document No. Such a Dimension can potentially grow direction 100% of the Fact Table. A Line Item Dimension can be considered if it becomes more than 10 - 20% of the Fact Table. But still be careful and try first other options to remodel the Dimensions. Otherwise it could work contra-productive and might even lead to performance degradation.

Best regards,

Sander

[RafkeMagic]

Christian Röttgers wrote:

Nevertheless imho I defined 5 more line-items, because it must be faster (and for testing purposes of course). I

Where those other 5 also "degenerated" dimensions (as per this link)? If not, no performance gain should be expected...

[Former Member]

Hi Ralf,

no. They are not "degenerated" (may change in future). As per the link, the only disadvantage is, that a line-item-Dimension can only hold one characteristic.

I agree, that a performance gain can not be expected; but a downgrade in performance is the case.

But why? Any idea on that?

Regards,

Christian

[RafkeMagic]

I don't have a straight answer, but it's probably linked to the fact that your dimension table contained 8000 entries only, and your SID table contains 100000.

To be sure you can "check" the query via RSRT (as per OSS1681396).