If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Relationship at data layer or business layer

Started by barrysaab, 16 May 2011 04:12:54 AM

Previous topic - Next topic

barrysaab

Which is very good and practical for relationship and cardinality,whether it should be at database layer or business layer.I don't see setting relationships in database layer is a good idea because when you create model query subjects at business layers it automatically overrides and you have to start afresh.isn't  double work when we set relationships at database layer.Please correct me.Thanks
Boy! Cognos getting on to me!!!

blom0344

The answer: It depends.  If you can accomodate for a model friendly database structure, then keeping joins at that level has its benefits. This is the best way for Cognos to generate minimized SQL / metadata caching. Analyzing report SQL is much easier, cause the SQL is much 'cleaner'. However, if you need to 'remodel' extensively, then model subject joins may be easier to maintain. We have both sorts, the latter works as well, though generating very large code in certain cases

cognostechie

I personally think setting relationship in the Databse later might lead to faster processing in some cases and it is definately much cleaner like Blom said (both for generating SQl and from an organising perspective). Also, if you have different attributes of the same dimension in different tables (Ex - Product Line, Product Type and Product in 3 different tables) and if you set up the joins in the Business Layer, that would mean you would be creating redundancies by first creating 3 model query subjects for each one ofthem, then setting up the relationships and then creating another model query subject to merge all of them. So you would be creating 4 instead of 1 if you set up the relationship in the Business Layer. It just clutters the model.

MFGF

I completely agree.  While restricting relationships to the database (foundation) layer seems less intuitive during the modelling process, it leads to more efficient runtime generation of SQL, which probably takes precedence over the ease of creation of the model.

Cheers!

MF.
Meep!