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 ?
Edited by: alex.v on Jan 22, 2013 10:11 AM
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!
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.
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 ...
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 ..
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.....
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
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
create measure based on 3 fact sources.
If helps pls mark let me know updates if required send me email
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 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!!!
I am using the second approach in one of my reports.
which design might be effective!!!
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 :)