Hello
thought I’d pass this by you all to see if theres anything potentially crazy in what were doing. I work for an ISV that bundles BOBJ/universes with its product. For years our universes have been running against our product which is built on top of ProgressV9 database. Anyone who has any experience with SQL queries and Progress will know its like swallowing razor blades … its painfully slow at times. We decided to do a nightly extract to SQLServer and bingo … queries run like lightning. This is great as 90% of our customer reporting doesn’t need ‘live’ data. Last weeks data is good enough.
Problem is, with SQLServer having a slightly different dialect of SQL to Progress, we have to tweak the universes. This means there are slight differences between the SQL from corresponding universes. For commercial reasons we have to maintain the ability to use the ‘same’ universes against Progress and SQLServer.
From a development point of view, we develop the Progress universe then push it through a utility to SQLServerize the object/join/etc SQL. We then have two ‘twin’ universes. They both have the same files names, descriptions, objects, conditions etc but slightly different SQL behind them.
We have a single document domain and two separate universe domains in the same E6Repository(separate schema owners) … one a Progress universe domain and one a SQLServer universe domain. We also have two main user groups … ‘Progress’ universe users and ‘SQLServer’ universe users. They can only have access to their own domain so a SQLServer user won’t see the Progress universes and vice versa.
What this does allow is a report created by a Progress universe user can be sent to a SQLServer universe user. It can be opened up and refreshed no problem … the SQL regenerating to the correct dialect as appropriate. This is pretty important as we have potentially 1000s of Progress version reports that will need to run no problem against the SQLServer environment as people ditch the ‘slow & live’ and move over to the ‘fast & dayold’ way of doing things.
Has anybody else done this kind of thing and can anyone see any problems with it.
Cheers
Mike
mick66 (BOB member since 2002-10-16)