Data import takes long time from azure SQL Data Warehouse

[pavann86]

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.

[KGrice]

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.

[pavann86]

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.

[KGrice]

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?

[pavann86]

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.

[KGrice · 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.