We value your privacy

This site uses cookies to improve your browsing experience, analyze site traffic, and show personalized content. See our Privacy Policy.

  1. Guides
  3. Applications
  5. Performant Dashboards
  7. From Existing OLTP DB

From Existing OLTP DB

This guide walks you through building performant dashboards by connecting to your existing OLTP (Online Transaction Processing) database and creating optimized materialized views for analytics.

Overview

When you have an existing OLTP database (like PostgreSQL, MySQL, or SQL Server), you can leverage MooseStack to create high-performance dashboards without disrupting your production database. This approach involves:

  1. Connecting to your existing database
  2. Creating materialized views that aggregate and pre-compute data
  3. Querying the materialized views for fast dashboard responses

Benefits

  • No disruption to your production OLTP database
  • Fast queries through pre-aggregated materialized views
  • Real-time updates as data changes in your source database
  • Scalable architecture that separates transactional and analytical workloads

Prerequisites

Before starting, ensure you have:

  • Access to your existing OLTP database
  • Database connection credentials
  • A MooseStack project initialized

Implementation Steps

Follow the steps below to implement performant dashboards from your existing OLTP database. Each step builds on the previous one, guiding you through the complete setup process.

1. Setup Connection

Step 1: Setup Connection

In this step, you'll configure MooseStack to connect to your existing OLTP database.

Overview

MooseStack needs to connect to your existing database to read data and create materialized views. This connection is configured securely and doesn't require any changes to your production database.

Configuration

moose.config.ts
import { defineConfig } from "@514labs/moose-cli"; export default defineConfig({  dataSources: {    postgres: {      type: "postgres",      host: process.env.DB_HOST || "localhost",      port: parseInt(process.env.DB_PORT || "5432"),      database: process.env.DB_NAME || "mydb",      user: process.env.DB_USER || "postgres",      password: process.env.DB_PASSWORD || "",    },  },});

Environment Variables

For security, store sensitive credentials in environment variables:

DB_HOST=your-db-host
DB_PORT=5432
DB_NAME=your-database
DB_USER=your-username
DB_PASSWORD=your-password

Verify Connection

After configuring the connection, verify it works:

moose db ping

Next Steps

Once your connection is configured, proceed to the next step to create materialized views.

2. Create Materialized View (Initiative)

Step 2: Create Materialized View (Initiative Scope)

In this step, you'll create materialized views that pre-aggregate data from your OLTP database for fast dashboard queries, specifically tailored for initiative-level reporting.

Overview

Materialized views store pre-computed query results, allowing dashboards to load instantly without querying your production OLTP database directly. This step shows you how to define and create these views.

Define Materialized View

dashboard-views.ts
import { OlapMaterializedView, OlapTable } from "@514labs/moose-lib"; // Define the source table (from your OLTP DB)interface OrdersTable {  id: string;  customer_id: string;  amount: number;  created_at: Date;} // Define the materialized viewinterface InitiativeSalesView {  date: Date;  initiative_id: string;  total_sales: number;  order_count: number;} export const initiativeSalesView = new OlapMaterializedView<InitiativeSalesView>(  "initiative_sales",  {    source: "orders", // References your OLTP table    query: `      SELECT        toDate(created_at) as date,        initiative_id,        sum(amount) as total_sales,        count(*) as order_count      FROM orders      GROUP BY date, initiative_id    `,    refresh: "incremental", // Update as new data arrives  });

Apply the View

Create the materialized view in your database:

moose db migrate

Query the View

Once created, you can query the materialized view directly:

const results = await initiativeSalesView.select({  date: { $gte: new Date("2024-01-01") },});

Next Steps

Your materialized view is now ready! You can use it in your dashboard queries for fast, pre-aggregated data.

2. Create Materialized View

Step 2: Create Materialized View

In this step, you'll create materialized views that pre-aggregate data from your OLTP database for fast dashboard queries.

Overview

Materialized views store pre-computed query results, allowing dashboards to load instantly without querying your production OLTP database directly. This step shows you how to define and create these views.

Define Materialized View

dashboard-views.ts
import { OlapMaterializedView, OlapTable } from "@514labs/moose-lib"; // Define the source table (from your OLTP DB)interface OrdersTable {  id: string;  customer_id: string;  amount: number;  created_at: Date;} // Define the materialized viewinterface DailySalesView {  date: Date;  total_sales: number;  order_count: number;} export const dailySalesView = new OlapMaterializedView<DailySalesView>(  "daily_sales",  {    source: "orders", // References your OLTP table    query: `      SELECT        toDate(created_at) as date,        sum(amount) as total_sales,        count(*) as order_count      FROM orders      GROUP BY date    `,    refresh: "incremental", // Update as new data arrives  });

Apply the View

Create the materialized view in your database:

moose db migrate

Query the View

Once created, you can query the materialized view directly:

const results = await dailySalesView.select({  date: { $gte: new Date("2024-01-01") },});

Next Steps

Your materialized view is now ready! You can use it in your dashboard queries for fast, pre-aggregated data.

On this page

OverviewBenefitsPrerequisitesImplementation Steps1. Setup Connection2. Create Materialized View (Initiative)
Edit this page
FiveonefourFiveonefour
Fiveonefour Docs
MooseStackHostingTemplatesGuides
Release Notes
Source552
  • Improving the Performance of Your Dashboards
  • Chat in your app
moose.config.ts
import { defineConfig } from "@514labs/moose-cli"; export default defineConfig({  dataSources: {    postgres: {      type: "postgres",      host: process.env.DB_HOST || "localhost",      port: parseInt(process.env.DB_PORT || "5432"),      database: process.env.DB_NAME || "mydb",      user: process.env.DB_USER || "postgres",      password: process.env.DB_PASSWORD || "",    },  },});
dashboard-views.ts
import { OlapMaterializedView, OlapTable } from "@514labs/moose-lib"; // Define the source table (from your OLTP DB)interface OrdersTable {  id: string;  customer_id: string;  amount: number;  created_at: Date;} // Define the materialized viewinterface InitiativeSalesView {  date: Date;  initiative_id: string;  total_sales: number;  order_count: number;} export const initiativeSalesView = new OlapMaterializedView<InitiativeSalesView>(  "initiative_sales",  {    source: "orders", // References your OLTP table    query: `      SELECT        toDate(created_at) as date,        initiative_id,        sum(amount) as total_sales,        count(*) as order_count      FROM orders      GROUP BY date, initiative_id    `,    refresh: "incremental", // Update as new data arrives  });
dashboard-views.ts
import { OlapMaterializedView, OlapTable } from "@514labs/moose-lib"; // Define the source table (from your OLTP DB)interface OrdersTable {  id: string;  customer_id: string;  amount: number;  created_at: Date;} // Define the materialized viewinterface DailySalesView {  date: Date;  total_sales: number;  order_count: number;} export const dailySalesView = new OlapMaterializedView<DailySalesView>(  "daily_sales",  {    source: "orders", // References your OLTP table    query: `      SELECT        toDate(created_at) as date,        sum(amount) as total_sales,        count(*) as order_count      FROM orders      GROUP BY date    `,    refresh: "incremental", // Update as new data arrives  });