Table Joins? DB or Conforming Layer?

[kado]

Within a typical 3 layer design (DB, conforming, presentation) ... outside of isolation from the underlying DB (changes etc.) are there any other reasons to put table joins in the conforming layer? Could there also be a performance impact?

thanks,
kado

[MFGF]

Generally you should try to avoid adding relationships to the second (conforming) modelling tier, as these will reduce the ability to fully minimize queries at runtime.  If possible, keep all relationships in the foundation (data source) layer.

MF.

[kado]

Interesting. I always thought best practice was to put the relationships in the 2nd tier (business or conforming or whatever). Thanks for the feedback and let me know if you have any other thoughts!

THANKs,
kado

[rockytopmark]

Cognos preaches to use the middle or "Conforming" Layer as you call it, for the joins, and Determinant settings.

My view as it gives the model a little more portability and eliminates the need to use shortcuts when creating multiple Role-Playing dimensions.  I have not seen any performance issues at all, and as MF warns, the minimized SQL feature is still active as long as the data source queries (Physical layer) are built with SELECT * FROM... format.

My colleagues have discussed this at length and this approach was standardized for these and possibly a few other reasons, I can't think of at the moment.

[MFGF]

Cognos preaches to use the middle or "Conforming" Layer as you call it, for the joins, and Determinant settings.

Not any more.  If you can get hold of the notes from any of their 8.3/8.4 modelling courses over the past couple of years, you will see they advocate resolving all the issues and traps and defining all the required relationships in the lowest (Foundation Objects) tier, and building the second (Consolidation) tier on top of this, comprising model query subjects with no relationships - although still emulating Fact and Dimension tables in a star-schema modelled database.  This is simply to provide a layer of abstraction and consolidation of snowflake structures into stars.  They made this change because they realised that as soon as modellers began to add relationships to the model query subjects in the middle tier, this changed the SQL query generation from 'minimized' to 'as view' (ie the queries were less able to be fully minimized at runtime).  I had some interesting debates over how this puts an extreme focus on query efficiency but makes the model less intuitive and harder to maintain, but all to no avail.

Regards,

MF.

[tupac_rd]

for some reason, the cognos consultants who came onsite always asked us to do the joins at the database layer level, since ReportNet 1.1. So, when I read rock's comments, I was kind of surprised, and MF's comments are more surprising that Cognos started recommending so, only from 8.3...

[MFGF]

MF's comments are more surprising that Cognos started recommending so, only from 8.3...

Not exactly - I was referring to the fact that the Cognos training courses were changed to reflect this strategy, not that Cognos did not recommend this before the 8.3 release.  When ReportNet was first released, there was no real information about best practice guidelines included in the training course materials. The initial Cognos 8 training materials improved on this, but did not fully reflect the best practice guidelines until they were revamped for the 8.3 release.  This is just their course materials, though - the modelling guidelines have been in place since the days of ReportNet.

Regards,

MF.

[CognosPaul]

Are there any differences between the framework versions that prompted this strategy change?

[MFGF]

Are there any differences between the framework versions that prompted this strategy change?

Not really.  The introduction of Cognos 8 saw the Determinants feature being surfaced in the UI - prior to this, the coding was still there, but buried within model.xml.  This change had no impact on query minimization and how it is impacted by defining relationships between model query subjects though.

Regards,

MF.

[Alp]

I am still confused on the best practice recommendations regarding 3 layers.
Let me share my thoughts and doubts:
1. Physical layer - import from DB. Physical tables and no any new query subjects.
2. Second layer to maintain query subject and query item names, merge query subjects to consolidate query items etc.
3. Presentation layer - local view as namespaces or folders based on shortcuts.

