Blog pro de Jean-Baptiste HEREN - Décisionnel

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

Jean-Baptiste HEREN — Mon, 30 Jan 2012 19:43:00 +0100

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

Explanation: You know if you are on a total column when the current context gives you more than one value for the year Axis.

isTotalYear Expression:

[script]
if(count(distinct [year]) > 1) then(1) else (0)

Conditional Expression:

[script]
if(count(distinct [year]) > 1) then([PackName].[Measures].[M1]) else ([PackName].[Measures].[M2])

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

Jean-Baptiste HEREN — Wed, 06 Apr 2011 15:45:00 +0200

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 another, using the cognos substitute macro.

Consider the following example :

You prompt on level value using the following hierarchy :

[SQL]
[First package].[First dimension].[First hierarchy].[First Level]

Then you want to filter one Query built on another package using the same prompt value :

[SQL]
[Second package].[Second dimension].[Second hierarchy].[Second Level]

It means yo uwant to convert :

[SQL]
 -- Source hierarchy
[First package].[First dimension].[First hierarchy].[First Level]->:[YK].[[First dimension]].[First hierarchy]].[First Level]].&[00004]]]

-- to Destination Hierarchy
[Second package].[Second dimension].[Second hierarchy].[Second Level]->:[YK].[[Second dimension]].[Second hierarchy]].[Second Level]].&[00004]]]

So you can use a Query element containing a substitute macro :

[SQL]
-- Formula
# sq(substitute ('First package','Second package', 
 substitute ('First hierarchy','Second hierarchy',
 substitute ('First dimension','Second dimension',
 substitute ('First Level','Second Level',
 prompt('First Level Prompt Name', 'token')))))) #

model.xml documentation in cognos 8 with xslt

Jean-Baptiste HEREN — Wed, 30 Mar 2011 06:56:00 +0200

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

Generate Extended properties in MS Sql Server 2005+

Jean-Baptiste HEREN — Mon, 28 Mar 2011 23:02:00 +0200

When you work with databases, it's cool to Keep all the documentation in the system, so that it is available for reporting and all kind of analysis.

Microsoft SQL Server 2005+ provides the ability to create Extended properties on tables (or even fields). Those properties are available using SQL management studio, on the tables properties panel.

Here I will explain how you can generate them fast using Excel and macro AND Extract them using SQL.

1- Generate SQL Script to insert properties and values from an Excel sheet

Usually, for any initial documentation, it will be easyer to fill an excel sheet and generate allk the indidual properties automatically. You don't want to spend a week filling all the stuff manually(!). You will find as an attachment to this post, an Excel demonstration excel file containing a simple sheet template and a macro. The maco will gerenate SQL instructions like this :

[SQL]
EXEC sys.sp_addextendedproperty @name=N'MY_PROPERTY_NAME', @value=N'MY_PROPERTY_VALUE', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MY_TABLE_NAME';

Here is the SQL macro code

