1 Reply Latest reply on Jul 31, 2012 12:10 AM by Segal-Oracle

    NPD Metrics - how to extract from the database in a usable format

      Metrics for NPD activities and projects are stored in the database one row for each metric in each activity or project instance. If there are multiple metrics per activity instance, for example, extraction of those metrics yeilds only a list of metrics and values. I'd like to see it in more of a table style output. Can you provide some guidance on how to extract this from the DB using sql? Thx
        • 1. Re: NPD Metrics - how to extract from the database in a usable format
          Here is the SQL that should help understand the structure. There are a few ways to pull this data in more of a column based approach. This should help you get started.

          select modml.baseName as MetricName, bases.name as Basis, ph.name as Phase,
          fy.name as FiscalYear, gl.Value as Value, UOM.ID as UOM, currml.Name as Currency
          from plmFieldExchangeGlobals gl
          inner join plmFieldExchangeDefinitions fed on fed.pkid = gl.FieldExchangeDefinitionPKID
          inner join plmFieldExchangeDefModels mod on mod.pkid = fed.fkFieldExchangeDefinitionModel
          inner join plmFieldExchDefinitionModelML modml on modml.fkFieldExchangeDefinitionModel = mod.pkid and modml.langID = 0
          inner join plmFieldExchangeBases bases on fed.fkFieldExchangeBasis = bases.pkid and bases.langID = 0
          inner join plmFieldExchangePhases ph on fed.fkFieldExchangePhase = ph.pkid and ph.langID = 0
          inner join plmFieldExchangeFiscalYears fy on fed.fkFieldExchangeFiscalYear = fy.pkid and fy.langID = 0
          left outer join UOM on UOM.pkid = mod.fkCommonUOM
          left outer join commonCurrenciesML currml on currml.fkCurrency = mod.fkCommonCurrency and currml.langID = 0
          where ProjectPKID = '320219992047-072a-4698-a553-de5ec124ff9c';