Exemplos de consulta da conexão de dados do Workfront

Para ajudá-lo a utilizar melhor os dados do Workfront Data Connect, esta página contém exemplos básicos de consultas que você pode usar para se familiarizar com a sintaxe e a estrutura de tipos específicos de consultas.

Consulta de dados personalizada

Este exemplo demonstra como você pode compor uma consulta para retornar seus dados personalizados no Workfront, como formulários personalizados e campos personalizados.

Cenário

Sua organização utiliza um formulário personalizado chamado Integração de finanças. O formulário é anexado a cada projeto e contém os seguintes campos:

Você precisa incluir essas informações na resposta para uma consulta no Data Connect. Os valores de dados personalizados para um registro no data lake estão contidos em uma coluna intitulada parametervalues. Essa coluna é armazenada como um objeto JSON.

Consulta

SELECT
    projectid,
    parametervalues,
    name,
    parametervalues:"DE:Business Unit"::int as BusinessUnit,
    parametervalues:"DE:Project ID"::int as ProjectID,
    parametervalues:"DE:Expanded Project Name"::text as ExpandedProjectName
FROM PROJECTS_CURRENT
WHERE ExpandedProjectName is not null

Resposta

A consulta acima retorna os seguintes dados:

Explicação

Ao consultar o objeto JSON parametervalues, cada campo de dados personalizado pode ser acessado como uma coluna usando o seguinte:

<field_name>:"<parameter_name>"::<data_type> as <column_name>

Tempo em consulta de status

Este exemplo demonstra como medir o tempo que um projeto gastou nos status atribuídos anteriormente. Ele pode ser facilmente adaptado para medir o tempo de tarefas ou problemas em um status, ou pode ser adaptado para medir quanto tempo um objeto teve qualquer outro atributo (incluindo valores de dados personalizados) aplicado a ele.

Cenário

A liderança de sua organização acredita que você está gastando muito tempo em cada estágio do ciclo de vida do trabalho. Antes de fazer recomendações para melhorar o processo, você deseja criar uma medida de linha de base da frequência com que o status de um projeto muda ao longo do tempo e quantos dias um projeto permanece em determinado status.

Você usará a visualização de dados PROJECTS_EVENT para obter uma lista de cada alteração de status em relação ao objeto do projeto. Você comparará o novo status com o status anterior, capturará o intervalo de tempo efetivo para o status atribuído anteriormente e calculará os dias gastos nesse status.

Usando essa saída bruta de tempo gasto em cada status por projeto, você pode começar a criar visualizações ou agregar mais os dados para criar médias de duração de status por status, tipo de projeto ou hora do ano. Essa linha de base é então usada para definir um referencial que você possa avaliar para atender às expectativas de sua liderança.

A consulta a seguir usa a visualização de dados Conexão de Dados PROJECTS_EVENTS para comparar cada evento de alteração de status do projeto e exibir o tempo em status.

Consulta

-- Calculate the begin/end effective timestamp and duration in days

SELECT

    projectid,
    name as project_name,
    prev_status as previous_status,
    status,
    status_change_date as status_begin_effective_timestamp,
    case
        when status_change_date is null then NULL
        else
            nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)
    end as status_end_effective_timestamp,
    datediff('DAYS',status_change_date, nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)) as status_duration_days

FROM
    ( -- Filter to just the records that have changed
     SELECT projectid,
        name,
        prev_status,
        status,
        begin_effective_timestamp as status_change_date
     FROM
        (  -- Calculate records where previous status is different
          SELECT DISTINCT
           pe.projectid,
           pe.name AS name,
           pe.STATUS,
           nvl(lag(pe.STATUS) over (partition by pe.projectid order by pe.BEGIN_EFFECTIVE_TIMESTAMP), status) prev_status,
           begin_effective_timestamp

          FROM projects_event pe
         -- Set any WHERE conditions to limit the results as needed
         --WHERE
            -- pe.PROJECTID = '5ebe…c1e1'
        )
        WHERE prev_status != status
    )
    order by status_change_date;

Resposta

A consulta acima retorna os seguintes dados:

Explicação

A consulta usa os recursos de rastreamento de eventos de alteração do Data Connect. Ela determina a data em que um evento que teve um novo valor de status diferente do evento anterior foi acionado.

Examinando a consulta de dentro para fora:

Planning: consulta de tipo de registro único

Este exemplo demonstra como consultar dados do Workfront Planning para obter um único tipo de registro armazenado no data lake da Data Connect.

Cenário