[VB]
Sub generateSQLSpecialProperties()
  ' Author: Jean-Baptiste Heren - 2011-03-29 - http://blog.jbheren.com
  ' Loop on current scheet and generate sql sys.sp_addextendedproperty based on sheet contents
  ' First column contains the table name
  ' first line contains the Properties names ex: Table_name | prop_description | prop_rule ...
  
  Dim tableName As String 'tablename extracted from first row of each column
  Dim filePath As String
  Dim slashPosition As Integer
  Dim pathOnly As String
  
  Dim dataSQL As String    ' will contain the SQL Contents
  Dim separator As String
  
  Dim myRow As Integer 'Row counter
  Dim myCol As Integer 'Column counter
  
  ' get current excel file Path
  filePath = ThisWorkbook.FullName
  slashPosition = InStrRev(filePath, "\")
  pathOnly = Left(filePath, slashPosition)
  
  MyFile = pathOnly + ActiveSheet.Name + "_macro_generated.sql"
  
  'get column values from second row
  myRow = 2
  myCol = 2
  
  'set and open file for output
  fnum = FreeFile()
  Open MyFile For Output As fnum
  
  
  Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on rows until blank.
        
      tableName = Trim(CStr(ActiveSheet.Cells(myRow, 1))) 'get Table name from first column
      
      Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on columns until blank.
        property_name = Trim(CStr(ActiveSheet.Cells(1, myCol))) 'get Property name from first column
        property_descr = Replace(Trim(CStr(ActiveSheet.Cells(myRow, myCol))), "'", "''")
        dataSQL = "EXEC sys.sp_addextendedproperty @name=N'" + property_name + "'"
        dataSQL = dataSQL + ", @value=N'" + property_descr + "'"
        dataSQL = dataSQL + ", @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE'"
        dataSQL = dataSQL + ",@level1name=N'" + tableName + "';"
        myCol = myCol + 1
      Loop
    
    myCol = 2 ' Return to specifiedcolumn
    myRow = myRow + 1 ' Move to next row
    
    Print #fnum, dataSQL
  Loop
  
  ' Close the file
  Close #fnum
  
End Sub

2- Extract table properties from SQL Sercer System tables

[SQL]
/* get extended properties on Tables */
SELECT 
  A.NAME as table_name
 ,B.name as property_name
 ,B.value as property_value
FROM SYSOBJECTS A INNER JOIN sys.extended_properties B on A.id = B.major_id
WHERE A.type = 'U'

Cognos Content store Tables Description

Jean-Baptiste HEREN — Mon, 28 Mar 2011 22:57:00 +0200

Here is the list of Cognos Content store tables with Description. Those informations come from an old document from Cognos so it may not be totally up to date.

Table name	Descrition
CMSYSPROPS	This table has the Content Store Version.
CMOBJNAMES	This table has the names of all the objects in the content store.
CMOBJPROPS1	Users, Roles \ Group Distribution list and contact information such as Email, phone number, Fax Given name etc are stored in this table
CMOBJPROPS2	Report scheduling information is stored. This table has fields like hour, day week etc
CMOBJPROPS3	Stores Screen Tip and Object description provided while creating the objects are stored here
CMOBJPROPS4	Stores the printer paper setting details like height and width of A3 , A4 , letter and 11X17 paper orientations
CMOBJPROPS6	Has the details of the all packages which were published using the FM.
CMOBJPROPS7	This table stores the XML of all reports and models. This is basically to maintain the metadata about the structure of the reports and models.
CMOBJPROPS10	Contact information is stored in this table. This table has columns like Contact Email and Contact.
CMOBJPROPS11	This table stores the data sources configuration details like connect string, the cube location etc.
CMOBJPROPS13	It stores the names of parameter passed to the range prompts
CMOBJPROPS14	Has details regarding the versions, creation time etc of the objects in the content store.
CMOBJPROPS16	This table provides the status of the multiple services of Cognos like LogService, MonitorService, ReportService, SystemService, JobService
CMOBJPROPS17	Stores performance details of each component like “query Studio, Analysis Studio, Event Studio” etc
CMOBJPROPS18	This table stores the drill path from the source to the final target report.
CMOBJPROPS20	Stores details regarding which are all the reports for which prompting has been enabled. And also has the details of which are the reports which has the default report options overridden.
CMOBJPROPS24	Stores the printer configuration details
CMOBJPROPS25	Stores the data regarding the objects deployed, like the deployed folder, the reports, the number of folders present in the deployment archive, etc
CMOBJPROPS26	This table stores the data about all the packages imported / exported in C8, with the properties selected during the process.
CMOBJPROPS27	Has the details regarding the data source created in the content store using Cubes.
CMOBJPROPS30	Stores the registration , service description etc details about portlets in this table
CMOBJPROPS31	Has the custom logging level for each of the Cognos services, CMID can be be linked to CMOBJNAMES for the names of each of the services
CMOBJPROPS32	Has the details of the stored procedures used as the query items in the FM model.
CMOBJPROPS33	Detail related to users, user groups user roles.
CMOBJPROPS34	Has the details regarding the drill through parameters of the drill through reports. The parameter assign values are present in coded format
CMOBJPROPS36	Has the list of all the Models published using framework manager
CMOBJPROPS37	This table has the details of the routing sets configured for server / load balancing for each package published in the content store
CMOBJPROPS38	Configuration details about number of items to retrieve in studios, for a package are saved under this table.
CMOBJPROPS39	Has values for properties of reports and views. PROPID can be linked to CMPROPERTIES for property names
CMOBJPROPS52	Contains the properties for the connections
CMOBJPROPS55	Has the URI for icons for each entry in Cognos Connection
CMLOCALES	Has the locale ids associated with each language supported by Cognos 8

Extract Cognos reports & packages list with path

Jean-Baptiste HEREN — Mon, 28 Mar 2011 22:33:00 +0200

When you want to make a documentation on Cognos (or anything), it's hard to keep it updated...

One of my recent projects has been to buid an automatic documentation on Cognos, showing dependencies between packages, reports and even source tables or ETL packages. Here I share with you, some SQL scripts extracting information from the Cognos Content Store. Hope it will be helpful.

[SQL]
/* Author: Jean-Baptiste HEREN - 2010-07-06      */
/* Descr: extract Report model XML from database */
select names.name, specs.spec
from CMOBJNAMES names
     inner join CMOBJPROPS7 specs 
     on names.CMID = specs.CMID 
where names.name LIKE '%Audit%'

/* Author: Jean-Baptiste HEREN - 2010-07-06      */
/* Descr: extract packages list */
SELECT DISTINCT
       cmobjects.cmid,
       cmobjnames.name as package_name
FROM   cmrefnoord1
       INNER JOIN cmobjects
         ON cmrefnoord1.refcmid = cmobjects.cmid
       INNER JOIN cmobjnames
         ON cmobjects.cmid = cmobjnames.cmid
WHERE  cmrefnoord1.propid = 31
       AND cmobjnames.localeid IN ( '24', '100')

/* Author: Jean-Baptiste HEREN - 2011-03-15 */
/* Descr: - Report names, full Paths & related package name from cognoscs database */
/* CMOBJPROPS25 - Stores the data regarding the objects deployed, like the deployed folder, the reports, the number of folders present in the deployment archive, etc*/
SELECT DISTINCT rank() OVER(ORDER BY package_name,NAME,max(Path) ASC) as rank, NAME as report_name, package_name, RTRIM(max(Path)) as path, cast(storeID as nvarchar) as store_id
FROM (
SELECT A.CMID, L1.mapdlocaleid, L1.locale, L1.TYPE ,L1.NAME /*, L9.NAME, L8.NAME, L7.NAME, L6.NAME , L5.NAME, L4.NAME  , L3.NAME  , L2.NAME*/
, LTRIM(SUBSTRING(
  CASE WHEN isnull(L10.NAME,'') = '/' THEN ' ' ELSE isnull(L10.NAME,'') END + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L9.NAME,'')  = '/' THEN ' ' ELSE isnull(L9.NAME,'')  END + CASE WHEN L9.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L8.NAME,'')  = '/' THEN ' ' ELSE isnull(L8.NAME,'')  END + CASE WHEN L8.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L7.NAME,'')  = '/' THEN ' ' ELSE isnull(L7.NAME,'')  END + CASE WHEN L7.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L6.NAME,'')  = '/' THEN ' ' ELSE isnull(L6.NAME,'')  END + CASE WHEN L6.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L5.NAME,'')  = '/' THEN ' ' ELSE isnull(L5.NAME,'')  END + CASE WHEN L5.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L4.NAME,'')  = '/' THEN ' ' ELSE isnull(L4.NAME,'')  END + CASE WHEN L4.NAME  = '/' THEN '' ELSE '/' END
+ CASE WHEN isnull(L3.NAME,'')  = '/' THEN ' ' ELSE isnull(L3.NAME,'')  END + CASE WHEN L3.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME  = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
  , CHARINDEX('Public',
  isnull(L10.NAME,'') + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ isnull(L9.NAME ,'') + CASE WHEN L9.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L8.NAME ,'') + CASE WHEN L8.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L7.NAME ,'') + CASE WHEN L7.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L6.NAME ,'') + CASE WHEN L6.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L5.NAME ,'') + CASE WHEN L5.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L4.NAME ,'') + CASE WHEN L4.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L3.NAME ,'') + CASE WHEN L3.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME  = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
), LEN(
  isnull(L10.NAME,'') + CASE WHEN L10.NAME = '/' THEN '' ELSE '/' END
+ isnull(L9.NAME ,'') + CASE WHEN L9.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L8.NAME ,'') + CASE WHEN L8.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L7.NAME ,'') + CASE WHEN L7.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L6.NAME ,'') + CASE WHEN L6.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L5.NAME ,'') + CASE WHEN L5.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L4.NAME ,'') + CASE WHEN L4.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L3.NAME ,'') + CASE WHEN L3.NAME  = '/' THEN '' ELSE '/' END
+ isnull(L2.NAME ,'') + CASE WHEN L2.NAME  = '/' THEN '' ELSE '/' END
--+ isnull(L1.NAME ,'')
))
  ) as Path
