Change Schema for OBIEE Reports

User_1JG01 · May 5, 2017 2:10PM

Hi,

Currently our reports are running off old DW schema called say: SCHEMA_old

and recently we have create new schema called: say : SCHEMA_NEW and they both reside in same DB,

so the existing reports are running off of old schema that we are going to decommission sometime soon, hence we would like to point OBIEE repository & reports to New schema called:SCHEMA_NEW

Can you please help me with your inputs

Regards:

Bhasker.v

Jsglp

Sherry George · May 5, 2017 2:59PM

You should be able to to change the schema in the connection pool and that should be all I guess. I assume only the Data Warehouse schema is getting changed.

User_1JG01 · May 5, 2017 3:12PM

Thanks for your inputs Sherry George

that is true only DW schema is changed to _New

I noticed previous X-colleague of mine did following

in the Connection Pool

+++++

Data Source Name: it has db details HOST & SERVICE &Port etc

and Under Shared Logon he created a user name called: DW_OBIEE & Password:

and he created physical schema called: SCHEMA_old (and it has all the objects where actual data used to reside)

so now i want to : DW_OBIEE to have that physical schema to read this data from Schema_new not frm Schema_old

not sure how?, just by changing the physical schema name SCHEMA_new, will take care of everything , somehow it doesn't look like to me atleast

Regards:

Bhasker.V

Jsglp

Sherry George · May 5, 2017 4:38PM

ok, are you using the fully qualified table names in connection pool? if you are then you can change the physical schema name to "schema_new" (actually do this in any case, I didn't mention it in my first reply)

Edit: Adding a little more detail. So if you want to use "DW_OBIEE" schema (user, assuming it is an Oracle database) to query the new schema "Schema_NEW" the DW_OBIEE user need to have select privileges on tables in "Schema_NEW". Also synonyms need to be created for tables in "Schema_NEW" if you are not using fully qualified name.

User_1JG01 · May 8, 2017 1:22PM

Thanks Sherry George, that helped for me to get started

Now i would like to do couple of things,

+++++++++++++++

One is i wanted to import repository(.rpd) and backup file that we expected by previous employee on BI Server, so i have copy with me as i keep making changes

+++++++++++++++

and secondly

on the physical layer say there is table called: EMP.EMPNO currently this set to INT in rpd, however as part of the new schema design that we talked about previous

we have changed some of the data type on actual ID Columns(Surrogate keys) to VARCHAR2(51) as part of our DW db re-design

So can you please tell me how I take this forward

Please correct me if this right

--step1>Change in the physical Schema , goto each table and each column say EMP.EMPNO change it from INT to type: VARCHAR LENGTH: 51

repeat the same for all the objects

then finally ---->TOOLS-->SHOW CONSISTENCY CHECKER

and make sure there are no errors

Please add/correct if you find any disconnects

Regards:

Bhasker.V

Jsglp

Sherry George · May 8, 2017 2:35PM

Hi Bhasker,

For backup depending on the OBIEE version, you can download the current live rpd in 12c using command line tool. The documentation is below.

https://docs.oracle.com/middleware/12212/biee/BIEMG/GUID-C0BBA452-CC54-43A3-9A61-F1ADCCBEBDDB.htm#upload_repos_cmd

For 11g you can use the Admin tool or go to the physical location in the server to copy the RPD.

For the second requirement the steps looks ok. You would need to make sure that the joins, any calculations in BMM or at the report level are not screwed up due to the data type change. You may also want to look at the Admin tool utility "Update Physical Layer" to do mass updates.

[Deleted User] · May 8, 2017 2:42PM

Sherry George wrote:Hi Bhasker,For backup depending on the OBIEE version, you can download the current live rpd in 12c using command line tool. The documentation is below.https://docs.oracle.com/middleware/12212/biee/BIEMG/GUID-C0BBA452-CC54-43A3-9A61-F1ADCCBEBDDB.htm#upload_repos_cmdFor 11g you can use the Admin tool or go to the physical location in the server to copy the RPD.

Online connection with Admin Tool and "Save As" of the currently running RPD is also standard functionality in 12c.

Sherry George · May 8, 2017 3:12PM

Hi Christian,

When I open the rpd in online mode the "Save As" is greyed out. While if I choose "Copy As" then it is asking for a new password. Is this expected or am I missing something.

[Deleted User] · May 8, 2017 3:20PM

Sorry typed too vague. "Copy" yes not "Save".

And the pwd is expected. You need one to secure offline RPDs.

Sherry George · May 8, 2017 3:31PM

Thanks Christian for clarifying.

User_1JG01 · May 9, 2017 11:24AM

Thanks Sherry George , Thanks Christian Berg,

Finally everything went fine, i changed schema names, data type, validated consistency (no Errors but few warnings though)

and deployed rpd into bi server,

now i could see that Dashboards do fetch data from our new schema that we changed which is good

however on some of the report , when i tried filter date, or some other propert and hit apply i get this following error not sure what it is tell me

"TypeError: Unable to set property 'bCalledFromMobileAPI' of undefined or null reference"

Any inputs, highly appreciated the knowledge that you have shared so far

Regards:

Bhasker.V

Jsglp

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Change Schema for OBIEE Reports

Answers