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.
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:
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.