If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Problem with decimals when importing data via SQL server

Started by HBonten, 18 Oct 2006 08:58:34 AM

Previous topic - Next topic

HBonten

Hi,
I am importing data in my Cognos Planning application by putting the data directly in the corresponding im_table in SQL server via a data transformation task.
When the data contains decimals, for example the value "4.7", the data appears in the contributor application as "47".
Contributor and SQL server run on a different machine, however the servers have the same regional settings (they both use . as a decimal symbol).
Does anyone have suggestions on how to solve this?
Thanks....

Blue

Looks like a scaling or precision issue to me. 
When you import the data are the source and target data types the same? 
Have you used the "money" data type in SQL Server? This causes some Cognos apps to display the data incorrectly. 
Is the import process doing any transformation on the source data?
Can you set the precision on the data in Contributor?

Bluey
Robert Edis
Principal
Robert Edis Consulting
Rotorua, New Zealand

andrewbho

What is your data format in your IM_cube name table?  What is the format of d-list item attribute?  Do you have any precision or scaling?  The formating in the dlist is just a visual display, most people think that it the decimal place does the rounding but it doesn't.  At first glance, it has to be with the format of your dlist or something happening in your DTS process.  Worst case scenario, run your data into the same analyst cube and see what happens.


HBonten

 What is your data format in your IM_cube name table?Ã, 
The data format is varchar 4000, the field import_value. The table and these definitions are generated automatically by Contributro.

What is the format of d-list item attribute?Ã,  Do you have any precision or scaling?
The format is numeric. Scaling factor 1, 2 decimal places.

The value in field import_value is "4.7". When imported it appears as 47.

andrewbho

I called my friend in 3LS in Minneapolis and he said that they would like to get your model and database.  He has never heard of this issue before.  If you haven't done it already, please log a case with Support.  Thanks.

Zeeuw

This has to do with the regional settings in the server! In the Netherlands, we have this on a regular basis because of the dutch decimal (,) which is used by organisations and the uk decimal (.) which is used in the software. So, the best way is to have all servers, apps, desktops in the same regional settings (I would prefer uk settings to be on the safe side) and then everything should be ok!