Jasper Reports

It's somewhat off-topic but recently I've been working with Jasper Reports which free and open-source and its capabilities blow my mind.
I can see why Cognos is easier to use in some cases... on the other hand you need to look at the price-benefit ratio too!
Is there anyone into open-source BI?

Cognos Express

I just read about Cognos entering the small-to-medium size business marketplace with a new product (new packaging?) called Cognos Express.
Have you played with it?
What do you think?
Please share your thoughts here...

I'm Back Doing Cognos Again

Hi all!

after a 6 month pause I'm back working with Cognos again.

Please expect some posts on how to build a dashboard and how to solve incremental cube building with Transformer.

Sic.

Parameter Maps on 8.3

I've been using parameter maps to implement data level security on Cognos 8.3
After some Cognos patches were installed my reports stopped to work.
It took me quite a while to trace the problem back to my use of parameter maps.
(The error messages did not help - business as usual.)
The parameter map I'm using is based on a SQL query. I think this is where Cognos got mixed up...
My queries are dimensional queries working off PowerCubes.
I have a feeling this did not mix well with the SQL in the parameter map.

I found a way to fix it... cannot really explain why it works but it did... so I'm sharing it here, maybe it can help someone.

I changed the value of the "Use for Parameter Info" proprty of the queries.
On the prompt page I set it to "Yes" and on the report I set it to "No"
This fixed the problem.

If anyone has related experience please share it in comments!

From DMR to "Real" OLAP - part 2

There are a couple more things that came to my mind about the differences between using a DMR model and using PowerCubes.

Roll-up along different dimensions

DMR allows you to specify different roll-up functions along different dimensions.

e.g. a metric called DailySales would use SUM() to roll up along the branches dimension to give the total amount collected in sales on a day but would use AVERAGE() along the time dimension to give the average amount collected in sales per day during a week, month or year.

This doesn't work in PowerCubes. It's not supported.

My first thought was that the lack of custom roll-up functions is a pretty big deal... then I figured I can live happily wihtout them. In my experience I could do everything using SUM().
For an average calculation I ended up having two metrics and doing a little calculation in the reports.

e.g. continueing the above example you could have sales_total and days_count metrics. For days_count you don't even need a metric, you can just calculate it in the report - unless you want to exclude holidays, store closures things like that.


Sorting of members in a dimension

A huge lacking feature in DMR is that there is no guarantee on the order of members in a dimension. Functions like previousMember(), nextMember() are not supported, they will return whatever they want. There is no way to specify sorting of members.
This is supported in PowerCubes which makes using dimensional expressions much safer.

Please also see part 1 if you haven't yet...

Automaticly Growing Time Dimension in Database

The following script works in Oracle and creates a table - actually a view - to be used as time dimension.

create or replace view timedimension
as
select
to_date('2009-01-01', 'YYYY-MM-DD') + level - 1 dt
from
dual
connect by
level <= trunc(current_date, 'DDD') - to_date('2009-01-01', 'YYYY-MM-DD') + 1;
/

The table contains one record per day and the dates run from 2009-01-01 till present, keeping it as small as possible - which will come handy when joining in queries.

Crosstab Column Headers

Previously I described a technique to add column headers to crosstabs and make them look like lists.

I found that this method sometimes leads to empty cells on the right hand side of the crosstab - I believe due to a bug in Cognos.

Here I describe another way to do it that works fine.

• Add an empty first row to the crosstab by bringing in Crosstab Spaces.
  
• Set the text of these... add one for each column on the left hand side of the crosstab.
  
• Set "Define Contents = Yes" for all the facts cells in this first row
  
• Unlock the editing (lock icon)
• Drag a Text Item into each member cell and set the text to be the column heading of each column in the top (horizontal) side of the crosstab
  
• Hide the topmost row of the corsstab by setting Box Type = None

a crosstab that looks like this:

                                        | measure1 | measure2
----------------------------------------+----------+---------
dimension member 1 | dimension member 2 | <1234>   | <1234>
                   +--------------------+----------+---------
                   | dimension member 2 | <1234>   | <1234>
                   +--------------------+----------+---------

will look like

                                           | measure1 | measure2
                                           |          |
-------------------------------------------+----------+---------
       crosstab space | crosstab space     | textitem | textitem
             member1" |      "member2"     |"measure1"|"measure2"
----------------------+--------------------+----------+---------
   dimension member 1 | dimension member 2 | <1234>   | <1234>
                      +--------------------+----------+---------
                      | dimension member 2 | <1234>   | <1234>
                      +--------------------+----------+---------