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


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 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, 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)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Maruthi Rao YarapathineniCommented:
as per my understanding we need to perform only once in all three environments. next time onwards we don't promote the connections.  we just promote with out any dependencies.
Since you are dealing with many derived tables, you need to perform this activity manually. Mostly I deal with SAP BW almost way back I have worked on DW back end.
naveenreddyinusAuthor Commented:
I know the solution that you are saying. I already mentioned it in my question. I am looking for an alternative solution.
What database are you using?

We got around this on a project I was on by defining synonyms for the tables and using the synonym in the IDT.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.