Time Based Modelling using Calculated Column VS Generated Time Dimension

[Former Member]

Hi All,

Below is my Data Model.

DSO (BW) --> Analytic View (HANA Studio) --> Cal View (HANA Studio) --> Virtual Provider (BW)

Now, in my base DSO there is no CalYear but there is a WorkDate. So, I have created a Calculation Column in a projection

of my Calc View with the logic "midstr("WORKDATE",0,4)".

My model is working fine and I'm able to get my desired output.

Now I have read a few blogs and came to know about Time base modelling using Time Dimension in HANA Studio

i.e you would generate a Time Dimension in HANA Studio and then use this Time Dim in your Analytic View using

a JOIN between your FACT and TIME_DIM.

Could anyone shed some light on which is a better approach ?

PS : In my base fact table i.e my DSO I don't have a time dimension for me to do a JOIN between HANA generated Time Dimension

       all I have is WORKDATE

Kind Regards,

Ashwin

[dmitry_kuznetsov1 · Accepted solution]

hi, Ashwin, the answer will largely depend on your exact situation, most importantly on two factors: data volume and requirements.

If if we talk about a CalculatedView without forced SQL Execution, your data volume is large and your requirement is to display the data on aggregated level only (just the year and month, without the WORKDATE itself), I would use the formula (like you did) and immediately aggregate.

If your data volume is ok, you anyway really need CATS document number, dates, etc., I would probably join to time dimension. Plus add the texts for quarters/months/weeks, too.

And then test with PlanViz and post your outcome back here

Cheers,

Dmitry

[Former Member · Accepted solution]

Hi Dnitry,

Thanks for your thoughts, Yup I shall try the second approach and see how it performs and

post my outcome here

Just a quick question though.. how do I JOIN my fact table to the TIME_DIM ?

say I generate TIME_DIM_YEAR which would have a YEAR in it, so in my FACT table I have no

YEAR but I do have WORKDATE?

should I add a new field to my FACT table and do a JOIN between my new field and YEAR of my TIME_DIM_YEAR ?

or should I do a JOIN between WORKDATE (FACT TABLE)  and YEAR (TIME_DIM_YEAR)

Kind Regards,

Ashwin

[dmitry_kuznetsov1 · Accepted solution]

I don't have a BW @ HANA system at hand now, but on standalone HANA there is a table "_SYS_BI"."M_TIME_DIMENSION", which you may want to use. Just take care - it is @ day detail.

[Former Member · Accepted solution]

Thanks Dmitry,

appreciate your thoughts!

Kind Regards,

Ashwin

[Former Member · Accepted solution]

Hi All,

I have found an easy way to do it in BW, I just brought in Time Dimension into my BW DSO and

then went ahead with my current model.

DSO (BW) --> Analytic View (HANA Studio) --> Cal View (HANA Studio) --> Virtual Provider (BW)

Kind Regards,

Ashwin