Sua organização usa o Workfront Planning para rastrear campanhas. Cada registro de campanha inclui um nome, status, data inicial, data final e proprietário. Você deseja obter uma lista de todas as campanhas ativas e seus principais detalhes para usar em um painel.

Consulta

SELECT
  recordid,
  FIELD_VALUES:"Name"::text AS campaign_name,
  FIELD_VALUES:"Status"::text AS campaign_status,
  FIELD_VALUES:"Start Date"::date AS start_date,
  FIELD_VALUES:"End Date"::date AS end_date,
  FIELD_VALUES:"Owner"::text AS owner
FROM PLANNINGRECORD_CURRENT
WHERE WORKSPACEID = '<your_campaign_workspace_id>'
AND RECORDTYPEID = '<your_campaign_record_type_id>'
AND FIELD_VALUES:"Status"::text = 'Active'
ORDER BY start_date ASC

Resposta

A consulta acima retorna os seguintes dados:

Explicação

Os registros do Planning no Data Connect compartilham uma única estrutura de tabela, independentemente do tipo de registro. A coluna RECORDTYPEID é usada para definir o escopo da consulta para um tipo de registro específico — neste caso, Campanhas. Substitua <your_campaign_record_type_id> pela ID do tipo de registro que deseja consultar, que pode ser encontrada nas configurações de tipo de registro do Workfront Planning ou consultando RECORDTYPE_CURRENT.

Os valores de campo são armazenados como um objeto JSON na coluna FIELD_VALUES e são acessados usando a mesma sintaxe de notação de dois pontos usada para dados de formulário personalizado:

<field_column>:"<field_name>"::<data_type> AS <alias>

As referências de nome de campo devem corresponder exatamente ao nome de campo definido na configuração de campo do tipo de registro do Planning, incluindo maiúsculas e minúsculas, espaçamento e emoji.

SELECT
    ID AS recordtypeid,
    DISPLAYNAME AS record_type_name,
    WORKSPACEID
    FROM RECORDTYPE_CURRENT
    ORDER BY record_type_name ASC

Planning: consulta de tipos de registros conectados

Este exemplo demonstra como consultar dados em dois tipos de registro conectados do Planning — um tipo de registro pai e um tipo de registro ao qual está conectado.

Cenário

Sua organização conecta registros de Campanha a registros Táticos no Workfront Planning. Você deseja produzir um relatório que mostre cada campanha ao lado dos principais detalhes de suas táticas associadas. Eles desejam especificamente mostrar o nome da tática, a prioridade estratégica e a alocação de orçamento para que a liderança possa analisar a atividade da campanha organizada por tática.

Na Conexão de Dados, as conexões entre tipos de registro nativos do Planning são armazenadas diretamente na coluna FIELD_VALUES_RAW de PLANNINGRECORD_CURRENT. Para um campo de referência chamado “Táticas”, o valor é uma matriz JSON de objetos de registro conectados, cada um contendo uma propriedade id com o RECORDID do registro conectado. Use LATERAL FLATTEN da Snowflake para expandir essa matriz em linhas e unir ao tipo de registro conectado.

Consulta

SELECT
  c.RECORDID AS campaign_id,
  c.FIELD_VALUES:"Name"::text AS campaign_name,
  c.FIELD_VALUES:"Status"::text AS campaign_status,
  t.FIELD_VALUES:"Name"::text AS tactic_name,
  t.FIELD_VALUES:"Strategic Priority"::text AS strategic_priority,
  t.FIELD_VALUES:"Budget Allocation"::float AS budget_allocation
FROM PLANNINGRECORD_CURRENT c,
INNER JOIN REFERENCE_CURRENT R
ON r.FROM_REFERENCEID = c.REFERENCE_IDS:"Tactics"::text
INNER JOIN PLANNINGRECORD_CURRENT t
-- Join to the Tactic record using the connected record ID from the array
ON t.RECORDID = r.TO_RECORDID
WHERE c.RECORDTYPEID = '<your_campaign_record_type_id>'
ORDER BY tactic_name, campaign_name

Resposta

A consulta acima retorna os seguintes dados:

Explicação - KP modificado

As conexões entre tipos de registro nativos do Planning são armazenadas em uma tabela de junção REFERENCE_CURRENT. A tabela de associação REFERENCE_CURRENT é usada para associações entre RecordType. Ao unir entre RecordType, o campo TO_RECORDID deve ser usado.

A coluna REFERENCE_ID na view PLANNINGRECORD contém uma lista de todos os campos REFERENCEID aplicáveis a esse registro de planejamento. Você pode acessar a id utilizando a mesma notação JSON como um campo_valor.

