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