MS SQL Server OLAP - rate of defect discovery

Author

rate of defect discovery - suggestions please

Joey Pruett

2006-03-29, 1:30 pm

Hello all -

I'm trying to find the "rate of discovery" in a cube that contains a
daily count of defects in our products. (I've linked to a screen cap
below). I'm looking for tips on MDX functions or syntax on how to
handle this. When I think of rate, I think of rate = distance / time.
But, I'm not sure how that falls into MDX.
http://www.myweb.cableone.net/jpruett/defects.jpg

Any help is greatly appreciated.

Thank you,

Joey

Deepak Puri

2006-03-30, 3:26 am

Hi Joey,

If you could provide more details (dimensions, measures) of the cube,
that would be helpful, since the screen-shot isn't that clear. But are
you looking for something like the daily rate of discovery of defects -
which would be the sum of defects in a slice of the cube, divided by the
number of days in that slice - and are you using AS 2005?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Joey Pruett

2006-03-30, 8:27 pm

Deepak,

The measure is Defect Count, which is a Count measure of the total
number of defects in the database. Upon reflection, this may or may
not allow us to calculate a find rate unless we start bringing inhow
many new issues were found each day to the cube. The dimensions are
descriptive items about defects - where found, time, contact, person,
prioritization, resolution, severity, status, subject, type, and area.
A daily rate of discovery of defects would be great.

We are using AS2000, and plan to move to 05 sometime later this year.

Thank you for your prompt response. Any input is greatly appreciated
and will help me discuss this with our data mart designers and get the
proper questions answered.

Let me know if you need to know anything else.

-Joey

Deepak Puri

2006-03-30, 8:27 pm

Joey,

If there is a fact table record added per defect discovered, then a
daily defect discovery rate could be computed from a count meaasure,
divided by the relevant number of days. But if you also need a TimeOfDay
dimension, then the computation may be more complex, since the whole day
may not always be considered.

When using AS 2000, an important issue is whether you need the rate
metric to support multi-select of dates (and of times, if there is a
TimeOfDay dimension). If so, you may need to create a separate cube to
correctly count the days/time. But this can be combined with the
original cube in a virtual cube, so this would be transparent to users.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

--CELKO--

2006-03-30, 8:27 pm

Look up a Dover Paperback on Sequential Analysis. It is a statistical
technique developed for manufacturing. The idea is that as things come
out of the factory, we grab a random sample and get an error rate. If
it is too high or too low we adjust the sample size to see if the
feedback worked. This is a good method when the testing is destructive
-- bullets, light bulbs, car crashing, etc.

Sponsored Links