FROM CMOBJECTS A
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmclasses.name in('report')
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L1 ON A.cmid = L1.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L2 ON L1.pcmid = L2.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L3 ON L2.pcmid = L3.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L4 ON L3.pcmid = L4.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L5 ON L4.pcmid = L5.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
        	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L6 ON L5.pcmid = L6.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L7 ON L6.pcmid = L7.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L8 ON L7.pcmid = L8.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L9 ON L8.pcmid = L9.cmid
INNER JOIN (
	SELECT cmobjnames.cmid, 
	cmstoreids.storeid,
	cmobjects.pcmid, 
	cmobjnames.name, 	
    cmobjnames.mapdlocaleid,
    cmobjnames.localeid,
    cmlocales.locale,
	cmclasses.name as type
	     FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
	    WHERE
	     	 cmobjects.cmid = cmobjnames.cmid
	    AND cmobjects.classid = cmclasses.classid
	    AND cmobjects.cmid = cmstoreids.cmid
        AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('fr','en')
) L10 ON L9.pcmid = L10.cmid
WHERE CLASSID IN (10,18) -- reports & packages
--  AND DISABLED is NULL
) Z INNER JOIN cmstoreids on Z.CMID = cmstoreids.CMID
    INNER JOIN (SELECT cmrefnoord1.cmid AS pid,
       cmobjnames.name as package_name
FROM   cmrefnoord1
       INNER JOIN cmobjects
         ON cmrefnoord1.refcmid = cmobjects.cmid
       INNER JOIN cmobjnames
         ON cmobjects.cmid = cmobjnames.cmid
WHERE  cmrefnoord1.propid = 31
       AND cmobjnames.localeid IN ( '24', '100')
     ) PKG on Z.CMID = PKG.PID
--WHERE Path like '%/REPORTING/%'
group by NAME, package_name, storeID

Analysis Services 2005 : L'erreur système suivante s'est produite : .

Jean-Baptiste HEREN — Mon, 24 Jan 2011 16:39:00 +0100

Voici une astuce pour SSAS sous SQL Server 2005. Si vous rencontrez l'erreur :

[script]
L'erreur système suivante s'est produite : .

Cette erreur est liée à la présence au niveau de l'onglet Membership des rôles Analysis Services, de membres n'existant plus dans Active Directory.

Il suffit donc nettoyer vos rôles :)

Installer JasperServer sur Linux Debian

Jean-Baptiste HEREN — Sat, 15 Jan 2011 12:15:00 +0100

JasperServer est la solution open source de BI & reporting la plus aboutie à ce jour. Cette solution comprend l'ensemble des outils nécessaire à la mise en place d'une solution décisionnelle professionnelle.

Nous allons voir ici quelles sont les étapes pour installer jasper server manuellement. Ceci est utile en particulier si vous disposez déjà d'un mysql et/ou d'un tomcat installés sur votre machine.

1- Téléchargement du paquet "JasperServer WAR install"

Ce paquet contient le portail Jasper, mysql et l'outil de développement de rapports. rendez-vous chez jasperForge ou directement sur sourceforge.

Connectez vous en tant qu'administrateur (root)

[script]
# unzip ./jasperserver-ce-3.7.1.bin.zip
# cd jasperserver-ce-3.7.1.bin/
# ls
apache-ant   jasperserver.war  samples
buildomatic  license.txt       scripts
docs	     releaseNotes.txt  Third-Party-Notices.pdf

2- Installation du serveur d'application Web

Ici nous installerons Tomcat5, il est également possible de réaliser l'installation avec JBoss, par exemple.

[script]
# apt-get install sun-java6-jdk tomcat5.5 tomcat5.5-admin tomcat5.5-webapps

Si l'installation s'est bien déroulée, une connexion du navigateur web à l'adresse http://localhost:8180/ devrait vous afficher ceci :

Il nous faut maintenant configurer un compte admin pour tomcat

[script]
# vim /var/lib/tomcat5.5/conf/tomcat-users.xml

Modifiez le mot de passe par défaut de tomcat et ajoutez les rôles admin & manager.

[xml]
<?xml version='1.0' encoding='utf-8'?>
<tomcat-users>
  <role rolename="tomcat"/>
  <role rolename="manager"/>
  <role rolename="admin"/>
  <user username="tomcat" password="tomcat" roles="tomcat,admin,manager"/>
</tomcat-users>

Modifier les paramètres de la VM pour jasperserver. En effet, les paramètres par défaut ne sont pas suffisants...

[script]
# vim /usr/share/tomcat5.5/bin/setclasspath.sh

Puis on ajoute la variable JAVA_OPTS, juste aprés le JAVA_ENDORSED_DIRS.

[script]
# Set the default -Djava.endorsed.dirs argument
JAVA_ENDORSED_DIRS="$BASEDIR"/common/endorsed

# JBH - Parameters for JasperServer to work fine
export JAVA_OPTS="$JAVA_OPTS -Xms128m -Xmx512m -XX:PermSize=32m -XX:MaxPermSize=128m -Xss2m -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -XX:+CMSPermGenSweepingEnabled"

Copier le connecteur jdbc mysql depuis le dossier d'installation

[scripts]
 cp scripts/drivers/mysql/jdbc/mysql-connector-java-5.1.10.jar /usr/share/tomcat5.5/common/lib/

Redémarrer le server afin de prendre en compte nos modifications

[script]
# /etc/init.d/tomcat5.5 restart
Stopping Tomcat servlet engine: tomcat5.5.
Starting Tomcat servlet engine: tomcat5.5.

Nous pouvons maintenant nous connecter au manager Tomcat en utilisant les login/pass de l'utilisateur tomcat. Pour cela, rendez-vous à l'adresse http://localhost:8180/manager/html

3- préparation de mysql avec buildomatic

[script]
# cd buildomatic
# cp sample_conf/inst-mysql_master.properties default_master.properties
# /etc/init.d/tomcat stop
# vim default_master.properties

Changes configuration values to :

[script]
# application server type (default is tomcat6)
appServerType = tomcat5

# Tomcat app server root dir
appServerDir = /var/lib/tomcat5.5/

# database type
dbType=mysql

# database location and connection settings
dbHost=localhost
dbUsername=root
dbPassword=password #set your own password

Nous allons maintenant regénérer les paramètres de buildomatic.

[script]
 ./js-ant clean-config
./js-ant gen-config

Nous allons maintenant exécuter les tâches de création de la base jasperserver

