Concepts, Mappings, and Views

This section is about Timbr SQL Data Definition Language (DDL) queries to create concepts, mappings, and views in an ontology

Create or replace concept

Create or replace a concept in an ontology.

Required information for create or replace a concept (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to create
{property_name1} - an example of a property name to be associated with the concept
{property_type1} - The data type associated with the property
{property_name2} - an example of a property name to be associated with the concept
{property_type2} - The data type associated with the property
{inherits_from_concept} - The parent concept of the concept you want to create
{concept_description} - Optional. A description of the concept

CREATE OR REPLACE CONCEPT `{concept_name}` (`{property_name1}` {property_type1}, `{property_name2}` {property_type2}, 
    PRIMARY KEY(`{property_name1}`), 
    LABEL(`{property_name2}`), 
) INHERITS (`{inherits_from_concept}`)
DESCRIPTION '{concept_description}';

Create / Update a tag of a concept

Create or update a tag of a concept

Required information for create or update a tag of a concept (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to add the tag to
{tag_name} - The tag key (name) to be added or updated to the concept
{tag_value} - The tag value to be associated with the tag key of the concept

ALTER CONCEPT `{concept_name}` UPDATE TAG `{tag_name}`='{tag_value}';

Remove a tag from a concept

Remove a tag of a concept

Required information for removing a tag of a concept (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to remove the tag from
{tag_name} - The tag key (name) to be removed from the concept

ALTER CONCEPT `{concept_name}` DROP TAG `{tag_name}`;

Create / Update a tag of a property

Create or update a tag of a property

Required information for create or update a tag of a property (the curly brackets {} should not be an input, they are used only as a variable substitution):

{property_name} - The name of the property you want to add the tag to
{tag_name} - The tag key (name) to be added or updated to the property
{tag_value} - The tag value to be associated with the tag key of the property

ALTER PROPERTY `{property_name}` UPDATE TAG `{tag_name}`='{tag_value}';

Remove a tag from a property

Remove a tag of a property

Required information for removing a tag of a property (the curly brackets {} should not be an input, they are used only as a variable substitution):

{property_name} - The name of the property you want to remove the tag from
{tag_name} - The tag key (name) to be removed from the property

ALTER PROPERTY `{property_name}` DROP TAG `{tag_name}`;

Create or replace a calculated property

Create and edit calculated properties for a concept

Required information for creating or replacing a calculated property (the curly brackets {} should not be an input, they are used only as a variable substitution):

{property_name} - The name of the property you want to create or replace
{property_type} - The type of the property you want to create or replace
{description_text} - Optional. The description of the property
{calculation_or_transformation} - The calculation or transformation you want to apply on the property

Create a calculated property

CREATE PROPERTY `{property_name}` {property_type} DESCRIPTION '{description_text}' AS SELECT {calculation_or_transformation};

Example:

CREATE PROPERTY `usd_revenue` decimal DESCRIPTION 'Euro Revenue calculated in USD currency' AS SELECT eu_revenue * 1.07;

Replace or edit a calculated property

CREATE OR REPLACE PROPERTY `{property_name}` {property_type} DESCRIPTION '{description_text}' AS SELECT {calculation_or_transformation};

Example:

CREATE OR REPLACE PROPERTY `usd_revenue` decimal DESCRIPTION 'Euro Revenue calculated in USD currency as of Jan23' AS SELECT eu_revenue * 1.07;

Creating a calculated property over an existing property

Users can create calculated properties over existing properties by simply following the SQL template above of - CREATE OR REPLACE PROPERTY...

Create or replace concept relationships

Create or replace a concept with relationship

Required information for create or replace a concept with a relationship (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to create
{property_name1} - an example of a property name to be associated with the concept
{property_type1} - The data type associated with the property
{property_name2} - an example of a property name to be associated with the concept and used in the relationship
{property_type2} - The data type associated with the property
{relationship_name} - The relationship name you want to create as it will appear in this concept queries and metadata
{target_concept} - The target concept to which the relationships point to
{target_concept_property} - The property in the target concept which is related to the property in the source concept
{inverse_relationship_name} - The relationship name as it will appear from the target concept context.
{inherits_from_concept} - The parent concept of the concept you want to create
{concept_description} - Optional. A description of the concept

CREATE OR REPLACE CONCEPT `{concept_name}` (`{property_name}` {property_type}, `{property_name2}` {property_type2},
    PRIMARY KEY(`{property_name}`), 
    LABEL(`{property_name2}`),
    CONSTRAINT `{relationship_name}`
        FOREIGN KEY (`{property_name2}`)
        REFERENCES `{target_concept}` (`{target_concept_property}`)
        INVERSEOF `{inverse_relationship_name}`
) INHERITS (`{inherits_from_concept}`)
DESCRIPTION '{concept_description}';

Create or replace concept transitive relationships

Create or replace a concept with a transitive relationship.

Required information for create or replace a concept with a transitive relationship (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to create
{property_name1} - an example of a property name to be associated with the concept
{property_type1} - The data type associated with the property
{property_name2} - an example of a property name to be associated with the concept and used in the relationship
{property_type2} - The data type associated with the property
{relationship_name} - The relationship name you want to create as it will appear in this concept queries and metadata
{target_concept} - The target concept to which the relationships point to
{target_concept_property} - The property in the target concept which is related to the property in the source concept
{inverse_relationship_name} - The relationship name as it will appear from the target concept context.
{transitive_depth} - The maximum number of transitive hops for the relationship
{inherits_from_concept} - The parent concept of the concept you want to create
{concept_description} - Optional. A description of the concept

CREATE OR REPLACE CONCEPT `{concept_name}` (`{property_name}` {property_type}, `{property_name2}` {property_type2},
    PRIMARY KEY(`{property_name}`), 
    LABEL(`{property_name2}`),
    CONSTRAINT `{relationship_name}`
        FOREIGN KEY (`{property_name2}`)
        REFERENCES `{target_concept}` (`{target_concept_property}`)
        INVERSEOF `{inverse_relationship_name}` 
        TRANSITIVE ({transitive_depth}) 
) INHERITS (`{inherits_from_concept}`)
DESCRIPTION '{concept_description}';

Create or replace logic concept

Create or replace a logic concept (based on a filter)

Required information for create or replace a logic concept (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to create
{inherits_from_concept} - The parent concept of the concept you want to create
{concept_to_query} - The parent concept to query for the filter
{sql_query_filter} - The query filter to be performed on which the logic concept is based on

CREATE OR REPLACE CONCEPT `{concept_name}`
    INHERITS (`{inherits_from_concept}`)
    FROM `timbr`.`{concept_to_query}`
    WHERE {sql_query_filter};

Create or replace mapping

Create or replace a concept mapping

Required information for create or replace a concept mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to create
{target_concept} - The name of the concept associated with the mapping
{table_column} - The table column to be mapped (can also be a calculated column)
{property_of_concept} - The property of the column associated with the table column (or calculated column)
{source_database} - The source database from which the mapping will occur
{source_table} - The source table from which the mapping will occur

CREATE OR REPLACE MAPPING `{mapping_name}` INTO (`{target_concept}`) 
    AS 
        SELECT `{table_column}` AS `{property_of_concept}`
        FROM {source_database}.{source_table};

Create or replace many-to-many mapping

Create or replace a many-to-many mapping to a concept. Many to many mappings are usually created between two concepts, or a concept and itself, where the mapping goes through an EAV table (Entity-Attribute-Value table) which stores the information about the relationship between two tables.

Required information for create or replace a many-to-many mapping to a concept (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to create
{relationship_name} - The many-to-many relationship name you want to create as it will appear in this concept queries and metadata
{eav_column1} - The table column from the EAV table to be mapped to the source concept
{source_concept} - The name of the source concept associated with the many-to-many relationship
{source_concept_property} - The property from the source concept to be related to the property of the target concept
{inverse_relationship_name} - The many-to-many relationship name as it will appear from the target concept context
{eav_column2} - The table column from the EAV table to be mapped to the target concept
{target_concept} - The target concept to which the many-to-many relationship points to
{target_concept_property} - The property in the target concept which is related to the property in the source concept
{eav_database} - The source database from which the mapping will occur
{eav_table} - The source table from which the mapping will occur

CREATE OR REPLACE MAPPING `{mapping_name}` (
    CONSTRAINT `{relationship_name}`
        FOREIGN KEY (`{eav_column1}`)
        REFERENCES `{source_concept}` (`{source_concept_property}`), 
    CONSTRAINT `{inverse_relationship_name}`
        FOREIGN KEY (`{eav_column2}`)
        REFERENCES `{target_concept}` (`{target_concept_property}`))
    AS 
        SELECT `{eav_column1}` AS `{source_concept_property}`,
            `{eav_column2}` AS `{target_concept_property}`
        FROM `{eav_database}`.`{eav_table}`;

Create or replace mv mapping

Create or replace a multi-value mapping

Required information for create or replace a multi-value mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to create
{added_property} - The property to be added from the multi-value table
{added_property_type} - The data type of the property to be added from the multi-value table
{mv_table_column} - The related column of the table to be mapped from the mediary property of the concept
{target_concept} - The name of the concept associated with the mapping
{mediary_property} - The related property of the concept to be mapped to the mediary table column
{added_column} - The table column (can also be a calculated column) to be mapped to the added property
{mv_database} - The source database from which the mapping will occur
{mv_table} - The source table from which the mapping will occur

CREATE OR REPLACE MAPPING `{mapping_name}` (`{added_property}` `{added_property_type}`, 
    FOREIGN KEY (`{mv_table_column}`) 
    REFERENCES `{target_concept}`(`{mediary_property}`)) 
    AS 
        SELECT `{mv_table_column}` AS `{mediary_property}`,
            `{added_column}` AS `{added_property}` 
        FROM `{mv_database}`.`{mv_table}`;

Create an ontology view

Create an ontology view which can be based on the ontology or the underlying datasources.

Required information for create an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the ontology view you want to create
{sql_for_view} - The SQL statement for the view you want to create

CREATE ONTOLOGY VIEW `{view_name}` AS {sql_for_view};

Creating a view with tags

You can also specify creating a view with tags by default in the create statement.

{tag_name} - The name of the tag to be associated with the view
{tag_value} - The value for the tag associated with the view

CREATE ONTOLOGY VIEW `{view_name}` WITH TAGS (`{tag_name}` = `{tag_value}`) AS {sql_for_view};

Create or replace ontology view with or without previous tags

Instead of running CREATE ONTOLOGY VIEW... command, some users prefer running CREATE OR REPLACE ONTOLOGY VIEW overwriting the previous ontology view. In those cases, a user may choose to maintain the previousely set tags of the ontology view or remove them

If you run a CREATE OR REPLACE command omitting the WITH TAGS (...) parts, then the previously set tags will be maintained
If you wish to remove the tags as well, then you can add to the CREATE OR REPLACE STATEMENT the part of WITH TAGS() and then the previousely set tags will also be removed.

Change ontology view description

Changes the description of an ontology view

Required information for changing the description of an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the ontology view you want to associate the description with
{view_description} - A string representing the description associated with the ontology view

ALTER ONTOLOGY VIEW `{view_name}` SET DESCRIPTION='{view_description}'

Create or update a tag of an ontology view

Required information for create or update a tag of an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{ontology_view_name} - The name of the ontology view you want to add the tag to
{tag_name} - The tag key (name) to be added or updated to the ontology view
{tag_value} - The tag value to be associated with the tag key of the ontology view

ALTER ONTOLOGY VIEW `{ontology_view_name}` UPDATE TAG `{tag_name}`='{tag_value}';

Remove a tag from an ontology view

Remove a tag of an ontology view

Required information for removing a tag of an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{ontology_view_name} - The name of the ontology view you want to remove the tag from
{tag_name} - The tag key (name) to be removed from the ontology view

ALTER ONTOLOGY VIEW `{ontology_view_name}` DROP TAG `{tag_name}`;

Remove a concept

Removes a concept from an ontology.

Required information for removing a concept from the ontology (the curly brackets {} should not be an input, they are used only as a variable substitution):

{concept_name} - The name of the concept you want to remove

DROP CONCEPT `{concept_name}`;

Remove a mapping

Removes a mapping from an ontology.

Required information for removing a mapping from the ontology (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to remove

DROP MAPPING `{mapping_name}`;

Remove an ontology view

Removes an ontology view from an ontology.

Required information for removing an ontology view from the ontology (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the mapping you want to remove

DROP ONTOLOGY VIEW  `{view_name}`;

Create or replace concept​

Create / Update a tag of a concept​

Remove a tag from a concept​

Create / Update a tag of a property​

Remove a tag from a property​

Create or replace a calculated property​

Create or replace concept relationships​

Create or replace concept transitive relationships​

Create or replace logic concept​

Create or replace mapping​

Create or replace many-to-many mapping​

Create or replace mv mapping​

Create an ontology view​

Change ontology view description​

Create or update a tag of an ontology view​

Remove a tag from an ontology view​

Remove a concept​

Remove a mapping​

Remove an ontology view​