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 intégré à 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 LDD (langage de définition de données) SQL 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. Au lieu de cela, ils représentent des modèles sémantiques qui définissent les relations et les 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 sous-paramètre sql, vous pouvez utiliser le sous-paramètre publish_as_db_analytic_model du paramètre derived_analytic_model pour créer un modèle analytique stable pouvant être interrogé en dehors de Looker.

Après avoir 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 laisser Looker gérer la création du modèle analytique. Lorsque vous utilisez le sous-paramètre sql, 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 sur 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 permettant 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 ne prend pas en charge 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 afin de 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 sur 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 dans lequel vous les avez spécifiées. Looker émet les instructions SQL dans les sous-paramètres sql_step tels que vous les définissez, sans wrapper. Cela signifie que vous devez 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 sur 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 paramètre sql, 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 pouvant être interrogé en dehors de Looker.

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

Consultez la section Accéder au modèle analytique stable 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.

Créer des dimensions et des mesures LookML en fonction de 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 les dimensions et mesures LookML basées sur le modèle analytique.

Consultez la documentation de votre dialecte pour obtenir des informations sur la syntaxe appropriée à utiliser pour définir votre modèle analytique et pour faire référence aux éléments de votre modèle analytique. 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

Vous trouverez ci-dessous un exemple de fichier de vue 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ée donc un modèle analytique stable qui peut ê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

Vous trouverez ci-dessous un exemple de fichier de vue 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 consiste à supprimer le modèle analytique s'il existe déjà, et la deuxième étape consiste à créer le modèle analytique.

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 sous-paramètre sql 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 après le déploiement en production du LookML du modèle analytique dérivé 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 Explorer du modèle analytique. Pour obtenir le nom stable d'un modèle analytique, procédez comme suit :

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

  2. Dans "Explorer", 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 le graphique BigQuery :
      • 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 temporaire et qui pointe vers la table obscurcie réelle 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 base est le début de la chaîne suivant l'instruction CREATE ou SELECT, avant ".".
    • CONNECTION_REGISTRATION_KEY : la clé d'enregistrement de la connexion comporte deux caractères. Selon le dialecte de votre base de données, elle suivra 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.

Par exemple, voici le texte de l'onglet SQL d'une requête Explorer 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

Le nom stable du modèle analytique est donc 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 intégrés à la base de données, tenez compte des considérations et des limites suivantes :

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

    • Pris en charge pour les dimensions et les mesures :
      • string
      • number
      • date
      • yesno
    • Pris en charge pour les dimensions uniquement :
      • time
      • date_time

  • Mesures :

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

    • Les mesures basées sur d'autres mesures sont acceptées : 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 du modèle analytique. Lorsque vous créez une mesure basée sur d'autres mesures, vous ne pouvez pas la définir comme type de mesure agrégée (sum ou count, par exemple). Vous devez définir la nouvelle mesure comme un type de mesure non agrégé, 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 inclure aucune jointure. De même, une vue basée sur un modèle analytique ne peut pas être jointe à une exploration comportant une vue de base LookML standard.

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

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