OBIEE report error.

Question

Hello community,

I would like you all to look at this error and suggest a possible solution.

We are trying to run a report in OBIEE 11g with oracle as its datasource. The report has been migrated from Teradata datasource to Oracle datasource because of client's requirement. Now, while running a criteria which requires no of accounts corresponding to the states with two filters applied (refer attachments), report is not giving any results (refer attachments). There is some problem with the filters. I am sharing the logical sql and the physical sql. Please suggest how to solve this report problem. I am also sharing the log file as well.

Logical SQL

SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/EDW Revenue Assurance/Dual Service - Active Electric with Inactive Gas Account';SELECT s_0, s_1, s_2, s_3 FROM (

SELECT 0 s_0, CASE WHEN "EDW_RA_POC"."Dim - Account"."State"='MA' THEN 'EMA' ELSE "EDW_RA_POC"."Dim - Account"."State" END s_1,

COUNT(DISTINCT "EDW_RA_POC"."Fact - DS_Electric_No_Gas"."Electric Service Account Id") s_2,

REPORT_AGGREGATE(COUNT(DISTINCT "EDW_RA_POC"."Fact - DS_Electric_No_Gas"."Electric Service Account Id") BY ) s_3

FROM "EDW_RA_POC"

WHERE(("Dim - Date"."Calendar Date" IN (date '2015-08-31')) AND ("Fact - DS Electric No Gas"."Extract Dt and Srv Start Dt Diff" - (TIMESTAMPDIFF(SQL_TSI_DAY, DATE '2015-08-31', DATE '2015-08-31')) > 90))

) djm ORDER BY 1, 2 ASC NULLS LAST

Actual Physical Log

select count(distinct T173903.GAS_SRVC_ACCT_ID) as c1,

case  when T173203.TERR_CD = 'MA' then 'EMA' else T173203.TERR_CD end  as c2

from

D_ACC_BI.BI_CALENDAR_VIEW T173508 /* Dim_Date_Dim */ ,

D_ACC_BI.BI_SACC_BACC_VIEW T173203 /* Dim_Account_Dim */ ,

D_ACC_BI.BI_DS_GAS_NO_ELECTRIC T173903 /* Fact_DS_Gas_No_Electric */

where  ( T173203.SRVC_ACCT_ID = T173903.GAS_SRVC_ACCT_ID and T173508.CALENDAR_DATE = TO_DATE('2015-08-31' , 'YYYY-MM-DD') and 90 < T173903.EXDT_STDT_DATE_DIFF - ( TRUNC( TO_DATE('2015-08-31' , 'YYYY-MM-DD') ) - TRUNC( TO_DATE('2015-08-31' , 'YYYY-MM-DD') ) ) and T173508.CALENDAR_DATE >= T173903.GAS_SA_STRT_DT and T173903.GAS_SA_END_DT >= T173508.CALENDAR_DATE and T173508.CALENDAR_DATE between T173903.GAS_LA_STRT_DT and T173903.GAS_LA_END_DT )

group by case  when T173203.TERR_CD = 'MA' then 'EMA' else T173203.TERR_CD end

order by c2

Rajesh Gurram - BI · Answer

Since BI is creating two sqls but the join coditions between BI_DS_ELECTRIC_NO_GAS and BI_CALENDAR_VIEW are different, you may want to run the sql using any client tool like Toad and identify the correct join and then make sure you use the same join format in physical join between these two tables.

3013484 · Answer

where  ( T173203.SRVC_ACCT_ID = T173903.GAS_SRVC_ACCT_ID and T173508.CALENDAR_DATE = TO_DATE('2015-08-31' , 'YYYY-MM-DD') and 90 < T173903.EXDT_STDT_DATE_DIFF - ( TRUNC( TO_DATE('2015-08-31' , 'YYYY-MM-DD') ) - TRUNC( TO_DATE('2015-08-31' , 'YYYY-MM-DD') ) ) and T173508.CALENDAR_DATE >= T173903.GAS_SA_STRT_DT and T173903.GAS_SA_END_DT >= T173508.CALENDAR_DATE and T173508.CALENDAR_DATE between T173903.GAS_LA_STRT_DT and T173903.GAS_LA_END_DT.

This query is what we want to use, do you suggest changes in this query?@Rajesh Gurram - BI

Rajesh Gurram - BI · Answer

I see that there are two physical queries fired for this report and the difference I see in the two queries have different LA/SA start and end date filters that you may want to verify?