Model

Understanding Models in RestBI

A Model is a crucial part of how you define, organize, and manage your business data. It encapsulates everything from your database connections, tables, joins, formulas, and filters into a single, cohesive unit. Models allow you to define the relationships between various data elements, making it easier to generate and execute complex queries on your data.

Models act as the blueprint of your data, defining how the different pieces of information in your database relate to each other. They are essential for organizing your data into meaningful structures that can be used to generate insights and reports.

Key Components of the Model

• Connection: Defines how RestBI connects to your database. This includes details like the database type, host, port, and credentials.

• Tables: Tables represent both physical tables and views within your database. Each table is defined with its columns. See column reference for more details

• Joins: Joins define the relationships between tables, used to generate queries across multiple tables.

• Formulas: Formulas allow you to create calculated fields based on existing columns or other formulas. They can be used to perform aggregations, calculations, and transformations on your data.

• Filters: Filters are conditions applied to your data to narrow down the results returned by your queries.

Example

import { Connection, DatabaseType, Table, ColumnDataType, Join, Model, Formula } from "./types";

// Define the PostgreSQL connection
const PostgresConnection: Connection = {
    id: '1',
    name: 'Postgres',
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'test',
    database: 'AdventureWorks',
    type: DatabaseType.POSTGRES,
};

// Define the SalesOrderHeader table with its columns
const salesOrderHeaderTable: Table = {
    id: '1',
    dbName: 'SalesOrderHeader',         // The actual table name in the database
    name: 'Sales Order Header',          // The display name used within the model
    schema: 'sales',                     // The schema name in the database
    columns: [
        { id: '1', dbName: 'SalesOrderID', name: 'Sales Order ID', dataType: ColumnDataType.NUMBER },
        { id: '2', dbName: 'OrderDate', name: 'Order Date', dataType: ColumnDataType.DATE },
        { id: '3', dbName: 'CustomerID', name: 'Customer ID', dataType: ColumnDataType.NUMBER },
        { id: '4', dbName: 'TotalDue', name: 'Total Due', dataType: ColumnDataType.NUMBER }
    ]
};

// Define other tables similarly...

// Define Joins between the tables
const updatedJoins: Join[] = [
    {
        id: '1',
        table1: 'SalesOrderHeader',
        table2: 'Customer',
        clauses: [{ column1: 'CustomerID', column2: 'CustomerID', operator: '=' }],
        joinType: 'INNER JOIN'
    },
    // Other joins...
];

// Define Formulas
const formulas: Formula[] = [
    {
        id: "1",
        name: 'Formula Bikes',
        expression: "SUM(CASE WHEN {{Category Name}} = 'Bikes' THEN {{Total Due}} ELSE NULL END)"
    },
    {
        id: "2",
        name: 'Average Sales',
        expression: "AVG({{Total Due}})"
    },
    {
        id: "3",
        name: 'Year',
        expression: "YEAR({{Order Date}})"
    },
    // More formulas if needed...
];

// Define the Model
export const AdventureWorksModel: Model = {
    id: '1',
    name: 'AdventureWorksModel',  // The internal name for the model
    displayName: 'Adventure Works Model',  // The display name for the model
    connection: PostgresConnection,  // The database connection for this model
    tables: [
        salesOrderHeaderTable,
        // ... Add other table references here
    ],
    joins: updatedJoins,  // The relationships between the tables
    formulas: formulas,  // The calculated fields within the model
    filters: []  // Any predefined filters can be added here
};

Model Reference

Property	Description
id	A unique identifier for the model.
name	The internal name of the model used in the application.
displayName	The user-friendly name for the model, often displayed in UIs.
connection	A Connection object that defines how the model connects to a specific database.
tables	An array of Table objects representing the tables or views in the database that are part of this model.
joins	An array of Join objects that define relationships between the tables in the model.
formulas	An array of Formula objects that define calculated fields or expressions within the model.
filters	An array of Filter objects that define reusable filters for queries.

Where Can Models Be Stored?

RestBI is designed to be highly flexible, allowing you to store your models wherever you prefer. Typically, models are stored as code in your version-controlled repository (e.g., GitHub, GitLab). This approach allows you to manage your models just like any other part of your codebase, enabling collaboration, version tracking, and rollbacks.

By storing models as code, you can:

• Easily integrate models into your CI/CD pipeline.

• Track changes over time and roll back to previous versions if needed.

• Collaborate with team members using version control systems.

• Keep your models and data logic consistent across different environments.

Further Reading

• Tables: Learn more about defining tables and their columns.

• Joins: Understand how to define and use joins in your models.

• Formulas: Dive deeper into creating and using formulas for advanced calculations.