Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

model.xml documentation in cognos 8 with xslt

Posted on Wednesday 30 March 2011, 06:56 - Décisionnel - Permalink

In one of my recent projects, I had to build an automatic documentation builded on top of a database, using cognos 8 reporting itself.

This article covers the way I could extract technical informations using the Cognos Framework manager project model.xml file.

1- Build xsl files

The XSLT language is used to define the way an xml should be displayed. This means you can select any parts of teh xml, based on his structure and build the output you want.

Defining the stylesheet

Here is an example witch displays a list of Query Subjects extracted from your project :

[XML]
<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
  <html>
  <body>
	<h1>Query Subjects</h1>
	<table border="1">
			<tr>
				<td><h2>refObj</h2></td>
				<td><h2>Source Type</h2></td>
				<td><h2>Namespace</h2></td>
				<td><h2>Folder</h2></td>
				<td><h2>Query Subject</h2></td>
				<td><h2>dataSource</h2></td>
				<td><h2>sql</h2></td>
			</tr>
		<xsl:for-each select="//querySubject[@status = 'valid']">
			<tr>
				<td><p>[<xsl:value-of select="../../name"/>].[<xsl:value-of select="name"/>]</p></td>
				<td><p><xsl:value-of select="definition/dbQuery/tableType"/></p></td>
				<td><p><xsl:value-of select="../../name"/></p></td>
				<td><p><xsl:value-of select="../name"/></p></td>
				<td><p><xsl:value-of select="name"/></p></td>
				<td><p><xsl:value-of select="definition/dbQuery/sources/dataSourceRef"/></p></td>
				<td><p><xsl:value-of select="definition/dbQuery/sql"/></p></td>
			</tr>
		</xsl:for-each>
	</table>
  </body>
  </html>
 </xsl:template></xsl:stylesheet>

Applying the stylesheet

During the XSL development, we need to test the output of our xsl applyend to our model.xml "live". To do this, we will :

- Add the stylesheed definition to your local copy of model.xml, like that :

[XML]
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="modelspecs.xsl"?>
<project>

- Open your xml in Internet Explorer or any other modern navigator and see the output.

2- Automatic processing

For my specific need, I wanted to generate automatic data files from different project's model.xml, to be used as source for some DTS.

Here are four examples of XSL files extracting different informations. Of course, we can also build one single xml file containing all the informations :

- cubes definitions : This example outputs pure text to be used as a CVS source in SSIS.

[XML]
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:strip-space elements="*"/><xsl:output method="text"/><xsl:template match="/">refObj;Source type
 <xsl:for-each select="//namespace">
  <xsl:if test="string-length(property[@name = 'dynamicContent']) != 0">[<xsl:value-of select="name"/>];Cube
  </xsl:if>			
 </xsl:for-each>
</xsl:template></xsl:stylesheet>

- Query Subjects & SQL : This example outputs simple xml file to be used as a XML source in SSIS.

[XML]
<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" cdata-section-elements="sql"/>
 <xsl:template match="/">
	<xsl:element name="root">
	<xsl:for-each select="//querySubject[@status = 'valid']">
		<xsl:element name="querySubjects">
			<xsl:element name="refObj">[<xsl:value-of select="../../name"/>].[<xsl:value-of select="name"/>]</xsl:element>
			<xsl:element name="sourceType"><xsl:value-of select="definition/dbQuery/tableType"/></xsl:element>
			<xsl:element name="namespace"><xsl:value-of select="../../name"/></xsl:element>
			<xsl:element name="folder"><xsl:value-of select="../name"/></xsl:element>
			<xsl:element name="querySubject"><xsl:value-of select="name"/></xsl:element>
			<xsl:element name="dataSource"><xsl:value-of select="definition/dbQuery/sources/dataSourceRef"/></xsl:element>
			<xsl:element name="sql"><xsl:value-of disable-output-escaping="yes" select="definition/dbQuery/sql"/></xsl:element>
		</xsl:element>
	</xsl:for-each>
	</xsl:element>
 </xsl:template></xsl:stylesheet>

- Query Subjects & Query items detail : This example outputs simple xml file to be used as a XML source in SSIS.

[XML]
<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" cdata-section-elements="sql"/>
 <xsl:template match="/">
	<xsl:element name="root">
		<xsl:for-each select="//querySubject[@status = 'valid']/queryItem">
			<xsl:element name="QueryItems">
				<xsl:element name="refObj">[<xsl:value-of select="../../../name"/>].[<xsl:value-of select="../name"/>].[<xsl:value-of select="name"/>]</xsl:element>		
				<xsl:element name="parentRefObj">[<xsl:value-of select="../../../name"/>].[<xsl:value-of select="../name"/>]</xsl:element>		
				<xsl:element name="QueryItem"><xsl:value-of select="name"/></xsl:element>
			</xsl:element>
		</xsl:for-each>
	</xsl:element>
 </xsl:template></xsl:stylesheet>

- Framework Packages Definition and contents : This example outputs simple xml file to be used as a XML source in SSIS.

[XML]
<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" cdata-section-elements="sql"/>
 <xsl:template match="/">
	<xsl:element name="root"> 
		<xsl:for-each select="//securityView/definition/set/refobj">
			<xsl:element name="packages">	
				<xsl:element name="packageName"><xsl:value-of select="../../../name"/></xsl:element>		
				<xsl:element name="contentRefObj"><xsl:value-of select="."/></xsl:element>		
			</xsl:element>
		</xsl:for-each>
	</xsl:element>
 </xsl:template></xsl:stylesheet>

- Framework Dimensions Definitions and contents : This example outputs simple xml file to be used as a XML source in SSIS.

[XML]
<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" cdata-section-elements="sql"/>
 <xsl:template match="/">
	<xsl:element name="root"> 
		<xsl:for-each select="//dimension[@status = 'valid']">
			<xsl:element name="dimensions">
				<xsl:element name="namespace"><xsl:value-of select="../name"/></xsl:element>
				<xsl:element name="dimension"><xsl:value-of select="name"/></xsl:element>
				<xsl:element name="modelQuery"><xsl:value-of select="definition/modelQuery/sql"/></xsl:element>
				<xsl:for-each select="hierarchy">
					<xsl:element name="hierarchy">
						<xsl:element name="name"><xsl:value-of select="name"/></xsl:element>
						<xsl:for-each select=".//refobj">
							<xsl:element name="refobject"><xsl:value-of select="."/></xsl:element>
						</xsl:for-each>
					</xsl:element>
				</xsl:for-each>
			</xsl:element>
		</xsl:for-each>
	</xsl:element>
 </xsl:template></xsl:stylesheet>

Command line xsl transformation is possible using the xslproc program :

[script]
xsltproc mysxmltocsv.xsl model.xml > doc.csv
or 
xsltproc mysxmltoxml.xsl model.xml > doc.xml

the unix tool xmlproc is also available in the libxslt binary package for windows at following address : ftp://ftp.zlatkovic.com/libxml/

3- Conclusion

Hope it will help someone, it's quite hard to find informations on how to dicument Cognos. if anyone has anything to share, I am still interested ^^.

Jean-Baptiste Heren

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Comments are closed


You Might Also Like

result_crosstab.png

Cognos Report : Display a different measure in row for the aggregated column

Following tip is when you use a Relational Datamodel (DMR). In a Cognos Report crosstab, If You need to Display different measure in the aggregated column, you can do it using a simple if-then-else...

Continue reading

Cognos 8 - convert a member unique name from one hierarchy to another

If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once. You can convert the member unique name from one hierarchy to...

Continue reading