Hi,
In Reporting, Filter section of a query

If one or more filters are already present in the filter section, 
when adding a new filter in the query, and asking for the available values based on the query item, 
the results is depending of the join used.

In my case, I'm using a FM package with 4 star schemes.
I have a filter on one dimension
and I'm adding a new filter on another dimension

In Analytics, the Select value will take the whole filter context (and only the filter context) to propose the values. (and not the measure from the query)

The question is: What is the logic behind if the 2 dims are connected to 4 facts tables ?
Which one will be chosen ?
Based on which logic?
My issue is that the values are not the same in each fact table 
So the result could be an incomplete list of values depending the fact table used 

The workaround, is to put the measure located in the query, in the filter section to force the selection of values to take the right join path 

Is it based on the join name in alphabetical order from the package (when there are multiple possibilities)
I don't understand why the select values is not automatically taking as context the measure used in the query
most of the time, filters are only done on dimensions

As info: version used: 11.1.7

Thanks!

Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF
------------------------------

#CognosAnalyticswithWatson

Hi Jean-Pierre,

Do you use conformed dimensions? In here, Products and Time are both conformed dimensions. 
Do you have the right cardinaly on the dimension side, and fact side?


When queries that request facts from multiple tables are performed in IBM Cognos Analytics, the query service performs what IBM Cognos calls a stitch query. Stitch queries consist of subqueries, one for each fact table, that are then merged together on their common attributes from a shared dimension table.
In the following example of a model, Products and Time are clearly shared dimensions, based on the cardinality defined between Returned items fact and Sales fact.

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company
------------------------------

Original Message:
Sent: Fri February 04, 2022 09:40 AM
From: Jean-Pierre CLEF
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi,
In Reporting, Filter section of a query

If one or more filters are already present in the filter section, 
when adding a new filter in the query, and asking for the available values based on the query item, 
the results is depending of the join used.

In my case, I'm using a FM package with 4 star schemes.
I have a filter on one dimension
and I'm adding a new filter on another dimension

In Analytics, the Select value will take the whole filter context (and only the filter context) to propose the values. (and not the measure from the query)

The question is: What is the logic behind if the 2 dims are connected to 4 facts tables ?
Which one will be chosen ?
Based on which logic?
My issue is that the values are not the same in each fact table 
So the result could be an incomplete list of values depending the fact table used 

The workaround, is to put the measure located in the query, in the filter section to force the selection of values to take the right join path 

Is it based on the join name in alphabetical order from the package (when there are multiple possibilities)
I don't understand why the select values is not automatically taking as context the measure used in the query
most of the time, filters are only done on dimensions

As info: version used: 11.1.7

Thanks!

Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF
------------------------------

#CognosAnalyticswithWatson

Hi Patrick,

Thanks for your reply !

Yes, I'm using conformed dimensions as showed in your presentation.

I will use you schema as example.

A query is based on the Sales Fact.
In my query I have: 
a measure from Sales Fact
Productname from product dim
Year from the time dim

In the filter section of the query, a filter is created on a specific product
if another filter is added (on time in your case) and instead of typing the values, you use the 'Select the multiple values' based on the filed Productname located in the query, the list of returned values are depending of the fact taken in the join between Product and Time.

The select multiple values is only using the information from the filter section (in our case product and time)
and not the measure from the query

The workaround I found to force him to go via the right Fact is to make a temp filter on the measure on the fact table he need to use.

You could have the same issue in your case if all products and all time records are not the same in both fact table 

I don't know if someone in Ottawa can give me the logic behind for the join strategy between 2 filter from 2 different dims when using select multiple values features

I don't know if you know or got this issue before ?

Thanks
Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF
------------------------------

Original Message:
Sent: Fri February 04, 2022 10:12 AM
From: Patrick Neveu
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi Jean-Pierre,

Do you use conformed dimensions? In here, Products and Time are both conformed dimensions. 
Do you have the right cardinaly on the dimension side, and fact side?


When queries that request facts from multiple tables are performed in IBM Cognos Analytics, the query service performs what IBM Cognos calls a stitch query. Stitch queries consist of subqueries, one for each fact table, that are then merged together on their common attributes from a shared dimension table.
In the following example of a model, Products and Time are clearly shared dimensions, based on the cardinality defined between Returned items fact and Sales fact.

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company

Original Message:
Sent: Fri February 04, 2022 09:40 AM
From: Jean-Pierre CLEF
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi,
In Reporting, Filter section of a query

If one or more filters are already present in the filter section, 
when adding a new filter in the query, and asking for the available values based on the query item, 
the results is depending of the join used.

In my case, I'm using a FM package with 4 star schemes.
I have a filter on one dimension
and I'm adding a new filter on another dimension

In Analytics, the Select value will take the whole filter context (and only the filter context) to propose the values. (and not the measure from the query)

The question is: What is the logic behind if the 2 dims are connected to 4 facts tables ?
Which one will be chosen ?
Based on which logic?
My issue is that the values are not the same in each fact table 
So the result could be an incomplete list of values depending the fact table used 

The workaround, is to put the measure located in the query, in the filter section to force the selection of values to take the right join path 

Is it based on the join name in alphabetical order from the package (when there are multiple possibilities)
I don't understand why the select values is not automatically taking as context the measure used in the query
most of the time, filters are only done on dimensions

As info: version used: 11.1.7

Thanks!

Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF
------------------------------

#CognosAnalyticswithWatson

Hi Jean-Pierre,

Did you change the generated SQL to "Minimized SQL" instead of default value? I could be changed in the query in Report.

Click Actions, Edit Definition, and then click the Query Information tab. The Test Results box is initially empty until you run the query.
Click Options, SQL Settings.
Set Generate SQL to As View or Minimized.
Click OK.

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company
------------------------------

Original Message:
Sent: Mon February 07, 2022 02:52 AM
From: Jean-Pierre CLEF
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi Patrick,

Thanks for your reply !

Yes, I'm using conformed dimensions as showed in your presentation.

I will use you schema as example.

A query is based on the Sales Fact.
In my query I have: 
a measure from Sales Fact
Productname from product dim
Year from the time dim

In the filter section of the query, a filter is created on a specific product
if another filter is added (on time in your case) and instead of typing the values, you use the 'Select the multiple values' based on the filed Productname located in the query, the list of returned values are depending of the fact taken in the join between Product and Time.

The select multiple values is only using the information from the filter section (in our case product and time)
and not the measure from the query

The workaround I found to force him to go via the right Fact is to make a temp filter on the measure on the fact table he need to use.

You could have the same issue in your case if all products and all time records are not the same in both fact table 

I don't know if someone in Ottawa can give me the logic behind for the join strategy between 2 filter from 2 different dims when using select multiple values features

I don't know if you know or got this issue before ?

Thanks
Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF

Original Message:
Sent: Fri February 04, 2022 10:12 AM
From: Patrick Neveu
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi Jean-Pierre,

Do you use conformed dimensions? In here, Products and Time are both conformed dimensions. 
Do you have the right cardinaly on the dimension side, and fact side?


When queries that request facts from multiple tables are performed in IBM Cognos Analytics, the query service performs what IBM Cognos calls a stitch query. Stitch queries consist of subqueries, one for each fact table, that are then merged together on their common attributes from a shared dimension table.
In the following example of a model, Products and Time are clearly shared dimensions, based on the cardinality defined between Returned items fact and Sales fact.

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company

Original Message:
Sent: Fri February 04, 2022 09:40 AM
From: Jean-Pierre CLEF
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi,
In Reporting, Filter section of a query

If one or more filters are already present in the filter section, 
when adding a new filter in the query, and asking for the available values based on the query item, 
the results is depending of the join used.

In my case, I'm using a FM package with 4 star schemes.
I have a filter on one dimension
and I'm adding a new filter on another dimension

In Analytics, the Select value will take the whole filter context (and only the filter context) to propose the values. (and not the measure from the query)

The question is: What is the logic behind if the 2 dims are connected to 4 facts tables ?
Which one will be chosen ?
Based on which logic?
My issue is that the values are not the same in each fact table 
So the result could be an incomplete list of values depending the fact table used 

