envisn on twitter envisn on linkedin envisn on facebook envisn on google+ youtube feed for envisn cognos blog




free ebook: download the content store survival guide newly revised

Envisn's IBM Cognos Blog

Current Articles | RSS Feed RSS Feed

Reporting with XML data in IBM Cognos BI – Part II

  
  
  
  

by Eugene Marcotte - Envisn, Inc.
In Part I we covered the basics of XML data. Here we will continue to broaden this subject area.

How do I create and use an XML Data Source in Framework Manager?

First create a new project in Framework Manager. Log in, create a language, and then when it asks you to pick a data source, click new. Pick a name, description and screen tip. Set the Type to “XML.” When asked for a connection string we enter the location of the XML document. The simplest case is just providing the path on your hard disk. On my test server I used an absolute path on the hard drive: C:\Documents and Settings\Administrator.ENVISN-OFC\Desktop\Xml data\data.xml

Remember, this file needs to be accessible by the server(s) that is running IBM Cognos so an absolute path like this generally doesn’t work well for large environments. When you test the connection there should be a connection string that looks something like: ;LOCAL;XML;C:\Documents and Settings\Administrator.ENVISN-OFC\Desktop\Xml data\data.xml

If all goes well, hit finish. Next, select the newly created data source and then select which tables and fields you want to use. Expand the datasources, tables and you should see our table with our fields listed.

Once the fields are imported into Framework Manager it is possible to do a test against the data to make sure it can read from the file correctly.

framework manager screenshot

And that’s pretty much the whole process for importing your XML data. You can use Framework Manager to do fancy modeling, renaming, and joining between data sources as required. Then, once you’re finished modeling you can create and publish the package to Cognos Connection and use it in Report Studio just like any other model.

Keep in mind that this is not an SQL system and as such the data-processing capabilities of Cognos are drastically reduced. It cannot have the SQL server compute joins between data, filter, summarize columns or do any other fancy manipulation. It can only do local processing in Cognos which is notoriously slow. In addition, to model multiple XML documents or “tables” you have to create a data source for each – unless someone knows some tricks I don’t!

Even more frustrating perhaps is that your XML data probably doesn’t conform to the schema that Framework Manager requires. Fear not! This is one of the strengths of XML – it is easily manipulated.

Simpler XML Access in IBM Cognos – Use SQL

There is a middle ground to using XML in Cognos and that is to use your database server’s built-in XML support. SQL Server, DB2, and others all have varying degrees of support for embedding XML and as a result let you build Framework Manager.

IBM has an article about doing just this using DB2 pureXML on their developerworks site:

http://www.ibm.com/developerworks/data/library/techarticle/dm-0811saracco/index.html

The article lists three options for dealing with XML in an SQL server, basically:

  1. Treat it as raw text
  2. Convert it to tables
  3. Let pureXML (or whatever your vendor happens to call their tool) deal with it

The nice thing about this is that it pushes all the XML management issues into one area, the database server, and leaves Framework Manager and IBM Cognos free to use their standard SQL querying mechanisms. This is extremely useful because it can exploit database server optimizations, keep your source XML documents in their original format, and reduce the number of factors involved when getting your data into IBM Cognos.

Keep in mind that there are dozens more options for hosting your XML data. Using some open source tools and other IBM products you can keep your XML documents in their original structure and run a transformation into the IBM Cognos schema. When using one of the XML via SQL solutions like pureXML, you are already performing something similar to this transformation, typically using some technology like XPath to map documents and their elements to views and columns.


Comments

Hi i had follwed all the steps and created a datasource but when i click on the datasource it is giving me an below error.Please suggest. 
 
QE-DEF-0285 The logon failed. 
 
QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible. 
 
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:  
 
Sample 
 
 
 
UDA-SQL-0031 Unable to access the "Sample" database. 
 
UDA-SQL-0528 The XML parser returned the following message: "C:\Sales Project.xml does not exist. 
 
". 
 
UDA-SQL-0529 XML "C:\Sales Project.xml" is not a valid XML document. 
 
Posted @ Thursday, September 08, 2011 1:51 PM by Deepthi
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics

Subscribe to the IBM Cognos Blog

Your email: