Setting the data soruce for a query subject

[dougp]

Cognos Analytics 11.0.4
Windows server and clients
SQL 2014
CQM

I'm trying to create a way to identify which server and database is being used in a package.

At any given time, the packages in my test environment may be using data from dev, QA or production databases, depending on what is being tested.  Also, ongoing model changes may not yet be visible in the test environment.  The package and the model may be out of sync.  So, if I want to see what server and database is being used by a package, I can't just inspect the model.

If I have one data source, it's simple.  Create a query subject, choose the data source option, select the same Content Manager (CM) data source that is used by the Framework Manager (FM) data source, change the SQL Type to Native, and set the SQL property to this:

Code Select Expand

SELECT  @@SERVERNAME as 'Server Name'
, D.name as 'Database Name'
  FROM master.dbo.sysprocesses P
  join master.dbo.sysdatabases D
  ON D.dbid = P.dbid
WHERE P.spid = @@SPID

It's just as easy if I have numerous FM data sources that each uses a different CM data source.  Just do the same thing as many times as I have data sources.

What if I have numerous FM data sources that use the same CM data source?  For example:

Name	Expenditure	Budget
Content Manager Data Source	Financial	Financial
Catalog	Expenditure	Budget
Schema	dbo	dbo

The reason for this type of setup is to allow the database server, rather than the Cognos server, to do all of the query work.  Since all of the databases are on the same server and can be accessed via the same connection, there is no reason to not use that to speed up reports.

Now, when I create a query subject and select data source, I am still asked for a CM data source.  This type of query subject doesn't appear to use a data source that is defined in FM.

What's more confusing is that model.xml (when viewed in a text editor) clearly shows a relationship between the query subject and the FM data source.  I tried changing the data source reference in the XML to the correct one, but it had no effect.

Do you have a different approach to solving this?