Posts

Showing posts from 2011

Restart Cognos Services from NT Scheduler and Email logs

Most of the company's that have multi server Cognos environments face challenges time to time on keeping environments up an running by minimizing outage times and crashes. From past couple of years I have implemented auto recycling/rebooting on windows NT Cognos servers for two main reasons: 1) Get rid of dump/garbage files due to report runs. 2) Ensures System stability if implemented weekly or monthly. With a little help from our NT guys we came up with a system reboot script which also emails a message, I scheduled this script as NT job sequentially(CM, BCM,DISP,DISP....) on all servers for a window of 1 hour every Saturday. This setup works really good except that one of the servers which is a dispatcher that always comes up at the end fails to initialize EVENT Service. So I was looking for ways to restart cognos service after system reboot and have the logs emailed automatically. Starting cognos services from a script is an easy task but to check if the services came up rig

Cognos 8/10 CAMID-User ID/LDAP ID report

I came across this slick KB article on how to get the list of active sessions/users currently on any cognos environment and implemented it. https://www-304.ibm.com/support/docview.wss?uid=swg21385278 After I got this to work the servlet that runs only retrieves the CAMID and the session start time. I started looking around in the content store tables and was able to successfully  retrieve the CAMID and the associated  LDAP ID/User ID. I made this a query subject in the Audit model and joined it to the security groups table and created a report that will give me User ID, CAMID, AD Group, Name , Email. Query : SELECT  distinct ldap_id,     last_name,     first_name,     email,     objid as CAMID     FROM            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,                   c1.surname last_name, c1.givenname first_name,                   c1.email email, c1.objid              FROM cmobjprops1 c1 LEFT OUTER JOIN cmobjprops33 c33                   ON c33.cmid = c1.cmid          

Cognos Retrieve deleted/orphaned user's my folders from content store

Recently I had a challenging requirement to retrieve some users My Folders content whose active directory profiles were accidentally deleted and recreated. This requirement came to me after three days the profiles have been recreated, because there were profiles with new GUID in active directory which is nothing but CAMID in cognos they were not able to see any of their reports in cognos. First i started importing the content store deployment from 3 days ago(this had to be extracted with a point in time copy as it gets overwritten every night) to a sandbox and it ran for 5hrs and showing errors on some user profiles saying their CAMID is missing. After struggling for 10 hours I found this slick SDK script that gave some hope. Basically this SDK script on cognos KB queries the content store for Orphaned My Folders and saves them to a folder in Public Folders with CAMID's. We then had our DBA's restore the content store to the point where users had old CAMID's and I pointed t

Cognos Transformer Cube failure Alert Mechanism

Recently I had a requirement to create a automatic alert mechanism on cube build failure. As we all know this can be a project by it self if we set it up in a traditional way of write cube logs to tables and creating reports on top of the tables and setting up events to send out alerts on any failure. But with the help of a network admin I was able to setup alerts and it took some basic Vb coding too.. Below is the workflow of how the setup works and the basic requirements(this is for windows environment only): There are two VB scripts we need to setup and schedule via windows scheduler. First script writes the name of the Transformer log file into a .txt file. In my case the log file had a date that had to queried everyday before writing the log file name. ' Declare Variables for Oracle SQL   Dim OdbcDSN   Dim connect, connectionstr, sql, resultset      Dim fso      Set fso = CreateObject("Scripting.FileSystemObject")    '*****************************

IIS 7 configuration for Cognos 8/10

