Esempi di query di Workfront Data Connect

Per utilizzare al meglio i dati di Workfront Data Connect, questa pagina contiene query di esempio di base che è possibile utilizzare per acquisire familiarità con la sintassi e la struttura di tipi specifici di query.

Query dati personalizzata

In questo esempio viene illustrato come comporre una query per restituire dati personalizzati in Workfront, ad esempio moduli personalizzati e campi personalizzati.

Scenario

La tua organizzazione utilizza un modulo personalizzato denominato Integrazione finanziaria. Il modulo viene allegato a ogni progetto e contiene i campi seguenti:

È necessario includere queste informazioni nella risposta per una query su Data Connect. I valori dei dati personalizzati per un record nel data lake sono contenuti in una colonna con titolo parametervalues. Questa colonna viene memorizzata come oggetto JSON.

Query

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

Risposta

La query precedente restituisce i dati seguenti:

Spiegazione

Quando si esegue una query sull’oggetto JSON parametervalues, è possibile accedere a ogni campo dati personalizzato come colonna utilizzando quanto segue:

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

Tempo nella query di stato

Questo esempio illustra come misurare il tempo trascorso da un progetto negli stati assegnati in precedenza. Può essere facilmente adattato per misurare il tempo di attività o problema in uno stato, oppure può essere adattato per misurare quanto tempo un oggetto ha avuto qualsiasi altro attributo (inclusi i valori di dati personalizzati) applicato.

Scenario

La leadership della tua organizzazione ritiene che tu stia passando troppo tempo in ogni fase del tuo ciclo di vita lavorativa. Prima di formulare raccomandazioni per migliorare il processo, è necessario creare una misurazione di base della frequenza con cui lo stato di un progetto cambia nel tempo e del numero di giorni in cui un progetto rimane nello stato specificato.

Stai per utilizzare la visualizzazione dati PROJECTS_EVENT per richiamare un elenco di ogni modifica di stato rispetto all’oggetto del progetto. Confronterai il nuovo stato con lo stato precedente, acquisirai l’intervallo di tempo effettivo per lo stato assegnato in precedenza e quindi calcolerai i giorni trascorsi in tale stato.

Utilizzando questo output non elaborato del tempo trascorso in ogni stato per progetto, puoi iniziare a creare visualizzazioni o aggregare ulteriormente i dati per generare le medie della durata dello stato in base allo stato, al tipo di progetto o al tempo dell’anno. Questa linea di base viene quindi utilizzata per impostare un benchmark da misurare per soddisfare le aspettative della leadership.

La query seguente utilizza la visualizzazione dati Data Connect PROJECTS_EVENTS per confrontare ogni evento di modifica dello stato del progetto e visualizzare il tempo nello stato.

Query

-- 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;

Risposta

La query precedente restituisce i dati seguenti:

Spiegazione

La query utilizza le funzionalità di rilevamento degli eventi di modifica di Data Connect e determina la data in cui è stato attivato un evento con un nuovo valore di stato diverso da quello dell’evento precedente.

Esame della query dall’interno verso l’esterno:

Pianificazione: query con tipo di record singolo

In questo esempio viene illustrato come eseguire query sui dati di Workfront Planning per un singolo tipo di record memorizzato nel data lake di connessione dati.

Scenario

La tua organizzazione utilizza Workfront Planning per monitorare le campagne. Ogni record di Campaign include un nome, uno stato, una data di inizio, una data di fine e un proprietario. Desideri richiamare un elenco di tutte le campagne attive e dei relativi dettagli chiave da utilizzare in una dashboard.

Query

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

Risposta

La query precedente restituisce i dati seguenti:

Spiegazione

I record di pianificazione in Data Connect condividono una singola struttura di tabella indipendentemente dal tipo di record. La colonna RECORDTYPEID viene utilizzata per definire l’ambito della query per un tipo di record specifico, in questo caso Campagne. Sostituire <your_campaign_record_type_id> con l’ID del tipo di record che si desidera ricercare, disponibile nelle impostazioni del tipo di record di Workfront Planning oppure eseguendo una query su RECORDTYPE_CURRENT.

I valori dei campi vengono memorizzati come oggetto JSON nella colonna FIELD_VALUES e sono accessibili utilizzando la stessa sintassi di notazione dei due punti utilizzata per i dati del modulo personalizzato:

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

I riferimenti al nome del campo devono corrispondere esattamente al nome del campo definito nella configurazione del campo Tipo di record Planning, incluse le maiuscole, la spaziatura e le emoji.

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

Planning: query tipi di record connessi

In questo esempio viene illustrato come eseguire query sui dati tra due tipi di record di Planning connessi, ovvero un tipo di record padre e un tipo di record a cui è connesso.

Scenario

