I have a need to identify the current state of a published package.
While performing development and maintenance on a model, the model and the published package are out-of-synch. I may be pointing a data source at the development database, but have not published the package yet so the package is still using production.
For a published package, I want to know what database server and database each data source in the package is using. I want this exposed via Reporting.
I tried creating a data source query subject like this:
select @@SERVERNAME AS 'Server Name'
, TABLE_CATALOG as 'Database Name'
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'SchemaVersion'
Unfortunately, this type of query subject bypasses the data source defined in Framework Manager and goes directly to the data source connection in Cognos.
Is there a way to do this?
This is an older post - does this help ?
http://www.cognoise.com/index.php/topic,32899.msg108230.html#msg108230
My original post includes my SQL Server solution that matches RichardP's Oracle solution. It doesn't work if there is any complexity in the model because data source query subjects use the raw data source connections in Cognos, not the data sources defined in the FM model.
If your intent is to quickly confirm, when using Dynamic Query, testing a database connection from the Admin UI will show a success status.
If you click on that hyperlink, you will see displayed the versions details of the driver and server being used.
If you want a generic approach, define a view in your databases which you access as a db-query subject. The db-query subject is associated to a data source in your FM model and in turn the database name in CM.
The admin UI in Reporting? The admin UI for a package? I'm confused.
Quote from: dougp on 27 Oct 2017 10:13:35 AM
The admin UI in Reporting? The admin UI for a package? I'm confused.
I think Nigel means the Administration UI - either the new Manage > Data Server Connections dialog, or the old C10 Admin Console (Manage > Administration Console > Configuration tab)
These don't expose anything via reporting, though. For this you'd need to look at the third piece of his reply...
Cheers!
MF.
The Admin UI provides no way to inspect which data source connection a package is currently using.
Quote from: dougp on 27 Oct 2017 11:01:25 AM
The Admin UI provides no way to inspect which data source connection a package is currently using.
Yes - agreed. For this I think he was alluding to having a view in your databases which you then include in FM as a database query subject - pretty much as described in his last sentence. It's not something I've ever tried, but knowing the person suggesting this, I'd expect it to be worth pursuing.
MF.
He didn't allude, he actually said...
Making changes to the database can always work. I can make SQL Server do whatever I need done, but that's not the problem. I would like to avoid modifying the database if the modification serves only to support a specific reporting tool.
I'll speak with my data modelers and database administrators and see how it goes.
Thank you, everyone, for your responses. It sounds like this is a flaw in Cognos that must be worked around.
BTW: I have been working with IBM Support on this for a long time. Just a few days ago I got a support person who understood my need. Still no word as to whether they consider it a flaw.
If you have defined a database query subject in FM, it will be associated to a data source object in the model which in turn references the content manager data source name.
Hence, in the attached screen cap, the vendor proprietary statement (i.e query marked as native) when executed will be applied to the associated data source object.
Not true. With a data source query subject, the Catalog property is ignored. It may not doesn't use the FM data source at all. It may be directly tied to the CM data source defined in the Data Source Connections page in Administration. See attached collage.
The catalog and schema properties of a data source object are used to form qualified table references when SQL is dynamically generated.
i.e. select .. from x.y.z or y.z.
Those properties are not used to perform an explicit 'use database' or 'set schema' statement against a database.
In your case, connection to SQL Server for both your data sources will have the context of the default database specified on the database connection properties.
Assuming you want both data source objects to share the same Content Manager data source connection, you could consider using a parameter map in the catalog property of the data source object and reference and project the parameter map value as a literal string in your SQL statement.
I should use a parameter map in place of the catalog name? Do you mean use a macro expression that uses a parameter map? I haven't had to use parameter maps yet. Is there an example of this in the samples?
Quote from: dougp on 06 Nov 2017 09:16:17 AM
I should use a parameter map in place of the catalog name? Do you mean use a macro expression that uses a parameter map? I haven't had to use parameter maps yet. Is there an example of this in the samples?
This post might help you: http://www.cognoise.com/index.php/topic,19754.msg60120.html#msg60120
I have also re-attached the document referred to in that post. Feels like I wrote it a million years or so ago.
That won't work. The data source connection used by the data source query subject is not the same thing as the FM data source. I'm trying to identify what database server and database each FM data source is configured to use. There appears to be no way to do this within FM.
I have asked by data modeler and database administrator to create a view in each database so I can use a model query subject to get this information. That will work because it uses a FM data source. I was just hoping to not need to involve more people in something that should be super-simple.