Filter on the Inner Queries

[raj_aries81]

Hi Experts,

I am in process of converting a SQL to a FM package that is being used across many reports. I have the SQL query structure something as below -

SELECT   

A.COLUMNS,
B.COLUMNS

FROM   

(
SELECT COLUMNS FROM TABLE1 INNER JOIN  TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN
WHERE <CONDITIONS> ) A

LEFT JOIN

(
SELECT COLUMNS FROM TABLE3 INNER JOIN  TABLE4 ON TABLE3.COLUMN=TABLE4.COLUMN
WHERE <CONDITIONS> ) B

ON   A.COLUMN=B.COLUMN

WHERE <CONDITIONS>

Now, in my reports, each report has a different set of hardcoded filters in the where clause of inner queries A, B.
Since I am converting this to a package, how can I force Cognos to implement this filter for the inner queries instead of the main Where Clause.

Please suggest !!.

Thanks & Regards
Raj

[bdbits]

There is not nearly enough information to suggest anything useful. We know nothing about your tables, joins defined in FM, type of model (relational/dimensional), etc.

In general, if you have modeled things correctly in FM, you should not need to force anything. Of course Cognos will not be correct 100% of the time, but it should get it right most of the time.

You can do a pass-through query in Report Studio. But do not make a habit of it as that would more-or-less defeat the purpose of using Cognos in the first place. It should only be done as a last resort after ensuring you have modeled everything correctly.

[cognos810]

Hello raj_aries81,
A very simple example ....
SELECT   

A.COLUMNS,
B.COLUMNS

FROM   

(
SELECT COLUMNS FROM TABLE1 INNER JOIN  TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN
WHERE TABLE2.SOMECOLUMNNAME_TO_BE_FILTERED='YOUR HARD CODED FILTER VALUE' ) A

LEFT JOIN

(
SELECT COLUMNS FROM TABLE3 INNER JOIN  TABLE4 ON TABLE3.COLUMN=TABLE4.COLUMN
WHERE TABLE3.SOMECOLUMNNAME_TO_BE_FILTERED='YOUR HARD CODED FILTER VALUE' ) B

ON   A.COLUMN=B.COLUMN

WHERE <CONDITIONS>

[raj_aries81]

There is not nearly enough information to suggest anything useful. We know nothing about your tables, joins defined in FM, type of model (relational/dimensional), etc.

In general, if you have modeled things correctly in FM, you should not need to force anything. Of course Cognos will not be correct 100% of the time, but it should get it right most of the time.

You can do a pass-through query in Report Studio. But do not make a habit of it as that would more-or-less defeat the purpose of using Cognos in the first place. It should only be done as a last resort after ensuring you have modeled everything correctly.

Thanks bdbits,

I have not created the model yet. I am planning to develope a relational model (DB is Teradata). My issue here is that the filter clause in the inner queries keeps changing from report to report ...say for eg. For one report I have to use DUE_DEL_CD in ('25',26,'30'), while another report has a different set of DUE_DEL_CD. If I apply a filter in report, I think it gets applied at the where clause for the over all query instead of filtering the data at the inner query.

Right now I did a SQL override for the report. As you rightly mentioned, the very purpose of Cognos is defeated if I go by this approach.
I am planning to create a model/package instead of SQL. Hence am looking for a solution to repliacte this approach in Cognos

Thanks & Regards
Raj

[raj_aries81]

Hello raj_aries81,
A very simple example ....
SELECT   

A.COLUMNS,
B.COLUMNS

FROM   

(
SELECT COLUMNS FROM TABLE1 INNER JOIN  TABLE2 ON TABLE1.COLUMN=TABLE2.COLUMN
WHERE TABLE2.SOMECOLUMNNAME_TO_BE_FILTERED='YOUR HARD CODED FILTER VALUE' ) A

LEFT JOIN

(
SELECT COLUMNS FROM TABLE3 INNER JOIN  TABLE4 ON TABLE3.COLUMN=TABLE4.COLUMN
WHERE TABLE3.SOMECOLUMNNAME_TO_BE_FILTERED='YOUR HARD CODED FILTER VALUE' ) B

ON   A.COLUMN=B.COLUMN

WHERE <CONDITIONS>

Thanks Cognos810,

My hardcoded filter varies from report to report. How can I achieve this functionality in Cognos FM so that if I apply a filter..it actually gets applied for inner queries not the main Where clause.

Regards
Raj

[raj_aries81]

There is not nearly enough information to suggest anything useful. We know nothing about your tables, joins defined in FM, type of model (relational/dimensional), etc.

