I have a requirement for a cube enhancement that I have no idea how to
implement. Any help will be appreciated. I currently have a cube that
measures our products performance when run on a job (l'll not bore you with
what the products are and it's not important). So for simplification, each
fact is a product on a job per customer. I measure the number of hours per
job and the number of failures.

The product is made up of a varying number of components. If one or more of
the components fail, I say that product failed on that job.

The enhancement request is to add a parts dimension in and this is difficult
because the user does not want job hour and product failure counts to be by
parts but still by product. This is difficult because the fact will change
to parts on a job. What they want is the cube to sum up like it is today,
but if they pull in the new parts dimension, the will see individual parts
failures.

Conceptually, here's an example of what I need...

Let's say a job has 100 hours, 3 products, and 2 of the products failed. If
the new parts dimension is pulled let's say the first failed product has 2
failed parts and the next failed product has 1 failed part. Well the tabular
output of the cube should look like this.

Hrs Failures
Job X
Product 1 100 0
Part 1 100 0
Product 2 100 1 <-- failure and hrs NOT
Part 1 100 1 summed by part but 1
Part 2 100 1 or 0
Product 3 100 1
Part 1 100 0
Part 2 100 1
Job X Tot 300 2
Job Y
Product 1 50 1
Part 1 50 0
Part 2 50 1
Job Y Tot 50 1
Grand Total 350 3 <-- across jobs, hrs and failures
summed by product not part

Please help! How do I do this? I've tried various approaches to no avail.
Can it be done with analysis services (if not, that's an acceptible answer I
can give my users too)?