June 26, 2013 at 3:44 pm
Hi there!
Creating an alert dashboard that updates every 15 mins as a front end. As the database back end
I have a task of reading 8 different sources (different locations) but with the same exact type of data into a staging table every 15 mins and then scraping that staging table and only load the records that create 'alerts' (within my threshold values) into a Notification table.
My goal is to keep the tables very light so we don't have to go through so many records everytime. The staging table will get unloaded on a daily basis into a historical table. My plan is to flag those records that have been already scraped on the previous run (15 mins ago) with a certain value on a "Flag" field that I will create.
Next time (15 mins later) I have to load more records into this table from the source, I will only read those records that have flag = 0 and update notification table with those that have not been already added there.
what would be the best approach to accomplish that utilizing SSIS. Any ideas that could make this better and/or faster?
The staging and notification tables will have no more than 2-3 tables and each table no more than 10 fields.
Thanks!
R.
June 26, 2013 at 10:10 pm
Does that mean that you wish to read only those rows which have been inserted or updated in 'the last fifteen minutes' every time the job runs?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 27, 2013 at 9:47 am
Yes, I basically would like to just read those NEW records that have recently been inserted into the "staging" database from all different sources. From there I will have jobs scraping that staging database to look for those records that have values that generate alerts based on the thresholds.
The "alerts" database records will also get flagged as they are inserted, so the front end dashboard will only read new records that have not been flagged. Once read they will get flagged by either the front end or another SSIS job.
That is exactly what I am trying to figure out. I wanna know the best way to architect this for fastest performance and reliability.
Thanks!
R.
July 2, 2013 at 8:15 am
sounds like a job for slowly changing dimensions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply