Cube compression WITH zero elimination option

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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.

[Former Member]

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

[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).

[Former Member]

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

[former_member228339]

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.

[Former Member]

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