In general, if you have modeled things correctly in FM, you should not need to force anything. Of course Cognos will not be correct 100% of the time, but it should get it right most of the time.

You can do a pass-through query in Report Studio. But do not make a habit of it as that would more-or-less defeat the purpose of using Cognos in the first place. It should only be done as a last resort after ensuring you have modeled everything correctly.

Is there a way that I can achieve this in FM....any suggestions.

[raj_aries81]

I have two tables Resource & CustomerInfo

In Resource table,a resource can have the status like - Available, Booked, NoService etc.I am using maxtimestamp column to identify the latest and
distinct record for each resource.

Cust table holds the customer details tied to a resource. This table also stores history, so if I join Resource with Cust, I
get multiple records for a Resource because of History. There is a status column that identifies Active, Inactive..etc customers

Business wants to see the customer info for only booked assets and no customer info to be displayed for Available & OOS items, hence I did a left
outer with Resource table and included a filter on status code in the join so that cust info is displayed only for Active Customers.

Status Code filter that I used in the join keeps varying from report to report and hence I don't want to hard code it.I want to embed this functionality in FM model to be used across reports.How can I enforce this in Cognos FM to use the filter in join instead of where clause.

Any help in this regard is highly appreciated.


Thanks
Raj

[Lynn]

Have you tried using a prompt or promptmany macro in your join?

[raj_aries81]

Hi Lynn...I am not using any prompt. I want to give the business users adhoc ability so that they can filter it as required.

I want my filter to be applied to my inner query as below (bolded filter) without hard coding I want to keep it generic. How can I achieve this in a package.

SELECT
R.RESC_NAME,
R.RESC_STATUS.
C.CUST_NAME,
C.CUST_STATE
FROM
(
SELECT
RESC_NAME,
RESC_STATUS
FROM
RESOURCE
) R

LEFT OUTER

(
SELECT
RESC_NAME
CUST_NAME,
CUST_STATE,

.....
FROM
CUST 
WHERE CUST_STAT_CD IN ('ACTIVE','OPERATE')
) C

ON R.RESC_ID_PK=C.RESC_ID_FK

Regards,
Raj

[Lynn]

What mechanism do you envision to allow your users to select the relevant status values? Is there a reason why a prompt wouldn't be the right thing for that purpose? It certainly qualifies as a generic, non-hard coded adhoc means for users to filter as required.

I wouldn't generally advise writing SQL for your FM modeling purposes. Try to structure your database to support your requirements to ensure you get predictable SQL and fast performance. I think you are putting a lot of architecture load on FM when the database ETL processes could more efficiently address some of your requirements such as flagging the latest resource information.

I would suggest you model your two tables separately and, if possible, consider a dummy record of some sort to avoid the left outer join scenario and use an inner join instead. Then you can assess the SQL being generated and any related performance concerns. It may be possible that you are trying to solve some problem that doesn't even exist. Teradata is a rather powerful DBMS and I would hope that the query planner could join and filter your tables efficiently.

[raj_aries81]

What mechanism do you envision to allow your users to select the relevant status values? Is there a reason why a prompt wouldn't be the right thing for that purpose? It certainly qualifies as a generic, non-hard coded adhoc means for users to filter as required.

I wouldn't generally advise writing SQL for your FM modeling purposes. Try to structure your database to support your requirements to ensure you get predictable SQL and fast performance. I think you are putting a lot of architecture load on FM when the database ETL processes could more efficiently address some of your requirements such as flagging the latest resource information.

Thanks Lynn for your inputs. However, even if I include a prompt/prompt macro and create a package how can I pass those values to the inner query. I assume it would be passed to the overall Where clause of the query.

I have spoken to the ETL & DB team, given the constraints and process involved they want to push this to the BI.

I would suggest you model your two tables separately and, if possible, consider a dummy record of some sort to avoid the left outer join scenario and use an inner join instead. Then you can assess the SQL being generated and any related performance concerns. It may be possible that you are trying to solve some problem that doesn't even exist. Teradata is a rather powerful DBMS and I would hope that the query planner could join and filter your tables efficiently.

Lynn, could you please elaborate on this. how can I use a dummy record and get to this.

Regards
Raj

[MFGF]

I have spoken to the ETL & DB team, given the constraints and process involved they want to push this to the BI.

...so what they are basically saying is that they don't want to put any effort into solving it? Perhaps you can say to the business "Given the constraints and process involved in doing this in the metadata model, we should simply push the problem to the users to figure out whenever they write a report"?

That seems to pass the problem nicely along to someone else - if it's a good strategy for your ETL and DB team, why not use it yourself? :)

