Relations error in FM

[Raj]

Hi all,

Can we give more than two relations between two tables in frame work manager?


Thanks
sekhar

[MFGF]

Hi all,

Can we give more than two relations between two tables in frame work manager?


Thanks
sekhar

Hi,

You can define as many relationships between query subjects as you like. Framework Manager will happily allow you to do this. The important thing to note, though, is that under no circumstances should you do this. When querying two query subjects that have multiple relationships defined between them, the query engine will use *one* of them to perform the join. Which one? Well, it makes an arbitrary decision based on the name of each relationship it can see, and it uses the first alphabetical. Yes - you are reading this correctly - it uses the names you specify for the relationships to decide which one to use! Is that what you want? Almost certainly not, I suspect :)

If you want two query subjects to be linked via multiple items, create a single relationship that uses those multiple items rather than using multiple relationships. Alternatively, if there are multiple ways to connect two query subjects, create shortcut aliases of one of the query subjects and split apart the relationships so that one links to each.

Cheers!

MF.

[Raj]

Thanks MFGF for reply it is very useful to me.

[bus_pass_man]

Yes but you need to use aliases to make the role-playing unambiguous.   

There must be one and only one relationship between any pair of query subjects.  If there are many relationships (pk/fk stuff) in the database (which is called role-playing ), you need to create aliases for the query subjects so that the relationships are re-targeted to use those aliases.  You would use objects from those aliases where you want to have the query use those relationships.

Also one and only one possible path of relationships (aka query path) must exist between a query subject and a fact table.

The end goal is to have virtual star schemas, where your fact tables have been identified and your dimension tables (and snowflakes) have been identified.  There would be one and only one possible query path between any particular set of tables which are a dimension and a fact table.  A dimension can have relationships to any number of fact tables. 

This will allow your model to generate the SQL queries in a manner which is consistent and predictable.  If there isn't one and only one possible query path then the query engine can't know how to generate the sql so it arbitrarily picks the alphabetically first path it encounters, which may or may not be what you want and may or may not vary from locale to locale.

Reading stuff by Kimball et al and taking the Cognos training would be useful I think.  The term role-playing is industry standard. 

The sample models have some examples of that although there's unnecessarily convoluted stuff which just makes things confusing and nullifies the point of being an example.   The simplest and clearest example is the time role-playing.   If you follow the use of aliases in the query layer through the business layer model query subjects and to the dimension layer you might get a clearer idea.

[cognostechie]

Also one and only one possible path of relationships (aka query path) must exist between a query subject and a fact table.

A Query subject can be a representation of either a Fact or a Dimension or both merged together so what exactly does this mean ?

A dimension can have relationships to any number of fact tables. 

The same applies to Facts as well. A Fact table can have relationships with any number of Dimension tables. 

[bus_pass_man]

OK, here.
'one and only one possible path of relationships (aka query path) must exist between a dimension query subject and a fact table query subject.'
Happy?  Or would you have been happier if I had just restricted myself to the first paragraph as my answer and let the bugger hang?  The original question doesn't give me much evidence about the knowledge base of the person so I needed to drag in a ton of concepts in a limited space to provide sufficient context.

The same applies to Facts as well. A Fact table can have relationships with any number of Dimension tables. 

Yep. Said that.  Pay attention to what was written in first part of the paragraph viz the virtual star schema stuff. 

All in all considering it was bashed out in about 10 minutes that posting is not a half-bad summation of an important topic.

[cognostechie]

I know but the reason I caught it is because everybody is not as technically savvy as you and me ( me only with your permission ) so when the same thing is said multiple times in different ways then the meaning is not precise anymore and is left open to interpretation which can mislead the community. Your post indicates you have knowledge of the subject and what you wrote is correct in your own mind but it may not translate the same way into the mind of the OP. That being said , I am not trying to play the role of a moderator here nor trying to challenge you but just trying to clarify things so that the OP doesn't implement it the wrong way.  No intent of any offense !