Best Practices with IBM Cognos BI

If Ansi outer join SQL leads to different results on Informix…

2011-05-18T13:15:00.006+02:00

During the migration of a reporting platform with native Informix SQL Syntax (especially the informix outer extension syntax) to an ansi compliant system (for example Cognos) you probably may encounter problems. There is no 1:1 equivalence for the outer extension syntax in informix to ansi sql. Therefore we have to workaround those cases to elimante different results.

But what is exactly the difference? A short example should help you to understand the issue with outer joins.

Our data (2 Tables):

Simple Ansi left outer:

SELECT x.c1, x.c2, y.c1, y.c2
FROM x LEFT JOIN y ON x.c1 = y.c1

Result:

Now the same example with a filter applied on the outer table leads us to the issue.

Informix syntax (what we want to re-write in ansi):

SELECT x.c1, x.c2, y.c1 AS yc1, y.c2 AS yc2
FROM x OUTER y
where x.c1=y.c1 AND y.c2='a'

Not (!) the same as (ansi left outer) :

SELECT x.c1, x.c2, y.c1, y.c2
FROM x LEFT JOIN y ON x.c1 = y.c1
WHERE y.c2='a';

Result (Ansi):

Correctly translated ansi syntax:

SELECT x.c1, x.c2, derived_y.c1 AS yc1, derived_y.c2 AS yc2 FROM x
LEFT JOIN (SELECT c1, c2 FROM y WHERE c2='a') AS derived_y ON x.c1 = derived_y.c1;

Result (Informix):

Here we are. As you can see we make use of the derived table syntax to workaround this issue. Unfortunately Informix 9.x does not support the derived sql syntax. In addition: I always recommend to make use of the latest current informix server version where possible because many ansi sql issues (mostly performance) were adressed in the version 11.x and above

Sorting in Business Objects vs. IBM Cognos

2011-01-09T19:45:00.003+01:00

By migrating Reports from Business Objects to Cognos you should keep in mind that the sort algorithm differ a bit. Especially string fields with non breaking spaces will be sorted different in Cognos. Upper and lower letters do net get considered. This also applies for local processed group functions (min, max) etc.

\Data\cqe\RTModels is growing very large

2010-04-12T14:04:00.001+02:00

For most customer the Cognos 8 runtime model directory which is only a temporary buffer pool grows very large (tons of gigabytes) after a while. Unfortunately there is no way to prevent the directory from growing. Usually this directory is designed to clean itself but depending on the usuage this cleanup does not work in most cases.

So, can this directory be cleared manually?

Yes, you can flush the directory, but the Cognos 8 Dispatcher and Content Manager Service has to be shut down at this time. Make sure that you delete only the contents (including subdirs), not the directory itself.

Cognos 8 Report Studio report based drill through

2010-04-12T13:37:00.002+02:00

Unfortunately, it is currently not possible to decide on a per report base which user is able to do an drill through and which not. So, if you want to limit the access to the drill through functionality per report AND by user or by row we need to do a trick. The drill through report uses usually parameters which can be (if you know the syntax) also feeded by url. By the way, drill through by direct url is even faster than the internal function of cognos 8, since Cognos 8 has to validate the request before executing the drill through report. So, if you need to save some seconds during drill through this is also an option. Details on how to open a report by url and feed the required prompt parameters can be read in the Cognos 8 BI Report Studio Guide. Before using such an feature, make sure that a misuse of the parameters isn´t possible (data security)…

Report picklists and data security

2010-04-12T09:54:00.002+02:00

Many Customers are using report picklists (with static or dynamic values) to limit the access of data. By doing this please keep in mind that the only way to secure your data access is to insert a filter in the FM Model and not in the report.

Do you know why? ;-)

Multiple CSV Output in Cognos 8

2010-04-09T10:18:00.003+02:00

On common problem in the daily business e.g. during the migration from Business Objects to Cognos is the export of multiple csv files from one single report. In full clients this is easily possible, heavily used and well accepted (for example by Business Objects Users)

In Web applications which are based on a HTTP GET Request it is unfortunately impossible to accomplish such a feature since it is only possible to support one single file transfer through one single HTTP request. Therefore IBM Cognos 8 does by design return only the CSV File for the first query (alphabetic order). All other Queries will be ignored.

So, what is the recommend way to solve the users problem?

One possible way to do this would be to create a forward page which is able to decide based on the requested output format if the user should be forwarded to the normal report layout or a CSV helper page. The helper page itself will ask for the meaningful name or description of the query which should be exported.

After answering the query question the user will be forwarded to a simple csv report and is able to save the output. For using this functionality the requesting IBM Cognos Consumer has to have the right to execute reports with embedded HTML containers, otherwise the user will get an error message saying that his user rights are to low to execute such kind of report.

Overall this solution is an acceptable workaround for reports which can´t be fully exported to CSV in IBM Cognos yet. Nevertheless if the output is needed for an daily progress it could be an option to save the csv output with an IBM Cognos SDK program on a central fileshare in the network rather than exporting the data manually. For sure also the use of the xml output format instead would be an good workaround.

Google Maps Polygon overlay with IBM Cognos 8

2010-01-13T17:26:00.001+01:00

Sometimes customers are interested to display a map of regions or countries with conditional coloring applied. But those map extensions and programs are quite expensive… I want to present you a way where you can do the same - for no money - with all the advantages of a public and open map engine.

The requirements:

an existing internet connection
a version of the epoly.js file
the coordinates preferably in xml format.

First of all we need to generate a key to authenticate our site properly to google. Using a key generated for a different site than our will fail with an error message.

The signup can be done at:

http://code.google.com/intl/de-CH/apis/maps/signup.html

Then we need a placeholder in our report like this

The map gets initialized by

function resizeMapContainer(w,h)
    {
        $("#map_canvas").width(w).height(h);
        map.checkResize();
    }

</script>

If the browser is ready and compatible we start

$(document).ready(function () {
if (GBrowserIsCompatible()) {
    var polys = [];
        map = new GMap2(document.getElementById("map_canvas"));
        map.setCenter(new GLatLng(42.16,-100.72),4);
        map.addControl(new GScaleControl());
        mapOv = new GOverviewMapControl();
        map.enableScrollWheelZoom();
        map.enableDoubleClickZoom();
        map.enableContinuousZoom();
        map.setUIToDefault();
        // Workaround resize Issue with Google Maps (API 2.x)
        window.resizeBy(-1, -1);

Now we want to make the polygons…. We read the coordinates from the states.xml file and put an overlay on the map….

// Read the data from states.xml
      var request = GXmlHttp.create();
      request.open("GET", "states.xml", true);
      request.onreadystatechange = function() {
        if (request.readyState == 4) {
          var xmlDoc = GXml.parse(request.responseText);
          // ========= Now process the polylines ===========
          var states = xmlDoc.documentElement.getElementsByTagName("state");

          // read each line
          for (var a = 0; a < states.length; a++) {
            // get any state attributes
            var label = states[a].getAttribute("name");
            // read each point on that line
            var points = states[a].getElementsByTagName("point");
            var pts = [];
              for (var i = 0; i < points.length; i++) {
               pts[i] = new GLatLng(parseFloat(points[i].getAttribute("lat")),parseFloat(points[i].getAttribute("lng")));
             }
                             if (states[a].getAttribute("name")==labels[a])
            {
            var poly = new GPolygon(pts,"#000000",1,1,colours[a],0.5,{clickable:false});
                polys.push(poly);
               map.addOverlay(poly);
            }
            }
                   // ================================================
        }
      }
      request.send(null);
          }
        });

In my case the overlay result looks like this. States are colored by a revenue rank based on the Gosales DW Model. „Pretty cool!“

and even movable and zoomable !

Since the rendering and applying of the polygons is done on client side you should not do this for reports which are running on an old machines and / or slow internet connections.

You should somehow make sure that your report data matches the appropiate data in the xml. Best would be a matching id in the xml file according to the id to the data in the report. In my example I helped myself out by just checking the name of the state

if (states[a].getAttribute("name")==labels[a])

(labels[a] holds the report result name and colours[a] the suitable color for that region – if those match the state can be colored)

If you have questions do not hesitate to contact me.

Hv fun!

IsC8Up - IBM Cognos 8 Heart Beat Checker

2009-09-15T10:40:00.011+02:00

Today, I am proud to present you a self written program which checks your IBM Cognos 8 Environment for its availability. The programm is completly freeware, so don´t hesitate to install and use it. It is able to check your Cognos 8 Gateway, Dispatcher and Services for issues and notifies you by mail in case of an problem.
The check interval is 5 Minutes. Error messages -if available - will be included in the email body.

The program can be executed as windows service or as GUI.
The program is free to use. Advanced options can be developed on request.
If you have such an request or an error message don´t hesitate to contact me by mail.

Download -> IsC8Up
(Windows 200x, Win XP, Vista) (. NET 3.x)

IBM Cognos 8 Sicherheitsgrundlagen

2009-09-10T13:17:00.006+02:00

Below an interesting article for Cognos security questions. The document tries to clarify the following : "What is the best way to implement security in Cognos" and "What you should have look at, when you implement security in Cognos".

Unfortunately the article is only available in german at the moment.

IBM Cognos 8 Sicherheitsgrundlagen

IBM Cognos TM1 quick integration guide for Cognos 8.4 BI

2009-08-19T13:26:00.013+02:00

Currently as of Version 9.4.1x of IBM Cognos TM1 the documentation
for using CAM Security in TM1 is unfortunately incomplete and /or misleading.
Therefore I decided to write you a quick integration help.

Install and configure IBM Cognos 8.4 on your system.

Install IBM Cognos TM1.

Install C8_TM1_Portlets_win32_8.4.28.4 to your Cognos 8.4 Directory

IBM Cognos Configuration

Turn anonymous access "off"

Add the TM1 server name to the valid domain list (CAF)

Enable the share of session information for IBM Cognos 8

remember your internal dispatcher URI and gateway URI

TM1

Locate your TM1 Instance which you want to get work with IBM CAM Security (for example (\TM1\Custom\TM1Data\PlanSamp)

Locate and open the file tm1s.cfg

ServerCAMURI=your internal dispatcher URI

dispatchClientCAMURI=your IBM Cognos 8 Gateway URI

ClientPingCAMPassport=900

CAMPortalVariableFile = portal\variables_TM1.xml

Change the IntegratedSecurityMode to 4

Locate and open the variables_TM1.xml file

<urls>
<url>http://your_tm1_server/TM1Web/TM1WebLoginHandler.aspx</url>
<url>http://your_tm1_server/TM1Web/TM1WebMain.aspx</url>
</urls>

Restart your TM1 Server and test the login.

Attention:

If I missed something let me know!

Hv fun!

Look behind IBM Business Viewpoint with IBM Cognos 8.4 BI

2009-08-17T12:46:00.025+02:00

Some days ago I had the chance to test IBM Cognos 8 Business Viewpoint and want to take the chance to write here some thoughts about it.
BV gives business users the ability to maintain, govern and share dimensional information from various sources (for example: csv files, excel and IBM Cognos 8 packages)

Even at early stage, it is a great tool for those aspects.
I am also pretty sure that this topic will get much higher priority in the upcoming years.
Surely, due to this early release of an MDM solution, IBM will have the chance to gather important feedback from customers while competitors (like Microsoft) still not have their products on the market. This is a huge benefit for IBM.

Installation:

After the installation of version 8.4.27.39 I got the message stating that the dll file "msvcp71.dll" is missing. Why?

The problem could be easily solved by copying the file from an existing bin directory of Cognos 8.4 to the bin of IBM Business Viewpoint. Apart from that, the installation and configuration of the tool was very handy. I don´t know why they did´nt implement a feature "test connection" for the database / content store as it is already implemented in IBM Cognos 8 BI but if you configured everything correctly the produt starts without any problems.. :-) Maybe they will implement this feature in a later release?

The service name "Tomcat Server" can not be renamed in the IBM Cognos Configuration and default to IBM Cognos 8 Business Viewpoint.

Usability:

IBM Business Viewpoint has a new sexy,easy and understandable desinged Web Interface for the administration of the product.

All dimensions are imported into the same workspace. This may result - like in my case - in a high memory consumption on the client side. In addition end users may get confused since the don´t know what to edit. This could be handled through security but i would rather expect somehting like projects or solutions for editing.

After modification of the GOSalesFact_EN_MSAS2005 (Sample Package) it was not possible to apply the new dimension structure back to cognos connection (as by design). Trying to publish changes to the same package, the original one will be overwritten. But the new package is incomplete. The facts are missing. The new version will be unusable for any use in production. Publishing to IBM Cognos Connection makes just sense with a new package to verify your changes. If you are finished with you work you can export the new dimension structure to a flat file or a new framework manager model.

There are many types of different views available for editing. Trees, Lists, etc.
I highly recommend to install the available hot fix package C8_BV_SERVER_8_4_Win32_FP001 at minimum. I had terrible memory leaks within Internet Explorer 6.x with earlier versions of the product.
My IE needed more than 600 MB of RAM! Trying to publish all dimensions from the GOSalesFact_EN_MSAS2005 Package at a time resulted in Error:

" java.lang.IndexOutOfBoundsException: Index: 0, Size: 0"

Looks like the developers did not expect many modifications at multiple dimensions or the publish of many dimensions at a time.

Conclusion:

IBM Business Viewpoint is one of the first products on the market for professional Master Data Management. Business Users don´t need any in depth technical knowledge to make use of it.
Applying new dimensional data to the underlying sources does not work automatically (In my Case a MSAS Cube) The product is still at an early stage.
I am happy to see further improvement in error handling and functionality of this great tool.

IBM Cognos 8.4 BI 64 Bit vs 32 Bit Benefits and Peformance

2009-08-13T14:10:00.081+02:00

Actually one hot topic in the business is the use of 64 Bit Operating Systems and x64 programs.
Since the release of IBM Cognos 8.4 BI Customers and partners of IBM may also choose between a 32 bit or 64 bit version of their product.
But what are the differences and the benefits of those?
Since not all customers are fully aware about the differences I decided to do some tests combined with giving some more detailed information about the product.

First of all, some of you may be dissapointed because its not the entire product which is 64 bit, just it´s application engine (Web Interface also known as Cognos Connection) runs in a 64 bit application server. The report validation and processing engine (which is written in c++) still runs in a 32 bit mode.
This means that currently "only" web requests in a 64 Bit IBM Cognos environment can be handled faster and more efficient through the 64 bit java application server. Surely the overall main logic may benefit from the greater adress space and faster handling of requests but forget the idea to install a 64 Bit IBM Cognos 8.4 Server to get rid of some long running reports without changes on database side.

But let´s go to the details :-)

The environment:

32 Bit Version
Intel Core 2 Duo processor with 2.4 GHz
4 GB of RAM
Windows 2008 Server Standard Edition (32 Bit)
SQL Server 2005 Developer Edition with SP3 (32 Bit)
Cognos 8.4 (32 Bit)

64 Bit Version
same Hardware
Windows 2008 Server Standard Edition (64 Bit)
SQL Server 2005 Developer Edition with SP3 (64 Bit)
Cognos 8.4 (64 Bit)

The test:
3 new Report Studio reports based on MSAS 2005 and the Cognos sample package GOSalesFact_EN_MSAS20052 new Report Studio reports based on SQL Server 2005 and the Cognos sample package GO Data Warehouse (query)were run in at least a minimum of 70 to 100 iterations. The fastest report ran in 37 seconds, the most time consuming report completed after 9 minutes for one execution.All values are based on averages given from the No. of iterations.

To get an reproducable test case by avoiding any caching or other side effects I decided to use the rstest utiltiy which inherits the main logic of report processing. This tool is often used internally by IBM Cognos Information Management people and has some interesting features. You can find this tool in the Cognos 8.4 bin directory.

The results of rstest may not be comparable to an execution within Cognos Connection, since the bibustkservermain process caches a lot of data. In other words the test results should reflect a "first time execution in Cognos Connection" and therefore the maximum processing time a report may need. As already pointed out, the rstest and bibustkserver processes are both 32 bit programs.

Looking at the result table above, you may raise the question why the elapsed time for the 64 bit tests were always lower then the 32 bit values?
Answer: The database server and olap server (both 64 bit) were able to return the results faster back to the IBM Cognos report engine, this caused a faster execution.

The last column which is calculated from the rstest cpu time vs total elapsed shows clearly that the time the IBM Cognos process needed to finish was even higher than on the 32 bit system.
One reason for that could be the emulation for the 32 bit processes on a 64 bit system. Those emulations cost surely some time.

Conclusion:

64 Bit is not necessarily faster than 32 bit. The 64 Bit version of Cognos 8.4 BI may improve performance for Cognos Connection activitites, especially if you have a high number of web users which are heavily using Cognos Connection. But don´t expect that your reports will run faster because of the installation of a 64 Bit version of IBM Cognos 8.4 BI.

IBM kauft SPSS für 1,2 Millarden Dollar

2009-08-11T10:18:00.003+02:00

IBM hat sich zum Ziel gesetzt seinen Analytics Bereich weiter auszubauen und kauft den ehemaligen SoftwarePartner SPSS. IBM wird die Firma im Bereich Information Management platzieren. SPSS eine Firma die Hauptsächlich im Bereich der Auswertungen von Meinungsumfragen und Fragebögen bekannt ist, steht in direkter Konkurrenz zu SAS.

IBM ergänzt sein bestehendes BI Portfolio mit einer perfekten Predictive Analyicts Lösung und ist somit auch bestens für die weiter stattfindende Konsolidierungswelle gerüstet.

Sollte es IBM wirklich schaffen die Tools von SPSS nahtlos in Ihr bestehendes Portfolio zu integrieren wird es zukünftig für die grössten Konkurrenten Oracle, SAP, Microsoft schwer werden IBM den Rang als "beste ganzheitliche Lösung" abzulaufen.

Ich bin gespannt wie es weiter geht...

Audit Informationen werden nicht immer geschrieben

2009-06-12T15:44:00.012+02:00

Falls man sich wundert warum trotz korrekt eingestellter IBM® Cognos® 8 Konfiguration plötzlich kein Auditing und keine Informationen mehr in das cogserver.log geschrieben werden, so wird man bei näherer Suche im cogserver.log evtl. den Hinweis finden, dass der für den Log Server Port verwendete Port noch in Benutzung ist.

Dieses Szenario passiert relativ häufig in Produktionsumgebungen, wenn der IBM® Cognos® Service auf dem System zu schnell neugestartet wurde. Dies kann bei Windows relativ leicht passieren, wenn man auf die Schaltfäche "Neustarten" drückt anstatt den Dienst zu stoppen und ein paar Sekunden zu warten um anschliessend neu zu starten.

In Produktionsumgebungen empfiehlt es sich also immer erst bis zum vollständigen Abbau aller BiBus Prozesse zu warten... was in der Regel auch ein paar Sekunden dauert.

Alternativ kann man natürlich auch gleich ein Batch schreiben, welches einem die Adminitrative Tätigkeit des Bereinigens der wichtigesten Temp Verzeichnisse erledigt.

Wichtige Temp Verzeichnisse sind z.b. \data\cqe\RTModels und \temp (das \temp Verzeichnis kann auch in IBM® Cognos® Configuration auf einen anderen Ort definiert werden). Diese sollten aber nur bereinigt (die Verzeichnisse selbst dürfen nicht gelöscht werden!) werden, wenn der Dienst heruntergefahren wurde.

Vertical Text with IBM® Cognos® Report Studio 8

2009-06-11T16:27:00.019+02:00

Eine oft gewünschte Funktionalität in IBM® Cognos ® Reports ist, dass Texte (wie in Excel) vertikal dargestellt werden sollen. Diese Funktionalität ist durchaus (wenn auch sehr eingeschränkt) möglich in IBM® Cognos ® 8.

Die dargestellte Methode ist nur in IE ab Version 6.x darstellbar und - da wir hier html tags verwenden - nur im HTML Format sichtbar.

Nachteile: Begrenzt auf HTML Layout in IE. Schlecht lesbar mit vielen Schriftarten.

Vorteile: Texte werden nach wie vor in PDF etc. dargestellt. Sie werden lediglich horizontal und nicht vertikal angezeigt.

*For Questions regarding to IBM® pls read the Legal Notice

Speicherplatzverbrauch und BIBusTKServerMain_*.dmp Dateien

2009-06-11T13:26:00.009+02:00

Häufig habe ich schon das Szenario bei Kunden erlebt, dass der zur Verfügung stehende Speicher auf der Partition des Installationsverzeichnisses von Cognos® 8.x fast vollständig aufgebraucht war.

Aus diesem Grund ist es ratsam die Dumps im Fehlerfall für den BiBus Prozess auszuschalten.
Im normalen Betrieb "sollte" der Prozess keine Dump Files schreiben, denn diese werden ja (wie der Name schon sagt) nur geschrieben, wenn eine Exception in der Berichtsverarbeitung (oder Validierung) aufgetreten ist, die der BiBus Prozess nicht abfangen kann. (In der Praxis kann dies aber leider je nach Umgebung relativ häufig passieren)

Die Datei zum Abschalten der Dumps liegt im configuration Verzeichnis (Version 8.4) und nennt sich
cclWin32SEHConfig.xml

Die Datei ist gut dokumentiert der Standardwert lautet wie folgt

<configuration><env_var value="2:2" name="CCL_HWE_ABORT"></configuration>

und sollte auf

<configuration><env_var value="0:0" name="CCL_HWE_ABORT"></configuration>

geändert werden um die Dumps vollständig auszuschalten.

Die Änderung ist mit dem nächsten Start des / der Bibus Prozesse gültig. Der Application Server muss also nicht vollständig heruntergefahren werden.