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
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
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.
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.