Regarding "Generate Time Data" Option in HANA

[Former Member]

Hi All

In the Quick Launch page of HANA studio we have an option "Generate Time Data" , can any body explain usage of this option.

Thanks

Santosh

[Former Member · Accepted solution]

"Generate Time Data" will create records in table SYSBI.M_TIME_DIMENSION (and others, check schema SYSBI) according to a calendar and granularity you choose. You can use this data to build time-based Attribute Views and then use these inside your Analytical or Calculation Views

--juergen

[Former Member · Accepted solution]

Hi Juergen

    In that case, Once it is generated can we use it in designing multiple analytical views.If one has generated time data already can any body use those generated tables in designing their analytical views and calculation views .e

Also I can't see others message, I got a reply through mail notifications but those replies are not visible here.request the moderator's help

Thanks

Santosh

Message was edited by: varada santosh

[hai_murali_here · Accepted solution]

Hi Santosh,

Generate Time Data option under Quick Launch helps the user to generate the Time data so that it can be used for the creation of Time based Attribute Views.

When you click Generate Time Data,you will be provided with two options

1.Gregorian:Mainly the data will be generated beased on From and To years along with the granularity(Hour,Min,Sec,Day,Month) mentioned

2.Fiscal:In this case,the time data will be generated based on the variant defined(some companies may use their own time period)

This time data will be stored under _SYS_BI.M_TIME_DIMENSION table(For Gregorian)/_SYS_BI.M_FISCAL_CALENDAR table(For Fiscal).If any user is having the access to this _SYS_BI schema, then he can start using these tables to create a Time based Attribute Views and can consume that in Analytic or Calculation VIews..

Hope this helps you!

Rgds,Murali

[Former Member · Accepted solution]

Hi Murali

  Thanks for sharing your wisdom, in that case can we say it is one time activity for organization.As once generated by single user any body having access to _SYS_BIC schema can use this time data .

Thanks

Santosh

[hai_murali_here · Accepted solution]

Hi Santosh,

Yes,you are right

You can refer this for more details on Generate Time Data : http://help.sap.com/hana/hana1_model_en.pdf

Rgds,Murali

[Former Member · Accepted solution]

Hi Murali

    Thanks for clarifying my doubts,I have gone through the mentioned book ,it talks about how to generate time data [Gregorian,Fiscal] ,but there is no detailed explanation like you mentioned.

Thanks Once again.

[hai_murali_here · Accepted solution]

Yes,Thats true,the point about consumption of these tables by different user is not mentioned.

Rgds,Murali

[former_member182277 · Accepted solution]

Hi Santosh,

As this question is already answered i just want to add one point in this.If you are using Generate data option then to make it option work we need to replicate the T005T, T005U, T009,T009B into the Studio then this option will work.

Regards,Neha

[Former Member · Accepted solution]

Hi Neha

  Thanks for adding a new point,can you eloborate or explain why do we need these tables to generate time data.If it is the case then how can we use this option with Non-SAP systems as these tables won't be there in Non-SAP systems. Request you to eloborate the releation ship between these tables and Generating Time Data.

Thanks

Santosh

[former_member182277 · Accepted solution]

Hi Santosh,

These are te standard tables that needs to there in SAP HANA Studio. Because if these tables are not exist then you are not able to use the Time Data option.

Personally, when we set up the SAP HANA studio in our lab, I am facing the same challenge and after replicating the standard table now I am able to use the option.

Hope it answer to your question.

Regards,Neha

[Former Member · Accepted solution]

Hi Neha

     That note is very helpful, but incase of Non-SAP systems is there any substitute for these tables or it is not needed incase of Non-SAP Systems.

Thanks

[former_member182277 · Accepted solution]

Hi,

According to my knowledge its limited to SAP HANA only. For Non-SAP systems, i think it might not be the case.

Regards,

Neha

[former_member399867 · Accepted solution]

Hi All,

Some how the table M_TIME_DIMENSION in schema _SYS_BI is deleted in our HANA DB system. And when we are trying to regenerate the time data, we are getting below error

Message : 

Exception while generating time data: SAP DBTech JDBC: [256]: sql processing error: Internal error: 'UPDATE TIME DIMENSION DAY 2012 2012 FIRST_DAY_OF_WEEK SUNDAY' could not generate data (Table _SYS_BI:M_TIME_DIMENSION not found):

Can you please suggest , as all other built models are throwing error out of this table missing.

Thanks

MB

[former_member225497]