[script]
debianlamp:/home/jb/jasper/jasperserver-ce-3.7.1-bin/buildomatic# ./js-ant create-js-db
Buildfile: build.xml

create-js-db:
     [echo] create database, js.dbName = jasperserver
      [sql] Executing commands
      [sql] 1 rows affected
      [sql] 1 of 1 SQL statements executed successfully

BUILD SUCCESSFUL
Total time: 3 seconds

Les commandes suivantes créent le paramêtrage par défaut (users)

[script]
# ./js-ant init-js-db-ce 
# ./js-ant import-minimal-ce

En option, la création et le chargement des bases de démonstration

[script]
# ./js-ant create-sugarcrm-db
# ./js-ant create-foodmart-db
# ./js-ant load-sugarcrm-db
# ./js-ant load-foodmart-db
# ./js-ant update-foodmart-db
# ./js-ant import-sample-data-ce

Puis le déploiement vers tomcat (le serveur doit être inactif).

[script]
 # ./js-ant deploy-webapp-ce
Buildfile: build.xml

init-source-paths:
     [echo]  
     [echo] Property values (in dev.xml: init-source-paths): 
     [echo]   js-path = ${js-base-path}
     [echo]   js-pro-path = ${js-pro-base-path}
     [echo]   repo-path = ${repo-path}
     [echo]  

set-ce-webapp-name:
     [copy] Copying 1 file to /home/jb/jasper/jasperserver-ce-3.7.1-bin/buildomatic/build_conf/default
     [copy] Copying 1 file to /home/jb/jasper/jasperserver-ce-3.7.1-bin/buildomatic/build_conf/default/webapp/WEB-INF

deploy-jdbc-jar:
     [copy] Copying 1 file to /var/lib/tomcat5.5/common/lib

deploy-webapp:
     [echo]  
     [echo] Property values (in install.xml: deploy-webapp): 
     [echo]  jsEdition = ce
     [echo]  warFileDistSourceDir = /home/jb/jasper/jasperserver-ce-3.7.1-bin/buildomatic/../jasperserver.war
     [echo]  warTargetDir = /var/lib/tomcat5.5/webapps/jasperserver
     [echo]  webAppName = jasperserver
     [echo]  webAppNameCE = jasperserver
     [echo]  webAppNamePro = jasperserver-pro
     [echo]  webAppNameSrc = jasperserver 
     [echo]  webAppNameDel = jasperserver
     [echo]  warTargetDirDel = /var/lib/tomcat5.5/webapps/jasperserver
     [echo]  
     [copy] Copying 1013 resources to /var/lib/tomcat5.5/webapps/jasperserver
     [copy] Copying 4 files to /var/lib/tomcat5.5/webapps/jasperserver
   [delete] Deleting directory /var/lib/tomcat5.5/work/Catalina/localhost/jasperserver

deploy-webapp-ce:

BUILD SUCCESSFUL
Total time: 25 seconds

Réglages de Tomcat

Lien symbolique vers la config de jasperserver dans /etc

[script]
cd /etc/tomcat5.5/Catalina/localhost
ln -s /usr/share/tomcat5.5/webapps/jasperserver/META-INF/context.xml ./jasperserver.xml

Ajout des policies pour jasperserver

[script]
# vi /etc/tomcat5.5/policy.d/04webapps.policy

On ajoute la partie suivante dans la section grant codeBase "file:${catalina.home}/bin/tomcat-juli.jar" {

[script]
   permission java.io.FilePermission "/usr/share/tomcat5.5/webapps/jasperserver/WEB-INF/classes/logging.properties", "read";
//

Il faut editer ensuite le fichier
///[script]
# vi /etc/tomcat5.5/policy.d/50user.policy

Ajouter la définition suivante :

[script]
grant codeBase "file:/usr/share/tomcat5.5/webapps/jasperserver/-" {
    permission java.security.AllPermission;
};

Mise en place des logs dans le dossier /var/log/tomcat5.5/

[scripts]
 vim /var/lib/tomcat5.5/webapps/jasperserver/WEB-INF/log4j.properties

[sript]
...
log4j.appender.fileout.File=/var/log/tomcat5.5/jasperserver.log
...
log4j.appender.jasperanalysis.File=/var/log/tomcat5.5/jasperanalysis.log

4- Démarrage de JasperServer

Nous pouvons maintenant démarrer tomcat

[script]
# /etc/init.d/tomcat5.5 start

Puis nous connecter à jasperserver à l'adresse http://localhost:8180/jasperserver/, si tout s'est bien passé, vous devriez voir la fenêtre de connexion.

les login par défaut de l'administrateur sont jasperadmin/jasperadmin.

Problèmes éventuels

Si l'application n'a pas démarré, vous pouvez consulter, en autres la log jasperserver configurée plus haut:

[script]
cat /var/log/tomcat5.5/jasperserver.log

Problèmes de sécurité dans les logs Cantala.

Sous Debian, TOMCAT peut poser un problème de sécurité, même aprés l'ajout des policies plus haut, il est possible qu'un problème subsiste. Si c'est le cas, vous pouvez désactiver le JSM (Java Security Manager) en modifiant la valeur TOMCAT5_SECURITY dans le fichier suivant :

[script]
# vim /etc/default/tomcat5.5

Modifiez la valeur du TOMCAT5_SECURITY comme suit :

[script]
# Use the Java security manager? (yes/no, default: yes)
# WARNING: Do not disable the security manager unless you understand
# the consequences!
# NOTE: java-gcj-compat-dev currently doesn't support a security
# manager.
TOMCAT5_SECURITY=no

Cognos Report Studio : position d'un membre dans une hierarchie

Jean-Baptiste HEREN — Wed, 03 Nov 2010 18:02:00 +0100

L'expression suivante renvoie niveau de profondeur du membre spécifié dans une hiérarchie.

[script]
ordinal (level(currentMember([My_Package].[My_Dimension].[My_Hierarchy])))

Formules MDX - Associer deux dimentions temporelles avec LINKMEMBER

Jean-Baptiste HEREN — Wed, 08 Sep 2010 22:00:00 +0200

Voici un exemple d'utilisation de la fonction LINKMEMBER pour le calcul d'une mesure dans un cube Miscosoft SSAS.

Présentation

Cet exemple est extrait d'un travail sur le thème de la "validité des prévisions":

Des données de prévision commerciale sont extraites et stockées chaque semaine. Chacune de ces extractions correspond à une valeur EXTRACT_DATE.
Les données d'entrée nous donnent des quantités (QTY) par date de prévision (SCHED_HIPT_DTTM)
Dans le cas réel, plusieurs dimensions complémentaires sont présentes (client, produit, etc.)

Notre table de faits présente donc au minimum, pour l'exemple, les éléments suivants :

EXTRACT_DATE (KEY)
SCHED_SHIP_DTTM ((KEY)
QTY (MEASURE)

Besoin d'analyse

Il s'agit d'observer au cours du temps (date de vision), l'évolution des quantités prévues pour une date donnée (date de prévision).
Par exemple, nous voulons observer l'évolution des quantités prévues pour Mai 2010, durant les mois qui précèdent. Le but est d'évaluer la fiabilité des prévisions de M-1 à M-6.

Modèle

On a donc un modèle de base contenant une table de fait et deux dimensions temporelles.

Dimensions

Les dimensions que nous voulons lier ont une structure identique.

Extract Time (date de Vision)

Schedule Time (date de réalisation prévue de la mesure)

Formule

Dans les formules suivantes, nous utilisons la fonction LINKMEMBER (Retourne le membre équivalent à un membre spécifié dans une hiérarchie spécifique).

CREATE MEMBER CURRENTCUBE.[MEASURES].[Qty on Schedule]
 AS SUM(
LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])
,[Measures].[QTY]), 
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[QTY M-1]
 AS SUM(parallelperiod([Extract Time].[Extract Time].[Month], 1, LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])),[Measures].[QTY]), 
