cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pavann86
Frequent Visitor

Data import takes long time from azure SQL Data Warehouse

I am Using Power bi desktop and importing data from azure sql data warehouse and finally publishing to cloud service and schedule the refresh. Now the data is populating 10 crores in warehouse. Some changes are flowing in the table structure for new columns. So at that time we are refresh the entire power bi file, it's taking too much time because of this we cannot make any changes to reports. Any one please suggest how to handle this type of situation. Same case going forward with huge data with structural changes in powerbi desktop.

1 ACCEPTED SOLUTION

Ok, I think I get the problem now. The refresh is fine in the cloud, but you can't refresh it in desktop to bring in the new columns and create additional charts/graphs.

 

Can you temporarily limit the row count of the actual source table in Azure? I'm not sure how much control you have over the table there, or what feeds it. If you can temporarily remove rows (e.g., move them to another table, or prevent x rows from populating), you could then update your report to get the new columns and create your new visuals. Once everything is set in the report and it's uploaded to Power BI for data refresh, you could set your source table back to include all rows.

 

Ideally you could save changes to your query without reloading the entire data set, but I can't find a way to do that. It would be nice to just put a row limit on your table in the query editor, bring in the new columns and create the visuals, and then take the row limit off without bringing in the whole set.

View solution in original post

5 REPLIES 5
KGrice
Solution Sage
Solution Sage

Are you pulling in more data than you need for your model, as opposed to pulling in all data in the tables? If you don't need every column, you can select the ones that are pertinent to your reporting and ignore the rest. Are the new columns that are coming in wanted, or are you looking for a way to keep them out of the model?

 

If you can filter out any rows that aren't needed, e.g., dates that aren't relevant, that will also help.

 

Basic I know, but that's where I'd start.

Thanks for your reply,

 

We have total columns 12 and 3 new columns came. we want to get that columns into model only option is refresh.  At that time of refresh we have 60 millions of data. we are not getting any solution without refresh getting the columns  into model for creation of new charts based on 3 newly added columns.

Is your data static except for the three new columns? Otherwise, I don't see the reports being very helpful with or without the new columns. If you're already having issues refreshing the data as is, adding three columns isn't the problem so much as getting the data to refresh at all.

 

You should be able to see something coming in through the preview in the Query Editor when you try to refresh. Depending on the cardinality of your data (each column has a couple possible values vs each column having a unique value for all 60 rows), 60 million rows could be difficult or easy to bring in. Maybe someone else that works with larger datasets more frequently can offer some additional pointers, but it sounds like you'll have to limit your dataset in some way to get a usable report.

 

Do you need all 15 columns and all 60 million rows? If you do, is there a meaningful way to break it up by subject matter into separate reports, which can be combined in a dashboard view?

My Data was not Static, it's coming from one table(i.e Denormalized) from AZURE DW. Data will increase on daily. As you Suggested about filter the data, but I dont want to place any filter on the data. In Desktop in preview mode the newly columns showing with data of limited rows but when I click on "Close & Apply" button it's trying to load the entire data. How we can control without loading 60 millions of records with these new columns. In desktop it's taking huge time for loading 60 million reocrds. once we published the pbix in cloud service, 60 million records loading very faster.

Ok, I think I get the problem now. The refresh is fine in the cloud, but you can't refresh it in desktop to bring in the new columns and create additional charts/graphs.

 

Can you temporarily limit the row count of the actual source table in Azure? I'm not sure how much control you have over the table there, or what feeds it. If you can temporarily remove rows (e.g., move them to another table, or prevent x rows from populating), you could then update your report to get the new columns and create your new visuals. Once everything is set in the report and it's uploaded to Power BI for data refresh, you could set your source table back to include all rows.

 

Ideally you could save changes to your query without reloading the entire data set, but I can't find a way to do that. It would be nice to just put a row limit on your table in the query editor, bring in the new columns and create the visuals, and then take the row limit off without bringing in the whole set.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors