Do's and Dont's¶
This documentation provides guidelines for using Lens functionalities, including when to create views and best practices to avoid errors.
Do's¶
Data Modelling¶
Calling a Measure to Create Another Measure in the Same Table¶
When creating a measure that references another measure within the same table, use curly braces {}
.
- name: current_month_sum
sql: sellingprice
type: sum
filters:
- sql: month({invoice_date}) = 5
- name: previous_month_sum
sql: sellingprice
type: sum
filters:
- sql: month({invoice_date}) = 4
- name: month_over_month_ratio
sql: "{current_month_sum} / {previous_month_sum}"
type: number
Proxy Dimension: Referencing Dimensions from Another Table¶
To reference a dimension from one table while creating a measure in another, use the curly braces {}
. Specify the dimension using {table.column}
if it is from another table, or {column name}
if it is within the same table.
- name: ref_dimension_cust
sql: "{account.state}"
type: string
measures:
- name: ref_measure
sql: case when {sales.ref_dimension} like '%w%' then **{ext_net}** end
type: sum
Calling a Measure from Another Table¶
When referencing a measure from another table, set sub_query
to true
.
Transforming Dimension and Calling it in a Measure¶
Use curly braces {}
when calling a transformed dimension within measures.
dimensions:
- name: quarter
sql: quarter(invoice_date)
type: number
measures:
- name: total_quantities
type: sum
sql: qty_dec_equ
filters:
- sql: year({quarter}) = 1
Views¶
- Purpose: Create views to provide a limited part of your data model to the consumer layer, such as any BI tool. Views are useful for defining metrics, managing governance and data access, and controlling ambiguous join paths.
- Members: Views do not have their own members. Instead, use the
table
orincludes
parameters to incorporate measures and dimensions from other tables into the view. - Refresh Key: Use a refresh key if the underlying data is refreshed on a regular cadence.
Example: revenue_view
In the following example, we create a view called revenue_view
, which includes selected members from the sales
, product
, and account
tables:
views:
- name: revenue_view
description: View containing sales 360 degree information
public: true
meta:
export_to_iris: true
iris:
timeseries: sales.invoice_date
excludes:
- sales.source
- sales.invoice_date
refresh:
every: 24h
tables:
- join_path: sales
prefix: false
includes:
- revenue
- invoice_date
- ext_net
- source
- join_path: product
prefix: true # product_category
includes:
- category
- brand
- class
- join_path: account
prefix: false
includes:
- site_name
- state
Jinja Macros¶
Lens data models support Jinja macros, allowing you to define reusable snippets of code. This feature helps in creating dynamic data models and SQL properties efficiently.
Dynamic Data Models¶
In the example below, we define a macro called dimension()
which generates a dimension. This macro is then invoked multiple times to generate various dimensions.
Example:
{# Declare the macro before using it, otherwise Jinja will throw an error. #}
{%- macro dimension(column_name, type='string', description=None, primary_key=false, public_key=false) -%}
- name: {{ column_name }}
sql: {{ column_name }}
type: {{ type }}
{% if description is not none %}
description: {{ description }}
{% endif %}
{% if primary_key -%}
primary_key: true
{% endif %}
{% if public_key -%}
public_key: false
{% endif -%}
{% endmacro -%}
tables:
- name: account
sql: {{ load_sql('account') }}
description: Table containing information about account
public: true
meta:
export_to_board: true
dimensions:
{{ dimension('customer_id', type='string', description='Customer primary key.', primary_key=true) }}
{{ dimension('site_number', type='number', description='The site number.') }}
{{ dimension('customer_name', description='Name of the customer.') }}
{{ dimension('address', description='Address of the customer.') }}
{{ dimension('city', description='City of the customer.', public_key=true) }}
{{ dimension('county_name', description='County name of the customer.') }}
SQL Property¶
Macros can also be used to generate SQL snippets for use in the sql
property. For example, to avoid division by zero errors when creating measures, define a macro and use it in multiple measures.
Example:
{%- macro nullif_sum_cast(column_name) -%}
cast(nullif(sum({{ column_name }}), 0) as decimal(20,5))
{%- endmacro -%}
measures:
- name: wallet_share
sql: >
round((sum(ext_net) FILTER (WHERE lower({source}) = 'proof')) / {{ nullif_sum_cast('ext_net') }}, 2)
type: number
Dynamic Segments with Secure Access¶
You can create dynamic segments with secure access using Jinja macros. This example sets up segments for different categories with user group restrictions.
Example:
{%- set categories = ["Wine", "Beer", "Spirits", "NAB"] -%}
segments:
{% for name in categories %}
- name: {{ "segment_" ~ name }}
public: true
sql: {{ "{category_name} = '" ~ name ~ "'" }}
meta:
secure:
user_groups:
includes:
- {{ name }}
excludes:
- default
{% endfor %}
User Group Configuration¶
user_groups:
- name: Wine
api_scopes:
- meta
- data
- graphql
# - jobs
# - source
includes:
- users:id:***
- users:id:***
- users:id:***
- name: Beer
api_scopes:
- meta
- data
- graphql
# - jobs
# - source
includes:
- users:id:***
- users:id:***
- name: Spirits
api_scopes:
- meta
- data
- graphql
# - jobs
# - source
includes:
- users:id:***
- users:id:***
- users:id:***
- name: NAB
api_scopes:
- meta
- data
- graphql
# - jobs
# - source
includes:
- users:id:***
- users:id:***
- name: default
includes: "*"
api_scopes:
- meta
- data
- graphql
# - jobs
# - source
By leveraging Jinja macros, you can create efficient, reusable, and dynamic configurations in your Lens data models.
Don’t¶
When creating lenses, avoid the following practices to ensure functionality and maintainability:
Avoid Using Curly Braces {}
in Descriptions¶
Since Jinja is supported, using curly braces {}
in descriptions can cause issues.
dimensions:
- name: sales_sk
type: string
column: sales_sk
public: false
primary_key: true
description: It is the primary key of sales
Avoid Using Single Quotes in Measures, Dimensions, and Segments¶
Using single quotes can throw errors. Instead, use double quotes.
tables:
- name: order
sql: {{ load_sql('order') }}
description: Table containing information about product
public: true
meta:
export_to_board: false
measures:
- name: statuses
sql: "listagg({order.status}) WITHIN GROUP (ORDER BY {order.status})"
type: string
dimensions:
- name: status
sql: "UPPER(status)"
type: string
Avoid Filtering Measures and Dimensions from the Same Table with OR Operator¶
Measures and dimensions from the same table cannot be filtered simultaneously using the 'OR' operator.
{
"measures": [
"sales.total_revenue"
],
"dimensions": [
"sales.site_number"
],
"segments": [],
"filters": [
{
"or": [
{
"member": "sales.site_number",
"operator": "equals",
"values": [
"1",
"21"
]
},
{
"member": "sales.total_revenue",
"operator": "gt",
"values": [
"0"
]
}
]
}
],
"timeDimensions": [],
"limit": 10,
"offset": 0
}
Avoid combining dimensions and measures in the same 'OR' condition. Use separate conditions for clarity.
{
"or": [
{
"member": "sales.invoice_no",
"operator": "equals",
"values": [
"448",
"1265",
"45",
"517",
"2874",
"1",
"837"
]
},
{
"member": "sales.site_number",
"operator": "equals",
"values": [
"1",
"21",
"6",
"18"
]
},
{
"member": "sales.posting_period",
"operator": "inDateRange",
"values": [
"2022-01-01",
"2024-06-01"
]
},
{
"member": "sales.revenue",
"operator": "gt",
"values": [
"30000"
]
}
]
}
By adhering to these guidelines, you can avoid common pitfalls and ensure your lens functions correctly and efficiently.