Re: [U2] What ETL?
Interesting comments on the subjectadmittedly 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 NOWsince 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org]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 Foerthmannmec...@gmx.net 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
Re: [U2] What ETL?
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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
If the price of Datastage wouldn't be so prohibitive... 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. 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. 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. 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. So I rather push to upgrade to SQL Server 2008 for my reporting database, so I can use SSIS instead of DTS. 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
On Thu, Jul 30, 2009 at 12:25 PM, Mecki Foerthmannmec...@gmx.net 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Dawn M. Wolthuis Take and give some delight today
Re: [U2] What ETL?
Thanks Dawn, There was no pricing for Datastage for U2 on the IBM website, and when I saw that they charge nearly 90.000 Pounds for Datastage for Oracle, I guessed that the U2 version would be a couple of ten thousand at least - knowing IBM prices. I also know that Cognos is rather expensive and to be honest I think it is quite 'buggy' and can be a real pain to work with too. I would have preferred MITS, but the company bought Cognos, and so I have to make the best out of it. You see, most of the company's other divisions are already running on Oracle (Chess and Glovia.com) and only one, even though it is the biggest, is running Avante. I have only been there for a year and a half and even though I have shown them what really can be done with Unidata and SB+ it is too late now. SQL Server 2008 is relatively cheap (processor licensing) and I actually find it not that hard to use DTS. But from what I have seen of SSIS so far, it seems to be a lot better. To set up a new table I just click on Import data, select save as DTS package and use the wizard to select the Unidata table I want to bring across. Then I usually just accept all the defaults and it then creates the table using the UniSQL schema for me automatically. So DTS does most of the work for me already. I then only have to change the Create table part to Truncate table, add a task to send me an email on fail and schedule the job to run every night at a certain time. And of course set the key and create indexes, but that is all done with a couple of mouse clicks as well. At night the system is only used to record the timesheets of the night shift on the shop floor, so I scheduled the first job to start running at 8pm and kick the next one off every couple of minutes. By 10 it's all done and if something failed there is an email waiting for me to tell me in the morning. If I need something you usually would use an ETL tool for, I create an I-descriptor. Works fine even with TRANS. And if push comes to shove I can always create a new file and populate it using a Basic program. That can then be scheduled to run from Unix long before DTS will attempt to download the table at night. I don't know if I will be using SQL Server for Oracle data once all divisions are on Oracle, since at the moment we use the Oracle data directly from Cognos. But we have several other systems that produce data as well and maybe I will use it to build a data warehouse for job costing purposes, KPIs and the like. Some of the queries we are running now on Oracle from Cognos take quite a long time to run, so it might be better to do it that way. The Cognos server already gets hammered at end of month time, and it will get worse the more divisions go live on Glovia.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
I created an ETL and installed it at a client site, now the company I currently work for has been marketing it recently.? It is called The SQLizer.? It takes the Unidata/Universe files it SQLizes and splits off the associations/MV fields into their own tables as part of the normalization process.? When I started I thought I'd use ODBC, but it turns out it is very easy to just dump the Uni files into text files and then have a short perl program do the explodes as needed. To transport and load, I am using the TMI Listener which is bundled with the SQLizer.? Target databases are SQL Server, MySQL, and Oracle.? SQLizer is being marketed for just under $10k. It can load SQL on demand (such as overnight batches) or keep relatively current using index-based triggers.? Since it is all batch based (phantom) it does not take up licenses (unless your site's phantoms *have* to take up a license due to being iphatoms.)? All it's calls to sockets are in perl, it is almost like calling ftp to run from Basic, so the phantoms stay phantoms. I have some questions for folks: ? 1) Are subvalue marks commonly used?? Right now the current users don't have an issue, the few tables that use it we have been splitting SVMs out into columns, but in general I was wondering how prevalent the use of SVMs has been. 2) Do folks generally have good metadata at their sites?? The U2 dictionaries are often unreliable and I have a data scanner to see what the data actually is, but I was wondering if folks typically setup their own metadata. 3) Do folks typically want overnight updates or to keep things relatively current throughout the day? 4) How much transformation would folks like to do on the U2 side?? I have the ability to trigger updates and then run a transformation program as part of the pre-SQLization, as well as the ability to do some on-the-fly I-descriptors, but do folks typically just want things SQLized to do the manipulations on the other side? 5) What other features are folks looking for? Thanks for any info you can provide! Steve... -- Steve Kneizys regalit...@aol.com -Original Message- From: Tony G 1tlx6h...@sneakemail.com To: u2-users@listserver.u2ug.org Sent: Thu, Jul 30, 2009 3:49 am Subject: Re: [U2] What ETL? 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
1. SubValue marks are used here... but in less than 1% of the data. 2. Dictionaries are 95% reliable 3. We do overnight... Expecting to do more 4. No transformations wanted 5. -B -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of regalit...@aol.com Sent: Thursday, July 30, 2009 7:46 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] What ETL? I created an ETL and installed it at a client site, now the company I currently work for has been marketing it recently.? It is called The SQLizer.? It takes the Unidata/Universe files it SQLizes and splits off the associations/MV fields into their own tables as part of the normalization process.? When I started I thought I'd use ODBC, but it turns out it is very easy to just dump the Uni files into text files and then have a short perl program do the explodes as needed. To transport and load, I am using the TMI Listener which is bundled with the SQLizer.? Target databases are SQL Server, MySQL, and Oracle.? SQLizer is being marketed for just under $10k. It can load SQL on demand (such as overnight batches) or keep relatively current using index-based triggers.? Since it is all batch based (phantom) it does not take up licenses (unless your site's phantoms *have* to take up a license due to being iphatoms.)? All it's calls to sockets are in perl, it is almost like calling ftp to run from Basic, so the phantoms stay phantoms. I have some questions for folks: ? 1) Are subvalue marks commonly used?? Right now the current users don't have an issue, the few tables that use it we have been splitting SVMs out into columns, but in general I was wondering how prevalent the use of SVMs has been. 2) Do folks generally have good metadata at their sites?? The U2 dictionaries are often unreliable and I have a data scanner to see what the data actually is, but I was wondering if folks typically setup their own metadata. 3) Do folks typically want overnight updates or to keep things relatively current throughout the day? 4) How much transformation would folks like to do on the U2 side?? I have the ability to trigger updates and then run a transformation program as part of the pre-SQLization, as well as the ability to do some on-the-fly I-descriptors, but do folks typically just want things SQLized to do the manipulations on the other side? 5) What other features are folks looking for? Thanks for any info you can provide! Steve... -- Steve Kneizys regalit...@aol.com -Original Message- From: Tony G 1tlx6h...@sneakemail.com To: u2-users@listserver.u2ug.org Sent: Thu, Jul 30, 2009 3:49 am Subject: Re: [U2] What ETL? 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org
Re: [U2] What ETL?
Hi, E.T.L. which one ? A in-house SB+/BASIC dev composed with 2 parts : - SB+ interfaces to define mapping extract options (Extract, Transform E.T. ) -- tablename --- link to filename(s) (same file into multi-account or multi-file with the same structure) --- human name of the table (for metadata providing to request tool) --- long description of the table (for metadata providing to request tool) -- columnname --- linked to Itype or SB+ expression to extract data --- sql-datatype --- human name of the column (for metadata providing to request tool) --- long descrition of the column (for metadata providing to request tool) --- specify the column indexing properties into dw -- options --- process before extract (to prepare temp file if necessary) --- select clauses to filter data --- process after read record (to transform the datas by BASIC if necessary) --- dw refresh rhythm incremental real time (based on trigger update trap) incremental time stamp (based on a time stamp, if exist into record) partial, based on select criteria complete - BASIC subr to extract data from files via E.T. and build SQL stmt to update data warehouse (Load L.) We run under Universe, Unidata, D3. We run in front of data warehouse running under MS-Sql, Orable, DB2, mySQL, Unidata, Universe(sql-schema), or direct access - Datawarehouse can be on the same server or separate. We have routines to interfaces data from outside U2 (xls, mdb, other-sql db, ...) and merge it into DW. The really big advantage is : - we work directly into the native structure of U2 (mv'ed, sv'ed, record_on_fixed_key, special storage organisation) - we use BASIC power to extract, transform data - the deployment consist to deploy one account with subr and one file with ETL defn per application - we build SQL stmt via template which can be dedicated by sql-db specificity Manu -Message d'origine- De : u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] De la part de Debster Envoyé : mercredi 29 juillet 2009 06:50 À : u2-users@listserver.u2ug.org Objet : [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
Here we are using ODBC and UniBasic. Extract, transform, and load (ETL) in database usage and especially in data warehousing involves: Extracting data from outside sources Transforming it to fit operational needs (which can include quality levels) Loading it into the end target (database or data warehouse)... (defn from Wikipedia) AnyOne can go to BlockBuster and rent the Perfect Storm -Billus Brutzmanicus -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Debster Sent: Wednesday, July 29, 2009 12:50 AM To: U2 Users List Subject: [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
Debster - Horses for courses. It's like saying 'what do you use for your applications' - there's a wealth of scenarios out there and tools for each of them .. At the top end, Aptitude from Microgen. Not cheap, but a fully featured suite for transforming data: very powerful, graphical and loved by financial analysts. Probably too heavy for most mv uses, and you need a serious consulting budget for that as well. But for sheer firepower it's one of the best. At the bottom end, BCI out of UniVerse. The advantage of BCI is that you have total programmatic control over the data you ship out, especially where it isn't easy to get it from enquiry type statements. Inbetween, Conduit [ad] or the data transformation tools in SQL Server if you want to pull data in from UniVerse over e.g. ODBC. And I'm waiting for someone to say DataStage... (I don't use it so I can't recommend it). Regards Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Debster Sent: 29 July 2009 05:50 To: U2 Users List Subject: [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
DataStage. Works well for us. Our vendor exposes a somewhat normalized SQL account. We interact with that, not directly to UD. It's graphical if you want. Programmatic if you need. Since we're a U2 shop, we like that it's based on a flavor of UV. Have been using it for a number of years with no real issues to send data directly to Oracle, SQL Server and DB2. Brad And I'm waiting for someone to say DataStage... (I don't use it so I can't recommend it). Regards Brian U.S. BANCORP made the following annotations - Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. - ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
DataStage would be an option. I am not using it, but it would be the cadillac solution. At the low, but excellent, end of the spectrum, I would put Cedarville DOWNLOAD, which can also extact to xml, for example IIRC. Depending on your goal, something like Informer or MITS might fit the bill too. I have a ton more information regarding ETL with UniData in my head, but much of my information is now dated (we are now using Cache' rather than UniData), so I'll just leave it at that. --dawn On Wed, Jul 29, 2009 at 9:18 AM, Brian Leachbr...@brianleach.co.uk wrote: Debster - Horses for courses. It's like saying 'what do you use for your applications' - there's a wealth of scenarios out there and tools for each of them .. At the top end, Aptitude from Microgen. Not cheap, but a fully featured suite for transforming data: very powerful, graphical and loved by financial analysts. Probably too heavy for most mv uses, and you need a serious consulting budget for that as well. But for sheer firepower it's one of the best. At the bottom end, BCI out of UniVerse. The advantage of BCI is that you have total programmatic control over the data you ship out, especially where it isn't easy to get it from enquiry type statements. Inbetween, Conduit [ad] or the data transformation tools in SQL Server if you want to pull data in from UniVerse over e.g. ODBC. And I'm waiting for someone to say DataStage... (I don't use it so I can't recommend it). Regards Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Debster Sent: 29 July 2009 05:50 To: U2 Users List Subject: [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Dawn M. Wolthuis Take and give some delight today ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
Bill You forgot ODS vs true Data Warehouse ;-) Brutzman, Bill bi...@hkmetalcraft.com Sent by: u2-users-boun...@listserver.u2ug.org 07/29/2009 10:06 AM Please respond to U2 Users List u2-users@listserver.u2ug.org To U2 Users List u2-users@listserver.u2ug.org cc Subject Re: [U2] What ETL? Here we are using ODBC and UniBasic. Extract, transform, and load (ETL) in database usage and especially in data warehousing involves: Extracting data from outside sources Transforming it to fit operational needs (which can include quality levels) Loading it into the end target (database or data warehouse)... (defn from Wikipedia) AnyOne can go to BlockBuster and rent the Perfect Storm -Billus Brutzmanicus -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Debster Sent: Wednesday, July 29, 2009 12:50 AM To: U2 Users List Subject: [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- This communication (including any attachments) is intended only for use by the addressee(s) named herein and may contain legally privileged or confidential information. If you are not the intended recipient or an authorized representative of the intended recipient on this communication, you are hereby notified that any dissemination or distribution of this communication (or attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and permanently delete the communication and any attachments from your system. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
We use a small in-house C#/Uniobject tool which: Extracts data from U2 using DBTOXML function. Uses SQLXMLBulkLoad object to put that into a SQL operational data store. We run classic ETL process on that and build star schemas etc I like it because ... it's simple, quick and relatively efficient, takes advantage of U2 to clean the data before it gets to XML. Takes advantage of XML schema and transformations to get the data into SQL. Stuart -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Debster Sent: Wednesday, 29 July 2009 2:50 PM To: U2 Users List Subject: [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ** 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] What ETL?
Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] What ETL?
OK - Lemme clarify that Unidata to SQLand other possible similar databases -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org]on Behalf Of Debster Sent: Wednesday, July 29, 2009 12:50 AM To: U2 Users List Subject: [U2] What ETL? Just takin' a survey What ETL tool do you use - Why do you like it? Ease of use Anyone can hold the helm when the sea is calm. -Syrus Publilius ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
