Aggretation design problem
- From: "Bob" <bob@xxxxxxxxxxx>
- Date: Wed, 1 Jun 2005 15:40:22 +1200
Hi,
The otp data rows contain batch number, test date, a fail type and a total
unit count.
So if you count for the fail types you get a distribution of failures.
To get the relative preformance of each batch you would start with total
failures / unit count for batch.
Herein lies the problem.
Data Row 1:
batchA, 1stmay2005, FailTypeF1, 500
Data Row 2:
batchA 2ndMay2005, FailTypeF1, 500
The cube has the following dimensions Batch, Time and FailType dimension.
It measures a count of FailType.
So it will correctly show a count of two for FailTypeF1 at monthlevel and a
count of 1 at day level at individual batch level.
Problem:
I want to calculate the ratio of failure count to unit count which is 2 /
500 or .4% at the month time level table or 1 / 500 at the day time level.
I can't see how to introduce the unit count into the cube.
It seems that it has to a measure so that it can take part in the calculated
member.
This means that it must either be 'counted' or 'summed'.
If it is summed it is wrong because there are two OTP data rows therefore
the figure becomes 1000. If it is counted it appears right at the smallest
time level but is wrong at higher levels.
It needs to be summed at batch level upwards so that an overall ratio of all
batch failures to total unit count can be obtained. Also obviously you would
want ratios for year x Quarter 1 etc.
I guess what I am trying to do is get a value that appears on multiple rows
in the OTP data to be treated as an independant variable but still be summed
at change of dimension level.
Would appreciate some guidance on how to generate meaningful ratios in this
situation.
thanks
bob
.
- Next by Date: Real Time BI with SQL 2000
- Next by thread: Real Time BI with SQL 2000
- Index(es):
Relevant Pages
|
Loading
