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.
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
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
Last updated