Transforming XML Documents for IBM Cognos BI – Part I

Written by The Envisn Team | August 9, 2010

Eugene Marcotte - Envisn, Inc.
This blog article explains some of the issues involved with managing XML data for use in Cognos and some ways you can deal with them. The primary focus is the initial step of data conversion. Part II will cover transforming XML documents using XQuery.

What’s available?

There are at least two ways I know of (and probably many more exist) to get XML data re-arranged, manipulated and structured the way Cognos Framework Manager needs it. They all have strengths and weaknesses and a huge range in difficulty.

Part of the problem is that XML data can come in any structure so it is really pretty much impossible to come up with a general purpose solution for how to map it into the structure you need for Cognos. Luckily there are tools available, such as eXtensible Stylesheet Language (XSL), which lets you run transformations (XSLTs) on XML documents. The next step up in complexity is the XQuery language which is very similar to XSLT with a different syntax.

For these examples, assume the source document looks something like our original customer’s example, (“Reporting with XML data in IBM Cognos BI – Part I” ) but not conforming to the Framework Manager schema. Note the namespace (xmlns=”http://envisn.com/”), this is just for convenience in the examples. It is entirely possible to address elements without a namespace.

<customers xmlns=”http://envisn.com/”>
   <customer id=’1’>
      <name>Envisn</name>
      <location>Harvard, MA</location>
   </customer>
   <customer id=’2’>
      <name>IBM</name>
      <location>New York</location>
   </customer>
</customers>

XSLT (Saxon)

XSLT is pretty much the standard way to transform an input document to some desired output. Typically people use it to generate web pages (HTML) based on XML data. It is a very mature technology and very fast.

One of the draw-backs is that it works on a single input document to generate its output (though there are some exceptions to this rule, using some advanced features like fn:doc() and XInclude). It is also very verbose compared to other tools like XQuery. In addition, each time your source document changes, you need to regenerate your data source document. So, each time you add a new customer to our example you need to re-run our transformation. To actually run an XSL you need a tool that can read and run the transformations. One example is Saxon 9, which is free, runs fast, and supports tons of features.

A simple XSL for converting our customer XML data might look something like this:

<xsl:stylesheet version="2.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
     xmlns:xs="http://www.w3.org/2001/XMLSchema"
     xmlns:e="http://envisn.com/"
     xmlns="http://developer.cognos.com/schemas/xmldata/1/">

    <xsl:template match='e:customer'>
           <row>
                  <value><xsl:value-of select='@id' /></value>
                  <value><xsl:value-of select='e:name' /></value>
                  <value><xsl:value-of select='e:location' /></value>
          </row>
     </xsl:template>

     <xsl:template match='/'>
         <dataset xmlns:xs="http://www.w3.org/2001/XMLSchema">
                  <metadata>
                         <item name="ID" type="xs:decimal" />
                         <item name="Name" type="xs:string" />
                         <item name="Location" type="xs:string" />
                  </metadata>
                  <data>
                         <xsl:apply-templates select="//e:customer"/>
                  </data>
        </dataset>
    </xsl:template>
</xsl:stylesheet>

For reference for how to write XSL, check out the w3schools tutorial. To run this XSL save it as “to-cognos.xsl” to the same location as your XML data. To start the transform using Saxon9, run the following command from a prompt (the syntax is the same for both Unix and Windows):

java -cp saxon9he.jar net.sf.saxon.Transform -s:doc.xml -xsl:to-cognos.xsl -o:cognos-datasource.xml

This runs the Saxon transformer with ‘doc.xml’ (the customer data) as input, using ‘to-cognos.xsl’ (our XSL) as the transformation, and saves the results to ‘cognos-datasource.xml.’ For more information on running Saxon XSLTs from command line check the documentation.

Part II will cover using XQuery.

View full post