1 Reply Latest reply: Jul 30, 2012 7:10 PM by Segal RSS

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

    RMG123
      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
          Segal
          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';


          Segal