My Business Intelligence Experience

I never really understood completely the 'Tuning' settings on dispatchers but I have played with them enough to see how they work and the best combination based on hardware available and want to share those here. For a quad-core process the minimum requirement for RAM I would recommend is 16gb(Cognos 8.3 and higher)- I saw a lot of perfomance improvement in load balancing and scheduling. Set up Processing Capacity to 2 or 1 based on how you have the distributed install and the hardware on each of those machines. High and Low affinity settings should ablways be default values of 1 and 2. Maximum number of processes for the batch report service,report service during peak and non-peak hours can be changed to 8(based on how stable the system is you can increase or decrease if you see unexplained errors or outages) Set the peak time hours according to your business hours and the schedule times Set Queue time limit of the report service (seconds) to 600 If you have PPES then...

Environment Tuning:  Notes Standard IIS Setup ISAPI IIS   900 seconds minimum 900 seconds IIS Content Expiration to 10 days 10 days HTTP Static File Compression turned on On Test Web Page Tune Tomcat server.xml Oracle cogdmor.ini fetch buffer = 4096  4096 CQEconfig.xml Time=300, size=xxx Time=300 , poolsize =50 Default font  Arial If needed Change Time Zone Eastern Restart Service Test Cognos Connection Tune Max Batch and Max Report Processes  Yes, set to 1x # proc because of limited memory 2 X #Processor cores

I like to have the Cognos content store setup on sql server express for couple of reasons a)  No need to involve DBA's b) Easy to maintain and schedule backup's. c) No need for reboots or outage monitoring /alerts from other groups. Cons: a) Not accepted as a production content store as Cognos doesn't support the express version and also not a corporate standard. So I have DEV and UAT on SQL server Express 2005. Here is the script that you can automate to take regular backups at database level.(from within cognos you can do a full export of CS) DECLARE @cursor AS CURSOR DECLARE @dbname @query AS VARCHAR ( 50 ), AS VARCHAR ( 200 ) SET @cursor = CURSOR SCROLL FOR SELECT NAME FROM MASTER . dbo . Sysdatabases WHERE NAME IN ( 'COG_84_CS' ) OPEN @cursor FETCH NEXT FROM @cursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET TO DISK = ''C:\DBbakup\' @query = 'BACKUP DATABASE ' + @dbname + ' + @dbname + ...

