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] the XML/DB Tool
Just one point - where you say hard code - I am not entirely in agreement - my xml handling code is generic for all the xml file i suck into u2, the EXT file is different for each xml file - it is in a way an addendum to the xsd and allows you to only import parts of the xml. If this is what you call hardcoding then i disagree it is a configuration. If however you are hardcoding your databasic for each xml file then yes that is hardcoding and i agree you should not do it that way ! Symeon. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Tony G Sent: 29 July 2009 22:02 To: u2-users@listserver.u2ug.org Subject: Re: [U2] the XML/DB Tool From: Coelho, Gregory I am on UniData (7.1 and 7.2) attempting to create a set of nested relationships in a XML file. In an attempt to learn how to use the XML/DB Tool I've read through IBM's UniData:UniBasic Extensions - but am now more confused that at the onset. Can anyone direct me to a knowledge source for help? I'll piggy-back on Greg's request. I don't know if this is at all what Greg is trying to do but I'll take a chance and see if anything I'm doing can help with what he's doing. I've recently obtained a large and completex dataset which is composed of hundreds of megabytes of XML, defined by a set of XSD documents. Transforming the XSD into strongly-typed .NET classes is easy. Reading the XML into strongly-typed objects is easy. The next step is mapping these objects to MV files. Having done this sort of thing a number of times before, I'm firmly of the opinion that one should not, for example, import XML data for Customers and push that data directly to a CUSTOMERS file. Rather, I believe the data should be stored in one or more files, like CUSTOMERS.XML.PRIMARY, CUSTOMERS.XML.ADDRESSES, CUSTOMERS.XML.CODES, etc, and from there business rules should be run to properly integrate this data with the live application files. This ETL layer helps to ensure that live data isn't corrupted by a data dump, and it also helps to allow for changes in both the XML and the application without having to link the two directly. It's an MVC pattern which works well in a wide variety of applications. I have been brainstorming for quite a while now to find the best way to link middle-tier code to the back-end as defined above. I do not like the idea of hardcoding XML handling into the MV environment, so unless strongly compelled, personally I tend to avoid the U2 XML tools entirely. But I'm open for suggestions. Right now I use C# partial classes to represent the XSD schema, and am planning to create new partial classes to augment these with SaveToDBMS functionality, looping on class objects to run rules and process the data into MV files. I'd like to automate the generation of the files that are updated (like CUSTOMERS.XML.ADDRESSES above) or write a code generator that will use schema or code (via reflection) to create code to do this same function for any set of XML/XSD. Assuming I've explained the above properly - has anyone done this? Anyone interested in the outcome? Any suggestions? Is this a case where the U2 XML capabilities may present some value? I'll be happy to clarify where required. Thanks! Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Nebula RD sells mv.NET and other Pick/MultiValue products worldwide, and provides related development services remove.pleaseNebula-RnD.com/blog Visit PickWiki.com! Contribute! ___ 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] the XML/DB Tool
Hardcoding and Handcoding are subtly different. On Thu, Jul 30, 2009 at 12:59 AM, Symeon Breen syme...@gmail.com wrote: Just one point - where you say hard code - I am not entirely in agreement - my xml handling code is generic for all the xml file i suck into u2, the EXT file is different for each xml file - it is in a way an addendum to the xsd and allows you to only import parts of the xml. If this is what you call hardcoding then i disagree it is a configuration. If however you are hardcoding your databasic for each xml file then yes that is hardcoding and i agree you should not do it that way ! Symeon. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] the XML/DB Tool
As usual, we're in agreement. I should clarify that I would avoid writing or generating code that makes use of the XMAP API to process specific documents of any real complexity. That sort of code is just too rigorous and personally I don't think it belongs in the MV engine. What I should have said is that I am thinking about generating mapping files, rather than using XML/DB. Yes, with my very limited undertanding of the XML extensions, I believe you're right if you're saying code that operates on mapping files can be hardcoded. If I do generate mapping files, I would also need to create the related data files and dict definitions. Well heck, rather than do this using U2-specific tools I'd much rather use something cross-platform, like Java or .NET, and avoid the nested relationships issues that Greg cites as well as a bunch of others that may be lurking within the U2-specific tools from IBM. I'll be making decisions very soon... Best, T From:Symeon Breen Just one point - where you say hard code - I am not entirely in agreement - my xml handling code is generic for all the xml file i suck into u2, the EXT file is different for each xml file - it is in a way an addendum to the xsd and allows you to only import parts of the xml. If this is what you call hardcoding then i disagree it is a configuration. If however you are hardcoding your databasic for each xml file then yes that is hardcoding and i agree you should not do it that way ! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] the XML/DB Tool
Yes - reminds me of some programmers i know who if they do not have a point and click interface and the ability to draw lines between tables, and tick boxes etc are incapable of any sql, and who are also incapable of coding anything outside of VS. Lol One of my pet hates i suppose is that toolsets make certain things like web services and xml easy for anyone to do - but should these people be doing it if they do not understand what it is they are doing ??? :) -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ross Morrissey Sent: 30 July 2009 09:24 To: U2 Users List Subject: Re: [U2] the XML/DB Tool Hardcoding and Handcoding are subtly different. On Thu, Jul 30, 2009 at 12:59 AM, Symeon Breen syme...@gmail.com wrote: Just one point - where you say hard code - I am not entirely in agreement - my xml handling code is generic for all the xml file i suck into u2, the EXT file is different for each xml file - it is in a way an addendum to the xsd and allows you to only import parts of the xml. If this is what you call hardcoding then i disagree it is a configuration. If however you are hardcoding your databasic for each xml file then yes that is hardcoding and i agree you should not do it that way ! Symeon. ___ 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
[U2] Slow selects
We are running Unidata 7.1 on a linux box (Red Hat Enterprise Linux ES release 4 (Nahant Update 4) Kernel 2.6.9-42.ELsmp on an i686). For some reason I cannot fathom, the first time you perform any Uniquery command on a file it takes forever to return. Say, 5 minutes. Subsequent queries on the same file, with different criteria (or the same), take seconds, say 10. This is generally on static, hashed files with lots of level 1 overflow, but no level 2 overflow. This is a pretty fast environment with this weird exception. Any ideas about what the problem might be? Thanks, Kebbon ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Slow selects
The first pass on a file has to start from scratch. Once it has been read and is still fresh in memory, a 2nd pass will run much faster. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kebbon Irwin Sent: Thursday, July 30, 2009 10:58 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow selects We are running Unidata 7.1 on a linux box (Red Hat Enterprise Linux ES release 4 (Nahant Update 4) Kernel 2.6.9-42.ELsmp on an i686). For some reason I cannot fathom, the first time you perform any Uniquery command on a file it takes forever to return. Say, 5 minutes. Subsequent queries on the same file, with different criteria (or the same), take seconds, say 10. This is generally on static, hashed files with lots of level 1 overflow, but no level 2 overflow. This is a pretty fast environment with this weird exception. Any ideas about what the problem might be? Thanks, Kebbon ___ 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] Slow selects
And that will only last for a while before that information is flushed from memory. David Laansma IT Manager Hubbard Supply Co. Direct: 810-342-7143 Office:810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services, and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Israel, John R. Sent: Thursday, July 30, 2009 11:03 AM To: 'U2 Users List' Subject: Re: [U2] Slow selects The first pass on a file has to start from scratch. Once it has been read and is still fresh in memory, a 2nd pass will run much faster. John Israel Sr. Programmer/Analyst Dayton Superior Corporation 721 Richard St. Dayton, OH 45342 937-866-0711 x44380 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kebbon Irwin Sent: Thursday, July 30, 2009 10:58 AM To: u2-users@listserver.u2ug.org Subject: [U2] Slow selects We are running Unidata 7.1 on a linux box (Red Hat Enterprise Linux ES release 4 (Nahant Update 4) Kernel 2.6.9-42.ELsmp on an i686). For some reason I cannot fathom, the first time you perform any Uniquery command on a file it takes forever to return. Say, 5 minutes. Subsequent queries on the same file, with different criteria (or the same), take seconds, say 10. This is generally on static, hashed files with lots of level 1 overflow, but no level 2 overflow. This is a pretty fast environment with this weird exception. Any ideas about what the problem might be? Thanks, Kebbon ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] the XML/DB Tool
001|111|011|111 store Symeon Breen wrote: Yes - reminds me of some programmers i know who if they do not have a point and click interface and the ability to draw lines between tables, and tick boxes etc are incapable of any sql, and who are also incapable of coding anything outside of VS. Lol One of my pet hates i suppose is that toolsets make certain things like web services and xml easy for anyone to do - but should these people be doing it if they do not understand what it is they are doing ??? :) -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Ross Morrissey Sent: 30 July 2009 09:24 To: U2 Users List Subject: Re: [U2] the XML/DB Tool Hardcoding and Handcoding are subtly different. On Thu, Jul 30, 2009 at 12:59 AM, Symeon Breen syme...@gmail.com wrote: Just one point - where you say hard code - I am not entirely in agreement - my xml handling code is generic for all the xml file i suck into u2, the EXT file is different for each xml file - it is in a way an addendum to the xsd and allows you to only import parts of the xml. If this is what you call hardcoding then i disagree it is a configuration. If however you are hardcoding your databasic for each xml file then yes that is hardcoding and i agree you should not do it that way ! Symeon. ___ 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 ___ 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
[U2] Fwd: What ETL?
Oops, this was intended for the list (does a reply no longer go to the entire list?). --dawn -- Forwarded message -- From: Dawn Wolthuis dw...@tincat-group.com Date: Thu, Jul 30, 2009 at 2:23 PM Subject: Re: [U2] What ETL? To: Mecki Foerthmann mec...@gmx.net On Thu, Jul 30, 2009 at 2:06 PM, Mecki Foerthmannmec...@gmx.net wrote: 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. I agree that MITS would have been the better choice. Given that the company bought Cognos, it would not cost much more to get the U2 version of DataStage. Just find the right sales folks to talk to at IBM (not the Cognos folks, they are unlikely to be clued in on this) 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. It sounds like you already have the background with SQL and ODBC, so that puts you ahead of the game there 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. Good deal, DTS will definitely be simpler to set up and get going with than DataStage, so this might be the best route, but I would at least get the quote for DataStage, which you could then use, I think (not sure) to go directly from UniData into the DataStage cubes, no need for another database in the mix if those cubes are handled by Oracle (or by Cognos, they might have something proprietary too, forgetting). 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. Yeah, the biggest concern I would have with your current approach, which I think is reasonable enough, is that you are adding a third significant database product into the mix with SQL Server. Maybe your site already has plenty of SQL Server so that this is not a big deal, but if other divisions are reporting out of Cognos with Oracle cubes, you could do the same today rather than wait to move away from U2 (a shame, as that will be a costly move for the company, no doubt). 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. It is quite a skill to learn how to tune Oracle and Cognos as a team. If you were to look at such queries coming out of MITS or Informer against UniData and compare that to the BFD in trying to tune Cognos/Oracle cubes, well, it just seems a shame that the industry has moved us backward so far with some of these tools. The Cognos server already gets hammered at end of month time, and it will get worse the more divisions go live on Glovia.com. Will you be using Cognos with SQL Server then, or using MS BI tools? Just curious (and headed out of town, so if I don't reply to anything quickly...). Good luck. --dawn -- Dawn M. Wolthuis Take and give some delight today
Re: [U2] PHP vs Java
Thanks to Kevin and Ross for responding. I was surprised to learn that Java does not have built-in functionality to support dynamic arrays although a little method can be written to handle it. The battle inside my head continues... --B -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Wednesday, July 29, 2009 9:21 PM To: U2 Users List Subject: Re: [U2] Using PHP To start, I'll echo Ross. PHP is great for sessions and all manners of web-type programming. Unlike Java - an otherwise excellent language which tends to require some complex-ish web server setup - PHP is much more lightweight and plugs into just about any web server quickly and efficiently. The language is purportedly derived from Perl but syntactically I find it closer to the original ANSI C++ with just a touch of Java-isms to keep it friendly. It's easy (dare I say fun?) to learn, easy to read, easy to deploy, and can be very maintainable (though of course individual mileage in this department varies per individual - as with all languages). There are all sorts of frameworks and infrastructures available for PHP but one doesn't have to use anything more than an editor to get started. The documentation is well written (I find it much more understandable than most) and there are plenty of books available with code samples to get one moving in the right direction. PHP can install with lots of different modules from SQL connectors to crypto libraries, ZIP and PDF creators and extractors, and a blindingly large array of other features as well. And if that's not enough there's a whole boatload of other user-contributed libraries available via PEAR. Generally speaking, (well written) PHP code performs very well, it's a reasonably mature language with a good object model (as of PHP5) but where it really shines is in passing data to and through a web server and managing sessions. I've also used it for *nix shell scripting for administration types of things and even had the pleasure of writing a NAGIOS plugin with it. Come to think of it, I've used it for all sorts of ad-hoc data analysis on my Windows box as well when awk was just a little too ... awkward? (pun intended, of course) I've heard PHP is the #2 language in the world behind C++, but I don't know definitively that's entirely reliable. That said, it does seem like it's gaining momentum because it's so flexible and pervasive. The fact that it can be used beyond the web and on nearly every platform imaginable without additional hardware or software, well, that has to account for something. The only downside to PHP in terms of U2 is that IBM has so far refused to create any kind of native connector. The UO connector works but it requires something of a walk on the wild side to mitigate some weirdness in the dynamic array extraction methods. Yeah, there's that TechConnect article which describes rolling your own, but even I'm not THAT geeky. Finally, FREE is always a good price, especially when you get so much with PHP. -K ___ 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] PHP vs Java
Wouldn't the ArrayList work? It has add and get methods plus a toArray method. Brutzman, Bill wrote: Thanks to Kevin and Ross for responding. I was surprised to learn that Java does not have built-in functionality to support dynamic arrays although a little method can be written to handle it. The battle inside my head continues... --B -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Wednesday, July 29, 2009 9:21 PM To: U2 Users List Subject: Re: [U2] Using PHP To start, I'll echo Ross. PHP is great for sessions and all manners of web-type programming. Unlike Java - an otherwise excellent language which tends to require some complex-ish web server setup - PHP is much more lightweight and plugs into just about any web server quickly and efficiently. The language is purportedly derived from Perl but syntactically I find it closer to the original ANSI C++ with just a touch of Java-isms to keep it friendly. It's easy (dare I say fun?) to learn, easy to read, easy to deploy, and can be very maintainable (though of course individual mileage in this department varies per individual - as with all languages). There are all sorts of frameworks and infrastructures available for PHP but one doesn't have to use anything more than an editor to get started. The documentation is well written (I find it much more understandable than most) and there are plenty of books available with code samples to get one moving in the right direction. PHP can install with lots of different modules from SQL connectors to crypto libraries, ZIP and PDF creators and extractors, and a blindingly large array of other features as well. And if that's not enough there's a whole boatload of other user-contributed libraries available via PEAR. Generally speaking, (well written) PHP code performs very well, it's a reasonably mature language with a good object model (as of PHP5) but where it really shines is in passing data to and through a web server and managing sessions. I've also used it for *nix shell scripting for administration types of things and even had the pleasure of writing a NAGIOS plugin with it. Come to think of it, I've used it for all sorts of ad-hoc data analysis on my Windows box as well when awk was just a little too ... awkward? (pun intended, of course) I've heard PHP is the #2 language in the world behind C++, but I don't know definitively that's entirely reliable. That said, it does seem like it's gaining momentum because it's so flexible and pervasive. The fact that it can be used beyond the web and on nearly every platform imaginable without additional hardware or software, well, that has to account for something. The only downside to PHP in terms of U2 is that IBM has so far refused to create any kind of native connector. The UO connector works but it requires something of a walk on the wild side to mitigate some weirdness in the dynamic array extraction methods. Yeah, there's that TechConnect article which describes rolling your own, but even I'm not THAT geeky. Finally, FREE is always a good price, especially when you get so much with PHP. -K ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] PHP vs Java
Yes... It looks like it would. Thanks Jeff. I will plan to reVisit the Morris County Library to find the textbook that I found the java source code for the method to handle dynamic arrays... and try to determine why ArrayList was not mentioned. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Powell Sent: Thursday, July 30, 2009 6:30 PM To: U2 Users List Subject: Re: [U2] PHP vs Java Wouldn't the ArrayList work? It has add and get methods plus a toArray method. Brutzman, Bill wrote: Thanks to Kevin and Ross for responding. I was surprised to learn that Java does not have built-in functionality to support dynamic arrays although a little method can be written to handle it. The battle inside my head continues... --B -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Wednesday, July 29, 2009 9:21 PM To: U2 Users List Subject: Re: [U2] Using PHP To start, I'll echo Ross. PHP is great for sessions and all manners of web-type programming. Unlike Java - an otherwise excellent language which tends to require some complex-ish web server setup - PHP is much more lightweight and plugs into just about any web server quickly and efficiently. The language is purportedly derived from Perl but syntactically I find it closer to the original ANSI C++ with just a touch of Java-isms to keep it friendly. It's easy (dare I say fun?) to learn, easy to read, easy to deploy, and can be very maintainable (though of course individual mileage in this department varies per individual - as with all languages). There are all sorts of frameworks and infrastructures available for PHP but one doesn't have to use anything more than an editor to get started. The documentation is well written (I find it much more understandable than most) and there are plenty of books available with code samples to get one moving in the right direction. PHP can install with lots of different modules from SQL connectors to crypto libraries, ZIP and PDF creators and extractors, and a blindingly large array of other features as well. And if that's not enough there's a whole boatload of other user-contributed libraries available via PEAR. Generally speaking, (well written) PHP code performs very well, it's a reasonably mature language with a good object model (as of PHP5) but where it really shines is in passing data to and through a web server and managing sessions. I've also used it for *nix shell scripting for administration types of things and even had the pleasure of writing a NAGIOS plugin with it. Come to think of it, I've used it for all sorts of ad-hoc data analysis on my Windows box as well when awk was just a little too ... awkward? (pun intended, of course) I've heard PHP is the #2 language in the world behind C++, but I don't know definitively that's entirely reliable. That said, it does seem like it's gaining momentum because it's so flexible and pervasive. The fact that it can be used beyond the web and on nearly every platform imaginable without additional hardware or software, well, that has to account for something. The only downside to PHP in terms of U2 is that IBM has so far refused to create any kind of native connector. The UO connector works but it requires something of a walk on the wild side to mitigate some weirdness in the dynamic array extraction methods. Yeah, there's that TechConnect article which describes rolling your own, but even I'm not THAT geeky. Finally, FREE is always a good price, especially when you get so much with PHP. -K ___ 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 ___ 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?
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
Re: [U2] PHP vs Java
Bill, I don't have the javadoc for the dynamic array class provided by uniobjects handy but that may do it. I don't think a simple ArrayList will do it, primarily because it won't have insert capability. Building a dynamic array in java is certainly possible but has some interesting cases to handle. Regards, Adrian On Fri, Jul 31, 2009 at 10:39 AM, Brutzman, Bill bi...@hkmetalcraft.comwrote: Yes... It looks like it would. Thanks Jeff. I will plan to reVisit the Morris County Library to find the textbook that I found the java source code for the method to handle dynamic arrays... and try to determine why ArrayList was not mentioned. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Powell Sent: Thursday, July 30, 2009 6:30 PM To: U2 Users List Subject: Re: [U2] PHP vs Java Wouldn't the ArrayList work? It has add and get methods plus a toArray method. Brutzman, Bill wrote: Thanks to Kevin and Ross for responding. I was surprised to learn that Java does not have built-in functionality to support dynamic arrays although a little method can be written to handle it. The battle inside my head continues... --B -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Kevin King Sent: Wednesday, July 29, 2009 9:21 PM To: U2 Users List Subject: Re: [U2] Using PHP To start, I'll echo Ross. PHP is great for sessions and all manners of web-type programming. Unlike Java - an otherwise excellent language which tends to require some complex-ish web server setup - PHP is much more lightweight and plugs into just about any web server quickly and efficiently. The language is purportedly derived from Perl but syntactically I find it closer to the original ANSI C++ with just a touch of Java-isms to keep it friendly. It's easy (dare I say fun?) to learn, easy to read, easy to deploy, and can be very maintainable (though of course individual mileage in this department varies per individual - as with all languages). There are all sorts of frameworks and infrastructures available for PHP but one doesn't have to use anything more than an editor to get started. The documentation is well written (I find it much more understandable than most) and there are plenty of books available with code samples to get one moving in the right direction. PHP can install with lots of different modules from SQL connectors to crypto libraries, ZIP and PDF creators and extractors, and a blindingly large array of other features as well. And if that's not enough there's a whole boatload of other user-contributed libraries available via PEAR. Generally speaking, (well written) PHP code performs very well, it's a reasonably mature language with a good object model (as of PHP5) but where it really shines is in passing data to and through a web server and managing sessions. I've also used it for *nix shell scripting for administration types of things and even had the pleasure of writing a NAGIOS plugin with it. Come to think of it, I've used it for all sorts of ad-hoc data analysis on my Windows box as well when awk was just a little too ... awkward? (pun intended, of course) I've heard PHP is the #2 language in the world behind C++, but I don't know definitively that's entirely reliable. That said, it does seem like it's gaining momentum because it's so flexible and pervasive. The fact that it can be used beyond the web and on nearly every platform imaginable without additional hardware or software, well, that has to account for something. The only downside to PHP in terms of U2 is that IBM has so far refused to create any kind of native connector. The UO connector works but it requires something of a walk on the wild side to mitigate some weirdness in the dynamic array extraction methods. Yeah, there's that TechConnect article which describes rolling your own, but even I'm not THAT geeky. Finally, FREE is always a good price, especially when you get so much with PHP. -K ___ 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 ___ 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 ___ U2-Users mailing list U2-Users@listserver.u2ug.org
