Calculations

[Ann]

Hi All,

I have a requirement to compute few calculated columns in my Time Dimension Table.
which is the best option to implement the same. Can some one please shed some light on the same
1. Import Table  Time_dim. Edit the same and add Calculated columns.
2. a) Import Table Time_dim
b) Create a new query Subject using Model  and create new calculations in the same.

Please guide on how the above approaches makes difference and which one is better.

[Lynn]

Personally I'd go with option 3....modify the time dimension table to include whatever additional columns are required. This will be the best performing option vs. computing things on the fly within every report or query against your model. It will also allow consistency if there is any direct query use against your database outside of using Cognos.

If that isn't an option for you, then go with option 2 and create the calculations in your model query subject. If you modify the SQL in your data source query subject then you will lose meta data caching which results in extra call backs to the database to retrieve meta data which is a negative impact on performance.

[Ann]

I did not have any option 3.

Option 1 (Import Table  Time_dim. Edit the same and add Calculated columns.)

Option 2 (a) Import Table Time_dim
b) Create a new query Subject using Model  and create new calculations in the same.
)

Did you mean Its better to go with Option 1 where the Time Dim table imported can be modified to include new data items?

[Lynn]

I was adding option 3 because that is really the best way to go about it....I know you didn't indicate that as an option.

When you import a table to FM you want the data source query subject to simply say "select * from whatever". You DO NOT want to alter that SQL to add any derived columns or filters. If this is your option 1 then don't do that. If you can instead have the database table itself altered to include additional columns with the desired calculations pre-done then do that and import it.

Otherwise create a model query subject using the imported object and put your calculations in the new imported query subject.

Hope this makes sense!

[Ann]

Thank you Lynn !!

But I would actually like to know how tmuch the both make difference. Any thing in performance?

(Modifying the datasource Query Subject  & Adding a new Model Query Subject).
Could you please shed some more light on the same?

[blom0344]

The performance penalty would not be very large, but everything defined or pushed back to the database will be the best solution. In similar fashion, using a database view makes  more sense than defining a SQL query subject. The database optimizer is much better at compiling the minimum expression required than Cognos..

[Lynn]

The performance penalty would not be very large, but everything defined or pushed back to the database will be the best solution. In similar fashion, using a database view makes  more sense than defining a SQL query subject. The database optimizer is much better at compiling the minimum expression required than Cognos..

Good suggestion to use a view if altering the physical database table isn't an option!

Proven practices say you should not alter the physical imported objects because you will lose meta data caching which results in callbacks to the database. I have never seen anything that indicates "how much" of a performance hit that causes, but to keep with best practices put your calculations in the model query subject if you cannot get the database table altered or get a view with the calculations included to import instead of the physical table.

[Ann]

Thank you so much blom0344 and Lynn.

I might go with a view creation insteda of Model Query Subject. :)