derived_analytic_model

Utilisation

view: view_name {
  derived_analytic_model: {
    sql: analytic_model_definition ;;
  }
}
Hiérarchie
view
derived_analytic_model
Valeur par défaut
Aucun

Règles spéciales
Les modèles analytiques ne sont compatibles qu'avec les connexions BigQuery et Snowflake.

Définition

Pour les connexions BigQuery et Snowflake, le paramètre derived_analytic_model définit un modèle analytique dans la base de données (un graphique BigQuery ou une vue sémantique dans Snowflake) géré par Looker. Dans ce scénario, Looker génère le modèle analytique dans votre base de données en exécutant les instructions SQL LDD (langage de définition de données) appropriées que vous spécifiez dans la définition du paramètre LookML derived_analytic_model. La syntaxe SQL que vous définissez dans le paramètre derived_analytic_model doit être compatible avec votre base de données.

Contrairement aux tables dérivées, les objets de modèle analytique gérés par Looker ne conservent aucune donnée dans la base de données et ne sont pas actualisés de manière incrémentielle. Ils représentent plutôt des modèles sémantiques qui définissent des relations et des mesures directement dans la base de données.

Pour définir un modèle analytique, utilisez l'un des sous-paramètres suivants du paramètre derived_analytic_model :

De plus, si vous définissez votre vue analytique avec le sql sous-paramètre, vous pouvez utiliser le publish_as_db_analytic_model sous-paramètre du derived_analytic_model paramètre pour créer un modèle analytique stable qui peut être interrogé en dehors de Looker.

Une fois que vous avez défini le modèle analytique dans le paramètre derived_analytic_model, vous pouvez définir des dimensions et des mesures LookML qui correspondent à votre modèle analytique. Pour obtenir des exemples, consultez la section Exemples.

sql

Utilisez le paramètre sql si vous souhaitez fournir le code SQL uniquement pour la définition du modèle analytique et que Looker gère la création du modèle analytique. Lorsque vous utilisez le sql sous-paramètre, n'incluez pas d'instruction CREATE ni CREATE OR REPLACE, car Looker générera automatiquement l'instruction LDD pour créer le modèle analytique côté base de données.

Pour obtenir un exemple d'utilisation du paramètre sql afin de créer un modèle analytique dans votre base de données, consultez Créer un modèle analytique dérivé avec sql.

sql_create

Utilisez le paramètre sql_create pour définir une instruction SQL complète afin de créer un modèle analytique. Lorsque vous utilisez le paramètre sql_create, vous devez inclure une instruction CREATE OR REPLACE (ou une instruction CREATE, si votre dialecte n'est pas compatible avec CREATE OR REPLACE).

Tenez compte des points suivants lorsque vous utilisez le sous-paramètre sql_create :

  • Pour les connexions BigQuery, utilisez une instruction CREATE OR REPLACE pour créer le modèle analytique.
  • Utilisez ${SQL_TABLE_NAME} pour remplacer le nom calculé du modèle analytique en cours de création. Cela garantit que l'instruction SQL inclura correctement le nom du modèle analytique que vous fournissez dans le paramètre LookML view.

Pour obtenir un exemple d'utilisation du paramètre sql_create afin de créer un modèle analytique dans votre base de données, consultez Créer un modèle analytique dérivé avec sql_create.

create_process

Utilisez le paramètre create_process lorsque vous devez définir plusieurs instructions SQL séquentielles pour définir le modèle analytique. Sous le paramètre create_process, utilisez le sous-paramètre sql_step pour spécifier les instructions SQL individuelles. Votre base de données exécutera les instructions sql_step une par une, dans l'ordre que vous avez spécifié. Looker émet les instructions SQL dans les sous-paramètres sql_step au fur et à mesure que vous les définissez, sans wrapper. Vous devez donc inclure une étape avec une instruction CREATE OR REPLACE (ou une instruction CREATE, si votre dialecte n'est pas compatible avec CREATE OR REPLACE).

Pour obtenir un exemple d'utilisation du paramètre create_process afin de créer un modèle analytique dans votre base de données, consultez Créer un modèle analytique dérivé avec create_process.

publish_as_db_analytic_model

Pour les modèles analytiques dérivés créés avec le sql paramètre, vous pouvez définir votre modèle analytique dérivé avec publish_as_db_analytic_model: yes pour inviter Looker à créer un modèle analytique stable qui peut être interrogé en dehors de Looker.

Le modèle analytique stable sera publié (créé) lors du prochain cycle du régénérateur Looker une fois le LookML du modèle analytique dérivé déployé en production avec publish_as_db_analytic_model: yes.