If the above said valid, I need to create copies of query subjects in the second layer.
Now the real problem in my project is that if I were to resolve all the relationship loops through snowflake model, I end up with about 500 shortcut aliases (in the database I have ~ 20 tables).
Having ~ 500 Query Subject in flat list is not fun :-(

Another way is to leave some relationship loops and reduce number of query subjects to manageable number, and then resolve the loops by putting only required query subject in the presentation layer so the ambiguous joins are left behind in the level below.

The bottom line is that for my case I can not see much help from the middle layer unless I keep joins on the level.

Any thoughts on the topic is much appreciated.
Thanks,
- Alp

[sir_jeroen]

I'll share you my experiences on this.
Since Cognos introduced determinants I develop my frameworks using the following principles:
- Database layer: NO joins except for query subjects which are combined into 1 query subject in the Business layer (e.g. Product Line, Product Type and Products are joined because in my business layer they form a single dimension); No determinants are set except for the unique keys;
- Business layer: Create joins between all Dimensions and their fact tables. Only joins are created that are necessary and relevant . This way of building gives me all flexibility to reuse objects, create joins which differ from the original join type (eg. 0..n -> 1..n), I can utilize the determinants to their max without the posibility that they influence one another. I also get predictable results because I know how the determinants are set and what joins there are.
- Presentation layer: Star schema groupings of all fact tables.

[Alp]

...
- Business layer: Create joins between all Dimensions and their fact tables.
...

Do you have any opinion on creation joins between 2 shortcut aliases?
If YES, how do you push these shortcuts to presentation layer?

You may have a valid question why I need joins between shortcuts. Here is background:
1. I have an org. structure (4 levels) in the relation database (not cube). This org. structure nodes makes one of the dimensions.
2. Some measures of the fact table are not additive, so having measure values 2 and 2 on two leaf level nodes may make it 3 on the parent node instead of 4.
3. p.2 means that I have to store facts for each node on each level.
4. Now I have two options:
      A) create query subject aliases for each level of the org. structure.
      B) create shortcut aliases for each level of the org. structure.
Now, 4.A means maintenance overhead and 4.B means that I need to create joins between shortcut aliases (that is why I have my question above).

Sorry for long explanation, but I am curious how people deal with org structures and not additive measures for them.

Thanks,
- Alp

[sir_jeroen]

What kind of shortcuts are you using alias or reference shortcuts? And why use shortcuts and not copies of the referenced object? Personally I prefer to use copies instead of shortcuts because it gives me more flexibility and more control (i know: it generates more work for me but that's not so important in relation to all the control I get over the dimension). In regards to point 2: Are the facts of different granularity? If so, this might be solved with determinants.

[Alp]

What kind of shortcuts are you using alias or reference shortcuts? And why use shortcuts and not copies of the referenced object? Personally I prefer to use copies instead of shortcuts because it gives me more flexibility and more control (i know: it generates more work for me but that's not so important in relation to all the control I get over the dimension). In regards to point 2: Are the facts of different granularity? If so, this might be solved with determinants.

Thanks for the reply!
As I mentioned above I used alias shortcuts and not reference.
The reason I wanted to proceed with aliases that the way I saw my showflaking grow I wanted to avoid maintaining ~ 50 copies of the same query subject.
I think I found another way to deal with my model so I do not need so many copies now, which is why I go with query subject copies.
However I am still interesting about practice of working with alias shortcuts because this is what Cognos doc suggests to use for resolving loops.

Second part is more important. You are right I have facts for different granularity.
This is relation model source (not Cognos Cube). The requirements is to use the model from Report Studio and one of the
reports could be:

Org Node Name Level 1 | Org Node Name L 2 | Org Node Name L 3 | some facts |

or

Org Node Name Level 1 | Org Node Name L 2 | some facts |

etc.

[JGirl]

I've always used the following approach when building a model from a star schema DW...
First Layer: DB query subjects, and only the joins required to flatten snowflaked dimensions
Second Layer: Model query subjects, pure facts and dimensions, with the star schema relationships and determinants set
Third Layer (only reqd if building a DMR model):  DMR views of the second layer's query subjects, and scope relationships
Final Layer: Star schema groupings, with a namespace per fact

My question is, if I were to take the alternative approach of moving all the relationships to the first layer, in the second layer after you've created your fact and dimension query subjects, the 'create star schema grouping' option only creates a shortcut to the fact table, without the relevant dimensions (as the relationships are not in this layer).  How do you get around this?

[blom0344]

My 2 cents would be that the whole automatic 'Star-schema grouping' wizard is just that: a wizard. In purely functional terms you do not need it. It doesn't add anything valuable to the model at all. In other words : you can live without it just the same..

[tupac_rd]

My 2 cents would be that the whole automatic 'Star-schema grouping' wizard is just that: a wizard. In purely functional terms you do not need it. It doesn't add anything valuable to the model at all. In other words : you can live without it just the same..

I almost thought the same too, except that when we were on 8.3, if we would'nt use automatic 'Star-schema grouping', the query would choose the wrong path. But they fixed it since, I think starting 8.3 SP1 or 2 not sure.... So other than that reason, yes I agree we can live without it...

[RobsWalker68]

Hi,

I've always found it does very much depend upon the type of underlying data source you are acting with. 

If the underlying data source is a star schema with no snow flaking then we tend to make the joins in the business layer instead of the db layer.  For query predictability the 'as view' concept seems to make more sense.   On the other if you are operating against an operational source then perhaps minimised SQL would be of more importance and the joins should be made an the database layer.

Kind Regards


Rob