1,101 total views, 24 views today

How to set up a Data Source in BI Publisher

Before start developing a report, the data source must be setup in BI Publisher. The data source is simply a database connection the report is looking for data to generate reports. For example, a user wants to generate a report that displays the list of countries. Here the list of countries may come from a database table.

In this section, we detailed how to set up a data source in BI Publisher 12C.

Version User: Bi Publisher 12.2.1

 

Setup Data Source in BI Publisher

 

1. Login into XMLPSERVER as WebLogic admin. The default user is http://HOST:PORT/xmlpserver

 

032617_0450_Howtosetupa1 How to set up a data source in BI Publisher

 

2. Click on the link Administrator on the top right corner

 

032617_0450_Howtosetupa2 How to set up a data source in BI Publisher

 

3. Click JDBC Connection under Data Sources

 

032617_0450_Howtosetupa3 How to set up a data source in BI Publisher

 

4. Click the button Add Data Sources. You can create multiple data sources 

 

032617_0450_Howtosetupa4 How to set up a data source in BI Publisher

 

5. Enter your database connection details that you are going to generate a report. Also, input pre/post process function if any. That’s it.We are done. 

 

Pre Process Function: Enter a PL/SQL function that you want to execute when a database connection is created. The function must return a Boolean

Post Process Function: Enter a PL/SQL function that you want to execute when a database connection is closed. The function must return a Boolean

 

Note: I have entered the HR schema here which is a demo schema bundled with Oracle database. By default this is locked, to unlock it, log in to your database as system user and issue the query

 

 

 

032617_0450_Howtosetupa5 How to set up a data source in BI Publisher

govindan How to set up a data source in BI Publisher
Connect me

Govind

Thank you for visiting my personal blog. Myself Govindan, Software Developer by profession since 2006 and hence I started this blog early in 2016 and ever since I've been writing about technologies experienced and learnings of everyday life.

The views expressed on this blog are my personal views and do not necessarily reflect the views of my employer.

