IBM COGNOS 8.4 SQL Server Text field issue

We have been facing this issue where COGNOS does not support BOLB / Text. Due to this, we did update over DB table columns from Text fields to Varchar both in the SQL Server and MySQL. But the problem we are facing is:

8.3 + MySQL = Works fine.

8.4 + MySQL = Works fine.

8.3 + SQL Server = Works fine.

8.4 + SQL Server = Errors Out. Strange isn't it.

Before we changed the DB fields from Text to VarChar; we got this error

RQP-DEF-0177 sqlPrepareWithOptions status = -9

Then, even after coversion of Text to VarChar;

RQP-DEF-0403 Using a blob column in this query requires that the query subject ------- must have either a key or a unique index.

So, we added the indexes and determinants to the DB and everything was set. I wanted to take one more screen shot of the property I changed but will do that later. I am testing more scenarios in different environments; let me do that first before I finalize the solution.

Hands on experience with IBM 10

Loads to explore on IBM 10 especially with all the hype and the ads that they have been showing I was very eager to explore it. It has been only couple of hours since I installed IBM 10 but here are some of the things I found that were interesting.

1. ODBC --- Loads of new ODBC options ex….Netezza – one of their latest acquisition
2. COGNOS Insight should get a top rating, my experience on it has be minimal but I am sure I will get to get a lot on it.
3. New installation (default)path has changed --- in comes IBM -->ibm/cognos/c10
4. Yet to explore report studio which is presently missing or is it in a different manner now. (May be it required a separate installation / package)
5. Lotus Connections software
6. From the internet ---- “Cognos Active Reports, allows Cognos users who find themselves--heaven forbid--disconnected from the network to still consume interactive reports. The product is a tool for developers to create these reports. Large data sets are not supported, but even so, Cognos users have been asking for such a product (already available from Cognos' big-name rivals) for years. “
7. Info about another acquisition --- SPSS ---- “from the internet” renowned statistical engine is now included in the Cognos suite as Cognos Statistics. As an optional and integrated component of the Cognos Report Studio, Cognos Statistics provides analysts with the capability to build reports that contain statistical analysis. No longer must they leave the comfy world of the Report Studio to extract data from the data warehouse, load it into the statistical engine, and then distribute the resulting reports. It can all be done centrally.We would love to have SPSS; but I guess it will cost us more.
8. Small things like --- web colors, web icons

I will get back on this very soon.

IBM COGNOS 8.4 – aggregate currentMeasure within detail error / not totaling properly

One of my friend called me the other day and explained me a situation where the sum of a row wasn’t right. It wasn’t very clear when he initially explained the issue to me and so I asked him to email me a screen shot (the image I have put up here.)
When I first looked at the screen shot, it looked fishy to me but flashed to me instantly that this should be a report studio error and that they would have changed the property of the data item. So I emailed him back with a screen shot of the property column that should be updated
Solution  Change the property of the Summary / Aggregate (Appointment Date)
Aggregate Function --- Calculated
Roll Up Aggregate --- Total
That did the trick !!!!!

What is this UDS? and why a validation tool?

UDS means Uniform Data system. They are basically a core set of information that are required to review the operation and performance of a Health Center. We developed an exception report in order to check whether the reports that we generate are appropriate. Usually the grantees should submit their UDS report by Feb 15 of each year in order to ensure adequate time for review and correction of data errors prior to final submission which is March 31.

For more information please look at http://www.hrsa.gov/data-statistics/health-center-data/index.html#what

I have been working on UDS reporting for the past 2 years. The first year we spent a lot of time in creating the reports and now that is stable, the second year we added drill downs on the reports and updated the report with the changes from UDS committee 2009. Moving forward; the 3rd year 2010, I designed this exception report because most of the issues that we came across in 2009 we based on the work flow, undefined data, improper capturing of data etc.

Will try to put in a example of the exception report idea that we implemented.

COGNOS 8 – Page Break on a Query

We created a report called Patient Statement. This report is actually a form that is set by the state as a standard, the specialty of this report is that; we need to design it in such a way that report the client should be able to print the report and post it directly through an envelope.
Let me explain it better. The form is designed such that when we will fold the report into 3 part, the address of the patient comes exactly @ the address area of the envelope. So the end-user, runs the report, prints it out and folds it into the envelope and it should be ready to post. (Basically reduce the labour work of finding the patients address and writing / typing it on the envelope each time). The other issue was the total for each page that should be at the bottom of the A4 page even though the number of claims or payments in the report varies. So we had to format the length of the page also.
We also needed to have only 1 patient per page. So in order to make this simple Page Break on the report, here are some steps to follow.
1. Create the list or crosstab
2. Go to the page explorer, and drag the ‘Page Set’ under the Page1.
3. Drag the Page1 under the Detail Pages (Under the Page Set1)
4. Drag the query item you want to page break on. Here in the above screen shot, you can see that I have tried to Page Break on Patient. I could have used only the Patient ID, but I was just playing around with the options. By adding multiple levels under the ‘Page Set’, we can actually break the page depending on all the condition we want to.
Such a simple but very useful option.
Look at the screen shot above to understand it better.

COGNOS 8.3 / 8.4 Report refresh issue / Data not refreshing on the report – Issue

Being in a healthcare field, there are humongous data that has to be worked on with loads of junk data also involving in it. There are some reports that a facility has to submit to the state for grants. So during this, the client does loads of testing on the reports that we generate. During this testing phase, the testers depend on particular dates for the data to verify and they run it so many times that the IE or the Cache memory in COGNOS does not refresh sometimes. So to beat this refresh problem all we need to do is:
File name: CQEConfig.xml.sample
File Location: c8\configuration
Changes to make: Open the file in a notepad, look for queryReuse and set the value = 0 from 5 at both the places.

COGNOS 8.3 Framwork Start Issue & Auto Sum feature on the metadata

First Image – Error -- > ‘Unable to access service URL’
The first thing one should check when this error pop’s up is the configuration file. We need to set the ‘Gateway URI’ to the ‘http://localhost:9300/p2pd/servlet/dispatch’ or copy paste the URL from ‘Disptacher URI for Gateway’ and restart the COGNOS services.
The other instance where I came across the same issue was when one of my friend installed Framework on an existing COGNOS instance. Usually what happens is that, when we install something new; we need to restart COGNOS. Especially when we install a framework, COGNOS automatically turn the configuration down and throws the above error when we try to open a new project.

Second Image – The auto sum feature in the query testing screen. Whenever we create a metadata we need to make sure we test the metadata with the auto sum checked, because the result set vary when the auto sum is checked from the unchecked result. The result that we get from the auto sum checked is the result that we should expect from the reports created on report studio. The auto sum I guess actually runs the report depending on the property of the each metadata like whether they are measure, attribute or anything else. But without the auto sum I think they are run individually.
Will post again on this topic.

Data Warehousing ETL experience

I got 2 new books

'The Data Warehouse ETL Toolkit' by Ralph Kimball and
'Mastering Data Warehouse Design' by Claudia Imhoff

My company is planning to buy an ETL tool; I am not sure if I will be involved in that team but I want to update myself with ETL knowledge as BI and ETL go hand in hand.

Installing Informatica will be a tough job, but let me try.