The Audit package that cognos provides doesn't come with a data item that just lists out the package name, you will have extract it for QS and RS reports. PP is an exception .Here is the syntax used to extract package name from search path for RS and QS against Oracle, you can alter this for non-oracle databases with equivalent functions. QS: SUBSTR ([Audit Database].[COGIPF_EDITQUERY].[PACKAGE],position ( 'package[@name=', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )+15,(position ( ']/model', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )-1)-(position ( 'package[@name=', [Audit Database].[COGIPF_EDITQUERY].[PACKAGE] )+15)) RS: SUBSTR ([Audit Database].[COGIPF_RUNREPORT].[PACKAGE],position ( 'package[@name=', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )+15,(position ( ']/model', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )-1)-(position ( 'package[@name=', [Audit Database].[COGIPF_RUNREPORT].[PACKAGE] )+15)) Folder Na...

I came across this good explanation and wanted to share: It's strange but this is how to understand the Denotation Cognos Uses [T:O] ? {T:O} []= Left Table's Relationship view of relationship {} = Right Table's Relationship view of relationship T = Relationship type of The Table in Question (as indicated by [] or {} in my diagram) O = Relationship type of The Other Table in Question ? = Operator Each Table Sees itself as the Left side of the Join and the other table as the Right (this is to help alleviate Right vs Left outer join performance) The Cardinality drop down is showing you what that table would see the other table as if it were the Left side of the Join So Table A and Table B: [1:1] = (1:n) Table A (marked with []) is related to Table B as 1:1 (A on left B on right) Table B (Marked with {}) is related to Table A as 1:N (B on left A on right) So for an INNER Many to One Relationship between Table A and table B you set: 1:1 = 1:N For an OU...

Once a new package is published, users get the warning when they open reports saying a new version of package is available and they have to update. You can get rid of this by updating report objects from a single SDK script run. Cognos has a sample, you will have to modify this and run it for a batch of reports based on packge. the way i did it was from Eclipse pass the text file name that has the package and report search paths in it as arguements. _https://www-304.ibm.com/support/docview.wss?uid=swg21346704

After i started blogging it came to my mind that initially when I was learning or exploring cognos/BI there was no place to go and ask people one on one and get the issue or question addressed. I know there are lot of forums where you can post questions, but the issue is some might be afraid to ask simple questions and some questions might not get answered right away or might even be lost. This gave me an idea for a simple forum where you can get online help from experts.The Professional Network.

I have this issue with my environment where a Go!Excel report used multiple times by users at the same time causes the gateway/dispatcher running this report to max out and eventually create .phd dump files. The portal is unsuable during the time of this dump file creation. I'm working with cognos on finding a solution.

http://www.ibm.com/developerworks/data/library/techarticle/dm-1001cognosmashup/index.html?ca=drs-#icomments http://www.ibm.com/developerworks/data/library/techarticle/dm-1005googlemapscognos/index.html?ca=drs-

While working on a issue with cognos support, they explained me the difference between ojdbc14.jar and ojdbc5.jar that we usually copy when installling cognos for the first time from oracle/jdbc/lib location. These are runtime java files designed to communicate with oracle based on java 1.4 and java 1.5 versions. By default if you don;t have a java home set on the server cognos uses it's own version of java, for cognos 8 its 1.5.

I recently installed Cognos 10 and it went smooth. I see the interface a bit more graphically appealing compared to 8.x versions. It has couple of new studios which are Business Insight and Business Insight Advanced which are kind of Go!Dashboard functionally. And a kool feature in advanced tool is that you can link and manage external data and create a package locally from the portal and publish it. There is something new called Active reports which is a .mht file output of a report that can be emailed and accesed offline. There are couple of other interesting tolls that come with C10, BI Life cycle manager or aka Utility manger for easy migration from 8 to 10. Query Analyzer for performance tuning on reports.Will keep you updated as I find more features.

Currently I'm working on a report documentation tool which is a web-based application that's integrated with cognos/microstrategy audit meta data. The main purpose of this tool is to document report details with unique alpha-numeric numbers, details from this tool can be accessed as reports that will help Business units and Analysts in any organization. The key advantages of this tool are efficient way to maintain report inventory, help identify different subject area reports easily and eliminate duplication of reports.This is just Phase I of the project, the tool will be available as a freeware sometime in 2011.

Recently I had an issue with Advanced conditional formatting/properties window not comminup at all after you click on it. I contacted cognos support and they don't have a solution for this. I figured out the reason as i remeber using it last year for couple of reports and it worked back then, initially i thought it might be an issue with a hotfix that i have applied as it's an issue on dev,uat and PROD or a system update. But the main reason is I edited Resources.xml and templates.xml files to put in custom corporate report templates and logos and that might have corrupted the way components communicate with each other. i had to revert back the change to isolate this.

I read a lot of documentation on portal customization in cognos and the problem is none of them work exactly the way they document. The easiest way is to edit system.xml file and copy custom images to the respective location: Images: ..\webcontent\skins\corporate\branding             ..\webcontent\skins\classic\branding            ..\\webcontent\ps\portal\images <!-- Custom OEM headers -->  <param name="OEM">     <customHeader showContext="true" contextDelimiter=" - ">       <style styleFolderName="corporate">     <table style="background-color:#ffffff">      <tr>       <td><img src="../skins/corporate/branding/my_corp_logo.gif"/></td>       <td class="header...

As you might have noticed that on the Cognos audit package the report run details are saved on different tables depending on the studio and there is no simple way to see all the report run details at one place i created this report in Report Studio(8.4) that Unions and gives you report run details from studios and total number of reports run in past 24hrs,reports run by the report executor,number of people running reports. The naming convension on the Data items are different than the default audit package. You can also implement this as a one of solution in the model. <report xmlns=" http://developer.cognos.com/schemas/report/6.0/ " expressionLocale="en-us">  <modelPath>/content/package[@name='BI MetaData']/model[@name='model']</modelPath>  <drillBehavior modelBasedDrillThru="true"/>  <queries>   <query name="Adhoc Reports">    <source>     <model/>    ...

Check this out, Motio has a range of products and solutions that help cognos admins, analysts, consultants http://www.motio.com/products.do The one I use the most is the freeware that helps me accomplish some admin tasks easily.

This has been pending for too long now. The main reason to open this blog is to post my personal experiences with BI applications mainly IBM Cognos. I hope this will be a good start  to share and gain knowledge on the issues I encounter from time to time.