VISIBLE = 1  ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[QTY M-2]
 AS SUM(parallelperiod([Extract Time].[Extract Time].[Month], 2, LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])),[Measures].[QTY]), 
VISIBLE = 1;   

CREATE MEMBER CURRENTCUBE.[MEASURES].[QTY M-3]
 AS SUM(parallelperiod([Extract Time].[Extract Time].[Month], 3, LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])),[Measures].[QTY]), 
VISIBLE = 1;   

CREATE MEMBER CURRENTCUBE.[MEASURES].[QTY M-4]
 AS SUM(parallelperiod([Extract Time].[Extract Time].[Month], 4, LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])),[Measures].[QTY]), 
VISIBLE = 1;   

CREATE MEMBER CURRENTCUBE.[MEASURES].[QTY M-5]
 AS SUM(parallelperiod([Extract Time].[Extract Time].[Month], 5, LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])),[Measures].[QTY]), 
VISIBLE = 1;   

CREATE MEMBER CURRENTCUBE.[MEASURES].[QTY M-6]
 AS SUM(parallelperiod([Extract Time].[Extract Time].[Month], 6, LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])),[Measures].[QTY]), 
VISIBLE = 1;

Résultat

En ne précisant que la "Schedule Time", on constate que l'"Extract Time" s'est bien positionnée automatiquement.

Pivoter une table avec Sql Server 2005 >

Jean-Baptiste HEREN — Thu, 22 Jul 2010 23:08:00 +0200

Pivoter une table peut avoir un interêt, notamment pour faire du reporting. Voici un exemple simple d'utilisation ce cette commande SQL

Table initiale

On a au départ , des données stockées dans une table, selon le modèle code/valeur, les deux colonnes représentant une clé primaire.

-------------
|TABLE_BASE |
-------------
|Code     k |
|value    k |
-------------

Table Finale

On souhaite obtenir l'ensemble des valeurs associées à un code, sur une seule ligne. Dans cet exemple, on sait qu'il n'existe au maximum, que 9 valeurs associées à un même code.

-------------
|TABLE_PIVO |
-------------
|Code     k |
|value1     |
|value2     |
|value3     |
|value4     |
|value5     |
|value6     |
|value7     |
|value8     |
|value9     |
-------------

Commande SQL

Ici, on utilise un Rank pour générer la données sur laquelle sera faite le Pivot.

[SQL]
/* Code SQL pour gérérer les données Pivotées */
WITH Options
AS
(
SELECT code, value, rank() over(PARTITION BY code order by value) as POS
FROM TABLE_BASE
)
SELECT * FROM Options PIVOT (MAX(value) FOR POS IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
) AS pvt

Macro Excel : Générer les ordres SQL INSERT à partir d'une feuille Excel

Jean-Baptiste HEREN — Tue, 29 Jun 2010 16:57:00 +0200

Voici le code d'une macro permettant la génération automatique d'ordres SQL INSERT à partir des données d'une feuille excel.

prérequis :

La première ligne de données doit contenir le nom des colonnes.
Le nom de la feuille doit correspondre au nom de la table

Résultat:

La macro génère un fichier .sql portant le nom de la table cible, contenant les ordres SQL du type INSERT INTO mytable(col1,col2,...) VALUES(val1,val2,...).

Sub generateSQLInsert()
'Loop on current scheet and generate sql INSERT based on column names as columns an sheet name as table
' Will create one statement per line aka INSERT INTO mytable(col1,col2,...) VALUES(val1,val2,...)
' Author: Jean-Baptiste Heren - 29-06-2010 - http://blog.jbheren.com

Dim tableName As String 'tablename extracted from first row of each column
Dim filePath As String
Dim slashPosition As Integer
Dim pathOnly As String

Dim columnsSQL As String ' will contain the insert into table (col1,col2,...) statement
Dim dataSQL As String    ' will contain the values (val1,val2,...) statement
Dim separator As String

Dim myRow As Integer 'Row counter
Dim myCol As Integer 'Column counter

'set Tablename from sheet name
tableName = ActiveSheet.Name

