Can someone pls explain what is Near Real Time stored procedure architecture framework? How it works? If you have implemented the stored procedure in your project, pls share how you implemented, challenges faced etc. Can we replace it by bteq? If yes, what are advantages and disadvanges with stored procedure and bteq approaches?
any info will be appreciated. Thanks in Advance.
I think that tpump is a better option.
I do agree when we use stored procedure in ELT.
Thanks Raja for your input. Let me add more details on what we are trying to achieve.
There is a transactional system, which is a source for EDW. The task is to create an enterprise summary from the transacational system on near real time. Having said that, one question.
The stored procedure can talk to the transactional system directly? or have to stage the transactions on EDW before running the stored procedure?
Ideally, you need to bring in data in TD before you could get the benefits of SP. The name 'Near real-time architecture' suggests you should be using tpump to trickle the data from source to TD (stage) and then can process it using SPs/BTEQs/etc.
I would personally prefer SPs due to the control they give and its just easy to manage.