Oracle Fusion > BI Publisher to Power BI via Python: wait for connection establishing

[arkadym]

Hello,

I was able to connect to Oracle Fusion > Analytics > BI Publisher Report (.xdo) via Python Pandas.

The script works well in Pycharm and provides the expected data.

But when I try to run the same script to load the result into a Power BI Desktop dataset using the same Python path as in Pycharm

and define my Pycharm.exe file in the IDE. I get a continuous message "Please wait while we establish a connection to Python".

I'm using the latest version of Power BI Desktop.

Thank you

[Syndicate_Admin]

Hi,

I would suggest using BI Publisher Connector which is an easy way to directly connect Power BI to Oracle Fusion Cloud database (ERP, HCM, SCM, and CX). You dont have to worry about coding or connection errors. BI Publisher Connector helps fetch data directly from Oracle Fusion Cloud database and physical layers of OBIEE/OAC/OAS via OTBI data models.

Its compatible with both Power BI Desktop and Service and all editions (including Pro and Premium). It also doesnt store any data, ensuring secure data connection. You can start visualizing data in minutes.

Some more benefits:

Error-free reporting & data visualization
Automate in Power BI Service
Hassle-free, one-time setup in mins

No-code, user-friendly tool

Check out more details here.

[arkadym]

the additional free way is call soapEnvelop within power query. But I prefer Python: I saved the above python script with encripted pwd as parametrized interface. This way I get data with just applying to the metioned interface with xdo - name and path    

[arkadym]

Hi,

Thank you.

I'm aware about the connector. But it's at extra cost, while Python is free. 

[arkadym · Accepted solution]

you won't believe , but the issue was in redundant blanks after the last row.

So this work well.

As compensation for your time, I provide here the working code. Perhaps it is helpfull

import requests
import lxml.etree as ET
import pandas as pd
import base64
import re

# Set up the SOAP request
url = 'https://myhost.oraclecloud.com:Port/xmlpserver/services/ExternalReportWSSService'
headers = {
'Content-Type': 'application/soap+xml;charset=UTF-8'
}
data = '''<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
<soap:Header/>
<soap:Body>
<pub:runReport>
<pub:reportRequest>
<pub:reportAbsolutePath>./.../.../MyReport.xdo</pub:reportAbsolutePath>
<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
</pub:reportRequest>
</pub:runReport>
</soap:Body>
</soap:Envelope>'''

# Set up the basic authentication credentials
user = 'myuser'
password = 'mypwd'

# Send the SOAP request with basic authentication
response = requests.post(url, headers=headers, data=data, auth=(user, password))

# Parsing XML
root = ET.fromstring(response.content)

# This code uses Python's base64.b64decode() method to decode the Base64-encoded data.
# The resulting report_bytes variable contains the original binary data.
report_bytes = base64.b64decode(root.xpath('//*[local-name()="reportBytes"]')[0].text)
report_bytes = report_bytes.decode('utf-8')

# Removing the first line <?xml version="1.0" encoding="UTF-8"?>
report_bytes = re.sub(r'^<\?xml.*\?>', '', report_bytes)

# Parsing XML (again)
root = ET.fromstring(report_bytes)

# extract column names
columns = [child.tag for child in root.find('G_1').iter()]

# create an empty data frame
df = pd.DataFrame(columns=columns)

#### populate the data frame #####
# create an empty list to store the data frames
df_list = []
for g in root.findall('G_1'):
row = {}
for child in g.iter():
row[child.tag] = child.text
df_list.append(pd.DataFrame(row, index=[0]))

# concatenate the data frames into one
df = pd.concat(df_list, ignore_index=True)
df = df.drop('G_1', axis=1)
print(df)