Freigeben über

Lernprogramm: Erstellen einer vollständigen Metrikansicht mit Verknüpfungen

Dieses Lernprogramm führt Sie durch die Erstellung einer umfassenden Metrikansicht für Vertriebsanalysen mithilfe des TPC-H-Datasets. Am Ende haben Sie eine Metrikansicht, die:

  • Verknüpft Bestellungen und Kunden über mehrere Tabellen hinweg mithilfe eines Schneeflockenschemas
  • Definiert Dimensionen für Zeit-, Geografie- und Bestellattribute
  • Berechnet einfache und komplexe Maße, einschließlich Verhältnissen, gefilterten Aggregationen und Fenstermaße.
  • Verwendet die Kompositierbarkeit, um komplexe Metriken aus einfacheren Measures zu erstellen.
  • Enthält Agentmetadaten für Dashboards und KI-Tools

Wenn Sie mit metrischen Ansichten noch nicht vertraut sind, beginnen Sie mit der Erstellung und Bearbeitung von Metrikansichten , um die Grundlagen kennenzulernen. Dieses Tutorial erweitert diese Grundlage mit praktischer Komplexität.

Anforderungen

Um die Schritte dieses Tutorials abzuschließen, benötigen Sie Folgendes:

  • Ihr Arbeitsbereich muss für Unity Catalog aktiviert sein.
  • Ein SQL Warehouse oder eine Computeressource mit Databricks Runtime 17.3 oder höher.

Das Datenmodell

Das TPC-H-Dataset modelliert eine Großhandels-Lieferkette. In diesem Lernprogramm werden drei Tabellen verwendet, die in einem Schneeflockenschema verknüpft sind:

  • orders verbindet sich mit customer an o_custkey = c_custkey
  • customer verbindet sich mit nation an c_nationkey = n_nationkey
Tabelle Rolle Schlüsselspalten
orders Faktentabelle (Bestelltransaktionen) o_orderkey, , o_custkeyo_totalprice, , o_orderdateo_orderstatus
customer Dimensionstabelle (Kundendetails) c_custkey c_name c_mktsegment c_nationkey
nation Dimensionstabelle (Referenz für Länder oder Regionen) n_nationkey, n_namen_regionkey

Schritt 1: Öffnen des YAML-Editors

In diesem Lernprogramm wird der YAML-Editor zum Erstellen der Definition der Metrikansicht verwendet. Weitere Methoden finden Sie unter Erstellen und Bearbeiten von Metrikansichten.

So öffnen Sie den YAML-Editor:

  1. Klicken Sie auf das Symbol Katalog in der Arbeitsbereich-Randleiste.
  2. Verwenden Sie die Suchleiste, um zu suchen samples.tpch.orders.
  3. Klicken Sie auf den Tabellennamen, um Tabellendetails anzuzeigen.
  4. Klicken Sie auf "Metrische Ansicht erstellen>". Geben Sie im Dialogfeld " Metrikansicht erstellen " einen Namen ein, und wählen Sie ein Katalog- und Schemaziel aus. Klicken Sie dann auf Erstellen.
  5. Klicken Sie bei Bedarf auf YAML , um den YAML-Editor zu öffnen.

Schritt 2: Einrichten der Metrikansicht

Beginnen Sie mit der Version und einem beschreibenden Kommentar.

version: 1.1

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

In diesem Beispiel wird die Version festgelegt und eine Beschreibung hinzugefügt:

  • version: 1.1 bestimmt die Version der YAML-Spezifikation.
  • comment dokumentiert den Zweck der Metrikansicht, der im Katalog-Explorer angezeigt wird.

Schritt 3: Definieren der Quelle und Verknüpfungen

Definieren Sie die primäre Quelltabelle, und verknüpfen Sie verwandte Tabellen. Die nation Verknüpfung wird unter der customer Verknüpfung geschachtelt, um das Schneeflakeschema widerzuspiegeln, bei dem Nation eine Unterdimension des Kunden ist.

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

filter: o_orderdate >= '1995-01-01'

In diesem Beispiel werden die Quelltabelle, Verknüpfungen und Filter definiert:

  • source setzt die Faktentabelle (Bestellungen) als Granularität fest.
  • joins bringt Kundendaten mithilfe einer viele-zu-eins-Beziehung ein.
  • Die geschachtelte nation-Verknüpfung veranschaulicht ein Schneeflockenschemamuster, das durch customer verläuft, um auf geografische Daten zuzugreifen.
  • filter Grenzwerte für aktuelle Daten, die für alle Abfragen in dieser Metrikansicht gelten.

Schritt 4: Definieren von Dimensionen

Dimensionen sind die Attribute, nach denen Benutzer gruppieren und filtern.

dimensions:
  - name: order_date
    expr: o_orderdate

  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"

  - name: order_year
    expr: YEAR(o_orderdate)

  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END

  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"

  - name: customer_name
    expr: customer.c_name

  - name: market_segment
    expr: customer.c_mktsegment

  - name: customer_nation
    expr: customer.nation.n_name