Recently I was helping a friend to install Cognos 10 on his Windows 7 laptop, the install went smooth but to get the IIS running and have the Portal page come up it took more than 2 hours of googling and tweaks. So i figured it's wise to share this and even if one person can save time it's worth it. Configuring IIS 7 for cognos has changed a lot when compared to IIS 6, where you have straight forward settings to setup virtual directories and allow read/write/execute. In IIS 7 you need to do a bit more or may be it's just for windows 7, I haven't explored IIS 7 on Windows NT 2008. I have included the cognos support link that has the detailed steps in setting up IIS7. Before you follow the link you can enable or install IIS 7 in all Windows 7 professional systems under Control Panel > Programs > Turn Windows Components On or Off. Also make sure you enable ISAp and CGi extensions after IIS is installed from the same location under World Web Services(you can google t

Analysis Studio Default Analysis report

Recently had a requirement where a blank Analysis Studio report needs to open when you open an existing report and click on new report icon. By default tool tries to put first two dimensions across and the first measure to form a cross tab, to overcome this you can utilize the 'Default Analysis' under Package > More> Modify the package configuration... >Default Analysis. The report that we setup here will be used as a default view and will open up when you choose default analysis or new icon with in Analysis Studio. There is an enhancement logged in with cognos to have Get Data Later option to be set as default.

Exploring New BI tools

Currently I'm exploring new BI tools after attending some demos from major vendors like SAP and Microsoft. Microsoft has a free BI excel plug in that's simple to learn and use but very powerful, it's called power pivot. You can download it from www.powerpivot.com - check it out. Microstrategy has similar architecture as Cognos so it's easy to learn it from Administration,Development and Design perspectives.

Prompts in Cognos Framework Manager

I use prompts in FWM time to time when the data is huge and I want to filter it right from table and also in manual SQL. Every time I use it I search for the syntax so i thought would share it here: Macro-   #prompt(‘pmtName’,’ datatype’)# Parameter Map-  #$parametermap{prompt(‘ pmtName’,’datatype’)}#  Session Parameter-   #$parametermap{$ Sessionparameter}# Date Prompt-  to_date(#prompt('Start/END Date', 'varchar')#,'yyyy-mm-dd') If you have multiple values to pass on to these prompts use  'in' as part of where clause. where Data_Item = (#prompt('Param_Name', 'varchar')#) 

Query Cognos 8/10 Content Store

I came across these queries that can give you good insight on the data and maintenance of Content Store in Cognos 8/10. You can also leverage the CMTools utility that comes with cognos install. ..\Cognos\bin\utilities\cm\CMTools\CMTools.exe select count(*) as "total number of objects" from CMOBJECTS; select c.NAME as "class name", count(*) as "number of objects" from CMOBJECTS o, CMCLASSES cgroup by o.CLASSID, c.CLASSID, C.NAME having c.CLASSID=o.CLASSID order by c.NAME ; select c.NAME as "class name", count(*) as "number of secured objects" from CMOBJECTS o, CMCLASSES c, CMPOLICIES pwhere o.CMID=p.CMID and p.POLICIES is not null group by o.CLASSID, c.CLASSID, C.NAME having c.CLASSID=o.CLASSID order by c.NAME ; select count(*) as "Total number of outputs", SUM(cast(coalesce(DATALENGTH(DATAPROP),0) as bigint)) as "Total size of outputs" from CMDATA where DATAPROP is not NULL; select

Cognos 8 Data Caching

Image
Just happen to come across this interesting feature in Cognos that I used a while ago in Reportnet which allows Data Caching for faster report runs. Cognos 8 supports user session based data caching for report prompts/LOV's and data, to use this feature you will have to edit the Governor settings in the Framework Manager model and check the 'Allow usage of local cache' box. On the Report Studio report you can use the 'Use Local Cache' property of a query to selectively enable caching. Note: If you are doing live reporting this feature must be turned off.

Cognos Compatibility

This link gives all the details on different versions of Cognos and it's compatible softwares. https://www-304.ibm.com/support/docview.wss?uid=swg27014432

Changing /temp.. file location under Cognos Configuration

I was trying to change the temp file location to an allocated shared drive on Cognos servers but there is an issue with one of the servers not being able to start application services in tomcat while starting services.I'm working with cognos support on this, but what i don't understand is how can an other server work and not this. All servers have separate folders under the share and write access to the cognos account, the only difference is this network share was allocated for the server that works without an issue, but I did share the folder on the temp drive and then created sub folders for each server and made sure Cognos account has write access. Hopefully I'll get an answer soon :).

Network Shared drive setup to save report outputs in Cognos 8

Image
Cognos can be configured to allow users to save report outputs on an network drive as opposed to content store. This feature comes in handy when you have lot of business users using a central file at a network location and also you can setup FTP jobs to send these output files across networks. I have listed the steps on how to configure it: 1) On your Content Manager install edit the 'Archive Location File System Root' to the network location.     Cognos Configuration>Actions>Edit Global Configuration..>General> 2) Make sure the user account that Cognos services are running under have write permissions on this share. 3) Now this file share location has to be configures in Cognos Administration portal.    Configuration> Dispatchers and Services > Click on Define File System location(Highlighted in yellow) 4) Click New    5) You can setup multiple location based on folders within the share and how you want to set up security.   6) From properties>p