Pour savoir comment obtenir le nom du modèle analytique stable afin de pouvoir l'utiliser pour interroger le modèle analytique stable en dehors de Looker, consultez la section Accéder au modèle analytique stable.

Créer des dimensions et des mesures LookML basées sur votre vue analytique

Une fois que vous avez défini votre modèle analytique, vous pouvez définir dans le même fichier d'affichage des dimensions et des mesures LookML basées sur le modèle analytique.

Pour en savoir plus sur la syntaxe appropriée à utiliser pour définir votre modèle analytique et pour faire référence à des éléments de votre modèle analytique, consultez la documentation de votre dialecte. Par exemple, pour créer une dimension LookML à partir d'une entité BigQuery Graph, vous devez utiliser des traits de soulignement pour séparer les éléments lors de la définition de la portée. Par exemple, pour BigQuery Graph, cette dimension LookML est basée sur la propriété location_id dans la table de nœuds Stores :

  dimension: location_id {
    type: number
    sql: Stores_location_id ;;
  }

Toutefois, pour créer une dimension LookML basée sur une vue sémantique Snowflake, vous devez utiliser le nom non qualifié d'une métrique ou d'une dimension.

Exemples

Les sections suivantes fournissent des exemples de création d'une vue analytique à l'aide des différents sous-paramètres de derived_analytic_model :

Créer un modèle analytique dérivé avec sql

Voici un exemple de fichier d'affichage LookML qui définit un modèle analytique basé sur SQL pour une base de données BigQuery à l'aide du sous-paramètre sql de derived_analytic_model. Looker créera le modèle analytique dans la base de données en exécutant les commandes SQL DDL fournies dans le paramètre sql.

Notez les points suivants dans l'exemple :

  • Le sous-paramètre sql ne contient que la définition du modèle analytique lui-même. Il n'y a pas d'instruction CREATE, car avec sql, Looker gère automatiquement les commandes CREATE pour le modèle analytique.
  • Le modèle analytique est défini avec publish_as_db_analytic_model: yes. Looker créera donc un modèle analytique stable qui pourra être interrogé en dehors de Looker.
