Forum Discussion
Missing data when loading a view from Oracle
After some debugging, research and several trials, I managed to find the root cause of the problem.
In the view, there is a function converting dates to day of week numbers. If using to_char(sysdate, 'D'), then the output of this function can be different according to where the query is coming from, and what NLS setting is set up in that server. So a different function should be used: TRUNC(sysdate) - TRUNC(sysdate, 'IW'), then you need to add +1 (if you want to have 1 for Monday and 7 for Sunday).
See more details here: https://stackoverflow.com/questions/14517761/using-oracle-sql-how-does-one-output-day-number-of-week-and-day-of-week/71970759#71970759
Hi smoortema,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Here’s a summary of the suggestions to fix the issue:
Check View Logic: Ensure the view doesn’t use SYSDATE or session-based filters. use fixed date filters instead.
Check User Context: Verify if the data returned differs based on the user or session by comparing the Oracle user in SQL Developer vs Power BI.
Use Native SQL Query: Replace the default loading with a custom SQL query to bypass query folding:
Source = Oracle.Database("dwh1_high", [Query="SELECT * FROM TEST2.REP_COMPLAINTS_V"])
Check Regional Settings & Data Types: Make sure dates and data types are interpreted correctly without causing silent errors.
Ignore Preview Limits: Ensure you're not relying only on the preview in Power Query (it shows a limited number of rows).
Use Query Diagnostics: Enable diagnostics in Power BI to see what query is actually sent and what Oracle returns.
If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.
Thank you & Regards,
Prasanna kumar