Seriously, push back on the ETL/DB guys for trying to fob you off here. The main role of a data warehouse is to provide fast, easy reporting.

MF.

[raj_aries81]

...so what they are basically saying is that they don't want to put any effort into solving it? Perhaps you can say to the business "Given the constraints and process involved in doing this in the metadata model, we should simply push the problem to the users to figure out whenever they write a report"?

That seems to pass the problem nicely along to someone else - if it's a good strategy for your ETL and DB team, why not use it yourself? :)

Seriously, push back on the ETL/DB guys for trying to fob you off here. The main role of a data warehouse is to provide fast, easy reporting.

MF.

Thanks MFGF...ETL & DB teams have the so called Year End freeze lame cause :)..SQL Override is the a strategy that doesnt require any approvals so they pushed it off their plate

[Lynn]

Lynn, could you please elaborate on this. how can I use a dummy record and get to this.

You would need your DB/ETL team to cooperate with you to solve this problem.

With regard to a prompt, I don't see what the issue is. Just put the prompt macro wherever you want it. Have you tried different scenarios and assessed the generated SQL and the corresponding performance?

[raj_aries81]

You would need your DB/ETL team to cooperate with you to solve this problem.

With regard to a prompt, I don't see what the issue is. Just put the prompt macro wherever you want it. Have you tried different scenarios and assessed the generated SQL and the corresponding performance?

Lynn..do you mean to say prompt macro in the SQL code instead of hard coding the values?

[Lynn]

Lynn..do you mean to say prompt macro in the SQL code instead of hard coding the values?

Yes. I would not suggest that you use of SQL code for query subjects since it is contrary to best practice recommendations. As such I've never tried it but I don't see why it wouldn't work. Why not give it a try?

[raj_aries81]

Yes. I would not suggest that you use of SQL code for query subjects since it is contrary to best practice recommendations. As such I've never tried it but I don't see why it wouldn't work. Why not give it a try?

Lynn, is there a way to create in package not in SQL code...

[Lynn]

Lynn, is there a way to create in package not in SQL code...

I don't understand the question. You can create two database query subjects and join them, placing prompt macros within the join expression or within the individual query subjects or wherever you want them to be. You can also create a SQL query subject and place prompt macros in there. Once you've done the modeling using either of those scenarios you can then produce a package to make these elements available to report authors.

I'm not seeing very much throughout this thread on what you've tried in Framework Manager and where you are getting stuck. It would probably help if you attempted to model it and come back with specific questions. If you are new to FM modeling then I would suggest you review the user guide.

[cognos810]

