IBM Cognos Proven Practices: IBM Cognos BI – XML as a Data Source

Nature of Document: Tip or Technique; Product(s): IBM Cognos BI; Area of Interest: Infrastructure, Modelling

IBM Cognos provides the ability to connect to many types of data sources for reporting, including XML. This document will illustrate techniques for connecting to XML data sources with IBM Cognos BI.

Share:

Introduction

Purpose

This document will illustrate techniques for connecting to XML data sources with IBM Cognos BI.

Applicability

The techniques in this document were tested with IBM Cognos 8.4 and IBM Cognos 10.1.1.

Assumptions

This document assumes familiarity with Framework Manager, XML, JSP code, shell scripts, and deploying an application to an application server or servlet/JSP container. Administrator privileges will be required to create data sources in IBM Cognos BI.

Exceptions

The techniques in this document are intended for smaller XML data sets. Testing is required to ensure acceptable performance in the environment these techniques are used. For larger data sets or more flexible XML implementations consider using IBM Cognos Virtual View Manager.


Overview

IBM Cognos provides many types of data sources to connect to for reporting. XML is one of them and is done without any extra infrastructure requirements such as middleware.

Administrators can connect directly to an XML file, or access XML via a URL. For the latter, the XML source can be an XML file residing in a Web server directory, or can be XML that is streamed programmatically, such as with a JavaServer Page (JSP). This document will provide examples of how to implement these methods.


Valid XML for IBM Cognos BI

When using IBM Cognos BI to connect directly to an XML source, the XML source must adhere to the schema found in the xmldata.xsd schema file located in <IBM Cognos BI install location>\bin. It is not necessary to specify the location of the schema in the XML source file itself. The XML source simply needs to validate with the xmldata.xsd file.

Here is a brief listing of some of the data types supported.

xs:int
xs:string
xs:double
xs:byte
xs:date

Please refer to the xmldata.xsd schema file and the documentation for more information and a full listing of the supported data types.

Below is a small example of what a valid IBM Cognos BI XML data source looks like.

<?xml version="1.0" encoding="UTF-8"?>
<dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/"
 xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
	<metadata>
		<item name="AsOfDate" type="xs:string" length="12"/>
		<item name="RateOfReturn" type="xs:double"/>
		<item name="FamAcctIndex" type="xs:string" length="3"/>
		<item name="RowID" type="xs:string" length="1"/>
		<item name="BrM" type="xs:string" length="1"/>
		<item name="ProductLineCode" type="xs:int"/>
	</metadata>
	<data>
		<row>
			<value>Apr 26, 2002</value>
			<value>0.210066429</value>
			<value>JA1</value>
			<value>F</value>
			<value>B</value>
			<value>1</value>
		</row>
		<row>
			<value>Apr 27, 2002</value>
			<value>0.1111111</value>
			<value>BBB</value>
			<value>G</value>
			<value>B</value>
			<value>2</value>
		</row>		
	</data>
</dataset>

It is important to note that the “length” attribute affects the efficiency of processing the XML data by IBM Cognos BI. Memory is allocated to the number specified in the “length” attribute regardless of the actual size of the data. For example, if the largest piece of data for an element is 50 characters, but a higher value is chosen for the “length” attribute, such as 255, memory will be allocated for the full 255. It is recommended to specify a length as close as possible to the largest estimated piece of data.

Again, for more flexible implementations of XML as a data source, consider using IBM Cognos Virtual View Manager.


Connect to an XML File on the File System

Provided with this document is a file called Test.xml which conforms to the xmldata.xsd schema. This file contains the structure of a basic XML data source.

