How to know if a Table was used in the reports / cubes / Data Modules / SQL

[Kiran Kandavalli]

Team,

Is there a way to know if a Table was used in the reports / cubes / Data Modules / SQL based Reports?

I know in Framework Model we can use Report Dependency to know this information but it will not give the information on SQL based Reports. Can you please suggest on this.

Basically I want the list of all the reports / cubes / Data Modules / SQL based Reports that uses a Table 'XYZ'.

Thanks!
Kiran

[dougp]

The way I have looked is using a PowerShell query against the report specs (xml), dataset specs (xml), and dashboard specs (json) that I export.  (not describing the export process here)  I go to Cognos, create a basic report asking for the item I want to find, export the report spec, find the data item in a query.  Now I know the exact syntax Cognos is using.  Then I run a PowerShell script to search all of my specs for that term.

But I have been forgetting to search for it in native SQL where an SQL object may be feeding a query.  Fortunately I don't have many of these.  You should be able to do the same thing, but the syntax will be different in native or pass-thru SQL.  In fact, you may not be able to accurately identify it there because, if you're looking for column DatabaseName.SchemaName1.tblB.a...

select a.a
, b.a

from DatabaseName..tblA a
  inner join DatabaseName.SchemaName1.tblB on b.b = a.c
  inner join tblB c on c.b = a.d

So if SchemaName1 = dbo, for example, there are a lot of ways you'll need to look for this.

If you have money, tools like MetaManager and Motio can probably help with this question.