SQL interface (alpha)

Introduction

In addition to HTTP REST APIs, Bloomeo provides a SQL interface. The SQL API enables delivering data over the Postgres-compatible protocol to a wide range of data visualization tools. In general, if an application connects to PostgreSQL database, it can connect to this interface as well. This feature is currently in alpha and may be subject to changes.

Prerequisites

To use the SQL interface, you need:

A Bloomeo account with access to the SQL interface feature.
Basic knowledge of SQL syntax and commands.
An up-to-date JWT token for authentication (see Authentication for more details).

Make your first query

To execute SQL queries against the Bloomeo SQL interface, follow these steps:

Get a JWT token for authentication.
Connect to the SQL interface using a PostgreSQL client (e.g., psql, DBeaver, etc.) with the following connection parameters:
- Host: cubesql.<ENV>.bloomeo-app.com (replace <ENV> with your environment you can find in your current url bar, e.g., in developer.app.bloomeo-app.com, the ENV is app).
- Port: 15432
- User: cube
- Password: Your JWT token
- Database: cube
Once connected, you can execute SQL queries. For example, to retrieve all tables in the database, you can run:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Or get the number of trials you have access to:

SELECT COUNT(*) FROM trial WHERE year = 2024;

Examples

With psql

Here is an example of how to connect to the Bloomeo SQL interface using the psql command-line tool:

export PGPASSWORD="your_jwt_token_here"
psql -h cubesql.demo.bloomeo-app.com -U cube -d cube -p 15432
psql (15.10 (Debian 15.10-0+deb12u1), server 14.2 (Cube SQL))
Type "help" for help.

cube=> SELECT COUNT(*) FROM trial WHERE year = 2024;
 COUNT(UInt8(1)) 
-----------------
               5
(1 row)

With R

Install dependencies:

install.packages(c("RPostgres", "DBI"))

Use the following code to connect and query:

library(DBI)             

# Set env and credentials
bloomeo_env <- "" # replace "" with the environment tag you can find in your current url bar, e.g., in developer.app.bloomeo-app.com, the ENV is app
token <- "" # replace "" with your JWT token

# Init a DB connector
con <- dbConnect(
  RPostgres::Postgres(),
  host = paste0("cubesql.",bloomeo_env, ".bloomeo-app.com"),  
  port = 15432,                   
  dbname = "cube",
  user = "cube",
  password = token
)