Please feeling free to reach me on any comments and feedbacks you have. Would be more than glad to listen and reply šŸ™‚
govindan How to set up a data source in BI Publisher
Connect me
By | 2017-03-26T00:15:37+00:00 March 26th, 2017|Categories: OBIEE|Tags: , |47 Comments
  • Lakshmi Narasimhan V

    Hi Govind,

    I gave the userid and password as directed by you. I will try again.

    Please advise whether we need to install Oracle BI Developer client tool 12.2.1.0.0. If yes, how to set up.

    Importantly, we are starting server manually. Is there any way to start from startup menu or how to create a short shortcut. Please let me know which screens are to be logged in:

    1. Localhost:9500/console
    2. Localhost:9502/xmlpserver
    3. Localhost:9502/Analytics
    4. Localhost:9500/em

    Is there any site to download tutorials and examples to improve my skills?

    Warm regards

  • Lakshmi Narasimhan V

    Thanks Govind
    I successfully logged into sqlplus as done by you and created a datamodel and created a report and ran it. Very happy.

    But I would like to know the userid and password for logging sqlplus. Please let me know.
    Warm regards

    • Govindan Padmanaban

      SQLPLUS is Oracle client utility to connect to the Oracle database. I dont know the username and password of your Oracle database instance.

  • Lakshmi Narasimhan V

    In sqlplus what is the userid and password

  • Lakshmi Narasimhan V

    Hi Govind

    My sid is orcl. So my connection string is jdbc:oracle:[email protected]:1521:orcl. But you have informed me to use localhost:1521:XE. Please advise. While installing db 12 c the system has specified my sid as orcl.
    Warm regards

    • Govindan Padmanaban

      Your JDBC URL is right.

  • Lakshmi Narasimhan V

    Hi Govind

    I trying to create a report as per your blog. I have to connect a datasource for hr. What is the user id and password ? I have given hr and hr which it is not accepting. N

    While creating oracle db I have already unlocked hr schema. If it is not ok, what shall I do?
    After this only i can go for creating a datamodel and report.
    Warm regards

    • Govindan Padmanaban

      Run the below query as system user

      ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

      • Lakshmi Narasimhan V

        In the sqlplus ?????

        • Govindan Padmanaban

          Yes

  • Lakshmi Narasimhan V

    Hi Govind

    In OBIEE configuration Asst. step 8 of 9 is taking too much time. So far roughly about three hours. How long will it take. Please advise me what to do.

  • Lakshmi Narasimhan V

    Hi Govind

    I await your reply regarding lower version of OBIEE. I do not want OBIEE 12c. But that should be compatible with the one you suggested for 12c. So that I can download and practice immediately. I appreciate your immediate reply.
    Warm regards

  • Lakshmi Narasimhan V

    Hi Govind

    I am unable to install obiee 12c throwing errors like 7545. Please advise me lower of obiee with matching db and fusion.
    Warm regards
    Sridhar

    • Govindan Padmanaban

      Check the below link for how to correct the error INST-07545. The BI version must match with the FMW version.

      http://www.catgovind.com/obiee/obiee-12c-error-the-distribution-bi_platform-12-2-1-0-0-contains-incompatible/

      • Lakshmi Narasimhan V

        Hi Govind,

        Thanks. I installed correct version of fusion. Now I am installing OBIEE 12c and got it. But while running RCU, error RCU 6002 occured. It says modify db configuration. Please advise me.

        Warm regards

        • Govindan Padmanaban

          Did you follow the instruction as per the following link especially at POINT 9?

          http://www.catgovind.com/oracle/how-to-install-oracle-database-12c-enterprise-edition-on-windows/

          • Lakshmi Narasimhan V

            Hi Govindan,

            Thanks for immediate reply. I am sorry I did not follow that part. Now what shall do? Can I deinstall and again install as per your instructions?
            Warm regards,

            • Govindan Padmanaban

              Yes, Deinstall and Install the Oracle database then run the RCU again .

              How to DeInstall oracle database
              ——————————————

              http://www.catgovind.com/oracle/how-to-completely-uninstall-oracle-database-12c-windows/

              • Lakshmi Narasimhan V

                Thanks. I deinstalled through start menu and somehow managed it. After that only I saw your blog in the google search. Anyhow you helped me in doing so. Now I will again install and OBIEE. I will try catch up things fast.
                After installing how am I configure and see the examples as per tutorial.
                If you can give me your number I would like to talk to you.
                Warm regards

                • Govindan Padmanaban

                  Please go through my blog posts for OBIEE

                  http://www.catgovind.com/category/obiee/

                  The best way to reach me is my blog or my facebook page; I will be active at most of the time

                  • Lakshmi Narasimhan V

                    Thanks, Please give your facebook link
                    Warm regards

                    • Govindan Padmanaban

                      Do you mean join between two tables? If yes, I will post it in this weekend.
                      I don’t have Feedback URL. Connect with me in LinkedIn/FB page for regular updates. Thanks!.

                    • Lakshmi Narasimhan V

                      I have already sent a request for friendship in fb. Please accept.

                    • Lakshmi Narasimhan V

                      Hi Govind,

                      Today morning I installed db and obiee. Also created rc utility. At the end of 5.7 screen BI domain configuration started. BI Configuration was over and next BI startup and BI Epilogue to be configured. I went outside for urgent work and system got struck up. Again I switched on and ran the rc utility again with different name as DEV1. Now what shall I do? Please advise.

                  • Lakshmi Narasimhan V

                    Please guide me for tutorials for master detail reports in OBIEE, possibly with links.
                    Warm regards

      • Lakshmi Narasimhan V

        Hi Govind,

        A warning window came. It says the selected oracle db is not configured to use AL32UTF8 character set. Please advise.

        Warm regards

  • Lakshmi Narasimhan V

    Hi Govind

    I am Sridhar 61 years old and would like to master Oracle BI Publisher 12c. How to download and set jdbc data connection? One gentleman told me to download virtualbox and then download. I have downloaded virtualbox but do not know what to do. is it nessary. I also would like to download oracle database for creating master detail reports. Pl guide me. My email id is [email protected]

    Thanks in advance

    • Govindan Padmanaban

      Have you installed Oracle BI Publisher?If not please follow the link

      http://www.catgovind.com/obiee/step-by-step-obiee-12c-installation-on-windows-oracle-bi-publisher-installation-and-oracle-bi-analytic-installation/

      You don’t need to download anything for JDBC connection., JDBC is simply establishing a connection between Oracle BI Publisher and the database schema (For example: HR Schema ). Oracle database installation is the pre-requisite for the BI Publisher installation. HR Schema comes with the database. Below is the Oracle Database installation . Please let me know if yoy have any questions

      http://www.catgovind.com/oracle/how-to-install-oracle-database-12c-enterprise-edition-on-windows/

      • Lakshmi Narasimhan V

        Thanks Govind
        I will download Oracle BI 12c from the link given you. I have oracle database 11g express edition and do I have to download other version? Regarding jdbc connection, my doubt is how to give connection string value i.e. Jdbc:oracle:thin:@hostname:port:SID. Here what value I have to give in @hostname, port and SID. How will I know the port no.

        Regarding virtualbox, what shall I do now.

        Sorry for bothering you.
        Warm regards

        • Govindan Padmanaban

          In your case,

          hostname=localhost
          port=1521
          SID=XE

          Please ignore the VIRTUALBOX in case if you are installing OBIEE as per the link I sent you earlier.

          • Lakshmi Narasimhan V

            Hi Govind

            I have ignored virtualbox, I will download OBIEE as advised by you. I hav jio and can download only 1 gb a day. I will follow your instructions and install. Regarding oracle database which version I have to install?

            Thanks a lot
            Warm regards
            Sridhar

            • Govindan Padmanaban

              I tested the OBIEE with the Oracle Enterprise Edition 12.1.0.2;

              http://www.catgovind.com/oracle/how-to-install-oracle-database-12c-enterprise-edition-on-windows/

              • Lakshmi Narasimhan V

                Thanks for your prompt reply. I will certainly install oracle ee. But it will take a week’s time as I can download one gb a day. Is there any faster way to download the above softwares? I am living in Erode. Also I have a new laptop with windows 10 and wuld like to know whether the above software can be installed and worked?
                Warm regards
                Sridhar

                • Govindan Padmanaban

                  I am not sure about where to download but It should work on Windows 10. I installed all the application on Win 10.

                  • Lakshmi Narasimhan V

                    Hi Govind,

                    Today I have downloaded both the zip files. But when I clicked the set up files to run as Administrator while extracting an error response window displayed saying “could not extract the archieve since it is corrupted. Error code=1”. What shall I do?

                    Regards
                    Sridhar

                    • Govindan Padmanaban

                      It is due to the file is not 100 percent downloaded

                    • Lakshmi Narasimhan V

                      Thanks Govind.
                      Do I have to again download? I have also downloaded oracle db. That is also throwing errors that it could not find files. What to do? Now second time I am trying to install the db. Then I will try OBIEE 12c.

                      Warm regards
                      Sridhar

                    • Lakshmi Narasimhan V

                      Hi Govind

                      Do I have to download Oracle Fusion middleware 12.2.1 othert than OBIEE ?
                      Please clarify.
                      Warm regards,
                      Sridhar

                    • Govindan Padmanaban

                      Yes. The OBIEE will sit on top of FMW.

                    • Lakshmi Narasimhan V

                      Installation of oracle db is taking too much time i.e. Nearly two hours. Yet it is stopping at 54%. Second time also I have cancelled. How should it take to install oracle db? Please confirm whether I hve to install fusion middleware prior to installation of OBIEE.
                      Warm regards
                      Sridhar
                      PS: If interested please give me your cell no. And time to contact. It is taking longer time to install.

                    • Lakshmi Narasimhan V

                      Hi Govindan,

                      unable to install db 12c.Today also the progress bar staying at 54% saying updating registry key etc., for more than one hour. Should I stop it or wait for another one hour?

                      Please give me your cell number so that I can talk to you.

                      Warm regards
                      Sridhar

                    • Lakshmi Narasimhan V

                      Hi Govind

                      Finding it difficult to install obiee 12c. It giving error as 7545. Ok now please tell me a lower version of 12c to go ahead with relevant fusion middleware and oracle db.

                      Warm regards
                      Sridhar

  • Ramya Kashyap

    Hi Govind,

    Your article is very useful. I have some followup on question on BIP.

    Can we add and use more than one DB source (example : JDBC 1 and JDBC 2) on BIP?

    Thanks in advance!

    Ramya Kashyap

    • Govindan Padmanaban

      Yes.

Like us on Facebook.
Connect!