Cognos Analytics

When I create datamodule with two datasources A and B and join them together (inner join) this will not lead tot desired result.

In Reporting I use one column from datasource A in a list. The result is that all the records from A are displayed instead of the records that exist in inner join between A and B. The Reporting module applies minimized SQL in the query. 

This is not what I expect  when using the data module????

------------------------------
Bastiaan Krijgsman
------------------------------

#CognosAnalyticswithWatson

Hello Bastiaan,
If you are using Cognos Analytics 11.1, you can create a Joined view of the tables. Joined View will force the Join to happen between the 2 underlying tables.

To create a Join view, again, you need to be on 11.1, ctrl click the 2 tables and right click and select New Table. On the New Table Window, Select Create a Joined View.

------------------------------
DENNY NAREZNY
------------------------------

Original Message:
Sent: 03-27-2019 07:11 AM
From: Bastiaan Krijgsman
Subject: Datamodule and relationships

When I create datamodule with two datasources A and B and join them together (inner join) this will not lead tot desired result.

In Reporting I use one column from datasource A in a list. The result is that all the records from A are displayed instead of the records that exist in inner join between A and B. The Reporting module applies minimized SQL in the query. 

This is not what I expect  when using the data module????

------------------------------
Bastiaan Krijgsman
------------------------------
#CognosAnalyticswithWatson

Hi @Bastiaan Krijgsman,

Can you please describe what you are trying to do? 

If you include a column from both table A and B, you will only get records that exist in both tables. 

​If you do not want to display any columns in B, you only want to see records from table A where the key exists in B, you need a filter.  One way to do this in CA 11.1 is to create a filter expression.  If you filter the table with "Table_A.ID IN (Table_B.ID)", you only see the items in table A that are also in table B without including columns from table B.

I made a little video, I hope this helps.


------------------------------
Rob Hatfield
------------------------------

Original Message:
Sent: 03-27-2019 07:11 AM
From: Bastiaan Krijgsman
Subject: Datamodule and relationships

When I create datamodule with two datasources A and B and join them together (inner join) this will not lead tot desired result.

In Reporting I use one column from datasource A in a list. The result is that all the records from A are displayed instead of the records that exist in inner join between A and B. The Reporting module applies minimized SQL in the query. 

This is not what I expect  when using the data module????

------------------------------
Bastiaan Krijgsman
------------------------------

#CognosAnalyticswithWatson

I have two sources in my datamodule (in CA11.0.13.1):
sales -> records with the municipalities where we have sold software licences
statcity -> records of all dutch municipalities with statistical info

I added a relation between this sources on municipality id

If I create a dashboard and add a map diagram with statcity.city in Location the result is that ALL the municipalities will be displayed instead of the municipalities where we actually sold licences. Because of the inner join in the datamodule I expect the following behaviour in sql:
select a.city from sales a inner join statcity b on a.id=b.id

I also tested this in CA11.1.1 with the Joined View functionality. Than it works!

A self service BI end user will not understand the behaviour in the datamodule in CA11.0.13.1 if he uses more than one source



------------------------------
Bastiaan Krijgsman
------------------------------

Original Message:
Sent: 03-28-2019 09:10 AM
From: Rob Hatfield
Subject: Datamodule and relationships

Hi @Bastiaan Krijgsman,

Can you please describe what you are trying to do? 

If you include a column from both table A and B, you will only get records that exist in both tables. 

​If you do not want to display any columns in B, you only want to see records from table A where the key exists in B, you need a filter.  One way to do this in CA 11.1 is to create a filter expression.  If you filter the table with "Table_A.ID IN (Table_B.ID)", you only see the items in table A that are also in table B without including columns from table B.

I made a little video, I hope this helps.


------------------------------
Rob Hatfield

Original Message:
Sent: 03-27-2019 07:11 AM
From: Bastiaan Krijgsman
Subject: Datamodule and relationships

When I create datamodule with two datasources A and B and join them together (inner join) this will not lead tot desired result.

In Reporting I use one column from datasource A in a list. The result is that all the records from A are displayed instead of the records that exist in inner join between A and B. The Reporting module applies minimized SQL in the query. 

This is not what I expect  when using the data module????

------------------------------
Bastiaan Krijgsman
------------------------------

#CognosAnalyticswithWatson

Hi @Bastiaan Krijgsman,

Thank you for the explanation.  A join in the model will only filter the tables when you use elements from both tables.  If you only refer to the sales table, you will get all records in that table.  You have to either refer to the statcity table too if you want to filter to dutch municipalities or apply a filter to the sales table.  The joined view feature in 11.1 does that.  ​

------------------------------
Rob Hatfield
------------------------------

Original Message:
Sent: 03-28-2019 06:24 PM
From: Bastiaan Krijgsman
Subject: Datamodule and relationships

I have two sources in my datamodule (in CA11.0.13.1):
sales -> records with the municipalities where we have sold software licences
statcity -> records of all dutch municipalities with statistical info

I added a relation between this sources on municipality id

If I create a dashboard and add a map diagram with statcity.city in Location the result is that ALL the municipalities will be displayed instead of the municipalities where we actually sold licences. Because of the inner join in the datamodule I expect the following behaviour in sql:
select a.city from sales a inner join statcity b on a.id=b.id

I also tested this in CA11.1.1 with the Joined View functionality. Than it works!

A self service BI end user will not understand the behaviour in the datamodule in CA11.0.13.1 if he uses more than one source



------------------------------
Bastiaan Krijgsman

Original Message:
Sent: 03-28-2019 09:10 AM
From: Rob Hatfield
Subject: Datamodule and relationships

Hi @Bastiaan Krijgsman,

Can you please describe what you are trying to do? 

If you include a column from both table A and B, you will only get records that exist in both tables. 

​If you do not want to display any columns in B, you only want to see records from table A where the key exists in B, you need a filter.  One way to do this in CA 11.1 is to create a filter expression.  If you filter the table with "Table_A.ID IN (Table_B.ID)", you only see the items in table A that are also in table B without including columns from table B.

I made a little video, I hope this helps.


------------------------------
Rob Hatfield

Original Message:
Sent: 03-27-2019 07:11 AM
From: Bastiaan Krijgsman
Subject: Datamodule and relationships

When I create datamodule with two datasources A and B and join them together (inner join) this will not lead tot desired result.

In Reporting I use one column from datasource A in a list. The result is that all the records from A are displayed instead of the records that exist in inner join between A and B. The Reporting module applies minimized SQL in the query. 

This is not what I expect  when using the data module????

------------------------------
Bastiaan Krijgsman
------------------------------

#CognosAnalyticswithWatson

Thank you both for sharing your information about this topic!

------------------------------
Bastiaan Krijgsman
------------------------------

Original Message:
Sent: 03-27-2019 07:11 AM
From: Bastiaan Krijgsman
Subject: Datamodule and relationships

When I create datamodule with two datasources A and B and join them together (inner join) this will not lead tot desired result.

In Reporting I use one column from datasource A in a list. The result is that all the records from A are displayed instead of the records that exist in inner join between A and B. The Reporting module applies minimized SQL in the query. 

This is not what I expect  when using the data module????

------------------------------
Bastiaan Krijgsman
------------------------------

#CognosAnalyticswithWatson