The workaround, is to put the measure located in the query, in the filter section to force the selection of values to take the right join path 

Is it based on the join name in alphabetical order from the package (when there are multiple possibilities)
I don't understand why the select values is not automatically taking as context the measure used in the query
most of the time, filters are only done on dimensions

As info: version used: 11.1.7

Thanks!

Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF
------------------------------

#CognosAnalyticswithWatson

An explanation of the join selection strategy can be found at: 

Dynamic query mode governors - IBM Documentation
It has a section on: (DQM) Context Sensitive Join Selection

If that doesn't quite answer your questions, could you please share an example report.

------------------------------
Kind regards,
HENK CAZEMIER
------------------------------

Original Message:
Sent: Mon February 07, 2022 03:59 AM
From: Patrick Neveu
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi Jean-Pierre,

Did you change the generated SQL to "Minimized SQL" instead of default value? I could be changed in the query in Report.

Click Actions, Edit Definition, and then click the Query Information tab. The Test Results box is initially empty until you run the query.
Click Options, SQL Settings.
Set Generate SQL to As View or Minimized.
Click OK.

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company

Original Message:
Sent: Mon February 07, 2022 02:52 AM
From: Jean-Pierre CLEF
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi Patrick,

Thanks for your reply !

Yes, I'm using conformed dimensions as showed in your presentation.

I will use you schema as example.

A query is based on the Sales Fact.
In my query I have: 
a measure from Sales Fact
Productname from product dim
Year from the time dim

In the filter section of the query, a filter is created on a specific product
if another filter is added (on time in your case) and instead of typing the values, you use the 'Select the multiple values' based on the filed Productname located in the query, the list of returned values are depending of the fact taken in the join between Product and Time.

The select multiple values is only using the information from the filter section (in our case product and time)
and not the measure from the query

The workaround I found to force him to go via the right Fact is to make a temp filter on the measure on the fact table he need to use.

You could have the same issue in your case if all products and all time records are not the same in both fact table 

I don't know if someone in Ottawa can give me the logic behind for the join strategy between 2 filter from 2 different dims when using select multiple values features

I don't know if you know or got this issue before ?

Thanks
Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF

Original Message:
Sent: Fri February 04, 2022 10:12 AM
From: Patrick Neveu
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi Jean-Pierre,

Do you use conformed dimensions? In here, Products and Time are both conformed dimensions. 
Do you have the right cardinaly on the dimension side, and fact side?


When queries that request facts from multiple tables are performed in IBM Cognos Analytics, the query service performs what IBM Cognos calls a stitch query. Stitch queries consist of subqueries, one for each fact table, that are then merged together on their common attributes from a shared dimension table.
In the following example of a model, Products and Time are clearly shared dimensions, based on the cardinality defined between Returned items fact and Sales fact.

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company

Original Message:
Sent: Fri February 04, 2022 09:40 AM
From: Jean-Pierre CLEF
Subject: Which is the join strategy behind the Select values selection in filter section of reporting

Hi,
In Reporting, Filter section of a query

If one or more filters are already present in the filter section, 
when adding a new filter in the query, and asking for the available values based on the query item, 
the results is depending of the join used.

In my case, I'm using a FM package with 4 star schemes.
I have a filter on one dimension
and I'm adding a new filter on another dimension

In Analytics, the Select value will take the whole filter context (and only the filter context) to propose the values. (and not the measure from the query)

The question is: What is the logic behind if the 2 dims are connected to 4 facts tables ?
Which one will be chosen ?
Based on which logic?
My issue is that the values are not the same in each fact table 
So the result could be an incomplete list of values depending the fact table used 

The workaround, is to put the measure located in the query, in the filter section to force the selection of values to take the right join path 

Is it based on the join name in alphabetical order from the package (when there are multiple possibilities)
I don't understand why the select values is not automatically taking as context the measure used in the query
most of the time, filters are only done on dimensions

As info: version used: 11.1.7

Thanks!

Regards,
Jean-Pierre

------------------------------
Jean-Pierre CLEF
------------------------------

#CognosAnalyticswithWatson