Report Dependencies on the Content Store

[kclark]

Hello,

I am having trouble finding ALL the reports that a change within a package affects.  Not just within the package itself via Framework Manager.  I am talking about global.  If the package is being referenced by another tool as a data source or ANY reports utilizing it as well.  I have heard of running a Find Report Dependencies request in Cognos Connection on the content store but how the heck do you do that?  Any ideas or help would be great.  Thanks!

[blom0344]

You can run a report dependencies analysis from framework manager. If you perform this on the whole model, then each existing package is used to determine which report depends on the package. In the past we found this to be less then perfect with Cognos replacing spaces in names by underscores.

In 8.4.1. the following contentstore query 'seems' to work for us:

Code Select Expand


select distinct temp2.name as package,temp1.folder,temp1.name from
(SELECT    temp.PARENTNAME AS FOLDER,CMOBJECTS.PCMID,CMOBJNAMES.CMID, CMOBJNAMES.LOCALEID, CMOBJNAMES.MAPDLOCALEID, CMOBJNAMES.ISDEFAULT, CMOBJNAMES.NAME,
                      CMOBJECTS.CLASSID
FROM         CMOBJNAMES INNER JOIN
                      CMOBJECTS ON CMOBJNAMES.CMID = CMOBJECTS.CMID
INNER JOIN
(SELECT P.CMID AS PARENT,P.NAME AS PARENTNAME FROM CMOBJNAMES P where P.LOCALEID = 10) temp
ON CMOBJECTS.PCMID = TEMP.PARENT
WHERE     (CMOBJECTS.CLASSID = 10)
AND SUBSTRING(TEMP.PARENTNAME,1,1) NOT IN ('1','2','3','4','5','6','7','8','9') AND
TEMP.PARENTNAME NOT LIKE 'Backup%') temp1
inner join
(SELECT  CMREFNOORD1.CMID AS PID, CMREFNOORD1.REFCMID, CMOBJNAMES.NAME
FROM         CMREFNOORD1 INNER JOIN
                      CMOBJECTS ON CMREFNOORD1.REFCMID = CMOBJECTS.CMID INNER JOIN
                      CMOBJNAMES ON CMOBJECTS.CMID = CMOBJNAMES.CMID
WHERE     (CMREFNOORD1.PROPID = 31 AND CMOBJNAMES.LOCALEID = 10)) temp2
on temp1.cmid = temp2.pid


The localeid is different (can be) in each Cognos instance. We added this since we have multiple languages enabled..

[kclark]

Hey Thanks!  I was removing some fields in a dimension...  So what I did was I ran the report dependencies on the query subject at the database layer.  That produced ALL the reports that use ANY of the fields in that query subject.  So I just manually went through the results looking for the specific fields being removed.  Which what you were saying to do below.  The query that you use...is that in the SDK or the Sql Server Management Studio????

[blom0344]

It is just an SQL script to be used used in any tool (indeed also SQL server Management Studio).
It is however tuned to our own development contentstore and you may need to tweak for your instance..

[TheBrenda]

I run the query but the results set is empty. What is the query suppose to do? Say an expression name changed, how would you change the query to find all old expression names?

[blom0344]

You don't..  You can read dependencies between reports and packages, but individual queries are not stored within the contentstore. They are generated at runtime from the model package..

[TheBrenda]

We have a disconnect. I want to know how to use your posted query.

I ran your posted query - as is - but nothing was returned. I cannot see how to use your query to find report dependencies.

Thanks

[blom0344]

 The script uses filter based on the used locale(s). Our design language is  dutch-belgium, which relates to localeid = 10 for our development content store. You can check which value you should use by checking the CMLOCALES table in the contentstore.
I currently use the following T-SQL script for the dependencies:

Code Select Expand


