Skip to Content
author's profile photo Former Member
Former Member

Cube compression WITH zero elimination option

We have tested turning on the switch to perform "zero elimination" when a cube is compressed. We have tested this with an older cube with lots of data in E table already, and also a new cube with the first compression. In both cases, at the oracle level we still found records where all of the key figures = zero. To us, this option did not seem to work. What are we missing? We are on Oracle 9.2.0.7.0 and BW 3.5 SP 17

Thanks, Peggy

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 02:49 PM

    Thanks for the information out of SAP help guide. And yes, of course, we turned on the checkbox, then submitted the compression job.

    I still have my original issue where the rows where the key figures all equalling zero are not getting deleted.

    Please advise.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Haven't looked at ZERO Elimination in detail in the latest releases to see if there have been changes, but here's my understanding based on the last time I dug into it -

      When you run compression with zero elimination, the process first excluded any individual F fact table rows with all KFs = 0, then if any of the summarized F fact table rows had all KF = 0, that row was excluded ( you could have two facts with amounts that net to 0 in the same request or different requests where all other Dims IDs are equal) and not written to the E fact table. Then if an E fact table row was updated as a result of a new F fact table row being being merged in, the process checked to see if the updated row had all KF values = 0, and if so, deleted that updated row from the E fact table.

      I don't beleive the compression process has ever gone thru and read all existing E fact table rows and deleted ones where all KFs = 0.

      Hope that made sense. We use Oracle, and it is possible that SAP has done some things differently on different DBs. Its also possible, that the fiddling SAP had done over that last few years trying to use Oracle's MERGE functionality at different SP levels comes into play.

      Suggestions -

      I'm assuming that teh E fact table holds a significant percentage of rows have all KFs = 0. If it doesn't, it's not worth pursuing.

      Contact SAP, perhaps they have a standalone pgm that deletes E fact table rows where all KFs = 0. It could be a nice tool to have.

      If they don't have one, consider writing your own pgm that deletes the rows in question. You'll need to keep downstream impacts in mind, e.g. aggregates (would need to be refilled - probably not a big deal), and InfoProviders that receive data from this cube.

      Another option would be to clone the cube, datamart the data to the new cube. Once in the new cube, compress with zero elimination - this should get rid of all your 0 KF rows. Then delete the contents of the original cube and datamart the cloned cube data back to the original cube.

      You might be able to accomplish this same process by datamarting the orig cube's data to itself which might save some hoop jumping. Then you would have to run a selective deletion to get rid of the orig data, or perhaps if the datamarted data went thru the PSA, you could just delete all the orig data from the cube, then load datamarted data from the PSA. Once the new request is loaded, compress with zero elimination.

      Now if you happen to have built all your reporting on the this cube to be from a MultiProvider on teh cube rather than directly form the this cube, you could just create anew cube, export the data to it, then swap the old and new cubes in the MultiProvider. This is one of the benefits of always using a MultiProvider on top of a cube for reporting (an SAP and consultant recommended practice) - you can literally swap underlying cubes with no impact to the user base.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 16, 2008 at 02:58 AM

    Dear

    Compression is nothing but moving the data from F fact table to E fact table by losing its request numbers.

    For cumulative key figures(compression)

    Here we have a option called zero elimination (it is applicable only for cumulative key figures). If you want to avoid the Info Cube containing entries whose key figures are zero values (in reverse posting for example) you can run a zero-elimination at the same time as the compression. In this case, the entries where all key figures are equal to 0 are deleted from the fact table.

    Zero-elimination is permitted only for Info Cubes , where key figures with the aggregation behavior ‘SUM’ appear exclusively. In particular, you are not permitted to run zero-elimination with non-cumulative values.

    Thanks

    Rama

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 16, 2008 at 03:11 AM

    Hi,

    By using the zero elimination the entries where all key figures are = zero are deleted from the fact table. So, if you turn it off, then these entries will not be removed during compression........ When we select "with zero elimination" then it deletes all records which key figure value is zero after compression.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 16, 2008 at 03:12 AM

    Peggy,

    It is not an error with 'zero eliminiation'.

    What you have done is correct but not in the proper sequence.

    The reason it didn't work is:

    The timing was wrong. Check the 'zero elimination indicator' on before running the Compression.

    Checking it on after collapsing the cube will not have any effect.

    Remember: this applies only for cumulatives with 'SUM'.

    Hope this helps

    Assign points if it helps.

    Thanks

    Vishno

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 07:28 PM

    Thanks so much Pizzaman for your detailed and complete answer!! We are on Oracle and I saw in the job log the joining to other temp tables during the delete statement. Now that makes sense to me after your explaination. I'll have to dig into the data a bit more, but the OSS ticket may be the next step. We are on BW 3.5 SP 17. We have been nervous now turning on this option because of all of the Merge bugs in the past. But supposedly on our release (Oracle 10.2.0.2.0) the bugs are fixed.

    Do you use the zero elimination option in your shop?

    Thanks again soo much for all your insight.

    Peg

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      It would be nice if they offered an RSRV option to delete 0 KF rows, sort of like some of the other cube data cleanup checks, eliminate multiple DIM IDs assigned to the same set of characteristic values, unused DIMIDs, etc.

      We have used zero elimination for at least 3 yrs at this point. I've tried to stay on top of the MERGE issue - but it does get tricky at times, as you need to know what BW SP and Oracle patch it's been working and when it could cause a problem (of course you could say the same thing about all of BW). It has worked out that when it has been a problem, SAP has disabled it's use in the SPs we have happened to land on

      As of today (1-31-08), we are running 3.5 SP18 Oracle 10.2.0.2 in Prod for several months and haven't had any issues. We run daily automated recon queries comparing key ODS, R3, and Cube amounts, so any problems should be caught pretty early on. Our Dev and QA systems are at 7.0 SP15 and haven't seen any issues there either, so perhaps this is one issue that can be filed away (but not forgotten).

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.