Skip to Content

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

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)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 22, 2015 at 10:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.

  • avatar image
    Former Member
    Apr 23, 2015 at 08:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Naveen Reddy Yolugoti

      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.