Add Tables & Fields

Define Semantic Layer

Select and define the tables and fields users can query.

Lumi can only formulate queries that pull from selected tables and fields.

Step-by-Step Guide

1. Navigate to the ‘Tables’ tab

2. Ensure you are in edit mode

3. Select the tables you wish to expose to Lumi from the dropdown list

4. Once a table is selected, provide a table name and add context describing what the table is about. Most databases tend to use an unusual table naming convention, we highly recommend you re-name the table into a more human-readable format. This will drastically improve Lumi’s ability to generate accurate code. For more details, refer to the best practices below.

5. For each selected table, use the drop down to select the fields you wish to expose to Lumi

6. Once a field is selected, provide a semantic name and add context describing what the field is about. Most databases tend to use an unusual field naming convention, we highly recommend you re-name the field into a more human-readable format. This will drastically improve Lumi’s ability to generate accurate code. For more details, refer to the best practices below.

Table Descriptions Best Practices

• Use intuitive names. Names should clearly reflect the table's content and purpose. Use descriptive names that convey the table's function or the type of data it holds, making it intuitive for users to understand what data they will find in each table.

• Be consistent. Consistency in naming conventions is key. Decide on specific naming conventions early in the design process and apply them uniformly across all tables. We recommend following snake_case naming convention.

• Plural vs. singular names. Decide whether to use singular or plural names for your tables and remain consistent. There's debate over which is best, but the most important thing is to choose one approach and stick with it throughout your schema.

• Use domain-specific terms. Leverage domain-specific terminology when naming tables, especially in the semantic layer. This ensures that the names are relevant and recognizable to the end-users, who are often domain experts but not necessarily tech-savvy.

• Avoid abbreviations and acronyms. Unless an abbreviation or acronym is well-known and universally understood by all potential users, it's best to avoid them. They can create confusion and reduce the readability of your schema.

• Avoid Using Reserved Keywords. Stay away from reserved SQL keywords (e.g., user) and database-specific terms for table names.

• Prioritize clarity over brevity. While it's important to avoid unnecessarily long names, clarity should never be sacrificed for the sake of brevity. The name should be as long as necessary to ensure it is understandable.

Field Descriptions Best Practices

• Use intuitive names. Field names should clearly indicate what data they contain. Use complete words that accurately describe the data in each field, ensuring that the name is understandable on its own without needing additional context.

Table	Field Name in Source	Semantic Name
Item Master	id	item_id
Item Master	desc	item_description
Sales Header	id	sales_header_id
Sales Header	sell_to_customer	customer_id
Purchase Header	card_code	vendor_id
Purchase Header	BPLId	branch_id

• Prioritize Clarity Over Brevity. Field names should be as long as necessary to ensure they are clearly understood. Avoid sacrificing clarity for the sake of keeping names short.

• Consider End-User Perspective. Always consider the perspective of the end-user or non-technical stakeholders when naming fields. Use terminology and naming conventions that will be most intuitive to them, even if it means deviating slightly from technical norms or preferences.

• Follow a Consistent Naming Convention. Use consistent names for fields found across tables. This will help Lumi better infer implicit join conditions.

Good Semantic Layer	Poor Semantic Layer
item_id is consistently defined across all relevant tables: item master, sales line, purchase line, and inventory tables.	item_id is used in item master, item_no is used sales line, product_id is used in purchase line, sku_id is used inventory table.

• Prefer Verbose Names Over Abbreviations. While it might be tempting to use abbreviations to keep field names short, they can obscure meaning and make the schema less accessible. Always prefer verbose, descriptive names over abbreviations, unless the abbreviation is widely understood and accepted.

• Include Units of Measurement Where Applicable. If a field represents a measurable quantity, include the unit of measurement in the field name. For example, temperature_celsius or length_cm make it clear what unit the data is measured in, reducing confusion and the need for additional documentation.

• Avoid Using Reserved Keywords. Like with table names, avoid using reserved SQL keywords and database-specific terms for field names to prevent conflicts and errors.

• Attribute Value Clarification. For fields with a predefined set of values, explicitly define each possible option.

Semantic Name	Good Context	Poor Context
order_status	Integer field that represents the order status; 1 = Pending, 2 = Active, 3 = Declined, 4 = Closed, 5 = Returned.	Represents the order status
inventory_adjustment_type	Integer field that represents the type of inventory adjustment transaction; 1 = Sales, 2 = Purchase, 3 = Positive adjustment., 4 = Negative Adjustment, 5 = Transfer, 6 = Consumption	Represents the inventory adjustment type
invoice_status	represents "O" for opened and "C" for closed	Represents the invoice status
sales_order_canceled_flag	represents "N" for not canceled and "Y" (and others) for canceled	Flag to represent if order is canceled or not.