If you’ve ever tried to build interactive analytics or dashboards directly on top of your transactional database, you know how painful it gets—slow queries, complex joins, duplicated logic across charts, and messy backend code.

That’s exactly where Cube.js comes in.

In this post, we’ll explore how you can use Cube.js to add modern analytics and AI-powered insights to your applications, even if your core system is built on .NET APIs with MSSQL (or any relational backend).

What Is Cube.js?

Cube.js is an open-source semantic layer that sits between your database and your dashboard UI. It helps you:

• Define metrics once and reuse them everywhere.
• Query data securely with caching and pre-aggregations.
• Serve analytics through a REST, GraphQL, or SQL API.
• Power dashboards, charts, and even LLMs that can query data through natural language.

It’s the backbone for creating a data API—your analytics logic lives in one place, not scattered across services or SQL files.

Why Use Cube.js for Your Application Analytics

Let’s take a real example: a Member management system built on .NET + MSSQL.

You may want dashboards like:

• Active members this month
• Monthly signups and churn
• Revenue per branch
• Average visits per member
• Trainer performance and class occupancy

Instead of hand-coding each metric in .NET or embedding one-off SQL everywhere, Cube lets you define these metrics declaratively in one schema and then query them instantly from your frontend.

Key Advantages

Architecture: Where Cube.js Fits

Here’s a high-level view of how Cube.js fits into your stack.

flowchart LR
    subgraph Backend["Application & Data Layer"]
        A[.NET API] --> B[MSSQL Database]
    end

    subgraph Cube["Cube.js Semantic Layer"]
        B --> C[Cube.js Schemas, Caching,, Pre-aggregations]
    end

    subgraph Clients["Consumers"]
        D[Web Dashboard React / Next.js]
        E[Mobile App]
        F[AI Assistant / LLM]
    end

    C --> D
    C --> E
    C --> F

In this model:

• Your existing .NET API and MSSQL remain the system of record.
• Cube.js connects directly to MSSQL and exposes a clean analytics API.
• Dashboards, reports, and AI agents consume analytics through Cube, not directly from MSSQL.

Setting Up Cube.js with MSSQL

1. Install Cube CLI

npm install -g cubejs-cli
cubejs create member-analytics -d mssql
cd member-analytics

2. Configure Database Connection

Create a .env file:

CUBEJS_DB_TYPE=mssql
CUBEJS_DB_HOST=your-sql-host
CUBEJS_DB_NAME=memberdb
CUBEJS_DB_USER=youruser
CUBEJS_DB_PASS=yourpass

CUBEJS_API_SECRET=your_secret_key
CUBEJS_DEV_MODE=true

3. Define Your First Cube

Example cube for memberships:

// schema/Memberships.js
cube(`Memberships`, {
  sql: `SELECT id, member_id, start_date, end_date, price, tenant_id
        FROM dbo.Memberships`,

  measures: {
    count: {
      type: 'count',
      drillMembers: ['id', 'startDate']
    },

    totalRevenue: {
      sql: 'price',
      type: 'sum',
      format: 'currency'
    },

    activeMembers: {
      sql: `member_id`,
      type: 'countDistinct',
      filters: [
        { sql: `${CUBE}.start_date <= NOW()` },
        { sql: `${CUBE}.end_date IS NULL OR ${CUBE}.end_date >= NOW()` }
      ]
    }
  },

  dimensions: {
    id: {
      sql: 'id',
      type: 'number',
      primaryKey: true
    },

    memberId: {
      sql: 'member_id',
      type: 'number'
    },

    tenantId: {
      sql: 'tenant_id',
      type: 'string'
    },

    startDate: {
      sql: 'start_date',
      type: 'time'
    },

    endDate: {
      sql: 'end_date',
      type: 'time'
    }
  },

  // Optional: basic multi-tenant row-level security
  // SECURITY_CONTEXT.tenantId comes from JWT
  // Example JWT: { "tenantId": "org_123" }
  // In production, prefer 'sql' in 'joins' or 'validate' for advanced RLS.
  // Uncomment if you want strict filtering:
  //  segments: {
  //    tenantScope: {
  //      sql: `${CUBE}.tenant_id = ${SECURITY_CONTEXT.tenantId}`
  //    }
  //  },

  preAggregations: {
    dailyMetrics: {
      type: 'rollup',
      measureReferences: [count, totalRevenue, activeMembers],
      timeDimension: startDate,
      granularity: 'day',
      partitionGranularity: 'month',
      refreshKey: {
        every: '6 hour'
      }
    }
  }
});