I'm having the same challenge as Manjunath Bidarkar.  Is there a way to populate M_TIME_DIMENSION with SQL Syntax?

thanks!

Brian

[Former Member]

No answer for previous question ???

[lbreddemann]

Hmm... what's not OK for you with the MDX command I posted above Brian's question?

That's actually the command send by the UI to the back end.

- Lars

[Former Member]

I have to use SQL command not MDX.

[lbreddemann]

What kind of difference does it make for whatever coding you do?

The MDX command interface is the same as the SQL interface.

Where ever you can run a SQL command with SAP HANA there you can run the mentioned MDX command.

- Lars

[Former Member]

Thanks Lars for your help. I tried it and it really works

Stefan

[Former Member]

Hi,

We have a requirement to generate these dates using SQL script. When I tried to execute the SQL script mentioned above i.e. "MDX UPDATE TIME DIMENSION Date 2000 2020" from HANA studio console, I get the below error.

Could not execute 'MDX UPDATE TIME DIMENSION Date 2000 2020'

SAP DBTech JDBC: [2]: general error: MDX query doesn't support prepared statement

Can you pls let me know if am doing anything wrong ?

We are in SAP HANA version: 1.00.096.00.1432206182

Thanks

Venkatesh

[lbreddemann]

Hmm... apparently there had been a change in the SAP HANA studio so that now all SQL commands get prepared before execution...

Anyhow, I was able to do something like this on rev. 96:

create procedure run_upd_time ()

language sqlscript

as

begin

    exec 'MDX UPDATE TIME DIMENSION Year 1950 1953';

end;

call run_upd_time();

Shouldn't be too difficult to build upon this.

- Lars

[Former Member]

Thanks Lars, it just worked fine.

-Venkatesh

[former_member399867]

Hi All,

Can someone share the structure of table M_TIME_DIMENSION from _SYS_BI schema or else can provide SQL syntax to Create table M_TIME_DIMENSION. We need to create this table to rectify Generate Time Data Error in our Hana studio.

Please help, thanks

Regards,

MB

[yeushengteo]

Hi,

This is the SQL table definition.

CREATE COLUMN TABLE "_SYS_BI"."M_TIME_DIMENSION" ("DATETIMESTAMP" LONGDATE CS_LONGDATE NOT NULL ,

  "DATE_SQL" DAYDATE CS_DAYDATE,

  "DATETIME_SAP" VARCHAR(14),

  "DATE_SAP" VARCHAR(8),

  "YEAR" VARCHAR(4),

  "QUARTER" VARCHAR(2),

  "MONTH" VARCHAR(2),

  "WEEK" VARCHAR(2),

  "WEEK_YEAR" VARCHAR(4),

  "DAY_OF_WEEK" VARCHAR(2),

  "DAY" VARCHAR(2),

  "HOUR" VARCHAR(2),

  "MINUTE" VARCHAR(2),

  "SECOND" VARCHAR(2),

  "CALQUARTER" VARCHAR(5),

  "CALMONTH" VARCHAR(6),

  "CALWEEK" VARCHAR(6),

  "YEAR_INT" INTEGER CS_INT,

  "QUARTER_INT" TINYINT CS_INT,

  "MONTH_INT" TINYINT CS_INT,

  "WEEK_INT" TINYINT CS_INT,

  "WEEK_YEAR_INT" INTEGER CS_INT,

  "DAY_OF_WEEK_INT" TINYINT CS_INT,

  "DAY_INT" TINYINT CS_INT,

  "HOUR_INT" TINYINT CS_INT,

  "MINUTE_INT" TINYINT CS_INT,

  "SECOND_INT" TINYINT CS_INT,

  "MONTH_LAST_DAY" TINYINT CS_INT,

  PRIMARY KEY ("DATETIMESTAMP")) UNLOAD PRIORITY 0 AUTO MERGE

Hope useful for you.

Regards.

YS

[former_member225497]

thank you!  I'm wondering if there's a way to populate this table using SQL instead of HANA studio?  I see the MDX UPDATE TIME syntax, but there is not much documentation about it available.

thanks!

Brian

[lbreddemann]

Hi there,

given the SAP HANA Studio JDBC trace, the time data is actually populated by the MDX syntax you mentioned.

As an example I created the data for the years 1950 to 1953 with granularity YEAR and this command got recorded:

MDX UPDATE TIME DIMENSION Year 1950 1953

Looks as if the syntax is something like

MDX UPDATE TIME DIMENSION <granularity> <start year> <end year>

However, as the syntax is not officially documented yet, this is not officially supported.

- Lars