Spanning Tree Help.

[jatmon]

Hi,

My company is currently upgrading impromptu from v5 to v7.
We will be building a new catalog as the database is also being upgraded.

I have been tasked with compiling a spanning tree (ie. all the database tables used in our reports of which we have over 600 !)

Apart from running the report, viewing the SQL and cutting and pasting the From clause, is there a macro or any other way of compiling a list of all the tables used in our reports? I'm not interested in the join structure, just a definitive list of all the tables used by our standard reports.

Thanks.

 

[BruceReed]

In Impromptu, there is a report to do this. Use the menu "Catalog, Content Overview" will generate a report to an icr file, showing table names, joins, field names etc. (In options you can control what you do or do not want to see).You can view this file in notepad.

Good luck
Bruce

 

[jatmon]

Bruce,

I am aware of the content Overview report, but this will show a list of all the tables in the catalog, where as I only want a list of the tables used in our reports.

There are a lot of tables in our current catalog that are not needed, and I need to compile a list of tables that are used , to build a new (but smaller) catalog and remove the unwanted tables.

Thanks,
J

 

[nagrajm]

A less cumbersome method would be to open and save each one of the report IMRs to a .SQL file and then import them into a Excel spreadsheet by selecting DATA -> GET EXTERNAL DATA -> IMPORT TEXT FILE. Select FIXED WIDTH and click FINISH.

You will notice that Excel imports the clauses SELECT, FROM, WHERE, ORDER BY into separate rows.

Bring the SQLs of all the IMRs into one Excel worksheet. Thereafter sort the first column alphabetically. You will have all the FROM clauses together and there you have the table names.

Cumbersome but comfortably do-able.

Hope that helps.

 

[jatmon]

Thanks,

I will give that a try.

J.

 

[BruceReed]

There is also an after market solution called I-Assistant (

http://www.i-sols.com).

I tried the evaluation software & it seemed OK. It was 2000 Euros (about $2000).

Nagrajm's solution is much cheaper (FREE), so I suggest looking at that first. Cognos is very weak in reporting of catalogs & files so any solution is better than nothing.

Good luck

Bruce