Additional Blogs by SAP
cancel
Showing results for 
Search instead for 
Did you mean: 
ted_ueda
Active Contributor
0 Kudos

SAP BusinessObjects Enterprise BI 4.0 introduces new SDK functionalities of interest to custom coders, integrators and OEM/Partners.  I've listed a few of these in a recent blog .  In this blog entry, I'll describe the Data Access Connection SDK.

This new public API's purpose is to allow for the management and maintenance of Universe relational data connections programmatically.  It's a feature oft requested in the past by developers, especially by those interested in integrating update of data connection metadata and parameters - especially DB logon credentials - into their Java applications.

First, a bit of background.

h2. Universes and Data Connections

The semantic layer for SAP BusinessObjects BI reporting solutions, Web Intelligence and Crystal Reports, consists of two parts:  the data connection, representing and encapsulating the metadata for connectivity to relational databases, and the Universe, representing an abstraction of the data schema in ways more intuitive and useful to users.

Since the days of BusinessObjects 6.x, the public SDK used to create, maintain and manage both data connections and Universes is the Universe Designer SDK .

Since BusinessObjects XI Release 1, these two objects, the former SI_KIND=Metadata.DataConnection and the latter SI_KIND=Universe, had their own separate InfoObject types, and were managed by the CMS separately.  Even then, the Universe Designer SDK was used to access and modify both types of objects.

Don't get me wrong, I enjoyed working with the Universe Designer SDK - it was simple to learn and code with.  But often, I'd encounter limitations imposed on its use because of its COM-based nature:  limits on scalability, limits on where I can deploy COM-based applications (especially when Enterprise was deployed on Unix servers), and especially limits on how I could integrate them into my Java apps. 

Furthermore, with BI 4.0 introducing a new Universe, unx,  that has its own client design tool,  Information Design Tool,  that shares relational database data connections with the 'classic Universe, unv, there was a compelling need to have a separate SDK strictly to manage just the data connecctions.

h2. Data Access Connection SDK

Thus the new DAC SDK - a public API for relational data connection modification and creation, fully integrated into the SAP BusinessObjects Enterprise Java SDK. 