<reference_ids>:"<reference_name>"::text

A exibição REFERENCE_CURRENT contém um ou mais registros em que TO_RECORDID aponta para outros campos recordId de planejamento nas exibições PLANNINGRECORD_*.

Para unir outro campo REFERÊNCIA a registros de planejamento adicionais, o mesmo padrão de associação às exibições REFERENCE_CURRENT e PLANNINGRECORD_* seria adicionado à consulta acima.

Planning: tipo de registro associado à consulta de dados do Workfront Workflow

Este exemplo demonstra como unir um tipo de registro do Workfront Planning a um objeto nativo do Workfront Workflow — neste caso, um Projeto — usando o recurso de conexão nativa do Planning, que armazena referências a objetos externos na exibição REFERENCE_CURRENT.

Cenário

Sua organização conecta registros do Campaign no Workfront Planning ao Workfront Projects usando o recurso de conexão nativo do Planning. Você deseja produzir um relatório combinado mostrando os detalhes da campanha juntamente com os dados de execução em tempo real do projeto vinculado — especificamente o Percentual concluído atual do projeto, a Data de conclusão planejada e o Proprietário do projeto atribuído — para que os gerentes de campanha possam rastrear o progresso do delivery sem sair do contexto do espaço de trabalho do Planning.

Consulta

SELECT
  c.RECORDID AS campaign_id,
  c.FIELD_VALUES:"Name"::text AS campaign_name,
  c.FIELD_VALUES:"Status"::text AS campaign_status,
  conn.TO_EXTERNALID AS linked_project_id,
  p.name AS project_name,
  p.percentcomplete AS project_percent_complete,
  p.plannedcompletiondate AS project_planned_completion,
  p.ownerid AS project_owner_id,
  u.name AS project_owner_name
FROM WORKFRONT.PLANNING.PLANNINGRECORD_CURRENT c
-- Join to the references table to find Workfront Project connections
INNER JOIN WORKFRONT.PLANNING.REFERENCE_CURRENT conn
ON conn.REFERENCE_ID = c.REFERENCE_IDS:"ProjectId"::text
-- Join to the Workfront Projects table on the external ID
INNER JOIN WORKFRONT.WF.PROJECTS_CURRENT p
ON p.projectid = conn.TO_EXTERNALID
-- Join to Users to resolve the project owner name
LEFT JOIN WORKFRONT.WF.USERS_CURRENT u
ON u.userid = p.ownerid
WHERE c.RECORDTYPEID = '<your_campaign_record_type_id>'
AND p.completiontype != 'CPL' -- Exclude completed projects
ORDER BY campaign_name

Resposta

A consulta acima retorna os seguintes dados:

Explicação

As conexões de um tipo de registro do Planning para um objeto nativo do Workfront Workflow são armazenadas em REFERENCE_CURRENT. Cada linha dessa exibição representa um link direcional: TO_EXTERNALID contém a ID do objeto Workfront conectado. As linhas que representam conexões do Workfront são identificadas por TO_EXTERNALCONNECTIONNAME = 'workfront' e um valor TO_EXTERNALOBJECTNAME que corresponde ao código da API do tipo de objeto do Workfront — por exemplo, PROJ para Projetos.

A coluna REFERENCE_ID nas tabelas PLANNINGRECORD contém uma lista de todos os campos REFERENCEID aplicáveis a esse registro. Você pode acessar a id utilizando a mesma notação JSON como um campo_valor.
Uma única REFERENCE_ID no PLANNINGRECORD_CURRENT pode conter um ou mais links de referência na tabela REFERENCE_CURRENT que se vinculam a objetos de um tipo de objeto específico na tabela Workfront.

<reference_ids>:"<reference_name>"::text

Observe que as views do Planning (PLANNINGRECORD_CURRENT, REFERENCE_CURRENT) residem no esquema WORKFRONT.PLANNING, enquanto as views nativas do Workfront Workflow (PROJECTS_CURRENT, USERS_CURRENT etc.) residir no esquema WORKFRONT.WF. Junções entre esquemas exigem nomes de tabela totalmente qualificados.

A consulta executa três junções:

Para unir outro campo REFERENCE a registros externos adicionais, o mesmo padrão de associação a REFERENCE_CURRENT e as exibições do Workfront Workflow seriam adicionadas à consulta acima.

Os valores de registro externo e de Planejamento podem ser unidos na mesma consulta, unindo-se várias vezes à tabela REFERENCE_CURRENT e usando o padrão de junção apropriado.