Joining with calculated columns in Analytic views ?

[Former Member]

Hi,

I recently came across a requirement where an INTEGER column in data foundation had to be joined with a DECIMAL column in attribute view. Even though the modeler allowed me to link these two columns with an arrow, the validation step failed to perform the join.

To explicitly address this situation, I added a calculated column ( casting DECIMAL field as INTEGER ) to attribute view. This time the modeler did not allow me to link the INTEGER column in data foundation to calculated column in attribute view.

Is this behavior expected ? If yes, is there an alternative ( apart from modifying base tables ) to convert columns to different data types and also allow joining with them ?

[Former Member · Accepted solution]

Your replication or ETL flow should be loading the decimal field as an integer. You should resolve that and then the model will work fine.

(For reference - you can only join calculated fields in the Calculation View. However, this is bad modeling practice (clumsy, bad performance) and I've never seen a compelling reason to do this.)

[Former Member]

You can use a GENERATED ALWAYS statement if you want to generate a column for join purposes.

Because of the way that HANA uses dictionary keys for efficient joins, you can't use different types in a join.

John

[Former Member]

Thanks. will look at this option. I hope it does not break the ETL.

Regards,

shashank