' get current excel file Path
filePath = ThisWorkbook.FullName
slashPosition = InStrRev(filePath, "\")
pathOnly = Left(filePath, slashPosition)

MyFile = pathOnly + tableName + ".sql"

'get column names from first row
myColInput = InputBox("What column to START on ?")
myCol = myColInput
myRow = 1
columnsSQL = "INSERT INTO " + tableName + " ("
separator = " "

Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop until you find a blank.

    columnsSQL = columnsSQL + separator + Trim(ActiveSheet.Cells(myRow, myCol))
    separator = ", "

    myCol = myCol + 1 ' Move to next column
Loop
columnsSQL = columnsSQL + ") "

'get column values from second row
myRow = 2
myCol = myColInput

'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on rows until blank.

    dataSQL = "VALUES ("
    separator = " "

      Do Until ActiveSheet.Cells(myRow, myCol) = "" 'Loop on columns until blank.
        dataSQL = dataSQL + separator + "'" + Trim(CStr(ActiveSheet.Cells(myRow, myCol))) + "'"
        separator = ", "
        myCol = myCol + 1
      Loop

    dataSQL = dataSQL + ");"

    myCol = myColInput ' Return to specifiedcolumn
    myRow = myRow + 1 ' Move to next row

    Print #fnum, columnsSQL + dataSQL
Loop

' Close the file
Close #fnum

End Sub

Convertir un fichier csv en xls en ligne de commande : mycsv2xls.vbs

Jean-Baptiste HEREN — Thu, 27 May 2010 16:01:00 +0200

Suite à un besoin ponctuel d'intégration de données, j'ai développé un petit script en VB, utilisant l'application Excel pour faire l'import automatique de fichiers délimités par des points-virgule dans un fichier Excel.

En effet, une source Excel dans Microsoft SSIS est beaucoup plus facile d'emploi qu'un fichier plat, notamment lorsque le nombre de colonnes dans le fichier n'est pas fixe.

Le Script en question pourra être exécuté dans SSIS par l'utilisation d'un 'Execute Process Task'.

le programme mycsv2xls pour Windows fonctionne comme suit :

mycvs2xls.vbs source_full_path target_directory

Analyse d'écarts dans le temps avec Analysis Services

Jean-Baptiste HEREN — Tue, 23 Mar 2010 16:47:00 +0100

L'exemple suivant illustre un cas courant d'analyse d'écarts d'une mesure dans le temps, traité via des formules MDX dans un cube Microsoft Analysis Services.
Cela pourrait s'appliquer à un "reporting" sur la qualité des prévisions de vente par rapport aux ventes réalisé.

Présentation du cas

Une table de faits contenant une "photo" mensuelle d'un calendrier de prévisions

|--------------------|

| Fact Table         |

|--------------------|

|EXTRACT_DATE (key)  | Date d'extraction (Photo des données)

|SCHEDULE_DATE (Key) | Date de la prévision

|Item_Code (Key)     | Code article

|...                 | Divers axes (client, Filiale, etc.)

|QTY(measure)        | Quantité prévue

|--------------------|

Notre but est de contrôler a posteriori, l'évolution de la quantité prévue, au cours du temps (chargements mensuels). L'idée est d'évaluer la fiabilité des prévisions au cours du temps, par rapport au réalisé.
Pour exemple :
- On choisit d'observer l'évolution de la quantité prévue en Juin 2010
- Nous souhaiterons donc afficher pour chaque date d'extraction précédent Juin 2010, la quantité enregistrée sur Juin 2010
- Cela revient à comparer Juin 2010 respectivement avec chacune des périodes d'extraction désirées.

Dans ce contexte, il est intéressant de savoir détecter l'équivalence entre les éléments de nos deux dimensions temporelles.

Cela peut être réalisé par l'utilisation de la fonction MDX suivante, qui retourne le membre équivalent à un membre spécifié dans une hiérarchie spécifique :

LinkMember(Member_Expression, Hierarchy_Expression)

L'utilisation de cette fonction est illustrée dans les formules suivantes.

Les Formules

Voici maintenant les formules utilisées pour calculer les écarts pour chaque période par rapport à la période de référence.

[Qty on Schedule]
Description : Quantité de référence. correspond à la date d'extraction "la plus proche" de la date prévue.

SUM(LINKMEMBER([Schedule Time].[Schedule Time].currentmember,[Extract Time].[Extract Time])

[Accuracy Qty]
description : % de validité des quantités (totales) entre la date d'extraction du contexte et la quantité de référence (ci-dessus)

iif([Measures].[QTY] > 0,

1- ABS(([Measures].[QTY] - [Measures].[Qty on Schedule]) /iif([Measures].[Qty on Schedule] > [Measures].[QTY],[Measures].[Qty on Schedule],[Measures].[QTY]))

,0)

[Accuracy Qty by Item]
Description : % de validité pondéré par article

SUM(descendants([Item].[By Model].currentmember,[Item].[By Model].[Item Code])

,Measures.[Accuracy Qty]*Measures.[Qty on Schedule])/SUM(Measures.[Qty on Schedule])

Exemple de rapport

Voici un exemple de rapport attaquant un cube SSAS depuis Cognos.

Cognos 8 Macro functions list

Jean-Baptiste HEREN — Fri, 13 Nov 2009 11:24:00 +0100

We have seen that cognos macro functions available in framework manager are also available in report studio.

Here is a simple list of existing Cognos Macro with description.

This list contains functions that can be used within a macro. A macro may contain one or more macro functions. A macro is delimited by a number sign (#) at the beginning and at the end. Everything between the number signs is treated as a macro expression, which is executed at run time.

+

value1 + value2
Concatenates two strings.
Example: # '{' + $runLocale + '}'#
Result: {en-us}

CAMIDList

CAMIDList ( [ separator_string ] )
Returns the pieces of the user's identity (account name, group names, role names) as a list of values separated by commas.
Example: #CAMIDList ( )#
Result: CAMID("::Everyone"), CAMID(":Authors"), CAMID(":Query Users"), CAMID(":Consumers"), CAMID(":Metrics Authors")

CAMIDListForType

CAMIDListForType ( identity type )
Returns an array of the user's identities based on the identity type (account, group, or role). It can be used with the macro functions csv or join.
Example: [qs].[userRole] IN ( #csv ( CAMIDListForType ( 'role' ) ) # )
Result: [qs].[userRole] IN ( 'Administrator', 'developer' )

CAMPassport

CAMPassport ( )
Returns the passport.
Example: #CAMPassport ( )#
Result: 111:98812d62-4fd4-037b-4354-26414cf7ebef:3677162321

CSVIdentityName

CSVIdentityName ( %parameter_map_name [ , separator_string ] )
Use the identity information of the current authenticated user to lookup values in the specified parameter map. Each individual piece of the user's identity (account name, group names, role names) is used as a key into the map. The unique list of values that is retrieved from the map is then returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.
Example: #CSVIdentityName ( %security_clearance_level_map )#
Result: 'level_500' , 'level_501' , 'level_700'

CSVIdentityNameList

CSVIdentityNameList ( [ separator_string ] )
Returns the pieces of the user's identity (account name, group names, role names) as a list of strings. The unique list of values is returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.
Example: #CSVIdentityNameList ( )#
Result: 'Everyone' , 'Report Administrators' , 'Query User'

array

array ( string_exp | array_exp { , string_exp | array_exp } )
Constructs an array out of the list of parameters.
Example:
#csv ( 'x1' , 'x2' , array ( 'a1' , 'a2' ) )#
Result:
'x1' , 'x2' , 'a1' , 'a2'

csv

csv ( array_exp [ , separator_string [ , quote_string ] ] )
Constructs a comma separated values string from the elements of the array. Optionally the separator and quote strings can be specified. The default separator is a comma ( , ) and the default quote character is a single quote ( ' ).
Example:
#csv ( array ( 'a1' , 'a2' ) )#
Result:
'a1' , 'a2'

dq

dq ( string_exp )
Surround the passed string with double quotes.
Example: #dq ( 'zero' )#
Result: "zero"

grep

grep ( pattern_string , array_exp )
Searches for elements of an array that match the pattern specified in the first argument. It returns an array with the elements that pass the pattern.
Example:
#csv ( grep ( 's' , array ( 'as', 'an', 'arts' ) ) )#
Result:
'as', 'arts'

join

join ( separator_string , array_exp )
Joins the elements of an array using the separator string.
Example:
# sq ( join ( ' | | ' , array ( 'as', 'an', 'arts' ) ) )#
Result:
'as | | an | | arts'

prompt

prompt ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
Prompt the user for a single value. Only the prompt_name argument is required. The datatype defaults to string when not specified. The prompt is optional when defaultText is specified. The text, when specified, will precede the value. A queryItem can be specified to take advantage of the Prompt Info properties of the query item. The trailing_text, when specified, will be appended to the value.
Example:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > #prompt('Starting CountryCode',
'integer',
'10'
)#
Result:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 10

promptmany

promptmany ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
Prompt the user for one or more values. Only the prompt_name argument is required. The datatype defaults to string when not specified. The prompt is optional when defaultText is specified. The text, when specified, will precede the value. A queryItem can be specified to take advantage of the Prompt Info properties of the query item. The trailing_text, when specified, will be appended to the value.
Example:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY IN ( #promptmany ( 'CountryName' ) # )

Result:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE IN ('Canada' , 'The Netherlands' , 'Russia')

sb

sb ( string_exp )
Surround the passed string with square brackets.
Example: #sb ( 'abc' )#
Result: [abc]

sort

sort ( array_exp )
Sorts the elements of the array in alphabetical order. Duplicates are retained.
Example:
#csv ( sort ( array ( 's3', 'a', 'x' ) ) )#
Result:
'a', 's3', 'x'

split

split ( pattern_string, string_exp | array_exp )
Splits a string or the string elements of the array into separate elements.
Example:
#csv ( split ( '::', 'ab=c::de=f::gh=i' ) )#
Result:
'ab=c' , 'de=f', 'gh=i'
Example:
#csv ( split ( '=' , split ( '::', 'ab=c::de=f::gh=i' ) ) )#
Result:
'ab' , 'c' , 'de' , 'f', 'gh' , 'i'

sq

sq ( string_exp )
Surround the passed string with single quotes.
Example: #sq ( 'zero' )#
Result: 'zero'

substitute

substitute ( pattern_string, replacement_string, string_exp | array_exp )
Search for a pattern in a string or in the string elements of an array and substitute the found text with other text.
Example:
#sq ( substitute ( '^cn=', '***', 'cn=help' ) )#
Result:
'***help'
Example:
#csv ( substitute ( '^cn=', '***', array ( 'cn=help' , 'acn=5' ) ) )#
Result:
'***help' , 'acn=5'
Example:
#csv ( substitute ( 'cn=', '', array ( 'cn=help' , 'acn=5' ) ) )#
Result:
'help' , 'a5'

Example :

Transform :
[Ph_Quality].[Manufacturing Business Unit].[Manufacturing Business Units].[Business Units]->:[YK].[[Manufacturing Business Unit]].[Manufacturing Business Units]].[Business Units]].&[FOOKEY]]]
to
[Ph_Quality].[Inventory Business Unit].[Business Units].[Business Units]->:[YK].[[Inventory Business Unit]].[Business Units]].[Business Units]].&[FOOKEY]]]

