M_TIME_DIMENSION week number wrong

[lbreddemann]

Hey there, Lars here.

Long time answerer and rare question poster.

Just stumbled over the following issue with SAP HANA's built-in M_TIME_DIMENSION data.

select * from m_database;
/*
SYSTEM_ID   DATABASE_NAME   HOST    START_TIME                  VERSION                 USAGE
     
HXE         HXE             hxehost 15/09/2020 6:29:36.226 PM   2.00.045.00.1575639312  DEVELOPMENT
*/

--- start fresh
truncate table  "_SYS_BI"."M_TIME_DIMENSION" ;

-- generate time dimension data, starting the week on Monday
do begin
    exec 'MDX UPDATE TIME DIMENSION Day 2020 2021 FIRST_DAY_OF_WEEK Monday"';
end;    

select count (*) from "_SYS_BI"."M_TIME_DIMENSION" ;
/*
COUNT(*)
734     
*/

select 
     year
   , week
   , date_sql
   , day_of_week
   , to_varchar(date_sql, 'WW - DAY') as "TO_VARCHAR conversion"
from 
    "_SYS_BI"."M_TIME_DIMENSION" 
where 
	   week != to_integer(to_varchar(date_sql, 'WW'))
	and month = 1 
order 
    by date_sql asc;
    
/*
YEAR    WEEK    DATE_SQL    DAY_OF_WEEK TO_VARCHAR conversion
2021    53      01/01/2021  04          01 - FRIDAY            <<< M_TIME_DIMENSION 
2021    53      02/01/2021  05          01 - SATURDAY          <<< thinks these dates are in calendar week
2021    53      03/01/2021  06          01 - SUNDAY            <<< 53 of 2020
2021    01      04/01/2021  00          02 - MONDAY          
2021    01      05/01/2021  01          02 - TUESDAY         
2021    01      06/01/2021  02          02 - WEDNESDAY       
2021    01      07/01/2021  03          02 - THURSDAY        
2021    01      08/01/2021  04          02 - FRIDAY          
2021    01      09/01/2021  05          02 - SATURDAY        
2021    01      10/01/2021  06          02 - SUNDAY          
2021    02      11/01/2021  00          03 - MONDAY          
2021    02      12/01/2021  01          03 - TUESDAY         
2021    02      13/01/2021  02          03 - WEDNESDAY       
2021    02      14/01/2021  03          03 - THURSDAY        
[...]
*/    

Based on the ISO week date definition, the TO_VARCHAR conversion from date to week number is correct and the deviation of both approaches is not documented, so I assume this is not as it should be.

Looks to me as if the the the data generation function not only starts the week on Monday but also the whole year, which is wrong.

As I don't have any S-user to create support incidents I trust that the SAP "community support" will somehow feed this to the SAP HANA developer team.

[Taesuk · Accepted solution]

Hi Lars,

Thanking you for bringing this topic for discussion. The definition of ISO week is the first week where 'THU' exists which is the week of '07/01/2021'.

The TO_VARCHAR 'WW' option is based on first day of the year '01/01/2021' which is not ISO week. For other DBs, there is 'IW' patter which is the ISO week which we do not provide. (e.g,. https://www.postgresql.org/docs/8.1/functions-formatting.html)

[lbreddemann · Accepted solution]

Hi Tae Suk

thanks for the super-quick reply.

To summarise:

- the TO_VARCHAR 'WW' option is not using ISO week, but simply starts at the 1st of January every year.

- the M_TIME_DIMENSION does use ISO week (as described here).

Reading back my question, I see that I mixed these two up.

Nevertheless, this behavior is neither documented nor obvious and can lead to rather difficult to analyse result deviations.

It would be great, if HANA could provide both the IW conversion option as well as a adequate documentation of the intended functionality.

Thanks,

Lars

[Taesuk · Accepted solution]

Hi Lars,

I will try to get the documentation updated. As for IW conversion option, due to other high priority items, I'm not sure when this can be planned and delivered but will add this to our backlog.