RE: Real Time Data Warehouse
Tom, I guess I am echoing various other responses, but here's my 2c anyway: 1. There are a whole host of tools that will populate a SQL database from UniVerse. (I recommend mvQuery: it's my product so I would anyway). You will need to consider how far each solution can be automated and what it allows in terms of reformatting information (mvQuery Print Server provides a server based request system that can be used to shedule regular exports, for example) but by and large getting the data across probably won't be your main problem. 2. I would put the actual data migration to one side initially, and consider first what you want to get out of this. I have seen very successful 'decision support' reporting come out of standard reporting when backed up by a knowledge of what an application actually holds. In my experience, it is usually the fact that managers do not know what information is actually available to them from a transactional system that is the key, and closing that knowledge gap (often on both sides as communication of requirements can also be rather thin) is far more important than jumping straight onto a given technical solution. You could go down the Cognos route and then discover what they really want is an Excel pivot table. 3. You might want to consider native OLAP solutions such as MITS, which runs directly on UniVerse. This might be a) cheaper and b) more flexible in the long run. 4. Before you do any of this, you may need to carefully audit what you have on your U2 system. One of the biggest problems with data warehousing is dirty data - missing entries, entries whose meaning has changed over time, similar but non matching data, etc. These should really be cleaned up at source, particularly if the warehouse is liable to change/respecification over its initial period. Verification is important too - the more abstract the data presented (and OLAP is by its nature highly abstract) the more opportunity for errors to go unnoticed. Again the verification may need to be close to the source data: I remember a systems manager saying to me beware the spurious credibility of a well presented report. Brian Leach This email was checked on leaving Microgen for viruses, similar malicious code and inappropriate content by MessageLabs SkyScan. DISCLAIMER This email and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and do not disclose the contents to any other person, use it for any purpose, or store or copy the information. In the event of any technical difficulty with this email, please contact the sender or [EMAIL PROTECTED] Microgen Information Management Solutions http://www.microgen.co.uk -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: Real Time Data Warehouse
I would question how real time the OLAP BI tools are never mind the database. Yes, the real-time requirement is ambiguous. It will be addressed as we flesh out the tactical analysis requirements for the BI tools. I would suspect they are looking for a dashboard solution rather than an OLAP tool. BI, OLAP, and reporting is required by the project (among some other things). BI will be used for tactical analysis using real-time data. OLAP will be used for strategic analysis using point-in-time data. And, reporting is essentially for generating external reports for customers. I have been putting a white paper together to try an identify that PICK is the platform of choice for this time of environment. I'm not going to touch that one... though I understand your point ~8^) Thanks for your response! Tom Firl -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: Real Time Data Warehouse
Just for some more background here is the real reason you are having to go through the ETL is so that the users can play with your data using 'standard' BI tools like Cognos against the SQL database ? Yes. Also, what USE is the information going to be put to ?! To be determined... but I get your message. Tactical analysis is a critical component in the businesses we cater to. Thanks for your response. Tom Firl -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: Real Time Data Warehouse
Another possible name for a real-time data warehouse is Operational Data Store (ODS). I'm somewhat familiar with the concept ODS, I don't think it will play a role in this project, but it is on my radar. Any approach to actually porting data to SQL Server, for example, sounds so small and innocent until two years down the line you add up the costs of hardware, software, training for users and IT, on-going support, etc and find that it was a much bigger expense than anyone estimated up front. I hear what you are saying... on more than one level. Thanks for your reply Dawn. Tom Firl -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: Real Time Data Warehouse
Tom, Sixty to ninety days from now, I'll have a solution for you ::sigh:: I have the UniVerse side in my Tier I product (Zeus) but the SQL won't be until Tier II (in development) and I may or may not have the jBASE bits by then. You may want to look at writing a straight SQL read/write solution with UniVerse handling everything through phantoms. As to DataStage - we have a Stager or two on the list with us - I'm sure they'll be happy to respond. - Charles 'SQL is Dead, Dawn Said So, but I'm still adding it to my product' Barouch Tom Firl wrote: 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
