Beispiele für Workfront Data Connect-Abfragen

Damit Sie Ihre Workfront Data Connect-Daten besser nutzen können, enthält diese Seite einfache Beispielabfragen, mit denen Sie sich mit der Syntax und Struktur bestimmter Arten von Abfragen vertraut machen können.

Benutzerdefinierte Datenabfrage

Dieses Beispiel zeigt, wie Sie eine Abfrage erstellen können, um Ihre benutzerdefinierten Daten in Workfront zurückzugeben, z. B. benutzerdefinierte Formulare und benutzerdefinierte Felder.

Szenario

Ihr Unternehmen verwendet ein benutzerdefiniertes Formular namens Finanzintegration. Das Formular ist an jedes Projekt angehängt und enthält die folgenden Felder:

Sie müssen diese Informationen in die Antwort für eine Abfrage bezüglich Data Connect aufnehmen. Benutzerdefinierte Datenwerte für einen Datensatz im Data Lake sind in einer Spalte mit dem Titel parametervalues enthalten. Diese Spalte wird als JSON-Objekt gespeichert.

Abfrage

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

Antwort

Die obige Abfrage gibt die folgenden Daten zurück:

Erklärung

Bei der Abfrage des parametervalues JSON-Objekts kann jedes benutzerdefinierte Datenfeld wie folgt als Spalte aufgerufen werden:

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

Zeit in der Statusabfrage

Dieses Beispiel zeigt, wie Sie die Zeit messen, die ein Projekt in zuvor zugewiesenen Status verbracht hat. Sie kann einfach angepasst werden, um die Aufgaben- oder Problemzeit in einem Status zu messen, oder sie kann angepasst werden, um zu messen, wie lange einem Objekt ein anderes Attribut (einschließlich benutzerdefinierter Datenwerte) zugewiesen wurde.

Szenario

Ihre Unternehmensführung ist der Ansicht, dass Sie in jeder Phase Ihres Arbeitszyklus zu viel Zeit investieren. Bevor Sie Empfehlungen zur Verbesserung des Prozesses abgeben, sollten Sie eine allgemeine Messung erstellen, um festzustellen, wie oft sich ein Projektstatus im Laufe der Zeit ändert und wie viele Tage ein Projekt in einem bestimmten Status verbleibt.

Sie verwenden die Datenansicht PROJECTS_EVENT, um eine Liste jeder Statusänderung für das Projektobjekt abzurufen. Sie vergleichen den neuen Status mit dem vorherigen Status, erfassen den effektiven Zeitraum für den zuvor zugewiesenen Status und berechnen dann die in diesem Status verbrachten Tage.

Mithilfe dieser Rohausgabe der in jedem Status pro Projekt verbrachten Zeit können Sie mit dem Erstellen von Visualisierungen beginnen oder die Daten weiter aggregieren, um Statusdauermittelwerte nach Status, Projekttyp oder Jahreszeit zu erstellen. Anhand dieser Grundlinie können Sie dann einen Maßstab festlegen, an dem Sie messen können, um die Erwartungen Ihrer Führung zu erfüllen.

Die folgende Abfrage verwendet die Datenansicht Data Connect PROJECTS_EVENTS , um jedes Projektstatusänderungsereignis zu vergleichen und die Zeit im Status anzuzeigen.

Abfrage

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

Antwort

Die obige Abfrage gibt die folgenden Daten zurück:

Erklärung

Die Abfrage verwendet die Tracking-Funktionen für Änderungsereignisse von Data Connect. Sie ermittelt das Datum, an dem ein Ereignis ausgelöst wurde, das einen neuen Statuswert hatte, der sich vom vorherigen Ereignis unterschied.

Überprüfen der Abfrage von innen nach außen:

Planung: Abfrage vom Typ Einzel-Datensatz

Dieses Beispiel zeigt, wie Workfront Planning-Daten für einen einzelnen Datensatztyp abgefragt werden, der im Data Connect Data Lake gespeichert ist.

Szenario

Ihr Unternehmen verwendet Workfront Planning zur Verfolgung von Kampagnen. Jeder Kampagnendatensatz enthält einen Namen, einen Status, ein Startdatum, ein Enddatum und einen Besitzer. Sie möchten eine Liste aller aktiven Kampagnen und ihrer wichtigsten Details zur Verwendung in einem Dashboard abrufen.

Abfrage

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

Antwort

Die obige Abfrage gibt die folgenden Daten zurück:

Erklärung

Die Planung von Datensätzen in Data Connect verwendet unabhängig vom Datensatztyp eine einzige Tabellenstruktur. Die Spalte RECORDTYPEID wird verwendet, um die Abfrage auf einen bestimmten Datensatztyp zu beschränken - in diesem Fall Kampagnen. Ersetzen Sie <your_campaign_record_type_id> durch die ID des Datensatztyps, den Sie abfragen möchten. Diese finden Sie in den Einstellungen für den Workfront Planning-Datensatztyp oder durch Abfragen von RECORDTYPE_CURRENT.

Feldwerte werden als JSON-Objekt in der Spalte FIELD_VALUES gespeichert und über dieselbe Doppelpunktsyntax aufgerufen, die auch für benutzerdefinierte Formulardaten verwendet wird:

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

Feldnamenverweise müssen genau mit dem Feldnamen übereinstimmen, der in der Feldkonfiguration für den Planungs-Datensatztyp definiert ist, einschließlich Groß- und Kleinschreibung, Leerzeichen und Emoji.

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

Planung: Abfrage für verbundene Datensatztypen

Dieses Beispiel zeigt, wie Daten über zwei verbundene Planungs-Datensatztypen hinweg abgefragt werden können - einen übergeordneten Datensatztyp und einen Datensatztyp, mit dem er verbunden ist.

