Your SQL can't be used in the RPD just because if you use it you are making the whole concept of RPD useless (and paying an OBIEE license when SQL Developer could do the same for free).
Yours is probably the most simple model you can have: dw_project_d is conformed dimension and dw_plan_time and dw_pa_hrs are 2 fact tables (can also be included in the same fact logical table using 2 logical table source).
Set SUM as aggregation and you are done ...
Please see the below . I think the same SQL logic is already implemented right? so whatever hours we pick from dw_plan_time and dw_pa_hrs tables satisfy that SQL condition ?
Am I missing something ?
1 person found this helpful
Your model looks just fine. Both facts join to your dimension table so now you can put aggregation rules on the measures and then analyze them by dimensional attributes.
Proper execution is obviously also the creation of a dimensional hierarchy for the logical dimension itself and the subsequent setting of content levels for all sources.
Thanks Christian & Gianni,
One more question similar to this.. There is another SQl logic which needs to be implemented in OBIEE. How can the below SQL be transformed..
FROM XXESC_PA_TIMECARDS T1
where t.PROJECT_NAME = t1.PROJECT_NAME
and t.TASK_NAME = t1.TASK_NAME
and t.EMPLOYEE_NAME = t1.EMPLOYEE_NAME
GROUP BY t1.PROJECT_NAME, t1.TASK_NAME, t1.EMPLOYEE_NAME) Total_Actual,
FROM XXESC_FORECAST_TIME F1
where t.PROJECT_NAME = F1.PROJECT_NAME
and t.TASK_NAME = F1.TASK_NAME
and t.EMPLOYEE_NAME = F1.EMPLOYEE_NAME) Total_Forecast
FROM XXESC_PA_TIMECARDS T
GROUP BY PROJECT_NAME, TASK_NAME, EMPLOYEE_NAME, ENDING_DATE, HRS
In this SQL I am clear on how to get the Actual hours..which means we need to get sum of all hours that are there in XXESC_PA_TIMECARDS table..And Total_Actual & Total_Forecast hours we are getting them only when project name, task name & employee name are equal ? Is my understanding correct ..If so how can this be implemented.
Thanks a lot !
1 person found this helpful
Similar as before, you model your tables making the joins based on your conditions between xxesc_pa_timecards and xxesc_forecast_time, for the "Total_Actual" you can add a "by" in the formula.
I have one more doubt.I have added a new view to my existing model. When I just use the columns from that view it is giving data..But when I combine the columns of that view with the other columns from other tables the data from that view columns are becoming null.Why is this happening ?
Any suggestions please.
LTS content levels are not set correctly.
Also: a view? Yuck.
I have set up the LTS content but that is not working..View is created in OBIEE . In physical layer I am not giving any join conditions. In BMM layer I have added in LTS to my existing fact table. I tried giving join conditions in physical layer .But the joins are not making any difference.I don't know if I am missing something.
Your fact must be joined to *something* dimensional. Otherwise it's jsut a standalone table/view and even that needs to have a degenerate dimension in order for fact measures to be aggreagated along dimensional attributes
I have the fact joined to the dimension in both physical & BMM layer..Please see the below..Am I missing anything..
This is the Business diagram..
Please help me with suggestions..Thank you !!
And how does HRS figure into this phyiscally?
HRS is the OBIEE view created in physical layer and it has joins with W_PLAN_TIME & W_PA_HRS. So is the reason in BMM layer I added in LTS.
OK that looks fine bbut you havent answered my question about the content levels. Are they set for all fact sources?
Yes, I have set the content for all LTS in fact. I am having another issue now..
without adding columns from HRS view the report looks as below which is correct.
when I add the column from HRS view it is duplicating the rows..Why is this happening.Sorry Christian if I am aksing silly questions..Please don't mind.
Why this happening..
Thank you so much !