Hello raj_aries81,
Yes you can use a parameter macro (Prompt, Promptmany) wherever you want the values of the parameter substituted in the query.
In a very simple exmaple,
Select A.*, B.* FROM
(Select * from TableA where someColumn=#prompt('parameter1UsedInReports','String')#) A
inner join
(Select * from TableA where someColumn=#prompt('parameter2UsedInReports','String')#) B
on A.someColumn=B.someColumn

This is a very simple illustration. You can use the parameter macro, for example, to completely exclude the where clause in case the filter is optional. Here is another example using ORDER_DETAILS and ORDER_METHOD from the GOSALES database.

Select   A.ORDER_NUMBER AS "A", B.ORDER_NUMBER AS "B" from
(
select * from gosales.ORDER_DETAILS
#promptmany('ORD_NUM_A','integer',' ','where ORDER_NUMBER IN (','',')')#
)A
inner join
(
Select * FROM gosales.ORDER_HEADER
#promptmany('ORD_NUM_B','integer',' ','where ORDER_NUMBER IN (','',')')#
)B
on
A.ORDER_NUMBER=B.ORDER_NUMBER

In essence, if you would supply values via the report for params ORD_NUM_A and ORD_NUM_B the resulting SQL would be:
Select A.ORDER_NUMBER AS "A", B.ORDER_NUMBER AS "B" from ( select * from gosales.ORDER_DETAILS where ORDER_NUMBER IN (Values_Passed_via_Prompt) )A inner join ( Select * FROM gosales.ORDER_HEADER where ORDER_NUMBER IN ('Values_Passed_via_Prompt') )B on A.ORDER_NUMBER=B.ORDER_NUMBER
OR, if you do not supply any prompt values, it would resolve to:
Select A.ORDER_NUMBER AS "A", B.ORDER_NUMBER AS "B" from ( select * from gosales.ORDER_DETAILS )A inner join ( Select * FROM gosales.ORDER_HEADER )B on A.ORDER_NUMBER=B.ORDER_NUMBER
<<Notice the where clause was removed when no values supplied>>.

Note: Cognos 10.2.1 FM HAD an issue with prompt macro testing and was fixed in FP1. If you are using Cognos 10.2.1 please make sure you have the Fix pack 1 or greater applied on both the server AND the FM installations.

-Cognos810

[raj_aries81]

I don't understand the question. You can create two database query subjects and join them, placing prompt macros within the join expression or within the individual query subjects or wherever you want them to be. You can also create a SQL query subject and place prompt macros in there. Once you've done the modeling using either of those scenarios you can then produce a package to make these elements available to report authors.

I'm not seeing very much throughout this thread on what you've tried in Framework Manager and where you are getting stuck. It would probably help if you attempted to model it and come back with specific questions. If you are new to FM modeling then I would suggest you review the user guide.

Thanks Lynn, I was looking at the option of filtering the subqueries, now that you have suggested me an approach let me go back and try to implement that. Thank you so much for your time.

Regards
Raj

[raj_aries81]

Hello raj_aries81,
Yes you can use a parameter macro (Prompt, Promptmany) wherever you want the values of the parameter substituted in the query.
In a very simple exmaple,
Select A.*, B.* FROM
(Select * from TableA where someColumn=#prompt('parameter1UsedInReports','String')#) A
inner join
(Select * from TableA where someColumn=#prompt('parameter2UsedInReports','String')#) B
on A.someColumn=B.someColumn

This is a very simple illustration. You can use the parameter macro, for example, to completely exclude the where clause in case the filter is optional. Here is another example using ORDER_DETAILS and ORDER_METHOD from the GOSALES database.

Select   A.ORDER_NUMBER AS "A", B.ORDER_NUMBER AS "B" from
(
select * from gosales.ORDER_DETAILS
#promptmany('ORD_NUM_A','integer',' ','where ORDER_NUMBER IN (','',')')#
)A
inner join
(
Select * FROM gosales.ORDER_HEADER
#promptmany('ORD_NUM_B','integer',' ','where ORDER_NUMBER IN (','',')')#
)B
on
A.ORDER_NUMBER=B.ORDER_NUMBER

In essence, if you would supply values via the report for params ORD_NUM_A and ORD_NUM_B the resulting SQL would be:
Select A.ORDER_NUMBER AS "A", B.ORDER_NUMBER AS "B" from ( select * from gosales.ORDER_DETAILS where ORDER_NUMBER IN (Values_Passed_via_Prompt) )A inner join ( Select * FROM gosales.ORDER_HEADER where ORDER_NUMBER IN ('Values_Passed_via_Prompt') )B on A.ORDER_NUMBER=B.ORDER_NUMBER
OR, if you do not supply any prompt values, it would resolve to:
Select A.ORDER_NUMBER AS "A", B.ORDER_NUMBER AS "B" from ( select * from gosales.ORDER_DETAILS )A inner join ( Select * FROM gosales.ORDER_HEADER )B on A.ORDER_NUMBER=B.ORDER_NUMBER
<<Notice the where clause was removed when no values supplied>>.

Note: Cognos 10.2.1 FM HAD an issue with prompt macro testing and was fixed in FP1. If you are using Cognos 10.2.1 please make sure you have the Fix pack 1 or greater applied on both the server AND the FM installations.

-Cognos810

Thank you Cognos810, for providing examples to make me understand the concept. I would definitely use this approach and will come back to you if I see any issues. Once again thank you some much for taking time and explaining me using examples.

Regards
Raj

[raj_aries81]

Note: Cognos 10.2.1 FM HAD an issue with prompt macro testing and was fixed in FP1. If you are using Cognos 10.2.1 please make sure you have the Fix pack 1 or greater applied on both the server AND the FM installations.

-Cognos810

As you rightly said, I got 'The Query contains one or more unresolved prompts.' in 10.2.1 looks like I don't have FP1 or greater and I do not have control over installing FPs ..I may have to talk to the admin.

[raj_aries81]

Hi Lynn,Cognos810,

Since I dont have control over applying the FPs, I thought of implementing this in SQL code at report level. I have included the PromptMacro in the sub-queries and
included a hidden prompt with the required Codes as default selection.

Thanks for your inputs. I have asked the admin team to install the FP1 or greater.

Regards
Raj

[cognos_sourabh]

Hi,

I think in this case you can write a dynamic sql in the RS or in the FM. Pass the values either from PROMPTS (in RS) or through PARAMETER MAPS (in FM)

Rgrds,
Sourabh.