Szenario

Ihr Unternehmen verknüpft Campaign-Datensätze mit taktischen Datensätzen in Workfront Planning. Sie möchten einen Bericht erstellen, der jede Kampagne zusammen mit den wichtigsten Details der zugehörigen Taktik anzeigt. Sie möchten insbesondere den Namen der Taktik, die strategische Priorität und die Budgetzuweisung anzeigen, damit die Führung die Kampagnenaktivitäten nach Taktik organisieren kann.

In Data Connect werden Verbindungen zwischen nativen Planungs-Datensatztypen direkt in der Spalte FIELD_VALUES_RAW von PLANNINGRECORD_CURRENT gespeichert. Bei einem Referenzfeld mit dem Namen „Taktik“ ist der Wert ein JSON-Array von verbundenen Datensatz-Objekten, von denen jedes eine ID-Eigenschaft mit der RECORDID des verbundenen Datensatzes enthält. Verwenden Sie die Snowflake-Option LATERAL FLATTEN , um dieses Array in Zeilen zu erweitern und mit dem verbundenen Datensatztyp zu verbinden.

Abfrage

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

Antwort

Die obige Abfrage gibt die folgenden Daten zurück:

Erklärung - Geänderte KP

Verbindungen zwischen nativen Planungs-Datensatztypen werden in einer REFERENCE_CURRENT JOIN-Tabelle gespeichert. Die Join-Tabelle REFERENCE_CURRENT wird für Joins zwischen RecordType verwendet. Beim Verbinden von RecordType sollte das Feld TO_RECORDID verwendet werden.

Die Spalte REFERENCE_ID in der PLANNINGRECORD-Ansicht enthält eine Liste aller REFERENCEID-Felder, die für diesen Planungsdatensatz gelten. Sie können auf die ID zugreifen, indem Sie dieselbe JSON-Notation als field_value verwenden.

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

Die REFERENCE_CURRENT-Ansicht enthält einen oder mehrere Datensätze, bei denen TO_RECORDID auf andere recordId in den PLANNINGRECORD_*-Ansichten verweist.

Um ein weiteres REFERENCE-Feld mit zusätzlichen Planungsdatensätzen zu verbinden, werden der obigen Abfrage das gleiche Muster der Verbindung mit REFERENCE_CURRENT und der PLANNINGRECORD_* -Ansicht hinzugefügt.

Planung: Datensatztyp mit Workfront Workflow-Datenabfrage verknüpft

Dieses Beispiel zeigt, wie ein Workfront Planning-Datensatztyp mithilfe der nativen Verbindungsfunktion von Planning, die externe Objektverweise in der REFERENCE_CURRENT-Ansicht speichert, mit einem nativen Workfront Workflow-Objekt - in diesem Fall einem Projekt - verbunden wird.

Szenario

Ihr Unternehmen verbindet Campaign-Datensätze in Workfront Planning mit Workfront-Projekten mithilfe der nativen Verbindungsfunktion von Planning. Sie möchten einen kombinierten Bericht erstellen, der die Kampagnendetails zusammen mit den Live-Ausführungsdaten aus dem verknüpften Projekt anzeigt - insbesondere den aktuellen Prozentsatz der Fertigstellung, das geplante Abschlussdatum und den zugewiesenen Projektbesitzer -, damit Kampagnen-Manager den Versandfortschritt verfolgen können, ohne den Arbeitsbereich Planung verlassen zu müssen.

Abfrage

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

Antwort

Die obige Abfrage gibt die folgenden Daten zurück:

Erklärung

Verbindungen von einem Planungs-Datensatztyp zu einem nativen Workfront Workflow-Objekt werden in REFERENCE_CURRENT gespeichert. Jede Zeile in dieser Ansicht stellt eine direktionale Relation dar: TO_EXTERNALID enthält die ID des verbundenen Workfront-Objekts. Zeilen, die Workfront-Verbindungen darstellen, werden durch TO_EXTERNALCONNECTIONNAME = 'workfront' und einen TO_EXTERNALOBJECTNAME-Wert identifiziert, der dem API-Code des Workfront-Objekttyps entspricht - z. B. PROJ für Projekte.

Die Spalte REFERENCE_ID in den PLANNINGRECORD-Tabellen enthält eine Liste aller REFERENCEID-Felder, die für diesen Datensatz gelten. Sie können auf die ID zugreifen, indem Sie dieselbe JSON-Notation als field_value verwenden.
Eine einzelne REFERENCE_ID in PLANNINGRECORD_CURRENT kann eine oder mehrere Referenzverknüpfungen in der REFERENCE_CURRENT-Tabelle enthalten, die mit Objekten eines bestimmten Objekttyps in der Workfront-Tabelle verknüpft sind.

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

Beachten Sie, dass sich Planning-Ansichten (PLANNINGRECORD_CURRENT, REFERENCE_CURRENT) im WORKFRONT.PLANNING-Schema befinden, während native Workfront-Workflow-Ansichten (PROJECTS_CURRENT, USERS_CURRENT usw.) im WORKFRONT.WF-Schema gespeichert sind. Für Schema-übergreifende Joins sind vollständig qualifizierte Tabellennamen erforderlich.

Die Abfrage führt drei Joins durch:

Um ein weiteres REFERENCE-Feld mit zusätzlichen externen Datensätzen zu verbinden, werden dasselbe Verbindungsmuster mit REFERENCE_CURRENT und den Workfront Workflow-Ansichten der obigen Abfrage hinzugefügt.

Externe und Planungs-Datensatzwerte können in derselben Abfrage zusammengefügt werden, indem sie mehrmals mit der REFERENCE_CURRENT-Tabelle verbunden werden und das entsprechende Join-Muster verwenden.