Skip to Content
0

Universe used in a Webi Query using SDK

Oct 18, 2016 at 01:43 PM

133

avatar image
Former Member

HI Team,

I have a code which is used to retrieve the SQL query for webi report in BI 4.1 using JAVA SDK.

IDocumentInstance doc = dimService.openDocument(context, iObject.getID()); System.out.println("Report Name : " + iObject.getTitle());

Workspace workspace = diService.getWorkspace(context, doc);

List<DataProvider> listDataProvider = wsService.getDataProviders(workspace);

for (DataProvider provider : listDataProvider) {

try {

System.out.println("Query :" + query_sequence);

System.out.println("Query Name :" + provider.getName());

queryName = provider.getName();

QueryExecutionPlan plan = dpService.getQueryExecutionPlan(provider, true); print(plan.getQueryExecPlanTree());

}

catch(Exception e) {

e.printStackTrace();

}

}

private static void print(QueryExecutionPlanNode planNode) {

try {

if (planNode instanceof NativeQueryNode) {

NativeQueryNode queryNode = (NativeQueryNode)planNode; System.out.println(queryNode.getNativeQueryString());

}

else if (

planNode instanceof OperatorNode) {

OperatorNode operatorNode = (OperatorNode)planNode;

for (QueryExecutionPlanNode node : operatorNode.getChildren()) {

print(node);

}

}

}

catch(Exception e) {

e.printStackTrace();

}

}

This code was provided by SAP to me. I am able to retrieve the query from this code. However, I am not able to retrieve to which universe this query connects.

For example: In a webi report: if we have 12 queries which are based on 4 universes, can we get which query connects to which universe using Java SDK.

Please suggest.

Regards,

Mitesh Joshi

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Prithviraj Shekhawat Oct 19, 2016 at 09:57 AM
0

You can find the details from si_webi_doc_properties. Query for your report properties in query builder and look for this property. You will have the details in there. You can use the logic in the code at below blog to extract the details.

https://blogs.sap.com/2016/05/17/how-to-get-data-source-details-from-webi-document-properties/

Thanks,

Prithvi

Share
10 |10000 characters needed characters left characters exceeded
Prithviraj Shekhawat Oct 19, 2016 at 09:57 AM
0

Additionally you can also use the below code.

<html>
<head>
<style>


	
	.table_header td {
		color: white;
		background-color: black;
		    font-size: 10pt;
	}
			
			.table_header1 td {
		    font-size: 10pt;
		    
	}
</style>
</head>
<body> 
<%@ page import="com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
com.crystaldecisions.sdk.properties.*,
com.crystaldecisions.sdk.plugin.desktop.folder.*,
com.businessobjects.sdk.plugin.desktop.webi.*,
java.io.*"%>


<%
	// These imports are for the XML DOM Parser
%>
<%@ page import="javax.xml.parsers.DocumentBuilderFactory"%>
<%@ page import="javax.xml.parsers.DocumentBuilder"%>
<%@ page import="org.w3c.dom.*"%>
<%@ page import="org.w3c.dom.Node.*"%>
<%@ page import="org.w3c.dom.Element"%>
<%@ page import="com.sun.org.apache.xerces.internal.parsers.*"%>
<%@ page import="org.xml.sax.InputSource"%>
<%
	// These imports are for transforming the DOM Parser back into a string
%>
<%@ page import="javax.xml.transform.Transformer"%>
<%@ page import="javax.xml.transform.TransformerFactory"%>
<%@ page import="javax.xml.transform.OutputKeys"%>
<%@ page import="javax.xml.transform.dom.DOMSource"%>
<%@ page import="javax.xml.transform.stream.StreamResult"%>
<%
	// Generic Java Imports
%>
<%@ page import="java.io.*"%>
<%@ page import="java.net.*"%>
<%@ page import="java.util.Enumeration"%>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>




<TABLE id="htmlTable" BORDER="1" cellpadding="0" cellspacing="0"   width="100%"  class="table" align="center">
<tr class="table_header">
<td>CommonConnection</td></tr>
<%
  //Enter Username
  String username = "adminstrator";
  //Enter User password
  String password = "password";
  //Enter CMS Name
  String cmsname = "localhost:6400";
  String authtype = "secEnterprise";
  
  


  IEnterpriseSession oEnterpriseSession=null;
NodeList dpNodes = null;
Node nNode=null;
Element element=null;
  try
  {
	oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authtype);
	IInfoStore oInfoStore = (IInfoStore)oEnterpriseSession.getService("","InfoStore");
 


 //Provide the report ID below for which you want to retrieve the query names and universe names
		String webiDocQuery="select * from ci_infoobjects where si_id=6180000";
		IInfoObjects webiDocObjects= oInfoStore.query(webiDocQuery);
%>
<table BORDER="1" cellpadding="0" cellspacing="0"   width="100%"  class="table" align="center">
<tr class="table_header">
<td>Report ID</td><td>Report Name</td><td>Report Path</td>
</tr>


