SAP BW Data Access Builder
In this topic ...
Related Topics ...
How to use the SAP View and Form Builder
Use this Builder to access to a SAP Business Warehouse using the XMLA protocol. XMLA is a specification of an XML-based query syntax that runs over SOAP, suitable for execution against multi-dimensional database/analysis engines. A feature of XMLA is that the query issued is a "MDX expression". SAP provides a "MDX Test" utility that is very helpful for creating these query expressions.
(For more information on XMLA see this XMLA FAQ ).
This Builder is based on and replaces the XMLA Execute Builder. The major advantages of this Builder over the earlier version are:
- Easier access to data
- Simple sample models illustrating how to produce tables with either Dynamic Table or Data Page Builders
- Connection pooling to control the number of simultaneous queries being issued (see Connection Pooling)
How this Builder Works
This Builder adds an LJO to your model that provides access to a SAP Business Warehouse using the XMLA protocol. The Builder allows you to specify the URL to the SAP BW SOAP endpoint and the MDX Command expression. The Builder then adds the SAPBWDataHelper LJO to the model and puts the command expression into a Variable for use at runtime.
In your model we can then invoke the executeMdx method on the LJO and it will execute the query and return the result.
The result returned is the whole XMLA response which although complete can be difficult to interpret. The SAPBWDataHelper LJO provides additional methods that can be invoked to return the particularly useful data, such as the data necessary to build a table, the row/column label names, unique row identifiers, chart data (suitable for use in the WebChart Builder), etc. The sample models illustrate use of these methods, and they use the "Dummy Response" feature so we can tun them without needing to access a SAP Business Warehouse system.
The SAP Extension package features a number of sample models that illustrate the use of this Builder. Since the protocol runs on SOAP, no additional jar files are required.
Specifying Inputs
The HTML Builder takes the inputs described in the table below. For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor."
Input Name Description Name Enter a name for this Builder call. The Designer displays this name in the Builder Call List. Request Info SAP BW URL Enter the URL to the SAP BW SOAP endpoint. This URL is typically of the form:
http://host:8081/sap/bw/xml/soap/xmla
Command Enter the Command XML containing the MDX expression. Advanced Timeout Enter a Timeout value in seconds for the SOAP request. The default = 30s
Log Request/Response Enable this check box if you want to display the service request and response in the application server's console as well as log this information in the Factory's log file. Properties XMLA Properties definitions. These can typically be left with their default value.
Dummy Response If you wish to test your model with data captured from an earlier request we can provide that data here. Instead of issuing an actual SOAP request the Builder will return the data you provide here from the SOAP Response.
Basic Auth Userid If the SOAP service requires basic user authentication, enter the user name to use for the request here. Basic Auth Password If the SOAP service requires basic user authentication, enter the password for the specified user here.
Objects Created by this Builder
The SAP BW Data Builder creates Variables for the Command and Properties data (if they were provided "hard-coded" instead of with a runtime indirect reference) and a "xmlaResponse" Variable which holds the complete XMLA response after the executeMdx method has returned. Each Variable name is prefixed with the name given to the Builder.
The SAPBWDataHelper LJO provides the following methods (see sample models for their usage). Note that the executeMdx method needs to be called before any of the other methods are callable:
Name Arguments Action executeMdx none Executes the MDX expression and returns the complete XMLA response. getTableData
- "noData" value - the String that is used in response cells that contain no data
- ValueCellName - either "FmtValue" or "Value" depending on whether you wish the formatted data value or the raw data value from the data cell
Parses the response and returns only the table cell data and row labels in a form ready to be used by Dynamic Table or Data Page Builders. It is possible to call this method and massage the data with custom information or additional data before passing it through to the Table Builder. The Dynamic Table Builder operates solely on the runtime info and can be very flexible in those situations.
getChartData
- flipAxes "true" or "false" to switch around the row/column axes
- removeFirstRow "true" or "false
- removeFirstColumn "true" or "false"
- removeLastRow "true" or "false"
- removeLastColumn "true" or "false
Parses the response and returns the table cell data formatted into a form ready to be used by the WebChart Builder. Setting "flipAxes" true can be useful to get the alternative view on the data. The removeXXX args are useful if, for example, the first row of data is summary/total data and thus dwarfs the other row values. Removing it can give a more useful chart.
It is possible to call this method and massage the data before passing it into a chart tool
getColLabelText columnNumber For any given column number (zero-based, corresponding to the table data) returns the col label from the Tuples/Tuple[i]/Member/Caption value getColLabels none Returns all column labels getColumnCount none Returns the column count getRowCount none Returns the row count getRowLabelText rowNumber For any given row number (zero-based, corresponding to the table data) returns the row label from the Tuples/Tuple[i]/Member/Caption and Member[1]/Caption values. If there is a Member[1] Caption the result is returned in the form a-b where a is the Member/Caption and b is the Member[1]/Caption.
getRowLabels none Returns all of the row labels. getRowLabelUName loopCounterVarName,PagingAssistantName For any given row number in the Dynamic Table rendering, returns the uname for that row. From the getLabels() result the Tuples/Tuple[i]/Member/UName and Member[1]/UName values are determined. If there is a Member[1] UName the result is returned in the form a__b where a is the Member/UName and b is the Member[1]/UName. Pass in the name of the DynamicTable LoopCounter variable (eg "dtLoopCounter"), and the name of the Paging Assistant (if any) used with the Dynamic Table. If there is no paging assistant being used, you may enter any name and it will be ignored. These values are used to calculate the current row number.
get1BasedRowLabelUName RowCounterVarName,PagingAssistantName For any given row number in the Data Page rendering, returns the uname for that row. From the getLabels() result the Tuples/Tuple[i]/Member/UName and Member[1]/UName values are determined. If there is a Member[1] UName the result is returned in the form a__b where a is the Member/UName and b is the Member[1]/UName. Data Page counters are 1-based, not zero-based like most other things .Pass in the name of the DataPage LoopCounter variable (eg "RowCounterVar"), and the name of the Paging Assistant (if any). If there is no paging assistant being used, you may enter any name and it will be ignored. These values are used to calculate the current row number.
getSAPBWAccess none Returns a SAPBWAccess object that can be used to more directly interact with SAP BW. You must call the "release" method on this object when you have finished with it. See example below.
Connection Pooling
There s not actually a pool of open connections used by this Builder since the XMLA/SOAP protocol is connection-less. However, this Builder uses the Apache Commons pooling mechanism to control access to a pool of tokens. Once a series of designated requests is in progress, further calls will be blocked until one completes.
The following properties control connection pooling behavior. These properties should be set in the override.properties file located in the project WEB-INF/config directory.
Property Default Value bowstreet.sapbw.session.pool.enabled false bowstreet.sapbw.session.pool.maxActiveSessions Commons DEFAULT_MAX_ACTIVE bowstreet.sapbw.session.pool.maxIdleSessions Commons DEFAULT_MAX_ACTIVE bowstreet.sapbw.session.pool.maxWait Commons DEFAULT_MAX_WAIT bowstreet.sapbw.session.pool.testOnBorrow false bowstreet.sapbw.session.pool.testOnReturn false bowstreet.sapbw.session.pool.maxIdleTimeSeconds 1800 bowstreet.sapbw.session.pool.idleSessionSweepSeconds 30 bowstreet.sapbw.session.pool.maxAgeMinutes 60
SAPBWAccess
It s not generally necessary to use this object, but it allows Java-level access to the SAP BW. Calling getSAPBWAccess() on the SAPBWDataHelper will use the connection pooling, unlike this example which uses the object directly, not from a pool:
public static void main(String[] args)
{
SAPBWAccess sa = null;
try
{
IXml props = XmlUtil.parseXml("<Properties>" +
"<PropertyList>" +
"<DataSourceInfo>default </DataSourceInfo>" +
"<Format>Multidimensional </Format>" +
"<AxisFormat>TupleFormat </AxisFormat>" +
"<Content>Data </Content>" +
"</PropertyList></Properties>");
IXml cmd = XmlUtil.parseXml("<Command>" +
"<Statement> SELECT [Measures].MEMBERS ON AXIS(0) , NON EMPTY [0PLANT].MEMBERS * [0MAT_PLANT__DUSG].MEMBERS ON AXIS(1) FROM [DUMINVVAL/DUMINVVAL_DU_MDX_Q001_IMELT] </Statement>" +
"</Command>");
sa = new SAPBWAccess("http://localhost:8082/sapbw");
sa.setCommand(cmd);
sa.setProperties(props);
// sa.setStubResult(XmlUtil.parseXml("<Result/>"));
sa.setLogging("true");
sa.setTimeout("25");
IXml x = sa.executeMdx();
System.out.println(x);
}
catch (Exception e)
{
System.err.println("Error:" + e.toString());
e.printStackTrace();
}
finally
{
if (sa != null)
sa.release();
sa = null;
}
}