sql_analytic_model_name (for views)

This page refers to the sql_analytic_model_name parameter that is part of a view .

sql_analytic_model_name can also be used as part of an Explore, described on the sql_analytic_model_name (for Explores) parameter documentation page.

Usage

view: view_name {
   sql_analytic_model_name 
 : 
 analytic_model_name ;; 
}

Definition

For BigQuery and Snowflake connections, the sql_analytic_model_name parameter specifies the name of an existing in-database analytic model (a BigQuery Graph or a semantic view in Snowflake ) to use as the basis for a LookML view. This lets you leverage analytic models defined directly in your database, such as BigQuery Graph or semantic views in Snowflake .

In this scenario, the analytic model object already exists in your database and is managed by your database; the analytic model isn't created, maintained, or governed by Looker. This is analogous to how regular database tables exposed as LookML views by using sql_table_name aren't governed by Looker.

In the LookML view file, use the sql_analytic_model_name parameter to point Looker to the analytic model on your database. Then create Looker dimensions and measures to map to the analytic model so that you can use Looker to query the analytic model.

Scoping analytic model names

When you reference an analytic model by using the analytic model name alone, Looker uses the default search path (the database and schema) that your Looker admin has configured in the settings for the database connection.

If you need to reference an analytic model in a different database and schema that isn't in the database user's default search path, you can scope the analytic model name by using the <database_name>.<schema_name>.<analytic_model_name> format to point to another database or schema:

• To reference an analytic model from a different schema, use <schema_name>.<analytic_model_name> .
• To reference an analytic model from a different database, use the full <database_name>.<schema_name>.<analytic_model_name> .

For a Google BigQuery connection, you can reference an analytic model in a different project and dataset by scoping the analytic model name using the format <project_name>.<dataset_name>.<analytic_model_name> . See the Google BigQuery connection documentation page for additional information.

Create LookML dimensions and measures based on your analytic view

After you create a view file and identify an analytic model as the sql_analytic_model_name , 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 SQL syntax to use 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.

Example

Here is an example BigQuery Graph named StoreGraph that is defined on a BigQuery database:

  CREATE 
  
 OR 
  
 REPLACE 
  
 PROPERTY 
  
 GRAPH 
  
 mydataset 
 . 
 StoreGraph 
  
 NODE 
  
 TABLES 
  
 ( 
  
 mydataset 
 . 
 Stores 
  
 AS 
  
 S 
 , 
  
 mydataset 
 . 
 Locations 
  
 AS 
  
 L 
  
 PROPERTIES 
 ( 
 id 
 , 
  
 name 
 , 
  
 population 
 , 
  
 MEASURE 
 ( 
 SUM 
 ( 
 population 
 )) 
  
 AS 
  
 total_population 
 ) 
  
 ) 
  
 EDGE 
  
 TABLES 
  
 ( 
  
 mydataset 
 . 
 Stores 
  
 AS 
  
 SL 
  
 SOURCE 
  
 KEY 
  
 ( 
 location_id 
 ) 
  
 REFERENCES 
  
 L 
  
 ( 
 id 
 ) 
  
 DESTINATION 
  
 KEY 
  
 ( 
 name 
 ) 
  
 REFERENCES 
  
 S 
  
 ( 
 name 
 ) 
  
 ); 
 

And here is an example LookML view that's based on the StoreGraph BigQuery Graph, including dimensions and measures that are mapped to the Graph:

 view: MyStoreGraphView {
  sql_analytic_model_name: StoreGraph ;;

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

  dimension: population {
    type: number
    sql: Locations_population ;;
  }

  dimension: location_name {
    type: string
    sql: Locations_name ;;
  }

  measure: locations_total_population {
    type: number
    sql: Locations_total_population ;;
  }
} 

Things to consider

Considerations for analytic models in Looker

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:

  • Filtered measures
  • Aggregate awareness
  • Looker legacy runtime

The analytic model must be accessible from the current connection

When the sql_analytic_model_name parameter is used within a view object, that view object can be referenced in an explore object, which is in turn referenced in a model object. The model object has a database connection defined in it. When you reference an analytic model in the sql_analytic_model_name parameter, the analytic model needs to be accessible within the associated connection that's specified in the model file.

The default database and schema (or, for Google BigQuery, the billing project and dataset ) are defined by your Looker admin when they create the Looker connection to your database .