In the following steps, a data source connection will be made to the Test.xml file and then a Framework Manger model will be created using this new XML data source.

  1. Place the provided Test.xml file somewhere on the file system. In this example, the file is placed on the root of the C drive.
  2. Open Framework Manager and create a new project called XML File Test.
  3. Select a Design Language, and then click OK.
  4. Under Select Metadata Source, select Data Sources, and then click Next.
  5. Click the New button.
  6. Click Next, and then in the Name field, type xmlFileTest.
  7. Click Next, and then select XML as the data source type from the Type: dropdown.
    Illustration 1: New Data Source wizard – type selection screen
    Illustration 1: New Data Source wizard – type selection screen
  8. Click Next, and then in the Connection string: box, enter the full or UNC path to the Test.xml file.
    Illustration 2: New Data Source wizard – Connection string screen
    Illustration 2: New Data Source wizard – Connection string screen
    A UNC path is recommended when the XML file is on a share and needs to be accessed by multiple IBM Cognos BI servers. If a local path is used, a copy of the XML file will need to be placed in that location on each IBM Cognos BI server, whic requires more maintenance.
  9. Click Finish, and then click Close.
  10. Select xmlFileTest as a data source, and then click Next.
  11. Select Tables. In this portion of the Framework Manager UI, the tree can be expanded to see the items being imported as seen below.
    Illustration 3: Metadata Wizard – xmlFileTest table expanded to show columns
    Illustration 3: Metadata Wizard – xmlFileTest table expanded to show columns
  12. Click Next, click Import, and then click Finish.
  13. In the Project Viewer pane, expand the xmlFileTest namespace.
  14. Right-click the xmlFileTest query subject, click Test and then click Test Sample.
    Illustration 4: Test Results dialog– Showing data returned from the XML data source
    Illustration 4: Test Results dialog– Showing data returned from the XML data source
    The data in the XML file is retrieved.
  15. Click Close, and then, in the Project Viewer, expand the xmlFileTest query subject. The item names found in the XML document are represented by query items.
    Illustration 5: Project Viewer – xmlFileTest query subject expanded to show query items
    Illustration 5: Project Viewer – xmlFileTest query subject expanded to show query items
    At this point a package can be created and published to be used as a reporting source in IBM Cognos BI.

Connect to an XML File over HTTP

In this example, the Test.xml file provided will be copied to a virtual directory and then referenced as a data source in IBM Cognos BI.

  1. Place the provided Test.xml file in <IBM Cognos BI install directory>\webcontent. Any virtual directory can be used but for the purposes of this example and ease of implementation, the webcontent directory will be used as it has already been set up as a virtual directory for the IBM Cognos BI application.
  2. Open Framework Manager and create a new project called XML Over HTTP Test.
  3. Select a Design Language, and then click OK.
  4. Under Select Metadata Source, select Data Sources, and then click Next.
  5. Click the New button.
  6. Click Next, and then in the Name field, type xmlOverHTTPTest.
  7. Click Next, and then select XML as the data source type from the Type: dropdown.
    Illustration 6: New Data Source wizard – Type selection screen
    Illustration 6: New Data Source wizard – Type selection screen
  8. Click Next, and then in the Connection string: box, enter the full URL to the Test.xml file, in this case http://localhost/cognos8/Test.xml. It is recommended that localhost be replaced with the actual server name or IP address.
    Illustration 7: New Data Source wizard – Connection string screen
    Illustration 7: New Data Source wizard – Connection string screen
  9. Click Finish, and then click Close.
  10. Select xmlOverHTTPTest as a data source, and then click Next.
  11. Select Tables, click Next, click Import, and then click Finish.
  12. In the Project Viewer pane, expand the xmlOverHTTPTest namespace.
  13. Right-click the xmlOverHTTPTest query subject, click Test and then click Test Sample.
    Illustration 8: Test Results dialog– Showing data returned from the XML data source
    Illustration 8: Test Results dialog– Showing data returned from the XML data source
    Again, the data in the XML file is retrieved, but this time over HTTP.
  14. Click Close. At this point a package can be created and published to be used as a reporting source in IBM Cognos BI.

Transform XML for Use in IBM Cognos BI

As mentioned earlier in this document, before an XML data source can be used in IBM Cognos BI, it must adhere to the schema found in the xmldata.xsd schema file located in <IBM Cognos BI install location>\bin. If the source XML does not conform to this schema, Extensible Stylesheet Language Transformations (XSLT) can be used in conjunction with a programming language of choice to transform the source XML into IBM Cognos BI consumable XML.

In the diagram below, a calling application, which can be any one of a number of programming languages or shell scripts, retrieves the XML from the source location. For example, the application may pull an RSS feed from a web site. The calling application then references an XSLT file that is used to transform the source XML into a different XML document, in this case, one that is appropriate for IBM Cognos BI.

Illustration 9: XML transformation application architecture diagram
Illustration 9: XML transformation application architecture diagram