view: MyWarehouseOrdersView {
  derived_analytic_model: {
    publish_as_db_analytic_model: yes

    # Defining the analytic model
    
    sql:
      NODE TABLES (
        Customers
        KEY(customer_id)
        PROPERTIES(
          country_code,
          concat(first_name, ' ', last_name) AS name,
          age,
          MEASURE(AVG(age)) AS AvgAge 
        ),
      Orders
        KEY(order_id)
        PROPERTIES (
          customer_id,
          employee_id,
          date,
          discount,
          MEASURE(AVG(discount)) AS AvgDiscount
        )

      EDGE TABLES (
        -- Relationship: Orders -> Customers
        looker_test.orders AS orders_to_users
          KEY(id)
          SOURCE KEY (order_id) REFERENCES orders (order_id)
          DESTINATION KEY (customer_id) REFERENCES Customers (customer_id)
          NO PROPERTIES
      ) ;;
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: customer_id {
    type: number
    sql: Customers_customer_id ;;
  }

  dimension: customer_age {
    type: number
    sql: Customers_age ;;
  }

  measure: orders_avg_discount {
    type: number
    sql: Orders_AvgDiscount ;; 
  }
}

Créer un modèle analytique dérivé avec create_process

Voici un exemple de fichier d'affichage LookML qui définit un modèle analytique basé sur SQL pour une base de données BigQuery à l'aide du sous-paramètre create_process de derived_analytic_model. Dans cet exemple, vous devez définir plusieurs instructions SQL séquentielles pour définir le modèle analytique. La première étape supprime le modèle analytique s'il existe déjà, et la deuxième étape le crée.

view: university_statistics {
  derived_analytic_model: {
    create_process: {
      sql_step: 
        DROP PROPERTY GRAPH IF EXISTS ${SQL_TABLE_NAME} ;;
      sql_step: 
        CREATE PROPERTY GRAPH ${SQL_TABLE_NAME}
         NODE TABLES (
          university.College
            KEY(college_id)
            PROPERTIES(college_id, college_name),
          university.Department
            KEY(dept_id)
            PROPERTIES(dept_id, dept_name, college_id,
              budget OPTIONS(description="Department budget in USD"),
              MEASURE(SUM(budget)) AS total_budget),
          university.Course
            KEY(course_id)
            PROPERTIES(
              course_id,
              course_name,
              credits,
              dept_id,
              MEASURE(AVG(credits)) AS avg_credits,
              MEASURE(SUM(credits)) AS total_credits,
              MEASURE(COUNT(course_id)) AS course_count)
        )
        EDGE TABLES (
          university.Department AS CollegeDept
            SOURCE KEY (college_id) REFERENCES College (college_id)
            DESTINATION KEY (dept_id) REFERENCES Department (dept_id),
          university.Course AS DeptCourse
            SOURCE KEY (dept_id) REFERENCES Department (dept_id)
            DESTINATION KEY (course_id) REFERENCES Course (course_id)
        );;
    }
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: college_id {
    type: number
    sql: College_college_id ;;
  }

  dimension: course_name {
    type: string
    sql: Course_course_name ;;
  }
  
  ...
}

Créer un modèle analytique dérivé avec sql_create

Voici un exemple de fichier d'affichage LookML qui définit un modèle analytique basé sur SQL pour une base de données BigQuery à l'aide du sous-paramètre sql_create de derived_analytic_model. Dans cet exemple, le paramètre sql_create définit l'instruction CREATE OR REPLACE complète à exécuter pour créer le modèle analytique en une seule étape.

view: MyWarehouseOrdersView {
  derived_analytic_model: {
    sql_create:
      CREATE OR REPLACE PROPERTY GRAPH ${SQL_TABLE_NAME}
        NODE TABLES(
          accounting.Loan AS Loan
            KEY(loanId)
            LABEL Loan PROPERTIES(
              loanId,
              loanAmount,
              balance,
              createTime,
              interestRate,
              accountId,
              balance + 100 AS derived_balance,
              CASE WHEN balance > 1000 THEN "High" ELSE "Low" END AS risk_level,
              CONCAT("ID-", CAST(loanId AS STRING)) AS full_id,
              DATE(2024, 1, 1) AS fixed_date,
              MEASURE(AVG(interestRate)) AS avg_interest_rate
            ),
          accounting.AccountView AS Account
            KEY(accountId)
            LABEL Account PROPERTIES(
              accountId,
              createTime,
              isBlocked,
              accountType,
              amount,
              ownerId,
              MEASURE(MIN(createTime)) AS oldest_account_create_time,
              MEASURE(MAX(createTime)) AS newest_account_create_time,
              MEASURE(AVG(amount)) AS avg_account_amount,
              MEASURE(SUM(amount)) AS total_account_amount,
              MEASURE(COUNT(DISTINCT accountType)) AS account_type_count
            ),
          accounting.PersonMV AS Person
            KEY(personId)
            LABEL Person PROPERTIES(
              personId,
              personName,
              age,
              age_tier,
              MEASURE(AVG(age)) AS avg_age,
              MEASURE(COUNT(DISTINCT age_tier)) AS age_tier_count
            )
        )
        EDGE TABLES(
          accounting.Loan AS Account_Repay_Loan
            KEY(loanId)
            SOURCE KEY(loanId) REFERENCES Loan(loanId)
            DESTINATION KEY(accountId) REFERENCES Account(accountId)
            LABEL Repay NO PROPERTIES,
          accounting.Account AS Person_Own_Account
            KEY(accountId)
            SOURCE KEY(accountId) REFERENCES Account(accountId)
            DESTINATION KEY(ownerId) REFERENCES Person(personId)
            LABEL Own NO PROPERTIES
        );;
  }

  # Mapping dimensions/measures to the dimensions/measures
  # provided by the analytic model

  dimension: loan_id {
    type: number
    sql: Loan_loanId ;;
  }

  dimension: account_ID {
    type: number
    sql: Account_accountID ;;
  }

  ...

}

Accéder au modèle analytique stable

Si vous avez créé votre modèle analytique dérivé à l'aide du sql sous-paramètre et que vous avez inclus l'instruction publish_as_db_analytic_model: yes sous votre paramètre derived_analytic_model, Looker publiera (créera) le modèle analytique stable lors du prochain cycle du régénérateur Looker une fois le LookML du modèle analytique dérivé déployé en production avec publish_as_db_analytic_model: yes.

Une fois le modèle analytique stable publié, vous pouvez l'interroger directement à l'aide de son nom stable. Vous pouvez déterminer le nom stable à partir des informations incluses dans l'onglet SQL de la section Données d'une requête d'exploration du modèle analytique. Pour obtenir le nom stable d'un modèle analytique, procédez comme suit :

  1. Ouvrez l'exploration de la vue de votre modèle analytique.

  2. Dans l'exploration, sélectionnez des dimensions ou des mesures dans le sélecteur de champs.

  3. Cliquez sur l'onglet SQL de la section Données.

  4. Dans l'onglet SQL, recherchez l'une des instructions SQL suivantes :

    • Pour BigQuery Graph :
      • CREATE PROPERTY GRAPH
      • SELECT ... FROM GRAPH_EXPAND('PROPERTY_GRAPH_NAME')
    • Pour la vue sémantique Snowflake :
      • CREATE SEMANTIC VIEW
      • SELECT ... FROM SEMANTIC_VIEW_NAME

  5. Le nom stable est une vue que Looker crée dans le schéma de brouillon, qui pointe vers la table réelle obscurcie affichée dans l'onglet SQL. Pour obtenir le nom stable de la vue analytique, renseignez les informations suivantes à partir de l'instruction SQL :

    SCRATCH_SCHEMA_NAME.CONNECTION_REGISTRATION_KEY_MODEL_NAME_VIEW_NAME
    • SCRATCH_SCHEMA_NAME : le nom du schéma de brouillon correspond au début de la chaîne suivant l'instruction CREATE ou SELECT, avant le "."
    • CONNECTION_REGISTRATION_KEY : la clé d'enregistrement de la connexion comporte deux caractères. Selon votre dialecte de base de données, elle suit un signe dollar ou le premier trait de soulignement dans le nom de la table dans l'instruction CREATE ou SELECT.
    • MODEL_NAME : nom du modèle LookML.
    • VIEW_NAME : nom de la vue dans laquelle le modèle analytique est défini.

Voici, par exemple, le texte de l'onglet SQL d'une requête d'exploration pour une connexion BigQuery. Le modèle analytique est défini dans la vue nommée sales_analytic_model, et le nom du modèle LookML est thelook. Dans ce cas, Looker a déjà créé le modèle analytique. Il n'y a donc pas d'instruction CREATE. Toutefois, l'instruction SELECT ... FROM GRAPH_EXPAND contient les informations sur le nom de la table :

-- use existing sales_analytic_model in `looker-test-db.looker_scratch.LG_J7LSZ1778710001008_sales_analytic_model`
SELECT
    sales_analytic_model.orders_id  AS sales_analytic_model_orders_id,
    AGG(sales_analytic_model.orders_count_orders ) AS sales_analytic_model_count_orders
FROM GRAPH_EXPAND("looker-test-db.looker_scratch.LG_J7LSZ1778710001008_sales_analytic_model") AS sales_analytic_model
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

Voici les valeurs dont vous avez besoin pour dériver le nom stable du modèle analytique :

  • SCRATCH_SCHEMA_NAME est looker-test-db.looker_scratch
  • CONNECTION_REGISTRATION_KEY est J7
  • MODEL_NAME est thelook
  • VIEW_NAME est sales_analytic_model

Par conséquent, le nom stable du modèle analytique est le suivant :

looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

Une fois que vous disposez du nom stable du modèle analytique, vous pouvez l'interroger directement.

Éléments à prendre en compte

Lorsque vous utilisez des modèles analytiques dans la base de données, tenez compte des points et des limites suivants :

  • Types de données : seuls les types de données suivants pour les dimensions et les mesures sont compatibles avec les modèles analytiques :

    • Types compatibles avec les dimensions et les mesures :
      • string
      • number
      • date
      • yesno
    • Types compatibles uniquement avec les dimensions :
      • time
      • date_time

  • Mesures :

    • Les mesures de base doivent être prédéfinies : les mesures de base doivent être prédéfinies dans le modèle analytique de la base de données sous-jacente. Looker ne peut pas définir une nouvelle mesure de base en effectuant une agrégation (telle que type: sum ou type: count) sur une dimension à partir d'un modèle analytique.

    • Les mesures basées sur d'autres mesures sont compatibles : vous pouvez utiliser le paramètre sql d'une mesure LookML pour effectuer des calculs non agrégés qui utilisent des mesures de base prédéfinies à partir du modèle analytique. Lorsque vous créez une mesure basée sur d'autres mesures, vous ne pouvez pas définir la nouvelle mesure comme un type de mesure agrégée tel que sum ou count. Vous devez définir la nouvelle mesure comme un type de mesure non agrégée, tel que string, number, date, ou yesno. Consultez l'exemple ci-dessous :

      measure: average_order_amount {
  type: number
  sql: ROUND(${total_order_amount} / NULLIF(${count_orders}, 0), 2) ;;
}

  • Jointures : une exploration dont la vue de base est basée sur un modèle analytique ne peut pas inclure de jointures. De même, une vue basée sur un modèle analytique ne peut pas être jointe à une exploration qui comporte une vue de base LookML standard.

  • Jointures implicites : les fonctionnalités qui reposent sur des jointures implicites ne sont pas compatibles avec les modèles analytiques. Parmi les fonctionnalités qui reposent sur des jointures implicites, citons les calendriers personnalisés et les champs définis avec type: location, type: distance ou type: zipcode.

  • Les fonctionnalités suivantes ne sont pas compatibles avec les modèles analytiques :