I am currently designing a report in Power BI -> Version: 2.64.5285.582 64-bit (November, 2018) pointing to Oracle SQL Developer -> Version 126.96.36.199.
Initially, I brought in limited data into my Power BI report for the development and testing purposes post which I removed the filters in the SQL Where clause to get in all the data into the report.
On refresh, I received the below error.
The exception unknown software exception (0xe06d7363) occurred in the application at location 0xfcd7bded.
Above error was received twice in 2 attempts to refresh the data.
The base table has around 138 million rows of data which is further joined/merged with other intermediate tables to ceate a dataset used for the report.
I checked with the database technical architect as well and they asked me to raise a support ticket as this was new to them.
Please assist with this.
@vishy86 ,Please take the following actions and check if you get errors.1. Update to latest version of Power BI Desktop(2.66.5376.2161).2. Import limited and necessary columns and rows of Oracle table by adding filters in the SQL Where clause and then load data to Power BI Desktop.3. Disable "Enable load" for intermediate tables in Power BI Desktop query editor.Regards,Lydia
Thanks for the revert and inputs.
Please see my comments in bold.
1. Update to latest version of Power BI Desktop(2.66.5376.2161) - Will update to the latest version as mentioned.
2. Import limited and necessary columns and rows of Oracle table by adding filters in the SQL Where clause and then load data to Power BI Desktop. - Is this to be done even after the above update? If yes, is there any advisable optimal size limit for the dataset imported into Power BI file?
3. Disable "Enable load" for intermediate tables in Power BI Desktop query editor. - I had disbaled the Enable Load option for the intermediate tables in Power BI Desktop query editor in my previous refresh runs.
I would like to know more about point #2 and size of datasets imported into Power BI. Please let me know.
@vishy86 ,You would need to put Oracle statement in the highlighted textbox when loading data to Power BI Desktop. There is no specific size limit for the dataset in Power BI Desktop, but you can take a look at this article to check optimize your report.Regards,Lydia
I am already using an Oracle SQL Query, as you showed above, to import data in Power BI Desktop.
Earlier, I had a WHERE condition which had a filter to limit the data I bring in, but that was just for the development and testing purposes.
Now, I have removed the WHERE condition in my Oracle SQL query and I am trying to import the dataset into Power BI Desktop which is when I encountered the mentioned error.
I have upgraded the Power BI version to Power BI Desktop 2.66.5376.1681, which is the latest available version in my organization software center.
Also, I have disbaled "Enable Load" for all the intermediate tables.
My main concern was with point #2 which you mentioned. Is there a specific concern of importing complete dataset into Power BI Desktop without having a WHERE condition. I am only using columns that I need for the report. Please advise on this.
@vishy86 ,In terms of improving performance of Power BI report, it is recommended to import required columns and rows from data source. Do need all the rows and columns from the base table which has 138 million rows of data ? You then merge the base table with other tables, which will put heavy load on Power BI Desktop and impact the performance of the report.Reagrds,Lydia
Yes, I need all the rows to be imported into the Power BI dataset as I do not have any specifications from business to exclude any rows.
I refreshed the data and after around 3-4 hours, all the data did get pulled in into the dataset.
I guess the update to the latest possible version helped. Thanks.
I'll keep a tab on the performance in subsequent refreshes and update here on how it goes.