Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
idelatorre
Starting Member
1 Post |
 Posted - 2005-03-21 : 14:37:14
|
Hi Everyone  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 Services 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 StationThe 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 down.Any ideas guys/gals?I will thank you a lot! Ignacio |
|
|
|
|
|
Complex Measure in OLAP