FM Report Dependencies using SQL

[sashafay]

Does anyone know how to replicate Cognos Framework Manager functionality "Find Report Dependencies" with SQL? The piece I really needed from it is "Model Object ID" that currently I can only pull manually from FM model, but I would like to automate it (make more reliable), with SQL or SDK. I can't find any good solution anywhere and Audit package won't help here. Any ideas appreciated. Thanks!

[Rahul Ganguli]

Nice thought. Please share once you get the solution, it will be really helpful for us.

[CoolP]

I have a very high-level idea of how to achieve it.

1. Get the xml for the entire model or for the individual objects using the Model Report option in FM. Use xsl(style sheet language) to translate it into a flat relational structure and then load into a database table(s).

2. Similarly take the xml's of all the report's and then use the command prompt and apply a different xsl to all these xml's in one shot. Load it into database tables.

3. Query the tables created in step 1 and step 2 to get the required output.

It is as good as developing a product. We did something on the similar lines for the OBIEE.


Thanks
Prasan

[sashafay]

Thanks for your idea Prasan, but I don't need document FM model as you described here.
I need to run report dependency based on query subjects within my model, so basically runtime status of user report inventory. Have you ever run this yourself? If you try, you will difference between what I want and what you listed here, which is no doubt very useful for documentation of a model design/metadata, but at this time I need something else. Thanks!  :)

[kommireddy]

Hi,

We had similar requirement and business wanted to know which of the fields used more frequent by consumers. Find Report dependency option in FM is working for time being, but I am also looking for automated way to generate this report. I found below info (SQL query), but it is based on ReportNet 1.1 version, I am trying to adjust the query to suit C10 content store table structure, but no luck yet, if you are able to make it work for C10, please let me know

http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/finding-reports-related-to-a-package-1571197

Btw, Find Report Dependency option did not work at the beginning and generated Java exceptions related to memory, as security source (custom java provider) has lot of entries. IBM support engineer suggested to set advanced Content Manager parameter CM.IPF_AUDIT_SECURITY_OBJECTS to camid, and it worked, I hope it is useful for those having similar issue.

[sashafay]

Thanks kommireddy, but unfortunately SQL that was provided in ITToolbox post isn't what I'm looking for. I corrected SQL but that didn't return much plus that gives you only package->report relationship and I'm looking for solution package->query subject->report relationship, which currently you can pull only from Framework Manager by running "Find Report Dependency" option. When you run it, check "Model Object ID" column and that is what I'm looking for to get somewhere/somehow with SQL or SDK or elsewhere.

Thanks for sharing JAVA exception advice. Didn't see that yet, but will keep in mind.

Thanks again everyone for great input, keep them coming!  8)