Same universe ... different DBs

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 :uk: (BOB member since 2002-10-16)

Mike: This is an interesting scenario, and I think it would be better served (more discussion) in General Discussion than Supervisor. So I’ll move it there is a second… if you want to have it moved back to Supervisor, just post a note and any forum moderator can do that for you.

As to your actual question… do you use full client or webi for your reports? And if webi, which version?

I am quite confident that your schema (scheme? ;-)) will work great for full client and for webi 2.x users. But I’ve seen some posts (and have not had the time to fully investigate on my own yet) that imply you might run into some problems with Webi 6 native webi (WID) documents in this scenario. Thus, my question about your version…


Dave Rathbun :us: (BOB member since 2002-06-06)

My tuppence worth (or two cents worth - or less - you decide!)

This rings of the little used features of business objects / designer that provide ‘true’ hetrogenous queries.

You can see some of these when editing universe objects listed under the ‘functions’ pane of the object editor. Scroll down and you can see entries such as:

{fn year()}

My understanding of these was that they would ‘convert’ standard DB functions to the native SQL equivalent depending on the ‘Database Engine’ setting on the universe connection.

The ‘guts’ of this system is buried in the PRM SBO and RSS files found under the ‘Data Access’ subfolders of a BO installation - and is to a degree customisable.

Know they exist - YES, ever used them in anger - NO :wink:

So, your solution is probably more viable and/or elegant than the core BO implementation.

Also, something I kicked myself about lately…

You can, in supervisor, override the unvierse connection for any given set of users. So, in theory you can deploy the same universe to both your Progress and SQLS groups, with the appropriate connection automatically determined.

Hope that helps…


philmorris :uk: (BOB member since 2002-11-12)