XI 3.1 Service Pack 2 - Web Intelligence Calculati...

ted_ueda · ‎08-05-2009

h5. Introduction

The recent release of Service Pack 2 for BusinessObjects Enterprise XI 3.1 introduces a torrent of new features (http://help.sap.com/businessobject/product_guides/boexir31SP2/en/xi31_sp2_whats_new_en.pdf) for Web Intelligence. Input Controls (), BI Services (expose Web Intelligence document components as a Web Service), Query on Query, and Fold/Unfold - the list of UI improvements and new ways to access your information is long.

What's most exciting about SP2 to me and other coders who works with Web Intelligence documents is the introduction of three new SDKs! Listing briefly:

Web Intelligence Custom Data Provider Plugins (http://help.sap.com/businessobject/product_guides/boexir31SP2/en/xi31_sp2_cdp_dev_en.pdf) - create Java classes for your own Web Intelligence Rich Client custom data sources and UI components to configure them.

Web Intelligence Extension Points

- an

Innovation Center

Technical Evaluation preview that allows for customization of the Web Intelligence Interactive Viewer (support for OEM/Partners).I've been working with these new SDKs the past few weeks, and I must say I'm pretty darn impressed - lots of cool things are now possible, allowing for a more compelling Business Intelligence experience for clients.

I intend to blog about each of the new SDKs in the coming weeks - the possibilities are just so grand, it's difficult to narrow down the choices to just a few examples to write about.

This wealth of choices is particularly so for the Calculation Extension Points, that I'll describe in this blog.

Web Intelligence Calculation Extension Points

Web Intelligence comes with a very large set of functions - functions to manipulate/format data and retrieve internal information from the document. There are functions to retrieve the Query metadata, the SQL statements generated by the document and set to the database, the identity of the user viewing the document, the processing time, and a lot more. But at times they just aren't enough - you can never have too many functions!

Those of you familiar with Crystal Reports may have worked with Crystal User Function Libraries (UFLs) to create custom functions using either .NET (CR.NET or CR Designer) or Java (CR for Eclipse) programming frameworks. UFLs are commonly in use in the Crystal world, particularly to utilize OS resources for internationalization/localization, mathematical functions, and information retrieved over a network.

Calculation Extension Points bring the power of custom functions to Web Intelligence. A coder can implement such functions using C++ - and take advantage of any resources accessible to the C++ code.

I'll describe three sample extensions that I'd written the past week.

The first example will describe how to create a function that will write Web Intelligence document information to a database whenever it's invoked. Quite useful when you're interested in obtaining fine-grained usage information.

The second example will sdescribe how to access daily currency exchange rates over the web. If you have a reporting requirement for always current foreign currency valuation, this function would be just the ticket.

The third example will show how to invoke OS resources - in particular a command-line utility for unit conversions. If you can't recall the conversion between Celsius and Farenheit, or kilograms and pound or feet and meters, then this function would save you time.

Calculation Extension Point for Database Writes

When troubleshooting Web Intelligence documents in the past, I've not-infrequently wanted a way to record what's within the document on some persistent store.

Often a problem doesn't rear its ugly head when you're looking - the issue may be intermittent, or caused by some as-yet-unknown combination or prompt values, or only for a select number of users. Grepping through logs after logs soon starts to feel like you're looking for a needle in a GigaByte haystack.

If only there was a Web Intelligence function like

* DBWrite(string message)*

that you can place within a document cell, and every time the function is evaluated with a document refresh or page navigation, the message argument is written to a database. Then you can place a cell with the following variable at the top of a report

=DBWrite(DataProviderSQL(DataProvider( (City))))*

and record the SQL query being sent to the database to retrieve the (City) object whenever an user refreshes the document. Or, you can place a cell with variable

=DBWrite(DocumentName() + ";" + CurrentUser() + ";" + Page())*

in the page footer, and record who was reading what document and which page. If you think that most people only read the first page of a document, then you're right - and now you'll have the data to back you up.

Here's the code (I recommend Select All Ctrl-A within the text area, and Copy Ctrl-C to a text editor, to make the code easier to read):

WebICalcExtDBWrite.cpp

/*

WebICalcExtDBWrite

This sample code shows how to implement a Web Intelligence Calculation Extension that writes

the string parameter value to an ODBC database.

You can use this to log Web Intelligence Document usage.

*/

#include <ibovariant.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

using namespace BOExtFunct;

BO_DECLARE_USER_FCT(DBWrite, returnValue, parameters) {

RETCODE retcode;

SQLHENV sqlhenv = SQL_NULL_HENV;

SQLHDBC sqlhdbc = SQL_NULL_HDBC;

SQLHSTMT sqlhstmt = SQL_NULL_HSTMT;

SQLINTEGER cbMessage = SQL_NTS;

int ok = 0;

try {

/*

Hard-coded ODBC connectivity info.

*/

UCHAR db_dsn[26] = "logme";

UCHAR db_user[26] = "db_username";

UCHAR db_pass[26] = "db_password";

/*

Retrieve string parameter to be written to the database, truncated to fit.

*/

const iBOValue &param0 = parameters[0];

std::wstring themessage(param0);

if(themessage.length() > 8192) {

themessage = themessage.substr(0, 8192);

}

SQLWCHAR* message = const_cast<SQLWCHAR*>(themessage.c_str());

/*

Connect to the ODBC database. The database scheme definition is given by (MySQL 5):

CREATE TABLE logme (

id int NOT NULL auto_increment,

timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

message varchar(8192) default NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

*/

SQLAllocHandle(SQL_HANDLE_ENV, NULL, &sqlhenv);

SQLSetEnvAttr(sqlhenv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

SQLAllocHandle(SQL_HANDLE_DBC, sqlhenv, &sqlhdbc);

retcode = SQLConnect(sqlhdbc, db_dsn, SQL_NTS, db_user, SQL_NTS, db_pass,SQL_NTS);

if((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {

/*

Create prepared statement, bind parameter, and execute.

*/

SQLAllocHandle(SQL_HANDLE_STMT, sqlhdbc, &sqlhstmt);

SQLPrepareW(sqlhstmt, L"INSERT INTO LOGME (message) VALUES (?)", SQL_NTS);

SQLBindParameter(sqlhstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_VARCHAR, 255, 0, message, 0, &cbMessage);

retcode = SQLExecute(sqlhstmt);

/*

Echo the parameter value back or error message if error encountered.

*/

if((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {

returnValue = themessage;

} else {

returnValue = L"Error Executing SQL Statement";

}

} else {

returnValue = L"Error Connecting to Database";

}

} catch(...) {

/*

Catch everything and eat what you catch.

*/

ok = -1;

}

/*

Clean up database resources.

*/

try {

SQLFreeHandle(SQL_HANDLE_STMT, sqlhstmt);

SQLDisconnect(sqlhdbc);

SQLFreeHandle(SQL_HANDLE_DBC, sqlhdbc);

SQLFreeHandle(SQL_HANDLE_ENV, sqlhenv);

} catch(...) {} // Ignore cleanup errors.

return (ok == 0) ? BONOERROR : BOERROR;

}

The code logs onto a ODBC connection, writes the message argument to the database, then echoes back the message on function return.

Calculation Extensions use a XML file to declare the function - its arguments, return type, and dynamic library where its implementation is to be found. Here's the XML for the above custom function:

WebICalcExtDBWrite.xml

Calculation Extension Point for Currency Exchange Rates

Dan Paulsen - the guy who sits next to me at the office and is a fount of knowledge for all things .Net - had a cool idea: Why not implement a Calculation Extension Point that goes on the web and retrieves the current exchange rate between any two currencies?

Well, why not? Then you can have a Web Intelligence function

MyCurrencyRate(string from_currency ; string to_currency)

that, given the three-letter currency codes for the "from" and "to" currency, returns the current exchange rate.

Then given a report with a (US Dollar) object, you can compute its current Canadian dollar value via the variable

* =MyCurrencyRate("USD", "CAD")* (US Dollar)*

The beauty of this function is that the Canadian currency value will be current whenever you refresh the document!

Here's the code:

WebICalcExtCurrencyRate.cpp

/*

WebICalcExtCurrencyRate

This sample code shows how to implement a Web Intelligence Calculation Extension that

retrieves Currency Exchange Rate from a web site.

*/

#pragma comment(lib, "wininet")

#define UNICODE

#include

using namespace BOExtFunct;

BO_DECLARE_USER_FCT(MyCurrencyRate, returnValue, parameters) {

int ok = -1;

HINTERNET internet;

HINTERNET connect;

DWORD size;

char* buffer[1024];

/*

URL to the Web site that returns the currency conversion rate:

currencyConverter.jsp?from=<from currency>&to=<to currency>

where <from currency> and <to currency> is the 3-letter currency code.

For example, to retrieve the exchange rate from US$ to CDN$, invoke

currencyConverter.jsp?from=USD&to=CAD

*/

std::wstring url(L"http://tueda-bexi3.product.businessobjects.com:8080/bexi3/currencyConverter.jsp?");

WCHAR header[] = L"Accept: /

";

try {

/*

Retrieve the from and to Currency Codes and construct the appropriate URL.

*/

const iBOValue& fromCurrencyCodeValue = parameters[0];

const iBOValue& toCurrencyCodeValue = parameters[1];

std::wstring fromCurrencyCode(fromCurrencyCodeValue);

std::wstring toCurrencyCode(toCurrencyCodeValue);

url = url.append(L"from=").append(fromCurrencyCode).append(L"&to=").append(toCurrencyCode);

if(!(internet = InternetOpen(L"WebICalcPluginSampleCurrencyRate",

INTERNET_OPEN_TYPE_DIRECT, NULL, NULL, 0))) {

returnValue = 0.0;

ok = -1;

} else if(!(connect = InternetOpenUrl(internet, url.c_str(), header, lstrlen(header), INTERNET_FLAG_DONT_CACHE, 0))) {

InternetCloseHandle(internet);

} else {

/*

Call the URL, and parse the body for the numeric currency rate.

*/

InternetReadFile(connect, buffer, 1024, &size);

std::string s((const char*) buffer, size);

std::istringstream strstream(s);

double rate;

strstream >> rate;

returnValue = rate;

ok = 0;

InternetCloseHandle(connect);

InternetCloseHandle(internet);

}

} catch(...) {

}

return (ok == 0) ? BONOERROR : BOERROR;

}

and the XML declaration:

WebICalcExtCurrencyRate.xmlThe code uses the
WinINet
API to call a JSP page currencyConverter.jsp deployed on a Java Web Application Server that, given the "from" and "to" currency codes, returns the current exchange rate. I wrote currencyConverter.jsp to invoke a page within Yahoo! Finance (http://finance.yahoo.com/) and scrape the HTML for the relevant rate. Here's the JSP code:</p>bq. currencyConverter.jsp
<br /><textarea cols="75" rows="10"><%@page

>     import = "java.net.*,

>               java.io.*,

>               java.util.*,

>               java.text.SimpleDateFormat"

> %>
<%

/** currencyConverter.jsp

Returns currency exchange rate from Yahoo! Finance.

Invoke as follows:

currencyConverter.jsp?from=USD&to=CAD

*

to get the currency rate from US Dollars to Canadian Dollars.

*

Returns conversion rate, or zero if any issues occur.

*/

try {

String fromCurrencyCode;

String toCurrencyCode;

Calendar today;

SimpleDateFormat dateFormat;

String todayString;

URL converterURL;

URLConnection converter;

InputStream converterContent;

ByteArrayOutputStream byteArrayOutputStream;

byte[] buffer;

int count;

String content;

/*

Retrieve the 3-letter currency codes.

*/

fromCurrencyCode = request.getParameter("from");

toCurrencyCode = request.getParameter("to");

/*

Specify today's date using format yyyyMMdd, e.g., 20090724

*/

today = Calendar.getInstance();

dateFormat = new SimpleDateFormat("yyyyMMdd");

todayString = dateFormat.format(today.getTime());

/*

Retrieve HTML content from the Yahoo! Finance Currency Conversion page,

*/

converterURL = new URL("http://finance.yahoo.com/currency/converter-pocket-guide/"

+ todayString + "/" + fromCurrencyCode + "/" + toCurrencyCode);

converter = converterURL.openConnection();

converterContent = (InputStream) converter.getContent();

/*

FIXME: Fragile string parsing for the conversion rate.

*/

byteArrayOutputStream = new ByteArrayOutputStream();

buffer = new byte[1024];

while((count = converterContent.read(buffer)) >= 0) {

byteArrayOutputStream.write(buffer, 0, count);

}

content = byteArrayOutputStream.toString("UTF-8");

content = content.substring(content.indexOf(fromCurrencyCode + " to " + toCurrencyCode));

content = content.substring(content.indexOf("exchange rate of ") + "exchange rate of ".length()).trim();

content = content.split("

s", 2)[0];

out.println("" + Double.parseDouble(content));

} catch(Exception eek) {

out.println("0.0");

}

%>

I didn't directly access the Yahoo! page from my Calculation Extension code, but went through an intermediate page, since I wanted the Extension code to be simple as possible. That turned out to be a good thing, since the orignal web service I was relying on just...died...just before I finished up the code.

It was simple to modify the JSP page - to redirect to the relevant page on Yahoo! Financial and screenscrape the relevant HTML for the current exchange rates. I didn't need to change the Calculation Extension code part at all...

Here's a screenshot of a doc that uses this function:

!https://weblogs.sdn.sap.com/weblogs/images/251913699/WebICalcExtCurrencyRate.jpg|height=442|alt=imag...!</body>