How to reduce the time for loading a data from SQL server.

[Satish_Kumar]

Hi All,

I am here again with a most common and pathetic issue in Power BI,

In my case i almost find my self sleepy while i tried to load any table from my SQL server which is having rows in Lakhs.

the minimum time for one table is around 45mintues,

Please if anyone have some workaround regarding to this issue i will the most happiest person an very much thankfull.

for refernce here is the screenshot.....

Best Regards,

Satish Kumar

[Satish_Kumar]

Hi All,

Its almost 24 hours to my post, But till now have not receive any answer from our community.

Regards,

Satish Kumar

[hemantsingh]

Hi @Satish_Kumar,

    How many calculated columns you have created in your model to which you are trying to feed the data from sql. Also, when you are loading data from sql server than i advice you to use direct query mode instead of import while loading data in model.

Direct query mode is really very good & is tightly integrated with power BI.

Regards,

Hemant

[Satish_Kumar]

@hemantsingh

i have done with direct query mode.

but takes as much time as i am importing through ODBC.

if i do not have any calculated column and try import simply then the importing time will be minimus of 45 min. 

Regards,

Satish Kumar

[hemantsingh]

@Satish_Kumar

  how many lines you are importing in your model collectively i.e aggregation of total no. of line in each table you are tryng to import in your model for analysis from sql server.???

   I am working with sql server too & have imported more than 10 million lines of data in 6 tables collectively in my model. total time it took was arround 15-20 minutes.

I suspect you have lots of unique values in every column,something simillar to primary key. If this is the case than reducing time for import will be very difficult as power BI internally woks on in memory columnstore technology. Lots of unique vale means lots of compression & hence increased time for data load.

 Hope this helps.

Regards

[Satish_Kumar]

@hemantsingh

See this cases will come when i will import the data.

let suppose if i am able to import them any how but how should i applied the filter on the table if we do not have any permission for doing that.

So if there is any other workaround please do revert.

And i am importing data about 72lakhs of rows.

Regards,

Satish 

[hemantsingh]

@Satish_Kumar

who said data cannot be filtered ???

You can filter data in edit query mode after importing data & also use dax funtion "filter" to filter out things as per your requirement.

Regards

[hemantsingh]

@Satish_Kumar

who said data cannot be filtered ???

You can filter data in edit query mode after importing data & also use dax funtion "filter" to filter out things as per your requirement.

Regards

[Satish_Kumar]

@hemantsingh

 no buddy said that i am bounded with certain condition.

Regards 

Satish

[hemantsingh]

@Satish_Kumar

  DAX is your best friend then.

Regards

[Satish_Kumar]

@hemantsingh

How DAX help me in importing the data.

Please will giude me a little.

Regards,

Satish

[hemantsingh]

DAX Won't help you in importing. It will certainly help you in filtering the data as per your will. You can refer to this link for further details on filtering of data using DAX.

https://www.sqlbi.com/articles/from-sql-to-dax-filtering-data/

Regards