Best way to do a calculation

I'm kinda stuck on how to accomplish the following. Help appreciated.

I have a fact table like so:

TimeByDay ProjectID TaskID %Complete ActualHours Planned Hours

4/4/07 001 002 .25 5 8

4/5/07 001 002 .30 6 8

4/6/07 001 002 .40 8 8

4/7/07 001 002 .45 8 8

I want to add a calculated measure that is the product of %Complete and the total planned hours. In my example, the total planned hours for task 002 is 32 hours (sum of all days for a given taskID and ProjectID)

I was thinking of adding the total to the fact table - which would yield alot of repetitive data.

However, If it wont kill performance - I'd prefer to have a calculated expression that computes the total hours then performs my %Complete * TotalHours calculation.

your help is very much appreciated.

Hello again JohnBGood.

If you create a calculated measure, in the cube, like MyCalculatedMeasure = [Measure].[%Complete]*[Measure].[Planned Hours]

,a client like Excel2007 would take care of creating the totals for you with this calculated measure.

You can also create a named calculation on you fact table, in the data source view, with TSQL like [%Complete]*[Planned Hours] and aggregate that, because it is hours that are possible to aggregate by sum().

HTH

Thomas Ivarsson

|||

A good way to test the impact this would have on SSAS performance is to first define the measure in a query:

Code Snippet

with member [Measures].[Estimated Hours]

as

[Measures].[Percent Complete] * [Measures].[Planned Hours]

select

[Measures].[Estimated Hours] on 0,

[Project].[Project Name].Members on 1

from [My Project Cube]

Try this with various restrictions, etc. to see how it holds up. If it behaves well, you can use the formula in a calculated member that will make this query available to all your users in their various applications.

If you run into performance problems, try using the [Measures].[Planned Hours] measure on Axis 0 (Columns) to see if the performance problem is still there. If it is, the calculation isn't the source of the performance issue.

A couple other things .... be sure to consider the format of the value you want to return to your users. You can lookup the syntax of the "WITH MEMBER" clause in Books Online and it will show you how to define formats here. Also, consider your NonEmpty Behavior. With the NonEmpty Behavior, you identify when to return an Empty value (and skip the calculation). In this scenario, you might want to skip the calculation when [Measures].[Planned Hours] is empty because a calculated value would be meaningless.

Good luck,
Bryan Smith