Incremental update for SQL Server Data Sources

Question

Hi all,

Using nightly sql job(aggregate queries on prod(OLTP) db of size 500 gb approx.) we are creating a Aggregate Database (summary data of 30 gb approx. - read only)  daily and this process is taking longer time..

We are trying to optimize this process. We would like to know the best solutions applied in similar scenario

Is there any efficient way to process only the daily updates of OLTP Prod DB to the aggregate Database??

 

Answer 1

Do you need to aggregate daily data or the overall data meeting specific criteria? If you want to do aggregation only on the incremental data, then try creating materialized view out of that specific day's operation which contains the logic of aggregation and then push that data into the warehouse accordingly.

Let me know if you have any further questions.

regards,

---

Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 2

Phani,

Thanks for your response.

We require aggregates on overall data. I apologize for using the term Incremental update..

The goal is to get the aggrgate on overall data of previous day. We got more than 2000 tables on prob db and aggregate database has 70tables(dropped and created everyday as of now) needs to accomodate the aggregates of overall data until the previous day..

Dashboard charts hits the aggregate database

What would the efficient method/process to create/update(if possible) the aggregate database?

Again thanks for answering the question

 

Answer 3

Hi Krish,

Will there be any changes to historical data while we keep loading the incremental data. Idea is, if there is a point where we can say that the data wont change, then have those aggregates as fixed ones and then keep adding the incremental data to that fixed one where you already have the averaged values till that point. To elaborate further,

For ex: If you have data aggregated for 3 months which will not change further, have this stored as materialized view or some way and then the 4th month data would be averaged on top of this object alone instead of aggregating all 4 months data again.

Hope I could explain what I meant. Let me know if you have any questions further.

Regards,

 

---

Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 4

Phani,

Thanks for your thoughtful response. I have taken your response to my team. It is possible but not straight forward though.

The source db is a enterprise level transactional database. So we have identify the objects where the history records will not be updated through the application interface and implement the incremental aggregate as mentioned by you.

Thank you