Rollup issue??

[Sumeru]

Hello folks, i am working on a report which is based off of the cognos cube whose source is an FM package.this report is also a source report i.e it has a drill thru definition to a detail report based off of relationl package.

my report is counting the number of report failures over a period of time.so what i did was i created a query item in FM on column "Report Status" with if then else stmt where a failure should return 1. and i used this query item as a measure in Transformer and i set its rollup to Sum assuming that it would serve my need. but for some reason the figures that i see from cube doesnt match with figures frm FM package.

any ideas/suggestions where i'm going wrong or to have a look at?

[cognostechie]

So your FM Model is querying the Content Store?

Since your result is an integer value (1 or 0), check the 'Precision' property of the measure in Transformer. Though there could be a no. of reasons why your Cube would not match 100% with the FM package (and by the way, this is a common problem in Cognos world and many consultants tell the customers that the Cube is close to the actual data and get away with it !) but the Precision setting is one of major reasons why this happens. Precision controls how the rollup is performed using a certain precision for rounding off from lowest level to the highest level. Even the lowest level in your Cube may not be the actual lowest level for the FM package and hance the need for drill-thru so even the lowest level in the cube is aggregated.

What no. do you have in the precision property ?

[Sumeru]

CT, the Precision property is set to 0 and i never thought it would impact the result set since all its getting is '1' which i thought would rollup accordingly to the hierarchy.
what would you suggest to set that to?
and what is the explanation that people give when they say data is "Close", i mean Cube doesnt generate any data by itself,right so how is it even possible to say that and still get away with that.
I really appreciate all the help i'm getting from you and Muppet lately.i started working on Transformer and its bumpy ride till now.Thanks again CT

[cognostechie]

Well the executives aren't technical so they tell them that the cube is dealing with millions so during rollup, the rounding off causes  the data to be a little off. This applies mainly to dollars and since the cubes are for analytical purposes instead of reporting, nobody needs pennies in the cube so they don't really care if the totals are 100 dollars off in a total of 10 million or so because it still gives them the same broad picture.

If your cube is not taking too long to build (or build it with a small data set for trial) and set the precision to 9. Just to see the results. 9 is an overkill and I would not recommend but just for making sure that is actually the problem here. If it fixes the problem, then try 3 and see if that works. Usually for a data type containing 2 decimals, 2 works fine but for no decimals, it should be higher than 2. Also if you can put the ultimate detail in the cube, do that just for checking if the data at the lowest level is the same which will indicate that the problem is only in rounding off and not in the SQL generated by FM.

[Sumeru]

CT,please correct me if i'm wrong.my understanding of precision was that it should be used when we are dealing with numbers with decimals upto what point after the decimal it should return the value,right?
so can you help me in understanding how it would help in this situation where we know for sure the number is 1.

[cognostechie]

Yes, precision should be used when you have decimals in the source field. It will determine up to what point after decimal should be considered to roll up the data, not upto what point it would display the value. However, I have had issues with Quantity which is always integer and using precision fixed it. That's why I suggested.

Again, as I mentioned earlier, this could be for other issues too, spoecially if you are using only one query to feed all the dimension and measures of the cube. That's a no-no !

Where is your data wrong? When you see the cube by itself or it does not match only between the cube and the drill thru detail report?  The latter would again mean the cube is wrong.

Try making a new report using the data source (query) that you are using for feeding Transformer.  Just presume there is no Transformer.

If you used a single query subject from FM as the Data Source in Transformer, use that in Report Studio [directly and see if the data is correct.