You don't mention whether you have compressed the aggregates. If you have not, when/if you compress the base cube, the aggregate will be compressed thru the same Request.
Compression has a few possible areas of impact:
<b>Partitions</b> - Each Request in the F fact table is in its own partition. The overhead involved in querying a F fact table increases as the number of Requests in the F fact table increases. When you compress your base cube, the data is moved to the E table table which is either NOT partitioned, or partitioned on FISCPER or CALMONTH, which should mean fewer partitions overall.
<b>Compression</b> - this is hard to predict. The main benefit is reduction in the number of rows that setting the Request ID (P dimension) value to 0 allows:
Let's say you have a transaction with all the same characteristc values (e.g. GL Account + Copst Center) every day of the month. At the end of the month you would have approximately 20 (one per business day) transactions spread across approx 20 requests. Now if the time grain of your InfoCube is monthly, rather than daily, when you compress those 20 Requests, a single (yes, just one) row gets written to the E fact table. That is the big potential gain! A 20 - 1 reduction in the number of rows in the InfoCUbe in this case. Now if your time grain was daily, you would NOT have any reduction in this case. S o you can see, it the benefit of compression will dpend on your model and you data.
The other possible rduction in data that could occur is if you scpecify the <u>Zero Suppression</u> option when you compress. If you have transactions where all the Key Figures are = sero, they are eliminated when they are moved to the E fact table. Also, if you have a row already in the E fact table where the KF = 10, and another row from the F Fact table with identical characteristic values and KF = -10 is compressed into the E fact table, the new KF amount is now zero, the the record removed from the E fact table. So depending on how many transactions you might have where all KFs are zero, you may see a significant reduction in rows after compression, or you may not.
<b>More queries</b> - One possible negative impact, when you run a BW query, the BW must now run a SQL query on the Fact table and all the tables it joins to, and another query on the E fact table and all the tables it needs to join to, and then must mere the results. This genreally should not be of much impact, but with a bad dimensional model, you could have a scenario where the query now has to perform full scans of a few very large dimension tables twice, which could outweigh the benefits of compression.
So as Bhanu says, yes compression will help queries that do not use the aggregate, and if you are not currently compressing your aggregate, compressing the base cube forces compression of the aggregate up to the same point, and that would hapl as well, as the aggregates have the same F and E fact table scenario as the base cube.
Message was edited by: Pizzaman
Made a change to clarify what I meant about the impact of setting the Request ID to 0. When A.H.P. mentions the removal, he (my assumption) is not not suggesting that the "removal" of this data value accomplishes anything, but rather, by changing the value to 0, this allows fact rows that have identical combinations of characteristic values but were loaded in different Requests, can be rolledup together when compression occurs.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.