Author Topic: Data Dictionary and Compilation of Reports  (Read 5363 times)

Offline tominboston

  • Full Member
  • ***
  • Join Date: Oct 2005
  • Posts: 7
  • Forum Citizenship: +1/-1
Data Dictionary and Compilation of Reports
« on: 13 Oct 2005 07:44:22 pm »
 ???
Does anyone know how to extract the sql from several thousand Cognos Query reports published to Upfront?
I need to compile a list of reports and their sql.

Help!
Much thanks!


Offline dwood09

  • Global Moderator
  • Senior Member
  • *****
  • Join Date: Jul 2005
  • Posts: 60
  • Forum Citizenship: +9/-0
Re: Data Dictionary and Compilation of Reports
« Reply #1 on: 13 Oct 2005 08:14:31 pm »
Does Cognos Query support Automation?!  If it does have an object model, then it should be possible to extract these details.  It is certainly possible to traverse the items of upfront.

cheers,
DW

Offline tominboston

  • Full Member
  • ***
  • Join Date: Oct 2005
  • Posts: 7
  • Forum Citizenship: +1/-1
Re: Data Dictionary and Compilation of Reports
« Reply #2 on: 13 Oct 2005 08:19:24 pm »
Man I am stuck on this one ... thanks for the input dwood,
but I don't know how I can get a user friendly view of the datastore/object store ... I can suck out a list of reports from the CQPackage.pkg but the sql is all crapped out .... (eg):
"... N_0aoBETWEEN_0aoNOT_20BETWEEN_0aoIS_0aoIS_20NOT_0an_0a0_0a0_0aClient_20Id_0aarial_2c_20geneva_2c_20helvetica_2c_20sans_2dserif_0a2_0a0_0a8_0accccff_0aarial_2c_20gen "

Any ideas?

(I'll have to buy you a beer if you can figure this out ...)

Offline dwood09

  • Global Moderator
  • Senior Member
  • *****
  • Join Date: Jul 2005
  • Posts: 60
  • Forum Citizenship: +9/-0
Re: Data Dictionary and Compilation of Reports
« Reply #3 on: 13 Oct 2005 08:23:25 pm »
Check out the Doco for Cognos Query.  Does it mention anything about macros, object model, automation, batch processing etc?

cheers,
DW

Offline tominboston

  • Full Member
  • ***
  • Join Date: Oct 2005
  • Posts: 7
  • Forum Citizenship: +1/-1
Re: Data Dictionary and Compilation of Reports
« Reply #4 on: 13 Oct 2005 08:24:36 pm »
Nada my friend ... just nada ....  :-[

Offline dwood09

  • Global Moderator
  • Senior Member
  • *****
  • Join Date: Jul 2005
  • Posts: 60
  • Forum Citizenship: +9/-0
Re: Data Dictionary and Compilation of Reports
« Reply #5 on: 13 Oct 2005 08:38:55 pm »
I've never used CQ, so am flying in the dark a bit, but can you check out / advise on the following:

* in upfront what details can you get from the CQ items, eg:
    * properties, descriptions, advanced details, file locations, references to definitions etc

* does the sql of CQ items exist in files somewhere on your server, or is it uploaded to a database
* where else can you see the SQL of these items?

cheers,
DW


Offline tominboston

  • Full Member
  • ***
  • Join Date: Oct 2005
  • Posts: 7
  • Forum Citizenship: +1/-1
Re: Data Dictionary and Compilation of Reports
« Reply #6 on: 13 Oct 2005 09:01:58 pm »
very little info about the reports in Upfront.

The sql appears to be saved in a files as a BLOB object - do you know how can I query these?
The info (sql and data) are in the following file formats:

1. querydb.oox (where x is a number) - these appear to be data files
2. rAAAAAzz.chg (where zz are 2 alpha chars) - these appear to be query files

both are not viewable in notepad ....
(crap)

Any ideas?

Offline Draoued

  • Global Moderator
  • Community Leader
  • *****
  • Join Date: Jul 2005
  • Posts: 96
  • Forum Citizenship: +12/-0
Re: Data Dictionary and Compilation of Reports
« Reply #7 on: 17 Oct 2005 10:41:47 am »
Hi ,

Some ideas .

Do you know this link ?? It s showing you the most used queries and the average execution time.
Always good to know which queries are the most used one , isn't it ?

http://MyCognosServer/cognos/cgi-bin/cqcgi.exe/script/history.iws

Then there are 2 options to collect the SQL generated by Cognos Query.

A general one , activating the Trace in cognos Query.
Very disk consuming, and requires a stop/start of Cognos Query, then you need to parse the big file to see all the sql.

A one by one action.
Opening one by one all the queries, and activating the Drill-through option, that will generate an IQD in the server.

Offline dwood09

  • Global Moderator
  • Senior Member
  • *****
  • Join Date: Jul 2005
  • Posts: 60
  • Forum Citizenship: +9/-0
Re: Data Dictionary and Compilation of Reports
« Reply #8 on: 17 Oct 2005 07:02:44 pm »
sorry dude, I got nothing   ???

Offline Draoued

  • Global Moderator
  • Community Leader
  • *****
  • Join Date: Jul 2005
  • Posts: 96
  • Forum Citizenship: +12/-0
Re: Data Dictionary and Compilation of Reports
« Reply #9 on: 18 Oct 2005 02:47:49 am »
Hi Dwood09 , Is it me dude ??

Which part you don't get ??

Offline tominboston

  • Full Member
  • ***
  • Join Date: Oct 2005
  • Posts: 7
  • Forum Citizenship: +1/-1
Re: Data Dictionary and Compilation of Reports
« Reply #10 on: 18 Oct 2005 09:47:03 am »
 :o

Thanks Draoued,  ... crap ...it all seems painful ... little in way of automation.

... I have been able to parse through the _.pkg file (convert to xml 1st) created during a Deployment, and extract the Report Title, description, ... etc. including the Binary for the sql for each report.  I just need to translate this binary and I think we're there!

Cheers

 


       
Twittear