CubeMeisters please help.

My previous post drew no responses so I thought I would restate the problem.
There is OTP data for which an overall OLAP %value is required.
i.e. number of say bad eggs over total number of eggs gives performance of
farm.
Problem is that because there are many types of egg failures there is an OTP
data row for each failed egg
Something like :
Batch FailType
1 FailA
1 FailB
1 FailA
2 FailA
3 FailB
I have no trouble aggregrating the fails and using Batches as dimensions.
The problem comes when you try to bring the batch size into the cube.
The batch sizes are available from the OTP but I can't see how to deploy
them.

If I simply add the batch sizes to the end of the data row then they will
not sum correctly eg batch 1 in the example would a batch size 3 times
greater than the correct value because there are three data rows. Batches
two and three would actually give correct batch sizes because there is only
one data row to be summed.
So I thought that if I introduced the reciprocal of the batch size to the
data row then that would sum correctly because each row represents 1/ batch
size eggs.
So the maths to get batch size from the cube would be :
sum of bad eggs / sum of reciprocals.
Great on paper (I think) but when I run it real world it is only close. eg
expecting one batch with 14 bad eggs and reciprocal sum of .0940542886 to
give a batch total of 115 but it gives 148.

It seems to me that this is such a general problem that someone must have an
answer.
Am I wrong in thinking that the batch size has to be a measure? Can it be
brought into play in another way.
My real world cube is dimensioned by time, area->batch, failtype
So the cube answers questions like:
1) the total failures for 2004 Q4 was N
2) The number of FailtypeA in area X in 2004 was Y etc etc.
3) Batch 988 had 3 failures in total in 2004 Q4
What I need to do is relate these absolute values to the batch size at that
dimension level.
eg Q1 becomes the overall % failure for 2004 was n% which is the total sum
of failures in 2004 divided by the sum of the batch counts in 2004.
I would appreciate any pointers into solving this.
Thanks
Bob




.

Relevant Pages

  • Re: Duplicate Values -- count conditions
    ... I know I can hide duplicates but if I put a Sum or Count function in my report footer it still counts the number of files twice i.e. 1000 when I only want it to show 500. ... Totals --> Sum ... I have a report that gives me information on batch files that are run through a processing system. ...
    (microsoft.public.access.reports)
  • Re: mosquito holocaust
    ... > But THEN I read that mosquitoes only suck blood to fertilize a batch of ... > eggs, and something like one bite translates to one batch of eggs. ... In my drought sticken area I haven't seen a skeeter AKA ...
    (sci.bio.entomology.misc)
  • Re: OT: ice cream (was: Re: another Parelli training technique)
    ... How did the Splenda'd batch taste? ... I never worried about pasteurizing the eggs. ... I love egg nog made with raw milk and eggs. ... The Splenda batch was good. ...
    (rec.equestrian)
  • Re: Count only individual records
    ... undesirable effect of "duplicating" the field you want to sum. ... grouping properties and hiding duplicate values. ... BatchID DateWorked BatchCount Completed Processor ... I want to only show the batch Count on the first instance of that record ...
    (microsoft.public.access.reports)
  • Re: Pancakes For $4.99?
    ... is 2.50 a quart at my market, so that is about 1.25 per batch. ... more if you like butter. ... Eggs are sky high, a dozen here is like 3 bucks ... Now coffee prices are really escalating. ...
    (rec.food.cooking)
Loading