This discussion is archived
13 Replies Latest reply: Jan 23, 2013 6:46 AM by Srini VEERAVALLI RSS

Date dimension

alex.v Newbie
Currently Being Moderated
Hello ,

there is a fact table having some measures (like measure1), and 3 date columns start_date , end_date , modified_date. and a requirement to analyze facts using one date dimension, that means to show in a report records like :
DATE, sum(Measure1) per start_date=DATE, sum(Measure1) per end_date=DATE, sum(Measure1) per modified_date=DATE.

how to design the BM in OBIEE 11g to accomplish this requirement ?

Thank you,
Alex

Edited by: alex.v on Jan 22, 2013 10:11 AM
  • 1. Re: Date dimension
    901916 Explorer
    Currently Being Moderated
    Hi,
    In BMM layer apply sum aggregation to the measure column. In answers create 4 columns

    date, filter(measure) using start_date=date, filter(measure) using end_date=date, filter(measure) using modified_date=date

    This meets your need right!

    Regards,
    Anitha.B
  • 2. Re: Date dimension
    alex.v Newbie
    Currently Being Moderated
    Anitha, thank you for your reply. the problem is that I need to show not just one row corresponding to one particular day date.
    but rather the ability to create reports having multiple rows like :
    DATE1, sum(Measure1) per start_date=DATE1, sum(Measure1) per end_date=DATE1, sum(Measure1) per modified_date=DATE1.
    DATE2, sum(Measure1) per start_date=DATE2, sum(Measure1) per end_date=DATE2, sum(Measure1) per modified_date=DATE2.
    .....
    or even
    MONTHx sum(Measure1) per start_date=MONTHx, sum(Measure1) per end_date=MONTHx, sum(Measure1) per modified_date=MONTHx.

    and even a generic DATE dimension hierarchy, and ability to drill down from year to months and days while showing these 3 measures.
    not sure whether it is possible or not ...
  • 3. Re: Date dimension
    901916 Explorer
    Currently Being Moderated
    Hi Alex,
    How do you determine that Date(X). Any criteria for X!! and you mean that Date1, Date2 are different columns!!!

    Regards,
    Anitha.B
  • 4. Re: Date dimension
    alex.v Newbie
    Currently Being Moderated
    in the above example DATE1, DATE2 are simply different values.

    for example a report containing all days of a month , each in its own row, and having those 3 measure computed per each day.
    01-APR-2012 sum(measure1) per start_date = 01-APR-2012, sum(measure1) per end_date= 01-APR-2012 ..
    02-APR-2012 sum(measure1) per start_date = 02-APR-2012, sum(measure1) per end_date= 02-APR-2012 ..
    ....
    30-APR-2012 sum(measure1) per start_date = 30-APR-2012, sum(measure1) per end_date= 30-APR-2012 ..
  • 5. Re: Date dimension
    901916 Explorer
    Currently Being Moderated
    01-APR-2012 sum(measure1) per start_date = 01-APR-2012, sum(measure1) per end_date= 01-APR-2012
    02-APR-2012 sum(measure1) per start_date = 02-APR-2012, sum(measure1) per end_date= 02-APR-2012

    can be simply achieved with

    date_col, filter(measure) using start_date=date_col, filter(measure) using end_date=date_col, filter(measure) using modified_date=date_col

    because date_col differs in each and every row.

    01-APR-2012, filter(measure) using start_date=01-APR-2012, filter(measure) using end_date=01-APR-2012, filter(measure) using modified_date=01-APR-2012
    02-APR-2012, filter(measure) using start_date=02-APR-2012, filter(measure) using end_date=02-APR-2012, filter(measure) using modified_date=02-APR-2012

    How do you say it's not possible.....
  • 6. Re: Date dimension
    alex.v Newbie
    Currently Being Moderated
    but how to define the relation between Date dimension and Fact table on both physical and business model layers ?
  • 7. Re: Date dimension
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I'm not sure about your fact table and columns, but based on this requirements you should have 3 date wids in fact table. like
    START_DT_WID
    END_DT_WID
    UPDATED_DT_WID -->modified date

    Take 3 aliases of Fact table and join with 3 date wids with date.row_wid
    like the below fact tables fact1,fact2, fact3 are alias of fact
    fact1.START_DT_WID=date.row_wid
    fact2.END_DT_WID=date.row_wid
    fact3.UPDATED_DT_WID=date.row_wid

    create measure based on 3 fact sources.

    If helps pls mark let me know updates if required send me email
  • 8. Re: Date dimension
    alex.v Newbie
    Currently Being Moderated
    Thank you, Srini,tomorrow I'll test and follow up
  • 9. Re: Date dimension
    901916 Explorer
    Currently Being Moderated
    Srini, correct me if I am wrong.

    If we are creating 3 alias for the fact table, then the design will be like

    date_dim--->fact1
    date_dim--->fact2
    date_dim--->fact3

    date_dim will be the confirmed dimension. If we select some column from fact1 and fact2 alone, Will it be feasible? I faced some performance issues previously

    or creating 3 date_dim alias will be feasible!!!

    fact<--date_dim1
    fact<--date_dim2
    fact<--date_dim3

    I am using the second approach in one of my reports.

    which design might be effective!!!

    Thanks,
    Anitha.B
  • 10. Re: Date dimension
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I would say different approaches..
    If you create 3 different alias for dates then you would end up with 3 different dates in presentation layer
    like create date-->Create year, created quarter etc
    Modified date-->Modified year, Modified quarter etc

    if you use a date dim and with 3 alias for fact
    you can use a particular date how many are modified metrics, how many are end date metrics and how many are created metrics etc

    the same you may not get using other approach..

    the way of looking angle for metric is matters here..

    I'm not sure if it helps or not.. do send me a test email for further discussions if needed :)
  • 11. Re: Date dimension
    alex.v Newbie
    Currently Being Moderated
    Srini, you are correct.
    I've made the mistake to previously mark your answers as Helpfull (and I cannot modify it), reply something so I can mark it as Correct
  • 12. Re: Date dimension
    alex.v Newbie
    Currently Being Moderated
    .
  • 13. Re: Date dimension
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Its okay. Suggestion worked for you and your follow up is more than enough.
    That is more than 'correct' for me :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points