Oops, this was intended for the list (does a reply no longer go to the entire list?). --dawn
---------- Forwarded message ---------- From: Dawn Wolthuis <[email protected]> Date: Thu, Jul 30, 2009 at 2:23 PM Subject: Re: [U2] What ETL? To: Mecki Foerthmann <[email protected]> On Thu, Jul 30, 2009 at 2:06 PM, Mecki Foerthmann<[email protected]> 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 _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