Dieses Beispiel veranschaulicht Bemaßungsmuster:

  • Zeitdimensionen mit mehreren Granularitäten (Datum, Monat, Jahr) unterstützen unterschiedliche Analyseanforderungen.
  • CASE Ausdrücke transformieren kryptische Codes in unternehmensfreundliche Bezeichnungen.
  • Verknüpfte Spalten verweisen mithilfe des in joins (z customer.c_name. B. ) definierten Alias auf Tabellen.
  • Geschachtelte Verknüpfungsspalten verwenden verkettete Punktnotation (z customer.nation.n_name. B. ) zum Durchlaufen des Schneeflakeschemas.

Schritt 5: Definieren von Measures

Measures sind die Berechnungen, die Benutzer analysieren möchten. Definieren Sie zunächst atomische Measures, und verwenden Sie dann die Komponierbarkeit, um komplexe Metriken zu erstellen, die auf zuvor definierte Measures mit der MEASURE() Funktion verweisen.

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)

  - name: total_revenue
    expr: SUM(o_totalprice)

  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)

  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)

  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)

  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')

  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')

  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day

In diesem Beispiel werden Measuremuster veranschaulicht:

  • Atommaße (order_count, total_revenue, unique_customers) sind einfache Aggregationen, die die Bausteine bilden.
  • Zusammengesetzte Measures (avg_order_value, revenue_per_customer) referenzieren zuvor definierte Measures unter Verwendung von MEASURE() anstelle der Duplizierung der Aggregationslogik. Wenn total_revenue Änderungen vorgenommen werden, verwenden diese Measures automatisch die aktualisierte Definition. Siehe Kompositierbarkeit.
  • FILTER Klauseln erstellen bedingte Metriken ohne separate Dimensionen.
  • Das t7d_customers Fenstermaß berechnet eine fortlaufende 7-Tage-Anzahl eindeutiger Kunden, die für das Nachverfolgen von Engagement-Trends im Laufe der Zeit nützlich sind. Weitere Fenstermaßmuster finden Sie unter "Fenstermaße ".

Schritt 6: Hinzufügen von Agentmetadaten

Agentmetadaten verbessern die Datenvisualisierung und verbessern die LLM-Genauigkeit, indem Anzeigenamen, Formatspezifikationen und Synonyme bereitgestellt werden. Fügen Sie diese Eigenschaften zu Ihren Dimensionen und Kennzahlen hinzu, um Ihren Metriken Geschäftskontext zu verleihen.

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0

In diesem Beispiel werden die folgenden Agentmetadaten hinzugefügt:

  • display_name stellt lesbare Bezeichnungen bereit, die in Visualisierungstools anstelle von technischen Spaltennamen angezeigt werden.
  • format definiert, wie Werte in Dashboards angezeigt werden (Währung, Zahl, Prozentsatz).
  • synonyms Helfen Sie KI-Tools wie Genie, Dimensionen und Measures durch Abfragen in natürlicher Sprache zu entdecken.

Ausführliche Informationen zu agentmetadatenoptionen finden Sie unter Agent-Metadaten in Metrikansichten.

Schritt 7: Vollständige YAML-Definition

Dies ist die vollständige Definition der Metrikansicht:

version: 1.1

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
Erstellen der Metrikansicht mit SQL

Wenn Sie diese Definition außerhalb des Katalog-Explorers erstellen, führen Sie die folgende SQL-Datei aus, um die Metrikansicht zu erstellen. Fügen Sie das vollständige YAML von oben zwischen den $$ Trennzeichen ein.

CREATE OR REPLACE VIEW catalog.schema.tpch_sales_analytics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
$$;

Weitere Möglichkeiten zum Erstellen einer Metrikansicht finden Sie unter Erstellen und Bearbeiten von Metrikansichten.

Schritt 8: Die Metrikansicht abfragen

Jetzt können Sie mithilfe von unternehmensfreundlicher Syntax abfragen:

-- Aggregates total revenue, order count, and average order value
-- by customer nation and market segment, ranked by highest revenue first.
SELECT
  customer_nation,
  market_segment,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(order_count) AS order_count,
  MEASURE(avg_order_value) AS avg_order_value
FROM catalog.schema.tpch_sales_analytics
GROUP BY customer_nation, market_segment
ORDER BY total_revenue DESC;

-- Monthly trend with backlog analysis
SELECT
  order_month,
  order_status,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(open_order_revenue) AS open_order_revenue
FROM catalog.schema.tpch_sales_analytics
GROUP BY order_month, order_status
ORDER BY order_month;

Was Sie gelernt haben

Sie haben eine Metrikansicht erstellt, die Folgendes veranschaulicht:

Funktion Beispiel
Snowflake-Schemabeitritte Bestellungen an Kunden zu Nation (geschachtelte n:1-Joins)
Zeitabmessungen Datum, Monat, Jahres granularität
Transformierte Dimensionen CASE Anweisungen, SPLIT Funktionen
Einfache Maßnahmen COUNT, SUM
Kompositierbarkeit avg_order_value und revenue_per_customer verweisen auf zuvor definierte Measures unter Verwendung von MEASURE()
Gefilterte Messungen FILTER (WHERE ...) für bedingte Aggregationen
Fenstermaße 7-tägige rollierende Kundenzählung mit trailing 7 day
Agentmetadaten display_name, formatsynonyms

Nächste Schritte

  • Last updated on