Well, MITS does a SELECT against their copy, then against the source copy and then merge the list. So - the larger it gets - well.... (Not to mention I think MITS is a hunk of glorified crap)
I've had no issues using triggers. (My box is huge thou....) Agreed: VSG sucks. Epicor ETL's "CDC" is rather large footprint for what it does - be prepared to pay some change (if not whole dollars) in both software and consulting. I believe it's basically an "index subroutine" pointed to another database account... I would tell your ETL vendor that you want a 30 day demo of the product before you buy - that way you can fully know what you're getting. We did that with Epicor ETL and decided it was way over priced for what we were getting. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mecki Foerthmann Sent: Friday, May 09, 2008 3:33 AM To: [email protected] Subject: Re: [U2] Using ETL to extract data from UD to SQL Well, this all contradicts what I've been told by our software vendor. Triggers just like ODBC supposedly totally kill the performance of UD (we are still on 5.2). I was advised to stay away from them or use them sparingly, if think I really have to. I was also told not to use VSG, and that, I had to experience myself, really killed our system when I tried it out a couple of weeks ago. And on the webcast I've seen from Epicor, Sybase ETL 4.2 (or at least Epicor's version of it) comes with an in-built tool that only transfers added or amended records. I know MITS does that as well - don't ask me how it works, though, but it definitely does (checksums maybe?). I would have suggested to use MITS, but they've already bought Cognos, so I am stuck with it. This ETL tool also recognises multi values and generates automatically a virtual sub-table based on U2 dictionary's MV field for every association in the database window , and these fields can then been dragged and dropped into the designer window. So thank you for all the suggestions and product offers how to transfer flat files - but I am not interested in flat files - my world (and our data) is MV! Furthermore this particular ETL product, I am talking about, uses UniObjects and not ODBC to connect to the U2 database. We are not really interested in operational data (this is for BI only), so scheduled updates of the sql-database are fine. As I said this tool looks very slick, I just wanted to know, if anybody had used it, before I recommend buying it. They said though in that demo that creating SQL views and schemas on the U2 side is not necessary, but would be helpful. And somehow, I just don't seem to be able to get any fun out of Unisql - at least on our system. I had it working on another site and it works fine with my PE at home, but at work I can convert tables, create new ones, whatever the manual says, but when I type a simple sql-statement like 'SELECT fieldname FROM tablename', no matter what I put in for fieldname or tablename (even rubbish), I always just get another sql> prompt - not even an error message. And through ODBC I can establish a connection but see no tables at all from Windows. I am not really concerned about 'dirty' data. If there is actually dirty data on the system (at least in the files I want to transfer), then it must be very old, and I should find it on the first pass. It can only be dates and numbers, and cleaning them up shouldn't be a big deal. I saw that ETL 4.2 even has a tool to set up rules to change data before writing them to the sql server - even though it failed dismally in the demo, when the guy tried to show it. So thank you all Mecki Boydell, Stuart wrote: > There is going to be a lot of effort for any ETL (Extract, transform, > load) on an 'heritage' database. You will have to deal with weird > table/file structures (who did it and why is it that way?!!) and dirty > data to get to a set of data suitable for your DW/reporting > requirements. > > If you have determined the reasons for going through the process will > give your business an ROI, it is worth getting it right... and there > will be a learning curve. Schemas might look pretty straightforward at > the end of it. > > Our rationale was to have an uptodate set of data off our transactional > host to give us various non U2 reporting and interface benefits. > > We do our ETL process from UV to SQL then (among other uses) build > Cognos cubes using the following method (basically): > > Use triggers on UV to track changed fact records. > Use SQL Job scheduler to control periodic extracts and cube build: > -Job reads parameter table from SQL of UV commands to run. > -Job invokes Uniobjects process which runs the command (either LIST > ... TOXML or subroutine) which selects changed "fact" records and > "dimension" tables - returns XML. > -Job invokes "SQLXMLBulkLoad" process to load SQL. (This tool is > great!) > -Job runs stored procs which re-key, check integrity and further > cleanse the data. > -Job invokes cube build. > > There's obviously a bit more control around it but this works quickly > and effectively with gigabytes of data daily. It's all parameter driven > so that changes are quick and simple. Much of the cleansing (and there > is a lot of junk in our files) happens using UV LIST FMT/CONV TOXML and > selection modifiers in the extract commands. > > Personally I'd steer away from ODBC or OLEDB which mix data format and > data transport. I prefer the idea of coupling discrete tools which > perform each function well. > > We use Cognos 8 which, according to our business analysts, works well. > It can use just about any SQL data source and you could point it > directly at your U2 db using ODBC but you'd miss the opportunity of > taking processing off your transactional db and cleansing your data for > reporting purposes. > > Regards > Stuart > > >> -----Original Message----- >> have any of you had any experience using ETL from Sybase? >> The company I work for has bought Cognos for reporting and data mining >> purposes on their other non-mv applications, and I have given the task >> (and I told them, it should be possible) to make this work with our >> UniData ERP application as well. >> Now I've heard of this ETL product from Sybase that looks pretty simple >> to use (drag-and drop) and is supposedly very fast since it doesn't use >> ODBC but UniObjects. >> So the question is, 'have any of you used it, and if yes, is it any >> > good?' > >> Or do you have any recommendations, which mv-to-sql bridges are >> worthwhile using. >> This setting up schemas and sql-tables in UniData (we're still on 5.2) >> seems to be way over my head, and I don't seem to be getting anywhere >> that way. >> >> many thanks in advance >> >> Mecki Foerthmann >> > > > > ********************************************************************** > This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au > Please consider our environment before printing this email. > ********************************************************************** > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
