multiple table relationships

[sdf]

Hi,

Just wondering if it's possible to have 2 tables with 2 different relationship/cardinalities.

Example, there's a join requirement for tables A and B with cardinality 1..1, then another join requirement same tables but different orders and cardinalities B and A 1..n.

Is this possible? and should I create the join in db layer or business layer?

please help.. thanks!

[Invisi]

Sure it's possible, why not? I would wonder though where you would have a 1:1 cardinality. In my school of star schema modelling it's rare to say the least. Do you have an example of two tables where you have a 1:1 cardinality?

As for in which layer to do it, that is subject to which religious camp you choose to follow, is my experience. I am from the camp where you do it in the logical layer. This layer seems to be a bit useless as I start to see it now. Others will tell you to do it in the database layer. I am no fan of that, as it means you have to create aliases in your database layer. As these aliases don't exist in your database, I prefer not to do it. Otherwise why call it 'database' layer. As I say, others think differently about this.

[MFGF]

It has more to do with query minimization than preference. If you define relationships for model query subjects that span multiple data source query subjects, you get less-minimized SQL (and therefore the queries are not as efficient as they would otherwise be). Defining the relationships between your data source query subjects avoids this issue. :)

MF.

[sdf]

thanks for the inputs..

I have created the relationships for table A and B.
So I now have relationship1 with (A) 1..1 <--> 1..1 (B).
And relationship2 with (B) 1..1 <--> 1..N (A).

Running Model Advisor, it sees that tables A and B have multiple relationships with each other. No errors.
Now, creating the query subject in the business layer. How can I choose or specify which relationship is to be used?

thanks!

[MFGF]

thanks for the inputs..

I have created the relationships for table A and B.
So I now have relationship1 with (A) 1..1 <--> 1..1 (B).
And relationship2 with (B) 1..1 <--> 1..N (A).

Running Model Advisor, it sees that tables A and B have multiple relationships with each other. No errors.
Now, creating the query subject in the business layer. How can I choose or specify which relationship is to be used?

thanks!

Hi,

Although Framework Manager will allow you to create multiple relationships between two query subjects, it's something you really (really really) don't want to do! If you do that, when you bring in items from both query subjects when creating a query in a report, only one of the relationships will be used. Which one? Well, since the query engine has to use one or the other regardless, it chooses based on the name of the relationships - the first alphabetical choice is used. This means in reality only one relationship will ever be used when reporting - and from your description this isn't what you want?

The best practice for solving this is to create an alias shortcut for one of the query subjects (either A or B - whichever is more appropriate) and join to this using one of the relationships, while joining to the original query subject using the other of the relationships. This means you then have two versions of your query subject which you can present in your business layer - one for each behaviour you want to use. It is often referred to as "Role-playing dimensions" in the documentation.

Cheers!

MF.

[sdf]

As always. Thanks for your professional response.
This is very informative and clear. This answers my question on another post as well.

I just started to venture on FM. ;)

Cheers MFGF!

[Invisi]

It has more to do with query minimization than preference. If you define relationships for model query subjects that span multiple data source query subjects, you get less-minimized SQL (and therefore the queries are not as efficient as they would otherwise be). Defining the relationships between your data source query subjects avoids this issue. :)

MF.

Are you referring to snowflaking?

[Lynn]

Are you referring to snowflaking?

Minimized SQL is discussed in these two articles:

https://www.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_fm.10.1.1.doc/c_bp-minimized_sql.html

http://www-01.ibm.com/support/docview.wss?uid=swg21342107

I do believe, however, that minimized SQL does occur with joins between model query subjects ONLY when the model query subjects are identical to the data source query subjects. That is rarely the case in my experience which is why I would advocate joins in the physical layer.