multiple paths between fact and dimension tables.

[rajivgupta1107]

Hi.
I have two fact tables and two dimension tables.
Both fact tables are joined to both dimension tables (a loop is formed).
When i drag objects from both fact tables, i see two sql generated via each path of dimension tables.
How to resolve it?

Also if i drag objects from fact tables, how the results will be computed, i mean which query?

Regards

[bonniehsueh]

I also ran into a similar situation at a client.

There may be other ways to do this, but I made changes in Framework Manager. I created role playing dimensions off of the dimensions that were shared between the 2 facts. That ensures that FM knows which join path to take (doesn't get stuck in a loop)

After creating the role palying dimensions. Delete all unnecessary joins. It is imporatnt to make sure that each dimension only points to the associated Fact. If you don't delete the joins that automatically come when copying a query subject, you'll be back to where you started.

Fact A
Fact B

Shared Dimension 1
Shared Dimension 2

New Role Playing Dimensions:
Shared Dimension A1
Shared Dimension A2
Shared Dimension B1
Shared Dimension B2

Join FACT A to Shared Dimension A1 and Shared Dimension A2. Make sure all query subjects that are joined to this FACT are exclusive to this FACT.

Let me know if this works

Cheers!

[blom0344]

Check Cognos documentation on determinants, multifact queries, multigrain.

The solution proposed will work for queries that touch just one fact, it will not solve a multi-fact scenario..

[rajivgupta1107]

thanks all for your valueable suggestions.

I dont want to create an alias for each and every dimension table.So cant go for role playing diemnsions.

I am not having the cognos documentation access. It will be a great help if you can give some link for the documentation.

Please suggest how can i solve the problem ? Do cognos ask end user which path to follow in case multiple paths are there between fact tables.

[goose]

Hi rajivgupta1107

The documentation is installed with FM by default look in <cognos-install-dir>\webcontent\documentation.

In order to report off the tables correctly you will need to do the following:

1) You may need to set determinants on the dimensions (if fact tables are at different grains)
2) Make sure the cardinality is set correctly for all relationships
3) You may also need to create star schema groupings depending on your requirements

Depending on your report and if your fact tables are at different grains you will see two queries generated as cognos must first aggregate the data to the correct level before joining.

Cheers
Angus

[rockytopmark]

I believe you are getting the expected results with the 2 generated SQLs. 

I have found that creating 2 single fact queries in your Report Studio report, and then joining them together with a Join Query in the report, usually will garner better performance.  This alone leads me to believe that you are getting what is expected.