OTBI Analysis result via SOAP request/response with proper column names

marcelhoffs · Apr 17, 2025 10:00AM

Hi,

I am using OTBI and I have created an Analysis. This works great.
Now I want fetch the result via a SOAP call. This also works fine. I use the XmlViewService and the executeXMLQuery operation and I get the result I expect. So far so good. This is the result I am getting.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="urn://oracle.bi.webservices/v6">

   <soap:Body>

      <sawsoap:executeXMLQueryResult>

         <sawsoap:return xsi:type="sawsoap:QueryResults">

            <sawsoap:rowset xsi:type="xsd:string"><![CDATA[<rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">

	<Row>

		<Column0>100004</Column0>

		<Column1>John</Column1>

		<Column2>Doe</Column2>

	</Row>

</rowset>]]></sawsoap:rowset>

            <sawsoap:queryID xsi:type="xsd:string">RSXS10_1</sawsoap:queryID>

            <sawsoap:finished xsi:type="xsd:boolean">true</sawsoap:finished>

         </sawsoap:return>

      </sawsoap:executeXMLQueryResult>

   </soap:Body>

</soap:Envelope>

The first column is the Person Number, the next is the First Name and the last column is the Last Name.

Now what bugs me is that the columns are named Column0, Column1 and Column2.
Is there a way to have this service output proper column names?

Riyaz Ali-Oracle · Apr 24, 2025 5:47AM

You can try the following:

Use Alias Names in OTBI Analysis
Before running the SOAP request, modify your OTBI analysis and explicitly set column aliases for each field.

OTBI sometimes retains these aliases when exporting via SOAP.

marcelhoffs · Apr 24, 2025 7:18AM

And where do I set that alias? I see on column properties custom headings, though changing that does not affect the SOAP output.

Is there another place where I can set an alias?

Riyaz Ali-Oracle · Apr 24, 2025 8:12AM

Steps to Set Column Aliases:

Navigate to the Criteria Tab

Open your OTBI Analysis and go to the Criteria section.

Modify the Column Formula

Click on the fx (Formula) button for the column you want to rename.

Use the AS keyword to define an alias.

Example:

sql
"Person"."Person Number" AS "Person_Number"
Save and Run the Report

Ensure the alias names appear correctly before executing the SOAP request.

Alternative Approaches
If SOAP still returns generic column names, you may need to adjust the BI Publisher Data Model, explicitly defining alias names in the XML template.

Another workaround is mapping the column names within your application after retrieving the SOAP response.

marcelhoffs · Apr 24, 2025 9:43AM

The aliasses approach does not work for me. So I changed the formula for one of the columns to this:

"Worker"."Person Number" AS "Person_Number"

It seems to already apply an alias to the column, namely saw_0.
Now with this modification you essentially generate an invalid SQL and I can't run the report anymore.

Error getting drill information: SELECT "Worker"."Person Number" AS "Person_Number" saw_0, "Worker"."Employee First Name" saw_1, "Worker"."Employee Last Name" saw_2 FROM "Workforce Management - Person Real Time" WHERE "Worker"."Person Number" IN ('P_PERSON_NUMBER')

Riyaz Ali-Oracle · Apr 24, 2025 12:42PM

Try the below:

Remove Explicit Aliases from SQL Formula Since OTBI applies its own aliases, avoid using "Worker"."Person Number" AS "Person_Number"—instead, just use:

sql
"Worker"."Person Number"

Then, rely on post-processing in your application to rename the columns.

2. Use BI Publisher Data Model Instead

a) If you need explicit column names in the SOAP response, consider creating a BI Publisher Data Model rather than relying on OTBI’s default behavior.
b) BI Publisher allows full control over XML output column names.

3. Modify XML SOAP Request (If Possible)

xml

<sawsoap:param name="columnAliases" value="Person_Number,First_Name,Last_Name"/>
</sawsoap:reportParams>
This depends on whether OTBI respects such parameters.

Post-Processing in Your Application If OTBI forces column names like saw_0, saw_1, etc., one workaround is handling renaming in your application code after retrieving the SOAP response.

Note: Pleae try below if the above steps does not work:

Try removing explicit aliases first and rerunning the query. If SOAP still returns generic column names, we can explore the BI Publisher route or XML modifications.

Oracle Transactional Business Intelligence

Categories

OTBI Analysis result via SOAP request/response with proper column names

Answers