locked
Period or Calendar dim RRS feed

  • Question

  • Hi,

    Question: I've a Period  (month) in a table. Is it wise to convert this to a date for ease of use of time intelligence functions, for instance? Or should I create a Custom Period Lookup table?

    Should I type the period table as a Date table? In order to use time intelligence funtions?

    Please some advice?

    Greetz,

    Hennie

    Tuesday, September 8, 2015 6:36 AM

Answers

  • Hi Hennie,

    in order to keep the options open, I'd very much recommend to:

    - create a separate date table and mark it as a date table (as you already said, some time intelligence functions rely on a proper date table)

    - you should use the date fields from you date table in your reports, so hide the date fields in your fact tables from the client tools

    - I'd convert the month field in the your fact table to a date-field (last day of the month) and use it to connect with your new DimDate (wouldn't work with month anyway because m2m)


    Imke Feldmann TheBIccountant.com

    • Proposed as answer by Charlie Liao Wednesday, September 9, 2015 7:04 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:55 AM
    Tuesday, September 8, 2015 7:07 AM
    Answerer
  • Hi,

    Best approach is to have one time table and convert all other time colums to date. Then join them to time table.

    So for example if you have values like "201509" its definitly best to convert it to date "09/01/2015" and join it to time table. Then use measures (mtd,etc..) to get expected behaviour.

    • Proposed as answer by Charlie Liao Wednesday, September 9, 2015 7:04 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:55 AM
    Tuesday, September 8, 2015 7:24 AM

All replies

  • Hi Hennie,

    in order to keep the options open, I'd very much recommend to:

    - create a separate date table and mark it as a date table (as you already said, some time intelligence functions rely on a proper date table)

    - you should use the date fields from you date table in your reports, so hide the date fields in your fact tables from the client tools

    - I'd convert the month field in the your fact table to a date-field (last day of the month) and use it to connect with your new DimDate (wouldn't work with month anyway because m2m)


    Imke Feldmann TheBIccountant.com

    • Proposed as answer by Charlie Liao Wednesday, September 9, 2015 7:04 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:55 AM
    Tuesday, September 8, 2015 7:07 AM
    Answerer
  • Hi,

    Best approach is to have one time table and convert all other time colums to date. Then join them to time table.

    So for example if you have values like "201509" its definitly best to convert it to date "09/01/2015" and join it to time table. Then use measures (mtd,etc..) to get expected behaviour.

    • Proposed as answer by Charlie Liao Wednesday, September 9, 2015 7:04 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:55 AM
    Tuesday, September 8, 2015 7:24 AM