Skip to Content
Introduction: In this Weblog I am going to present a DynPage(PAR) that extracts data from BW (using MDX Statements) and displays it in a HTMLB table. I would recommend any developer to take the code from the weblog and create a portal service.This would let any component to call the service and get the data from BW. I am currently using this solution for NASA using a portal service. Jar Files Required: connectionapi.jar,com.sap.portal.ivs.connectorserviceapi.jar,ConnectorHelper.jar,GenericConnector.jar,connector.jar BAPIS Used: There are several BAPIs being called in sequence to execute MDX statement in BW. The BAPIs are following. * BAPI_MDDATASET_CREATE_OBJECT * * BAPI_MDDATASET_SELECT_DATA * * BAPI_MDDATASET_GET_STREAMINFO * * BAPI_MDDATASET_GET_AXIS_INFO * * BAPI_MDDATASET_GET_AXIS_DATA * * BAPI_MDDATASET_GET_CELL_DATA * * BAPI_MDDATASET_DELETE_OBJECT * Step1: Construct and Test your MDX select statement in BW using transaction MDXTEST. If you are going to write MDX statement against a query then remember to check the  technical attribute “Release for OLE DB for OLAP” in “Query Designer”.   Screen Print (Release for OLE DB for OLAP)  image   Screen Print (MDXTEST Transaction) image Step2: Create a HTMLB Dynpage PAR project in NWDS(Netweaver Developer Studio) or Eclipse using the wizard. This should create a template similar to following lines of code. public class display extends PageProcessorComponent { public DynPage getPage() { return new displayDynPage(); } public static class displayDynPage extends DynPage { /** 1. Initialization code executed once per user. */ public void doInitialization() { } /** 1. Input handling code. In general called the first time with the second page request from the user. */ public void doProcessAfterInput() throws PageException { } /** 1. Create output. Called once per request. */ public void doProcessBeforeOutput() throws PageException { Form myForm = this.getForm(); // get the form from DynPage // create your GUI here…. } } public void doProcessBeforeOutput() throws PageException { // get the form from DynPage Form myForm = this.getForm(); IPortalComponentRequest request = (IPortalComponentRequest) this.getRequest(); IPortalComponentResponse response = (IPortalComponentResponse) this.getResponse(); IPortalComponentContext ctxt = request.getComponentContext(); String BWSystem = ctxt.getProfile().getProperty(“System Alias”); //Build your mdx statement in a ArrayList List mdxstatement = new ArrayList(); mdxstatement.add(“SELECT”); mdxstatement.add(“[Measures].MEMBERS ON AXIS(0) ,”); mdxstatement.add(“NON EMPTY (0BPARTNER).MEMBERS ON AXIS(1)”); mdxstatement.add(“FROM (Z_PH_IMS/IDES_CRM_PH_IMS_0003)”); try { Vector data = getQueryData(mdxstatement, request, BWSystem, “X”); // create a table view model DefaultTableViewModel model = new DefaultTableViewModel( (Vector) data.get(1), (Vector) data.get(0)); //create a htlmb table view TableView tv = new TableView(“query”); //add the model to table view tv.setModel(model); tv.setSelectionMode(TableSelectionMode.NONE); tv.setVisibleRowCount(100); tv.setVisibleFirstRow(1); tv.setHeaderVisible(true); tv.setDesign(TableViewDesign.ALTERNATING); //add table view component to form form.addComponent(tv); } catch (Exception e) { response.write(e.getMessage()); } } Step5: Add the following Stubs to your component public Vector getQueryData( List mdxstatement, IPortalComponentRequest request, String system_alias, String formatted) throws Exception { return ExecuteMDXQuery( getConnection(request, system_alias), mdxstatement, formatted); } private IConnection getConnection( IPortalComponentRequest request, String alias) throws Exception { IConnectorGatewayService cgService = (IConnectorGatewayService) request.getService( IConnectorService.KEY); ConnectionProperties prop = new ConnectionProperties( request.getLocale(), request.getUser()); return cgService.getConnection(alias, prop); } private Vector ExecuteMDXQuery( IConnection client, List mdxstatement, String formatted) throws Exception { /* Start Interaction * */ IInteraction interaction = client.createInteractionEx(); // call the BAPI_MDDATASET_CREATE_OBJECT IInteractionSpec interactionSpec = interaction.getInteractionSpec(); interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_CREATE_OBJECT”); RecordFactory recordFactory = interaction.getRecordFactory(); MappedRecord importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); // Create IFunction instance IFunctionsMetaData functionsMetaData = client.getFunctionsMetaData(); IFunction function = functionsMetaData.getFunction(“BAPI_MDDATASET_CREATE_OBJECT”); IStructureFactory structureFactory = interaction.retrieveStructureFactory(); IRecordSet table = (IRecordSet) structureFactory.getStructure( function.getParameter(“COMMAND_TEXT”).getStructure()); Iterator i = mdxstatement.iterator(); while (i.hasNext()) { table.insertRow(); table.setString(“LINE”, (String) i.next()); } importParams.put(“COMMAND_TEXT”, table); MappedRecord exportParams = (MappedRecord) interaction.execute( interactionSpec, importParams); IRecord exportStructure = null; String datasetid = (String) exportParams.get(“DATASETID”); //System.out.println(“Datasetid: ” + datasetid); // call the BAPI_MDDATASET_SELECT_DATA interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_SELECT_DATA”); importParams = null; importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); importParams.put(“DATASETID”, datasetid); function = functionsMetaData.getFunction(“BAPI_MDDATASET_SELECT_DATA”); exportParams = null; exportParams = (MappedRecord) interaction.execute( interactionSpec, importParams); exportStructure = (IRecord) exportParams.get(“RETURN”); if (exportStructure.getString(“TYPE”).toUpperCase().equals(“A”) || exportStructure.getString(“TYPE”).toUpperCase().equals(“E”)) throw new Exception(exportStructure.getString(“MESSAGE”)); //BAPI_MDDATASET_GET_STREAMINFO for header interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_GET_STREAMINFO”); importParams = null; importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); importParams.put(“DATASETID”, datasetid); exportParams = null; exportParams = (MappedRecord) interaction.execute( interactionSpec, importParams); exportStructure = null; exportStructure = (IRecord) exportParams.get(“RETURN”); if (exportStructure.getString(“TYPE”).toUpperCase().equals(“A”) || exportStructure.getString(“TYPE”).toUpperCase().equals(“E”)) throw new Exception(exportStructure.getString(“MESSAGE”)); IRecordSet header = (IRecordSet) exportParams.get(“GROUPS”); //BAPI_MDDATASET_GET_AXIS_INFO ( to get the number of columns and rows ) interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_GET_AXIS_INFO”); importParams = null; importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); importParams.put(“DATASETID”, datasetid); function = functionsMetaData.getFunction(“BAPI_MDDATASET_GET_AXIS_INFO”); exportParams = null; exportParams = (MappedRecord) interaction.execute( interactionSpec, importParams); exportStructure = null; exportStructure = (IRecord) exportParams.get(“RETURN”); if (exportStructure.getString(“TYPE”).toUpperCase().equals(“A”) || exportStructure.getString(“TYPE”).toUpperCase().equals(“E”)) throw new Exception(exportStructure.getString(“MESSAGE”)); IRecordSet axis_info = (IRecordSet) exportParams.get(“AXIS_INFO”); IRecordSet axis_dimensions = (IRecordSet) exportParams.get(“AXIS_DIMENSIONS”); //BAPI_MDDATASET_GET_AXIS_DATA get the axis data for axis 1 interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_GET_AXIS_DATA”); importParams = null; importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); importParams.put(“DATASETID”, datasetid); importParams.put(“AXIS”, “001”); function = functionsMetaData.getFunction(“BAPI_MDDATASET_GET_AXIS_DATA”); exportParams = null; exportParams = (MappedRecord) interaction.execute( interactionSpec, importParams); exportStructure = null; exportStructure = (IRecord) exportParams.get(“RETURN”); if (exportStructure.getString(“TYPE”).toUpperCase().equals(“A”) || exportStructure.getString(“TYPE”).toUpperCase().equals(“E”)) throw new Exception(exportStructure.getString(“MESSAGE”)); IRecordSet mndtry_prptys = (IRecordSet) exportParams.get(“MNDTRY_PRPTYS”); //BAPI_MDDATASET_GET_CELL_DATA get all the data interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_GET_CELL_DATA”); importParams = null; importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); importParams.put(“DATASETID”, datasetid); importParams.put(“START_CELL”, “000”); importParams.put(“END_CELL”, “999999”); function = functionsMetaData.getFunction(“BAPI_MDDATASET_GET_CELL_DATA”); exportParams = null; exportParams = (MappedRecord) interaction.execute( interactionSpec, importParams); exportStructure = null; exportStructure = (IRecord) exportParams.get(“RETURN”); if (exportStructure.getString(“TYPE”).toUpperCase().equals(“A”) || exportStructure.getString(“TYPE”).toUpperCase().equals(“E”)) throw new Exception(exportStructure.getString(“MESSAGE”)); IRecordSet cell_data = (IRecordSet) exportParams.get(“CELL_DATA”); //BAPI_MDDATASET_DELETE_OBJECT interactionSpec.setPropertyValue( “Name”, “BAPI_MDDATASET_DELETE_OBJECT”); importParams = null; importParams = recordFactory.createMappedRecord(“CONTAINER_OF_IMPORT_PARAMS”); importParams.put(“DATASETID”, datasetid); function = functionsMetaData.getFunction(“BAPI_MDDATASET_DELETE_OBJECT”); interaction.execute(interactionSpec, importParams); Vector rtrnVec = MergeData( header, cell_data, axis_info, axis_dimensions, mndtry_prptys, formatted); client.close(); return rtrnVec; } public Vector MergeData( IRecordSet header, IRecordSet cell_data, IRecordSet axis_info, IRecordSet axis_dimensions, IRecordSet mndtry_prptys, String formatted) throws Exception { Vector colName = null; Vector rows = null; Vector retrnVector = new Vector(); //fill the vector for column names header.beforeFirst(); // Moves the cursor before the first row. colName = new Vector(); while (header.next()) { colName.addElement(header.getString(“DSCRPTN”).trim()); } int num_rows = 0; int num_columns = 0; axis_info.beforeFirst(); while (axis_info.next()) { if (axis_info.getString(“AXIS”).equals(“000”)) num_columns = axis_info.getInt(“COORDINATES”); else if (axis_info.getString(“AXIS”).equals(“001”)) num_rows = axis_info.getInt(“COORDINATES”); } if (num_rows == 0) num_rows = 1; if (num_columns == 0) num_columns = 1; rows = new Vector(); Vector dataVec = null; cell_data.beforeFirst(); cell_data.next(); mndtry_prptys.beforeFirst(); for (int i = 1; i <= num_rows; i++) { dataVec = new Vector(); axis_dimensions.beforeFirst(); while (axis_dimensions.next()) { if (axis_dimensions .getString(“AXIS”) .trim() .equals(“001”)) { mndtry_prptys.next(); dataVec.add(mndtry_prptys.getString(“MEM_CAP”).trim()); } } //System.out.println(“Number of Columns: ” + num_columns); for (int j = 1; j <= num_columns; j++) { //System.out.println( //”Cell Data: ” + cell_data.getString(“VALUE”).trim()); if (formatted.trim().equals(“”)) dataVec.add(cell_data.getString(“VALUE”).trim()); else dataVec.add( cell_data.getString(“FORMATTED_VALUE”).trim()); cell_data.next(); } rows.addElement(dataVec); } retrnVector.add(colName); retrnVector.add(rows); return retrnVector; } Step6: Validate your import statements with following import statements import java.util.ArrayList;import java.util.Iterator;import java.util.List;import java.util.Vector;import javax.resource.cci.MappedRecord;import javax.resource.cci.RecordFactory;import com.sapportals.connector.connection.IConnection;import com.sapportals.connector.execution.functions.IInteraction;import com.sapportals.connector.execution.functions.IInteractionSpec;import com.sapportals.connector.execution.structures.IRecord;import com.sapportals.connector.execution.structures.IRecordSet;import com.sapportals.connector.execution.structures.IStructureFactory;import com.sapportals.connector.metadata.functions.IFunction;import com.sapportals.connector.metadata.functions.IFunctionsMetaData;import com.sapportals.htmlb.Form;import com.sapportals.htmlb.enum.TableSelectionMode;import com.sapportals.htmlb.enum.TableViewDesign;import com.sapportals.htmlb.page.DynPage;import com.sapportals.htmlb.page.PageException;import com.sapportals.htmlb.table.DefaultTableViewModel;import com.sapportals.htmlb.table.TableView;import com.sapportals.portal.htmlb.page.PageProcessorComponent;import com.sapportals.portal.ivs.cg.ConnectionProperties;import com.sapportals.portal.ivs.cg.IConnectorGatewayService;import com.sapportals.portal.ivs.cg.IConnectorService;import com.sapportals.portal.prt.component.IPortalComponentContext;import com.sapportals.portal.prt.component.IPortalComponentRequest;import com.sapportals.portal.prt.component.IPortalComponentResponse; Step7: Upload the par and run it Portal.This particular example displays the data from BW IDES against query IDES_CRM_PH_IMS_0003 stored in catalog Z_PH_IMS. Eureka! image
To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. pandu k
    dear prakash,

    Glad to meet you. Good to read the wonderful article from your weblog. Actually i am trying to do same as what you had already posted in this weblog. But rather i am using SQL and u r using MDX, is it possible to extract the BW data via JCA using SQL statement with given BAPIs. plz kindly help me in this context, i am using BI Java SDK from SAP AG with SAP NW Developer Studio 2.0.7. coming to the .jar files which u recommended where can we get them. awaiting for your reply. thanks in advance. i am glad to recieve some responses from bw professionals who read this comment.

    rgds,
    srinu…

    (0) 
    1. Prakash Singh Post author
      Hi Srinu,
           The code posted above does not use BI Java SDK. I wonder why you are trying to execute SQL statement agains BW data? The bapis that i have used cannot execute SQL statement. However, you could write your own RFC(in ABAP) that executes SQL statements and return the data. I don’t know if BI JAVA SDK can execute SQL statement. There is plenty of documentation on BI JAVA SDK. You should look into it. I could send you the libraries that i have used via email. Email me at singhpra@hotmail.com . thank you.
      (0) 
  2. Carol Nighorn
    Thank you for the interesting solution.  Have you tried creating reports with BW Master Data Hierarchies (Profit Center, Cost Center, etc.) with your solution?  In the past, we have had difficulty creating reports which use BW Master Data Hierarchies using MDX.
    (0) 
    1. Prakash Singh Post author
      Hi Carol,
            All of the java reporting or extract that i have written is against transactional data. I will try to create some against master data heirarchies and see if i run into any problem. I will keep you posted. Thank you for your feedback.
           regards
                  prakash.
      (0) 
    1. Prakash Singh Post author
      MDX is the only way to get data out of BW from external application. BEX doesn’t satisy every reporting need of customer and that’s why you will find lot of companies have bought softwares like Cognos & Crystal Reports which also use MDX to grab data from BW. In my case we wanted to generated custom reports that didn’t look like BW report.
      (0) 

Leave a Reply