Importing Data from Oracle Database via advanced editor results in Date offset

[mcclurej]

I have a specific query built and stored in Oracle as a view:

SELECT
	TEAM_ASSIGNED
,	REPORT_YEAR
,	REPORT_MONTH
,	REPORT_MONTH_NUM
,	REPORT_DAY
,	REPORT_DAY_NUM
,	REPORT_DATE
,	REPORT_PERIOD_FLAG
,	INCOMING
,	TRIAGE_AND_TRANSFERRED
,	RESOLVED
,	BALANCE
FROM SCHEMA_NAME.VW_CRM_INCIDENTS_HISTORY
WHERE 1=1
AND REPORT_DATE = '01/NOV/20'

The data types are:

TEAM_ASSIGNED	        VARCHAR2(256)
REPORT_YEAR	        VARCHAR2(4)
REPORT_MONTH	        VARCHAR2(3)
REPORT_MONTH_NUM	VARCHAR2(2)
REPORT_DAY	        VARCHAR2(9)
REPORT_DAY_NUM	        VARCHAR2(2)
REPORT_DATE	        DATE
REPORT_PERIOD_FLAG	CHAR(14)
INCOMING	        NUMBER
TRIAGE_AND_TRANSFERRED	NUMBER
RESOLVED	        NUMBER
BALANCE	                NUMBER

When I run the above query in Oracle, I get zero results (as expected). However, when I run the query in Power BI I get several thousand rows!
Removing the date filter shows that all REPORT_DATE dates have had a -1 day offset to them. What's even stranger is that the REPORT_DAY value shows as SUNDAY even though there are absolutely no sundays in the dataset. The below function is what is being used to create the REPORT_DAY field:

    , CASE WHEN TO_CHAR(TRANSACTION_DATE,'D') = 6 
            THEN TO_CHAR(TRANSACTION_DATE + 2,'DAY') 
            ELSE 
      CASE WHEN TO_CHAR(TRANSACTION_DATE,'D') = 7 
            THEN TO_CHAR(TRANSACTION_DATE + 1,'DAY') 
            ELSE 
      CASE WHEN TO_CHAR(TRANSACTION_DATE,'HH24') >= 17 
            AND TO_CHAR(TRANSACTION_DATE + 1,'D') = 6 
            THEN TO_CHAR(TRANSACTION_DATE + 3,'DAY') 
            ELSE 
      CASE WHEN TO_CHAR(TRANSACTION_DATE,'HH24') >= 17 
            THEN TO_CHAR(TRANSACTION_DATE + 1,'DAY') 
            ELSE TO_CHAR(TRANSACTION_DATE,'DAY') 
      END END END END AS REPORT_DAY 

You can see we are explicitly moving the REPORT_DAY forward to Monday where it is Saturday, or Sunday, or Friday after 5pm.

My suspicion is that Power BI is somehow parsing the entire VIEW in Power BI prior to sending it to the Oracle Query analyser to run. Thus Power BI's internal timezone and date values (e.g. Power BI's NLS) are being used instead of the Oracle server for which the query is supposed to run.

Is this true, or is there another explanation, and does anyone have a method of resolving this issue?

PS: I should mention the REPORT_DATE function is as follows:

    , CASE WHEN TO_CHAR(TRANSACTION_DATE,'D') = 6 
            THEN TRUNC(TRANSACTION_DATE + 2) 
            ELSE 
      CASE WHEN TO_CHAR(TRANSACTION_DATE,'D') = 7 
            THEN TRUNC(TRANSACTION_DATE + 1) 
            ELSE 
      CASE WHEN TO_CHAR(TRANSACTION_DATE,'HH24') >= 17 
            AND TO_CHAR(TRANSACTION_DATE + 1,'D') = 6 
            THEN TRUNC(TRANSACTION_DATE + 3) 
            ELSE 
      CASE WHEN TO_CHAR(TRANSACTION_DATE,'HH24') >= 17 
            THEN TRUNC(TRANSACTION_DATE + 1) 
            ELSE TRUNC(TRANSACTION_DATE) 
      END END END END AS REPORT_DATE 

UPDATE:
I've narrowed the problem to the 'D' declaration of the TO_CHAR function:

[mcclurej · Accepted solution]

So work around is to remove all references to TO_CHAR([DATE],'D') and instead use TO_CHAR([DATE],'DY') I can only assume this is because the Power BI servers start their weeks on Sunday instead of Monday, and therefore all Day numbers start one day sooner. Fortunately, the Day names stay the same, so you can use the DY function to look for MON to start your week.