Update a new column from two existing columns ina table

[Former Member]

I am trying to add a new column in the time dimension table. My customer wants a column that gives them year and quarter but they want quarter listed as Q1.  The CAL_QUARTER gives me 20131.  what they want is 2013 Q1.  I can do the conversion of data from YEAR_INT and QUARTER_INT with the following sql statement:

Select concat(YEAR_INT, Concat(' Q', + QUARTER_INT) ) from "_SYS_BI"."M_TIME_DIMENSION"

But in HANA I have not been able to insert the new data into the new column (REP_QUARTER)

I have tried insert into and update statements.  Can you help me out with the correct sql statement.

[rama_shankar3]

Hi Mark,

Based on what you I understand, you can have a cross reference table maintained manually for each quarter and link this table within your analytical view using a join. The downside of this approach is that you have to maintain values manually for the horizon no.of years and quarters.

Regards,

Rama

[former_member184768]

Hi Mark,

Personally, I prefer to create a new table with the date custom attributes defined exclusively. We have a custom calendar (integrating factory calendar) with custom definitions for the Week / Month / Quarter. Due to customization, we have different start and end dates for Weeks / Months as compared to the calendar week / month.

All such customization is stored in the custom table, which is created from the data from M_TIME_DIMENSION table. Eventually M_TIME_DIMENSION will be unloaded from the memory and will not be used in the application.

Regards,

Ravi

[lbreddemann]

Hi Mark,

the _SYS_BI schema, like all _SYS_* schemas are special system schemas and users are not supposed to change the definition or the contents of the objects in these schemas on their own.

With the next revision upgrade any new or changed column is likely to be overwritten again, anyhow.

Therefore you might be best off to stick with the much more flexible approach that you are using currently.

Also, you should think about formatting the actual output value in your report front end instead of doing it in the model. Ultimately you don't want to set the output format of data in the data model but keep this decision to the report design level.

- Lars