the macro will be like that : (regex need the brackets to be escaped with \)
#
substitute('\[\[Inventory Business Unit\]\]\.\[Business Units\]\]','[[Manufacturing Business Unit]].[Manufacturing Business Units]]',
substitute('\[Inventory Business Unit\]\.\[Business Units\]\.\[Business Units\]','[Manufacturing Business Unit].[Manufacturing Business Units].[Business Units]'
,prompt('BU Inventory','MUN'))
)
#

unique

unique ( array_exp )
Removes duplicate entries from the array. The order of the elements is retained.
Example:
#csv ( unique ( array ( 's3', 'a', 's3', 'x' ) ) )#
Result:
's3', 'a', 'x'

urlencode

field_one=urlencode(prompt('userValue'))
URL encodes the passed argument. Useful when specifying XML connection strings.
urlencode(prompt('some_val'))
%27testValue%27

Cognos 8 Report Studio's Conditional block

Jean-Baptiste HEREN — Thu, 12 Nov 2009 16:51:00 +0100

Conditional blocks allow you to add multiple reports to one page. By defining multiple zones, for example, you can coose between a Synthesis and a detailed table.

To make use of conditional blocks, you will need :

1 - one value prompt

This prompt will allow the user to choose the conditional block to display. It can be located on a promt page, on the reports pages themselves or both.
On this particulat prompt, set a parameter name, auto-submit to 'Yes' and define a static list of choices.

2 - one variable for conditional choices

in menu view->variables, create a string variable to be used as conditional variable in the conditional block. and set Expression to get the prompt choice parameter value.
Create as many values as conditional blocks you need, keep the same ones as in the conditional prompts.

3 - one conditional block

On report's page, add a conditional block and set the conditional variable to the one you have created & select values you vant to use.

Then, you only have to select one value in current block, add contents to it. Select another value, add contents... it's done.

Last period prompt option in Cognos report Studio

Jean-Baptiste HEREN — Tue, 10 Nov 2009 20:42:00 +0100

When you build a new report with some prompts, including a time dimension; you may want to add an option to automatically select the last Period. this way users can :

see their last data without chosing it in a time tree (but with ability to choose another one).
schedule a report witch will always select the last period available.

This can be done by using a set containing the #promptmany()# Cognos macro

1- create the set definition

First, we need a report with at least one query and a prompt on the time dimension. Following examples works on the Month level.

If your query already exists, just remove any filter on your Time dimension as we will replace it with a data item set.

In report's Query, we will add a new data item containing our set definition, named Chosen_Month.

The #promptmany()# Cognos macro allows create a set from parameters with ability to specify a default value :

#promptmany('[parameter name]','[Output Type]','[Default set definition]')#

We want to set the Date prompt MONTH parameter with last period as default value:

