Storing date fields in SAP HANA for optimal data load and report performance?

[rama_shankar3]

Guys:

Depending on the source of the data for SAP HANA, the date field type declaration is declared either as a string / timestamp / date and time as 2 separate fields.

As an example if we import data from SAP ECC using SLT into SAP HANA dates are converted into strings by default and we can tweak it as needed.

Below is one scenario where I loaded date field from a flat file into SAP HANA:

Create column table sales.sales_fact(region_id integer, item_id integer, sales_amount double, order_dt timestamp);

The date format in the insert statement looks like this:

INSERT INTO sales.sales_fact  (region_id , item_id , sales_amount,  order_date ) values (1,2,10.5, '4/21/2009 2:25:53 PM')

Requests: What has your experiences been with loading date and time fields into SAP HANA with high volume data ?

Thanks,

Rama Shankar

[former_member182277 · Accepted solution]

Hi Rama,

I loaded the around 4 million of data through flat file (.csv file).

I loaded the file using Control file that is created in the Linux and data is successfully loaded.

Regards,

Neha

[rama_shankar3 · Accepted solution]

What was the format of your date and time field?

[rama_shankar3 · Accepted solution]

Neha:

  I am able to load data with date fields pretty quickly. No issues loading data. I just have not benchmarked high volumes yet.

I am trying to get an in-depth understanding on the preferred format to load to date and time fields into SAP HANA for optimal performance.

Based on recent research, I feel that the DATE and TIME data type will be better. I will be trying this out soon. Have you used DATE and TIME data types for date in your load?

Let me know what format, if you used anything other than the format I mentioned above.

Thanks,

Rama

[former_member182277 · Accepted solution]

Hello Rama,

I used simple data and time functionality while loading.

But if you have other requirement also like in other format you need to load the data then please go to

SAP HANA Studio -> Catalog -> _SYS_BI -> Tables -> M_FISCAL_CALENDAR / M_TIME_DIMENSION

You can see the definition and data for the tables. That will give you the better understanding like when which data type need to be used while loading the data.

Hope it is useful for you.

Regards,

Neha

[rama_shankar3 · Accepted solution]

Neha:

Thanks for your response.

  Which format did you use? Please can you share the  sample create table and insert SQL taht you used in your scenario for your date usage like I have done above.

  Also, do not miss John's blog- that is Awesome...

  You did bring up another topic all together different from this one on the fical calendar usage / customization - which a BIGGIE...I will research and get back to the HANA gurus if I have questions on how to use the fiscal calendar topic.

Thanks.

Rama

[former_member182277 · Accepted solution]

Hello Rama,

Please find the below code:

insert into "<schema_name>"."<table_name>" values ('00001', '00002', '00003', '1996-01-02 00:00:00.0')

I used data type as timestamp.

Hope it is useful.

Regards, Neha

[rama_shankar3 · Accepted solution]

Neha:

Thanks.

  You are using the same format that I have mentioned above. If you read John's blog ( see link from John below) you will understand that this is not the best optimized way of loading date and time into SAP HANA when it is high volume. It is my understanding that SAP HANA prefers non-string data types when it comes to fact tables.

http://scn.sap.com/community/in-memory-business-data-management/blog/2012/03/20/inside-sap-hana--opt...

Rama

[Former Member]

Hi Neha/Rama,

Greetings!

Request your help on a similar issue that I am facing.

Scenario: I have been given a CREATE TABLE script of Oracle as below:

CREATE TABLE PD_PRODUCT

(  PRODUCT_ID varchar(256) NOT NULL,  DATALOAD_TIME DATETIME DEFAULT GETDATE() NOT NULL );

I replace this with the following in HANA Studio:

CREATE TABLE PD_PRODUCT

(  PRODUCT_ID varchar(256) NOT NULL,  DATALOAD_TIME TIMESTAMP DEFAULT NOW() NOT NULL );

Requirement: To update the DATALOAD_TIME column with DEFAULT values of current timestamp whenever data gets loaded (this is my understanding based on the original script with GETDATE() what it tries to achieve.)

I have tried swaping NOT NULL with DEFAULT and all such combinations, use CURRENT_TIMESTAMP, etc. but these won't work. I tried to search if we can even use NOW() in CREATE table statement but did not find anything which says "NO". So I am still figuring out a way to do this.

Request your help.

Prior thanks!

Regards,

Kunal Gandhi

[rama_shankar3]

kunal:

You will have to use:

CURRENT_TIMESTAMP (if you need date and time) OR

CURRENT_DATE ( if you need date only)

Hope this helps.

Regards,

Rama

[Former Member]

Hi Rama,

I have already tried that and it does not work in the CREATE TABLE statement.

Regards,

Kunal Gandhi

[rama_shankar3]

Kunal:

I have not used this default option myself. I am busy with a go-live so no time to try.

For time being, please try using a insert trigger with the earlier options that I mentioned above.

When time permits, I will research this for you.

Regards,

Rama

[former_member184713]

Were you able to get the current_timestamp work as a default value in the CREATE TABLE ? That would be really useful for me too.

[Former Member]

No I was not. Let me know if you find a way.

[former_member184768]

Hi Keven,

Not sure which version of HANA are you currently on, but I tried the following and it works for me. I am on revision 49.

create table ty (a int, b timestamp default current_timestamp)

insert into ty (a) values (1);

insert into ty (a) values (2);

insert into ty (a) values (3);

select * from ty

Regards,

Ravi

[former_member184713]

Ho I see, That means that the default is partially working.

I take your example :

create ROW table ty (a int, b timestamp default current_timestamp)

insert into ty (a) values (1);

insert into ty (a) values (2);

insert into ty (a) values (3);

It work (rev 41). Create table is defaulting to Row table.

Column :

create COLUMN table ty (a int, b timestamp default current_timestamp)

insert into ty (a) values (1);

Could not execute 'insert into ty (a) values (1)'

SAP DBTech JDBC: [2048]: column store error: [1513] TableUpdate failed

insert into ty (a,b) values (1, current_timestamp);

Statement 'insert into ty (a,b) values (1, current_timestamp)' successfully executed in 2 ms 940 µs  - Rows Affected: 1

[Former Member]

This message was moderated.

[Former Member]

Take a look at my blog - I am loading 3.7bn rows of time/data data in 73 minutes.

http://scn.sap.com/community/in-memory-business-data-management/blog/2012/03/20/inside-sap-hana--opt...

[rama_shankar3]

Thanks John.

[Former Member]

Hi John,

I'm not having luck with the link - do you have an updated one?

Thanks,

Jody

[Former Member]

Here you go - looks like they changed all the links for that forum.

http://scn.sap.com/community/hana-in-memory/blog/2012/03/20/inside-sap-hana--optimising-data-load-pe...

[Former Member]

Great thanks!