1 2 Previous Next 19 Replies Latest reply on May 10, 2017 1:38 PM by Christian Berg

    Change Schema for OBIEE Reports

    2881023

      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

        • 1. Re: Change Schema for OBIEE Reports
          Sherry George

          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.

           

          • 2. Re: Change Schema for OBIEE Reports
            2881023

            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

             

             

             

            • 3. Re: Change Schema for OBIEE Reports
              Sherry George

              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.

              • 4. Re: Change Schema for OBIEE Reports
                2881023

                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

                • 5. Re: Change Schema for OBIEE Reports
                  Sherry George

                  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.

                  • 6. Re: Change Schema for OBIEE Reports
                    Christian Berg

                    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_cmd

                     

                    For 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.

                    • 7. Re: Change Schema for OBIEE Reports
                      Sherry George

                      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.

                       

                      • 8. Re: Change Schema for OBIEE Reports
                        Christian Berg

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

                         

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

                        • 9. Re: Change Schema for OBIEE Reports
                          Sherry George

                          Thanks Christian for clarifying.

                          • 10. Re: Change Schema for OBIEE Reports
                            2881023

                            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

                            • 11. Re: Change Schema for OBIEE Reports
                              Sherry George

                              Hi Bhaskar,

                               

                              Not sure if the error is related to schema change. What I could find was a support document.

                              https://support.oracle.com/epmos/faces/DocumentDisplay?id=2167987.1

                               

                              I do not know what changes other than the schema change was done to the environment. What is your OBIEE version?  Also test in a different browser.

                              • 12. Re: Change Schema for OBIEE Reports
                                2881023

                                Hi Sherry George,

                                thats very helpful , its right on exact same error m getting

                                to answer we are using : Oracle Business Intelligenc 11.1.1.9.0

                                 

                                guess , i will need to figure our where this java code was built? so i can modify per the article to see if that helped

                                do you know where can i find the java code with in obiee?

                                 

                                Regards:

                                Bhasker.V

                                Jsglp

                                • 13. Re: Change Schema for OBIEE Reports
                                  Sherry George

                                  Hi Bhaskar,

                                   

                                  You will need to provide more details about what exactly you are experiencing with "some" reports. This can include your observations like if it is happening for some particular columns or in dashboard prompts etc..A reproducible test case will help.  May the report has some java script in narrative view (you can find this in results tab).

                                   

                                  I will also suggest that you may close this thread by marking it "Answered"(since your original question about pointing OBIEE to new schema has been answered)  and open a new one to discuss the current issue, (you can refer this thread from the new one) and you can provide all the details about the issue there.

                                  • 14. Re: Change Schema for OBIEE Reports
                                    2881023

                                    Hi Sherry George,

                                     

                                    thanks for all your inputs, infact that doc that you have shared did help me in resolving that java error it perfectly worked with that solution

                                    with Catalog-->Dashboards-->Report(Select)-->Edit-->Selection 1 -->Text 1--> copy that Java code and paste in notepad/editplus etc, find below text(1st one)  and change to 2nd one

                                    Change from -->

                                    if( ! h.verifyPromptValuesAndDisplayError(b))

                                     

                                    Change to-->

                                    if( ! h.verifyPromptValuesAndDisplayError(b,{bCheckForRequired:true}))

                                     

                                    Thanks for all your help and support

                                     

                                    I truly appreciate all your inputs

                                     

                                    Regards:

                                    Bhasker.V

                                    Jsglp

                                    1 2 Previous Next