IBM Cognos 8 provides basic support for XML as a data source. This support is supplemented by IBM Cognos Virtual View Manager: a secure, scalable, and flexible solution for querying XML as a data source.
IBM Cognos Virtual View Manager is a federation engine that provides unified ODBC access to multiple, disparate data sources, including XML data sources in various forms. IBM Cognos Virtual View Manager exposes these data sources as tabular structures that Cognos 8 can query.
IBM Cognos Virtual View Manager is available, for no additional cost, starting with the IBM Cognos 8.4 software. Before IBM Cognos 8.4, IBM Cognos Virtual View Manager was known as the Composite Information Server.
IBM Cognos Virtual View Manager
This section gives a brief overview of the IBM Cognos Virtual View Manager (VVM). VVM federates data from across multiple, disparate data sources and provides a unified ODBC interface to its clients. VVM consists of a central, Java-based, multi-threaded server that performs the federation. Note that VVM is a standalone Java process, and it does not run within a J2EE server such as IBM WebSphere® Application Server. VVM also has a web-based GUI to perform certain tasks related to administration, monitoring, and security for the VVM server. Figure 1 shows this high-level architectural diagram of VVM.
Figure 1. Cognos Virtual View Manager architectural overview
VVM Studio, a thick GUI client, enables the modeling of external data sources as relational structures. VVM Studio creates, as well as publishes, heterogeneous views for the ODBC clients to consume. IBM Cognos 8 uses the ODBC interface in order to communicate with VVM. As far as IBM Cognos 8 is concerned, VVM is like any other relational database management system with which it can communicate using the ODBC protocol.
This article focuses on XML data sources only.
Understanding the types of XML data sources supported
VVM supports the following comprehensive list of XML data sources:
- The XML source can be a file that is accessible either over the local file system or over a network using a URL. If the remote computer that is hosting the XML document does not have a Web server, the XML document needs to be mapped to the computer where the VVM server is running.
- The XML source can be an XML document streamed over HTTP. That is, the XML can be the message body of an HTTP response from an HTTP server. VVM supports HTTP GET and HTTP POST requests for access to such XML documents.
- The XML can be a response from a Web service operation. When provided the location of a WSDL document, VVM is capable of accessing any operation that the WSDL defines as a potential source of XML. Although a WSDL can bind web services to any messaging protocol such as SOAP, HTTP, or MIME, VVM supports only SOAP bindings. The SOAP bindings dictate the underlying transport protocol, the encoding scheme, and the message style. As far as VVM is concerned, the transport protocol supported is HTTP, so the SOAP envelopes must be exchanged over an HTTP connection. The encoding scheme that determines how the messages are encoded for transport should be literal. The SOAP messages will be transmitted as plain XML capable of being validated against their own schema. However, VVM does have limited support for the SOAP message encoding scheme as the SOAP specification defines it. VVM supports both rpc and document message styles.
Querying XML data sources using VVM
This section describes how you can query an XML data source in IBM Cognos 8 using VVM. The process follows these steps:
- Step 1. Create an XML data source in VVM
- Step 2. Transform the XML
- Step 3. Publish the transformation
- Step 4. Query the data source from IBM Cognos 8
Step 1. Create an XML data source in VVM
In this phase, you introduce the XML data source (File-XML, XML-HTTP, or WSDL) to VVM by providing the necessary information about the data source.
- For a File-XML data source, you need to provide the location of the directory on the local file system, or you need to provide a URL to a file hosted by a remote system. If the files are on the local file system, more than one file can be part of the File-XML data source. In the case of a remote file, the data source can contain only one file. You can also specify the XML document's character encoding and schema location, or you can let VVM detect the two automatically. The connection parameters needed to provide a File-XML data source include file name, path, character set, schema location, and file name filter, as shown in Figure 2.
Figure 2. Introspecting a File-XML data source
- For an XML-HTTP data source, you need to provide the HTTP request URL to fetch the
desired HTTP response, with XML in its message body. You need to specify the
type of HTTP request as either GET or POST. An HTTP GET
request is not supposed to have a message body. Therefore, any request parameters
to the HTTP server can be sent only by means of the Input Query String, which is the
part of the URL that comes after the
?(question mark). Conversely, an HTTP POST request can have a message body and can be leveraged to send input parameters to the HTTP server in the form of XML.
If you need to provide complex input parameters to the HTTP server, use the HTTP POST type of request.
Note that the input to the HTTP server and the response from the HTTP server will be XML and will be transported as the HTTP request or response message-body. Therefore, you need to provide the grammar of the input and output XML to VVM in the form of an XML-type definition set that primarily represents a valid XML structure. This enables VVM to construct and de-construct the HTTP request and response message bodies, respectively. Refer to the IBM Cognos Virtual View Manager Installation and Configuration Guide (see Resources) for more information on how to create XML-type definition sets.
The connection parameters needed to introspect an XML-HTTP data source include datasource name, URL, method, login ID, password, and pass-through login information, as shown in Figure 3.
Figure 3. Introspecting an XML-HTTP data source
- For a WSDL data source, you simply specify the WSDL location. The connection parameters needed to set up a
WSDL data source include name, URL, login ID, password, and pass-through login information.
Figure 4. Introspecting a WSDL data source
Using the introspected information, VVM fetches the metadata related to that particular XML data source. This metadata includes the structure of the XML document. The structure is used to transform the XML, as shown in Listing 1.
Listing 1. A sample XML document
<Contacts> <person> <name>Rob</name> <age>24</age> <Country>USA</Country> </person> <name>John</name> <age>34</age> <Country>Canada</Country> </person> </Contacts>
Figure 5 shows the structure VVM extracts as a result of the introspection process.
Figure 5. XML document structure introspected by VVM
For XML-HTTP or WSDL data sources, you can supply a user name and password if the HTTP server processing the requests expects them. For XML-HTTP and WSDL data source, VVM can also secure the XML data sources by means of digital certificates. VVM does this by providing the capability to associate certificate key stores with an XML data source. VVM provides support for two types of certificate keys stores: Java Key Store and PKCS12. When VVM tries to connect to the XML data source, it uses the certificates in the associated certificate key stores to establish its credentials with the server hosting the XML data. These two features ensure that data security is not compromised while accessing XML-based data sources.
Step 2. Transform the XML
In this step, you create a VVM transformation to convert the XML document's hierarchical structure into a row-based, relational structure. VVM Studio provides an easy-to-use GUI to create a mapping between the input XML nodes and the output relational columns, as shown in Figure 6.
Figure 6. Mapping XML elements to relational columns in VVM Studio
For creating the transformations, VVM provides the following options:
- Basic XML to tabular mapping
- This mapping flattens the XML documents into a collection of rows, where each row represents a node in the XML document. This type of transformation would rarely be used for production environments.
- XSLT transformation
- This mapping transforms the XML data into tabular form based on an underlying XSLT transform.
- Streaming transformation
- This mapping transforms streaming XML data into tabular form. This transformation does not require the entire XML document to be realized in memory. This makes it appropriate for use in situations where the XML file is extremely large.
- XQuery transformation
- This mapping transforms tabular data into XML form. This type is not appropriate for querying XML data sources.
The XSLT transformation and the streaming transformation are the ones that are used most commonly. XSLT transformation enables complex transformations by allowing you to directly edit the underlying XSLT. The output of the XSLT, however, needs to follow the structure shown in Listing 2.
Listing 2. Valid output format of XSLT transformation
<results> <result> <column_one>AAA</column_one> <column_two>BBB</column_two> <column_three>CCC</column_three> </result> <result> <column_one>DDD</column_one> <column_two>EEE</column_two> <column_three>FFF</column_three> </result> </results>
Streaming transformations work well in situations where the XML documents are extremely large. However, creating a customized transformation is not supported for streaming transformations.
The mapping and transformation options described in this section show the flexibility and scalability in querying XML data sources.
Step 3. Publish the transformation
Publishing is a process in which VVM makes a particular resource available to its clients for querying, such as IBM Cognos 8. For ODBC clients, resources are published as relational constructs, such as tables and stored procedures.
In this step, you publish the transformation you created in the previous step. You can publish the transformation either as a stored procedure or as a table. To publish the transformation as a table, you need to first embed the transformation inside an SQL view and then publish the view as a table, as shown in Figure 7.
Figure 7. Publishing in VVM
Exposing the transformation as a table by embedding it inside an SQL view is recommended.
After you publish, the resource is available as a table or as a stored procedure within a database under the Virtual View Manager Data Services node, as shown in Figure 7. The resource becomes available for the outside world to query using SQL over ODBC.
Step 4. Query the data source from IBM Cognos 8
After the XML data source is introspected, transformed, and published, it is ready for IBM Cognos 8 to query it. IBM Cognos 8 uses a data source of type IBM Cognos Virtual View Manager, which is based on an ODBC driver VVM provides, as shown in Figure 8.
Figure 8. IBM Cognos 8 data source wizard
You can find details regarding how IBM Cognos 8 handles ODBC connectivity with regards to VVM in the IBM Cognos 8 Administration and Security Guide (see Resources).
After you create a data source for VVM in IBM Cognos 8, the data source can be used in the IBM Cognos Framework Manager to import objects from VVM, such as tables and stored procedures, and to model query subjects on top of those objects. You can then publish these query subjects as a Cognos 8 package for the various reporting tools, such as the IBM Cognos Report Studio, to use.
Using the caching feature of VVM
VVM enables the caching of tabular resources, such as SQL views, and of procedural resources, such as transformations. Caching stores the results from these views or procedures in an intermediate storage that could be either a file or a relational database.
For an XML data source, each time you need data from the XML, VVM has to fetch the XML from its source, load it in memory, parse it, and then transform it into relational format. This can be very time-consuming if the XML file is large or if the source, such as the HTTP server, the Web Server, or the SOAP server, is slow due to response time or network latency. These factors can cause considerable performance bottlenecks when querying XML data sources without the VVM caching feature.
When you cache the XML transformation, you store the transformation output to a local file or to some relational database of choice. The XML is loaded, parsed, and transformed only when the cache is loaded or refreshed. At all other times, the data is fetched from the cache and not from the source XML.
VVM provides a very configurable mechanism for caching, including the following options:
- You can enable or disable the cache at any time.
- The storage for the cache can be either automatic, which stores the results to a file, or user-specified, in which case the end user can choose a supported database to store the cache results.
- The cache can be refreshed manually or it can be scheduled to refresh at any desired frequency. For example, if you know that your source XML changes every 45 minutes, you might schedule the cache to refresh once each hour.
- You can set an expiration time frame for a cache such that if the view or procedure being cached is accessed after the cache expires, a cache refresh is kicked off regardless of whether a cache refresh had been scheduled at that time.
Figure 9 shows the cache configuration options.
Figure 9. Cache configuration in VVM
Note that if the results from the cache are likely to be subjected to filtering or sorting, you should select a user-specified database as the storage option. The automatic cache-to-file storage option can result in performance issues because it does not have native support for filtering and sorting.
IBM Cognos Virtual View Manager provides the following:
- A range of options to connect to XML data sources.
- Data security by providing support for username and password authentication and for digital certificates.
- Scalability by providing caching. Caching relieves the load on the target data source, and it eliminates performance bottlenecks, which would exist if the XML were to be loaded, parsed, and transformed for each data access. Scalability is also provided with the capability to perform streaming transformations that can handle large XML documents without running out of memory.
- Flexibility and efficiency by providing sophisticated mechanisms to transform the hierarchical XML data into relational format. No plumbing code is needed for the transformation.
In summary, IBM Cognos 8 offers a secure, scalable, flexible, and efficient mechanism for querying and reporting on XML data sources using IBM Cognos Virtual View Manager.
- Read the IBM Cognos Virtual View Manager Installation and Configuration Guide for more information on how to create XML-type definition sets.
- Refer to the IBM Cognos 8 Administration and Security Guide for details about how IBM Cognos 8 handles ODBC connectivity with regards to VVM.
- See "IBM Cognos 8 - XML as a Data Source" (developerWorks, Feb 2010) for information on IBM Cognos 8 basic support for XML.
- In the XML area on developerWorks, get the resources you need to advance your XML skills, including DTDs, schemas, and XSLT.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Business analytics on developerWorks
Experiment with new directions in software development.
Tips for improving outcome and controlling risk.
Software development in the cloud. Register today and get free private projects through 2014.
Evaluate IBM software and solutions, and transform challenges into opportunities.