Controlling the join path in a loop join

[CognosPaul]

I feel dirty asking this.


I have a model with three tables. A, B, and C.

Each table is connected to the other, a total of 3 joins.

When pulling data from any one table, only those two tables, and the associated join, should be used. However, if data is pulled from the three tables, the joins have to be A->B->C. So far I've seen the joins change based on the location of the items in the query. Does anyone have an idea how to force this? Merging the tables before the join is out of the question, since that would force a join even if it's not needed.

My gut instinct, different queries for the different joins won't work either; this model needs to work in QS, and the performance hit to the database with the wrong join would be disastrous.

Of course, building a proper DWH is out of the question, as that would be far too easy.

[blom0344]

Paul,  if you define a database view on  A & B instead of  merging within the Cognos model, then the db optimizer may be smart enough to only access the table A if no objects from table B are fetched.  I have tested this against SQL server with views that  were defined over 10-15 tables. Importing the view and building a model QuerySubject and subsequently running traces showed that the query executed on the DB touched only 1 table if no objects where selected from other part of the view def.
Actually this is the opposite effect I was always told by Oracle DBA's, so no idea if this is typically SQL server smartness.

[pumccg]

You could also consider making an alias of C, say C2, then join A to C2. This way there is no merging etc but your authors would need to know when to use C and when to use C2. The joins are then:

A-B
B-C
A-C2

[cognostechie]

If A is joined to B and B is joined to C, then A does not need to be joined to C because there is already a path. I am presuming that these joins are in the Database Layer, not the Business Layer and the query is set to use 'minimized SQL'. Cognos would only use those joins that are required so if a report is made with only one query subject then no joins would be used. If a report is made with A and C then it would join A to B and B to C. This way, it would always use a consistent join strategy.

If you could provide more details (like what tables you have, what are the join conditions), I would be happy to resolve this. I have done lot of modelling on top of source systems  (without a DWH).

Send me a PM or e-mail if you don't want to post the information here.

[pumccg]

If you have only created joins between A-B-C, then that is the recommended approach - you should not have loops in your joins as this can result in ambiguous query paths. However I was under the impression you actually needed to also have a direct join between A-C, as well as A-B-C. Thus by making an alias of C in the database layer, called C2, and joining it directly to A, then you can model the joins paths A-C2, as well as A-B-C etc and use either C or C2 when required in the reports. Again, your authors have to know when to use C and when to use C2.

[CognosPaul]

The DWH is still in the planning stages, and the architects are more used to BO where they can set up specific contextual rules with regards to how it builds the joins.

simplified:
Each customer in table A has one or more accounts in B. Each account has zero or more policies in C. The accounts table is a large table, containing millions of rows. As the model is going to be available in QS and Workspace, they're looking for a way of ensuring that the accounts table isn't included in the joins unless it needs to be.

Properly using indexes minimizes those issues.

They are also concerned about losing data if they have to go through an intermediate table. This type of issue is generally due to bad data. Why should any customer have policies without an account?

The bottom line is that Cognos is not BO, and using the product to its strengths is the way to go.  We're redesigning the DWH to make it more suitable for Cognos.

[pumccg]

If you want to ensure your DWH is most suitable for Cognos BI then the recommendation is to use Kimball-style physical star schema design. The Cognos query engine generates its queries based on what it interprets to be facts, and dimensions. When it sees multi fact queries across conformed dimensions it then generates its stitched queries, using coalesce statements and its own full outer joins to ensure you don't lose data. Have you seen the Cognos document "Guidelines to Modeling Metadata"? Many DBAs aren't happy with the way the Cognos engine "thinks" but if you accept how it does think, and design your DWH to suit it, it goes much better. If you don't, and then have to make it behave in Framework Manager by designing virtual star schemas, it behaves but is nowhere as fast as it could have been.

[CognosPaul]

Yes, I'm very familiar with Kimball's books. As I said, we're still in the planning phase of the model, and I'm trying to encourage them to use Cognos to it's strengths instead of BO's.

[cognostechie]

Paul -

A better way will be to make a Transformer Cube with Account and Policy in the same Dimension, Customer being the highest level followed by Account and Policy. An alternate hierarchy straight from Customer to Policy can be created in the same dimension avoiding the Account. That will let them look at it both ways.

If you have to do it in FM then the only time they would want to avoid the Account table is when the Policy does not have an Account. So I am presuming that you have Account Id and Policy ID in both the Account and Policy tables. I am also presuming that currently you have the joins like this:

Customer -> Account -> Policy -> Customer. So you also have Customer ID in all tables.

Well, not the best way to do it but based on the objective, you can do this:

1> Remove the join between Policy and Customer
2> Create a Query subject with a SQL that will select only those Policies that do not have an Account and call it Policies without Account.
3> Join this to Customer.
4> Modify the existing Policy Query subject to include only those Policies that have an Account

So your join will be :

Customer -> Account -> Policy
Customer -> Policy without Account

Now , in the Business layer, create a Query subject to show the new Policies without Account. The business will now make 2 queries, one for Policies with Accounts and one without Accounts.


 

[cognostechie]

I figured another way to make it behave like BO. The users can decide which join to use when.

1> Disconnect the join between Account and Policy
2> Disconnect the join between Customer and Policy
3> Create a folder in  the Business Layer, name it Joins
4> Right-click on the folder and select the icon that looks like filter
5> Name it Customer joined to Policy
6> Navigate to the Database Layer and select Cust-ID from the Customer, add =, select Cust-ID from the Policy

Now you have a filter with the expression Customer.Cust-ID = Policy.Cust-ID
Create another filter to join Account to Policy

Now the user can decide which join to use and use the filter accordingly.