Blog pro de Jean-Baptiste HEREN

Notes d'un consultant freelance en informatique

Aller au contenu | Aller au menu | Aller à la recherche

model.xml documentation in cognos 8 with xslt

Publié le mercredi 30 mars 2011, 06:56 - Décisionnel - Lien permanent

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 ^^.

Article modifié le jeudi 07 avril 2011, 16:16

Jean-Baptiste HEREN

Auteur: Jean-Baptiste HEREN

Restez au courant de l'actualité et abonnez-vous au Flux RSS de cette catégorie

Les commentaires sont fermés


Voir aussi

Définir indépendamment, l’accès au détail des éléments d'un crosstab

Si vous avez ce besoin, voici un très bon tutoriel sur le site d'IBM. Pour résumer : Débloquer le contenu du tableau Dans les propriétés, changer la valeur "définir le contenu" à Yes Faire...

Lire la suite

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...

Lire la suite