How to list the packages' data connections

[TheBrenda]

We have hundreds of packages. Before we send them to the client, would like to check the data connections of the packages and verify that they are correct. What SDK, utility, etc can I use to make a report of all the packages and their data connections (which SQL database they are pointing to)

[TheBrenda]

Specifically - Want to know what SQL database the packages are using.

[cognostechie]

Hi -

Maybe the SDK can do it better but you can also check this from the package itself though it will be tedious task. In Cognos Connection, navigate to the folder where your package resides. On the far right side of the package, there is a 'More' option. Click that and you will see an option called 'View the package Data Sources'. You will then have to go to Cognos Administration and the Data Source will tell you which database it is pointed to.

[TheBrenda]

That give me the Framework manager, DataSource Name. We call all of our data sources HBC. But in the Properties of the HBC Data Source, you have the Content Manager Data Source and that is the actual SQL DB Name - like Store41.

[TheBrenda]

When we deploye the package from FrameWork manager, we use HBC data source, but we change the FM Content Manager Data Source (CMDS) to point to one of a group of databases that all have the same schema. Say we have SQL Databases Store41, Store42 and they have the same schema. We will deploy an FM Package using Data Source HBC with FM CMDS as Store41, and then deploy another package using Data Source HBC with FM CMDS as Store42. In Cognos Connections, the Data Source for each of these will say HBC. I want to see the Store41 and Store42 SQL Database Name.

[cognostechie]

You did not mention all these things earlier.  The CMDS name is definitely embedded in the package but I don't know how to check that. Maybe somebody else can chip in here. Apart from this, somebody screwed up your environment big time. Somebody who did not know BI well enough and was not creative enough architected the environment. Having a separate DB for each store is insane to say the least. I did DW and BI for a retail company that had more than 150 stores with huge data but didn't have to create separate DBs. Moreover, having a separate data source for each DB does not tell you the name of the DB. For all practical reasons, the data source Store41 may not be pointing to the database named Store41 so even if you find  out which CMDS the package is using, you will still need to check the connection and it might have multiple connections too.

[Andrew Weiss]

TheBrenda,

MetaManager has a lineage report for tying Datasources to Packages and Packages to Reports.  There's a Knowledge Base article located at the link below that describes the technique.  There's also an example report on the article.  You can download the trial of MetaManager and try this out on a small set of objects to prove out that it does what you need.

http://www.bspsoftware.com/kb/finding-and-deleting-obsolete-datasources/

If you need any more information or you're interested in a demonstration please let me know.

Thanks,
-Andy

[TheBrenda]

First - I was wrong. Cognos Connection does give me the information that I need. It does not give me the SQL Database Name, but it gives me the Data Source Connection Name used and by that name I can tell the SQL DataBase.

Now I just want an automated way to find the Data Source name for each of my packages. 

Don't be so quick to judge. I am giving a really simplied, and rather nonscensical example, just to try and explain what i am trying to accomplish. And I am learning along the way. The packages were so messed up so I did not realize that Cognos Connection was giving me the answer that I wanted.

[TheBrenda]

Free tool MotioPI does it.

Content -> select Public Folders -> select Packages. For my purposes I wanted all packages so I did not Narrow or Filter. It took a while to run but all packages were returned. When you select a single package it shows the data source at the end of the bottom pane. But you can File -> Export Ouput -> csv and see all packages and all datasource in an Excel worksheet.

[cognostechie]

That's nice to know ! Thanks for sharing !