This appendix gives a quick version of the Business Intelligence Administration tool installation steps.
Using Oracle ODBC driver to connect OBIEE with Tableau is challenging and is not a. The Tableau knowledgebase article Connect to Oracle BI Server with. In addition, it requires the installation of a copy of Oracle Business Intelligence. Dec 17, 2010 - ODBC drivers. HKEY_LOCAL_MACHINE SOFTWARE ODBC ODBCINST.INI Oracle BI Server 11g. Oracle BI Server 11g. Have fun and so long.
E.1 Installing the Tool
The Repository, or RPD, is the file that contains the metadata for the BI Server in Fusion Applications. This includes database connections, tables, joins, and the structures by which these are presented to the report writer. In order to read or make changes to the RPD file, the BI Administration Tool must be installed.
E.1.1 System Prerequisites
- 64-bit Windows OS is required to run the BI Administration tool.
- The BI Admin tool version must match the Fusion Applications version you have installed.
E.1.2 Locate and Install the Software
There are two different ways to obtain the correct version of the tool:
- From the Oracle Fusion Applications BIEE Analytics Home, download the 64-bit Windows Oracle BI Client Installer.
- OR: Copy it from the BI Server from this path:
FA_HOME/fusionapps/bi/clients/biserver/biee_client_install_x64.exe
Once you have downloaded, run the bi
ee_client_install_x64.exe
executable to install the tool.![Oracle Bi Server Odbc Driver Install Oracle Bi Server Odbc Driver Install](http://cdn-ak.f.st-hatena.com/images/fotolife/a/atsm/20130530/20130530143138.png)
E.1.3 Set up ODBC Connection to Fusion Applications
To set up the ODBC connection from the Windows machine, you must locate the correct port on the Fusion Applications BI Server. Typically it is 10206, but if ports were changed as part of the FA install, perform the following steps:
- Log in to the BI Server's Enterprise Manager Console.
- Expand Business Intelligence within the Farm_BIDomain and select coreapplication. Select Overview and the sub tab Processes.
- Expand the BI Servers, and make a note of the port number.
Armed with the correct port, you are ready to create a new ODBC connection.
- Select Oracle BI Server Driver on the Windows machine.
- Enter the Server name for the host that contains the BI Domain. Click Next.
(If necessary, add an entry to thewindows/system32/drivers/etc
file, so that the windows machine can resolve the host name.) - On the resulting screen, enter the BI Server port (either 10205 or derived above) and the user name/password with which you plan to connect. Click Next.
E.1.4 Connect to RPD in BI Admin Tool
The user could now connect to the BI Server in On-Line mode and could update the live RPD. CAUTION! Depending on network speed and the size of the RPD, this can be both slow and dangerous. Updating a live RPD while Fusion Applications users and reports are working with it is not advised.
A better solution is to copy the RPD file from the BI Server to a local or shared drive the Windows client can access, updating the RPD in off-line mode, and then publishing it through the BI Server's Enterprise Manager console.
The RPD files are stored in the following path on the BI Server:
BIInstance/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository
.To find the correct version:
- Log in to Enterprise Manager, go to the Business Intelligence / coreapplication section, and select the Deployment / Repository tabs.
This will identify the current version of the RPD. - Copy that version locally or to the shared directory, to ensure you are working with the most current RPD.
In general, there are several links available to download the ODAC components from the Oracle official site. It varies across the Oracle versions, and the type of installer that we would need. Most of the time, we look for the .exe installer to install the Oracle related drivers as we are more comfortable with the Windows MSI installers.
In this blog, we will go through the ODAC driver installation and configuration with the SQL Server Reporting services (SSRS) data source to connect Oracle data source using Oracle native driver.
![Install oracle bi server odbc driver Install oracle bi server odbc driver](https://myobieespace.files.wordpress.com/2015/12/obiee-client-tools-dsn2.jpg)
Assumption:
- We would be using a single system to develop the report using SQL Server Data Tools (SSDT) and host the report in Report Server. It means SSDT and SSRS both are installed in the same system.
- SSDT version – Visual Studio 2017
- SSRS – SQL Server Reporting Services 2014.
Steps:
1. SSDT runs in 32 bit whereas SSRS runs in 64 bit. Since we are in the same system, we need to install both the bit-ness of Oracle drivers one by one.
We need to go to the Oracle official site to search for the drivers for the required version. Please note that we need to check for the installer which has the OLE DB/ ODP. Net (Oracle data Provider for .Net components.)
For 64-bit driver-
(Download link available during the time the blog had been written)
For 32-bit driver- http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
(Download link available during the time the blog had been written)
2. After uncompressing the file, you will see the installer file. Once you double click on that, you will get the information on GUI which is self-explanatory.
You need to note down where the oracle driver is getting installed, you need to place the tnsnames.ora file in that location which we will discuss later. For me, the installation folder as
E:appclientxxxxproduct12.2.0client_1 (32 bits)
E:appclientxxxxproduct12.2.0client_2 (64 bits)
Client_#, the number will change according to your installation order.
No need to update any environmental variable. During the installation, it was already done. You can check the same going to the My computer -> Properties -> Advance System setting -> Environmental Variables -> System Variables -> Path
3. Once the 32 bits and 64 bits driver installations are done, you need to place the tnsnames.ora file in the following location
E:appclientxxxxproduct12.2.0client_1NetworkAdmin
E:appclientxxxxproduct12.2.0client_2NetworkAdmin
The format of tnsnames.ora file as below- Ref: https://docs.oracle.com/cd/F49540_01/DOC/network.815/a67440/appb.htm
4. So, we have now successfully installed the Oracle drivers. The next step is to test the connection from Visual Studio and SSRS.
5. Open the SSDT. I tested in SSDT 2017. Create a Report Server Project –> File-> New -> Project -> Reporting Services -> Report Server Project
6. After creating the Reporting Services Project, you need to do the test connection. You can do the test connection by following the below screenshot. Please note that here we are using the OLE DB driver.
7. The same test connection, we will now be doing in SSRS. To do that we need to register the OraOLEDB driver. Go to the command Prompt (Run as Admin) -> and then run the following command. The path might vary as per your installation directory. We need to register the 64 bits driver since SSRS run on 64 bits ( E:appclientxxxxproduct12.2.0client_2binOraOLEDB12.dll)
8. Once this is done, after deploying the project, we can do the test connection in SSRS
9. We can use the ODP.Net to connect to the SSRS as well. This is the default driver which shows in SSDT. We need to register the ODP.Net DLLs. Go to the 64 bits installation folder. For me it is -
E:appclientxxxxproduct12.2.0client_2
Run the following commands using the command Prompt (Run as admin).
10. Once it is done, we can test the connection from SSRS, and as well as from SSDT.
This is how you would be able to test the connectivity from the SSRS to the Oracle database. If it is not working, you might need to test the Oracle connection outside of SSRS. If that does work, and only connections made from SSRS fails, then I would recommend you contact the Microsoft Support team.
Author: Samarendra Panda - Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft