To enforce the it you would have to have a global where clause in the Logical table source....
Forget the CASE, you don't need it ....
"Keep it simple" : add a filter in your analysis with SHIP_TO = BILL_TO, convert your filter to SQL so that you can write that kind of filter.
Of course if the requirement is not for just a single analysis but always do what Thomas posted.
If I go with WHERE CLAUSE, all the existing reports/dashboard will have a new query with where class modified. I should not disturb the existing system and my application is vast. If this is identified in regression testing then I would be in the position to answer multiple questions and for sure data will change.
More over main part is, this should happen only if I add PRIMARY_SHIP_TO_NAME column in the analysis.
SHIP_TO BILL_TO SHIP_TO_NAME
10001 10001 Minneapolis Minneapolis
10001 10001 Minneapolis
if I remove the same the analysis should return actual data
SHIP_TO BILL_TO SHIP_TO_NAME 10001 10005 Minneapolis 10001 10004 Minneapolis 10001 10003 Minneapolis 10001 10002 Minneapolis 10001 10001 Minneapolis
SHIP_TO BILL_TO 10001 10005 10001 10004 10001 10003 10001 10002 10001 10001
I have seen in some sites that we can write a SELECT statement in BMM layer, i have tried it but didn't worked for me. It looks like 'SELECT' and 'WHERE' are not the keywords.
Please suggest if we can achieve this with any other option.
Thanks much, Helan
I agree Gianni, but I need it in RPD level
Hence the beauty of aliases, multiple logical table sources and multiple logical tables ... so you can do it, basically you are going to create a new logical star and this one has the where clause. Use of information drives model design!
IMO, you've crossed the line into ETL work here. A DW or datamart should already calculate some sort of indicator that you could use to pull the proper rows of data and exclude others. I'm 100% sure this COULD be done in the RPD, but thinking that it SHOULDN'T be.
Agreed ... push any and all work back to DB if you can.
As a tentative solution I have requested user's to apply not null filter on this column while creating ad hoc analysis. I have to take care ETL work too and I'm not good at it hence trying to fix it in OBIEE