Insights into Cube compression

What is compression :
For this we need to go into specifics of how data gets updated into the cube.
A cube has two fact tables..
The F Fact table or the uncompressed fact table – this table is partitioned based on request ID.
In other words .. the F Fact table has a partition for each data load. This is the request ID. The partitioning index for the cube is the 900 index or the P Index.

Then there is the E Fact Table or the compressed fact table. The E Fact Table may or may not be partitioned based on the
cube design. If the cube is partitioned then the E Fact table is partitioned.

*When compression is done </p><p>The selected requests in the F Table are summarized and then inserted into the <br />E Fact Table and the summarized into one Request or in other words – the requets are merged together by summarizing the same.<br /><br /><u>Advantages of compression :</u><br />Query access is faster<br />Cube size comes down – this helps in tasks like rebuild of aggregates etc.<br />Indexes are better maintained<br />Data Load is more efficient because the F Table has fewer requests ane lesser data.<br /><br /><u>Disadvantages of compression :</u><br />Request based deletion is not possible on compression. Only selective deletion is possible.<br /><br /><br />Let us look ate the basic compression job – this job was got from the SM37 monitor…<br />Here references to acxtual cubes have been removed and also Key Figures and characters are highlighted.<br /><br /><u>Compression starts here – parameters for compression :
Zero Elimination</u><br /><br /><u>What is zero elimination :</u><br />If there is some record which has got nullified in the cube it can be currently listed as :<br />Customer|Product|Sales<br />ABC | sprocket | 100<br />ABC | sprocket | -100<br /><br />The DSO will be reading zero for the same record..</p><p>If you have zero suppression in the query then in all likelihood this record is not reported  in such a scenario this record can be removed from the cube to reduce the number of records.<br />Zero elimination basically is for records in the fact table that do not have any facts – in other words all the key figures for the record in the fact table are zero.</p><p><br /><br /><u>Job Sequence*This also gives the clock symbol for the requests in the manage tab where the request is shown as being compressed / summarized.

Leave RSM1_CHECK_DM_GOT_REQUEST in row 70; Req_State ”                             
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_INSTANCE_FOR_RNR    469143 LINE 43                 
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_TSTATE_FOR_RNR 7 LINE 243                          
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_INSTANCE_FOR_RNR    468424 LINE 43                 
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_TSTATE_FOR_RNR 7 LINE 243                          
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_INSTANCE_FOR_RNR    467673 LINE 43                 
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_TSTATE_FOR_RNR 7 LINE 243                          
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_INSTANCE_FOR_RNR    466617 LINE 43                 
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_TSTATE_FOR_RNR 7 LINE 243                          
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_INSTANCE_FOR_RNR    465550 LINE 43                 
RSS2_DTP_RNR_SUBSEQ_PROC_SET GET_TSTATE_FOR_RNR 7 LINE 243                          

