Partition vs compression
Please some one explain me what is the relation b/w partition and compression in the cube?
John Skrabak replied
You've gotten some good responses, so I won't repeat them, but I would just like to clarify the compression process a bit. It is NOT actually "compressing" the data in the same sense as when we use database compression or file zipping and replace repeating values with tokens.
In BW, the compression process moves the data from the F fact table to the E fact table. Along the way, it removes the Request ID from fact rows (actually, I believe it just sets the value to 0). This allows the data in the cube ( to varying degrees ) to be summarized more, reducing the number of fact records that end up in the E fact table. So the compressed E fact table can be smaller due to the reduced number of rows in it, not because the data has acutally been "compressed".
e.g. Let's say you load store sales data to a cube every day of the month and the time grain of this cube is Calendar Month. By the end of the month, you would have 30 Requests in the F fact table - each Request representing a day's sales. So each store has 30 rows of data for that month. Now if we compress the cube and remove the 30 different Request IDs, the compression process will summarize those 30 row to just 1 row of sales data for each store (remember the time grain is calendar month), resulting in a 30 to 1 reduction in the data volume after compression ( assuming each store has sales each day of the month). It's pretty easy to see that this reduction in the number of rows that a query must read should result in faster query execution.
So the degree of compression is dependent on the granularity of your cube AND the data. SOme cubes, due to the data model, can yield little to no reduxtion in the number of rows (e.g. Material inventory cube) Also - some SAP doc will also refer to Compression as Condensing.
The Compression process can optionally perform Zero Elimination, which can further reduce the number of rows in the compressed fact table (again depending on teh data). Rows in the F fact table where all the KF values are zero are eliminated, as are any rows where the characteristic values match and all the KFs net to 0 once the rows are summarized. Zero elimination even goes to the effort to eliminate previously compressed rows in the E fact table that had a KF with a non-zero values, but after being updated with newer data, now have all KF = 0.
Hope this helps.