Rank in Cognos 8 development

We all know how to use the rank( ) function in cognos, it basically ranks the measure across a dimesion by weight. The basic syntax is rank([Data_Item] ASC/DESC) which will give a result set, but most of the times we need to just show top X or bottom X. For this requirement we can use A--> running-count (rank([Data_Item] ASC/DESC)) and then add a filter like A<=X to get top X, but there is a catch here what if there are multiple rows that have the same rank and you still want to show them. In this case use A--> running-count (rank(distinct([Data_Item] ASC/DESC))) and then a filter A<=X. To get the bottom X try this: A >= (maximum (running-count (rank(distinct([Data_Item] ASC/DESC)))) - X) There are multiple ways to achive this requirement, i saw couple of other interesting ways on the net but I used this and found simple enough to share.

CPU Monitoring on Cognos Servers

There a tons of freeware apps available that you can use to monitor CPU usage on Cognos servers and this really helps when you have a distributed environment as you can monitor the CPU on each of these servers right from you desktop/laptop without having to RDP. I use the Performance Monitoring service available in Windows Server OS . I found this link with a good explanation on how it works. http://www.computerperformance.co.uk/HealthCheck/Processor_Health.htm You can set this up from your local machine(XP) using perfmon tool. From windows RUN type perfmon that will bring up the Performance Monitoring window where you can add counters of the servers, if it throws an error that's because of security or counters not enabled on the machine you are trying to add, your NT guys should be able to fix it.

Parameterized URL in Cognos 8

I find this feature in Cognos 8 slick as this not only lets you manipulate the prompts and output format from URL but also helps you to integrate reports into other legacy/corporate portals/share point sites. The best feature I like is how you can selective choose different headers to be displayed and pass Parameters. http://www.ibm.com/developerworks/data/library/cognos/page123.html

Issues after upgrading from Cognos 8.3 to 8.4

This is my experience on upgrading a windows environment from cognos 8.3 to cognos 8.4. I din't use Upgrade Manger or other tools available from cognos to see how the reports or packages behave after the upgrade as i did the full content store export and import due to lack of resources and considering that 8.3 and 8.4 are not very different. I did this on a sandbox first where all developers tested reports and confirmed they saw no difference. After the upgrade there were issues with the way jobs were running as most of them were failing with socket/pogo errors and the ones that ran fine were sending emails from a different email than whats configured under notification. The fix was available on one of the forums where we edit a file and that fixed it. This was a distributed install with individual cognos components on each machine, after the upgrade over the weekend it ran fine and then on monday it suddenly crashed and nothing worked so i had to install a stand alone install on

Cognos 8 dimensional functions

I read this document couple of years ago on dimensional functions and found it useful when you are dealing with report development in Report Studio based on Transformer Cubes. These functions can also be applied on DMR reporting as DMR's support both MDX and relational functions. http://www.ibm.com/developerworks/data/library/cognos/page82.html

Logging Console in Cognos 8

I find this tool useful to read logs when dealing with security errors, tomcat logs and java errors. You can find this with in the congnos install directory \bin( logconsole.exe) http://www.ibm.com/developerworks/data/library/cognos/page80.html

Cognos 8/10 perfomance tuning

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

Cognos 8/10 Post Install tweaks and perfomance tuning

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

Script to backup Cognos content store from sql server express

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 + '

Extract Package name from search path of Cognos reports

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

Good explanation on cardinaly and how joins work in Framework manager-Cognos

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

Cognos SDK sample to update reports when a new package is published

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

How about an Online help forum?

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.

Dump files(.phd) using cognos go-excel

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.