Design Question: Calculated Field

I'm trying to create an OLAP system using SQL Server 2000 Analysis
Services (AS). I want the AS cube to be based on a database with a
star schema.

I have a field called Ratio. The initial ratio value is based upon
this formula: (CurrentTimePeriodAmount -
PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
initial ratio can be manually overridden and replaced. Then the ratio
is used to calculate future Amounts.

Should I place that calculated Ratio field in the "source" database
design or should it be part of the Analysis Services cube?What you may want to consider instead of creating a new field in the
physical table, is to use a view with the calculation there. I am a
huge advocate of using views for fact tables for the reason you are
talking about below.

The problem is if the ratio is going to be replaced/overridden you
need to ask yourself do you need to create a new record for this
ration value so you can track the changes over time. Are these changes
going to be adhoc, if so does your front-end tool allow for this type
of What If Functionality. Things to ask yourself.

HTH
-todd

imani_technology_spam@.yahoo.com wrote in message news:<8be6e8.0402030738.213518f7@.posting.google.com>...
> I'm trying to create an OLAP system using SQL Server 2000 Analysis
> Services (AS). I want the AS cube to be based on a database with a
> star schema.
> I have a field called Ratio. The initial ratio value is based upon
> this formula: (CurrentTimePeriodAmount -
> PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
> initial ratio can be manually overridden and replaced. Then the ratio
> is used to calculate future Amounts.
> Should I place that calculated Ratio field in the "source" database
> design or should it be part of the Analysis Services cube?|||I want to be able to display an initial, calculated ratio. I also
want a seperate field for the user to type in a ratio. How could that
be implemented? Also, I'm considering using SQL Server Analysis
Services with some kind of MS Excel 2000 front end.

toddack@.hotmail.com (Todd) wrote in message news:<a4f4b945.0402031412.4c3173fd@.posting.google.com>...
> What you may want to consider instead of creating a new field in the
> physical table, is to use a view with the calculation there. I am a
> huge advocate of using views for fact tables for the reason you are
> talking about below.
> The problem is if the ratio is going to be replaced/overridden you
> need to ask yourself do you need to create a new record for this
> ration value so you can track the changes over time. Are these changes
> going to be adhoc, if so does your front-end tool allow for this type
> of What If Functionality. Things to ask yourself.
> HTH
> -todd
> imani_technology_spam@.yahoo.com wrote in message news:<8be6e8.0402030738.213518f7@.posting.google.com>...
> > I'm trying to create an OLAP system using SQL Server 2000 Analysis
> > Services (AS). I want the AS cube to be based on a database with a
> > star schema.
> > I have a field called Ratio. The initial ratio value is based upon
> > this formula: (CurrentTimePeriodAmount -
> > PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, that
> > initial ratio can be manually overridden and replaced. Then the ratio
> > is used to calculate future Amounts.
> > Should I place that calculated Ratio field in the "source" database
> > design or should it be part of the Analysis Services cube?