I have a measure design problem I've been trying to solve and haven't been
able to get there. I run Analysis Services 2000 Standard Edition in my
environment.

I have a cube with four dimensions:
Customer->Job: hierarchy of our customers and each job we did for them
ProductLines: each productline used on a job (can have multiple productlines
per job)
Tools: each tool used on a job (a productline can be composed of one or more
tools)
Services: each service provided on a job (each tool can provide one or more
services)

Measures:
We currently have a measure which simply counts the number of service
failures. On a job, if a service fails, it's value is 1, otherwise the value
is 0.

We need two more measures that I don't know how to implement:
ToolFailure: for each job, if the one or more services for that tool fails,
the value of ToolFailure is 1 else it's 0
ProductLineFailure: for each job, if the one or more tools for a product
line, the value of ProductLineFailure is 1 else it's 0

My problem is that I cannot simply use MAX(ServiceFailures) for ToolFailures
and MAX(ToolFailures) for ProductLineFailures because the user doesn't
necessarily filter by job. When the data is not filtered by job, the number
of service failures are summed across jobs before I can calculate the MAX()
for ToolFailures and ProductLineFailures within each job.

Thanks, Brian Parker

If you can, please respond to this design issue which I'm struggling with.

Thanks! Brian Parker

"Brian Parker" wrote:

Hi Brian,

What does the fact table in your cube represent - is there a record for
each service failure on a job? Assuming that's the case, and that
[Measures].[ServiceFailures] is the count of those records, then maybe
NonEmptyCrossJoin() can identify dimension members with associated
failures, like:

[Measures].[ToolFailures]:
Count(NonEmptyCrossJoin(
[Tools].[Tool].Members,
{[Measures].[ServiceFailures]}))

[Measures].[ProductLineFailures]:
Count(NonEmptyCrossJoin(
[ProductLines].[ProductLine].Members,
{[Measures].[ServiceFailures]}))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thank you for your feedback Puri. I actually tried something like this
originally, but the one remaining problem I'm having is I need to sum up tool
failures and product line failures within each job and I don't know how to do
that.

To calculate Tool Failures and Product Line Failures, I have to:

1) Within each job in the set of jobs being looked at, for each tool in the
run, I look if 1 or more services had failed. If 1 or more have failed, then
calculated tool failure for that tool = 1.

2) Within each job in the set of jobs being looked at, after the tool
failures have been calculated for that job, if one or more tool failures for
a product line have failed, then calculated product line failure for that
product line = 1.

3) These tool failures and product line failures then sum up as measure
normally do across jobs.

An example would be if I had 2 jobs with 1 productline that has 2 tools
each, and finally the 2 tools each have 2 services. In this example, I show
simply how the counts roll up.

product line 1 has 2 failures across 2 jobs
tool 1 has 1 failure across 2 jobs
tool 2 has 2 failures across 2 jobs

service failures are a simple count...

service 1 has 2 failures
service 2 has 3 failures

job1
productline 1 failure = 1
tool 1 failure = 1
service 1 failed
service 2 failed
tool 2 failure = 1
service 1 no failure
service 2 failed

job2
productline 1 failure = 1
tool 1 failure = 0
service 1 no failure
service 2 no failure
tool 2 failure = 1
service 1 failed
service 2 failed


"Deepak Puri" wrote:

I left one thing out of my problem description, query performance is also an
issue for me. This cube is being accessed by users via Microsoft Excel pivot
table service, so if I calculate these values without using calculated
measures, but having the values calculated when the cube is actually built,
that would be much better in my mind.

Thanks, Brian Parker

"Brian Parker" wrote: