This content has been marked as final. Show 13 replies
Hi,1 person found this helpful
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 ...
How do you determine that Date(X). Any criteria for X!! and you mean that Date1, Date2 are different columns!!!
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.....
but how to define the relation between Date dimension and Fact table on both physical and business model layers ?
I'm not sure about your fact table and columns, but based on this requirements you should have 3 date wids in fact table. like1 person found this helpful
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
Thank you, Srini,tomorrow I'll test and follow up
Srini, correct me if I am wrong.1 person found this helpful
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..1 person found this helpful
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 :)
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
Its okay. Suggestion worked for you and your follow up is more than enough.
That is more than 'correct' for me :)