cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Improving Data Transfer Speed in ETL Process

We are using POWERBI in our organization. WE are having data around 400GB in our server. The issue is that it will take around a day to refresh all the dashboards which will be used to followup and monitor daily achievements. Is there any method to speedup the refresh process? Please provide your suggestions which are highly aprriciated by us.

9 REPLIES 9
AndyPeng
Frequent Visitor

To improve the performance of importing large dataset from Oracle Database to Power BI, the key is adjusting “FetchSize” parameter in the connection string. Unfortunately there is no way to do that in the native Oracle connector in Power BI.

 

Luckily, you can adjust the connection string in OleDb connector. In my case I got 5 times performance improvement just by setting “FetchSize” to 5000.

Check the link below.

https://www.thebiccountant.com/2021/01/12/your-oracle-data-import-in-power-bi-and-power-query-is-slo... 

josef78
Impactful Individual
Impactful Individual

If you are talking abou Report Server (on-premise)...

1) Which is you source of data? MSSQL database or some else? E.g. for some sources are slow (including Oracle), and is better load data first to MSSQL db.

2) check where is problem, if in serving data from source, network transfer, processing (columns commpresion) or saving to catalog. You optimize or speed up problematic part.

3) check if the problem is generic, or report specific, problem can be in model desing within report.

4) try better way for enterprise deploymet, for large models use dedicated SSAS Tabular mode, use incremental load and additional processing options and benefits, and use Power BI only as visualisation tools...

Anonymous
Not applicable

Hi,

Thanks for your help. We are using ORACLE DBs.

josef78
Impactful Individual
Impactful Individual

Connecting to Oracle DB for large tables is very complicated, and is hated from Oracle side.

Performance is very dependent on type and version of Oracle Client.


Oracle ODP.NET (which is used in "Oracle database" connector in Power BI) and ODBC connectors are very slow, independent on version, and are unusable for large tables.

 

Little better is it with Oracle OLEDB provider.
In default configuration are slow same as above, but with some registry (or connection string) settings of FetchSize, you can gain significant performance boost (about 3x to 5x depends on version) if change default value 100 to value 1000 or more (higher values bring only minimal boost, but make makes another troubles in case many parallely loading). It is big improvement, but best what you gain is still much slower then competition solution.

 

Another complication is version, Oracle OLEDB working good only in 12.1 version of client, but this version is now unsupported. In version 12.2 (and also 18.3) Oracle implemented memory bug, which for transfer of large table consume extreme large amount of memory (connector consume aprox 4GB of RAM, for import 400MB table). In version 19.3 Oracle implement another bug, also for large tables, when every table during transfer is rapidly slowing down (aprox to half during every 15minutes).

 

Currently best solution for large Oracle tables is use 3rd party connector (e.g. https://www.progress.com/odbc/oracle-database ) which is much better then Oracle OLEDB. Or use two step extract, first extract Oracle data to MSSQL using SSIS with Microsoft provided Oracle connector https://www.microsoft.com/en-us/download/details.aspx?id=58228 ) and then load to Power BI from MSSQL (although in two step, is still faster then Oracle OLEDB).

lbendlin
Super User
Super User

needs more details. Which db? does it support bulk operations? Does the destination have indexes? if yes, are you dropping them before import?

Anonymous
Not applicable

DB type : Oracle
Yes
No indexing

lbendlin
Super User
Super User

Use CSV data sources wherever possible.  (Dataflows are basically bunches of CSV files, too, so they are a good second best choice)

Anonymous
Not applicable

Thank you for extending your help to us. Currently we are acrrying out DB to DB data transfer. Any suggestions on how to improve the dta transfer speed from DB to DB??

Can you please explain a bit more?

Are you trying to refresh data from Oracle to Power BI? or you are trying to data refresh first from Oracle to SQL Server then to Power BI?

Is it necessary to import all the data (400GB) in Power BI? Since data refresh takes too much time you can try out direct query?

 

https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors