Is there a way to propagate Table Column Comments to Views?

[mrwong05]

Hi Hana Experts,

We are using HANA 2.0 SPS02 On-Prem edition.

Our developers are having trouble propagating Hana Table Column Comments to View Column Comments.

This is the create view documentation: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.05/en-US/20d5fa9b75191014a33eee9269...

Here is the COMMENT ON documentation: https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.05/en-US/20d3817075191014ba7bcfdbab...

So our objective is to write views and consume the views in Calculation views and use the Comment on the column for the Calculation View's Column Label.

For Table Columns consumed in a CV, extract semantics is available to populate the labels. But for Views which have no Comments, the underlying Table Columns can not be used to extract semantics.

It's very tedious to write a view of a table with all the comments when there are multiple tables involved, which is why we want to only write the comment for the base tables and have the views auto-generate the View Column Comment.

For single table views, we can extract the comments from the underlying table with the following:

SELECT 'COMMENT ON COLUMN ' || SCHEMA_NAME || '.' || '<table_name>.' || COLUMN_NAME || ' IS ''' || COMMENTS || ''';'FROM TABLE_COLUMNS WHERE SCHEMA_NAME = '<schema_name>' AND TABLE_NAME = '<table_name>';

and re-run the COMMENT ON SQL on individual Views.

Any suggestions?

[lbreddemann · Accepted solution]

There is, unfortunately, no way to leverage column comments (from the system dictionary) as column labels for dependent objects.

One issue with the approach you described is that columns can change meaning when they are used in views. Typically, views are used to transform and filter data to make up a different semantic of the underlying data - thus describing comments can not generally be copied.

Having said that, it would be great to have the option to declare additional metadata, like a label, maybe multi-language labels, units of measure, currency, timezone, calendar system, item-catalog, link to further documentation, etc. declared once with the data model and the ability to reuse and enhance this information "up the usage chain".

It would be really nice to be able to know for sure what data a column contains at any point in the development and usage cycle. If SAP only had some sort of data dictionary functionality somewhere...

Sorry for being a bit cynical here, but this sort of feature has been built several times in different SAP (and other vendor's solutions) in the past, but it does not seem to have generally stuck. I guess it died together with the model-driven-development idea and the CASE tools.

[NeilJH]

I'm looking to do this for views too.
Creating tables and views through the Catalogue/Repositories.
For a table we can do this:
{name = "COL_NAME"; sqlType = NVARCHAR; length = 128; comment="Columns comes from table X";}

Want to do similar for views when creating them like this:
schema="ETL";
public=false;
query="
select COL_NAME          // somehow add a comment to this view-column
from xxx
";