Re: Infromix & Data Warehousing Solutions

Dough Metacube Query Optimizer is a tool that as you may Know, is targeted to analyze a specific Multidimensional Model, it actually creates a Matrix for every combination from the Model and based on some methodology will determine what will be the TOP N (You tell it how many) Aggregate tables from that particular Model. AUTOMATIC: Let's assume, you have a dimension containing 4 level of hierarchies, it will tell you what levels within that hierarchy are the candidates to be combined against other hierarchy levels of other dimensions, it will consider of course the Cost Disk space for a particular combination. Actually I have been involved in two DataWarehouse Projects using Metacube 4.01, and of course I will let Warehouse Optimizer analyze the MODEL for me, and create the aggregate table, let's say the TOP 10. Then after created them with their respective indexes (This feature is not available for automatic creation in this version), I will run update statistics High, Medium .... (Accordingly to some tips for performance and tuning). AUDIT: Just after I have introduced the end-users the model, they started quering information, by that time, some queries were running slow ( 5 minutes ), even when the schema was fragmented and update statistics were just fine. In those cases I run the Metacube Warehouse Optimizer and Let it Know about the existence of my Audit File. Using that audit file, Optimizer will create the necessary aggregate tables for that particular queries, of course you should trade-off between the ocurrence of those queries with the disk space the new aggregate will consume. Regards, My .01 Cents Worth. Mario Estrada -------------------------------------------Reply Separator-------------------------------- -----Original Message----- From: Doug Johnson <teabag@ma.deleteme.ultranet.com> To: informix-list@iiug.org <informix-list@iiug.org> Date: Jueves 27 de Agosto de 1998 08:38 PM Subject: Re: Infromix & Data Warehousing Solutions >Tim, > >Thanks for the info but we're not on the same page. I'm not talking about >the Informix query optimizer (access plan shown by 'explain plan'). There's >tons of info on www.informix.com on that optimizer. I'm talking about the >new MetaCube Warehouse Optimizer. My understanding is that it: >.. takes the 'audit' info from MetaCube about existing queries. >.. creates an analytical performance model (methodology unknown) >.. adds hypothetical aggregate tables to the 'schema' >.. re-evaluates the model to generate estimated query elapsed times. > >Dr. Chandra presented some nice plots that showed elapsed times decreasing >(and leveling off) and storage requirements increasing (and leveling off) as >N aggregates were added. She said that the next version would also recommend >some number of indexes with the same performance vs. storage trade-offs. >Given the difficulty of designing data warehouses and marts, this seems like >it could be an incredibly useful tool. I was just wondering if anyone has >used it. > >> The optimizer can only be as good >>as your best implementation of the data base. So all that stuff >>about fragmentation, disk layouts, and classic table design are really >>important. If not done right, no optimizer on either Metacube or XPS >will >do much good. > > > >