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

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.

Articles Related

How to

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 ?

Create a program

The program below do :

first an connexion
second send a Obiee Logical Sql that you can retrieve in any advanced tab of an answer. In our example, we use the Sql of the answer to densify the data in this article : OBIEE - Densification with the fact-based fragmentation capabilities
third retrieve and print the XML file.

We use for this two web services :

the authentication service
the Xml View Service

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();

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 :