User Intiated ETL

Hi All,

Can someone please share their advice with me on an issue we are having. I was consulted by member on another project who's customer wants them to create a process where if the user makes a change to the source data in an excel spreadsheet, the data warehouse should be immediately updated to reflect the change. This change can be a correction of data from the past not just current data so therefore historical data will need to be changed. The developer said they can give the user the ability to run the ETL job on demand as we need to give them what they need and is pursuing this solution. I said that we never give users access to the back room in any form. And that data warehouses are not meant to operate in this fashion due to not being real time. I thought of partitioning but due to the historical aspect, don't think it will work. Due to there only being a couple of thousand records I suggested a refresh schedule of every hour or less depending on demand on the system. I think we need to manage the users expectations here rather than give a solution that is not a best practice just to meet a requirement.

I can think of nothing else to convince this developer to not go down this road. Can someone more experienced help me out here? I have read a couple of Ralph Kimball books and he has always said keep the users out of the back room and no where were there any suggestions of user triggered ETL jobs being run.

So any advice would be much appreciated, perhaps I am in error, just like to know one way or the other so we can give good solid solutions to our customers.

Thanks for any help

Brian

Actually I think Ralph did address this requirement back in 2001, in his Design Tip #31: Designing A Real Time Partition:

http://www.kimballgroup.com/2001/11/19/design-tip-31-designing-a-real-time-partition9/

Mike Honey wrote:Actually I think Ralph did address this requirement back in 2001, in his Design Tip #31: Designing A Real Time Partition:

http://www.kimballgroup.com/2001/11/19/design-tip-31-designing-a-real-time-partition9/

Thanks for the redirect to that Design Tip, I didn't see it previously. It looks like the accumulating snapshot will work for our needs in editing older data. I thought for some reason partitioning was only for current data not entered after the last ETL load, or new data not update data.

Thanks for the help,

Brian

Im new to database. I have requirement for developing metrics framework. In my organization we have hierarchy. Each hierarchy we have certain metrics thats common for all some metrics specific to particular department