Hi Gurus,
Need your urgent help.
We are converting all the thousands of OBIEE reports to Power BI. Came accross an OBIEE report where the SQL that is running in the background is given below:
---- SRC_F_NUM_DAYS_DETAIL Query ----
select distinct ( TRUNC( nvl(T639572.PLCMNT_END_DT , ( SYSDATE + -1 )) ) - TRUNC( T639572.PLCMNT_START_DT ) ) as DAYS_IN_PLCMNT,
case when T639572.STAGE_PROGRESSED = 'FPR' then 'ONG' when T639572.STAGE_TYPE = 'FPR' then 'ONG' when T639572.STAGE_PROGRESSED not in ('FPR') and T639572.STAGE_TYPE = 'INV' then 'INV' when T639572.STAGE_TYPE = 'INV' and T639572.STAGE_PROGRESSED is null then 'INV' end as SR_PLCMNT_STG_TYP,
T639572.CASE_ID as CASE_ID,
T639572.STATUS as APPROVAL_STATUS,
T639572.SECONDARY_ID as SECONDARY_ID,
T639572.PRIMARY_ID as PRIMARY_ID,
T639572.PLCMNT_START_DT as PRIMARY_ID,
T639572.PLCMNT_END_DT as PLCMNT_END_DT,
T639572.CHILD_ID as CHILD_ID,
T463078.COUNTY as COUNTY,
T463078.REGION as REGION,
T469163.NM_STAGE as STAGE_NAME,
T463190.NBR_UNIT as UNIT,
T639617.NM_PERSON_FULL as CASEMGR,
T639897.NM_PERSON_FULL as CHILD_NAME,
T639967.DECODE as PRIMARY_RELNSHIP,
T639757.NM_PERSON_FULL as PRIMARY_NAME,
T639978.DECODE as SECONDARY_RELNSHIP,
T639827.NM_PERSON_FULL as SECONDARY_NAME,
T639687.NM_PERSON_FULL as SUPERVISOR,
case when T658103.CODE = 'FPR' then 'ONG' else T658103.CODE end as CURRENT_STAGE_TYPE,
T463078.CNTY_CODE as COUNTY_CODE,
T463078.REGION_NUM as REGION_NUM
from
shinesdm.W_CODE_TABLES_D T658103 /* W_SAFETY_RESOURCE_CODES_D */ ,
shinesdm.W_PERSON_D T639687 /* W_VKC_SUPERVISOR_D */ ,
(
(
(
(
shinesdm.W_GEO_D T463078 inner join (
shinesdm.W_UNIT_D T463190 inner join (
shinesdm.W_STAGE_NAME_D T469163 inner join (
shinesdm.W_PERSON_D T639897 /* W_VKC_CHILD_D */ inner join (
shinesdm.W_PERSON_D T639617 /* W_VKC_CASE_MANAGER_D */ inner join shinesdm.W_VKC_F T639572 On T639572.CASE_MANAGER_ID_KEY = T639617.PERSON_KEY) On T639572.CHILD_NM_KEY = T639897.PERSON_KEY) On T469163.ID_STAGE = T639572.STAGE_ID) On T463190.UNIT_KEY = T639572.UNIT_KEY) On T463078.GEO_KEY = T639572.GEO_KEY) left outer join
shinesdm.W_CODE_TABLES_D T639967 /* W_VKC_PRIMARY_REL_D */ On T639572.PRIMARY_REL_KEY = T639967.CODE_KEY) left outer join
shinesdm.W_PERSON_D T639757 /* W_VKC_PRIMARY_VKC_D */ On T639572.PRIMARY_VKC_NM_KEY = T639757.PERSON_KEY) left outer join
shinesdm.W_CODE_TABLES_D T639978 /* W_VKC_SECONDARY_REL_D */ On T639572.SECONDARY_REL_KEY = T639978.CODE_KEY) left outer join
shinesdm.W_PERSON_D T639827 /* W_VKC_SECONDARY_VKC_D */ On T639572.SECONDARY_VKC_NM_KEY = T639827.PERSON_KEY
where ( T639572.STAGE_TYPE = T658103.CODE and T463078.ACTIVE_FLG = 'Y' and T463190.ACTIVE_FLG = 'Y' and T639572.SUPERVISOR_ID_KEY = T639687.PERSON_KEY and (T639572.PLCMNT_END_DT >= TO_DATE('2023-09-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') or T639572.PLCMNT_END_DT is null) and T639572.PLCMNT_START_DT <= TO_DATE('2023-09-30 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )
-------
There is no Join with the W_DAY_D DIM Table and the main Fact table W_VKC_F. The DAY is only coming for clause written in Red.
But NOT SURE HOW TO PASS THIS CONDITION in the Power BI REPORT. The report is Based on the Query SRC_F_NUM_DAYS_DETAIL in the PBIX file which is attached.
Hence, the numbers of the OBIEE Detail Report and the Power BI Detail Report DOES NOT match and unacceptable.
Please suggest how to achieve this.