<%
		for(int a=0;a<webiDocObjects.size() ;a++)
		{
			IInfoObject webiDocObject=(IInfoObject)webiDocObjects.get(a);
						
						out.println("<tr class=\"table_header1\"><td>"+ webiDocObject.getID() + "</td><td>"+ webiDocObject.getTitle() + "</td>");
						
			
					
		IProperties prop = webiDocObject.properties();
		
		 		
				 
		 
  IProperty getProp = prop.getProperty("SI_PARENTID");
  String FolderID = getProp.toString();
  IInfoObjects folder = oInfoStore .query("select si_id,si_name,si_parentid,si_path from ci_infoobjects where si_id=" + FolderID);
  IInfoObject ifolder=(IInfoObject)folder.get(0);    
  if(ifolder.getKind().equals("Folder"))
  {
   IFolder iifolder=(IFolder)ifolder;
   String finalFolderPath="";
   if(iifolder.getPath()!= null)
   {
    String path[]=iifolder.getPath();
    for(int fi=0;fi<path.length;fi++)
    {
     finalFolderPath = path[fi] + "/" + finalFolderPath;
    }
    finalFolderPath = finalFolderPath + iifolder.getTitle();
   }
   else
   {
    finalFolderPath=finalFolderPath+iifolder.getTitle();
   }
   out.println("<td>" + finalFolderPath + "</td>");
  }
  else if((ifolder.getKind().equals("FavoritesFolder")))
  {
   out.println("<td><b>FavoritesFolder</b>  ::  " + ifolder.getTitle() + "</td>");
  }
  else if((ifolder.getKind().equals("Inbox")))
  {
   out.println("<td><b>Inbox</b>  ::  " + ifolder.getTitle() + "</td>");
  }
  else if((ifolder.getKind().equals("ObjectPackage")))
  {
   //out.println("<td><b>ObjectPackage</b>  ::  " + ifolder.getTitle() + "</td>");
   IProperties prop1 = ifolder.properties();
  IProperty getProp1 = prop1.getProperty("SI_PARENTID");
  String FolderID1 = getProp1.toString();
  IInfoObjects folder1 = oInfoStore.query("select * from ci_infoobjects where si_id=" + FolderID1);
  IInfoObject ifolder1=(IInfoObject)folder1.get(0);    
  if(ifolder1.getKind().equals("Folder"))
  {
   IFolder iifolder1=(IFolder)ifolder1;
   String finalFolderPath1="";
   if(iifolder1.getPath()!= null)
   {
    String path[]=iifolder1.getPath();
    for(int j=0;j<path.length;j++)
    {
     finalFolderPath1= path[j] + "/" + finalFolderPath1;
    }
    finalFolderPath1 = finalFolderPath1 + iifolder1.getTitle()+"/"+ifolder.getTitle();
   }
   else
   {
    finalFolderPath1=finalFolderPath1+iifolder1.getTitle()+"/"+ifolder.getTitle();
   }
   out.println("<td>" + finalFolderPath1 + "</td>");
  }
   
  }
  
  if(prop.getProperty("SI_WEBI_DOC_PROPERTIES") != null)
		{			
				String xmlRecords=prop.getProperty("SI_WEBI_DOC_PROPERTIES").getValue().toString();
				


				Document myDoc = convertStringToDom(xmlRecords);
				dpNodes = myDoc.getElementsByTagName("WEBI_DP");
				int count=0;
				out.println("<td>");
%>
<table BORDER="1" cellpadding="0" cellspacing="0"   width="100%"  class="table" align="center">
<tr class="table_header">
<td>DP Name</td><td>DP ID</td><td>DS Name</td>
<tr>
<%
				for (int n = 0; n < dpNodes.getLength(); n++) 
				{
					nNode = dpNodes.item(n);


    if (nNode.getNodeType() == Node.ELEMENT_NODE) {
					element=(Element)nNode;
				
				String nodeDPName = (String) element.getAttribute("DPNAME");
				String nodeDPID = (String) element.getAttribute("DPID");
				String nodeDSName = (String) element.getAttribute("DSNAME");
				
    
					
				  out.println("<tr class=\"table_header1\"><td>"+nodeDPName+"</td>");
				out.println("<td>"+nodeDPID+"</td>");
				out.println("<td>"+nodeDSName+"</td></tr>");
								
				}
								
				}
				out.println("</table></td></tr>");
		}
		else
		{
			out.println("<td>");
%>
<table BORDER="1" cellpadding="0" cellspacing="0"   width="100%"  class="table" align="center">
<tr class="table_header">
<td>DP Name</td><td>DP ID</td><td>DS Name</td>
<tr>
<%
				  out.println("<tr class=\"table_header1\"><td>Not Found</td>");
				out.println("<td>Not Found</td>");
				out.println("<td>Not Found</td></tr>");
				out.println("</table></td></tr>");
		}




		
		}
		out.println("</table></td></tr>");
		
		
	
	


  }
  catch(Exception e)
  {
  out.println(e);
  }
  finally
  {
  oEnterpriseSession.logoff();
 
  }
%>
<%!
public Document convertStringToDom(String domXMLSTring) throws Exception {
		DOMParser parser = new DOMParser();
		parser.parse(new InputSource(new java.io.StringReader(domXMLSTring)));
		return (parser.getDocument());


	}
%> 
</table>
</body>
</html>


Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Prithvi,

Thanks for an update.

I will give a try and will get back to you with my findings.

Much appreciated.

Regards,

Mitesh Joshi

0
avatar image
Former Member
Oct 20, 2016 at 01:38 PM
0

Hello Mitesh,

Just in case you didn't already know, the SDK you're using is not supported (itis an internal one) and can be updated without notice (and so, your code may be breaked).

The official and supported API to use is the Web Intelligence RESTful API.

Best regards,

Anthony

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Anthony,

Thanks for the update. I will give a try and will update my findings. The code which I am using has been provided by SAP when I raised an SAP Ticket. I asked them to give me API Reference for this code, however, still waiting for their reply.

Regards,

Mitesh Joshi

0