Create business reports for XML data with Cognos 8 BI and DB2 pureXML
Two techniques to help you get started
During the past decade, dozens of industry groups have defined XML-based formats for representing and exchanging data. Examples of such formats include the Financial Products Mark-up Language (FpML) for over-the-counter derivatives trading, Health Level 7 (HL7) and Clinical Data Interchange Standards Consortium (CDISC) specifications for healthcare, Association for Cooperative Operations Research and Development (ACORD) specifications for insurance, Financial Information Exchange Markup Language (FIXML) for securities transactions, ISO 200022 (sometimes called âUNIFIâ) for banking payments, and Standards in Automotive Retail (STAR) for automotive manufacturing.
As a result, XML messages frequently contain important business and transactional data that organizations must store and manage to comply with regulatory bodies and internal standards. Furthermore, the content of these XML messages often provide important information about internal processes, operational status, and customer preferences. Consequently, querying and reporting on XML data has become increasingly important to many firms. This article explores two ways in which you can create business reports with Cognos 8 BI and XML data managed by DB2 pureXML.
XML data management options
Popular approaches for managing XML data include:
- Using character or binary large object (CLOB or BLOB) columns of tables to store the XML data intact in a relational database management system (DBMS).
- "Shredding" or decomposing the XML data into pieces and mapping these pieces to various columns of tables within a relational DBMS. (With this approach, the XML is not stored intact as XML.)
- Storing the XML in its native hierarchical format within a hybrid DBMS.
All three methods are viable, and all three carry various trade-offs, depending on the application requirements under consideration.
For example, large objects offer a simple data modeling approach that preserves the fidelity of the original XML data and allows entire XML documents or messages to be retrieved in a straightforward manner with simple SQL queries. However, storing XML as large objects makes it costly to search, update, and retrieve subsets of the XML data because the DBMS doesnât understand its internal structure.
Decomposing XML into multiple columns of one or more tables enables users to query and update the data without acquiring any specialized skills, such as knowledge of XML parsing or document navigation techniques. In addition, runtime query and update performance of specific portions of the decomposed XML data is generally predictable. That's because the XML data has essentially been converted into another form. But the decomposition (or shredding) process often involves complex, labor-intensive mappings that are difficult to evolve as XML messaging formats change over time. Furthermore, querying decomposed XML data can be challenging, often requiring multi-table joins.
Native XML storage implies storing XML data intact with full DBMS knowledge of the data's internal hierarchical structure. If such capability is built into a relational DBMS, firms can store, share, query, and update existing data stored in tables as well as XML data previously contained only within transient messages or flat files. Furthermore, labor-intensive document decomposition and reconstruction processes can be avoided. In addition, certain performance advantages and programming productivity enhancements are possible, thanks to greater DBMS knowledge of XML technology. However, this approach is supported by a limited number of DBMS products, such as IBM DB2 pureXML, and can require learning additional query language expressions beyond those supported by simple SQL.
DB2 pureXML overview
DB2 9.5 provides firms with a common application programming interface and database management platform for data modeled in tables as well as XML hierarchies. This architecture, pictured in Figure 1, enables DB2 to support all three XML storage options previously described. However, its pureXML technology — sometimes referred to as its native XML support — is increasingly being deployed at various firms. As such, it is the focus of this article.
Figure 1. DB2's architecture offers relational and XML data management support
While a full description of DB2's pureXML capabilities is beyond the scope of this article, it's worth nothing that DB2 can support queries written in the industry-standard SQL or XQuery languages (as well as "hybrid" queries that contain both SQL and XQuery expressions). Furthermore, for applications that need to perceive XML data as relational data, DB2 enables firms to create views that dynamically transform XML into result sets with traditional SQL data types. Indeed, this is one way in which Cognos users can work directly with DB2 pureXML data, as you'll see shortly. For more about DB2 pureXML, see the Related topics section of this article.
Cognos 8 BI overview
IBM Cognos 8 BI provides a wide range of reporting and analytic capabilities using a single, service-oriented architecture (SOA). Cognos 8 BI allows users to create and share reports that incorporate data across multiple enterprise sources, enabling them to make timely and well-informed business decisions. Cognos 8.3 BI is used for the tests in this article.
Cognos 8 BI features a three-tier architecture, as illustrated in Figure 2. The presentation tier delivers reports to a variety of platforms, the application tier contains services needed for report generation and management, and the data tier provides access to a wide range of data sources.
Figure 2. Three-tier architecture of Cognos 8 BI
Cognos 8 BI provides several key features for both business and IT users, including reporting, analysis, dashboards, score cards, and data modeling. Reporting enables users to work with a variety of pre-built report types that can be deployed over a range of data sources; a single metadata layer defines the information that can serve as the source of report data. Analysis enables the guided exploration of information that pertains to multiple dimensions of a business. Business dashboards communicate complex information quickly, translating data from various sources into visually rich presentations using gauges, maps, charts, and other graphical elements that show multiple results together. Score cards help businesses align teams and tactics with strategy, communicate goals consistently, and monitor performance against targets.
This article focuses on the use of the Cognos 8 BI Framework Manager for data modeling and Report Studio for report creation over DB2 pureXML data.
Use Cognos 8 BI with DB2 pureXML
Now explore two fundamental options for generating Cognos reports over data stored in DB2 pureXML. These include:
- Using reporting wizards with relational views of XML data
- Writing SQL-based queries over XML data
The first option minimizes the need for Cognos users to understand query language expressions and provides a simple, straightforward means to work with data stored as pureXML. As such, many firms favor this approach. However, this option presumes that a DB2 administrator can anticipate the XML data access needs of business users. Such knowledge is necessary to create the views upon which reports will be based. Furthermore, runtime performance concerns can arise, depending on the volume of XML data, the definition of the view, and the subsequent queries that are generated for the report.
The second option can be useful if the XML data that needs to be accessed is difficult to anticipate in advance. In addition, IT professionals who are already familiar with SQL/XML may find it convenient to write queries directly for complex scenarios. For example, they may wish to prototype complex queries and investigate their runtime performance characteristics using readily available DB2 tools (such as IBM Data Studio or the DB2 Control Center). When satisfied with their results, they can simply cut and paste the resulting SQL/XML statements into the appropriate Cognos tool.
Both techniques will be explored using the Cognos 8 BI Framework Manager for data modeling, Cognos 8 BI Report Studio for report creation, and DB2 9.5 pureXML for data management.
Overview of a sample database
To make these examples realistic, a sample database is used that contains FpML (derivatives trading) information based on the XML schema and sample data supplied by the International Swap and Derivatives Association (ISDA). IBM provides a number of free, industry-specific software bundles for DB2, including an FpML 4.2 bundle. The database created by this FpML bundle is used in this article. (To download this bundle, see the Download section. )
You'll learn how to create reports based on a single table that contains both relational and XML data. This table, FPMLADMIN.FPML, is defined as shown in Listing 1:
Listing 1. Definition of sample FPML table
create table fpmladmin.FPML( productID varchar(50), conversationID varchar(50), messageID varchar(50), document XML)
Note: The "document" column in this table is of type "XML." This causes DB2 to store XML data in its native hierarchical format.
FpML data is often heavily nested and can be rather complex. However, you don't need to be an expert to learn how to report on FpML data (or other types of XML data). The reports generated in this article are based on a certain type of FpML data — specifically, data related to credit derivatives — and are designed to work with only specific portions of FpML trade records for simplicity.
A sample FpML document is partially shown in Figure 3 using IBM Data Studio, a database development tool available for free. For more information on Data Studio, refer to the Download section of this article. A graphical representation of this same document is shown in Figure 4; some people find the latter easier to read. (You can also use the DB2 Control Center to display XML data stored in DB2.)
Figure 3. Partial FpML document stored in DB2 and displayed in Data Studio
Figure 4. Partial graphical (tree) view of an FpML document stored in DB2 and displayed with Data Studio
Report over relational views of XML data
As mentioned previously, DB2 enables firms to create relational views over hierarchical
data stored in XML columns. To do so, an administrator may write a SQL/XML query that
serves as the view definition. The query maps the output of one or more XPath expressions
to one or more columns in a relational result set. (The XPath expression must identify a
portion of the XML data — that is, a specific XML node — that returns one
data value.) The
XMLTable() function provides a straightforward way to do this.
If you're not already familiar with SQL/XML, XPath expressions, or
XMLTable(), see the Related topics section.
Step 1: Create the DB2 view
The statement below creates the FPMLADMIN.CREDITV view that is used in this article. This is a relational view that contains information about credit default swap derivatives that involve a failure to pay by some known entity. Such derivatives data is among the trade data captured in the FPMLADMIN.FPML table. Note that the CREDITV view contains columns representing the date of the trade, the entity referenced in the credit swap, the two parties involved in the swap, the amount of money involved if a "failure to pay" event occurs, and the currency of that money.
Listing 2. Definition of sample DB2 view
create view fpmladmin.creditv tradeDate, entityName, party1, party2, amount, currency) as select t.* from fpmladmin.fpml, xmltable(XMLNAMESPACES(DEFAULT 'http://www.fpml.org/2005/FpML-4-2'), '$DOCUMENT/FpML' columns tradeDate date path 'trade/tradeHeader/tradeDate', entityName varchar(80) path 'trade/creditDefaultSwap/generalTerms/referenceInformation/referenceEntity/entityName', party1 varchar(80) path 'party/partyName', party2 varchar(80) path 'party/partyName', amount varchar(30) path 'trade/creditDefaultSwap/protectionTerms/creditEvents/failureToPay /paymentRequirement/amount', currency varchar(10) path 'trade/creditDefaultSwap/protectionTerms/creditEvents/failureToPay /paymentRequirement/currency' ) as t where productid like 'credit%' and xmlexists ('declare default element namespace "http://www.fpml.org/2005/FpML-4-2"; $DOCUMENT/FpML/trade/creditDefaultSwap/protectionTerms /creditEvents/failureToPay/paymentRequirement') and xmlexists ('declare default element namespace "http://www.fpml.org/2005/FpML-4-2"; $DOCUMENT/FpML/trade/creditDefaultSwap/generalTerms /referenceInformation/referenceEntity/entityName')%
A script to create this view is provided in the Download section of this article.
Step 2: Define the Cognos 8 BI metadata for DB2
After installing and configuring the data modeling and reporting features of IBM Cognos 8 BI, perform the following steps to define the appropriate metadata for your work.
- Launch the Framework Manager tool.
- Select create a new project. Give the project a name, and click OK.
- Select a language, and click OK.
- For the metadata source, select Data Sources, and click Next.
- Click New to create a new data source. Click Next, and then provide a name for your data source (such as "FPML") and click Next.
- Specify DB2 as the data source type, and click Next.
- Enter the DB2 database name (FPML), and a valid user ID and password for the database.
- Test the connection to make sure you entered everything correctly. Once you have a successful connection, click Finish.
- From the Data Source window, select your newly created data source, and click Next.
- Select all the objects to import, click Next,, and click Import. Wait until all the objects have been successfully imported, and click Finish. You have now defined the appropriate metadata (or data model) for this project. At this point, you are brought to the Framework Manager main page (see Figure 5).
Figure 5. Cognos 8 Framework Manager main page
Step 3: Create a query subject
From the Framework Manager's main page, you are able to create a query subject to define the contents of your report. To do so, perform the following steps:
- Right-click on the FPML data model that you just created and select Create > Query Subject.
In the left pane, expand FPML and CREDITV. Double-click on all the columns and they
should appear on the right pane (see Figure 6). This creates a query that selects everything from the DB2 view (in SQL, this could be expressed as
SELECT * from FPMLADMIN.CREDITV).
- From the Test tab, select Test Sample. The query is executed, and you should see output similar to Figure 7.
- Select OK to save the query.
Figure 6. Query definition window
Figure 7. Query test window
It's worth noting that this example employs a very basic query. Using standard Cognos features, you can create a more sophisticated query over this view that filters out specific columns and rows of data, as desired.
Step 4: Create and publish a package
You're now ready to create and publish a package so that you can create a report in Report Studio.
- Launch the Framework Manager main page, if needed.
- At the bottom of the tree structure on the left, right-click Packages, and select Create > Package.
- Give the project a name, and click Next.
- Specify that the contents of the package will include the query you created, the FPML data model, and the CREDITV view. To do so, inspect the options provided in the list and ensure the appropriate items are selected. De-select everything else (see Figure 8). Click Next.
- Move all of the function sets except for DB2 to the left pane, and click Finish . When you are asked if you want to open the Publish Wizard, select Yes. Click Next, then click Next , and then click Finish. You need to publish a package to make the metadata available to the users who will then use the package in other Cognos tools to create reports.
Figure 8. Package definition window
At this point, the package has been created, and you're ready to use it to create a report.
Step 5: Create a report based on your package
This section shows you how to create a very simple list report. Once you have the metadata created in the Framework Manager and have published a package, you can use the data in much more complex and interesting ways.
To begin creating your report, you must launch the Cognos 8 BI Report Studio. In Windows, you can do so by selecting the Cognos 8 BI menu item from the Windows Start Menu. A welcome Web page is brought up in your Web browser (see Figure 9).
Figure 9. Cognos 8 BI Welcome Web page
To build your report, complete the following steps:
- Click Create professional reports to launch the Report Studio.
- Select Create a new report or template and specify that you want to create a report of type List.
- Expand your query in the tree structure in the left pane. Select all the columns and then drag them onto the main pane.
- Double-click on the title to give your report a new title.
- From the Menu Bar, select Run. Specify that you want to Run Report â HTML. You should see a report similar to that shown in Figure 10.
Fig 10. Report created in Report Studio
As mentioned previously, Cognos 8 BI Report Studio provides a variety of built-in report types, as well as the ability to customize the contents of any particular report as desired. Such capabilities can be applied to relational views, including views built on underlying pureXML data. Only a very simple reporting example has been included here. To learn more about Cognos 8 BI Report Studio capabilities, see the Related topics section.
Reporting over result sets returned by SQL/XML queries
For those situations where the use of views is inappropriate or not practical, firms may instruct Cognos to generate reports based directly on SQL/XML queries.
Since you are using keywords in your query that are not available in Cognos SQL, you must select the SQL type of Native or Pass-Through on the Query Options page. (You'll learn how to do this shortly.) Either will work, but Native is recommended because Framework Manager can automatically optimize for performance. No optimization is done when using Pass-Through. For more information on the use of SQL types, see the "Framework Manager User Guide," accessible through the Related topics section of this article.
Several of the initial tasks required to create a report based on a SQL/XML query are the same as those described in the previous example. In particular, you still need to create the Cognos 8 BI metadata for DB2. Assuming you've already done that, follow the steps outlined below to create a Cognos report using DB2 SQL/XML queries.
Step 1: Create a query
The query creation process for this example is somewhat different from the previous scenario, because you'll be manually entering your SQL/XML query rather than relying on Cognos to generate a query behind the scenes on your behalf. As a result, you need to set a database property to govern how the query will be processed internally. Then, you can input the query itself. The following steps guides you through both tasks:
- From the Cognos 8 BI Framework Manager, expand the Data Sources in the tree structure in the left pane, and select your DB2 data source.
- Inspect the properties pane on the bottom of the Framework Manager window, and change the Query Processing parameter to Limited Local.
- Now create another query. Right-click on the data model in the tree structure and select Create > Query Subject.
- Select Data Source (Manually build a SQL query), give the query a name, and click OK.
- Select your data source and un-check Run database query subject wizard, and click Finish
- Under the SQL tab, manually enter the SQL/XML query desired, as shown in Figure 11.
Figure 11. Query definition window
Now you're ready to test the query that you've just defined, by completing the following steps:
- In the lower right of the Test tab, click Options. For the Type field, select Native, and click OK (see Figure 12).
- Click Test Sample, and your query results should be displayed (see Figure 13).
Figure 12. Query options window
Fig 13. Query test results window
At this point, you may be wondering what type of query you should enter. For the work shown in Figure 11, the query below was used, which is included in a script that you can find in the Download section of this article. The query is somewhat complex but provides a realistic example of how someone can return multiple portions of XML data (for example, multiple XML nodes) and restrict results based on an XML predicate. In particular, this query returns the text values of the trade IDs associated with all credit default swap derivatives in which "Amcor Ltd" or "Abbey National PLC" are referenced entities.
Listing 3. Sample SQL/XML query
select xmlserialize(content xmlquery('declare default element namespace "http://www.fpml.org/2005/FpML-4-2"; $fpml/FpML/trade/tradeHeader/partyTradeIdentifier/tradeId/text()' passing document as "fpml") as varchar(25) ) tradeID1, xmlserialize(content xmlquery('declare default element namespace "http://www.fpml.org/2005/FpML-4-2"; $fpml/FpML/trade/tradeHeader/partyTradeIdentifier/tradeId/text()' passing document as "fpml") as varchar(25) ) tradeID2 from fpmladmin.fpml where xmlexists('declare default element namespace "http://www.fpml.org/2005/FpML-4-2"; $fpml/FpML/trade/creditDefaultSwap/generalTerms /referenceInformation/referenceEntity[entityName="Amcor Ltd" or "Abbey National PLC"]' passing document as "fpml" )
Note: The first line of this query invokes the
XMLSerialize() function, which instructs DB2 to return a serialized string version of the XML data. Some tools, such as Cognos 8 BI, require XML to be serialized for processing. (For more information about XML serialization, see the
Related topics section.)
Step 2: Create and publish a package
With your query created, your next step is to create and publish a package following the same basic process described in the Create and publish a package step from the earlier section.
- Launch the Framework Manager main page, if needed.
- At the bottom of the tree structure on the left, right-click Packages and select Create > Package.
- Give the project a name, and click Next.
- Specify that the contents of the package will include the query you created and the FPML data model. To do so, inspect the options provided in the list and ensure the appropriate items are selected. De-select everything else. Click Next.
- Move all of the function sets except for DB2 to the left pane, and click Finish. When you are asked if you want to open the Publish Wizard, select Yes. Click Next, then click Next, and then click Finish. Now that your packaged is published, you will have access to it in other Cognos 8 BI tools.
Step 3: Create a report
- Launch the Report Studio.
- Click the package that you created.
- Select Create a new report or template, and specify that you want to create a report of type List
- Expand your query in the tree structure in the left pane. Select all the columns, and then drag them onto the main pane.
- Double-click on the title to give your report a new title.
- From the menu bar, select Run. Specify that you want to Run Report â HTML. You should see a report similar to that shown in Figure 14.
Figure 14. Cognos 8 BI report from SQL/XML query
Creating business reports based on XML data can be done in a variety of ways using popular software offerings. In this article, you learned two techniques for using Cognos 8 BI to create reports over DB2 pureXML data. These include the use of views and the execution of SQL/XML queries. To learn more about Cognos 8 BI or DB2 pureXML, review the materials cited in the Resources section.
The authors would like to thank Nigel Campbell and Matthias Nicola for their review of this article.
- "DB2 9 pureXML: Overview and Fast Start": Get a free book that introduces DB2 pureXML and its support for SQL/XML and XQuery, by Cynthia M. Saracco, Donald Chamberlin, and Rav Ahuja.
- "Best Practices â Managing XML Data": Read about DB2 pureXML best practices.
- XMLTABLE by example series: Explore the XMLTable() function supported by DB2 pureXML, in this two-part series.
- DB2 pureXML Wiki: Visit to find links to more articles, success stories, news items, and demos for DB2 pureXML.
- DB2 Information Center: Find complete DB2 pureXML information.
- IBM Alphaworks: Access live industry-specific software demos for DB2 pureXML.
- Download industry-specific software bundles for DB2 pureXML, including the FpML bundle used in this article. (FpML specifications are available from the International Swaps and Derivatives Association.)
- Explore resources available through Cognos Services and Support.
- DB2 FpML bundle
- DB2 9.5 Express-C
- IBM Data Studio