Cognos Analytics join multiple tables

Question

I am working in Cognos Analytics 11.1.7.

I have a data module with three tables. Table 1 and 2 contain all the transactions we do, where table 1 contains the remitter's part of the transaction and table 2 contains the beneficiary's part of the transaction. I.e. one transaction is divided into two tables. Table 3 holds account numbers.

I want to create a report that shows the remitter's customer ID and account number. However, in some cases, the the remitter's account is missing. These customer ID's have unique customer ID's (Y instead of X). In those cases, I want the beneficiary's customer ID and account number. Consider the following three tables

Table 1: REMITTER

CUSTOMER_ID	ORDER_NR
1X	123
2Y	456
1X	789

Table 2: BENEFICIARY

CUSTOMER_ID	ORDER_NR
4X	123
6X	456
6X	789

Table 3: ACCOUNTS

CUSTOMER_ID	ACCOUNT_NR
1X	1111
2Y
3X	3333
4X	4444
5X	5555
6X	6666

What I want is basically the following report:

REPORT OF ALL TRANSACTIONS TODAY

CUSTOMER_ID	ACCOUNT_NR	ORDER_NR
1	1111	123
6	6666	456
1	1111	789

I have solved the CUSTOMER_ID column with a switch case:

CASE
    WHEN REMITTER.CUSTOMER_ID CONTAINS 'Y'
    THEN BENEFICIARY.CUSTOMER_ID
    ELSE REMITTER.CUSTOMER_ID
END

Now here's the problem, I can't create a join (relationship) between the column created above and the ACCOUNTS table since my own column lies directly "under" the data module (on the same level as the tables in the index list to the left). However, if I create a column "under" REMITTER table, I can't use the case calculation from above. Cognos gives med the following error:

The expression is not valid.

XQE-MSR-0008 In module "STACKOVERFLOW", the following query subjects are not joined: "REMITTER", "BENEFICIARY".

I have tried to circumvent the error by creating all kinds of joins between REMITTER and BENEFICIARY on ORDER_NR but Cognos keeps giving me this error.

I have also tried to make a "triangle" of joins, where REMITTER and BENEFICIARY are joined on ORDER_NR, REMITTER and ACCOUNTS are joined on CUSTOMER_ID and BENEFICIARY and ACCOUNTS are joined on CUSTOMER_ID. This doesn't work. However, when I delete either the REMITTER/ACCOUNT or BENEFICIARY/ACCOUNTS join, it works with the table I keep joined.

I am slowly losing my sanity here. Thanks!

Answer 1

What is the nature of the relationships between these entities?

That is a question which you should ask for everything in your model.

The pattern of that relationship drives the relationship between the objects in the model, which in turn drives what decisions you need to make in your modelling.

For example, is this a Bridge table situation? If so, you need to be aware of it so you can model appropriately.

In the end it falls back on Kimball:

• Identify the facts
• Identify the dimensions

I am assuming that the cardinality is beneficiary to remitter to account or remitter to beneficiary to account.

Put beneficiary and remitter into a view in the module, create a relationship between it and account, and delete the relationship between the middle table and account (so that the SQL will use the relationship which you created ).

I think putting the calculation into the table which is in the middle would also do the trick.

I can not say that I can map between your described 'triangle' of joined tables and a business purpose so I could not use that information to understand the entity relationship. Such a pattern of relationship is specifically identified as one to be identified and, as part of the Cognos proven practices, corrected. Because I can not identify if there truly is a business purpose to have such a triangle or not, I can not, and will not, describe the appropriate modelling actions as they are dependent on the business purpose of the relationships between the entities, which takes us back to St. Ralph.

Answer 2

Use queries.

In a report, create a query to resolve the remitter/beneficiary problem, then join that to another query that gets the accounts data.

If this functionality must be canned -- because many unknown report developers will use it to produce many unknown reports -- you can still do the same thing, but in a data set.

As C'est Moi alludes to, you can also do this in the data module by joining Remitter and Beneficiary into a table that computes the Customer_Id.