How to Design a SQL Semantic Model Using Kyubit’s Visual Diagram Designer

Building a robust semantic layer bridges the gap between raw SQL data and meaningful business intelligence. Follow this comprehensive, step-by-step guide to design, configure, and process your SQL semantic model using Kyubit’s intuitive, drag-and-drop Visual Diagram Designer.

Connect to Your SQL Database

The first step in building your semantic layer is establishing a secure connection to your underlying data warehouse. Navigate to the Kyubit platform and create a new Data Source pointing to your existing SQL database. Kyubit features native connectivity to a wide range of relational database management systems, including Microsoft SQL Server, PostgreSQL, Oracle, MySQL, MariaDB, and SQLite.

Create a Kyubit Analytic Model

A Kyubit Analytic Model acts as the crucial semantic layer between complex database structures and your business users' actual data analytics capabilities. To initialize the model, select the Diagram-based Analytic Model option, provide a descriptive, business-friendly name, and link it directly to your previously created SQL data source before saving.

Navigate the Model Designer Interface

Once initialized, the Model Designer tab opens a highly interactive, visual workspace optimized into three primary functional zones: a left panel listing database tables with integrated search, a central canvas for visual diagram design, and a right panel showing the final Model Structure of your measures and dimensions.

Define the Central Fact Table

A Fact Table is the cornerstone of any data warehouse or business intelligence model that stores quantitative, measurable data. Simply drag-and-drop your primary transaction table to the central canvas, click on its context menu, and select 'Set as Fact Table' to activate mapping checkboxes for metrics and attributes.

Preview Table Sample Data

To ensure data quality and verify that you are working with the correct entities, click on any added table menu and select 'Show Table Sample Data'. A live sample of the first 200 rows will instantly display, allowing you to audit actual data values, column profiles, and formats directly within the visual diagram designer.

Configure Analytical Measures

Measures represent numerical values of vital analytical importance in your business model. Activate the Measures area (light blue background), then check numeric columns in your fact table. Once added to the model structure, click the new measure to define its custom business caption and specialized format strings.

Add Dimension Tables

Enrich your data analysis by dragging and dropping descriptive dimension tables that provide analytical context to your fact metrics. All tables within the interactive diagram layout can be dynamically resized with handles or repositioned smoothly by executing a simple mouse-press and dragging them around by the header.

Create Visual Table Relationships

To establish SQL joins effortlessly, drag a Primary Key column from your dimension table and drop it directly onto the related foreign key column in the fact table. A visual connection arrow will instantly link the tables, color-coding the entity and activating checkboxes to map its descriptive attributes.

Create Dimension Entities

Before selecting specific table columns as attributes, you must build the structural containers. Click on the Model Structure context menu in the right panel and select 'New Dimension'. Provide an intuitive, user-friendly name for the entity, and click OK to make it instantly visible within your emerging semantic model architecture.

Map Dimension Attributes

Select your target dimension container in the structure tree (marked with a light blue background) and check the desired columns in the database table diagram. Fields automatically map underneath the dimension. Click on any added dimension attribute to set a clean business caption utilized to break down measure values during BI analysis.

Employ Smart Time Dimensions

Accelerate your modeling workflow with Kyubit's automated time intelligence. Checking a Date or DateTime column prompts the designer to automatically generate a comprehensive, pre-built time dimension. This populates multi-level time hierarchies (Years > Quarters > Months > Days), quarter names, month names, and weekdays with a single click.

Build Attribute Hierarchies

To support advanced parent-child drill-down analytics (such as Country > State > City), navigate to the Model Designer menu and select 'New Hierarchy'. Once the structure is initialized inside your dimension, simply select your attributes and allocate them to the hierarchy levels to define the drill-down sequence.

Optimize with Dimension Details

High-cardinality attributes like email addresses or phone numbers degrade performance if mapped as standard attributes. Instead, configure them as Dimension Details. This maintains a lightweight, high-performance semantic model during core operations while preserving the data for granular drillthrough actions.

Canvas Navigation: Zoom and Pan

Managing large, comprehensive diagrams containing dozens of data warehouse entities is completely effortless. Scale the visual interface seamlessly by using your mouse scroll wheel to zoom in and out at any time, or click and hold down a mouse click on empty space to pan smoothly across the canvas.

Apply Advanced Table Filtering

Exclude unnecessary historical data or scope down rows without modifying underlying database views. Select 'Table Filtering' from the table context menu and write custom SQL conditions. Always enclose column names in square brackets—for example, writing [OrderDate] > '2026-01-01' restricts input data strictly to current-year values.

Configure Dimension Attribute Ranges

When dealing with high-variance numeric attributes (such as Customer Age, Order Totals, or Tenure Days), generating a distinct dimension member for every single unique value leads to cluttered, unusable reports. Kyubit's semantic model addresses this by allowing you to establish custom value segments or intervals. Transforming raw numeric data into structured dimension ranges groups your data into meaningful, high-level brackets that provide significantly more useful analysis and elegant reporting for business end-users.

Process and Publish Your Model

Technically, once you configure at least one measure and one dimension attribute, your system is viable. Trigger the Process Model action to index relationships and compile visual logic. Once processing finishes, your model goes fully live, ready for data analysis, reporting, and dashboard creation.