Re: Informix Data Warehouses
Naomi Walker wrote: > > We have been an Informix OLTP shop for many years, and are now in the > process of deciding on a database engine for our data warehouse (on a > Sun E10k). I have heard through the grapevine that there are > considerable problems with Informix that cause data to be lost, and > problems with its general administration (like lots of broken indicies). > > Is anyone out there running a data warehouse using Informix care to offer > me some feedback? > > Thanks! > > -- > Naomi Walker (aka N7FSA) | naomi@anasazi.com > | Phoenix, Arizona > ---------------------------------------------------------------------- > It's almost impossible to overestimate the unimportance of most things. Here's some feedback: Informix kicks ass. For example, today I loaded 17 GB of data in 39 minutes into XPS 8.21.UC1, on a 4-node IBM SP2 cluster using AIX. I unloaded the same data in just over one hour, to disk, for a test. Other tables with less data of course load a lot faster. This is in the context of a data warehouse, where we have weekly feeds of data coming from a mainframe. We don't load 17 GB every week, but we needed to make a few changes to one of our fact tables, and the unload went off without incident. The other tables we load weekly with your typical pipe-delimited load files, each between 100-500 MB,and the whole load and update takes between 17 and 21 minutes, for 6-10 tables. The load cycle includes a series of updates we perform on some of the dimension tables. Most of the tables load quite well in the 1-2 minute range. The updates are the most time intensive part of the process. Seriously, the XPS loader sucks data extremely well. Oracle doesn't even come close, and Sybase, please hand me a towel to wipe the tears from the laughter. *-) Regarding indexes, please talk to the Informix Advanced Technology Group folks first before talking to anyone else. There are some surprising things that are being done in this arena. Data Warehousing is a science unto itself. The XPS engine ain't finished yet, there are still some things that need to be implemented, but nothing really what I would call a show-stopper. It falls into a category by itself. I would laugh if someone told me they were using the 7.x product for a data warehouse. XPS also allows a one-node implementation, which we use by the way on a development box. XPS is a great choice especially if you are already an Informix shop. But please, do the math, and get the warehouse totally supported by management. This means considering the conversion efforts of your data as important as what we would immediately think about, that being loading data into the data base. You will want to buy software for this purpose, otherwise you'll have all kinds of people writing conversion programs. I've spent a majority of my time actually working on the conversion side. The loading is so fast that it's not that big of an issue, the conversions and prep time are the focus. And then there are those damn end users who will actually use the thing... Sigh... :-) Metacube is a nice animal but we really haven't dug into it too deeply. Metacube comes with a web interface, an Excel add-in, and of course the regular client. The web client looks exactly like the WinNT/9x client, and they even have ( Egad! ) an MS Transaction Server component mess I refuse to put on my workstation just because I don't trust TX server. There is also a Netscape plug-in for the web Metacube client to allow using Active X in Netscape. It's required if you go Netscape instead of Internet Explorer. This version of Metacube has a lot more going on than previous versions, and has a lot of promise. More on Metacube from those more experienced. XPS is ready for the data warehouse, but please go buy the book called "The Data Warehouse Toolkit", by Ralph Kimball, and get on board with the whole picture of data warehousing. It requires the up-front work to appreciate the XPS engine, and this really is important. You can get started at http://www.rkimball.com . You should consider seriously using something other than AWK or SED for your data conversions, and there are plenty of products out there expressly for the data warehouse. There is also the book "Parallel Systems in the Data Warehouse" by Stephen Morse, David Isaac, Prentice Hall (Sd); ISBN: 013680604X ; at Amazon.com. XPS gets a great write-up in this book compared to other architectures. If it's any consolation, XPS has renewed my belief that Informix actually has a plan. :-) Hope this is of some enlightenment... As always your mileage may vary.... Tim -- Tim Schaefer tschaefe@mindspring.com tim_schaefer@fpl.com www.inxutil.com timohtim@netscape.net