Leveraging HANA Time Dimension Tables within your views
We all need to generate time dimensions within HANA. This generation depends on if your business leverages the gregorian calendar and/or the fiscal year calendar and which associated variant. This drives the tables that are required to be loaded with T009 and T009B for the fiscal year variant and T005T and T005U for the Gregorian Calendar. Yes, this means that you must replicate these accordingly for the time dimension generation to work.
Within these tables, there are limitations to the data sets that are available, mostly in the Fiscal Calendar and the aggregated calendar (i.e. M_Time_Dimension_Month). If you ever want to use these tables, you either have to use the full date or month, period or year independently. There are some cases that you need to leverage the concatenation within a join or other and since using calculated attributes in a join is not possible, this causes an issue. A few options that I reviewed were to create a generation SQL statement directly against the M_TIME_DIMENSION tables so the new column would always be populated but I don’t know the impact to the program that was created. With this risk that is an unknown, once the table was generated, I took a second option of creating a new table which copies that data from the original time dimension and creates a Z table.
One instance that I used this was for Fiscal Year Calendar. Within the CO-PA tables CE1*, you have a created on date and budat, which is your posting date but the planned values in CE2* don’t go to the Calendar Day level so you are limited. In order to satisfy the link between the time dimension table and the base foundation tables, I created a Z Time Dimension table that then sources a new column with the format YYYYMMM. By doing this, I am able to join to PERIO from CE1 and PERBL from CE2. This also allows me to either use the filters against the base tables or within the attribute views using the between statement.
What I have found is there are multiple options that you have and since the time dimension tables are only loaded once for the first 50 years, this is a great option.
Please Note***One lessons learned after loading the T009 and T009B tables, I had to close out HANA Studio and then opened it back up in order for it to recognize the Variants that were available. This is unknown why this happens but something that I found to happen each time I made an update.
I hope that this can help others when generating your time dimension.
Please follow me on twitter @tim_korba.