0 Replies Latest reply on Sep 21, 2016 2:07 AM by Rizwan M

Compute Median cost except the current contractor

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

ContractorContractor MedianZone Median for all ContractorsZone Median for all other Contractors
Contractor A225275280
Contractor B232.5275315
Contractor C530275225

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.