Workfront Data Connect 쿼리 예제

Workfront Data Connect 데이터를 보다 효율적으로 활용할 수 있도록, 이 페이지에는 특정 종류의 쿼리의 구문 및 구조에 익숙해지는 데 사용할 수 있는 기본 예제 쿼리가 포함되어 있습니다.

사용자 지정 데이터 쿼리

이 예에서는 쿼리를 작성하여 사용자 정의 양식 및 사용자 정의 필드와 같은 Workfront에서 사용자 정의 데이터를 반환하는 방법을 보여 줍니다.

시나리오

조직은 재무 통합이라는 사용자 정의 양식을 사용합니다. 양식은 모든 프로젝트에 첨부되며 다음 필드를 포함합니다.

Data Connect에 대한 쿼리 응답에 이 정보를 포함해야 합니다. 데이터 레이크의 레코드에 대한 사용자 지정 데이터 값이 제목이 parametervalues인 열에 포함되어 있습니다. 이 열은 JSON 개체로 저장됩니다.

쿼리

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

응답

위의 쿼리는 다음 데이터를 반환합니다.

설명

parametervalues JSON 개체를 쿼리할 때 다음을 사용하여 각 사용자 지정 데이터 필드에 열로 액세스할 수 있습니다.

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

상태 시간 쿼리

이 예에서는 프로젝트가 이전에 할당된 상태에서 소비한 시간을 측정하는 방법을 보여 줍니다. 상태에서 작업 또는 문제 시간을 측정하거나 객체에 다른 속성(사용자 정의 데이터 값 포함)이 적용된 시간을 측정하도록 쉽게 조정할 수 있습니다.

시나리오

조직 리더쉽은 작업 수명주기의 각 단계에서 너무 많은 시간을 소비하고 있다고 생각합니다. 프로세스를 개선하기 위한 권장 사항을 만들기 전에 시간이 지남에 따라 프로젝트 상태가 변경되는 빈도와 프로젝트가 지정된 상태에 머무르는 일 수에 대한 기준선 측정을 만들어야 합니다.

PROJECTS_EVENT 데이터 보기를 사용하여 프로젝트 객체에 대해 각 상태 변경 목록을 가져옵니다. 새 상태를 이전 상태와 비교하고, 이전에 할당된 상태의 유효 시간 범위를 가져온 다음, 해당 상태에서 보낸 일수를 계산합니다.

프로젝트당 각 상태에서 보낸 시간의 이 원시 출력을 사용하여 시각화를 작성하거나 데이터를 추가로 집계하여 상태, 프로젝트 유형 또는 연도별 상태 기간 평균을 작성할 수 있습니다. 그런 다음 이 기준선은 리더십의 기대에 부응하기 위해 측정할 수 있는 벤치마크를 설정하는 데 사용됩니다.

다음 쿼리는 Data Connect PROJECTS_EVENTS 데이터 보기를 사용하여 각 프로젝트 상태 변경 이벤트를 비교하고 상태의 시간을 표시합니다.

쿼리

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

응답

위의 쿼리는 다음 데이터를 반환합니다.

설명

이 쿼리는 Data Connect의 변경 이벤트 추적 기능을 사용합니다. 이전 이벤트와 다른 새 상태 값이 발생한 이벤트가 트리거된 날짜를 결정합니다.

쿼리를 처음부터 끝까지 검사합니다.

Planning: 단일 레코드 유형 쿼리

이 예에서는 Data Connect 데이터 레이크에 저장된 단일 레코드 유형에 대해 Workfront Planning 데이터를 쿼리하는 방법을 보여 줍니다.

시나리오

조직은 Workfront Planning을 사용하여 캠페인을 추적합니다. 각 캠페인 레코드에는 이름, 상태, 시작 날짜, 종료 날짜 및 소유자가 포함됩니다. 대시보드에서 사용할 모든 활성 캠페인과 주요 세부 정보 목록을 가져오려고 합니다.

쿼리

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

응답

위의 쿼리는 다음 데이터를 반환합니다.

설명

Data Connect의 계획 레코드는 레코드 유형에 관계없이 단일 테이블 구조를 공유합니다. RECORDTYPEID 열은 쿼리를 특정 레코드 종류(이 경우 캠페인)로 범위를 지정하는 데 사용됩니다. <your_campaign_record_type_id>을(를) 쿼리할 레코드 형식의 ID로 바꾸십시오. ID는 Workfront Planning 레코드 형식 설정에서 또는 RECORDTYPE_CURRENT를 쿼리하여 찾을 수 있습니다.

필드 값은 FIELD_VALUES 열에 JSON 개체로 저장되며 사용자 정의 양식 데이터에 사용된 것과 동일한 콜론 표기법 구문을 사용하여 액세스됩니다.

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

필드 이름 참조는 대문자, 간격 및 이모지를 포함하여 Planning 레코드 유형 필드 구성에 정의된 필드 이름과 정확히 일치해야 합니다.

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

Planning: 연결된 레코드 유형 쿼리

이 예에서는 두 개의 연결된 Planning 레코드 유형(상위 레코드 유형 및 연결된 레코드 유형)에 걸쳐 데이터를 쿼리하는 방법을 보여 줍니다.

시나리오

조직은 Workfront Planning에서 캠페인 레코드를 Tactic 레코드에 연결합니다. 각 캠페인을 관련 전술의 주요 세부 사항과 함께 표시하는 보고서를 생성하려는 경우 특히 전술명, 전략 우선순위, 예산 배분을 제시해 리더십이 전술별로 구성된 캠페인 활동을 검토할 수 있게 하고자 한다.

Data Connect에서 기본 Planning 레코드 유형 간의 연결은 PLANNINGRECORD_CURRENT의 FIELD_VALUES_RAW 열에 직접 저장됩니다. "Tactics"라는 참조 필드의 경우 값은 연결된 레코드의 RECORDID가 있는 ID 속성이 각각 포함된 연결된 레코드 개체의 JSON 배열입니다. Snowflake의 LATERAL FLATTEN을 사용하여 이 배열을 행으로 확장하고 연결된 레코드 유형에 연결합니다.

쿼리

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

응답

위의 쿼리는 다음 데이터를 반환합니다.

설명 - 수정된 KP

기본 Planning 레코드 유형 간의 연결은 REFERENCE_CURRENT 조인 테이블에 저장됩니다. REFERENCE_CURRENT 조인 테이블은 RecordType 간의 조인에 사용됩니다. RecordType 사이에 조인할 때는 TO_RECORDID 필드를 사용해야 합니다.

PLANNINGRECORD 뷰의 REFERENCE_ID 열에는 해당 계획 레코드에 적용할 수 있는 모든 REFERENCEID 필드 목록이 포함됩니다. field_value와 동일한 JSON 표기법을 활용하여 ID에 액세스할 수 있습니다.

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

REFERENCE_CURRENT 보기에는 TO_RECORDID가 PLANNINGRECORD 보기의 다른 계획 recordId 필드를 가리키는 레코드가 하나 이상 _*.

REFERENCE_CURRENT 및 PLANNINGRECORD에 대한 동일한 조인 패턴을 사용하여 다른 REFERENCE 필드를 추가 계획 레코드에 조인하려면 위의 _*에 뷰를 추가합니다.

Planning: Workfront Workflow 데이터 쿼리에 연결된 레코드 유형

이 예에서는 REFERENCE_CURRENT 뷰에 외부 개체 참조를 저장하는 Planning의 기본 연결 기능을 사용하여 Workfront Planning 레코드 유형(이 경우 프로젝트)을 기본 Workfront Workflow 개체에 조인하는 방법을 보여 줍니다.

시나리오

조직은 Planning의 기본 연결 기능을 사용하여 Workfront Planning의 캠페인 레코드를 Workfront Projects에 연결합니다. 캠페인 관리자가 Planning 작업 공간 컨텍스트를 종료하지 않고 게재 진행률을 추적할 수 있도록 연결된 프로젝트(특히 프로젝트의 현재 완료율, 계획된 완료 일자 및 할당된 프로젝트 소유자)의 라이브 실행 데이터와 함께 캠페인 세부 사항을 표시하는 결합된 보고서를 생성하려고 합니다.

쿼리

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

응답

위의 쿼리는 다음 데이터를 반환합니다.

설명

Planning 레코드 유형에서 기본 Workfront Workflow 객체로의 연결은 REFERENCE_CURRENT에 저장됩니다. 이 보기의 각 행은 하나의 방향 링크를 나타냅니다. TO_EXTERNALID에는 연결된 Workfront 객체의 ID가 저장됩니다. Workfront 연결을 나타내는 행은 TO_EXTERNALCONNECTIONNAME = 'workfront' 및 Workfront 개체 유형의 API 코드(예: PROJ for Projects)에 해당하는 TO_EXTERNALOBJECTNAME 값으로 식별됩니다.

PLANNINGRECORD 테이블의 REFERENCE_ID 열에는 해당 레코드에 적용할 수 있는 모든 REFERENCEID 필드 목록이 포함되어 있습니다. field_value와 동일한 JSON 표기법을 활용하여 ID에 액세스할 수 있습니다.
PLANNINGRECORD_CURRENT의 단일 REFERENCE_ID에는 REFERENCE_CURRENT 테이블에 하나 이상의 참조 링크가 포함되어 있을 수 있습니다. 이 링크는 Workfront 테이블에서 특정 객체 유형의 객체에 연결됩니다.

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

Planning 뷰(PLANNINGRECORD_CURRENT, REFERENCE_CURRENT)는 WORKFRONT.PLANNING 스키마에 있는 반면 기본 Workfront Workflow 뷰(PROJECTS_CURRENT, USERS_CURRENT 등)는 Workfront.WF 스키마에 있습니다. 스키마 간 조인에는 정규화된 테이블 이름이 필요합니다.

쿼리는 세 개의 조인을 수행합니다.

다른 REFERENCE 필드를 추가 외부 레코드에 연결하려면 REFERENCE_CURRENT 및 Workfront Workflow 뷰에 동일한 조인 패턴이 위의 쿼리에 추가됩니다.

외부 및 Planningrecord 값은 REFERENCE_CURRENT 테이블에 여러 번 조인하고 적절한 조인 패턴을 사용하여 동일한 쿼리에서 조인할 수 있습니다.