La tua organizzazione collega i record di Campaign ai record Tattici in Workfront Planning. Desideri generare un rapporto che mostri ogni campagna insieme ai dettagli chiave delle relative tattiche associate. Vogliono mostrare in modo specifico il nome tattico, la priorità strategica e l’allocazione del budget in modo che la leadership possa rivedere l’attività della campagna organizzata per tattica.

In Connessione dati, le connessioni tra i tipi di record nativi di Planning vengono memorizzate direttamente nella colonna FIELD_VALUES_RAW di PLANNINGRECORD_CURRENT. Per un campo di riferimento denominato “Tactics”, il valore è un array JSON di oggetti record connessi, ciascuno dei quali contiene una proprietà ID con il RECORDID del record connesso. Utilizzare LATERAL FLATTEN di Snowflake per espandere l’array nelle righe e unire il record al tipo di record connesso.

Query

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

Risposta

La query precedente restituisce i dati seguenti:

Spiegazione - KP modificato

Le connessioni tra i tipi di record nativi di Planning vengono memorizzate in una tabella di join REFERENCE_CURRENT. La tabella di join REFERENCE_CURRENT viene utilizzata per i join tra RecordType. Quando si esegue l’unione tra RecordType, è necessario utilizzare il campo TO_RECORDID.

La colonna REFERENCE_ID nella vista PLANNINGRECORD contiene un elenco di tutti i campi REFERENCEID applicabili a tale record di pianificazione. Puoi accedere all’ID utilizzando la stessa notazione JSON di field_value.

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

La vista REFERENCE_CURRENT contiene uno o più record in cui TO_RECORDID punta ad altri campi di pianificazione recordId nelle viste PLANNINGRECORD_*.

Per unire un altro campo REFERENCE a record di pianificazione aggiuntivi, alla query precedente verrà aggiunto lo stesso pattern di unione di REFERENCE_CURRENT e PLANNINGRECORD_*.

Pianificazione: tipo di record unito alla query dati di Workfront Workflow

In questo esempio viene illustrato come unire un tipo di record Workfront Planning a un oggetto flusso di lavoro Workfront nativo, in questo caso un progetto, utilizzando la funzione di connessione nativa di Planning, che memorizza i riferimenti agli oggetti esterni nella vista REFERENCE_CURRENT.

Scenario

La tua organizzazione collega i record di Campaign in Workfront Planning ai progetti Workfront utilizzando la funzione di connessione nativa di Planning. Desideri generare un rapporto combinato che mostri i dettagli della campagna e i dati di esecuzione live del progetto collegato, in particolare la percentuale di completamento corrente del progetto, la data di completamento pianificata e il proprietario del progetto assegnato, in modo che i responsabili delle campagne possano tenere traccia dell’avanzamento della consegna senza uscire dal contesto dell’area di lavoro di pianificazione.

Query

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

Risposta

La query precedente restituisce i dati seguenti:

Spiegazione

Le connessioni da un tipo di record Planning a un oggetto flusso di lavoro Workfront nativo vengono memorizzate in REFERENCE_CURRENT. Ogni riga di questa visualizzazione rappresenta un collegamento direzionale: TO_EXTERNALID contiene l’ID dell’oggetto Workfront connesso. Le righe che rappresentano le connessioni Workfront sono identificate da TO_EXTERNALCONNECTIONNAME = 'workfront' e un valore TO_EXTERNALOBJECTNAME corrispondente al codice API del tipo di oggetto Workfront, ad esempio PROJ per progetti.

La colonna REFERENCE_ID nelle tabelle PLANNINGRECORD contiene un elenco di tutti i campi REFERENCEID applicabili a tale record. Puoi accedere all’ID utilizzando la stessa notazione JSON di field_value.
Un singolo REFERENCE_ID nella tabella PLANNINGRECORD_CURRENT può contenere uno o più collegamenti di riferimento nella tabella REFERENCE_CURRENT che collegano oggetti di un tipo di oggetto specifico nella tabella Workfront.

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

Si noti che le viste di Planning (PLANNINGRECORD_CURRENT, REFERENCE_CURRENT) risiedono nello schema WORKFRONT.PLANNING, mentre le viste native di Workfront Workflow (PROJECTS_CURRENT, USERS_CURRENT, ecc.) risiede nello schema WORKFRONT.WF. I join tra schemi richiedono nomi di tabella completi.

La query esegue tre join:

Per unire un altro campo REFERENCE ad altri record esterni, alla query precedente verrà aggiunto lo stesso pattern di unione a REFERENCE_CURRENT e alle viste del flusso di lavoro di Workfront.

I valori dei record External e Planningpossono essere uniti nella stessa query unendo più volte alla tabella REFERENCE_CURRENT e utilizzando la serie di join appropriata.