Is data compression all automatic? Or does manual steps occur in HANA Studio

[Former Member]

Hi all,

I'm new to HANA and have been leveraging SCN in a BIG way to answer my questions (thanks everyone for your contributions). This is my first post as I was unable to find an answer to my current question .

I've been reading up on data compression in HANA and I learned that there are different techniques, such as Dictionary, Cluster, and Run-length encoding.

My Question is: Is the compression automatic? Or does it need to be modeled within HANA Studio? Let's use Dictionary Encoding as an example. Are there algorithms in place within HANA that will automatically aggregate values so only distinct values remain? Will the attribute vector  and inverted indexed tables automatically be created?

Just as some background, this is what I am basing my question on:

http://www.agilityworks.co.uk/our-blog/demystifying-the-column-store-%E2%80%93-viewing-column-store-...

Thank you!

Kyle

[Former Member · Accepted solution]

Hello Kyle,

Compression will be done automatically. Data base layer/engine will take care of this part.

Using HANA studio you can aggregtae the values. For ex: If you want to present data in reoport with/with out aggregation, that will be your choice. With aggregation, aggregated data will be displayed in report and with out aggregation detailed(distinct) data will be displayed. This option is available in calluculation view. I hope we have check box while creating calc view.

Thanks,

Phani

[Former Member]

Thank you everyone for your responses.Very helpful and much appreciated

Kyle

[Former Member]

Compression is automatic.

[lbreddemann]

Luckily just CIA and not NSA (couldn't resist).

[justin_molenaur2]

Doesn't it technically occur during the SPARSE (TYPE=SPARSE) of the merge process? If this is the case, then auto merge usually handles this (automatic), but it can be controlled manually as well.

From what I have observed - until the SPARSE process executes, used memory can be high. After this completes, I assume the appropriate compression techniques are applied and the memory usage drops quite a bit.

SELECT *

FROM "SYS"."M_DELTA_MERGE_STATISTICS"

WHERE "TABLE_NAME" = <TABLE>

Regards,

Justin

[richard_bremer]

Hi Justin,

you are right, the compression is related to the delta merge - and therefore, as long as delta merges happen automatically, compression will also happen automatically.

SAP HANA has two compression stages on the column store: the first - and typically dominating one - is the implicit compression obtained from using a data dictionary for all column-store columns.

The second stage is often called "sparse compression" - it offers additional compression on top of the dictionary compression, with several available algorithms. During the "optimize compression" run, the most appropriate compression algorithm is chosen for each column (and some columns may not be sparse-compressed at all, because it would not bring a benefit).

The optimize compression run does not happen with every delta merge. Instead, it will be performed with the first delta merge on a given table, and then only if the data content of the table has changed significantly (typically, if I remember correctly, the system waits until the number of records has doubled. Note that the optimize compression run may _change_ the compression type chosen for a given column. The sparse compression itself will be done with every delta merge, using the algorithms selected in the past optimze compression.

If you have switched off automatic delta merging globally, or if you have disabled automatic delta merging for a given table, there will also be no automatic compression (in the entire system or on the given table). BW on HANA uses the smart merge feature, so that in a standard BW on HANA, automatic compression will happen (with the timing of the delta merge being decided in a cooperation between BW and HANA).

Best,

Richard

[justin_molenaur2]

Good to get confirmation on this process. So I guess inherently through the MERGE step of the merge process, we already realize benefits of compression through the dictionary usage. From there, further optimizations take place through SPARSE.

Excellent summary, thanks for that!

Happy HANA,

Justin