Querying and reporting on XML data sources in IBM Cognos 8 using IBM Cognos Virtual View Manager

XML is increasingly becoming common as a means for information exchange. A web service application is an example of the type of application that uses XML to exchange information. Any business intelligence product or solution must have sufficient capability to query information that could be in the form of XML. Learn how IBM Cognos® 8 delivers a comprehensive, flexible, secure, and scalable solution to query and report on XML data sources, including web services, by leveraging the IBM Cognos Virtual View Manager.

Share:

Abhay Sadadekar (asadadek@in.ibm.com), Senior Staff Software Engineer, IBM

Photo of Abhay SadadekarAbhay Sadadekar is a Senior Staff Software Engineer with IBM India Software Labs. He has been a member of the IBM Cognos Virtual View Manager development team since 2008. He has been involved in the development of various enterprise grade applications, primarily in the domain of Business Intelligence and Corporate Performance Management.



12 August 2010

Also available in Chinese Spanish

Introduction

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.

Frequently used acronyms

  • VVM - IBM Cognos Virtual View Manager
  • ODBC - Open Database Connectivity
  • GUI - Graphical User Interface
  • HTTP - Hypertext Transfer Protocol
  • WSDL - Web Services Description Language
  • SOAP - Simple Object Access Protocol
  • URL - Universal Resource Locator

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
Shows data coming from many data sources to VVM, and output via JDBC to a repository

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:

File-XML
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.
XML-HTTP
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.
WSDL
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:

  1. Step 1. Create an XML data source in VVM
  2. Step 2. Transform the XML
  3. Step 3. Publish the transformation
  4. 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
Screen cap: Local File System selected, Character set with Auto Detect chosen, File Name Filter is *.xml
  • 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
    Screen cap: Method is POST, Pass-through Login is Disabled, and No Input is selected
  • 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
    Screen cap: Pass-through Login is disabled

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
Shows Contacts structure composed of person, which is composed of name, age, and Country

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
Screen cap: Result table shows that Rob is 24 in the USA and John is 34 in Canada

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
Screen cap: XML View with embedded XML_Transform

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
Screen cap: New Data Source Wizard selecting IBM Cognos Virtual View Manager (ODBC) for the connection

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
Screen cap: Cache Configuration with Automatic, Manual, and Never Expire selected; Cache clears when user clears it manually

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.


Conclusion

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.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management, XML
ArticleID=506878
ArticleTitle=Querying and reporting on XML data sources in IBM Cognos 8 using IBM Cognos Virtual View Manager
publish-date=08122010