Multple star schemas - incorrect one being used

[captainkarisma]

Hi guys,

Anyone seen this before in FM 8.4

I have 10 facts in my database layer. I have created relationship joins at this layer.

In the presentation layer I have created
Star Schema 1(fact1) , Star Schema 2(fact2) ........  Star Schema 10(fact 10) etc

In report studio, when i create a simple list report off Star Schema 2(fact 2), just bring in 2 dimension attributes, checking the SQL, it lists SQL as coming "from fact1"  instead of "from
fact 2" as i would have expected.

It's driving me nuts. When I add certain dimensions from star schema 1, it does say "from fact 1" , but certain others say "from fact 2" .

I have launched context explorer and checked all my joins and they validate and I don't appear to have a problem with them

Any ideas?

Many thanks

[MFGF]

Hi,

When you say "just bring in 2 dimension attributes" do you mean you have no items from Fact 2 in the report?

Assuming you are using conformed (shared) dimensions, if you just bring in items from two dimensions and no fact, you will be in a situation where there are multiple, ambiguous join paths between those two dimensions - it could choose to link them via any one of the 10 fact tables you have.  The decision it takes is based on the names of the relationships you defined - whichever is the first alphabetical relationship name will be used in an ambiguous situation.

If you bring in at least one item from a fact table,then you are enforcing the fact and it's relationships to the dimensions to be used.

Regards,

MF.

[captainkarisma]

Hi MFGF,

Yes, no facts in the query.

I thought that when you create a star schema with shortcuts, then you create the context for that fact? So i was very surprised when it didnt take this context into account
But it could be choosing the alphabetical fact, sounds like it could be. Just confused as why, thats all

Thanks for your feedback thus far

[MFGF]

Hi,

The shortcuts are simply pointers back to the original objects elsewhere in the model.  In this case the shortcuts are not enforcing any kind of context though.  The query ends up being formulated based on the underlying dimensions, and these of course can be linked by any of the multiple fact relationships.  It's the names of the relationships that drive which one is used in that situation.

Regards,

MF.

[captainkarisma]

Hi,

Was just reading
http://publib.boulder.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_fm.10.1.0.doc/ug_fm_id11190create_star_schema_group.html

"Star schema groups also provide context for queries with multiple join paths. By creating star schema groups in the business view of the model, you can clarify which join path to select when many are available. This is particularly useful for fact-less queries"

I am using a fact-less query. It's not doing what the doco says but appears to be following your logic.  Hence my confusion....

[tupac_rd]

I think it is a bug in some versions of Cognos 8, we had the same issue when we moved from 8.3 to 8.4, and we had to create star schema groups for each fact. Although we don't have this issue for 8.4.1 .... But I guess to be on the safer side I guess we should use 'Star Schema Groups'. It gets tedious if we have to add a new dimension to all 10 facts, which already have a bunch of dimensions....