# Simple query
res <- dbGetQuery(con, "
  SELECT COUNT(*)
  FROM trial
  WHERE year = 2024;
")
print(res)

# Disconnect
dbDisconnect(con)

With Python

Install dependencies:

# Install via pip
pip install psycopg2-binary sqlalchemy
# Install via uv
uv add psycopg2-binary sqlalchemy

Use the following code to connect and query:

from sqlalchemy import create_engine, text

# Set credentials and connection URL in SQLAlchemy format
user = "cube"
token = "" # replace "" with your JWT token
bloomeo_env = "" # replace "" with the environment tag you can find in your current url bar, e.g., in developer.app.bloomeo-app.com, the ENV is app
host = f"cubesql.{bloomeo_env}.bloomeo-app.com"

# Set URL
port = "15432"
db_name = "cube"
db_url = f"postgresql+psycopg2://{user}:{token}@{host}:{port}/{db_name}"

# Set query
query = """
  SELECT COUNT(*)
  FROM trial
  WHERE year = 2024;
"""

# Start engine and connect to DB
engine = create_engine(db_url)

# Query the DB
with engine.connect() as conn:
    result = conn.execute(text(query))
    for row in result:
        print(row)

Use join

The SQL API also supports joins through the __cubeJoinField virtual column, which allows to control how specific cubes are joined. Join can also be done through CROSS JOIN.

SELECT
  notation.variableId,
  notation.value
FROM
  notation
  LEFT JOIN trial ON notation.__cubeJoinField = trial.__cubeJoinField
  LEFT JOIN variable ON notation.__cubeJoinField = variable.__cubeJoinField
WHERE
  variable.type = 'dec'
  AND trial.id = '67ed56309385a8605ffc99e6'
  AND (notation.tenantid = '6396e894c24797ed07bb40ab')
GROUP BY
  notation.variableId,
  notation.value
LIMIT
  10000;

SELECT
  notation.variableId,
  notation.value
FROM
  notation
  cross JOIN trial 
  CROSS JOIN variable
WHERE
  variable.type = 'dec'
  AND trial.id = '67ed56309385a8605ffc99e6'
  AND (notation.tenantid = '6396e894c24797ed07bb40ab')
GROUP BY
  notation.variableId,
  notation.value
LIMIT
  10000;

Limitations

The SQL interface is currently in alpha and may have limitations or bugs.
Not all SQL features may be supported, refer to the Reference documentation for details.
Performance may vary depending on the complexity of the queries and the load on the system.
Ensure to keep your JWT token secure, as it provides access to your data.

Relations

graph TD
	experiment_subject["experiment_subject"] -->|many_to_one| trial["trial"]
	genotype["genotype"] -->|many_to_one| modality_subModalities["modality_subModalities"]
	genotype["genotype"] -->|one_to_many| genotype_organization["genotype_organization"]
	genotype["genotype"] -->|one_to_many| genotype_sharedWithOrganizations["genotype_sharedWithOrganizations"]
	genotype["genotype"] -->|one_to_many| genotype_species["genotype_species"]
	genotype["genotype"] -->|one_to_one| met_factors_moda_submoda["met_factors_moda_submoda"]
	genotype_market_segment_selection["genotype_market_segment_selection"] -->|many_to_one| genotype["genotype"]
	genotype_market_segment_selection["genotype_market_segment_selection"] -->|many_to_one| genotype_market_segment["genotype_market_segment"]
	genotype_market_segment_selection["genotype_market_segment_selection"] -->|one_to_many| genotype_market_segment_selection_micro_market_segment_ids["genotype_market_segment_selection_micro_market_segment_ids"]
	growing_area["growing_area"] -->|one_to_many| growing_area_geography["growing_area_geography"]
	growing_area["growing_area"] -->|one_to_many| growing_area_organization["growing_area_organization"]
	growing_area_climate["growing_area_climate"] -->|many_to_one| growing_area["growing_area"]
	growing_area_geography["growing_area_geography"] -->|many_to_one| growing_area["growing_area"]
	growing_area_organization["growing_area_organization"] -->|many_to_one| growing_area["growing_area"]
	growing_area_soil_type["growing_area_soil_type"] -->|many_to_one| growing_area["growing_area"]
	growing_area_users["growing_area_users"] -->|many_to_one| growing_area["growing_area"]
	market_segment["market_segment"] -->|one_to_many| market_segment_species["market_segment_species"]
	market_segment["market_segment"] -->|one_to_many| met_market_segment_selection["met_market_segment_selection"]
	market_segment["market_segment"] -->|one_to_many| micro_market_segment["micro_market_segment"]
	market_segment["market_segment"] -->|one_to_many| trial_market_segment_selection["trial_market_segment_selection"]
	market_segment_species["market_segment_species"] -->|many_to_one| market_segment["market_segment"]
	met["met"] -->|one_to_many| met_organization["met_organization"]
	met["met"] -->|one_to_many| met_sharedWithOrganizations["met_sharedWithOrganizations"]
	met["met"] -->|one_to_many| met_speciesV2["met_speciesV2"]
	met["met"] -->|one_to_many| met_tagIds["met_tagIds"]
	met["met"] -->|one_to_many| trial["trial"]
	met["met"] -->|one_to_one| met_factors["met_factors"]
	met["met"] -->|one_to_one| met_geography["met_geography"]
	met["met"] -->|one_to_one| met_market_segment_selection["met_market_segment_selection"]
	met["met"] -->|one_to_one| met_responsible_users["met_responsible_users"]
	met["met"] -->|one_to_one| met_team_users["met_team_users"]
	met_factors["met_factors"] -->|many_to_one| met["met"]
	met_factors["met_factors"] -->|one_to_one| met_factors_modalities["met_factors_modalities"]
	met_factors_moda_submoda["met_factors_moda_submoda"] -->|one_to_one| genotype["genotype"]
	met_factors_moda_submoda["met_factors_moda_submoda"] -->|one_to_one| other_factor_modalities["other_factor_modalities"]
	met_factors_moda_submoda["met_factors_moda_submoda"] -->|one_to_one| product["product"]
	met_factors_modalities["met_factors_modalities"] -->|many_to_one| met["met"]
	met_factors_modalities["met_factors_modalities"] -->|one_to_one| met_factors_moda_submoda["met_factors_moda_submoda"]
	met_factors_modalities["met_factors_modalities"] -->|one_to_one| modality["modality"]
	met_geography["met_geography"] -->|many_to_one| met["met"]
	met_market_segment["met_market_segment"] -->|one_to_many| met_market_segment_selection["met_market_segment_selection"]
	met_market_segment_selection["met_market_segment_selection"] -->|many_to_one| met_market_segment["met_market_segment"]
	met_market_segment_selection["met_market_segment_selection"] -->|one_to_one| met["met"]
	met_organization["met_organization"] -->|many_to_one| met["met"]
	met_responsible_users["met_responsible_users"] -->|many_to_one| user["user"]
	met_responsible_users["met_responsible_users"] -->|one_to_one| met["met"]
	met_sharedWithOrganizations["met_sharedWithOrganizations"] -->|many_to_one| met["met"]
	met_sharedWithOrganizations["met_sharedWithOrganizations"] -->|many_to_one| met_shared["met_shared"]
	met_speciesV2["met_speciesV2"] -->|many_to_one| met["met"]
	met_tagIds["met_tagIds"] -->|many_to_one| bloomeo_tag["bloomeo_tag"]
	met_tagIds["met_tagIds"] -->|many_to_one| met["met"]
	met_team_users["met_team_users"] -->|many_to_one| met["met"]
	met_team_users["met_team_users"] -->|many_to_one| user["user"]
	micro_market_segment["micro_market_segment"] -->|many_to_one| market_segment["market_segment"]
	micro_market_segment["micro_market_segment"] -->|one_to_many| micro_market_segment_geography["micro_market_segment_geography"]
	micro_market_segment["micro_market_segment"] -->|one_to_many| micro_market_segment_organization["micro_market_segment_organization"]
	micro_market_segment["micro_market_segment"] -->|one_to_many| micro_market_segment_species["micro_market_segment_species"]
	micro_market_segment_geography["micro_market_segment_geography"] -->|many_to_one| micro_market_segment["micro_market_segment"]
	micro_market_segment_organization["micro_market_segment_organization"] -->|many_to_one| micro_market_segment["micro_market_segment"]
	micro_market_segment_species["micro_market_segment_species"] -->|many_to_one| micro_market_segment["micro_market_segment"]
	modality["modality"] -->|one_to_one| modality_subModalities["modality_subModalities"]
	modality_subModalities["modality_subModalities"] -->|many_to_one| modality["modality"]
	modality_subModalities["modality_subModalities"] -->|one_to_one| genotype["genotype"]
	modality_subModalities["modality_subModalities"] -->|one_to_one| other_factor_modalities["other_factor_modalities"]
	modality_subModalities["modality_subModalities"] -->|one_to_one| product["product"]
	notation["notation"] -->|many_to_one| notebook["notebook"]
	notation["notation"] -->|many_to_one| variable["variable"]
	notation["notation"] -->|one_to_one| notation_info_value_ids["notation_info_value_ids"]
	notation["notation"] -->|one_to_one| notation_multi_notation_values["notation_multi_notation_values"]
	notation["notation"] -->|one_to_one| plot["plot"]
	notation["notation"] -->|one_to_one| plot_or_subplot["plot_or_subplot"]
	notation["notation"] -->|one_to_one| sub_plot["sub_plot"]
	notation_info_value_ids["notation_info_value_ids"] -->|many_to_one| notation["notation"]
	notation_multi_notation_values["notation_multi_notation_values"] -->|many_to_one| notation["notation"]
	notebook["notebook"] -->|many_to_one| notebook_geography["notebook_geography"]
	notebook["notebook"] -->|many_to_one| notebook_organization["notebook_organization"]
	notebook["notebook"] -->|many_to_one| notebook_species["notebook_species"]
	notebook["notebook"] -->|many_to_one| op_task["op_task"]
	notebook["notebook"] -->|many_to_one| trial["trial"]
	notebook["notebook"] -->|one_to_many| notebook_plot_variable["notebook_plot_variable"]
	notebook["notebook"] -->|one_to_many| notebook_trial_variable["notebook_trial_variable"]
	notebook_geography["notebook_geography"] -->|many_to_one| notebook["notebook"]
	notebook_organization["notebook_organization"] -->|many_to_one| notebook["notebook"]
	notebook_plot_variable["notebook_plot_variable"] -->|many_to_one| notebook["notebook"]
	notebook_plot_variable["notebook_plot_variable"] -->|many_to_one| variable["variable"]
	notebook_species["notebook_species"] -->|many_to_one| notebook["notebook"]
	notebook_trial_variable["notebook_trial_variable"] -->|many_to_one| notebook["notebook"]
	notebook_trial_variable["notebook_trial_variable"] -->|many_to_one| variable["variable"]
	op_task["op_task"] -->|belongs_to| met["met"]
	op_task["op_task"] -->|belongs_to| trial["trial"]
	op_task["op_task"] -->|many_to_one| op_task_geography["op_task_geography"]
	op_task["op_task"] -->|many_to_one| op_task_organization["op_task_organization"]
	op_task["op_task"] -->|many_to_one| op_task_species["op_task_species"]
	op_task["op_task"] -->|one_to_many| op_task_variables["op_task_variables"]
	op_task["op_task"] -->|one_to_one| op_task_team["op_task_team"]
	op_task_geography["op_task_geography"] -->|many_to_one| op_task["op_task"]
	op_task_organization["op_task_organization"] -->|many_to_one| op_task["op_task"]
	op_task_species["op_task_species"] -->|many_to_one| op_task["op_task"]
	op_task_team["op_task_team"] -->|many_to_one| user["user"]
	op_task_team["op_task_team"] -->|one_to_many| op_task["op_task"]
	op_task_variables["op_task_variables"] -->|many_to_one| op_task["op_task"]
	op_task_variables["op_task_variables"] -->|many_to_one| variable["variable"]
	other_factor_modalities["other_factor_modalities"] -->|one_to_many| other_factor["other_factor"]
	plot["plot"] -->|many_to_one| trial["trial"]
	plot["plot"] -->|one_to_one| treatment["treatment"]
	plot_or_subplot["plot_or_subplot"] -->|many_to_one| trial["trial"]
	plot_or_subplot["plot_or_subplot"] -->|one_to_many| notation["notation"]
	plot_or_subplot["plot_or_subplot"] -->|one_to_one| treatment["treatment"]
	product["product"] -->|many_to_one| modality_subModalities["modality_subModalities"]
	product["product"] -->|one_to_many| product_market_segment_selection["product_market_segment_selection"]
	product["product"] -->|one_to_many| product_organization["product_organization"]
	product["product"] -->|one_to_many| product_sharedWithOrganization["product_sharedWithOrganization"]
	product["product"] -->|one_to_many| product_species["product_species"]
	product_market_segment_selection["product_market_segment_selection"] -->|many_to_one| product["product"]
	product_market_segment_selection["product_market_segment_selection"] -->|one_to_many| product_market_segment["product_market_segment"]
	product_market_segment_selection["product_market_segment_selection"] -->|one_to_many| product_market_segment_selection_micro_market_segment_ids["product_market_segment_selection_micro_market_segment_ids"]
	product_market_segment_selection_micro_market_segment_ids["product_market_segment_selection_micro_market_segment_ids"] -->|many_to_one| market_segment["market_segment"]
	product_organization["product_organization"] -->|many_to_one| product["product"]
	product_sharedWithOrganization["product_sharedWithOrganization"] -->|many_to_one| product["product"]
	product_species["product_species"] -->|many_to_one| product["product"]
	resource["resource"] -->|many_to_one| genotype["genotype"]
	resource["resource"] -->|many_to_one| trial["trial"]
	resource["resource"] -->|many_to_one| variable["variable"]
	sub_plot["sub_plot"] -->|many_to_one| trial["trial"]
	sub_plot["sub_plot"] -->|one_to_one| plot["plot"]
	treatment["treatment"] -->|one_to_many| trial_conclusion_treatments["trial_conclusion_treatments"]
	treatment["treatment"] -->|one_to_one| plot_or_subplot["plot_or_subplot"]
	treatment["treatment"] -->|one_to_one| treatment_modalities["treatment_modalities"]
	treatment["treatment"] -->|one_to_one| trial["trial"]
	treatment_modalities["treatment_modalities"] -->|many_to_one| modality["modality"]
	treatment_modalities["treatment_modalities"] -->|one_to_one| treatment["treatment"]
	treatment_modalities["treatment_modalities"] -->|one_to_one| trial_factors_modalities["trial_factors_modalities"]
	trial["trial"] -->|many_to_one| trial_geography["trial_geography"]
	trial["trial"] -->|many_to_one| trial_organization["trial_organization"]
	trial["trial"] -->|many_to_one| trial_speciesV2["trial_speciesV2"]
	trial["trial"] -->|one_to_many| op_task["op_task"]
	trial["trial"] -->|one_to_many| plot["plot"]
	trial["trial"] -->|one_to_many| trial_location_coordinates["trial_location_coordinates"]
	trial["trial"] -->|one_to_many| trial_partner_selection["trial_partner_selection"]
	trial["trial"] -->|one_to_many| trial_sharedWithOrganizations["trial_sharedWithOrganizations"]
	trial["trial"] -->|one_to_many| trial_tagids["trial_tagids"]
	trial["trial"] -->|one_to_many| trial_templateobs_units_plot["trial_templateobs_units_plot"]
	trial["trial"] -->|one_to_many| trial_templateobs_units_trial["trial_templateobs_units_trial"]
	trial["trial"] -->|one_to_one| growing_area["growing_area"]
	trial["trial"] -->|one_to_one| met["met"]
	trial["trial"] -->|one_to_one| trial_factors["trial_factors"]
	trial["trial"] -->|one_to_one| trial_market_segment_selection["trial_market_segment_selection"]
	trial["trial"] -->|one_to_one| trial_responsible_users["trial_responsible_users"]
	trial["trial"] -->|one_to_one| trial_team_users["trial_team_users"]
	trial_concl_modality_advtag["trial_concl_modality_advtag"] -->|many_to_one| trial_conclusion_modalities["trial_conclusion_modalities"]
	trial_concl_modality_advtag["trial_concl_modality_advtag"] -->|one_to_many| bloomeo_tag["bloomeo_tag"]
	trial_concl_modality_dbktag["trial_concl_modality_dbktag"] -->|many_to_one| trial_conclusion_modalities["trial_conclusion_modalities"]
	trial_concl_modality_dbktag["trial_concl_modality_dbktag"] -->|one_to_many| bloomeo_tag["bloomeo_tag"]
	trial_concl_modality_warntag["trial_concl_modality_warntag"] -->|many_to_one| trial_conclusion_modalities["trial_conclusion_modalities"]
	trial_concl_modality_warntag["trial_concl_modality_warntag"] -->|one_to_many| bloomeo_tag["bloomeo_tag"]
	trial_concl_treatment_advtag["trial_concl_treatment_advtag"] -->|one_to_one| bloomeo_tag["bloomeo_tag"]
	trial_concl_treatment_advtag["trial_concl_treatment_advtag"] -->|one_to_one| trial_conclusion_treatments["trial_conclusion_treatments"]
	trial_concl_treatment_dbktag["trial_concl_treatment_dbktag"] -->|one_to_one| bloomeo_tag["bloomeo_tag"]
	trial_concl_treatment_dbktag["trial_concl_treatment_dbktag"] -->|one_to_one| trial_conclusion_treatments["trial_conclusion_treatments"]
	trial_concl_treatment_warntag["trial_concl_treatment_warntag"] -->|one_to_one| bloomeo_tag["bloomeo_tag"]
	trial_concl_treatment_warntag["trial_concl_treatment_warntag"] -->|one_to_one| trial_conclusion_treatments["trial_conclusion_treatments"]
	trial_conclusion["trial_conclusion"] -->|one_to_one| met["met"]
	trial_conclusion["trial_conclusion"] -->|one_to_one| trial["trial"]
	trial_conclusion["trial_conclusion"] -->|one_to_one| trial_conclusion_modalities["trial_conclusion_modalities"]
	trial_conclusion["trial_conclusion"] -->|one_to_one| trial_conclusion_treatments["trial_conclusion_treatments"]
	trial_conclusion_modalities["trial_conclusion_modalities"] -->|many_to_one| modality["modality"]
	trial_conclusion_modalities["trial_conclusion_modalities"] -->|many_to_one| trial_conclusion["trial_conclusion"]
	trial_conclusion_treatments["trial_conclusion_treatments"] -->|one_to_one| treatment["treatment"]
	trial_conclusion_treatments["trial_conclusion_treatments"] -->|one_to_one| trial_conclusion["trial_conclusion"]
	trial_factors["trial_factors"] -->|many_to_one| trial["trial"]
	trial_factors["trial_factors"] -->|one_to_one| trial_factors_modalities["trial_factors_modalities"]
	trial_factors_moda_submoda["trial_factors_moda_submoda"] -->|many_to_one| genotype["genotype"]
	trial_factors_moda_submoda["trial_factors_moda_submoda"] -->|many_to_one| other_factor_modalities["other_factor_modalities"]
	trial_factors_moda_submoda["trial_factors_moda_submoda"] -->|many_to_one| product["product"]
	trial_factors_moda_submoda["trial_factors_moda_submoda"] -->|many_to_one| trial["trial"]
	trial_factors_modalities["trial_factors_modalities"] -->|many_to_one| trial["trial"]
	trial_factors_modalities["trial_factors_modalities"] -->|one_to_one| modality["modality"]
	trial_factors_modalities["trial_factors_modalities"] -->|one_to_one| trial_factors_moda_submoda["trial_factors_moda_submoda"]
	trial_geography["trial_geography"] -->|many_to_one| trial["trial"]
	trial_market_segment["trial_market_segment"] -->|one_to_many| trial_market_segment_selection["trial_market_segment_selection"]
	trial_market_segment_selection["trial_market_segment_selection"] -->|many_to_one| trial["trial"]
	trial_market_segment_selection["trial_market_segment_selection"] -->|many_to_one| trial_market_segment["trial_market_segment"]
	trial_no_partner["trial_no_partner"] -->|hasMany| trial_partner_selection["trial_partner_selection"]
	trial_no_partner["trial_no_partner"] -->|many_to_one| growing_area["growing_area"]
	trial_no_partner["trial_no_partner"] -->|many_to_one| met["met"]
	trial_no_partner["trial_no_partner"] -->|many_to_one| plot["plot"]
	trial_no_partner["trial_no_partner"] -->|one_to_many| op_task["op_task"]
	trial_organization["trial_organization"] -->|many_to_one| trial["trial"]
	trial_partner_selection["trial_partner_selection"] -->|many_to_one| trial["trial"]
	trial_partner_selection["trial_partner_selection"] -->|one_to_one| partner["partner"]
	trial_responsible_users["trial_responsible_users"] -->|many_to_one| trial["trial"]
	trial_responsible_users["trial_responsible_users"] -->|many_to_one| user["user"]
	trial_sharedWithOrganizations["trial_sharedWithOrganizations"] -->|many_to_one| trial["trial"]
	trial_sharedWithOrganizations["trial_sharedWithOrganizations"] -->|many_to_one| trial_shared["trial_shared"]
	trial_speciesV2["trial_speciesV2"] -->|many_to_one| trial["trial"]
	trial_tagids["trial_tagids"] -->|many_to_one| bloomeo_tag["bloomeo_tag"]
	trial_tagids["trial_tagids"] -->|many_to_one| trial["trial"]
	trial_team_users["trial_team_users"] -->|many_to_one| trial["trial"]
	trial_team_users["trial_team_users"] -->|many_to_one| user["user"]
	trial_templateobs_units_plot["trial_templateobs_units_plot"] -->|one_to_one| variable["variable"]
	trial_templateobs_units_trial["trial_templateobs_units_trial"] -->|one_to_one| variable["variable"]
	user["user"] -->|many_to_one| tenant["tenant"]
	user["user"] -->|one_to_one| user_permission_profile["user_permission_profile"]
	user_permission_profile["user_permission_profile"] -->|many_to_one| role["role"]
	user_permission_profile["user_permission_profile"] -->|many_to_one| tenant["tenant"]
	user_permission_profile["user_permission_profile"] -->|one_to_one| user["user"]
	variable["variable"] -->|one_to_many| variable_species["variable_species"]

Tables

bloomeo_tag

SQL table/view: bloomeo_tag

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
context	string
systemmetadataCreatedby	string	Systemmetadata.createdby
systemmetadataUpdatedby	string	Systemmetadata.updatedby
tenantid	string
type	string
value	string
systemmetadataCreateddate	time	Systemmetadata.createddate
systemmetadataUpdateddate	time	Systemmetadata.updateddate

Measures

Name	Type	Title	Flags
count	count
concat	string	tags

Joins

None

experiment_subject

SQL table/view: experiment_subject

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
label	string
referenceid	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
trialid	string
type	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one

genotype

SQL table/view: genotype

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, public
name	string
species	string
isControl	boolean	Is Control
speciesFlatten	string
organizationFlatten	string
sharedWithOrganizationsFlatten	string
crossType	string
germplasmMaterialType	string
materialCreationProfile	string
variety	string
generation	string
externalId	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count
count_distinct	count_distinct

Joins

To cube	Relationship
genotype_species	one_to_many
genotype_organization	one_to_many
genotype_sharedWithOrganizations	one_to_many
modality_subModalities	many_to_one
met_factors_moda_submoda	one_to_one

genotype_market_segment

SQL table/view: genotype_market_segment

Dimensions

None

Measures

None

Joins

None

genotype_market_segment_selection

SQL table/view: genotype_market_segment_selection

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
genotypeId	string	Id
marketSegmentId	string	marketSegmentId
tenantId	string	Tenant Id

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
genotype	many_to_one
genotype_market_segment	many_to_one
genotype_market_segment_selection_micro_market_segment_ids	one_to_many

genotype_market_segment_selection_micro_market_segment_ids

SQL table/view: genotype_market_segment_selection_micro_market_segment_ids

Dimensions

Name	Type	Title	Flags
genotypeMarketSegmentId	string	Id	primary_key
marketSegmentSelection_microMarketSegmentIds_idx	number	microMarketSegmentId Idx	primary_key
marketSegmentSelection_microMarketSegmentId	string	microMarketSegmentId

Measures

Name	Type	Title	Flags
count	count

Joins

None

genotype_organization

SQL table/view: genotype_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

None

genotype_sharedWithOrganizations

SQL table/view: genotype_sharedWithOrganizations

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
sharedWithOrganization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Shared With Organizations

Joins

None

genotype_species

SQL table/view: genotype_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species

Joins

None

growing_area

SQL table/view: growing_area

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, public
comments	string
description	string
identifier	string
label	string
name	string
location	string
location_longitude	number
location_latitude	number
climateFlatten	string	Growing area climates
soilTypeFlatten	string	Growing area soil types
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
organizationFlatten	string	organization
geographyFlatten	string	geography

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
growing_area_geography	one_to_many
growing_area_organization	one_to_many

growing_area_climate

SQL table/view: growing_area_climate

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
climate	string

Measures

Name	Type	Title	Flags
count	count
concat	string	Growing area climates

Joins

To cube	Relationship
growing_area	many_to_one

growing_area_geography

SQL table/view: growing_area_geography

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
geography	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
growing_area	many_to_one

growing_area_organization

SQL table/view: growing_area_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organization

Joins

To cube	Relationship
growing_area	many_to_one

growing_area_soil_type

SQL table/view: growing_area_soil_type

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
soiltype	string

Measures

Name	Type	Title	Flags
count	count
concat	string	Growing area soil types

Joins

To cube	Relationship
growing_area	many_to_one

growing_area_users

SQL table/view: growing_area_users

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
users	string
tenantId	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
growing_area	many_to_one

label_printing_page_format

SQL table/view: label_printing_page_format

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
size	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

None

label_printing_profile

SQL table/view: label_printing_profile

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
codetype	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

None

label_printing_profile_fields

SQL table/view: label_printing_profile_fields

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
fields_type	string	Fields.type

Measures

Name	Type	Title	Flags
count	count

Joins

None

market_segment

SQL table/view: market_segment

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
description	string
comments	string
name	string
identifier	string
species	string
speciesFlatten	string
geographyFlatten	string
organizationFlatten	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate

Measures

Name	Type	Title	Flags
count	count
concat	string	market segments

Joins

To cube	Relationship
trial_market_segment_selection	one_to_many
met_market_segment_selection	one_to_many
market_segment_species	one_to_many
micro_market_segment	one_to_many

market_segment_species

SQL table/view: market_segment_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species

Joins

To cube	Relationship
market_segment	many_to_one

met

SQL table/view: met

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
description	string
label	string
materiallevel_germplasmlevel	string	Materiallevel.germplasmlevel
name	string
concludeStatus	string
objective	string
species	string
speciesFlatten	string
geographyFlatten	string
organizationFlatten	string
sharedWithOrganizationsFlatten	string
status	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
templateobsid	string
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
enddate	time
periodfrom	time
startdate	time
year	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met_market_segment_selection	one_to_one
met_factors	one_to_one
met_speciesV2	one_to_many
met_geography	one_to_one
met_organization	one_to_many
met_sharedWithOrganizations	one_to_many
met_team_users	one_to_one
met_responsible_users	one_to_one
met_tagIds	one_to_many
trial	one_to_many

met_factors

SQL table/view: met_factors

Dimensions

Name	Type	Title	Flags
metId	string	Id	primary_key
id	string
type	string
factor_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met_factors_modalities	one_to_one
met	many_to_one

met_factors_moda_submoda

SQL table/view: met_factors_moda_submoda

Dimensions

Name	Type	Title	Flags
metId	string	Id	primary_key, shown
refId	string
isDiscarded	string
localStage	string
isControl	boolean
factor_idx	number		primary_key
modality_idx	number		primary_key
subModalities_idx	number		primary_key
plannedNumberOfSeed	number
plannedNumberOfTrial	number
dosageValue	string
dosageUnit	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
genotype	one_to_one
product	one_to_one
other_factor_modalities	one_to_one

met_factors_modalities

SQL table/view: met_factors_modalities

Dimensions

Name	Type	Title	Flags
metId	string	Id	primary_key
modalityid	string
isControl	boolean
globalModalityId	string
shortId	string
modality_idx	number		primary_key
factor_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met_factors_moda_submoda	one_to_one
modality	one_to_one
met	many_to_one

met_geography

SQL table/view: met_geography

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
geography	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
met	many_to_one

met_market_segment

SQL table/view: met_market_segment

Dimensions

None

Measures

None

Joins

To cube	Relationship
met_market_segment_selection	one_to_many

met_market_segment_selection

SQL table/view: met_market_segment_selection

Dimensions

Name	Type	Title	Flags
metId	string	Id	primary_key
marketSegmentId	string	MarketSegmentSelection.marketSegmentId
marketSegmentSelection_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met	one_to_one
met_market_segment	many_to_one

met_market_segment_selection_micro_market_segment_ids

SQL table/view: met_market_segment_selection_micro_market_segment_ids

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
marketSegmentSelection_microMarketSegmentIds_idx	number	marketSegmentSelection.microMarketSegmentIds Idx
marketSegmentSelection_idx	number
marketSegmentSelection_microMarketSegmentIds	string	marketSegmentSelection.microMarketSegmentIds

Measures

Name	Type	Title	Flags
count	count

Joins

None

met_organization

SQL table/view: met_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

To cube	Relationship
met	many_to_one

met_responsible_users

SQL table/view: met_responsible_users

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
responsibleusers	string
responsibleusers_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met	one_to_one
user	many_to_one

met_shared

SQL table/view: met_shared

Dimensions

None

Measures

None

Joins

None

met_sharedWithOrganizations

SQL table/view: met_sharedWithOrganizations

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

To cube	Relationship
met	many_to_one
met_shared	many_to_one

met_speciesV2

SQL table/view: met_speciesV2

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
speciesV2	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species V2

Joins

To cube	Relationship
met	many_to_one

met_tagIds

SQL table/view: met_tagIds

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
tagId	string
tagId_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met	many_to_one
bloomeo_tag	many_to_one

met_team_users

SQL table/view: met_team_users

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
teamusers	string
teamusers_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
met	many_to_one
user	many_to_one

met_trials_draft

SQL table/view: met_trials_draft

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
trialsdraft_growingareaid	string	Trialsdraft.growingareaid
trialsdraft_name	string	Trialsdraft.name

Measures

Name	Type	Title	Flags
count	count

Joins

None

micro_market_segment

SQL table/view: micro_market_segment

Dimensions

Name	Type	Title	Flags
marketSegmentId	string
microMarketSegmentId	string	Micro Market Segment Id	primary_key, shown
description	string
name	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
identifier	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
micro_market_segment_geography	one_to_many
micro_market_segment_organization	one_to_many
micro_market_segment_species	one_to_many
market_segment	many_to_one

micro_market_segment_geography

SQL table/view: micro_market_segment_geography

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
geography	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
micro_market_segment	many_to_one

micro_market_segment_organization

SQL table/view: micro_market_segment_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organization

Joins

To cube	Relationship
micro_market_segment	many_to_one

micro_market_segment_species

SQL table/view: micro_market_segment_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species

Joins

To cube	Relationship
micro_market_segment	many_to_one

modality

SQL table/view: modality

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
hash	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
modality_subModalities	one_to_one

modality_subModalities

SQL table/view: modality_subModalities

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
refId	string
type	string
quantity	string
subModalities_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
genotype	one_to_one
product	one_to_one
other_factor_modalities	one_to_one
modality	many_to_one

notation

SQL table/view: notation

Dimensions

Name	Type	Title	Flags
id	string	Id	shown
value	string
finalValue	string
notebookId	string
subjectId	string
subjectType	string
variableId	string
tenantid	string
reviewStatus	number
notationDate	time
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate

Measures

Name	Type	Title	Flags
count	count
sum	sum
avg_value	number
max	max
min	min

Joins

To cube	Relationship
variable	many_to_one
plot_or_subplot	one_to_one
plot	one_to_one
sub_plot	one_to_one
notebook	many_to_one
notation_multi_notation_values	one_to_one
notation_info_value_ids	one_to_one

notation_info_value_ids

SQL table/view: notation_info_value_ids

Dimensions

Name	Type	Title	Flags
notationId	string	Id	primary_key
notationInfoValueId	string

Measures

None

Joins

To cube	Relationship
notation	many_to_one

notation_multi_notation_values

SQL table/view: notation_multi_notation_values

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
value	string
multinotationvalues_notationdate	time	Multinotationvalues.notationdate

Measures

Name	Type	Title
count	count
multinotationvalues_sum	sum	Multinotationvalues.sum
multinotationvalues_avg	avg	Multinotationvalues.avg
multinotationvalues_min	min	Multinotationvalues.min
multinotationvalues_max	max	Multinotationvalues.max

Joins

To cube	Relationship
notation	many_to_one

notebook

SQL table/view: notebook

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
observationroundid	string
observerid	string
status	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
trialid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
speciesFlatten	string
geographyFlatten	string
organizationFlatten	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one
op_task	many_to_one
notebook_plot_variable	one_to_many
notebook_trial_variable	one_to_many
notebook_species	many_to_one
notebook_geography	many_to_one
notebook_organization	many_to_one

notebook_completion_completion_details_variables_completion

SQL table/view: notebook_completion_completion_details_variables_completion

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
completion_completiondetails_variablescompletion_variable_variableid	string	Completion.completiondetails.variablescompletion.variable.variableid

Measures

Name	Type	Title	Flags
count	count

Joins

None

notebook_geography

SQL table/view: notebook_geography

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
geography	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
notebook	many_to_one

notebook_organization

SQL table/view: notebook_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

To cube	Relationship
notebook	many_to_one

notebook_plot_variable

SQL table/view: notebook_plot_variable

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
variable_id	string
tenantid	string

Measures

None

Joins

To cube	Relationship
variable	many_to_one
notebook	many_to_one

notebook_species

SQL table/view: notebook_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species

Joins

To cube	Relationship
notebook	many_to_one

notebook_trial_variable

SQL table/view: notebook_trial_variable

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
variable_id	string
tenantid	string

Measures

None

Joins

To cube	Relationship
variable	many_to_one
notebook	many_to_one

op_task

SQL table/view: op_task

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
description	string
experimentid	string
label	string
metadata_createdby	string	Metadata.createdby
name	string
stage	string
status	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
trialid	string
type	string
variablegroupid	string
metadata_createddate	time	Metadata.createddate
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
enddate	time
startdate	time
speciesFlatten	string
geographyFlatten	string
organizationFlatten	string

Measures

Name	Type	Title	Flags
count	count
count_completed	count
completion_rate	number
count_incomplete	count
count_obs_round	count
count_ops	count

Segments

Name	Type	Title	Flags
completed
not_completed

Joins

To cube	Relationship
op_task_variables	one_to_many
op_task_team	one_to_one
op_task_species	many_to_one
op_task_geography	many_to_one
op_task_organization	many_to_one
trial	belongs_to
met	belongs_to

op_task_completion_completion_details_variables_completion

SQL table/view: op_task_completion_completion_details_variables_completion

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
completion_completiondetails_variablescompletion_variable_variableid	string	Completion.completiondetails.variablescompletion.variable.variableid

Measures

Name	Type	Title	Flags
count	count

Joins

None

op_task_geography

SQL table/view: op_task_geography

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
geography	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
op_task	many_to_one

op_task_organization

SQL table/view: op_task_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

To cube	Relationship
op_task	many_to_one

op_task_species

SQL table/view: op_task_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species

Joins

To cube	Relationship
op_task	many_to_one

op_task_team

SQL table/view: op_task_team

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
team	string
team_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
op_task	one_to_many
user	many_to_one

op_task_variables

SQL table/view: op_task_variables

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
variableId	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
variable	many_to_one
op_task	many_to_one

other_factor

SQL table/view: other_factor

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, public
name	string	Name
code	string	Code
description	string	Description

Measures

None

Joins

None

other_factor_modalities

SQL table/view: other_factor_modalities

Dimensions

Name	Type	Title	Flags
other_factor_id	string	Id	primary_key, public
modalities_idx	number		primary_key
id	string	Id	primary_key, public
name	string	Name
description	string	Description
isControl	boolean	Is Control
externalId	string	External Id

Measures

None

Joins

To cube	Relationship
other_factor	one_to_many

partner

SQL table/view: partner

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
accountid	string
accountname	string
country	string
location_type	string	Location.type
region	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate

Measures

Name	Type	Title	Flags
count	count

Joins

None

partner_contacts

SQL table/view: partner_contacts

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
contacts_id	string	Contacts. Id
contacts_email	string	Contacts.email
contacts_firstname	string	Contacts.firstname
contacts_lastname	string	Contacts.lastname
contacts_mobilephone	string	Contacts.mobilephone

Measures

Name	Type	Title	Flags
count	count

Joins

None

partner_growing_area_ids

SQL table/view: partner_growing_area_ids

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
growingareaids	string

Measures

Name	Type	Title	Flags
count	count

Joins

None

partner_location_coordinates

SQL table/view: partner_location_coordinates

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

None

partner_partner_types

SQL table/view: partner_partner_types

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
partnertypes	string

Measures

Name	Type	Title	Flags
count	count

Joins

None

plot

SQL table/view: plot

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
label	string
code	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
parentId	string
trialid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
repnumber	string
qty_of_seeds	number

Measures

Name	Type	Title	Flags
count	count
qty_of_seeds_sum	sum

Joins

To cube	Relationship
treatment	one_to_one
trial	many_to_one

plot_or_subplot

SQL table/view: plot_or_subplot

Dimensions

Name	Type	Title	Flags
mergeid	string	mergeId	primary_key, shown
id	string
label	string
code	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
parentId	string
trialid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
repnumber	string
qty_of_seeds	number

Measures

None

Joins

To cube	Relationship
treatment	one_to_one
trial	many_to_one
notation	one_to_many

product

SQL table/view: product

Dimensions

Name	Type	Title	Flags
name	string	Name
id	string	Id	primary_key, public
officialName	string
alternateName	string
speciesFlatten	string
organizationFlatten	string
sharedWithOrganizationsFlatten	string
isControl	boolean
ownership	string
companyName	string
externalId	string
comments	string
label	string
formulation	string
formulationUnit	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count
count_distinct	count_distinct

Joins

To cube	Relationship
product_species	one_to_many
product_organization	one_to_many
product_sharedWithOrganization	one_to_many
product_market_segment_selection	one_to_many
modality_subModalities	many_to_one

product_market_segment

SQL table/view: product_market_segment

Dimensions

None

Measures

None

Joins

None

product_market_segment_selection

SQL table/view: product_market_segment_selection

Dimensions

Name	Type	Title	Flags
product_market_segments_join_id	string	Id	primary_key
productId	string	Id
marketSegmentId	string	MarketSegmentSelection.marketSegmentId

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
product	many_to_one
product_market_segment	one_to_many
product_market_segment_selection_micro_market_segment_ids	one_to_many

product_market_segment_selection_micro_market_segment_ids

SQL table/view: product_market_segment_selection_micro_market_segment_ids

Dimensions

Name	Type	Title	Flags
productId	string	Id	primary_key
climate	string
marketSegmentId	string	MarketSegmentSelection.marketSegmentId
microMarketSegmentId	string	MicroMarketSegmentId

Measures

Name	Type	Title	Flags
count	count
concat	string	Growing area climates

Joins

To cube	Relationship
market_segment	many_to_one

product_organization

SQL table/view: product_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
product	many_to_one

product_sharedWithOrganization

SQL table/view: product_sharedWithOrganization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
shared_with_organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
product	many_to_one

product_species

SQL table/view: product_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
product	many_to_one

resource

SQL table/view: resource

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
resourceType	string
like	string
publicId	string
assetId	string
url	string
metadata_trialId	string	Metadata.trialid
metadata_materialId	string	Metadata.materialid
metadata_variableId	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one
genotype	many_to_one
variable	many_to_one

role

SQL table/view: role

Dimensions

Name	Type	Flags
id	string	primary_key, shown
name	string
is_custom	boolean
base_role_id	string
permissions	string
created_by	string
created_date	time
updated_by	string
updated_date	time
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

None

sub_plot

SQL table/view: sub_plot

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
label	string
code	string
subPlotNumber	string
isDiscarded	boolean
tenantid	string
parentId	string
trialid	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate

Measures

None

Joins

To cube	Relationship
plot	one_to_one
trial	many_to_one

tenant

SQL table/view: tenant

Dimensions

Name	Type	Title	Flags
id	string		primary_key

Measures

None

Joins

None

treatment

SQL table/view: treatment

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
qty_of_seeds	number
label	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string
trialid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
treatmentnumber	string
replications	number
metTreatmentId	string

Measures

Name	Type	Title	Flags
count	count
qty_of_seeds_sum	sum

Joins

To cube	Relationship
plot_or_subplot	one_to_one
trial_conclusion_treatments	one_to_many
treatment_modalities	one_to_one
trial	one_to_one

treatment_modalities

SQL table/view: treatment_modalities

Dimensions

Name	Type	Title	Flags
treatmentId	string	Id	primary_key, shown
expModalityId	string
globalModalityId	string
modalities_idx	number
trialId	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
treatment	one_to_one
trial_factors_modalities	one_to_one
modality	many_to_one

trial

SQL table/view: trial

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key, shown
comments	string
concludeStatus	string
contract	string
cultivationmethod	string
currency	string
description	string
growingareaid	string
label	string
location_type	string	Location.type
materiallevel_germplasmlevel	string	Materiallevel.germplasmlevel
metid	string
name	string
objective	string
speciesFlatten	string
geographyFlatten	string
organizationFlatten	string
sharedWithOrganizationsFlatten	string
species	string
status	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
templateobsid	string
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
enddate	time
startdate	time
plantsnumber	number
rowsnumber	number
cost	number
year	number
tagIdsFlatten	string	Trial tags

Measures

Name	Type	Title	Flags
count	count
cost_sum	sum

Joins

To cube	Relationship
met	one_to_one
growing_area	one_to_one
plot	one_to_many
op_task	one_to_many
trial_templateobs_units_plot	one_to_many
trial_templateobs_units_trial	one_to_many
trial_market_segment_selection	one_to_one
trial_speciesV2	many_to_one
trial_geography	many_to_one
trial_organization	many_to_one
trial_sharedWithOrganizations	one_to_many
trial_team_users	one_to_one
trial_responsible_users	one_to_one
trial_tagids	one_to_many
trial_partner_selection	one_to_many
trial_location_coordinates	one_to_many
trial_factors	one_to_one

trial_concl_modality_advtag

SQL table/view: trial_concl_modality_advtag

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key, shown
tagId	string
conclusionModalities_idx	number		primary_key
advantagesTags_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion_modalities	many_to_one
bloomeo_tag	one_to_many

trial_concl_modality_dbktag

SQL table/view: trial_concl_modality_dbktag

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key, shown
tagId	string
conclusionModalities_idx	number		primary_key
drawbackTags_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion_modalities	many_to_one
bloomeo_tag	one_to_many

trial_concl_modality_warntag

SQL table/view: trial_concl_modality_warntag

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key, shown
tagId	string
conclusionModalities_idx	number		primary_key
warningTags_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion_modalities	many_to_one
bloomeo_tag	one_to_many

trial_concl_treatment_advtag

SQL table/view: trial_concl_treatment_advtag

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	shown
tagId	string
conclusionTreatments_idx	number		primary_key
advantagesTags_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion_treatments	one_to_one
bloomeo_tag	one_to_one

trial_concl_treatment_dbktag

SQL table/view: trial_concl_treatment_dbktag

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key, shown
tagId	string
conclusionTreatments_idx	number		primary_key
drawbackTags_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion_treatments	one_to_one
bloomeo_tag	one_to_one

trial_concl_treatment_warntag

SQL table/view: trial_concl_treatment_warntag

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key, shown
tagId	string
conclusionTreatments_idx	number		primary_key
warningTags_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion_treatments	one_to_one
bloomeo_tag	one_to_one

trial_conclusion

SQL table/view: trial_conclusion

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
experimentId	string
experimentType	boolean
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	one_to_one
met	one_to_one
trial_conclusion_treatments	one_to_one
trial_conclusion_modalities	one_to_one

trial_conclusion_modalities

SQL table/view: trial_conclusion_modalities

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key
conclusionId	string	Id	primary_key
modality_id	string
preconisation	boolean
preconisation_comments	string
conclusionModalities_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_conclusion	many_to_one
modality	many_to_one

trial_conclusion_treatments

SQL table/view: trial_conclusion_treatments

Dimensions

Name	Type	Title	Flags
trial_conclusion_id	string	Id	primary_key, shown
treatment_id	string
preconisation	boolean
preconisation_comments	string
conclusionTreatments_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
treatment	one_to_one
trial_conclusion	one_to_one

trial_factors

SQL table/view: trial_factors

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key
id	string
type	string
factor_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_factors_modalities	one_to_one
trial	many_to_one

trial_factors_moda_submoda

SQL table/view: trial_factors_moda_submoda

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key, shown
refId	string
isDiscarded	string
localStage	string
isControl	boolean
factor_idx	number		primary_key
modality_idx	number		primary_key
subModalities_idx	number		primary_key
plannedNumberOfSeed	number
plannedNumberOfTrial	number
dosageValue	string
dosageUnit	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
genotype	many_to_one
product	many_to_one
other_factor_modalities	many_to_one
trial	many_to_one

trial_factors_modalities

SQL table/view: trial_factors_modalities

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key
id	string
isControl	boolean
globalModalityId	string
shortId	string
modality_idx	number		primary_key
factor_idx	number		primary_key

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial_factors_moda_submoda	one_to_one
modality	one_to_one
trial	many_to_one

trial_geography

SQL table/view: trial_geography

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
geography	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Geographies

Joins

To cube	Relationship
trial	many_to_one

trial_location_coordinates

SQL table/view: trial_location_coordinates

Dimensions

Name	Type	Title	Flags
trial_id	string	Trial Id	primary_key
coordinate	number	coordinate
coordinate_idx	number	0 is latitude, 1 is longitude

Measures

Name	Type	Title	Flags
count	count

Joins

None

trial_market_segment

SQL table/view: trial_market_segment

Dimensions

None

Measures

None

Joins

To cube	Relationship
trial_market_segment_selection	one_to_many

trial_market_segment_selection

SQL table/view: trial_market_segment_selection

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key
marketSegmentId	string	MarketSegmentSelection.marketSegmentId
marketSegmentSelection_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one
trial_market_segment	many_to_one

trial_market_segment_selection_micro_market_segment_ids

SQL table/view: trial_market_segment_selection_micro_market_segment_ids

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
marketSegmentSelection_microMarketSegmentIds_idx	number	marketSegmentSelection.microMarketSegmentIds Idx
marketSegmentSelection_idx	number
marketSegmentSelection_microMarketSegmentIds	string	marketSegmentSelection.microMarketSegmentIds

Measures

Name	Type	Title	Flags
count	count

Joins

None

trial_no_partner

SQL table/view: trial_no_partner

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
comments	string
conclusion	string
contract	string
cultivationmethod	string
currency	string
description	string
growingareaid	string
label	string
location_type	string	Location.type
materiallevel_germplasmlevel	string	Materiallevel.germplasmlevel
metid	string
name	string
objective	string
program	string
project	string
species	string
status	string
systemmetadata_createdby	string	Systemmetadata.createdby
systemmetadata_updatedby	string	Systemmetadata.updatedby
templateobsid	string
tenantid	string
systemmetadata_createddate	time	Systemmetadata.createddate
systemmetadata_updateddate	time	Systemmetadata.updateddate
enddate	time
startdate	time
plantsnumber	number
rowsnumber	number
cost	number

Measures

Name	Type	Title	Flags
count	count
count_distinct	count_distinct

Joins

To cube	Relationship
met	many_to_one
growing_area	many_to_one
plot	many_to_one
op_task	one_to_many
trial_partner_selection	hasMany

trial_organization

SQL table/view: trial_organization

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

To cube	Relationship
trial	many_to_one

trial_partner_selection

SQL table/view: trial_partner_selection

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
partnerselection_idx	number		primary_key
partnerselection_partnerid	string	Partnerselection.partnerid

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
partner	one_to_one
trial	many_to_one

trial_partner_selection_contact_ids

SQL table/view: trial_partner_selection_contact_ids

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
partnerselection_contactids_idx	number	Partnerselection.contactids Idx
partnerselection_idx	number
partnerselection_contactids	string	Partnerselection.contactids

Measures

Name	Type	Title	Flags
count	count

Joins

None

trial_responsible_users

SQL table/view: trial_responsible_users

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
responsibleusers	string
responsibleusers_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one
user	many_to_one

trial_shared

SQL table/view: trial_shared

Dimensions

None

Measures

None

Joins

None

trial_sharedWithOrganizations

SQL table/view: trial_sharedWithOrganizations

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
organization	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Organizations

Joins

To cube	Relationship
trial	many_to_one
trial_shared	many_to_one

trial_speciesV2

SQL table/view: trial_speciesV2

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
speciesV2	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species V2

Joins

To cube	Relationship
trial	many_to_one

trial_tagids

SQL table/view: trial_tagids

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key, shown
tagId	string
tagId_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one
bloomeo_tag	many_to_one

trial_team_users

SQL table/view: trial_team_users

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
teamusers	string
teamusers_idx	number

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
trial	many_to_one
user	many_to_one

trial_templateobs_units_plot

SQL table/view: trial_templateobs_units_plot

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key
templateobsunits_plot_variableid	string	Templateobsunits.plot.variableid

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
variable	one_to_one

trial_templateobs_units_trial

SQL table/view: trial_templateobs_units_trial

Dimensions

Name	Type	Title	Flags
trialId	string	Id	primary_key
templateobsunits_trial_variableid	string	Templateobsunits.trial.variableid

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
variable	one_to_one

user

SQL table/view: user

Dimensions

Name	Type	Flags
id	string	primary_key
externalid	string
firstname	string
lastname	string
mail	string
deactivated	boolean
color	string
tenantid	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
tenant	many_to_one
user_permission_profile	one_to_one

user_permission_profile

SQL table/view: user_permission_profile

Dimensions

Name	Type	Flags
id	string	primary_key
tenantid	string
geography	string
organization	string
species	string
roleid	string
baseroleid	string
userid	string

Measures

Name	Type	Title	Flags
count	count

Joins

To cube	Relationship
tenant	many_to_one
role	many_to_one
user	one_to_one

variable

SQL table/view: variable

Dimensions

Name	Type	Title	Flags
id	string	Variable Id	primary_key, public
comments	string
constraint_nbdigits	string	Variable Constraints
description	string
identifier	string
inputtype	string	Input Type
limited_choices	string	Limited Choices
is_used_for_permission	boolean	Is Used For Permission
name_en	string	Name
short_name	string	Short Name
created_by	string	Created By
updated_by	string	Updated By
tenantid	string	Tenant ID
type	string	Variable Type
unit	string
created_date	time	Created Date
updated_date	time	Updated Date
deactivated	boolean	Is Deactivated
speciesFlatten	string	Variable species

Measures

Name	Type	Title	Flags
count	count
sum	sum
count_distinct	count_distinct

Joins

To cube	Relationship
variable_species	one_to_many

variable_limited_choices

SQL table/view: variable_limited_choices

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
limitedchoices_label	string	Limitedchoices.label
limitedchoices_value	string	Limitedchoices.value

Measures

Name	Type	Title	Flags
count	count

Joins

None

variable_species

SQL table/view: variable_species

Dimensions

Name	Type	Title	Flags
id	string	Id	primary_key
species	string
level	number

Measures

Name	Type	Title	Flags
count	count
concat	string	Species

Joins

None