cancel
Showing results for 
Search instead for 
Did you mean: 

Compression

Former Member
0 Kudos

Hello Guys

I have built aggregates on my cube to ikprove query performance.But i have not compressed the data in the cube. My question is that will compressing the data in the cube in any way increase my query performance.

Thanks

Raj

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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.

Former Member
0 Kudos

thanks pizzaman very useful info full points to you

Answers (1)

Answers (1)

edwin_harpino
Active Contributor
0 Kudos

hi Raj,

yes, compression will also improve the query performance,

by eliminate the reqeust id and reduce volume data in

infocube.

hope this helps.

Former Member
0 Kudos

Hai

I think my query already takes the data from the aggregate( aggregate is compressed).

My question is since the query gets the data from the aggregate ,compressing or not compressing the cube should not affect the query performance in any way right.

Please correct me if i am wrong I am quite new to BW.

Thanks

Rajesh

former_member188975
Active Contributor
0 Kudos

Hi Rajesh,

It would help in case the system cannot find the necessary data in the aggregate and needs to fetch the data from the cube.