Column and or views used in a report

[CraigFSindorf]

I need to run a query against the Content Store that shows me all the reports that use a view called DIM_DATE_CALENDAR as an example. IN the results i would like to know reports and path if possible. We do NOT have the SDK or MOTIO. Is there any way to get what I am after and NOT do it package by package through FrameWork Manager?

TIA

[Eric.Pleiss]

Craig,
I don't know a way to grab that in Cognos other than they way you noted with FM.  But there are a couple of 3rd party tools, such as BSP's MetaManager and the Report Search tool can quickly query your reports to find any that include that view or other things that you could want for another query and then if needed you can update those objects in bulk to round out your use case, should updates be needed.

You can get a free trial and download the software to do the documentation you're looking for - www.bspsoftware.com

[cognostechie]

I need to run a query against the Content Store that shows me all the reports that use a view called DIM_DATE_CALENDAR as an example. IN the results i would like to know reports and path if possible. We do NOT have the SDK or . Is there any way to get what I am after and NOT do it package by package through FrameWork Manager?

TIA

Hi

Click on the view in the Database Layer and from the 'Tools' menu, select 'Find Report Dependencies'. This will show you all reports (with the path) using that view. No need to do it package by package.

[CraigFSindorf]

Yes if we had 1 FM or Package but I do not know where all the current FMs are for the packages on the portal. We have 30 plus packages and I need to look inside each one. And I believe when you do it in FM it does it for the packages that model produces

[cognostechie]

No. It will look into reports made from each of those packages and tell you the report name so you have to do it only once for one FM Model. If you don't know where those FM models are then you can create the models from packages. IBM has instructions on their web site for this.

If you are looking for someone to quickly provide you the query to run against content store then I doubt if anyone has it. The other thing you could do is to get what Eric suggested

[dougp]

I get this question regularly.  "We want to delete this table/view from the data mart.  What Cognos reports use it?"

I use a recursive CTE that returns the complete path to every report.  If I can, I filter that to only the packages that I know are related to the question.  Then I join those results on the report's CMID to the CMOBJPROPS7 table to get the SPEC.  I filter that query to just the reports that match the criteria.  Here's where it gets slow.

Create a new report that uses the view.  (Keep it small.  No pages.  Just one query with one data item.)
Copy the report spec to the clipboard.
Find the reference to the view.

You'll filter the results of the Content Store query like this:

Code Select Expand

SELECT r.ReportPath
FROM #reports r
  INNER JOIN CMOBJPROPS7 s ON s.CMID = r.ReportCMID
WHERE s.SPEC LIKE '%[[]DIM_DATE_CALENDAR]%'

That's why you filter by package first.  If you are having to inspect a lot of report specs (many thousands of characters of XML-formatted text) using this LIKE statement, it will be slow.

And all of this assumes that you can track it all down by looking for any report that uses a query subject named the same as your view.  It gets more uncertain if you have any SQL objects (raw, custom SQL) in reports or if the names vary across packages.

There's a solution that is simpler for the database administrators, data modelers, and Cognos administrator:  Rename the view and see if anybody complains.  After a few months of no complaints, drop the view.

[CraigFSindorf]

So DOUGP Do i run this against the content store or in a regular report? The task at hand is to identify the reports that use the view because a couple columns in the view will be changing and reports can or will fail. I prefer to find all reports NO MATTER the package that use the view that way I can do an estimate on how many reports might or might not need to be touched.

[dougp]

At this point, you're hacking the database.  I guarantee IBM will be unwilling to support such an action or its potential outcomes.  I threw this idea out here in hopes it would help.  But if you require a deeper explanation, this may not be the approach for you.  You may be better off using a more standard approach like using FM to identify the impact of a change, using the SDK, using a 3rd-party tool like Motio PI or BSP MetaManager, or hiring a Cognos consultant to help you.

[cognostechie]

So DOUGP Do i run this against the content store or in a regular report? The task at hand is to identify the reports that use the view because a couple columns in the view will be changing and reports can or will fail. I prefer to find all reports NO MATTER the package that use the view that way I can do an estimate on how many reports might or might not need to be touched.

Where is the problem? In the approach !

In any existing table/view, existing column names should not be modified but new columns should be created. That way, existing reports will not be effected.

[CraigFSindorf]

I can not control what the DBA's are doing as its a mass change in the structure of the company. Tables are changing and data elements are changing.

[cognostechie]

In that case tell them that reports will have be identified one-by-one and fixed. That might deter them. If you find out a way to identify reports using a method which reduces the pain then you will be supporting them in bad practices.

[cognostechie]

or just tell your management to buy the tool that Eric suggested. I won't make efforts to support someone's bad practice so just tell them to shell out the money and buy the tool

[dougp]

I don't think you stated why you are doing this.  It's hard to believe that the DBAs or data architects are just randomly (or even systematically) changing table and column names.  That would affect more than reports.  It would break everything.  What is the actual problem you are trying to address?

If it is as simple as you've stated...
Decide what level of expertise you can bring to bear, explain your and your organization's current limitations with regard to this project, and provide your boss with likely costs to implement software packages or hire contractors to help fill the gaps.

Can you leave the existing table(s) there?  Or maybe views that have the same names, column names, data types, but return no data?  That would enable you to not have reports just break outright and help you determine which reports actually matter.  I know I have about 19,000 reports, but only about 1500 of them are actually used.  On the other hand, sometimes breaking reports can be a good thing.  You quickly learn what gets used.

But [Date] seems like a pretty common dimension.  Why would anyone in their right mind change the name?  (Or are they actually changing it to [Date]?  That may make sense.)

In that case tell them that reports will have be identified one-by-one and fixed. That might deter them. If you find out a way to identify reports using a method which reduces the pain then you will be supporting them in bad practices.

Come on.  That's probably not fair.  This may be a wonderful change that just requires the reports to catch up.  Or maybe they're changing every instance of a lower case "s" to an upper case "S" to match the company logo -- which is, of course, ridiculous.  Either way, the dude has a job to do.  It's gonna take time and he's gonna get paid.  Sometimes, that's all work is.

On the other hand, I agree.  We try to only add tables and columns.  But it sounds like my work environment is far more pleasant.  People actually work together.

[cognostechie]

You are correct when you look at it from a developer's perspective. I tend to look at it from other levels too especially because I am also a businessman