Loading data from SQL database for multiple years best practice

[jpt1228]

Hello, I have been building a data model for the past 6 months from our ERP database. I am importing the views in the DB. I have an invoice table, inventory table, sales order table etc.

I pull data in from 2014-Current every time the auto update runs, I have been noticing people that are working on the same server are experiencing a slowdown in the way they can interact with the ERP. I suspect it is because of pulling in all that data from 2014-now every time.

What is the best practice to pull in historical data and only refresh the most current data or pull in new data and add to data model? From what I have read power BI doesn't support inceimental data loads. I was thinking of pulling in the data from the previous years and then just merging with the current year data using the advanced SQL option. In theory this makes sense but I can't find a good description if I should be using the data load and include in report refresh options to avoid loading in the historical data.

I have been creating dummy tables and experimenting but wanted to reach out to the smart folks here for their take. I'm still very new to PBI and DB building so try to keep the technical jargon to a minimum.

Thanks

[SivaMani · Accepted solution]

@jpt1228,

Hope you're using import connection.

Have two queries,

1. Up to 2016

2. After 2016

Then uncheck the Enable Load option for the 1st query.

Hope it will help you!

[jpt1228]

Simple enough. I'll give it a try