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_C11P") */ SET E."KF1" = E."CL
