/* * Helpers.java * * Copyright 2004-2006 by SAP AG. All Rights Reserved. * SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, and other SAP * products and services mentioned herein as well as their respective logos * are trademarks or registered trademarks of SAP AG in Germany and in several * other countries all over the world. All other product and service names * mentioned are the trademarks of their respective companies. Data contained * in this document serves informational purposes only. National product * specifications may vary. * * These materials are subject to change without notice. These materials are * provided by SAP AG and its affiliated companies ("SAP Group") for * informational purposes only, without representation or warranty of any kind, * and SAP Group shall not be liable for errors or omissions with respect to * the materials. The only warranties for SAP Group products and services are * those that are set forth in the express warranty statements accompanying * such products and services, if any. Nothing herein should be construed as * constituting an additional warranty. */ package com.sap.ip.bi.sdk.samples; import java.io.PrintWriter; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.Enumeration; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Locale; import javax.resource.spi.ManagedConnectionFactory; import org.omg.cwm.analysis.olap.Cube; import org.omg.cwm.analysis.olap.CubeDimensionAssociation; import org.omg.cwm.analysis.olap.Dimension; import org.omg.cwm.objectmodel.core.Attribute; import com.sap.ip.bi.sdk.dac.connector.IBIConnection; import com.sap.ip.bi.sdk.dac.connector.IBIOlap; import com.sap.ip.bi.sdk.dac.connector.olap.odbo.BIOdboMetadataConstants; import com.sap.ip.bi.sdk.dac.olap.query.IBIMemberFactory; import com.sap.ip.bi.sdk.dac.olap.query.impl.factory.BIQueryFactory; import com.sap.ip.bi.sdk.dac.olap.query.main.IBIQuery; import com.sap.ip.bi.sdk.dac.olap.query.member.IBIMember; import com.sap.ip.bi.sdk.dac.result.IBIDataSet; import com.sap.ip.bi.sdk.dac.result.IBIDimensionCursor; import com.sap.ip.bi.sdk.dac.result.model.BIDataSetTableModel; import com.sap.ip.bi.sdk.dac.result.model.BITableItem; import com.sap.ip.bi.sdk.exception.BIException; import com.sap.ip.bi.sdk.exception.BIOlapQueryException; import com.sap.ip.bi.sdk.exception.BIResourceException; import com.sap.ip.bi.sdk.exception.BISQLException; import com.sap.ip.bi.sdk.localization.sdk.Sdk; import com.sap.ip.bi.sdk.localization.sdk.samples.Samples; import com.sap.ip.bi.sdk.util.impl.BIResourceProperties; import com.sapportals.connector.connection.IConnectionFactory; import com.sapportals.connector.connection.IConnectionSpec; /** * Helpers - * * Provides static helper methods for the BI Java SDK * examples that facilitate connecting to data sources and * rendering result sets. * * @author SAP * @version 3.50 SP2 * @since 3.50 */ public class Helpers { // Creates HTML doctype. private static final String DOCTYPE = "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">"; // Stylesheet for HTML files. private static final String CSS = "<style type=\"text/css\">" + "\n" + "<!--" + "\n" + "h3 {" + " font: 14pt arial, helvetica, sans-serif;" + " font-weight: bold;" + " color: #003366" + "}" + "\n" + "h4 {" + " font: 12pt arial, helvetica, sans-serif;" + " font-weight: bold;" + " color: #003366" + "}" + "\n" + "hr {" + " color: #003366;" + " size: 1pt;" + " align: left;" + "}" + "\n" + "body {" + " font: 10pt arial, helvetica, sans-serif;" + " background-color:#ffffff;" + " margin:20px" + "}" + "\n" + "table {" + " border: 1px solid #909cae;" + " border-collapse: collapse" + "}" + "\n" + "td.headCenter {" + " border: 1px solid #909cae;" + " font: 9pt arial, helvetica, sans-serif;" + " background-color:#c4d9e9;" + " margin:20px;" + " text-align:center" + "}" + "\n" + "td.headLeft {" + " border: 1px solid #909cae;" + " font: 9pt arial, helvetica, sans-serif;" + " background-color:#c4d9e9;" + " margin:20px;" + " text-align:left" + "}" + "\n" + "td.odd {" + " border: 1px solid #909cae;" + " font: 9pt arial, helvetica, sans-serif;" + " background-color:#d4e2ee;" + " margin:20px;" + " text-align:center" + "}" + "\n" + "td.even {" + " border: 1px solid #909cae;" + " font: 9pt arial, helvetica, sans-serif;" + " background-color:#eff6fb;" + " margin:20px;" + " text-align:center" + "}" + "\n" + "td.single {" + " border: 1px solid #909cae;" + " font: 9pt arial, helvetica, sans-serif;" + " background-color:#eff6fb;" + " margin:20px;" + " text-align:left" + "}" + "\n" + ".code {" + " font-family: Courier, mono" + "}" + "\n" + "-->" + "</style>"; // Suppress default constructor for non-instantiability private Helpers() { // This constructor will never be invoked } // Creates connection to an XMLA data source. public static IBIConnection connectToXMLADatasource(PrintWriter out) throws BIResourceException { ManagedConnectionFactory mcf; IConnectionFactory cf = null; IConnectionSpec cs = null; IBIConnection connection = null; try { mcf = (ManagedConnectionFactory) Class.forName( "com.sap.ip.bi.sdk.dac.connector.xmla.XmlaManagedConnectionFactory") .newInstance(); cf = (IConnectionFactory) mcf.createConnectionFactory(); cs = cf.getConnectionSpec(); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1210, e); } BIResourceProperties props = new BIResourceProperties(Helpers.class, ".xmla"); // Collects information about the properties used to connect // to the data source. Enumeration propnames = props.propertyNames(); while (propnames.hasMoreElements()) { String key = (String) propnames.nextElement(); //out.println("<p>key:" + key + " prop:" + props.getProperty(key) + "</p>"); cs.setPropertyValue(key, props.getProperty(key)); } try { // Establishes the connection. connection = (IBIConnection) cf.getConnectionEx(cs); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1100, e); } return connection; } public static SampleMetaDataBW getSampleMetaDataBW(IBIConnection connection) throws Exception { return new SampleMetaDataBW(connection); } // Creates connection to an ODBO data source. public static IBIConnection connectToODBODatasource(PrintWriter out) throws BIResourceException { ManagedConnectionFactory mcf; IConnectionFactory cf = null; IConnectionSpec cs = null; IBIConnection connection = null; try { mcf = (ManagedConnectionFactory) Class.forName( "com.sap.ip.bi.sdk.dac.connector.odbo.OdboManagedConnectionFactory") .newInstance(); cf = (IConnectionFactory) mcf.createConnectionFactory(); cs = cf.getConnectionSpec(); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1230, e); } BIResourceProperties props = new BIResourceProperties(Helpers.class, ".odbo"); // Collects information about the properties used to connect // to the data source. Enumeration propnames = props.propertyNames(); while (propnames.hasMoreElements()) { String key = (String) propnames.nextElement(); cs.setPropertyValue(key, props.getProperty(key)); } try { // Establishes the connection. connection = (IBIConnection) cf.getConnectionEx(cs); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1110, e); } return connection; } // Creates connection to a JDBC data source. public static IBIConnection connectToJDBCDatasource(PrintWriter out) throws BIResourceException { ManagedConnectionFactory mcf; IConnectionFactory cf = null; IConnectionSpec cs = null; IBIConnection connection = null; try { mcf = (ManagedConnectionFactory) Class.forName( "com.sap.ip.bi.sdk.dac.connector.jdbc.JdbcManagedConnectionFactory") .newInstance(); cf = (IConnectionFactory) mcf.createConnectionFactory(); cs = cf.getConnectionSpec(); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1200, e); } BIResourceProperties props = new BIResourceProperties(Helpers.class, ".jdbc"); // Collects information about the properties used to connect // to the data source. Enumeration propnames = props.propertyNames(); while (propnames.hasMoreElements()) { String key = (String) propnames.nextElement(); cs.setPropertyValue(key, props.getProperty(key)); } try { // Establishes the connection. connection = (IBIConnection) cf.getConnectionEx(cs); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1120, e); } return connection; } public static void createJDBCSampleData() throws BIResourceException { try { com.sap.ip.bi.sdk.samples.jdbc.Samples.setup(); } catch (SQLException ex) { throw new BIResourceException(Locale.getDefault(), Sdk.SDK_0000, new Object[] { ex.getMessage()}); } } public static String getJDBCTablePrefix() { return com.sap.ip.bi.sdk.samples.jdbc.Samples.getJDBCTablePrefix(); } // Creates connection to a SAP Query data source. public static IBIConnection connectToSAPQDatasource(PrintWriter out) throws BIResourceException { ManagedConnectionFactory mcf; IConnectionFactory cf = null; IConnectionSpec cs = null; IBIConnection connection = null; try { mcf = (ManagedConnectionFactory) Class .forName("com.sap.ip.bi.sdk.dac.connector.sapq.SapqManagedConnectionFactory") .newInstance(); cf = (IConnectionFactory) mcf.createConnectionFactory(); cs = cf.getConnectionSpec(); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1220, e); } BIResourceProperties props = new BIResourceProperties(Helpers.class, ".sapq"); // Collects information about the properties used to connect // to the data source. Enumeration propnames = props.propertyNames(); while (propnames.hasMoreElements()) { String key = (String) propnames.nextElement(); cs.setPropertyValue(key, props.getProperty(key)); } try { // Establishes the connection. connection = (IBIConnection) cf.getConnectionEx(cs); } catch (Exception e) { throw new BIResourceException(Locale.getDefault(), Samples.SDK_SAMPLES_1130, e); } return connection; } public static String getDocTypeDefinition() { return DOCTYPE; } public static String getStyleSheetDefinition() { return CSS; } public static void renderQueryAndDataset( PrintWriter out, IBIQuery query) { out.println( "<p><b>MDX Statement that was executed:</b><br> " + "<span class=\"code\">" + (String) query.getNativeRepresentation() + "</span>" + "</p>"); IBIDataSet dataset = query.execute(); renderDataset(out, dataset, true); } // Facilitates display of any data set as an HTML table using // BIDataSetTableModel, supporting crossjoins and dimension // properties on columns and rows, and hierarchical // display. public static void renderDataset( PrintWriter out, IBIDataSet dataset, boolean isDisplayingHierarchy) { try { BIDataSetTableModel table = new BIDataSetTableModel(dataset, isDisplayingHierarchy); out.println("<p>Result set:</p>"); out.println( "<table width=700 border=1 cellpadding=0 cellspacing=0>"); int row = table.getRowCount(); int col = table.getColumnCount(); int evenOdd = -1; for (int i = 0; i < row; i++) { if (evenOdd > -1) { evenOdd++; } out.println("<tr>"); for (int j = 0; j < col; j++) { BITableItem item = (BITableItem) table.getValueAt(i, j); String cellText = item.toString(); switch (item.getSection()) { // cells case BIDataSetTableModel.SECTION_CELLS : if (evenOdd == -1) { evenOdd = 0; } out.println(renderTd( cellText, (evenOdd & 1) == 0 ? "even" : "odd")); break; // member on row axis but not member property case BIDataSetTableModel.SECTION_ROW_HEADERS : if (isDisplayingHierarchy && (item.getItem() instanceof IBIDimensionCursor)) { boolean atLeastOneLevel = false; StringBuffer dashes = new StringBuffer(); for (int k = 0; k < item.getRelativeLevel(); k++) { dashes.append("--"); atLeastOneLevel = true; } if (atLeastOneLevel == true) { dashes.append(" "); } cellText = dashes.toString() + cellText; } out.println(renderTd(cellText, "headLeft")); break; // member on column axis but not member // property case BIDataSetTableModel.SECTION_COLUMN_HEADERS : if (isDisplayingHierarchy && (item.getItem() instanceof IBIDimensionCursor)) { // captions for members of a hierarchy // on the columns axis are printed // only for the first occurrence of the // caption in the upper-left corner. // all other cells are cleared // 1999 // Q1 Q2 Q3 Q4 // the condition is that the text is // only displayed if the row of the // output grid equals the level of the // member, otherwise it is suppressed. // as the minimum displayed level may // be different from 0, we have to // adjust for the actual minimum // level; that's why we compare for // the relative level. if (item.getRelativeLevel() != (i - item.getAccumulatedHeaderPositionCount())) { cellText = ""; } } out.println(renderTd(cellText, "headCenter")); break; // captions case BIDataSetTableModel.SECTION_LABELS : out.println(renderTd(cellText, "headCenter")); break; } } out.println("</tr>"); } out.println("</table>"); } catch (BISQLException e) { e.printStackTrace(); out.println("<p>Error: " + e.getMessage() + "</p>"); } } // Facilitates display of a result set as an HTML table. public static void renderResultset( PrintWriter out, ResultSet resultset) { try { ResultSetMetaData rsmd = resultset.getMetaData(); out.println("<p>Result set:</p>"); out.println( "<table width=700 border=1 cellpadding=0 cellspacing=0>"); //Now output Table column header out.println("<tr>"); int cols = rsmd.getColumnCount(); for (int i = 1; i <= cols; i++) { out.println(renderTd(rsmd.getColumnLabel(i), "headCenter")); } out.println("</tr>"); //Now output Table rows int j = -1; while (resultset.next()) { j++; out.println("<tr>"); for (int i = 1; i <= cols; i++) { Object o = resultset.getObject(i); String content = null; if (resultset.wasNull()) { content = "{null}"; } else { content = o.toString(); } out.println(renderTd(content, (j & 1) == 0 ? "even" : "odd")); } out.println("</tr>"); } //end table out.println("</table>"); } catch (Exception ex) { ex.printStackTrace(); out.println("<p>Error: " + ex.getMessage() + "</p>"); } } public static String renderTd(String data, String clazz) { return "<td" + (clazz != null ? " class=\"" + clazz + "\"" : "") + ">" + escape(data) + "</td>"; } public static String escape(String data) { final String[][] map = { { "£", "£" } }; String content = data; if (content == null || (content != null && content.equals(""))) { content = " "; } else { for (int i = 0; i < map.length; i++) { String[] pair = map[i]; if (content.indexOf(pair[0].charAt(0)) >= 0) { int position = content.indexOf(pair[0].charAt(0)); while (position >= 0) { content = new StringBuffer(content) .replace(position, position + 1, pair[1]) .toString(); position = content.indexOf(pair[0].charAt(0)); } } } } return content; } public static IBIMember createMember(IBIMemberFactory memberFactory, Dimension dimension, String memberName, String memberUniqueName) throws BIOlapQueryException { HashMap taggedValues = new HashMap(); taggedValues.put( BIOdboMetadataConstants.MEMBER_NAME, memberName); taggedValues.put( BIOdboMetadataConstants.MEMBER_UNIQUE_NAME, memberUniqueName); IBIMember member = memberFactory. createMember(dimension, memberName, null, taggedValues); return member; } public static class SampleMetaDataBW { public Cube cube; public Dimension soldToPartyDimension; public Dimension salesOrgDimension; public Dimension divisionDimension; public Dimension measuresDimension; public Dimension distributionChannelDimension; public Dimension versionDimension; public Dimension valueTypeDimension; public IBIMember divisionHiTechMember; public IBIMember divisionElectronicParts; public IBIMember divisionServiceMember; public IBIMember measuresQuantityMember; public IBIMember measuresCostMember; public IBIMember measuresIncomingOrderValue; public IBIMember distributionChannelAll; public IBIMember distributionChannelDirectSales; public IBIMember valueTypeActualMember; public IBIMember versionCurrentMember; public Attribute countryAttribute; public Attribute industryAttribute; public Cube cube2; public Dimension salesOrgDimension2; public IBIMember salesOrgNewYork2; public IBIMember salesOrgSanFrancisco2; private SampleMetaDataBW(IBIConnection connection) throws Exception { IBIOlap olap = connection.getOlap(); cube = olap.getObjectFinder().findCubeFirst( (String) null, "$0D_SD_C03"); cube2 = olap.getObjectFinder().findCubeFirst( (String) null, "0D_SD_C03/0D_SD_C03_Q009"); // This is just a sanity check to verify that the // cube on which this example relies was retrieved if (cube == null){ throw new BIException(Locale.getDefault(), Samples.SDK_SAMPLES_1000, new Object[] {"$0D_SD_C03"}); } else if (cube2 == null) { throw new BIException(Locale.getDefault(), Samples.SDK_SAMPLES_1000, new Object[] {"0D_SD_C03/0D_SD_C03_Q009"}); } // initialize metadata for cube $0D_SD_C03 for (Iterator i = cube.getCubeDimensionAssociation().iterator(); i.hasNext(); ) { CubeDimensionAssociation cda = (CubeDimensionAssociation) i.next(); Dimension dim = cda.getDimension(); if (dim.getName().equalsIgnoreCase("0D_DIV")) { this.divisionDimension = dim; } else if (dim.getName().equalsIgnoreCase("0D_SALE_ORG")) { this.salesOrgDimension = dim; } else if (dim.getName().equalsIgnoreCase("0D_SOLD_TO")) { this.soldToPartyDimension = dim; } else if (dim.getName().equalsIgnoreCase("0D_DIS_CHAN")) { this.distributionChannelDimension = dim; } else if (dim.getName().equalsIgnoreCase("Measures")) { this.measuresDimension = dim; } else if (dim.getName().equalsIgnoreCase("0D_VTYPE")) { this.valueTypeDimension = dim; } else if (dim.getName().equalsIgnoreCase("0D_VERSION")) { this.versionDimension = dim; } } // to filter the data by a specific division, // we create a member BIQueryFactory queryFactory = (BIQueryFactory) olap.getQueryFactory(); divisionHiTechMember = createMember(queryFactory, divisionDimension, "High Tech", "[0D_DIV].[7]"); divisionElectronicParts = createMember(queryFactory, divisionDimension, "Electronic Parts", "[0D_DIV].[15]"); divisionServiceMember = createMember(queryFactory, divisionDimension, "Service", "[0D_DIV].[8]"); valueTypeActualMember = createMember(queryFactory, valueTypeDimension, "Actual", "[0D_VTYPE].[010]"); versionCurrentMember = createMember(queryFactory, versionDimension, "Current Version", "[0D_VERSION].[000]"); distributionChannelAll = createMember(queryFactory, distributionChannelDimension, "All Distribution channel", "[0D_DIS_CHAN].[All]"); distributionChannelDirectSales = createMember(queryFactory, distributionChannelDimension, "Direct Sales", "[0D_DIS_CHAN].[1]"); measuresQuantityMember = createMember(queryFactory, measuresDimension, "Quantity in base untis (SAP DEMO)", "[Measures].[0D_QUANT_B]"); measuresCostMember = createMember(queryFactory, measuresDimension, "Cost in statistics currency (SAP DEMO)", "[Measures].[0D_COSTVALS]"); // retrieve dimension attributes List plantFeatures = this.salesOrgDimension.getFeature(); Attribute attribute; for (int j = 0; j < plantFeatures.size(); j++) { attribute = (Attribute) plantFeatures.get(j); if (attribute.getName().equalsIgnoreCase("[10D_COUNTRY]")) { countryAttribute = attribute; } } // initialize metadata for cube 0D_SD_C03/0D_SD_C03_Q009 for (Iterator i = cube.getCubeDimensionAssociation().iterator(); i.hasNext(); ) { CubeDimensionAssociation cubeDimensionAssociation = (CubeDimensionAssociation) i.next(); Dimension dimension = cubeDimensionAssociation.getDimension(); if (dimension.getName().equalsIgnoreCase("0D_SALE_ORG")) { this.salesOrgDimension2 = dimension; } } salesOrgNewYork2 = createMember(queryFactory, salesOrgDimension2, "New York", "[0D_SALE_ORG].[1612]"); salesOrgSanFrancisco2 = createMember(queryFactory, salesOrgDimension2, "San Francisco", "[0D_SALE_ORG].[1614]"); } } }