Installation and Configuration of Oracle Analytics server

JDK Installation

1.       Download and extract the recommended JDK version.

Fusion Middleware Installation

1.       Start the Fusion middle ware installation

$JAVA_HOME/java -jar fmw_12.2.1.4.0_infrastructure.jar

In welcome screen, click on Next

Select “Skip Auto Updates”, click on Next

Provide “Oracle Home” path and click on Next

Select “Fusion Middleware Infrastructure” and click on Next.

If Prerequisite Checks completed successfully, click on Next

Click on Install

Click on Next

Click on Finish

Apply Patch set

After installing, we need to apply the 30657796 patch which was downloaded from e-delivery
Navigate to downloaded patch directory, set ORACLE_HOME and PATH environmental variables and run opatch apply command

OAS Installation

Start Oracle Analytics Server Installation as shown below

On welcome screen, click on Next

Select “Skip Auto Updates”, click on Next

Provide “Oracle Home” and click on Next

If Prerequisite Checks completed, click on Next

Click on Install

Click on Next

Click on Finish

RCU Configuration

Navigate to oracle_common/bin directory, run RCU as shown below

On Welcome screen, click on Next

Select “Create Repository”, click on Next

Enter the Database details and click on Next

Click on OK

Select Create new prefix as per “Best Practices” and select Oracle Business Intelligence. Click on Next

Click on OK

Enter password for newly created Schema’s, click on Next

Click on Next

Click on OK

Click on OK

Click on Create

Click on Close

OAS Configuration

1.       Next step is to configure OAS, for this we need to run

cd $ORACLE_HOME/bi/bin

./config.sh

On Welcome screen, click on Next

Select below options and click on Next

If Prerequisite checks completed, click on Next

Provide Domain Directory details and click on Next

Enter database schema details, click on Next

Click on Next

Select Clean State, click on Next

Click on Configure

Click on Next

Click on Finish

Accessing Oracle Analytics services

Weblogic console

http://172.16.11.180:9500/console

Fusion Middleware Control

http://172.16.11.180:9500/em

BI Publisher

http://172.16.11.180:9502/xmlpserver

DV

http://172.16.11.180:9502/dv/ui

Analytics

http://172.16.11.180:9502/analytics

Oracle Analytics Server Data Visualization Machine Learning Fails With 'Something Went Wrong' Using Insight Explain Feature or Data Flow

Oracle Analytics Server Data Visualization Machine Learning Fails With 'Something Went Wrong' Using Insight Explain Feature or Data Flow

Show Details 

Step |j| Execution failed. Status: FAILED. Message: [nQSError: 46240] Python Process exited with non 0 exit Code

[nQSError: 43224] The Dataflow "Address State Data Flow" failed during the execution.

[nQSError: 43204] Asynchronous Job Manager failed to execute the asynchronous job.

Solution:

Login to the server

Using 'sudo' or as 'root' user, install the libgfortran package

Restart OAS:

• stop.sh
• start.sh

SQL query to find top 50 tables by size

select top50.owner, top50.table_name, meg, a.num_rows
from dba_tables a,
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;


Note: Change 50 to any other number of tables you want to find

Loading xml file data to oracle table using python

Sample xml file (test.xml):

<?xml version="1.0"?>

<data>

    <customer name="Ravi" >

        <email>lravikumarvsp@gmail.com</email>

        <phone>9999999999</phone>

    </customer>

    <customer name="kumar" >

        <email>kumar@gmail.com</email>

    </customer>

    <customer name="lanke" >

        <email>lanke@gmail.com</email>

        <phone>8888888888</phone>

    </customer>

    <customer name="ravikumar" >

        <phone>7777777777</phone>

        <address>

            <street>kukatpally</street>

        </address>

    </customer>

</data>

Python script for parsing xml file data to data frame:

import pandas as pd

import xml.etree.cElementTree as et

#Reading Data from the xml file

parsedXML = et.parse( "test.xml" )                                                                         

dfcols = ['name','email','phone','street']                                                                                

df = pd.DataFrame(columns=dfcols)

def getvalueofnode( node ):

                return node.text if node is not None else None

for node in parsedXML.getroot():                                                                              

                name = node.attrib.get('name')

                email = node.find('email')

                phone = node.find('phone')

                street = node.find('address/street')

                df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)

print (df)                                                                                                                                  

Run the program as below:

Now create a table with column names in the data frame.

CREATE TABLE TEST 

   (                          "NAME" VARCHAR2(200 BYTE),

                               "EMAIL" VARCHAR2(200 BYTE),

                               "PHONE" VARCHAR2(200 BYTE),

                               "STREET" VARCHAR2(200 BYTE)

   )

Python script for loading xml file to oracle table.

import pandas as pd

import cx_Oracle

import xml.etree.cElementTree as et

#Reading Data from the xml file

parsedXML = et.parse( "test.xml" )                                                                         

dfcols = ['name','email','phone','street']                                

df = pd.DataFrame(columns=dfcols)

def getvalueofnode( node ):

                               return node.text if node is not None else None

for node in parsedXML.getroot():                                                                               

                               name = node.attrib.get('name')

                               email = node.find('email')

                               phone = node.find('phone')

                               street = node.find('address/street')

                               df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)

print (df)                                                                                                                             

#Loading Data into the Database Table

host1 = '172.16.11.32'

port1 = '1521'

sid1 = 'srcdb'

schema1 = 'system'

pwd1 = 'Admin123'

sdsn = cx_Oracle.makedsn(host1, port1, service_name=sid1)    

connect=cx_Oracle.connect(schema1, pwd1, sdsn)                                                        

cursor=connect.cursor()

dataframe_list=df.values.tolist()                                                                                                              # convert the dataframe to list

sql='INSERT INTO TEST values (:1,:2,:3,:4)'

for index,elem in enumerate(dataframe_list):                                                   

                               cursor.execute(sql,dataframe_list[index]) 

                               connect.commit()                                                             

Run the script to load the data to oracle table now

Check the table for data