Complex Cube Measure

My problem is creating a cube with a very complex measures, I did it by hand
and on a relational table using oracle for a custom dashboard my company
bought, now I need to create an Analysis Services Cube and Reporting Service
s
Client to let analysts create more in-depth reports.
The problem is this, we have a very complex metric with 4 levels in its
product hierarchy:
Business Unit
->Product Line
-->Part Number
-->Production Station
The measure is FTY,at the station level it means the percentage of pieces
that went through the station without failing, the formula for station is 1
-
pieces_rejected/pieces_tested.
Now, this would be great if I could group all the pieces tested and
rejected for a particular partnumber, and apply the same formula but to
obtain the FTY of a partnumber I have to multipy the FTY of each of its
stations, example:
Part Number 12345-001-00 FTY=0.8 * 0.5 * 1.0 = .4
->Welding Station PiecesTested=100 PiecesRejected=20 FTY=0.8 (80%)
->Assembly Station PiecesTested=1000 PiecesRejected=500 FTY=0.5 (50%)
->Test Station PiecesTested=100 PiecesRejected=0 FTY=1.0 (100%)
For the Product Line FTY we have to average all of its part numbers and
for the Business Unit we create an average of its product lines.
I don't know how to design my FTY measure so that it will take into account
that there is a different calculation for each step in the product drill dow
n.
Any ideas guys/gals?
I will thank you a lot!
IgnacioIf [PiecesTested] and [PiecesRejected] are base measures of a
[MetricsCube] with a [MetricsDim], then the metric: [Measures].&
#91;FTY]
could be defined like:
[vbcol=seagreen]
With Member [Measures].[FTY] as
'iif([MetricsDim].CurrentMember.Level.Ordinal <
[MetricsDim].[Part Number].Ordinal,
Avg([MetricsDim].Children,
[Measures].[FTY]),
iif([MetricsDim].CurrentMember.Level is
[MetricsDim].[Part Number],
Exp(Sum(Filter([MetricsDim].Children,
Not IsEmpty([Measures].[PiecesTested])),
Log([Measures].[FTY]))),
([Measures].[PiecesTested] - [Measures].[PiecesRejected])
/[Measures].[PiecesTested]))'
FORMAT_STRING = 'Percent'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!