Documentation is available on-line, both the [Developer Guide | http://help.sap.com/businessobject/product_guides/boexir4/en/connsdk_java_dg_40_en.zip] and JavaDocs .  

You'll need to refer to the Developer Guide for a list of additional jar files required over those for the Enterprise SDK.  

The DAC SDK works with the connection metadata that's not encapsulated as a property of IDataConnection InfoObject objects.  The connections modifiable via the DAC has been given a new type, SI_KIND='CCIS.DataConnection' (an aside - I've seen people write code for previous versions that directly access the old Metadata.DataConnection to modify the connections using a non-public-API - that'll no longer work, and was never supported in the first place).  The property holds the metadata as an XML.

Essentially, DAC methods allow you to retrieve this XML from the InfoObject, make modifications without breaking the XML validity, then commit the changes back.  Plain and simple.   

Typical workflow:

  • Retrieve the MutableConnection property from the IDataConnection.
  • Update the properties using the MutableConnection.putProperty(...) method, that updates the XML metadata.
  • Do IDataConnection.setConnection(MutableConnection) and IDataConnection.save() to commit the changes back.
h2. Sample code to change DB passwords

I've uploaded a simple sample code here: Java_DAC_BE14_Change_Connection_Password.

This simple sample uses the DAC SDK to update the database password for a Data Connection specified by its SI_CUID value.    Here's the main code:

bq. java_dac_be14_change_connection_password.jsp<br /><%
/*
  1. Applies to Version: SAP BusinessObjects Enterprise BI 4.0
  1. Last Modified: 2010-12-18
  1. Description:
  1.     Shows how to change a Data Connection password using the
  1.     Data Access Connectoin SDK
  1.     
*/
%><%@page import = "com.crystaldecisions.sdk.exception.SDKException,
                    com.crystaldecisions.sdk.framework.*,
                    com.crystaldecisions.sdk.occa.infostore.*,
                    com.sap.connectivity.cis.plugin.api.*,
                    com.sap.connectivity.foundation.api.*,
                    java.util.*"
%><html>
<head>
     <title>DAC SDK Change Connection Password</title>

     <link href="style.css" mcehref="style.css" rel="stylesheet" type="text/css" />

<%
IEnterpriseSession enterpriseSession = null;


/*
  1. Retrieve Enterprise Logon credentials.
*/


final String BO_CMS_NAME  = request.getParameter("bo_cms_name");
final String BO_AUTH_TYPE = request.getParameter("bo_auth_type");
final String BO_USERNAME  = request.getParameter("bo_username");
final String BO_PASSWORD  = request.getParameter("bo_password");


/*
  1. Retrieve Connection CUID and new password.
*/
String connectionCuid = request.getParameter("connection_cuid");
if(connectionCuid == null) {
    connectionCuid = "";   
}


String newConnectionPassword = request.getParameter("connection_password");
if(newConnectionPassword == null) {
    newConnectionPassword = "";   
}


/*
  1. Construct InfoStore query for the DataConnection InfoObject.
*/


String query = "Select TOP 1 "
             + "     * "
             + " From "
             + "     CI_APPOBJECTS "
             + " Where "
             + "     SI_KIND='CCIS.DataConnection'  "
             + "     And SI_CUID='" + connectionCuid.replaceAll("'", "''") + "' ";


try {
    IInfoStore infoStore;
    IInfoObjects infoObjects;
    IDataConnection dataConnection;
    MutableConnection mutableConnection;
    Connection.PropertySet propertySet;


    /*
    
  1. Logon to Enterprise and retrieve the InfoStore service.
     */


    enterpriseSession = CrystalEnterprise.getSessionMgr().logon(BO_USERNAME, BO_PASSWORD, BO_CMS_NAME, BO_AUTH_TYPE);
session.setAttribute("enterpriseSession", enterpriseSession);
    infoStore = (IInfoStore) enterpriseSession.getService("", "InfoStore");


    /*
    
  1. Retrieve the DataConnection InfoObject.
     */


    infoObjects = (IInfoObjects)infoStore.query(query);
    dataConnection = (IDataConnection) infoObjects.get(0);


    /*
    
  1. Modify the "CREDENTIALS" category "PASSWORD" property for the connection,
    
  1. then update and commit the DataConnection.
     */
   
    mutableConnection = (MutableConnection) dataConnection.getConnection();
    //mutableConnection.putProperty("USER", "CREDENTIALS", "String", newConnectionUsername);
    mutableConnection.putProperty("PASSWORD", "CREDENTIALS", "String", newConnectionPassword);


    dataConnection.setConnection(mutableConnection);
    dataConnection.save();


    /*
    
  1. List out the new DataConnection credentials.
     */


    propertySet = mutableConnection.Properties();


%>
    <div class="infoDiv">
        <table class="infoTblx">
            <th colspan="2" class="tableHeader">Data Connection Updated Info</th>
            <tr>
                <td>Connection CUID: </td><td class="tdval"><%=dataConnection.getCUID()%></td>
            </tr>
            <tr>
                <td>Connection Name: </td><td class="tdval"><%=dataConnection.getTitle()%></td>
            </tr>
<%
    for(String key : propertySet.keySet()) {
        Connection.PropertySet.Entry entry;
        entry = propertySet.get(key);
        if("CREDENTIALS".equals(entry.Category())) {
%>
            <tr>
                <td><%= entry.Key() %>:</td><td class="tdval"><%= entry.ObjectValue() %></td>
            </tr>           
<%
        }
    }
%>
        </table>
    </div>

<%
} finally {


    /*
    
  1. Ensure logoff.
     */
    try {
        if(enterpriseSession != null) {
            enterpriseSession.logoff();
        }
    } catch(Exception e_ignore_in_cleanup) {};
}
%>


The bit that updates the password is this:

    mutableConnection.putProperty("PASSWORD", "CREDENTIALS", "String", newConnectionPassword);

It updates the "PASSWORD" property of the "CREDENTIALS" category of value type "String" with the new value.  

It's quite simple.  

It's fairly common to have organizations update their database passwords regularly, and it's at times a chore to ensure all consumers of the database connection remain updated with the latest passwords.    The DAC SDK greatly helps in this regard - it wouldn't take that much effort to create something schedulable - say a Java Program Object, that'll retrieve the new password from a secure source, and update all relevant IDataConnection InfoObject objects on Enterprise with the new values.

h2. Conclusion

The Data Access Connection SDK is a simple API, but with a lot of bang-for-the-buck development time-wise.  It's simple to learn, simple to implement, and certainly something that will simplify your database administrative tasks.

3 Comments