13 Replies Latest reply: Jan 23, 2013 8:46 AM by Srini VEERAVALLI RSS

    Date dimension

    Alex.V-Oracle
      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
          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-Oracle
            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
              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-Oracle
                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
                  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-Oracle
                    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
                      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-Oracle
                        Thank you, Srini,tomorrow I'll test and follow up
                        • 9. Re: Date dimension
                          901916
                          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
                            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-Oracle
                              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-Oracle
                                .
                                • 13. Re: Date dimension
                                  Srini VEERAVALLI
                                  Its okay. Suggestion worked for you and your follow up is more than enough.
                                  That is more than 'correct' for me :)