Procedure to generate time demensions using System date

[KamalMehta]

Hi Experts,

I have created a Table having time dimensions such as Year start date , Month Start Date , Week start date etc. Now i need to write a stored Procedure to Populate all the above time dimensions of this Table based upon Calendar day for last 5 years and also 15 years going forward.

Can you please guide on writing the Procedure for this.

Thanks

[Former Member]

Hi Rocky,

     my document may help you

[Former Member]

Have you looked into M_TIME_DIMENSION? This may solve your problem without the need for code.

[KamalMehta]

Hi John,

Thanks for your time.

Can you please elaborate how can i use this table for my requirement as i new to HANA.

I have checked this table.

Thanks

[Former Member]

You didn't explain your requirement but you may be able to join on this table. Here's an explanation of how it works.

http://scn.sap.com/docs/DOC-25977

This concept isn't HANA-specific, other databases use it too.

[KamalMehta]

Thanks John for taking time again.

Actually my requirement is that user would enter Period and Year in the selection screen and based upon that KPI's for actual date would be calculated i.e. YTD ,MTD , CY , PY etc. in HANA.

Can you please suggest how can i use the Time Table created based on the above link and integrate it in HANA Model for various Time based calculations as stated abovee.

thanks

[Former Member]

Why not use BW and Virtual Time Characteristics? That's a SAP Standard fit for your requirements.

Activating Virtual Time Hierarchies - OLAP Engine - SAP Library

[Former Member]

If you want to do this in HANA, you could of course adapt this code for HANA. It wouldn't be a big effort to get a developer to translate this to SQLScript.

Create and Populate Date Dimension for Data Warehouse - CodeProject

[KamalMehta]

Hi John,

Thanks.

Actually we need to implement the same in HANA.

The Table M_TIME_DIMENSION is not helping much as i need to do various time based calculations for YTD, MTD etc. that's why i thought to create some Time Dimension Table as stated in below blog by Ravindra.

For this i need to write Procedure to populate this table with all the required time dimensions.

Please suggest.

Thanks

[Former Member]

Yes in this case you could get a SQLScript programmer to convert the script I posted. All the logic is in there.