Console
    Start free

    derived_analytic_model

    Usage

    view: view_name {
   derived_analytic_model 
 : { 
 sql 
 : 
 analytic_model_definition ;; 
 } 
}
    Hierarchy
    view
    derived_analytic_model
    Default Value
    None
    Special Rules
    Analytic models are supported only for BigQuery and Snowflake connections.

    Definition

    For BigQuery and Snowflake connections, the derived_analytic_model parameter defines an in-database analytic model (a BigQuery Graph or a semantic view in Snowflake ), that is managed by Looker. In this scenario, Looker generates the analytic model within your database by executing the appropriate SQL Data Definition Language (DDL) statements that you specify in the definition of the derived_analytic_model LookML parameter. The SQL syntax that you define in the derived_analytic_model parameter must be supported by your database.

    Unlike derived tables , Looker-managed analytic model objects don't persist any data within the database and aren't incrementally refreshed. Instead, they represent semantic models that define relationships and measures directly in the database.

    To define an analytic model, use one of the following subparameters of the derived_analytic_model parameter:

    In addition, if you define your analytic view with the sql subparameter, you can use the publish_as_db_analytic_model subparameter of the derived_analytic_model parameter to create a stable analytic model that can be queried outside of Looker.

    After you define the analytic model inside the derived_analytic_model parameter, you can define LookML dimensions and measures that map to your analytic model. See the Examples section for examples.

    sql

    Use the sql parameter if you want to provide the SQL for only the definition of the analytic model and have Looker manage the creation of the analytical model. When you use the sql subparameter, don't include a CREATE or a CREATE OR REPLACE statement, because Looker will automatically generate the DDL statement to create the analytic model on the database side.

    See Creating a derived analytic model with sql for an example of using the sql parameter to create an analytic model on your database.

    sql_create

    Use the sql_create parameter to define a full SQL statement to create an analytic model. When you use the sql_create parameter, you need to include a CREATE OR REPLACE statement (or a CREATE statement, if your dialect doesn't support CREATE OR REPLACE ).

    Note the following when you use the sql_create subparameter:

    • For BigQuery connections, use a CREATE OR REPLACE statement to create the analytic model.
    • Use ${SQL_TABLE_NAME} to substitute in the computed name of the analytic model being created. This ensures that the SQL statement will correctly include the analytic model name that you provide in the LookML view parameter.

    See Creating a derived analytic model with sql_create for an example of using the sql_create parameter to create an analytic model on your database.

    create_process

    Use the create_process parameter when you need to define multiple, sequential SQL statements to define the analytic model. Under the create_process parameter, use the sql_step subparameter to specify the individual SQL statements. Your database will execute the sql_step statements one at a time, in the order that you specified them. Looker issues the SQL statements in the sql_step subparameters as you define them, with no wrapper, which means that you have to include a step with a CREATE OR REPLACE statement (or a CREATE statement, if your dialect doesn't support CREATE OR REPLACE ).

    See Creating a derived analytic model with create_process for an example of using the create_process parameter to create an analytic model on your database.

    publish_as_db_analytic_model

    For derived analytic models that are created with the sql parameter, you can define your derived analytic model with publish_as_db_analytic_model: yes to prompt Looker to create a stable analytic model that can be queried outside of Looker.

    The stable analytic model will be published (created) on the next cycle of the Looker regenerator after the derived analytic model's LookML is deployed to production with publish_as_db_analytic_model: yes .

    See the Accessing the stable analytic model section for information about getting the name of the stable analytic model so that you can use the name to query the stable analytic model outside of Looker.

    Create LookML dimensions and measures based on your analytic view

    After you define your analytic model, in the same view file you can then define LookML dimensions and measures that are based on the analytic model.

    See your dialect's documentation for information about the proper syntax to use for defining your analytic model, and for referring to elements in your analytic model. For example, to create a LookML dimension from a BigQuery Graph entity, you must use underscores to separate elements when scoping. For example, for BigQuery Graph, this LookML dimension is based on the location_id property in the Stores node table:

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

    However, to create a LookML dimension that's based on a Snowflake semantic view, you must use the unqualified name of a metric or dimension.

    Examples

    The following sections provide examples of creating an analytic view by using the different subparameters of derived_analytic_model :

    Creating a derived analytic model with sql

    The following is an example LookML view file that defines a SQL-based analytic model for a BigQuery database using the sql subparameter of derived_analytic_model . Looker will create the analytic model within the database by executing the SQL DDL commands that are provided in the sql parameter.

    Note the following in the example:

    • The sql subparameter contains only the definition of the analytic model itself. There is no CREATE statement, because with sql Looker automatically handles the CREATE commands for the analytic model.
    • The analytic model is defined with publish_as_db_analytic_model: yes , so Looker will create a stable analytic model that can be queried outside of 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 
  
 ;; 
  
  
 } 
 }

    Creating a derived analytic model with create_process

    The following is an example LookML view file that defines a SQL-based analytic model for a BigQuery database by using the create_process subparameter of derived_analytic_model . In this example, you need to define multiple, sequential SQL statements to define the analytic model. The first step drops the analytic model if it already exists, and the second step creates the analytic model.

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

    Creating a derived analytic model with sql_create

    The following is an example LookML view file that defines a SQL-based analytic model for a BigQuery database by using the sql_create subparameter of derived_analytic_model . In this example, the sql_create parameter defines the full CREATE OR REPLACE statement to execute to create the analytic model in a single step.

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

}

    Accessing the stable analytic model

    If you created your derived analytic model by using the sql subparameter and you included the publish_as_db_analytic_model: yes statement under your derived_analytic_model parameter, Looker will publish (create) the stable analytic model on the next cycle of the Looker regenerator after the derived analytic model's LookML is deployed to production with publish_as_db_analytic_model: yes .

    When the stable analytic model is published, you can query it directly by using its stable name. You can determine the stable name from the information that is included on the SQLtab in the Datasection of an Explore query of the analytic model. Follow these steps to get the stable name for an analytic model:

    1. Open the Explore for your analytic model's view.

    2. In the Explore, select any dimensions or measures from the field picker.

    3. Click the SQLtab of the Datasection.

    4. In the SQLtab, locate one of the following SQL statements:

      • For BigQuery Graph:
        • CREATE PROPERTY GRAPH
        • SELECT ... FROM GRAPH_EXPAND(' PROPERTY_GRAPH_NAME ')
      • For Snowflake semantic view:
        • CREATE SEMANTIC VIEW
        • SELECT ... FROM SEMANTIC_VIEW_NAME

    5. The stable name is a view that Looker creates in the scratch schema, which points to the actual obfuscated table shown in the SQL tab. To get the stable name for the analytic view, fill in the following information from the SQL statement:

        SCRATCH_SCHEMA_NAME 