The calling application can write the XML to a file which IBM Cognos BI can connect to on the file system or over HTTP through a virtual directory. This is a good choice if the resulting XML document is larger in size and has relatively static data. If the calling application is web-based, such as a JSP, it can directly stream the XML to IBM Cognos BI. This is more suitable when there is a requirement for real time data where the XML source is smaller in size (the transformation of the source XML will occur each time the JSP is called).

Both cases will be demonstrated in subsequent sections. However, before testing each scenario, the application called devWorksXMLDS, which is provided with this document, will be deployed using the Tomcat servlet/JSP container which ships with IBM Cognos BI.

If deploying to another application server such as IBM WebSphere, consult the documentation for deployment instructions.


Setting up the Provided Sample

The following is a list of high level steps followed by detailed instructions required to deploy the devWorksXMLDS application provided with this document. For multi-server installs, the application must be deployed on each IBM Cognos BI dispatcher.

  • Unpack the installation to an IBM Cognos BI server
  • Edit files that may require modification
  • Build a WAR file
  • Deploy the WAR file to an application server or servlet container

Unpack the Installation

Unpack the file devWorksXMLDS.zip to the <IBM Cognos BI install location>/war directory of an IBM Cognos BI installation.

The devWorksXMLDS directory should now be present.

At this point, it is best to configure the application before building the WAR file. Potentially only two files need configuration per environment - build.bat for Windows or build.sh for Unix/Linux and devWorksXMLDS.bat for Windows or devWorksXMLDS.sh for Unix/Linux.

Configure the Installation

Navigate to <IBM Cognos BI install location>/war/devWorksXMLDS to edit the required files.

If editing the build.bat file, the following line may need to be updated to point to the correct JRE or JDK location in that environment.

..\..\bin\jre\1.5.0\bin\java -classpath
 "../../bin/ant.jar;../../bin/xml-apis.jar;../../bin/xercesImpl.jar"
 org.apache.tools.ant.Main %*

If editing the build.sh file, the following lines may need to be updated to point to the JRE or JDK in that environment.

if [ "$JAVA_HOME" = "" ] 
then
	echo The JAVA_HOME variable must be present
else 
	$JAVA_HOME/bin/java -classpath
	 "../../bin/ant.jar:../../bin/xml-apis.jar:../../bin/xercesImpl.jar"
	 org.apache.tools.ant.Main $@
fi

If editing the devWorksXMLDS.bat file, the following line may need to be updated to point to the correct JRE or JDK location in that environment.

set MY_JAVA="c:\Program Files\cognos\c8.4\bin\jre\1.5.0\bin\java.exe"

If editing the devWorksXMLDS.sh file, the following lines may need to be updated to point to the JRE or JDK in that environment.

if [ "$JAVA_HOME" = "" ]; then
	JAVA_HOME=/usr/bin
fi

JAVA="$JAVA_HOME/java"

Build the WAR file

Build the WAR file by running the appropriate build script for the environment.

For Windows, run <IBM Cognos BI install location>/war/devWorksXMLDS/build.bat.

For UNIX or Linux, run <IBM Cognos BI install location>/war/devWorksXMLDS /build.sh.

The build scripts will create the <IBM Cognos BI install location>/war/devWorksXMLDS/devWorksXMLDS.war file.

Deploy the WAR file

In this example, the WAR file will be deployed to the IBM Cognos BI Tomcat server. Ensure that IBM Cognos BI is running.

Copy the <IBM Cognos BI install location>/war/devWorksXMLDS/devWorksXMLDS.war file to the <IBM Cognos BI install location>/webapps directory.

After a short time, the IBM Cognos BI Tomcat server will automatically unpack the WAR file. The WAR file can subsequently be deleted once released by Tomcat.

The deployed application contains the required JAR files which are used by the application's shell scripts and JSP.

Illustration 10: Windows Explorer – Showing required JAR files
Illustration 10: Windows Explorer – Showing required JAR files

These JAR files are required for the XML implementation and transformation. The XSLT processor used by this application is Xalan-J. Detailed information about Xalan-J can be found at http://xml.apache.org/xalan-j.

The devWorksXMLDS installation is now ready for use.


Transforming an RSS Feed into Consumable XML

The devWorksXMLDS application provides two mechanisms by which an RSS XML feed can be transformed into IBM Cognos BI consumable XML:

  1. By running a shell script (devWorksXMLDS.bat for Windows or devWorksXMLDS.sh for UNIX/Linux) which stores the transformed XML data in a file
  2. By calling a JSP and streaming the transformed XML data over HTTP

Both methods use XSLT to transform the source data into an IBM Cognos BI consumable XML document.

Run a Script to Generate an XML File

The devWorksXMLDS shell scripts provided retrieve XML from an RSS feed (in this case from the IBM developerWorks site), transforms the XML into IBM Cognos BI consumable XML and stores it in a file on the file system. This file can act as an IBM Cognos BI data source by connecting to the file through a local path or UNC path or by calling the file from a virtual directory over HTTP.

Illustration 11: XML transformation application architecture diagram
Illustration 11: XML transformation application architecture diagram

Again, this method is best suited for larger data sets and where the underlying data is relatively static. The data is refreshed by running the script on a pre-determined schedule.

The syntax for running the script with parameters is:

Windows

devWorksXMLDS <sourceXML> <xsltFile> <outputXML>

Unix/Linux

./devWorksXMLDS.sh <sourceXML> <xsltFile> <outputXML>

The sample scripts provide default values for the parameters, but if any parameter requires a different value than the default, all parameters must be specified.

Once the script is run, an XML file appears in the specified output location which can be used as a data source for IBM Cognos BI. By default, the output location is the same location as the shell script.

Illustration 12: Windows Explorer – Showing a generated output file named devWorksXMLDS.xml
Illustration 12: Windows Explorer – Showing a generated output file named devWorksXMLDS.xml

This file can now be connected to in an IBM Cognos BI data source connection through the file system as shown below:

Illustration 13: New Data Source wizard – Connection string screen with a file system path
Illustration 13: New Data Source wizard – Connection string screen with a file system path

The full connection string reads:

C:\Program Files\cognos\c8\webapps\devWorksXMLDS\devWorksXMLDS.xml

Again a UNC path is recommended so that the file may be accessed by multiple IBM Cognos BI servers.

If the XML file is in a virtual directory (as is the case with this example), the connection string would be as shown below:

Illustration 14: New Data Source wizard – Connection string screen with a URL
Illustration 14: New Data Source wizard – Connection string screen with a URL

The full connection string reads:

http://localhost:9300/devWorksXMLDS/devWorksXMLDS.xml

“localhost” should be replaced by the server name or IP address.

Call a JSP to Stream XML Data Over HTTP

Again, this method is typically used when there is a requirement for real time data that will not be resource intensive. The amount of data should be small in order to ensure performance.

The sample devWorksXMLDS JSP provided retrieves XML from an RSS feed (again, from the IBM developerWorks site), transforms the XML into IBM Cognos BI consumable XML and then streams it to IBM Cognos BI.

Illustration 15: XML transformation application architecture diagram
Illustration 15: XML transformation application architecture diagram

No data is stored with this method.

The URL to call the JSP is http://<server>:<port>/devWorksXMLDS/devWorksXMLDS.jsp, where <server>:<port> is the the application server that the devWorksXMLDS web application was deployed to. This is the same URL to be specified when configuring the Connection String property for the IBM Cognos BI data source.

In this example, the URL to the JSP deployed to Tomcat is http://localhost:9300/devWorksXMLDS/devWorksXMLDS.jsp where localhost is replaced by the actual server name or IP address.

To test the application, enter the URL in a Web browser. The results should appear similar to the screen capture below.

Illustration 16: Web browser showing XML results returned by the devWorksXMLDS.jsp application
Illustration 16: Web browser showing XML results returned by the devWorksXMLDS.jsp application

The location of the source data and the XSLT file is specified within the JSP. The variable sXMLSrc specifies the location of the source XML data and the variable sXSLTFile specifies the location of the XSLT file.

This page can now be connected to in an IBM Cognos BI data source connection as shown below:

Illustration 17: New Data Source wizard – Connection string screen
Illustration 17: New Data Source wizard – Connection string screen

The full connection string reads:

http://localhost:9300/devWorksXMLDS/devWorksXMLDS.jsp

Conclusion

Using the steps and sample code provided with this document can help to quickly implement XML as a data source in an IBM Cognos BI environment.


Download

DescriptionNameSize
Sample scripts for this articleCognos_BI-XML_as_a_Data_Source.zip6KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=818584
ArticleTitle=IBM Cognos Proven Practices: IBM Cognos BI – XML as a Data Source
publish-date=05282012