Detecting Sales Anomalies with ML.NET — Razor Dashboard + SQL Integration

🔍 Introduction

In this post, I’ll Walk through how I built an intelligent anomaly detection dashboard using ML.NET, SQL Server, and Razor Pages. The goal: flag unusual sales patterns across stores and organizations using DetectIidSpike

🧪 Problem Statement
Sales data can fluctuate — but how do we know when it’s abnormal?
Using ML.NET’s DetectIidSpike, we can identify sudden spikes in sales values and surface them in a dashboard with filters, counts

🧱 Tech Stack
• ML.NET TimeSeries: DetectIidSpike for anomaly detection
• SQL Server View: vw_SalesAnomaly as the data source
• Razor Pages: UI with dropdown filters and summary counts
• LINQ: Dynamic filtering by Store and Or

🧠 ML.NET Pipeline

var pipeline = mlContext.Transforms.DetectIidSpike(
                    outputColumnName: nameof(SalesOutput.Prediction),
                    inputColumnName: nameof(SalesInput.Sales),
                    confidence: 95,
                    pvalueHistoryLength: 30)
                     .Append(mlContext.Transforms.CopyColumns("OrgName", "OrgName"))
                    .Append(mlContext.Transforms.CopyColumns("StoreName", "StoreName"));

var results = mlContext.Data.CreateEnumerable<SalesOutput>(transformed, reuseRowObject: false)
                        .Select((p, index) => new AnomalyPoint
                        {
                            Index = index,
                            Score = p.Prediction[1],
                            PValue = p.Prediction[2],
                            IsAnomaly = p.Prediction[0] == 1,
                            OrgName = p.OrgName,
                            StoreName = p.StoreName
                        }).ToList();
                return results;

This detects spikes in the Sales column and retains metadata for filtering

🗂️ Data Source

SELECT 
CAST(Sales AS REAL) AS Sales,
CAST(Multiplier AS REAL)AS Multiplier,
Severity,
OrgName,
StoreName 
FROM vw_SalesAnomaly

The view aggregates sales metrics across stores and organizations.

📊 Razor Dashboard Logic

if (!string.IsNullOrEmpty(SelectedStore))
            {
                results = results.Where(x => x.StoreName == SelectedStore).ToList();
            }
            if (!string.IsNullOrEmpty(SelectedOrg))
            {
                results = results.Where(x => x.OrgName == SelectedOrg).ToList();
            }
            AnomalyCount = results.Count(x=>x.IsAnomaly);
            OrgCount = results.Where(r => r.IsAnomaly).Select(r => r.OrgName).Distinct().ToList().Count();
            StoreCount = results.Where(r => r.IsAnomaly).Select(r => r.StoreName).Distinct().ToList().Count();
            StoreList = results.Select(x => x.StoreName).ToList();
            OrgList = results.Select(x => x.OrgName).ToList();
            TotalRecords = results.Distinct().ToList().Count();

🎨 Razor UI Highlights

🔍 Alert Summary Pane

<div class="alert alert-strong-danger d-flex align-items-center justify-content-between mb-3">
  <div class="alert-box">
    <span class="label">No.of Anomaly</span>
    <div class="icon-line">
      <i class="bi bi-eye-fill"></i>
      <span>@Model.AnomalyCount</span>
    </div>
  </div>
</div>

• Custom alert styling with .alert-strong-danger
• Bootstrap Icons for visual cues
• Dynamic counts for anomalies, affected orgs, and store

🧭 Interactive Filters

<form class="form-group">
    <label for="SelectedOrg">Org Name</label>
    <select id="SelectedOrg" name="SelectedOrg" class="form-control" onchange="this.form.submit()">
        <option value="">-- Select Organization --</option>
        @foreach (var org in @Model.OrgList)
        {
            <option value="@org" selected="@(org == @Model.SelectedOrg ? "selected" : null)">
                @org
            </option>
        }
    </select>
</form>

• Organization and store filters auto-submit on change
• Razor logic ensures selected values persist

📊 Chart.js Visualizatio

const data = {
    labels: @Html.Raw(JsonConvert.SerializeObject(Model.results.Select(a => a.Index))),
    datasets: [
        {
            label: 'Sales Score',
            data: @Html.Raw(JsonConvert.SerializeObject(Model.results.Select(a => a.Score))),
            borderColor: 'blue',
            fill: false
        },
        {
            label: 'Anomalies',
            data: @Html.Raw(JsonConvert.SerializeObject(Model.results.Select(a => a.IsAnomaly ? a.Score : (double?)null))),
            borderColor: 'red',
            pointBackgroundColor: 'red',
            pointRadius: 5,
            showLine: false
        }
    ]
};

• Blue line for normal scores
• Red dots for anomalies
• Responsive chart with title plugin

📋 Tabular Insights

<table class="table">
    <thead>
        <tr><th>OrgName</th><th>StoreName</th><th>Score</th><th>P-Value</th><th>Status</th></tr>
    </thead>
    <tbody>
        @foreach (var a in Model.results)
        {
            <tr>
                <td>@a.OrgName</td>
                <td>@a.StoreName</td>
                <td>@a.Score</td>
                <td>@a.PValue</td>
                <td>
                    <span class="@(a.IsAnomaly ? "text-danger" : "text-primary")">
                        @(a.IsAnomaly ? "Anomaly" : "Normal")
                    </span>
                </td>
            </tr>
        }
    </tbody>
</table>

• Conditional styling for anomaly status
• Clean layout using Bootstrap’s table classes

This powers the dropdown filters and summary counts on the dashboard.

📈 Output
• ✅ Total anomalies detected
• 🏢 Unique orgs and stores affected
• 📋 Filterable results by Store and Org

Detail Code:

📁 Visit GitHub

🚀 Conclusion
ML.NET makes it easy to build intelligent dashboards that detect and visualize anomalies in real time. This project combines SQL, ML.NET, and Razor Pages