Now the summarization query is fired … kinda long but has a lot of information…!!_{P” ) / INTO “/BIC/E<CUBE>” <br />E USING ( SELECT /+ PARALLEL ( FACT , 3 ) /0 “PDIMID” , “KEY_<CUBE>T” , <br />”KEY_<CUBE>U” , “KEY_<CUBE>1” ,KEY_<CUBE>2 , “KEY_<CUBE>3” , “KEY_<CUBE>4″ , <br />”KEY_<CUBE>5” , “SID_0CALMONTH” ,SUM(KF1″ ) AS “KF1” ,SUM( “KF2” ) AS “KF2” ,<br />SUM( “KF3” ) AS “KF3” ,SUM( “KF4” ) AS “KF4” ,SUM( “KF5” ) <br />AS “/BIC/YKF5” ,SUM( “KF6” ) AS “KF6” ,SUM( “KF7” ) AS “KF7” ,<br />SUM( “KF8” ) AS “KF8” ,SUM( “/BIC/YKF9” ) AS “KF9” ,SUM( “KF10” ) <br />AS “KF10” ,SUM( “KF11” )AS “KF11” ,SUM( “KF12” ) AS “KF12” ,<br />SUM( “KF13” ) AS “KF13” ,SUM( “KF14” ) AS “KF14,SUM( KF15″ ) AS <br />”KF15” ,SUM(“KF16” ) AS “KF16” ,SUM( “/BIC/YKF17” ) AS “KF17” ,<br />SUM( “KF18” ) AS “KF18” ,SUM( “KF19″ ) AS”KF19” ,SUM( “KF20” ) <br />AS “/BIC/YKF20” ,SUM( “KF21” ) AS “KF21” ,SUM( “KF22” ) AS “KF22″<br />,SUM( “KF23” ) AS “KF23” ,SUM( “KF24” ) AS “KF24” ,SUM( “/BIC/YKF25” ) <br />AS “KF25” ,SUM( “KF26” ) AS “KF26” ,SUM( “KF27” ) AS “/BIC/YKF27” ,<br />SUM( “KF28” ) AS “KF28” ,SUM( “KF29” ) AS “KF29″FROM “/BIC/F<CUBE>” <br />FACT WHERE “KEY_<CUBE>P=74 AND KEY_<CUBE>T” IN (4751 ,4755 ,4756 ,4757 ,4759 ,4777 ,<br /><< Request numbers Selected >>4778 ,4779 ,4784 ,4790 ,47938 ,4804 ,4806 ,4807 ,4817 ,4818 ,4828 ,4831 ,4835 ,4836 ,<br />4837 ,4839 ,4845 ,4850 ,4851 ,48853 ,4854 ,4855 ,4856 ,4857 ,4858 ,4859 ,48601 ,4862 ,<br />4863 ,4864 ,4865 ,4866 ,4867 ,4868 ,4874 ,4876 ,4881 ,4882 ,4886 ,4887 ,4888 ,48890 ,<br />4891 ,4892 ,4893 ,4894 ,4895 ,4896 ,48978 ,4899 ,4900 ,4901 ,4902 ,4903 ,4904 ,4905 ,<br />,4907 ,4908 ,4937 ,4938 ,4939 ,4940 ,4941 ,49943 ,4944 ,4945 ,4946 ,4947) GROUP BY KEY_<CUBE>T” , “KEY_<CUBE>U” , “KEY_<CUBE>1” , “KEY_<CUBE>2” , “KEY_<CUBE>3” ,  “KEY_<CUBE>4”, “KEY_<CUBE>5” , “SID_0CALMONTH” HAVING (SUM(“KF1”) <> 0 ) OR (SUM (“KF2” ) <> 0 ) OR (SUM (“KF3”) <> 0 ) OR (SUM(“KF4”) <> 0 ) OR (SUM (“KF5”)> 0 ) OR (SUM (“KF6”) <> 0 ) OR (SUM (=KF7) <> 0 ) OR (SUM (“KF8”)> 0 ) OR (SUM (“KF9”) <> 0 ) OR (SUM (“KF10”) <> 0 ) OR (SUM (“KF11”)> 0 ) OR (SUM (“KF12”) <> 0 ) OR (SUM (KF13) <> 0 ) OR (SUM (“KF14”)<br /><> 0 ) OR (SUM (“KF15”) <> 0 ) OR (SUM (“KF16”) <> 0 ) OR (SUM (“KF17”) <<br />> 0 ) OR (SUM (“KF18”) <> 0 ) OR (SUM (“/BIC/YKF19”) <> 0 ) OR (SUM (“KF20”) <<br />> 0 ) OR (SUM (“KF21”) <> 0 ) OR (SUM (“KF22”) <> 0 ) OR (SUM (“KF23”) <<br />> 0 ) OR (SUM (“KF24”) <> 0 ) OR (SUM (“KF25”) <> 0 ) OR (SUM (“KF26”) <><br />0 ) OR (SUM (“KF27”) <> 0 ) OR (SUM (“/BIC/YKF28”) <> 0 ) OR (SUM (“KF29″) <><br />0 ) ) F ON ( E.”KEY_<CUBE>P” = “PDIMID” AND EKEY_<CUBE>T” = F.”KEY_<CUBE>T” AND   E.”KEY_<CUBE>U” = F.”KEY_<CUBE>U” AND   E.”KEY_<CUBE>1″ = F.”KEY_<CUBE>1″ AND   E.”KEY_<CUBE>2″ = F.”KEY_<CUBE>2″ AND   E.”KEY_<CUBE>3″= F.”KEY_<CUBE>3″ AND   E.”KEY_<CUBE>4″ = F.<br />KEY_<CUBE>4 AND   E.”KEY_<CUBE>5″ = F.”KEY_<CUBE>5″ AND   E.”SID_0CALMONTH” = F.”SID_0CALMONTH” ) WHEN NOT MATCHED THEN INSERT ( E.”KEBL_C11P” , E.”KEY_<CUBE>T” , E.”KEY_<CUBE>U” E.”KEY_<CUBE>1″ , E.”KEY_<CUBE>2″ , E.”KEY_<CUBE>3″ , E.”KEY_<CUBE>4″ , E.”KEY_<CUBE>5″ , E.”SID_0CALMONTH” , E.”KF1″ , E.”KF2″ , E.”KF3″ , E.”KF4″ , E.”KF5″ , .”KF6″ ,E.”KF7″ , E.”KF8″ , E.”KF9″ , E.”KF10″ , E.”KF11″ ,.”KF12″ , E.”KF13″ , E.”/BIC/YKF14″ , E.”KF15″ , E.”KF16″<br />, E.”KF17″ , E.”KF18″ , E.”/BIC/YKF19″ , E.”KF20″ , E.”KF21, E.KF22″ , E.”KF23″ , E.”/BIC/YKF24″ , E.”KF25″ , E.”KF26 E.KF27″ , E.”KF28″ , E.”/BIC/YKF29″ ) VALUES ( “PDIMID” , F.”KEY_<CUB” , F.”KEY_<CUBE>U” , F.”KEY_<CUBE>1″ , F.”KEY_<CUBE>2″ , F.”KEY_<CUBE>3″ , F.”KEY_<CUBE>4″ , F.”KEY_<CUBE>5″ , F.”SID_0CALMONTH” F.”KF1″ , F.”KF2″ , F.”/BIC /YKF3″ , F.”KF4″ , F.”KF5″, F.”KF6″ , F.”KF7″ , F.”/BI<br />C/YKF8″ , F.”KF9″ , F.”KF10″ , F.”KF11″ , F.”KF12″ , F.”/B<br />IC/YKF13″ , F.”KF14″ , F.”KF15″ , F.”KF16″ , F.”KF17″ , F.”/B<br />IC/YKF18″ , F.”KF19″ , F.”KF20″ , F.”KF21″ , F.”KF22″ , F.”<br />KF23″ , F.”KF24″ , F.”KF25″ , F.”KF26″ , F.”KF27″ , F.”/B<br />IC/YKF28″ , F.”KF29″ ) WHEN MATCHED<br />THEN UPDATE /+ INDEX(“/BIC/E<CUBE>” “/BIC/EYBL_C11}P”) */ SET E.”KF1″ = E.”CL