WITH REPORTS_LINKED_TO_PACKAGES AS (
select distinct names.name,temp.* from
(select cmrefnoord1.cmid,package_id,package_name
from
(SELECT DISTINCT
       cmobjects.cmid as package_id,
       cmobjnames.name as package_name
FROM   cmrefnoord1
       INNER JOIN cmobjects
         ON cmrefnoord1.refcmid = cmobjects.cmid
       INNER JOIN cmobjnames
         ON cmobjects.cmid = cmobjnames.cmid
WHERE  cmrefnoord1.propid = 31
       AND cmobjnames.localeid IN (select max(LOCALEID) from CMLOCALES where LOCALE = 'nl-be')) packages
inner join cmrefnoord1
on packages.package_id = cmrefnoord1.refcmid) temp
inner join
cmobjnames names on temp.cmid = names.cmid and names.LOCALEID = (select max(LOCALEID) from CMLOCALES where LOCALE IN ('nl-be','nl'))
),
INDIVIDUAL_REPORTS AS
(SELECT NAMES.NAME AS REPORT,NAMES.LOCALEID,NAMES.CMID,VERSION
FROM CMOBJECTS
INNER JOIN CMOBJNAMES NAMES
ON CMOBJECTS.CMID = NAMES.CMID
WHERE CLASSID = 10 AND DISABLED IS NULL AND
NAMES.LOCALEID = (select max(LOCALEID) from CMLOCALES where LOCALE IN ('nl-be','nl'))
),
RELATED_FOLDERS AS
(SELECT NAME AS FOLDER, OBJ.PCMID AS FOLDER_ID,OBJ.CMID AS REPORT_ID FROM
CMOBJNAMES NAMES
INNER JOIN
CMOBJECTS OBJ
ON OBJ.PCMID = NAMES.CMID
WHERE OBJ.CLASSID = 10 AND
NAMES.LOCALEID = (SELECT MAX(LOCALEID) FROM CMLOCALES WHERE LOCALE IN ('nl-be','nl'))
)

SELECT DISTINCT IND_REP.CMID,IND_REP.REPORT,REL_FOL.FOLDER AS FOLDER,COALESCE(REP_LINK.package_name,'_MISSING !!')
as PACKAGE
FROM INDIVIDUAL_REPORTS IND_REP
INNER JOIN
RELATED_FOLDERS REL_FOL
ON IND_REP.CMID = REL_FOL.REPORT_ID
LEFT OUTER JOIN
REPORTS_LINKED_TO_PACKAGES REP_LINK
ON IND_REP.REPORT = REP_LINK.NAME
WHERE ISNUMERIC(COALESCE(REP_LINK.package_name,'_MISSING !!')) = 0
ORDER BY PACKAGE


[TheBrenda]

sorry, I am not at work now and do now have availability of my resources to test the query and to ask the question properly. but .... does this produce a report of all dependences? if I wanted to find all the dependencies of a single field in a query subject - say the name or type of a field is changing - can I use this query and what would I change to look just for a single field?

[blom0344]

No, it comes down to the relationship between report and package.  If you want the dependency for a query-item, then the dependency check from the model would be the way to go.  Individual query items are stored within the report xml.  If you look for  CMOBJPROPS7.SPEC in the contentstore then you find the XML definition.

[bgeorge]

I had the same question as the OP and found this post but discovered the code shared above is for MS SQL Server installations of ODS/Cognos. We are running on Oracle. So I had to come up with my own query. Here is:

Code Select Expand


select name, path, package from (
select a.name, b.path,
case when instr(spec,'<modelPath>') >0
      then substr(spec, instr(spec,'<modelPath>'), instr(spec,'</modelPath>') - instr(spec,'<modelPath>'))
      else  substr(spec, instr(spec,'srcModel='), instr(spec,'"',instr(spec,'srcModel="')+10 ) - instr(spec,'srcModel='))
  end package
from
(select cmobjprops7.cmid,  cmobjnames_base.name, dbms_lob.substr(spec,700,1)spec 
from cognos10.cmobjprops7
join cognos10.cmobjnames_base on cmobjprops7.cmid = cmobjnames_base.cmid
where cmobjprops7.spec is not null) a
join ( select o.cmid, SYS_CONNECT_BY_PATH(o.objname, ' --> ') path
from (
    select obj.CMID CMID, case when obj.cmid = obj.pcmid then null else obj.PCMID end PCMID, nm.name objname
    from cognos10.CMOBJECTS obj
    join cognos10.CMOBJNAMES nm on obj.cmid = nm.cmid
    where nm.isdefault = 1
) o
start with o.PCMID = 0
connect by prior o.CMID = o.PCMID) b
on a.cmid=b.cmid
)
where package not like '%Operational Data Store Packages%'
order by 2;

In our case the "old" packages were all in a folder called "Operational Data Store Packages" and the new packages were not. So I was looking for any reports created that use the new packages.