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

    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

       

      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.

       

      Thanks in advance.