Oracle Analytics Cloud (OAC) was updated recently to support direct connectivity with Oracle Planning & Budgeting Cloud Service (PBCS) and EPBCS. Consequently, we can now source data live from PBCS applications without the need to extract and import into OAC. I have seen several financial departments use PBCS as a source for budgets and forecasts to compare against actuals from ERP. In addition, OAC enables dynamic drilldowns from PBCS summary views into multiple levels of drill-to reports from General Ledger journals and sub-ledger transactions. In this blog I will go through the steps that are required to enable the direct connection from OAC to PBCS. Note that this direct connectivity works from within the OBI repository, and not the connection adapter options available in the OAC landing page.
- Download Oracle BI Administration Tool V126.96.36.199.0 or later. (See Oracle Business Intelligence Developer Client Tool.)
- Install the client tool you downloaded in the first step. (Note that you can only install the client tool on a Windows operating system. Even though OAC servers run Oracle Linux, the BI Admin client tool only runs on Windows.)
- Configure the client tool “admintool.cmd” file by adding the variable (see screenshot below): INSTANCE_NAME=%DOMAIN_HOME%
In my example, the “admintool.cmd” file is located under the following directory:
- Configure the client tool “NQSConfig.INI” file to reference the Java Host of your OAC service.
Before doing that you will need to know the host name of your OAC server and the Java Host port number. To find what port Java Host is using, in a browser navigate to Enterprise Manager. Then from the upper left menu, expand Business Intelligence, and click on “biinstance”. Then select the “Availability” tab and expand “BI JavaHosts”. This should show you the Java Host port number. In my example, the screenshot below shows that it’s 9506.
Now find the NQSConfig.INI file in the local directory where you installed the client tool. In my example, it is located under the following directory:
Edit the file and set: JAVAHOST_HOSTNAME_OR_IP_ADDRESSES=”host:port”
Replace host with your OAC host name or IP address. Replace port with the port number you found in Enterprise Manager earlier.
- Import Metadata from PBCS: Open up the BI Admin client tool. You can run it by going to the Windows start menu, and under Oracle Business Intelligence Client, select “Administration”. Once it opens, create a new repository from the File menu and give it a name. When you are at the step to Import Metadata, you need to specify the PBCS data source. Do the following selections:
Connection Type: Hyperion ADM
Provider Type: Hyperion Planning
With: adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<PBCS IP or Host Name>:Vision?locale=en_US;tenantName=<PBCS Identity Domain>;hubProtocol=https;
In the above, you need to substitute for your PBCS IP or Host name and your PBCS Identity Domain. In addition, the above url connects to the Vision app so you will need to replace “Vision” with the name of the PBCS app you are connecting to.
And enter a PBCS administrator User Name and Password.
If you get an error at this step, you want to check your Admin Tool log file (NQSAdminTool.log) for more information. In my case, I found the log file under the following local directory:
- Select PBCS Applications: When you click Next on the Import Metadata Data Source screen, you will get the following screen to select from your PBCS Metadata Objects and move the ones you would like to integrate into OAC to the Repository View on the right side. When you finish, the repository Physical layer will list the PBCS applications you imported.
- Finalize Repository: Drag the Physical Layer objects over to the Business Model layer and then to the Presentation layer as in the following screenshot. Then run the Consistency Checker from under the File menu and save the file.
- Upload the Repository file to the OAC server. In a browser, navigate to your OAC Service Administration screen and select Snapshots. Click on Replace Data Model. Choose the Repository file you saved locally and give it a password. Once you click OK, it may take a few minutes for the file to get uploaded and set in place as your new data model for OAC. Note that it will replace whatever data models you may have created before using the browser-based thin data modeler. So take a backup snapshot before uploading the repository file.
- Create Reports: Once the new data model is in place, you will notice that your existing reports wont be working anymore. This is after all because your existing data model is now replaced with a new one. And you notice that under Subject Areas you see your PBCS application(s) available for reporting. Following are screenshots of quick reports I created against PBCS using an Analysis and also a Data Visualization project.