Dynamically calculated columns on HANA tables

[Former Member]

Hi,

I had calculated attributes in my attribute view. I didn't want to do that in the cal.view for various reasons (e.g. to ensure every view that uses the attributes also uses the same calculation).

For performance reasons it was suggested to alter the table and add a dynamic column.

ALTER TABLE "<your_schema>"."<your_dimension_table>" add ( <calculated_attribute_name> varchar GENERATED ALWAYS AS (<formula>));

I haven't found a lot of documentation on that feature so maybe you can help clarifying for me:

1. our tables come from ERP via SLT. Do I assume correct that synchronization will not be negatively impacted as such dynamic columns are not physically stored?
2. will such calculations really be faster than doing it in the attribute view? ... (would also be nice to briefly understand why)
3. Do I assume correct that I can use these columns like any column (e.g. for joins)?

thanks much

Elmar

[Former Member · Accepted solution]

Hi Elmar,

One thing I can't recommend highly enough is to test out such scenarios yourself whenever possible.

There are often many, many ways to get the desired end result in HANA - and your use case can highly influence your approach.

If you want to test the impact of calculated attributes vs generated columns, I'd highly recommend you find a fact table with ~ 100 million records if you have one, grab an attribute view that has an underlying table with a few hundred thousand records, create a calculated attribute, and query your model.

Do the same with a generated column, and compare your performance. I think the results will surprise you, and convince you of which approach is better.

(I used specific numbers to emphasize the importance of your use case against all available options. If you have small tables, calculated attributes may be fine... but this needs to be tested).

Also, in some cases you may want to do generated columns against SAP tables that come in through SLT. Sometimes this may cause headaches though, such as when ABAP Accelerator is run against HANA tables. ABAP code will expect tables to have a structure X, run SELECT *, find structure Y, and blow up. I'm certainly not endorsing this style of coding, but refactoring all the related code isn't an option at this point.

Just something to be mindful of.

[justin_molenaur2 · Accepted solution]

Since I have a sneaking suspicion that you have already performed the test you mention here in a thorough  manner, what were your findings? Did you see drastic variance between these two methods? I could do the test myself but I can guarantee you know the results off the top of your head

with your comment on ABAP Accelerator running against modified tables, I assume this would affect any scenarios that were either enhance by GENERATED ALWAYS technique or by SLT configuration. In either case, from your statement about it 'blowing up', I would assume that in use cases that expect a certain structure to function correctly, these would be bad design choices. I was not aware of this risk, I have introduced some structure modifications for ECC tables at my current client, need to rethink those decisions.

Regards,

Justin

[Former Member · Accepted solution]

Hey Justin,

Good to hear from you. My findings have been - "Yes, drastic difference"   but again, I must qualify that on a per-use-case basis. A dim table of 10 records (to illustrate) shouldn't have much problem with Calculated Attributes, but generally speaking - generated columns are significantly better.

Also, you are correct regarding ABAP Accelerator. Don't ask me what ABAP Accelerator actually is (I'm a Java guy that keeps ending up on mostly non-SAP sources), but what a colleague told me was that the "SELECT *" peppered so elegantly throughout ABAP code base get hosed when they receive an unexpected data structure.

There may be other workarounds. I've never done this - and some may say it's an awful idea - but if you *had* to do calculated attributes, and couldn't modify physical tables, perhaps one could create custom tables via SLT or Data Services, store the calculations there along with the same key fields, and then join them in an Attribute View. Good idea? Maybe. Awful idea? Quite possible as well. I've never had to do it.

As usual - there are many ways to skin a cat when it comes to HANA.

[Former Member]

Thanks everybody for the helpful feedback.

It basically confirmed not going down tha track. Other reasons I had not to go down this path:

• maintainability and documentation is not as good as calculations inside the modeling views
• according to SAP the performance only improves if all calculated fields of an attribute view are calculated that way. In most cases I would need the content of more than 1 table thus calculated columns cannot remove all view calculations.

I keep it in mind however as exceptional solution for certain situations.

[Former Member]

Hi Elmar,

I'm little confused. Where would you actually right this 'Alter table command with GENERATED AS ALWAYS syntax' ?

Thanks & regards,

Jomy

[justin_molenaur2]

the SQL commands could be issued directly in the SQL console or alternatively from within a SQLScript procedure if needed.

Regards,

Justin

[Former Member]

Hey Elmar,

So GENERATED ALWAYS statements are AWESOME! They are materialized.

1. They add a small amount of cost to insert, but it is negligible (a few %). In most SLT scenarios you won't even notice it. They do also carry a storage cost (because they are materialized), which is the major negative.

2. Well it depends. I would almost never use GENERATED ALWAYS instead of using a calculated column, because there is a storage cost of materializing data. The cost of calculation is in most cases very low anyhow. In your case you say you always do the calculation, so maybe then it's worth using it. But I doubt it because the HANA calculation engine uses C++ for those functions and it's ridiculously fast.

3. Yes! This is why you need GENERATED ALWAYS statements. For instance if I have TIMESTAMP in a fact table then I quite often generate DATE as a column. Now, I can do joins on DATE in master data objects which improves performance massively compared to doing a join on M_TIME_DIMENSION and a join on DATE_SQL.

The other time where you use GENERATED ALWAYS is where you want to filter on the generated column. Because the value is saved as a dictionary key, it is very efficient to filter. In addition if you are using Analytic Views or Calculation Views, the filter predicate will be passed down to the OLAP engine, causing very high performance aggregations.

John

[Former Member]

Hi Elmar,

I'll try to answer all the question with the experience that I had. So:

1. Personally, I have never used this type of dynamically calculated columns with SLT tables. But, you're correct when you say that is not phycally stored. So, I guess, it'll not have any sort of negative impact.

2. It actually depends, but if it's not faster it'll at least be the same speed. Attribute Views utilize only the Join Engine in SAP HANA, do not uses the OLAP or the Calculation Engines. Because of that, it's usually faster. (there is a lot of content here in SCN about the these three engines, I'd recomend a brief search )

3. Yes, you can.

Regards,

Rodrigo

[Former Member]

Hi Rodrigo,

Just FYI calculated attributes are calculated in the Calculation Engine. Run a VizPlan against queries that include calculated columns and you'll see this.

Cheers,

Jody