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/
