Interesting comments on the subject....admittedly I was fishing and wondering if anyone would mention Kore Technologies Kourier product. (which I don't have)
So I guess I should offer also what I currently do My first question when someone mentions ETL, is "What exactly are you trying to do?" Mention what was once a term relegated more toward data modeling tools for data warehousing (and they are usually just as confused about WHAT is a data warehouse often blurring an ODS with what a true Data Warehouse is - but I digress) "Are you trying to do data modeling" "For reporting?" "Data movement to another box?, Platform?" "What and when do you want to move data?" It took me a while, but I don't like the approach of spending months on creating a home grown U2 tool - often you code yourself into a corner with no room for growth or change in platform -- Use that U2 coding for something useful in an application IF you are not moving an entire file, (which I would say amounts to 98.5% of the time) why would you limit yourself to a tool that only handles a single file at a time? When I implemented the EDI translator I selected something that was not strictly EDI so I could simply use it to move data, generate reports etc My Steps more or less: 1) I First design the Unidata File (this would include determining what fields I want from an existing file) 2) I then create simple SQL Dictionaries which I keep within a separate account (to keep them nice & tidy and away from wayward fingers 2 dict levels for the same file if need be ) 3) Then Convert the Dicts to SQL 4) Simply use VSG to create Views, Combined Views, Sub-tables as needed down to the sub-value I hate the U2 XML tool since I think it makes butt ugly XML and that does not remotely resemble the masses - tried it and gave up I don't think the U2 mapper is quite ripe yet - close but not ripe in less than 7.1 (soon to upgrade) 5) Use plain 'ol ODBC setting up the datalink 6) Use the windows client 3rd party tool to map the visible U2 Tables to SQL tables as needed (drag & drop - promotes SQL mindset) 7) A key is dropped to a Samba Shared Directory activating a Event Trigger on SQL to grab the data and extract the data as mapped 8) The Event driven mapping allows for data to be written back to Unidata down to the Sub-value NOW....since I want to get more bang for my buck out of the "not only for EDI tool" I would like to if possible disband the use of the Samba Share Event Trigger and simply write that data FROM Unidata to SQL (ok - I need a good driver!) Suggestions? ALSO - (here is the search for the magic bullet) IF a file is mapped to a SQL file how to capture "changed" only records for transmission I'm scratching my head thinking..... Well.....if you STAT a file or record.......and that is where the brain fart takes over and I lose the path I am going down That is just my .02 Debster -----Original Message----- From: [email protected] [mailto:[email protected]]on Behalf Of Dawn Wolthuis Sent: Thursday, July 30, 2009 1:33 PM To: U2 Users List Subject: Re: [U2] What ETL? On Thu, Jul 30, 2009 at 12:25 PM, Mecki Foerthmann<[email protected]> wrote: > If the price of Datastage wouldn't be so prohibitive... They have some very good pricing for the U2 version of DataStage. It has some restrictions which might sound problematic, but I'm told it is easy to use and deal with the restrictions. I do not have pricing information, but I recall there was a vast difference between the regular product price and the U2 product price. > So I (have to) use SQL-Server DTS through ODBC and except for one file which > has some records with tens of thousands of multi-values I had no problems so > far. Yes, I opted not to suggest that option simply bz you have to work with your source via ODBC, which can be rather painful, even if doable. > I am not sure, maybe the ODBC part of Avante is even SB+ standard, but I > guess with UniSQL it wouldn't be a big deal to create SQL-schemas either. > It was a bit tricky to get some I-Descriptors to work at first, but I soon > got the hang of it. > > I found it is actually quite quick - even files with hundreds of thousands > of records take only a couple of minutes to load, and since I do the upload > over night it isn't a problem. > SSIS would obviously even better than DTS since you can trap unclean data, > but if you can't have anything else and your data is clean, DTS does the > trick. > If it works with Unidata 5.2 and standard SQL Server 2000 tools, who needs > expensive ETL software? > Well, that's at least what my boss obviously thinks. Yes, if you are headed into SQL Server and you already have ODBC licked, then DTS is a reasonable, if a bit irritating, approach. > And if I could really convince him that I needed Datastage so I can use > Cognos to produce reports from Avante, the board would send him packing if > he asked them to spend nearly 100K on ETL software. Cognos is way, way overpriced for a U2 shop, in my opinion. I do not have a current price list, however. > Especially since they have decided to go to an Oracle based ERP package next > year anyway, so if we buy an ETL tool it has to work for Oracle as well. There's a reason to use DataStage instead. Check on the U2 pricing. It isn't close to free, but it isn't DataStage regular pricing either. > So I rather push to upgrade to SQL Server 2008 for my reporting database, so > I can use SSIS instead of DTS. Ugh, not very pretty, especially if moving to Oracle in the future, but sometimes things just aren't pretty, eh? Are you going to use SQL Server for your reporting once in Oracle too? --dawn > Mecki > > > > Tony G wrote: >> >> Somehow when someone mentions BI there are suggestions for >> Reporting tools, and with notes about ETL come notes about BI and >> Reporting. In my mind (what there is of it) there is a vast >> difference between these concepts. I think the confusion comes >> in when a product like MITS, for example, incorporates its own >> ETL functionality to accomplish what it does. Some basic >> reporting tools do the same in the name of performance. But the >> ETL performed by these tools is generally proprietary and cannot >> be used in other contexts. >> >> Now when you're talking about a real ETL platform like DataStage, >> you should be able to use data from platform X with any platform >> Y. A product like MITS or Cognos or Informer or any other could >> code their front-end processes to use DataStage as a data source. >> This would open them for use, even with non-MV platforms like >> Oracle or DB2 - and of course products like Cognos do exactly >> that - but our MV-centric colleagues generally don't think in >> those directions. >> >> Speaking of DataStage, I was discussing an association with a >> company a while back for providing mainstream BI tools for MV >> (that option is still considered from time to time and interested >> parties are welcome to contact me). As we can all relate, I had >> to spend a lot of time explaining the Pick/MV concepts which were >> completely unfamiliar to them. In our discussion we decided that >> the best way to use common BI tools with MV was not to link >> directly to MV at all as a data source, but to use a middle-tier >> ETL tool, I could provide the extraction from MV and they could >> extract from the generic middle-tier using common queries and >> tools. Ironically when we were discussing what tools they >> already used, they mentioned DataStage. Maybe they were unique >> but it seems to me that DataStage could be considered a >> poster-child as a successful MV application, but somehow that >> marketing value seems completely untapped. *sigh* >> >> Tony Gravagno >> Nebula Research and Development >> TG@ remove.pleaseNebula-RnD.com >> >> _______________________________________________ >> U2-Users mailing list >> [email protected] >> http://listserver.u2ug.org/mailman/listinfo/u2-users >> >> > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > -- Dawn M. Wolthuis Take and give some delight today _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