set( #promptmany('MONTH', 'MUN', 'closingPeriod([Invoice Sales].[Time Dimension].[YMD Hierarchy].[Month])')# ).

The Output Type MUN (Member Unique Name) format will give the following Result :

[Invoice Sales].[Time Dimension].[YMD Hierarchy].[Month]->[all].[2009].[Oct-2009]

2- Set prompts to reflect our new behaviour

Now the Time prompts is not required anymore so update the required flag to Optional

(optional) Another solution would be to create a prompt option Box named "Last Period" using a collection ('Y','N') ie with parameter named last_period.

Then create a render variable on the period prompt.

You cans also set a default value to make the parameters selection faster

set all other required prompts to cascading prompt on last_period.

Then you will see the Period list only if required.

I hope this will help you :).

SSAS Batch Processing with XMLA

Jean-Baptiste HEREN — Mon, 09 Nov 2009 12:10:00 +0100

When you want to schedule the processing of SSAS database (using Sql Server jobs), you have two options :

create ans SSIS package containing Analysis services tasks & schedule it.
Build ans XMLA script & schedule it.

Here we will see how you can build an XML script describing the processing tasks you want to execute.

1- Buiding & testing the script

In Sql Server management studio, connect to yous SSAS database.

Open a new XMLA Query window & paste the following script in it.

<Batch ProcessAffectedObjects="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
    <Object>
      <DatabaseID>ASdatabase</DatabaseID>
      <DimensionID>Dimension ID(not name)</DimensionID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Process>
    <Object>
      <DatabaseID>ASdatabase</DatabaseID>
    <DimensionID>Dimension 2 ID</DimensionID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Process>
<Object>
    <DatabaseID>ASdatabase</DatabaseID>
    <CubeID>Cube ID</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

Of course, you just have to set your own database and dimensions/cubes IDs. You can add as many <process></process> blocks you need.
Now just run your Script and see what happens.

2- Scheduling

When you are ready, create or open ans Sql job under SQL Server Agent.

add a step to your job, choose "SQL Server Analysis Services" as Type & paste your script.

Of course this is just a Quickstart. For more informations about XMLA structure & options, just refer to the MSDN Help.

setup cognos to work with OLAP MSAS cubes security

Jean-Baptiste HEREN — Wed, 26 Aug 2009 20:38:00 +0200

Usually, when we create a data source in Cognos, it is easier to specify a connection using Cognos Service Credentials. This means that all connections to the data source are done using the Cognos service user's profile.

Advantage of this method is that it needs less setup on the corresponding database server (only one account).
Disadvantage is that you cannot make use of "row level security" as all Cognos users will connect using the same profile (Data source has no information about current Cognos user).

To take advantage of an eventual user level security setup behind Cognos data sources, we need to connect using the current Cognos user's profile.

In the following example, we are editing a Microsoft Analysis services data source. We will setup that Cognos connection so that user's own credentials are transmitted to the database server:

Remove SSO : just because it didn't work for me when combined with MSAS active directory connections to data sources, on Cognos 8.2. It may work on later releases… If someone can help, let me know :).
Using the portal, change connection's setup so that it uses an external namespace (AD).

In Cognos directory, select data sources tab and click on the connection
On the connexion page, click on the properties icon to edit parameters.
On connection string, click on the pen icon to edit credentials.
In the connection string, we change from "Cognos 8 service credentials" to "External Namespace: Active Directory". Then click OK on each page to validate.

Row Level security in MSAS

Jean-Baptiste HEREN — Wed, 26 Aug 2009 07:11:00 +0200

Security implementation in Analysis Services

In some cases, companies want to give different level of access to strategic data, depending on user’s profiles. Analysis services gives us ability to secure cubes data by building Roles containing security rules, mainly by filtering dimensions data.
For example, if you have a cube giving Turnover amounts per salesperson, salesmen should see only the data corresponding to their sales area only. Also, one manager may want to see data for all areas he’s responsible for.
This document describes how we can achieve security on the sales Area axis for the sales domain. Notice taht in this example, we are using Cognos as reporting tool. This addidional layer has no impact on the way we setup MSAS & Active directory, results will be the same with Microsoft Reporting services

Use Case

As an abstract, here is a typical use case, after setup of row level security in MSAS and Active directory sign on in Cognos.

Security Definition architecture

To apply row level security, we will have to define User profiles & associate them with Security Roles in Sql Analysis services, using Active directory for Binding.

To apply Security on Dimensions in MSAS Cubes, we want to rely directly on active directory. This way we will be able to add new users without changing the security definitions in Analysis services:
-         We create one active directory group for each MSAS Security role
-         Each MSAS Role has only one AD Group as member.
-         New users just have to get the right AD Groups to get the right access to MSAS Cubes & dimensions.

Figure 1 - Static model of Security Elements

We will work on dimensions data security with order defined as Deny, Allow. In other words, if a dimensions is secured : default is deny and we specify allowed elements. This way, we will be able to combine multiple roles givig access to different elements.

One role in Analysis services can manage access to any element:
-         connexion
-         cubes
-         cell data
-         dimensions
-         dimension data (the one we are working on)

Here we want to manage security on dimensions attributes, so that users can only access to data filtered using allowed dimension’s attributes. Notice that we can apply security on each level of one dimension.

We have one “Sales Area” Dimension with three levels, on witch we want to create security roles.

Figure 2 - Static model of Sales Area Dimension

One Global user Role

We will change the way Cognos connects to MSAS. Instead of always connecting using Cognos Service credentials, we want Cognos to connect using user’s credentials.

Then we have to create a generic user role in each SSAS Database, so that all users can access to cubes, even if they don’t own a secured group. This role must at least set database & cubes read access. All Cognos users will have to be member of this role, this can be done using a Cognousers group defined in Active directory.

All Cognos Users groups will need a basic read access, at least.

Specific user Roles (with security on data)

Those roles will limit access to specified Dimensions Elements.

For Example:
- Sales_Asia.role will grant access to the Sales area attribute Member ASIA.

- France.role will grant access to the Country attribute Member FRANCE.

If we want the cube to recalculate aggregations and totals regarding the applied security, we will have to select the “Enable visual Total” option on advanced Tab.

MSAS Roles validation

The “Grant based” Security allows a user to cumulate access from multiple roles.

For Example, one user with ASIA & FRANCE groups will get both access. We can validate this using the cube browser’s testing tools.

In the Sql Server Management studio, we can browse cubes using specified profile or roles :

In SQL server management studio cube browser, we Specify the security context;

Then we navigate Dimensions and data;

We can see that both Asia and France Roles associated members are allowed.