Cognos package and datasource dependency from content store

[mrcool]

Hi All,

I am able to fetch all the packages and datasources from the content store but not finding a way to establish the association between them.
Could any one help me with the query or tables which has the relation between the package and datasource?
I don't want to use free tools like motio as it's not suitable for my requirement.
I would also like to know where I can find the model xml in content store as the table(CMOBJPROPS7) specified in the documentation doesn't have model xml in it.

Thanks,
mc

[saumil287]

Hi,
  The reports xml is stored in CMOBJPROPS7  table and datasources are stored in a seperate table.

The model.xml(I think) comes from a multiple table. Also the model.xml is stored locally in your project file location.

The association Info is present inside the model.xml file in the bottom which also contains a package Info. That package Info is present in report specification xml

Regards
saumil

[mrcool]

Thanks Saumil. Do you have any SQL to find the association?

[mrcool]

Please help me with the content store table that has model.xml spec. I browsed all the tables but couldn't find it anywhere.

Thank you.

[saumil287]

Hi,

What you want is model.xml which you can get form the local project file of cognos framework manager.
Go to the project folder of what model.xml you want and then look for model.xml.

you can use SDK to access the model.xml from the server.

If you are getting the model.xml from local folder then why you want to go to the server and access that?

Thanks & Regards
saumil sanghvi

[mrcool]

Thanks Saumil. Basically I want to know the data source associated with the reports. As I can not get that association directly I am trying to find out model.xml in content store to find out the datasource of the package.

[edewerth]

Given the variety of methodologies of configuration, my package and reports are open freely to be used by any of the 300 datasources  that I have and we can control that by security Roles so you might want to see what roles also have access to the reports and package.

[mrcool]

Thanks Edward, As of now I am looking for only the data sources of my reports. I have tried everything I know but still no luck.

[saumil287]

Hi,

If you know the cognos framework manager project which is used to create package and use that package to create reports then you can identify which datasource the reports are using?

SOLN: Go to the model.xml of the FM project, open that xml look for datasources tag which are used in the model at that bottom in the file, You will get the datasource name then you can edit tthe datasource to look at which database it is pointing to?

2nd approach: The reports for which you want to find the datasource, there is a report function which you can place in the report which gives you the name of the datasource the report is using.
I did previouly long back but not recognize it, you can go through the available list of functions from report studio

Thanks & Regards
saumil sanghvi

[mrcool]

Thanks Saumil.

I cannot go with either of your solution as I have so many models and reports. I am looking for a generic solution which can give me the information I am looking for.

[mrcool]

Is there any KB document using SDK?

[saumil287]

Hi,

I also gone through the content store, but no model is found,
but using sdk, you can fetch the model.xml from content store which might acessing multiple tables and then creates a model.xml

You need to check with sdk sample in cognos

Thanks & Regards
saumil sanghvi

[cognos810]

Hello mrcool,
You will need to use SDK to get this information exported into csv/txt. These are the major steps you would take when going the SDK route.
1)Search for all reports/queries.
2)Get the package name for each report.
3)For each package, extract the model and save it in a temp XML file.
4)Parse the temp file using DOM to get the node value for cmDataSource tag within the XML.

But I have a question, do you really need a report to data source association? That information "can be" vague. Just to give you a very simple scenario: A model has references to 3 data sources and has multiple query subjects, lets say Query Subject 1, Query Subject 2 and Query subject 3. Query subject 1 pulls data from data source 1, Query subject 2 pulls data from data source 2 and Query subject 3 pulls it from data source 3. All these query subjects form one package and is published to the portal. Now, Report 1 uses Query Subject 1, Report 2 uses Query subject 2 and Report 3 uses Query subject 3.

If you were to write an SDK code, which is possible, Report 1 will point to package 1 and package 1 will point to all 3 data sources. This information can be ambiguous and not entirely accurate as Report 1 is never tied to Data source 2 or 3, may be not in your case, but just trying to show a possibility.

For SDK help, to get package names from reports, please check this IBM KB:
https://www-304.ibm.com/support/docview.wss?uid=swg21374788

For getting Data source names from the package, using SDK get the model.xml for that package and save it, then parse it using DOM for the tag <cmDataSource> in the XML.

Let me know if you need more info on this.

-Cognos810