Different schema in Prod and dev/uat

[Arsenal]

Hey All,

DB is Sql Server. I don't even have the connection params yet (new project) but have been told the schema names between prod and dev/uat (dev and uat will use same db server) are different. So, I can't play around and find out for myself..yet.

Did do some research and came across a document that describes how to have dynamic schemas for DB2 (nice job on the doc, Lynn!). There were some suggestions that schema names for SQL Server can be left blank in the datasource properties in FM..somehow this doesn't seem right to me but I can't recall right now from prior projects and don't have any models in this new one to check yet.

So basically, my question is will different schema names in prod and dev/uat require a special solution like creation of dynamic schema names or is it as simple as leaving the schema name out of FM datasource property? Multiple packages are not an option.

Deeply appreciate help/suggestions

[bi4u2]

In my experience it is as simple as not having anything on the schema property in FM/Data Sources,

[bi4u2]

Let me add to this that if you need to run the metadata wizard to bring in additional tables, FM may add it as a second datasource with the schema name. So what I do is edit the data source query data source from select * from [DataSource1].

to select * from [DataSource]. and then go to Tools/Update object. Then I delete the duplicated data source from the model.

[Lynn]

I think leaving the schema name blank works fine if you can reference a table in a SQL statement without having to qualify using a schema name.

If you log into the database directly (using whatever login Cognos would use) and are able to successfully query with "select * from table" in both environments, then leaving the schema name blank should work fine. In Oracle environments I've seen this accommodated with synonyms.

If, however, you need to say "select * from schema.table" in order to get results, then you will need to provide the schema in your model.

[Arsenal]

thanks for all your suggestion guys.
can't wait to try this no schema thingy. Sounds like it might work. Will circle back with findings

[Arsenal]

Finally, got to try it out. If I remove the schema name from the DS property in FM, the query subject errors out. Putting the schema name back in works out fine. So it looks like leaving the schema name out will not work for whatever reason

I wonder if the quantify with schema name is some sort of a setting on the DB side sine apparently bi4u2 has experience of working without schema name

[cognostechie]

Technically, schemas are applicable to Oracle and DB2 but not SQL Server. In SQL Server, you can have different databases, not different schemas within one database.

[Arsenal]

cognostechie, unfortunately in this situation it so happens that schema name in dev/uat is different than in prod. Ideally, it should have been the same and I can push back on it but just trying to see if there is some way out there. Hence I enquired about the blank in schema name. That didn't work out

Hey Lynn, does your method for DB2 dynamic schema name apply to sql server as well?

Thanks

[Lynn]

Hey Lynn, does your method for DB2 dynamic schema name apply to sql server as well?

I'm not certain, but don't see why not. The basis for the parameter map is a data source query subject that determines the server name so I can tell if I am in Prod or Dev. It isn't really hard to give it a try as long as you can figure out a SQL statement to know which environment you are in.

Good luck and I'd like to hear if you manage to get it going!

[cognostechie]

The other thing you could do is to leave the schema name as dbo but in the Catalog property of DS, change the name of the database before you publish the package to Prod. I am presuming the name of the database is different in Prod becuase there is only one schema in SQL Server called dbo.

Not the best solution because it requires changing it manually every time you publish the package for Prod. The parameter map should work.

[Arsenal]

yeah cognostechie, changing name everytime is not optimal.

Will be giving Lynn's method a shot and will report back on whether I was able to make it work.

[Arsenal]

Lynn's method worked. Was a bit tricky to come up with the SQL part. Sql for the data source query subject is below for those interested. Also attaching Lynn's original pdf...great job on it again, Lynn.

select
'CURRENT_ENVIRONMENT' as CURRENT_ENVIRONMENT,
case when @@Servername  = 'Server 1' then 'schema1'
when @@Servername   ='server 2' then 'schema2'
when @@Servername   ='server3' then 'schema3'
else 'schema1'
end AS Schema

[Lynn]

Great! Glad you got it working and very nice of you to supplement with the SQL server portion!

[nsaxena]

Great! Glad you got it working and very nice of you to supplement with the SQL server portion!

Hello everyone, i replicated similar solution and i was getting below error when i am inserting macro in schema

"GEN-ERR-0008 A possible recursive definition involving the parameter map 'Dynamic Library' based on query items that were detected."

Any idea what i am missing

SQL query i used is :
SELECT DISTINCT
'CURRENT_ENVIRONMENT' as CURRENT_ENVIRONMENT,
CASE CREATOR
WHEN 'PROD' THEN 'PRODSCHEMA'
WHEN 'TEST' THEN 'TESTSCHEMA'
END AS LIBRARY_NAME
 FROM SYSIBM.SYSTABLES
WHERE CREATOR IN ('PROD','TEST')

I TRIED WITH/WITHOUT DISTINCT ,but error was same