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/
