Compute Median cost except the current contractor

Rizwan M

Hi all,

I was successfully computing median cost for a contractor in the zones he worked in. Now I need to compare that cost with the Zone's median cost, which seems simple. But the tricky part is to calculate the Zone's median cost by excluding the current contractor.

e.g.

Contractor A     North Zone   

          Work Order-125          130

          Work Order-368          350

          Work Order-125          225

Contractor B     North Zone

          Work Order-1257       195

          Work Order-587         325

          Work Order-923         270

          Work Order-670         195

Contractor C     North Zone

          Work Order-145         865

          Work Order-380         530

          Work Order-865         280

Contractor  Median Costs

Contractor A          225

Contractor B          232.5

Contractor C          530

North Zone's Median Cost = 275

OBIEE report requirement is to compare each contractor's median with North Zone's median by excluding the data for Contractor at the time of computing Zone's median for Contractor A, by excluding Contractor B's data at the time computing Zone's median for Contractor B and so on.

Report requirement

Contractor	Contractor Median	Zone Median for all Contractors	Zone Median for all other Contractors
Contractor A	225	275	280
Contractor B	232.5	275	315
Contractor C	530	275	225

I have a Work Orders Fact table joined with Contractor Dim, Zone Dim and a Contractor Worked in Zones Dim (with a contractor and Zone unique combination, this was required to create a Contractor > Zone hierarchy), each Dim joined on the basis of surrogate key to the Fact. There are many other Dims as well.

I tried creating a new Fact table with a Cartesian of a Contractor and all other contractors who have worked in the same area while excluding the current contractor, and storing the Work Order No and amount with each line. But the number of rows become enormous.

Is it really possible to achieve this scenario with dimensional modelling ?

I would really appreciate some workable ideas.

Thanks in advance.