OBIEE - How to retrieve a result set with the XMLViewService WebService and the logical SQL ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - About

This article show you how you can easily retrieve information with the help of the XMLViewService OBIEE web services.

We are going to make a simple connexion, then sending an Obiee Logical Sql to retrieve the result in a Xml format.

3 - How to

3.1 - Consume the Web Service

Before you begin to create the Java Procedure, you must consume the Web Service. You can find all the details in this article : Eclipse - How to consume a web service (OBIEE) with WTP ?

3.2 - Create a program

The program below do :

We use for this two web services :

package com.consume;
import java.net.URL;
import com.siebel.analytics.web.soap.v5.*;
 
/**
 * @author gerardnico
 * http://gerardnico.com
 */
public class WebService {
 
	public WebService () {
		main();
	}
 
	/**
	 */
	public void main() {
 
		try {
 
		 SAWSessionServiceLocator awsessionservicelocator = new SAWSessionServiceLocator();
		 XmlViewServiceLocator  xmlViewServiceLocator = new XmlViewServiceLocator();
 
		 SAWSessionServiceSoap m_Session;
 
		 m_Session = awsessionservicelocator.getSAWSessionServiceSoap(
                                        new URL("http://ngerard:9704/analytics/saw.dll?SoapImpl=nQSessionService"));
 
		 XmlViewServiceSoap xmlService = xmlViewServiceLocator.getXmlViewServiceSoap(
                                        new URL("http://ngerard:9704/analytics/saw.dll?SoapImpl=xmlViewService"));
 
		 String m_sessionID = m_Session.logon("Administrator", "Administrator");
 
 
		 String query = "SET VARIABLE DISABLE_CACHE_HIT=1; SELECT Products.\"Prod Name\" saw_0, 
Calendar.\"Calendar Year\" saw_1, Calendar.\"Calendar Week Number\" saw_2, \"Sales Facts\".\"# of Calendar Week Number 
Grand Total\" saw_3 FROM SH WHERE (Calendar.\"Calendar Year\" IN (2000, 2001)) AND (Calendar.\"Calendar Week Number\" 
BETWEEN 20 AND 30) AND (Products.\"Prod Name\" = 'Bounce') ORDER BY saw_1, saw_2";
 
		 QueryResults results = xmlService.executeSQLQuery(query, 
XMLQueryOutputFormat.fromString("SAWRowsetData"), new XMLQueryExecutionOptions(), m_sessionID);
 
		 String xmlResult = results.getRowset();
 
		 System.out.println(xmlResult);
 
		 m_Session.logoff(m_sessionID);
 
 
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
 
 
	}
 
}

You can also use the impersonatex function to behave as an other user.

  AuthResult authresult = impersonateex("user", "password", "other user", new SAWSessionParameters());
  String  m_sessionID = authresult.getSessionID();
Advertising

4 - The Result

When we start this program, the SOAP OBIEE XmlViewService return us an XML file corresponding to the result of the Sql.

<rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" ><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>20</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>21</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>22</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>23</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>24</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>25</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>26</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>27</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>28</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>29</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2000</Column1>
<Column2>30</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>20</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>21</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>22</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>23</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>24</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>25</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>26</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>27</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>28</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>29</Column2><Column3>Yes</Column3></Row><Row><Column0>Bounce</Column0><Column1>2001</Column1>
<Column2>30</Column2><Column3>Yes</Column3></Row></rowset>

The answer result :