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.

How to use the ‘Convert-XLS’ tool for creating a fixed length text file from an Excel / How to convert a COGNOS report to a fixed length text file

COGNOS does not support fixed length text file output. So we found this tool which converts an excel file into fixed length text file. Following are the steps involved in this 2 step process for getting a fixed length text file from COGNOS.
1. Download the software from http://www.softinterface.com/Convert-XLS/Convert-XLS.htm
2. Install it to the default location (which should be C:\Program Files\Softinterface, Inc\Convert XLS)
3. Create a simple text file (Uncomp-Original.txt) where we can define the length for each column. Something like this:
a. 1 2 [32] Space Left
b. 2 12 [32] Space Left
c. 3 8 [32] Space Left
d. 4 8 [32] Space Left
e. 5 20 [32] Space Left
f. 6 1 [32] Space Left
g. 7 8 [32] Space Left
h. 8 1 [32] Space Left
4. Now place this ‘Uncomp-Original.txt’@ the location ‘C:\Program Files\Softinterface, Inc\Convert XLS\Profiles’. Please make sure that it is ‘Profiles’ and not ‘Profile’. If the ‘Profiles’ folder does create by default please create one.
5. Now, run the ‘Covert-XLS’ tool.
6. Click on ‘Conversion Options’
7. Now that we have file in the place, we should be able to see ‘Uncomp-Original.txt’ in the drop down and just click on ‘Apply’.
8. Next we need to set the ‘Original File Format’ to [-4142] Excel Workbook (*.XLS) and choose the radio button to ‘Use Convert XLS’. Once this is done, from the ‘Folder’ under the ‘Original File Format’, choose the source file.
9. Under the ‘Convert to File Format’, choose ‘[21] Text (ASCII), Fixed Width (*.TXT)’ and click on the ‘Convert’ or ‘Play Button’.
10. You will find the output @ the specified location (Target File)

COGNOS 8.3 sqlPrepareWithOptions status=’-9’

We came across this issue very recently when we updated over ODBC driver from 3.51.12 to 3.51.27. The main reason why we upgraded the ODBC was because of the ‘Auto Reconnect’ property that is available. Especially when pulling large DB and network issues, it is advised that we have this new ODBC which can reconnect automatically when the connection is lost due to network problem or large data size.
Anyways, coming back to the error message here; the framework was also throwing this error and I was thinking that it was something to do with the joins. But later found out that, when a filter is added on to the query subject and this filter has ‘=’ to the prompt ([service date] = ?date_prompt?) then SQL bind parameter issue was coming up. So I changes it to ‘IN’ instead and things started working properly.

Even though I made the changes in the framework, the reports were still having issues. I had to changes all the filters in the errors to ‘IN’ from ‘=’ in place where the prompt was considering a ‘numeric’ filter.

I don’t know if this is best solution and the only solution to the problem but temporarily I am able to run the reports. I am pretty sure I did see this issue before for which I had a different solution then.