Determinants on Fact Tables

[blom0344]

(1) Determinants and joins in model layer:

Determinants set:

List: okay   Crosstab: okay

Determinants not set:

List: bloated   Crosstab: bloated

(2) Determinants and joins in data layer:

Determinants set:

List: okay    Crosstab: okay

Determinants not set:

List: okay    Crosstab: okay

My conclusion would be that both list and crosstab react the same.

Interestingly , when I have  joins and determinants on the datalayer, then adding the same determinants to the models in  the model-layer gives me a warning in Report Studio about those determinants will be ignored due to missing joins 

[cognostechie]

Interestingly , when I have  joins and determinants on the datalayer, then adding the same determinants to the models in  the model-layer gives me a warning in Report Studio about those determinants will be ignored due to missing joins 

I guess because the joins were in the Data layer and the determinants were in the model layer..
I don't need to put it in the Model layer because my joins are in the Data layer and putting determinants there works fine.

By the way, the answer to the question of the Original Poster (in case he/she still wants it):

His question -

Does anyone know of any 'official' documentation that supports this?  Does anyone have an argument for adding determinants to dimensions as a default even when they are just flat hierarchies and the facts always join at the lowest grain?

Yes, I have official documentation (book which Cognos publishes and hands out in their training class). It says Determinants should be added to dimensions in case of multiple granularities between facts. In the training whenever they used flat hierarchies and no fact to fact joins, they never used determinants.

It also says Determinants are a way to tell Cognos engine how the indexes in the database are constructed and what makes the data unique.

Our tests show that you need determinants on fact tables in case of a fact to fact join when you have one to many relationship between those facts. 

[blom0344]

Yes, I have official documentation (book which Cognos publishes and hands out in their training class). It says Determinants should be added to dimensions in case of multiple granularities between facts. In the training whenever they used flat hierarchies and no fact to fact joins, they never used determinants.

It also says Determinants are a way to tell Cognos engine how the indexes in the database are constructed and what makes the data unique.

Our tests show that you need determinants on fact tables in case of a fact to fact join when you have one to many relationship between those facts.

This actually confirms what I have found in my training material. However, no explicite statement when you should not define them..

fact to fact seems to be somewhat inconclusive (as I pointed out in the examples I gave) However , this is a bit academic. Where ever they are defined, the result is correct.. 

[cognostechie]

However, no explicite statement when you should not define them..

It says -

There are very limited cases when you would need or want to use determinants. They include:

1> When you have joins on a single query subject with multiple levels of granularity
2> When you have BLOB data type in the query
3> To improve performance of a DMR

Doesn't say anything about fact-to-fact join but then they don't advice to have that kind of a join to begin with.

[blom0344]

One does choose to define such joins. :( Unfortunately many databases are just to far from the ideal Kimball structured examples Cognos uses in its training material. The sales order header / order detail is just an example. In our designs we have to cope with facts in almost every table. Makes it a real challenge..

[cognostechie]

Yeah sure. I completely agree. I have fact-to-fact joins too in many cases.  I was just mentioning that from an answers perspective (to answer the OP) about whether or not Cognos has that in the document.

[josepherwin]

If your TA is so determine to put determinant (sounds like poetry already!) on Fact tables, I'm guessing there should not be a problem as long as performance is not impacted.

From some of the training that I attended for Cognos, one of the lecturers suggest that "I do not care how model is designed or reports developed as long as I can get my report as quickly as possible!". In her analogy, she said that the FM model may not be following Cognos standards, and Reports may be designed in a very poor way, BUT, if it ran in < 1 minutes, then it is not a problem!

So in relation to your scenario, just put the determinants on the Fact tables as long as it does not have any impact on performance. However, once impacted, then try to remove the determinants and see if it improved or not. Once improved, you can document your findings, and shove it to your TA.

[blom0344]

My first reaction would be to heap praise upon such a down to earth approach. If the report is fast enough nobody cries wolf against the model underneath.

Couple of remarks though:

(1) Neatly modelled means also much easier to read SQL and therefore less time tracking/solving issues.

(2) Development is often on small testdatabases. With production databases growing over time you may end up in trouble remarkably fast

(3) Adding / losing determinants is best monitored from the database. If different SQL is generated (traces!!) then you have a case, as this may impact performance

[MFGF]

From some of the training that I attended for Cognos, one of the lecturers suggest that "I do not care how model is designed or reports developed as long as I can get my report as quickly as possible!". In her analogy, she said that the FM model may not be following Cognos standards, and Reports may be designed in a very poor way, BUT, if it ran in < 1 minutes, then it is not a problem!

That's a very cavalier attitude to take, especially to modelling.  The best practice standards were developed to ensure accurate, consistent reporting while still achieving optimum performance within these constraints.  I would (personally) argue that the top priority for any reporting solution is to deliver correct information - if performance is put above this, then you end up with the potential to deliver wrong results very quickly! :)

Was your course run by IBM or by a third party?  I honestly can't imagine IBM would be too impressed if they knew one of their instructors was giving out advice like that.

Regards,

MF.

[Lynn]

I guess I fall on the side of following the best practices as much as possible. In addition to the reasons stated, I would add the following:

• Models tend to evolve. Changes and additions may come down the pike and can be difficult to incorporate in something that isn't properly built. Over time you've got a frankenstein on your hands.
• A BI solution is not just a report or even a few reports. If you take a micro-view of "as long as I can get MY report as quickly as possible!" then you may not be modeling your database or your Framework Model to quickly and accurately deliver the information needed to cover all the business questions thrown at it.
• Many developers suck at documentation. They may understand what they've done and how to get it to behave, but the ramp up time for someone else to cover for them or takeover can be longer.

However, none of us get the luxury of having all the moon and the stars aligned. We need to balance perfection with practicality. We may need to do things that are a bit off the normal course, but I would advise judicious use of non-standard approaches after having considered the pros and cons. And also ensure things are documented and discussed.

Going back to the original question about determinants it seems to me there ought to be some justification for having them or not having them beyond "so-and-so made me put them in there". Either they are needed for correct results and/or performance or they're not. I think a lot of good information in this thread illustrates methods to make a determinant determination.

[MFGF]

...so you're saying, Lynn, that Ties can now determine a Determinant determination :D  I'll add that to my list of replies in the pub to the question "so what did you do today?" ;)

[Lynn]

hee hee. Determining the determinant determination is par for the course in the department of redundancy department.

I think Ties as well as Techie shed an awful lot of light on things although I think the original poster is long gone.

[josepherwin]

That's a very cavalier attitude to take, especially to modelling.  The best practice standards were developed to ensure accurate, consistent reporting while still achieving optimum performance within these constraints.  I would (personally) argue that the top priority for any reporting solution is to deliver correct information - if performance is put above this, then you end up with the potential to deliver wrong results very quickly! :)

Was your course run by IBM or by a third party?  I honestly can't imagine IBM would be too impressed if they knew one of their instructors was giving out advice like that.

Regards,

MF.

I think I probably put the wrong sentence there as she was exaggerating her point.

What I was trying to say is, in relation to this issue, I do not see a problem of putting a determinant in the fact table. Sometimes on top of your Cognos best practices, there are other business rules/scenario that may be specific to an organisation which may break the Cognos modelling standards.   :)