Add Tables & Fields
Last updated
Last updated
Select and define the tables and fields users can query.
Lumi can only formulate queries that pull from selected tables and fields.
Navigate to the ‘Tables’ tab
Ensure you are in edit mode
Select the tables you wish to expose to Lumi from the dropdown list
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.
For each selected table, use the drop down to select the fields you wish to expose to Lumi
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.
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.
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.
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.
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.
Table | Field Name in Source | Semantic Name |
---|---|---|
Good Semantic Layer | Poor Semantic Layer |
---|---|
Semantic Name | Good Context | Poor Context |
---|---|---|
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
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.
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.