This discussion is archived
1 Reply Latest reply: Jul 30, 2012 5:10 PM by Segal RSS

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

RMG123 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points