Hi Tom We customise many strategic systems to achieve this sort of facility.
I would question how real time the OLAP & BI tools are never mind the database. I would look at breaking the data in two components as some details such as financial require to be snapshoted to ensure the integrity of the data. Others may be related to current sales where accuracy is not so critical as is having a pretty good feel. These would require different update times. You don't need to keep updating End of Day balances every 10 minutes. Look at whether you update the remote files via triggers rather than trying to copy the whole file across. The big issue is to convert transactional data into timeshots that are meaningful. I would suspect they are looking for a dashboard solution rather than an OLAP tool. If you are in a position, can I suggest actually putting a prototype system up in Universe to show some of the expected outcomes. I often find that those requesting the information are not sure what they are looking for, so if I show them useful information that they require for decision making processes, then I can dictate the project. Also a prototype in 2 days achieves 80% of the solution, where management are then faced with a solution that is already partially working to having to put up a lot of money for an unknown solution on one of these BI Tools. This has won us a lot of business. There are many other ways of achieving this than just an ETL solution, but you would probably end up with a mixture of solutions. Jbase and Universe have similar code to updating SQL databses from BASIC code and based on similar code to Visual Basic processes. I have been putting a white paper together to try an identify that PICK is the platform of choice for this time of environment. Regards David Jordan Managing Consultant [EMAIL PROTECTED] Dacono Holdings Pty Ltd Business & Technology Consulting PO Box 909 Lane Cove NSW 2066 Australia Ph 61 2 9418 8329 Fax 61 2 9427 2371 www.dacono.com.au -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Firl Sent: Tuesday, 10 February 2004 8:55 AM To: U2-Users Distribution List (E-mail) Subject: Real Time Data Warehouse So, I'm moving on from my "encrypted database" problem (that was put on hold) and now I have a new, interesting problem. I'm looking at a proposal that seems to demand a solution that is a cross between a "data replication system" and a "data warehouse". The system needs to be able to "Extract" data from a feed up updates to specified Universe or jBASE files in "real-time" (once a minute, or so will suffice), do some "Transformation" on the data, then "Load" the data into a DB2 or SQL Server (not my implementation requirement... don't yell at me). During peak times, I'm supposing could be over a thousand updates per minute written to the data replication feed. I don't know if it's reasonable to expect this system to be able to handle that kind of throughput... that is to be determined. The rationale for the system is to allow people to use standard reporting, OLAP, and BI tools. In industry parlance, I think such a system is called a "Real Time Data Warehouse (RTDW)". So, here's where you can help... I'm brainstorming for design/implementation ideas. First, I'm trying to get the lay-of-the-land of tools and companies that can help with the "ETL" (Extract-Transform-Load) part of this project (is this what DataStage does?). Where do I look? Second, I'm searching for clever ideas about how to create and extract the data feed containing file updates -- such as leveraging UV-DR. I'd prefer to create the data replication feed in isolation from the ETL tool. Seeing as I'm a little lazy (and hoping we won't have to roll our own) I'd like to evaluate off-the-shelf solutions. TIA, Tom Firl Columbia Ultimate -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users