Joins created in FM are being ignored

[Tangerine]

 >:(

I can't seem to figure this out - so I am posting the issue here hoping some brilliant person will solve my issue!

Issue:
Query Studio & Report Studio IGNORE joins to dimension tables, if a field from that dimension table is not in my query. 

Fact   join on account #      Account Dim
1:1                                   1:M

If I pull over a field from the Fact table say account - I would EXPECT that I only get accounts that exist in the Dim because that join exists in the Model.  This is not the case - My result set shows all accounts in the fact regardless if it is an account in the Dim or not.

Can you make any suggestions other than always having some field in my Account Dim in my query with the fact?  I was under the impression that if the join exist in FM then it would be used to query regardless if it was used in the query or not...

Hope this was clear - if not happy to answer any questions... I am in a pickle here...

[Lynn]

>:(

I can't seem to figure this out - so I am posting the issue here hoping some brilliant person will solve my issue!

Issue:
Query Studio & Report Studio IGNORE joins to dimension tables, if a field from that dimension table is not in my query. 

Fact   join on account #      Account Dim
1:1                                   1:M

If I pull over a field from the Fact table say account - I would EXPECT that I only get accounts that exist in the Dim because that join exists in the Model.  This is not the case - My result set shows all accounts in the fact regardless if it is an account in the Dim or not.

Can you make any suggestions other than always having some field in my Account Dim in my query with the fact?  I was under the impression that if the join exist in FM then it would be used to query regardless if it was used in the query or not...

Hope this was clear - if not happy to answer any questions... I am in a pickle here...

What you are describing is minimized SQL. The generated query is based on elements required to satisfy the request.

Wouldn't all your facts have a corresponding account in the dimension table? Generally you don't want to lose facts through join drop out.

Also, your cardinality seems backwards. The query engine uses cardinality to determine which query subjects behave as facts and which behave as dimensions. A query subject that is on the "n" side of all relationships is deemed to be a fact. The link below touches on this but you if you haven't had formal training you might want to consider it. The user guide also provides a lot of useful information.

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.1.0/com.ibm.swg.im.cognos.ug_fm.10.1.0.doc/ug_fm_id19782bp-relational_modeling_concepts.html

[Tangerine]

Thank you very much for your response Lynn!! Sorry was late for me and did have a typo in terms of the join. This model and package are about 8 years old.

There are more accounts in the fact table then in the account dim. I only want to show the activity in the fact that has a corresponding account in the account dim. I would think a join in framework model to the account dim would limit the fact table this way.

I thought that it originally behave this way - is it possible a setting could have altered this?

[Invisi]

...
I only want to show the activity in the fact that has a corresponding account in the account dim. I would think a join in framework model to the account dim would limit the fact table this way.
...

Consider that when your data warehouse is modelled correctly, all fact rows are linked to a row in your account dimension. This is a vital part of data warehousing. They will be linked to a special row, having a meaning like "unknown account". If you want to exclude fact rows that are missing a real account, you have to filter out the special row of the account dimension.