OBIEE 12 Admin tool OCI Connectivity

Connecting to the Admin tool Online : Find the Port

netstat -plnt

Find the nqsserver ports --- 9514 was the candidate for OBIEE RPD Connectivty online

tcp        0      0 :::9514                     :::*                        LISTEN      24868/nqsserver
tcp        0      0 :::9515                     :::*                        LISTEN      24868/nqsserver


Anyway Moving on TNS Setup.......

12c is using a local TNS in Online Mode and Offline Mode and not using the TNS_ADMIN on a windows machine.

However you can view the data in the tables within the RPD( Weird huh) as it using the network tnsnames.ora


Anyway goto the OBIEE Administration tool installation location and paste a tnsnames.ora file.

Steps: goto the OBIEE Administration tool installation location

[OBIEE Installation Directory]\Oracle_Home\network

Create a Directory called admin

Then within that paste a valid tnsnames.ora file.

Restart the BI Administration tool and everything works as it should ie creating tables from DB.

ODI 12C and OBIEE 12C Integration with SOAP WSDL

I have attached a link to the step by step instructions at the end of this post on how to integrate SOAP WSDL from ODI to OBIEE so you can start ibots or do various other tasks within OBIEE from ODI without the need of a dedicated ODI agent on the OBIEE Server. This solution was developed in 12c.

Business Case

One of our customers wanted OBIEE reports to be mailed after the completion of a data load in ODI. We know there are different methods of achieving this. But we did not want to deploy an ODI Agent onto the OBIEE Server in the Corporate Environment.

That is the need and here is the solution we came up with – Using WSDL & SOAP approach to build bridge between the two most popular Oracle technologies in Data Warehousing world.

Solution Technology

We used the two following technologies that are made available as part of ODI and OBIEE while building the solution

WSDL (Web Service Definition Language) is often used in combination with SOAP and an XML Schema to provide Web services over the Internet. A client program connecting to a Web service can read the WSDL file to determine what operations are available on the server. Any special datatypes used are embedded in the WSDL file in the form of XML Schema. 

SOAP (Simple Object Access Protocol) is a protocol specification for exchanging structured information in the implementation of web services in computer networks. It uses XML Information Set for its message format, and relies on application layer protocols, most often Hypertext Transfer Protocol (HTTP) or Simple Mail Transfer Protocol (SMTP), for message negotiation and transmission

Solution Overview

The solution enables us from ODI to remotely login to OBIEE and initiate an iBot and log out. Following are the high level steps

   1.     Use OdiInvokeWebService to login to OBIEE with SOAPImplementation URL  

   2.     The above call creates a response file with the OBIEE Session Id

   3.     Use a Simple mapping to move this file content into a database table

   4.     Create a variable with sub string and in string operations to extract the OBIEE session id

   5.     Use OdiInvokeWebService to initiate an iBot with the above session id and iBot name to        mail the required report to recipients

   6.     Use OdiInvokeWebService with the same session id to log off from OBIEE

We have created an ODI package to orchestrate the above steps which executes the above steps in a seamless chain.

Deploying/upload rpd in OBIEE 12.2.1.0 / 12c server

Unlike in 11g, em does not have any option for deployments of rpd, it is all now uploading repository to a specific service instance. we should be using weblogic scripting command “uploadrpd’ to upload repository to Oracle BI Server.
You execute the utility through a launcher script, data-model-cmd.sh on UNIX and data-model-cmd.cmd on Windows. You can find the launcher script at the following location:
<Oracle_Home>/user_projects/domains/bi/bitools/bin
>wlst.cmd
Syntax:
uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

• I specifies the name of the repository that you want to upload.
• W is the repository’s password. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.
• SI specifies the name of the service instance.
• U specifies a valid user’s name to be used for Oracle BI EE authentication.
• P specifies the password corresponding to the user’s name that you specified for U. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.
• S specifies the Oracle BI EE host name. Only include this option when you are running the command from a client installation.
• N specifies the Oracle BI EE port number. Only include this option when you are running the command from a client installation.
• SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when you are running the command from a client installation.
• H displays the usage information and exits the command.

Example: data-model-cmd.sh uploadrpd -I <RepositoryName.rpd> -SI ssi -U weblogic -S server.example.com -N 9502 -SSL

Note: Using “uploadrpd”, we can upload an Oracle BI repository in RPD format and cannot upload a repository composed of MDS XML documents.
The online rpd in the server is with name liverpd.rpd_n (n is integer with increased number) and can be found at: <Oracle_Home>\user_projects\domains\bi\bidata\service_instances\ssi\metadata\datamodel\customizations
You can log into analytics and see the subject area contents of new rpd with changes affected.
You can also download current online rpd from server which acts as offline rpd on which you will be developing your changes or for any diagnose purpose
syntax: downloadrpd -O RPDname [-W RPDpwd] -SI service_instance -U cred_username [-P cred_password] [-S hostname] [-N port_number] [-SSL] [-H]

rpd will be saved under the same location where you executed above command..