New query subject creates copy of Data Source?

[Invisi]

I add a new query subject to my database layer, innocently choose what I think is an existing Data Source, and suddenly I have a '1' copy of it! And I have no idea how to connect it to my original Data Source! Who will tell me what I do wrong and how I can correct it?

[Michael75]

I've often seen this in different versions of 10.1 & 10.2. Others will probably chip in so say why it happens, but I find it sufficient to edit the data source QS SQL i.e.

SELECT * FROM [MY_DATA_SOURCE1].TABLE

and then delete the redundant data source itself, and you're good to go.

I don't know if having the second data source is actually harmful to performance, but it offends my sense of tidiness, and that's a good enough reason for me to get rid of it. :)

[Lynn]

I've often seen this in different versions of 10.1 & 10.2. Others will probably chip in so say why it happens, but I find it sufficient to edit the data source QS SQL i.e.

SELECT * FROM [MY_DATA_SOURCE1].TABLE

and then delete the redundant data source itself, and you're good to go.

I don't know if having the second data source is actually harmful to performance, but it offends my sense of tidiness, and that's a good enough reason for me to get rid of it. :)

I don't know why it happens either and I am equally offended by the intrusion! I do exactly the same thing...edit the SQL and then delete the unwanted data source.

[AnalyticsWithJay]

This happens because the Schema property on the data source is empty. It will stop occurring if you update the property value.

As others mentioned, you can simply edit the SQL to change the data source reference.

[bus_pass_man]

A data source consists of a unique combination of the data source, a schema and, if it exists in the vendor's product, a catalog.   

If you import multiple schemas from a data source you will get multiple data source created.   I've attached a picture of the great outdoors sales sample model, which illustrates this. 

So assume you have a data source A and it has schemas Q, R, and S.   Assume that you import each of those schemas in that order.

You will end up with multiple data sources.  The first one will be called A.  It will have one schema, in our case Q.  Another will be A1.  It will have R as its schema.  A2 will have S as its schema.

If you've fiddled about with a data source's properties in FM you can end up with another instance of the data source because FM can't match the values of the data source that it is using to import to anything which already exists in the model.  I accidentally added a space after a schema value and the next time I imported I got a new data source. 

Select the data sources in FM and view the properties pane.  Compare the values in the two data sources. 

Editing the SQL in the query subject might not work if that table doesn't exist in the schema so you really need to make sure what is going on before doing something precipitous.

[Lynn]

That makes perfect sense! Thanks CognoidJay and buss_pass_man  :)

[Invisi]

To me it makes no sense?  :-\ My client uses SQL Server. As far as I know, there is no schema, or do you mean that [xxx] dbo? When I click on the Schema option, I get a tekst field that I can fill with whatever I want? What's the use in that????

[Invisi]

Michael75, Lynn, sadly your ingenious solution doesn't work for me because I already altered my SQL to this:

Code Select Expand

Select *
From  dim_product_line

So there is no data source to alter in the query anymore. I tried "Insert Data Source" at the bottom and choose my original data source, but that has no effect.


As for the schema, I see from the replicate data source it's indeed "dbo". I remove that because that and also the Catalog limit the portability of the data source. I am now working with 2 different underlying database connections for test purposes and I can't have Cognos baking the database name into the report queries from the framework.

[MFGF]

To me it makes no sense?  :-\ My client uses SQL Server. As far as I know, there is no schema, or do you mean that [xxx] dbo? When I click on the Schema option, I get a tekst field that I can fill with whatever I want? What's the use in that????

The issue here is that you (or someone else) has previously removed the schema entry for the existing data source. In doing this you have made it impossible for FM to recognise that the existing data source matches the one you are importing new definitions from. Hopefully this makes more sense now?

MF.

[Invisi]

I noticed that. I removed it on purpose for the reasons mentioned in my previous post. It's impossible to test with multiple connections when the Catalog is entered. Or do you have a solution for that which I don't know?

[Michael75]

It's impossible to test with multiple connections when the Catalog is entered.

Actually, it's not impossible. Rather than hacking the DS QS SQL, what you should do is to set up multiple connections under the data source in Cognos Admin. And having left the [MY_DATA_SOURCE] in the SQL, you will now be prompted as to which connection you want to use. So you can test the same scenario against multiple connections.

And if you don't want to be prompted, or you don't want other people using the same data source to be prompted, then you set the security provisions on the different connections so that the user in question only has access to one connection, or you disable the connections that no longer interest you, and the prompt disappears.

[Invisi]

Michael, that is what I did? But when in the Framework the Catalog is filled, Cognos still puts the schema and catalog in the SQL and goes to the wrong connection??????????????

[Michael75]

Michael, that is what I did? But when in the Framework the Catalog is filled, Cognos still puts the schema and catalog in the SQL and goes to the wrong connection??????????????

I've worked mostly with FM against Oracle, and more recently Netezza. In both, you can blank out the FM Catalog property before publishing. But I'm not sure about the Schema property.

I'm thinking that this is a question for someone with good SQL Server experience. Hopefully somebody will chip in.

Good luck!

[Invisi]

Do I understand that as a workaround you remove the Catalog just before publishing and then put it back?

[Michael75]

Do I understand that as a workaround you remove the Catalog just before publishing and then put it back?

That wasn't what I meant to say, though I realise that what I wrote can be read in that way.

I meant to say that, when a package is created, the Catalog is filled by default. We set it to spaces and leave it that way. I can't really tell you why, as it's a colleague who deals with this, and he's been here a couple of years longer than me.

[Invisi]

Spaces meaning a physical [SPACE], or blank? Because blank is what I do, and it gives me a Data Source that multiplies like rabbits when I add tables. You also have SQL Server as database?

[Michael75]

Spaces meaning a physical [SPACE], or blank?

Sorry, I meant blank, i.e. entirely removing the Catalog value.

You also have SQL Server as database?

Sorry, as I implied earlier in the thread, I've never used Cognos against SQL Server. There are people on Cognoise who do have that experience. Hope they can help you.

[Invisi]

Sorry, I remember you mentioning that earlier. Maybe it's a database type specific thing, so it's only SQL Server that has this issue.

[damzI]

This is a known feature (or bug) that if you import data from tables from different schema-s - the data source in Framework Manager replicates. I would check the dependencies of the "rogue" data source and then manually update the data source query subjects pointing to the "copy" - and then delete it. Hope that helps...

[Invisi]

That's what I'm doing now indeed. Thanks. It's just rather intensive, as I am incrementally extending the model.