Nedd a little guidance on a data capture scenario from Oracle
in [Help]
|
From: The Frog on 12 Mar 2010 03:37 Hi Everyone, I am trying to decide how to approach a design problem. The scenario is that there is a data warehouse using Oracle that contains 'real- time' data that is needed for a report. The source data in the warehouse is messy to say the least, but with enough time wasted I have managed to get a clean 'capture' of the data, and am currently using a passthrough query to get to that data. There are approximately 600k rows of data, and it takes about 13.5 minutes to receive all that from the passthrough. So far so good. My design issue is that the desired report is needed with comparison over time. This 'real-time' system only shows what the world 'looks like' right now. My thinking to achieve a time comparitive based report is to 'capture' the data from the passthrough query and dump it into a separate MDB (ie/ generate a new one each time a capture is done), and timestamp it. I am wondering if anyone has done this and can let me know the pro's and cons of this approach. Is there a better way to handle this? And while we're at it is there a simple way to perform this capture outside of tons of code? - I can achieve a capture but I am not sure that my approach is a good one. Any feedback or shared wisdom greatly appreciated :-) Cheers The Frog
From: Roger on 12 Mar 2010 06:17 On Mar 12, 1:37 am, The Frog <mr.frog.to....(a)googlemail.com> wrote: > Hi Everyone, > > I am trying to decide how to approach a design problem. The scenario > is that there is a data warehouse using Oracle that contains 'real- > time' data that is needed for a report. The source data in the > warehouse is messy to say the least, but with enough time wasted I > have managed to get a clean 'capture' of the data, and am currently > using a passthrough query to get to that data. There are approximately > 600k rows of data, and it takes about 13.5 minutes to receive all that > from the passthrough. So far so good. > > My design issue is that the desired report is needed with comparison > over time. This 'real-time' system only shows what the world 'looks > like' right now. My thinking to achieve a time comparitive based > report is to 'capture' the data from the passthrough query and dump it > into a separate MDB (ie/ generate a new one each time a capture is > done), and timestamp it. > > I am wondering if anyone has done this and can let me know the pro's > and cons of this approach. Is there a better way to handle this? And > while we're at it is there a simple way to perform this capture > outside of tons of code? - I can achieve a capture but I am not sure > that my approach is a good one. > > Any feedback or shared wisdom greatly appreciated :-) > > Cheers > > The Frog instead of capturing / timestamping into an MDB, can you not do that within Oracle and report from that ? if you are going to collect data into an MDB, why would you need 'tons of code', isn't it a simple insert query from Oracle to an mdb, where the MDB table's timestamp field defaults to the current date ?
From: The Frog on 12 Mar 2010 10:26 Hi Roger, The reason for the capture is that comparison over time needs to be done, but of course the 'live' data will change, making the report impossible, so the data needs to be saved at a point in time, hence the capture. My tons of code was based around using recordsets to receive the data so that I had some form of error control and feedback over the process. I have also done the make-table query approach (much faster) but that method lacks any feedback, and for such a (relatively) long process feedback for the user is a good idea (IMO). I had forgotten about the timestamp field default value of now() trick! Thankyou for the reminder. Simple things you sometimes forget....... After doing a little testing it looks like an individual data capture will contain about 50Mb of data. Should be able to dump many captures into a single MDB. My thinking at the moment is to re-link the tables from the FE to user designated BE and dump the data into that. My concern is I suppose centered around providing sufficient feedback to the user during the capture process. I am yet to find a 'minimalist' approach that gets the job done. At the moment the best I can do is to run a passthrough that tells me how many rows to expect (pretty quick), then use a recordset and page through keeping count....... Open to suggestions. Cheers The Frog
From: Roger on 12 Mar 2010 10:57 On Mar 12, 8:26 am, The Frog <mr.frog.to....(a)googlemail.com> wrote: > Hi Roger, > > The reason for the capture is that comparison over time needs to be > done, but of course the 'live' data will change, making the report > impossible, so the data needs to be saved at a point in time, hence > the capture. > > My tons of code was based around using recordsets to receive the data > so that I had some form of error control and feedback over the > process. I have also done the make-table query approach (much faster) > but that method lacks any feedback, and for such a (relatively) long > process feedback for the user is a good idea (IMO). > > I had forgotten about the timestamp field default value of now() > trick! Thankyou for the reminder. Simple things you sometimes > forget....... > > After doing a little testing it looks like an individual data capture > will contain about 50Mb of data. Should be able to dump many captures > into a single MDB. > > My thinking at the moment is to re-link the tables from the FE to user > designated BE and dump the data into that. My concern is I suppose > centered around providing sufficient feedback to the user during the > capture process. I am yet to find a 'minimalist' approach that gets > the job done. At the moment the best I can do is to run a passthrough > that tells me how many rows to expect (pretty quick), then use a > recordset and page through keeping count....... > > Open to suggestions. > > Cheers > > The Frog I assume you have a form with a command button to start the capture you could have a couple of textboxes on the form, one to display the step currently being processed the other, driven by the timer event, to display the number of records in the table(s) being built...
From: The Frog on 15 Mar 2010 04:09
Hi Roger, I do have a button that starts the process, and I can place a 'hamster in a wheel' so to speak based on the forms timer event to show activity is taking place. I know how many records need to be brought back (separate count query), but using an SELECT INTO statement for the table, which is by far the quickest method gives me no feedback on how far through it is during the process (there is only one table / view to capture) - hence using the recordset. I was just hoping that someone might know a trick with access to show that kind of data without using a recordset. It looks like I might just have to stay with the existing method so far. It does work, just takes a long time...... Thanks for the help, I appreciate your time and interest. Cheers The Frog |