Different Schemas in different environments for same tables used in a Universe

[Former Member]

Hi,

I have a Universe in Development where I have tables pointed to a schema (DW) in DEV but in TEST/INT I have the same tables under a different schema (TESTDW). So when I promote the Universe & reports from DEV to TEST, I will be getting errors as the schema is incorrect. So we need to find a way so that we can define the schema globally instead of having it at the individual table.

I know we can repoint the schemas, etc but i need to avoid extra work in other environments.

Known Solutions: Promote the reports to TEST, then select all the tables & change the schema by Right Clicking the selected tables and selecting Change Qualifier/Owner.

One of the few reasons, I don't want to follow this route, because I have lot of derived tables which I need to change it manually by editing the SQL Statement,  &  also If I add new tables or columns again in future & promote them to TEST, then again I have to change the schema.

Did anyone faced this kind of issue?Is there any other way that we can use like Begin_Sql , etc?

(FYI, I am using BO4.0 SP5)

[Former Member · Accepted solution]

Hi Reddy,

I think while promoting your universe you need to just change the connection of universe.

There are connection defiend for each region ex. for dev,test etc.

|So after migration you should just change the connection and u are done.

Thanks,

swapnil

[Former Member · Accepted solution]

Hello Swapnil,

In my question above, I am speaking about my schema names are different in different environments. and not about connections. If schema names are different and if you promote the Universe and connection, we will be getting errors as the schema name that's in DEV is not present in TEST.

Please read the question properly one more time to understand it properly.

Thanks,

Naveen.

[Former Member · Accepted solution]

Hi ,

You can go for synonyms.

because synonyms can have same name in all the schemas

Thanks,

Swapnil

[Former Member · Accepted solution]

There are known issues with working with synonyms - I believe that they are finally fixed on 4.1 SP5. Plus you'd have to manage prm files, etc., so that doesn't in itself lend for seemless migration.

[Former Member · Accepted solution]

since it is been resolved by SAP you dont have to take care much about it.

Just one time process of creating synonyms and there you go..

I think this the the best approach one can apply as now its been supported by SAP even.

[Former Member · Accepted solution]

I disagree. I think it is bad design to have the same database sat on different schema names. If you have separate servers/instances for each environment then there's no reason that the same database shouldn't be qualified as the same thing in each environment. You don't have to worry about synonym management at all then.

[Former Member]

Hi,

I'd give serious consideration to having the schema named the same across all environments. That way you can promote consistently and without any extra effort. While it may cost the dba some effort now, it will cost you effort every time you promote a universe if you don't.

Regards,

Mark

[Former Member]

Mark, Thanks a lot for your concern. We actually have same schema name across all 3 environments but there is a huge project going on in my company which is kind of hard to explain, so our team has decided to go ahead with a different names for schemas as 2 different teams will be working on paralelly on these schemas & they will combine them after a year or so. (I know this is not a solution)

Thanks Swapnil for Synonyms solution. Your solution might have worked, if we were using BO 4.1 SP5 but unfortunately we are using BO 4.0 and in this version I can't view any synonym tables in the universe.

[Former Member]

Thanks for the detailed explanation. Good luck with getting back to a best practice scenario. Upgrading to 4.1 and using synonyms would be a good idea if you can do it in the next two months, otherwise you may as well go with what you're planning on.