4. Run Cube

npm run dev

Visit http://localhost:4000 to open the Cube.js Developer Playground and auto-generate queries/charts.

Embedding Dashboards or APIs

You can query Cube from any client using REST, GraphQL, or the Cube.js JS client.

REST Example

curl -X POST https://cube.yourdomain.com/cubejs-api/v1/load \
  -H "Authorization: Bearer <jwt>" \
  -H "Content-Type: application/json" \
  -d '{
    "measures": ["Memberships.totalRevenue"],
    "timeDimensions": [
      {
        "dimension": "Memberships.startDate",
        "granularity": "month",
        "dateRange": "Last 12 months"
      }
    ]
  }'

React / Next.js Example

import cubejs from '@cubejs-client/core';

const cubejsApi = cubejs('<JWT>', {
  apiUrl: 'https://cube.yourdomain.com/cubejs-api/v1'
});

async function loadActiveMembersByMonth() {
  const resultSet = await cubejsApi.load({
    measures: ['Memberships.activeMembers'],
    timeDimensions: [
      {
        dimension: 'Memberships.startDate',
        granularity: 'month',
        dateRange: 'Last 12 months'
      }
    ]
  });

  return resultSet.chartPivot();
}

Once you have the chartPivot() result, you can easily feed it into Recharts, ECharts, Chart.js, or any charting library.

Making It AI-Ready: Cube.js + LLMs

The real magic happens when you combine Cube’s semantic layer with an LLM-based assistant.

Instead of directly generating SQL, your AI agent can generate Cube queries against a known schema, which is safer and easier to govern.

sequenceDiagram
    participant User as Business User
    participant Chat as AI Chatbot / Copilot
    participant LLM as LLM Engine
    participant Cube as Cube.js
    participant DB as MSSQL

    User->>Chat: "Show last month's revenue by branch"
    Chat->>LLM: User query + Schema context
    LLM-->>Chat: Cube query JSON
    Chat->>Cube: /load (Cube query)
    Cube->>DB: Optimized SQL
    DB-->>Cube: Aggregated data
    Cube-->>Chat: Result set
    Chat-->>User: Chart + explanation

Why This Pattern Is Powerful

• The LLM never touches raw tables directly—it works through Cube’s curated schema.
• You can control which measures/dimensions are exposed to AI.
• You keep performance, security, and governance in one place.

Deployment Options

You have two main approaches:

1. Cube Cloud (Managed)

• Zero ops for the Cube infrastructure.
• Built-in scaling, SSL, and pre-aggregation storage.
• Connects to your MSSQL (on-prem or cloud, with secure tunnels/VPNs).

2. Self-Hosted Cube.js

• Run Cube.js via Docker, Kubernetes, or a VM.
• Add Redis or Cube Store for caching and pre-aggregations.
• Frontend (e.g. Next.js on Vercel) calls your Cube instance via HTTPS.

Either way, your .NET backend and MSSQL remain unchanged; you are simply adding a dedicated analytics layer.

Best Practices for Production

1. Pre-aggregate daily and monthly metrics
   Use Cube’s pre-aggregations for heavy metrics like revenue, visits, and churn.
2. Use caching
   Enable Redis or Cube Store so repeated dashboard queries are served from cache.
3. Secure JWTs and RLS
   • Encode tenantId, userId, and role in JWTs.
   • Use SECURITY_CONTEXT in Cube for row-level security.
4. Monitor refresh jobs
   Set up monitoring and alerting for pre-aggregation refresh failures.
5. Version control schemas
   Keep your Cube schema/ directory in Git alongside the application code.

Example Use Cases

• SaaS product analytics (user engagement, cohorts, retention)
• Retail / POS analytics (sales, inventory, promotions)
• IoT / sensor analytics (aggregations over time windows)
• Financial dashboards (KPIs, margin analysis, risk metrics)
• AI Insight generators (natural-language reports for management)

Final Thoughts

Cube.js transforms your analytics layer from ad-hoc SQL and scattered reports into a programmable, secure, and AI-ready platform.

For developers running modern SaaS or enterprise apps—especially those on .NET + SQL Server—Cube brings the agility and scalability of a modern analytics stack without locking you into a monolithic BI tool.

If you want your dashboards to be:

• lightning-fast,
• consistent across web, mobile, and reporting, and
• ready for AI copilots and voice/chat interfaces,

then Cube.js is an excellent foundation for your analytics and AI journey.