Comparing Database Table Size, records and Export Files

The is a listing a an Example Database Table, the size it consumes within the Database and the Size an Export of the same will consume. The “Part” Table contains information of certain Parts which are supplied by different manufactures and looks like the following:

 

The Table contains 1.4 Mio records:

Within the Database this Table consumes “only” 25 MB of storage:

When exporting the Table using the Export Wizard of SQL Developer and choosing the loader format,

the same Table consumes 147 MB of storage:

Thus, the is nearly a factor of 6 between the space a Table consumes within the Database and the exported information of the same.

counting rows for all database tables

The following SQL Code can be used to display the rowcount for all (Orale BI Applications) tables:

select table_name,
to_number(extractvalue(xmltype
(dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables
order by table_name;

for further reference, check here.

Installing Oracle R on Database VM Image

Download the Oracle Pre-Built Database Developer VMs from OTN.
Import the extracted VM into Virtual Box and bootup the Virtual Machine. After starting the VM, the following screen is presented witin Virtual Box:

check the Linux Version of the Downloaded Virtual Machine with below command:

# uname -r

For this VM it is: 4.1.12-61.1.27.el7uek.x86_64, thus: Oracle Enterprise Linux 7.

Install R (distribution) for Oracle R Enterprise

Afterwards, switch user to root (Password is “oracle”) and navigate to the yum Repository configuration:

using the vi (i command for – – inserting- – Mode and Esc for exiting the insert Mode, save with :wq) update the following sections within the repo file:

[ol7_latest]
enabled=1

[ol7_addons]
enabled=1

[ol7_optional_latest]
enabled=1

e.g.:

Install R with the following command

this automatically downloads and performs the installation:

Installing Oracle R Enterprise Server

Add the Oracle Home Lib to the Library Path environment variable:

This can also be added to the ~/.bashrc

As next step, download the Oracle R Enterprise software from OTN.

And store to a installation directory e.g. /u01/ORE_Inst_Dir

and unzip the files:

Start the installation by running the installer:

Installing Oracle R Client

Unzip the Client zip files:

switch to root user and install all unzipped files:

Start Oracle R Client with the ORE command and load the Oracle R libaries with library(ORE). When loading the R libaries, the following error is thrown:

Loading required package: OREembed
Error in dyn.load(file, DLLpath = DLLpath, …) :
   unable to load shared object ‘/u01/app/oracle/product/12.1.0.2/db_1/R/library/png/libs/png.so’:
   libpng12.so.0: cannot open shared object file: No such file or directory
Error: package ‘OREembed’ could not be loaded

Since, the file libpng12.so.0 is not available it needs to be installed:


[Thanks to the following article which features the same issue]

After installing the missing file, the ORE Libaries are loaded successfully within R:

Further references:

http://rajiv-kuriakose.blogspot.de/2016/03/how-to-install-oracle-r-enterprise-on.html

Official Oracle Documentation:

https://docs.oracle.com/cd/E57012_01/doc.141/e57007/install_oreserver.htm#BGBJIIAG

How to create a direct database query with OTBI?

Within Oracle Fusion OTBI go to New > Analysis and click on Create Direct Database Query:

specify the connection Pool information within the editor:

The following connection pools can be entered:

• HCM – ”HCM_OLTP”.”Connection Pool”
• CRM – “CRM_OLTP”.”Connection Pool”
• FIN and PROC – “FSCM_OLTP”.”Connection Pool”

Please note: when copying&pasting the connection pool names from WordPress, there may be an issue with the double quotes(“) and you may need to re-type the double quotes (“) within OTBI.

After that, a query may be entered for direct database execution on the Oracle Fusion Cloud (bundled) Database (identified by the Connection pool supplied before):

For a listing of all Physical Tables please refer to (only available for HCM): https://cloudcustomerconnect.oracle.com/posts/ac54c31fcf

This contains a Excel mapping with the Physical OTBI Tables:

 

For a full support note, please refer to:

• How To Create A Report with Direct Database Query in OTBI/OBIEE Analysis, Who Can Access Direct Database Query (Doc ID 2141412.1)

For a similar approach using BI Publisher please check:

• Creating a BI Publisher Report using SQL Query in Oracle Fusion HCM Cloud

To check the general TechNote for OTBI and Fusion HCM, please refer to:

• Oracle Fusion HCM: Resources for Oracle Transactional Business Intelligence (Doc ID 2023155.1)

Oracle BI EE 12c: Configuring first Oracle Database connectivity with TNS Names after installation

In case the oracle Database should be used by Oracle BI, the database connectivity must be configured after the installation using the Oracle Database driver and TNS Names configuration.

For the server machine

On the Server (where Oracle BI EE 12 is installed), the Installation routine of Oracle BI automatically ship’s a Instant Client Version of the Oracle Database Client (Database driver).
The files of the driver are located within the following directory:
/u01/apps/obi12c/product/Oracle_Home/instantclient

On Linux/Unix	On Windows	Description
libclntsh.so.10.1	oci.dll	Client code library
libociei.so	oraociei10.dll	OCI Instant Client data shared library
libnnz10.so	orannzsbb10.dll	Security library

The TNS Names configuration that should be used by the Oracle BI Server has to be specified by the sqlnet.ora and tnsnames.ora which need to be placed within the following directory:

/opt/biee/user_projects/domains/bi/config/fmwconfig/bienv/core

The following shows a example of the sqlnet.ora file:

And the following shows a example of the tnsnames.ora fiile:

Using above files the Oracle BI server should be able to connect to the database specified within the TNS Names file.

For the developer machine

The Oracle BI 12c Administration Tool (BI Developer Studio) requires the above (example) configuration files for sqlnet.ora and tnsnames.ora to be placed within the following directory of the client developer machine:
C:\Oracle\BIAdmin\domains\bi\config\fmwconfig\bienv\core

The TNS Name specified within the tnsnames.ora may then be used within the Connection Pool definition of the Oracle BI Metadata either by using the defined name or by using the fully qualified TNS Names entry. When refreshing the count of a database object such as a Table, the Oracle BI Server should then be able to retrieve the row count and display the row count within the Oracle BI Administration Tool:

In case of bug fixing, the log of the Oracle BI Administration Tool is located within the following path:
C:\Oracle\OracleBIClient\domains\bi\servers\obis1\logs