by Eugene Marcotte - Envisn, Inc.
This blog article attempts to give a quick overview of Cognos data sources, XML documents and how you can start to bridge the divide between the two. IBM’s developer Works site has another article which may also provide some different insight into this process. In Part II, XML data with XQuery, we will continue this and cover creating and using XML data sources with Framework Manager.
What are data sources?
Anyone familiar with IBM Cognos BI and Framework Manager will have a pretty good idea what a data source is. Basically they provide Cognos with an interface to tabular or dimensional data. Mostly this is through SQL databases like Oracle or Microsoft SQL Server. Framework Manager builds a model which can generate SQL to run against these sources.
What is XML?
Briefly, XML is a data format that is not tabular. It is strictly hierarchical. In addition, it has no in-built notion of data types (such as VARCHAR or INTEGER). The basic building blocks are elements, text and attributes (as well as a few others that I won’t cover here). A quick example is:
<customer id=’1’>
<name>Envisn</name>
<location>Harvard, MA</location>
</customer>
<customer id=’2’>
<name>IBM</name>
<location>New York</location>
</customer>
</customers>
Since it is not constructed into neat rows and columns it is not going to be very helpful for modeling in framework manager which needs to map into this data.
Using schemas to understand what you have
XML and SQL tables share the concept of a schema. When you create a new table you create a schema. A table of customers might contain columns with an INTEGER id, and two VARCHARS one for name and another for location. This forces the data into a common format which makes it possible to write queries against.
XML itself is well defined, but it is possible to define even tighter XML schemas. An XML schema can define the structure of elements such as how they nest, and what values they can take. Just like SQL schemas this makes it possible to write queries against the data. Note: While it is possible to query against data without a schema, the schema forces data to fit into a form which lets application developers build mappings and assumptions into their applications.
How does an XML schema help us get XML into Framework Manager?
It turns out that there is a schema of sorts that Framework Manager can understand and build models against. It is probably easiest to visualize with an example, using the same data as before:
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>
<row>
<value>1</value>
<value>Envisn</value>
<value>Harvard, MA</value>
</row>
<row>
<value>2</value>
<value>IBM</value>
<value>New York</value>
</row>
</data>
</dataset>
This simple example shows the rough outline of the format of the XML data Framework Manager can understand. It defines a dataset as two sections: metadata and data. It is important to note the namespace ( xmlns='http://developer.cognos.com/schemas/xmldata/1/') on the data. Framework Manager only looks at elements in this namespace.
The metadata defines what columns are available, think of it as the table schema in SQL. Since XML doesn’t really have data types, it uses some identifiers from XMLSchema to tag the text of the fields with the way they should be interpreted by IBM Cognos.
The data section is simply a list of rows with values. The order of the rows is the order Cognos will default in showing the data until you define a sorting on it in a report or Framework Manager. The order of values must match that of the items in the metadata section. That is, if the first item in metadata is ID, the first value of every row must be the ID.
Part II will cover creating and using XML data sources with Framework Manager.
