Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |