Monday, November 21, 2011

IBM COGNOS Cube error ---- The supported limit of categories in the model has been exceeded.

Recently I was asked about a situation where a cube would run out of space. There are scenarios / wrong design where I think the cube would usually go out memory other than excess data:
1. Too many date columns J
2. Unwanted drill downs ( should always be user centric)
3. Improper usage of pivot property for the columns that are relative
4. Creating a cube from a framework / report that is build on a wrong data schema

A good example I can relate to here, is the default date column that the transformer creates with date, year, quarter, month. In my case, the end users don't really use quarters in their reporting. When I removed quarters for couple of date columns, the cube size drastically changed and even the performance improved.

In the first image here, the error was due to large amount of data and excess date columns on the cube. The design was just ridiculous. Once I removed the quarter it did solve the issue, temporarily. Sometimes it is better to educate the end user and make them use a canned report based on the cube rather than letting them use the analysis studio or query studio on the cubes. Just a thought.

Best practices ---- it is always good to have a session bi-weekly or monthly with a group of end users and design  / update the cubes according to the changing needs. (with the business modules being so aggressive now a days, I am confident the requirements change from day to day). I understand people are reluctant to change but that is where proper education comes to place. I have seen the impact of proper education to a business process and approach. Business Intelligence tool like IBM COGNOS is not only designed for reporting needs but also for decision making.

Friday, November 4, 2011

IBM COGNOS 'sqlPrepareWithOptions' staus='-69' Error --- explaining the issue right!!!

I was working on a report where we needed some SQL based query subject that had to be joined in with metadata based query subject. Usually, I write the SQL queries on an editor; check it once and then put it on the report studio but this was one of those cases where I am trying to finish up things.

In the query I wrote, I had used   'max (case …..) 'Function; basic syntax on SQL query is that when we use an aggregate function we need to have a 'group by' function at the end. There is nothing new in this, the only thing that surprised me was that COGNOS error -69 details, explained the issue perfectly. I was so impressed, don't know why but made me happy. J