. CONNECTION_REGISTRATION_KEY 
_ MODEL_NAME 
_ VIEW_NAME
      • SCRATCH_SCHEMA_NAME : The scratch schema name is the beginning of the string following the CREATE or SELECT statement, before the " . "
      • CONNECTION_REGISTRATION_KEY : The connection registration key is two characters; depending on your database dialect, it will follow either a dollar sign or the first underscore in the table name in the CREATE or SELECT statement.
      • MODEL_NAME : The name of the LookML model.
      • VIEW_NAME : The name of the view where the analytic model is defined.

    For example, here is the text from the SQLtab of an Explore query for a BigQuery connection. The analytic model is defined in the view that's named sales_analytic_model , and the name of the LookML model is thelook . In this case, Looker has already created the analytic model, so there is no CREATE statement. But the SELECT ... FROM GRAPH_EXPAND statement contains the table name information:

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

    Here are the values that you need to derive the stable name of the analytic model:

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

    Therefore, the stable name for the analytic model is the following:

    looker-test-db.looker_scratch.J7_thelook_sales_analytic_model

    Once you have the stable name of the analytic model, you can query the analytic model directly.

    Things to consider

    When using in-database analytic models, keep the following considerations and limitations in mind:

    • Data types:Only the following data types for dimensions and measures are supported with analytic models:

      • Supported for dimensions and measures:
        • string
        • number
        • date
        • yesno
      • Supported for dimensions only:
        • time
        • date_time

    • Measures:

      • Base measures must be predefined:Base measures must be predefined in the underlying database analytic model. Looker can't define a new base measure by performing an aggregation (such as type: sum or type: count ) on a dimension from an analytic model.

      • Measures that are based on other measures are supported:You can use the sql parameter of a LookML measure to perform non-aggregate computations that use predefined base measures from the analytic model. When you create a measure that's based on other measures, you can't define the new measure as an aggregate measure type such as sum or count . You must define the new measure as a non-aggregate measure type, such as string , number , date , or yesno . See the following example:

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

    • Joins:An Explore whose base view is based on an analytic model can't include any joins. Similarly, a view that's based on an analytic model can't be joined into an Explore that has a standard LookML base view.

    • Implicit joins:Features that rely on implicit joins aren't supported for analytic models. Some examples of features that rely on implicit joins are custom calendars and fields that are defined with type: location , type: distance , or type: zipcode .

    • The following features are not supported with analytic models:
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: