I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson

Original Message------

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson

I don't know that there is a good "out of box" or one-touch solution, but you can use Motio PI ( I think the freeware version will suffice ), validate all your reports, and output the validation results including full SQL.     Then a simple CRTL-F with the view name will tell you where it's used.

It's not elegant, but it does work.

For FM models and Transformer .mdl files I've used notepad++ and the Find In Files search option to point to the director and file suffix I want to search ( bonus: notepad++ is also no charge! )

HTH,


------------------------------
R.A. Dawson Sr
------------------------------

Original Message:
Sent: Fri February 07, 2020 07:37 PM
From: Jeff Demaris
Subject: Finding "where used" information from Cognos environment

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson

Good one! We always use Motio PI free version to validate our reports in our QA step. I did not think to use it early on to find broken reports.​

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Mon February 10, 2020 08:32 AM
From: R.A. Dawson Sr
Subject: Finding "where used" information from Cognos environment


I don't know that there is a good "out of box" or one-touch solution, but you can use Motio PI ( I think the freeware version will suffice ), validate all your reports, and output the validation results including full SQL.     Then a simple CRTL-F with the view name will tell you where it's used.

It's not elegant, but it does work.

For FM models and Transformer .mdl files I've used notepad++ and the Find In Files search option to point to the director and file suffix I want to search ( bonus: notepad++ is also no charge! )

HTH,


------------------------------
R.A. Dawson Sr

Original Message:
Sent: Fri February 07, 2020 07:37 PM
From: Jeff Demaris
Subject: Finding "where used" information from Cognos environment

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson

We have this situation from time to time where the MS SQL columns are renamed. Guess that we do not have so many reports or packages that we cannot find the changed references. Also, in Framework Manager we manually add the "Release:Table:Column:COgnos FIeld Name" in the description for each field - [2011.01].[RMAGMAST].[RSXCLDT]: Closed Date. It helps us in this scenario, but also we use this description as the basis for a cognos report that the clients can use to search for column names, table names, release new fields, etc.

I don't know  a slick way to do it without a bit of programming to parse the model.xml. Obviously the model.xml will have the database view name and column name and presentation name.  Like below view v_ods_LNACR, column [BANK NUM] is presentation  [BANK NUMBER] in reports. You can get a list of all matching presentation names and then look for the presentation name in the report definition. Depending on how unique the names are you may have good results. I have a couple of MS SQL sprocs that read the Content Manager database looking for presentation names. It returns the path, package, owner, matching text in report definition wand each occurrence of the match. ALso have some sprocs that will replace text in report definitions. I have previously posted my code here. Or you could use the IBM DRU utility for finding and replacing text in report definitions. It is much more industrial strength. It can use regular expressions to search for matching text so it can find it over CR, LF, and other embedded special characters.

Exec cm_SearchForTextInReports '[Demand Deposits / Savings].[Account Attributes].[GL Type Code]'

Exec cm_ReplaceTextInReports '[Account Attributes].[GL Type Code]','[Account Attributes].[GL Type Code 1]', 1

<definition>

<dbQuery>

<sources>

<dataSourceRef>[].[dataSources].[HBI]</dataSourceRef>

</sources>

<sql type="cognos">

Select

v_ods_LNACR.&quot;Bank Num&quot;,

From

[HBI].v_ods_LNACR as v_ods_LNACR

</sql>

</dbQuery>

</definition>

<queryItem>

<name locale="en">Bank Number</name>

<description locale="en">[2011.01].[LNACR].[ARBNK]: Bank Number</description>

<lastChanged>2012-04-05T01:29:01</lastChanged>

<externalName>Bank Num</externalName>

<usage>attribute</usage>

<datatype>decimal</datatype>

<precision>3</precision>

<scale>0</scale>

<size>2</size>

<nullable>false</nullable>

<regularAggregate>count</regularAggregate>

<semiAggregat

 

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Fri February 07, 2020 07:37 PM
From: Jeff Demaris
Subject: Finding "where used" information from Cognos environment

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson

if you have sandbox environment, you can turn on Native sql to be sent and capture in Cognos Logs. You can use MotioCI to run reports (if you already created regression test suite) and capture queries in Cognos Logs and search for impacted views and columns.

------------------------------
Buddhi Gupta
------------------------------

Original Message:
Sent: Fri February 07, 2020 07:37 PM
From: Jeff Demaris
Subject: Finding "where used" information from Cognos environment

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson

Thank you for the replies.  

As I mentioned initially, we tried using Motio but the search process used was WAY too slow for what we needed to accomplish, so we have been looking for something better.  

We looked at the SDK and are probably going to keep that on our radar for longer term, but we are not ready to execute on SDK development at this time;  From talking with IBM it sounded like it would not be a straight-forward bit of logic to code in any case. 

What we ended up doing is to use data that we have been captured over time that could be combine into the data set we need. 
* query logging from the database - our database retains sql history of all queries executed, with session information. 
* Cognos session logging on database - we call a stored procedure whenever a report connects (in the open session command block) and pass in package and report name, and session id and request id.
* Cognos audit information - Cognos audit runreports table contains history of all Cognos queries that were run
Data for the last year from these three data sets was parsed, joined and loaded into a database table to provide the result set we need.  
The end result of the sql information joined with audit information via session information does allow us to find where any database object is referenced in our reports (as long as it was executed in the past year, per our data pulls. 
One good side benefit from this is that we are able to parse the path of the reports and tell how much gets executed from "my folders", and there are a lot of executions from there!

One final note on this - we have begun looking at the Cognos Toolkit from IBM.  IBM was able to demonstrate how we could use the toolkit to get the "where used" answers we were looking for without going through the process above.  In our specific case we were fortunate to already have the historical data captured, but we don't do the same thing on all data sources so solutions like Cognos Toolkit and Motio remain in our arsenal to respond to future needs. 


------------------------------
Jeff Demaris
------------------------------

Original Message:
Sent: Fri February 07, 2020 07:37 PM
From: Jeff Demaris
Subject: Finding "where used" information from Cognos environment

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------
#CognosAnalyticswithWatson

​If your models are created using framework manager, you could check each model for affected reports. Open a model using framework manager, click on a data item and then click 'Tools' > 'Find Report Dependencies'. This will show a list of reports; which can be exported to excel.
There currently isn't an equivalent option for data modules. I believe it's being worked on though.
Regards
Trevor

------------------------------
Trevor Fyfe
------------------------------

Original Message:
Sent: Fri February 07, 2020 07:37 PM
From: Jeff Demaris
Subject: Finding "where used" information from Cognos environment

I have what I think is a pretty common question and I've searched a lot on this topic but not found a satisfactory answer.   I am hoping that there's someone in this community who's faced and solved this problem. 
A few database views in a reporting data source are changing and this will impact reports on the Cognos server.  
We need to identify what packages/reports reference this view so the scope of work to remediate can be communicated to the project team.  
We have tried querying the content store cmobjprops7 table and have been able to parses out the XML to get the report definition, but found that not all of the report XML contains a sqlText entry with the report sql and there are dataitem expressions throughout some of the other XML with references to model presentation layer renaming of tables and fields.  So we are not confident the content store query will give reliable results.  

We have begun working with the SDK to see if we can extract possibly the package information and find references to the database views, but have had very slow progress there due to our lack of experience with the SDK.  

I should also mention that we have started using Motio to pull the information but it is way to slow and we won't get the required information in time. 

My question to the community is if anyone has found a solution for this and is willing to share.

------------------------------
Jeff Demaris
------------------------------

#CognosAnalyticswithWatson