Object Types

Nouns are the foundational elements of the ontology graph in TextQL. They represent key entities or objects relevant to a business’s operations and analysis. By conceptualizing data entities as nouns, TextQL simplifies the interaction between the user and complex data structures, making it more intuitive for non-technical users to query and understand data.

​

Characteristics of Nouns:

• Direct Mapping: Although nouns usually correspond directly to tables within a data warehouse, they offer flexibility. This means a noun might represent a complex aggregation or a simplified view of multiple tables combined, depending on what makes most sense for end-user interaction and query simplicity.

• Business-Centric: Nouns are defined in business terms, making them easily recognizable and usable by business users. This approach abstracts the technical complexities of the data model, focusing instead on the logical business concepts.

• Flexibility and Adaptability: The ontology allows for the representation of entities that may not have a direct one-to-one correlation with the underlying data model. This flexibility ensures that the ontology can evolve to meet changing business needs without requiring modifications to the data warehouse structure.

Examples of Nouns:

• Customer: Represents individual customers or clients of the business. This noun could map directly to a Customers table in the data warehouse, encapsulating details such as customer ID, name, contact information, and transaction history.
• Product: Denotes the items or services offered by the business. The Product noun could aggregate data from multiple tables that detail product information, pricing, categorization, and inventory levels.
• Sale: Reflects sales transactions. This could be a complex noun that aggregates data from sales headers, details tables, and might also include derived metrics such as total sales amount, quantity sold, and associated customer and product information.

By defining nouns, TextQL creates an intuitive layer over the data warehouse that enables users to ask questions and explore data using familiar business terminology. This approach not only simplifies data access for non-technical users but also ensures that queries are consistent with the logical business model, enhancing the accuracy and relevance of insights derived from the data.

​

How to create a noun:

First, click the ‘New Noun’ button in the ontology panel, and select the noun backing:

• Backing Table: One table that has all of the main columns that should be represented in this concept
• SQL Query: A SQL query that represents a logic view containing all the columns you want to include. This is most useful if you want to normalize your data. Work in progress noun types that aren’t supported (although they can be recreated with some cleverness in the SQL Query):
• Filter Noun: A noun that is the same as another noun, but with a filter applied. This is useful to split i.e. an order event type into sub-objects such as purchases, cancellations, and refunds.
• Union Nouns: A noun with multiple backing tables.
• Dimensional Noun: A noun that exists only as a dimension on one or more objects.

Once you have a noun, there’s two important things you have to select now:

• Name: this is the logical name of the Noun. This will be visible throughout TextQL and is crucial for our ontology
• Description: This should be additional context for the language model to better understand the meaning of this Noun within your ontology.
• Primary Key: This should be the same primary key used in the data model for this table. This will be used for joins (though can be overridden, see links)
• Title Attribute: This isn’t currently used much but can be useful for the AI, should be a human readable column that a user would think about instead of the primary key (think user id vs username). If such a column doesn’t exist, just pick the same value as the primary key.

Once this is all filled out, you now have a Noun!

​

Adding “Core Attributes” to nouns

In the edit noun page, clicking column names turns them green; you do not need to do this to make the column visible to the query generation AI. This only determines the attributes that are shown by default in object selection (others can still be pulled in either through AI or manually.)

Create Noun Button

Edit Noun Button

Start by clicking on the noun in the graph.

Preview

You can select the preview button to get a table previewing the Backing Table or SQL Query

​

Derived Attributes

These are additional “core” columns you can add to a Noun that are calculated across a link. For example:

• The name of the customer who purchased the order, calculated from the customer object and order -> customer link.
• The total number of reports a manager has, calculated as the count of manager -> employee links for a given manager.

Underneath, these are calculated using correlated subqueries.

Note that if the join path may lead to a row multiplication (i.e. a many-to-many, or crossing from the -one side of a many-to-one link), selecting an aggregation is advised. Otherwise, the results will be arbitrary single values (calculated by LIMIT 1.)

​

Formulaic Attributes

These attributes are used to define formulas or calculations based on the values of other attributes within the ontology. For example, you can create a formulaic attribute that calculates the revenue contribution of a sale by multiplying price and quantity.

Formulaic attributes are defined in the SQL dialect of the underlying connector, and can only reference attributes inherent to the backing table or query of the object — they cannot refer to derived or other formulaic attributes. Column references are specified with the primaryTable identifier, not the backing SQL table or query. For example, to define the above mentioned revenue metric:

primaryTable.price * primaryTable.quantity

​

Metrics

Metrics are quantitative measurements or statistics that provide insights into the performance or behavior of your data. These can include counts, sums, averages, percentages, and more. Examples of metrics could be the total number of sales, the average revenue per customer, or the conversion rate of a marketing campaign.

To define a metric, you need to select the relevant noun and attribute that the metric is based on, along with an aggregation. Then, select the list of dimensions that it is valid to break the metric down by.

Metrics are useful for tracking key performance indicators (KPIs) and monitoring the impact of certain events or actions within your ontology