Analytics & Report Platform

Unified multi-tenant analytics — from live grid reports to ad-hoc AnalysisQuery, multisource KPI feeds, and performance intelligence

Architecture Layers

Report Modes

Async Decision Flow

MultiSource Data Connectors

Analytics connects to multiple data sources beyond the primary OLTP database. Each source is registered in MREPORTDATASOURCE and selected at report-time via MREPORTDATASOURCERULE based on date range, module, and tenant configuration.

Supported Source Types

DataSource Schema

DataSource Rule Evaluation

// DataSourceRuleEvaluator — selects best datasource for a given report+params
1. Load rules for ReportViewId (or global rules if none specific)
2. Filter: ModuleCode matches or is null
3. Filter: query date range exceeds DateRangeDays threshold (if set)
4. Sort by Priority ASC — first matching rule wins
5. Fallback: MREPORTDATASOURCE WHERE IsDefault=true AND TenantId=@t
6. Resolve connection string from Vault via ServerConfigCache.GetConnectionStringAsync(key)

Workspace Concept

A Workspace is a named, saveable AnalysisQuery configuration — a user's or team's persistent "analytical view" of a dataset. Workspaces capture: the selected DataSource, column configuration, applied filters, sort order, grouping, and optionally a scheduled refresh or export.

Workspace Schema

ETL & DataSync Platform

ETL Pipeline Architecture

GB5's ETL capability is a push-based, configurable pipeline that extracts data from registered sources (GB5 OLTP, external APIs, file uploads), transforms via configurable mapping rules, and loads into target tables. Used for: cross-tenant consolidation, historical migrations, and the DataSync module.

DataSync Module

DataSync lives inside GB5Framework (not a separate microservice) and orchestrates the replication of multi-tenant data across environments. It migrates and replaces the legacy Gb4Import replication service.

DataSync Database Schema (18 tables)

GB4 Import Migration

The legacy Gb4Import WCF service replicated product/customer/financial master data across GB4 tenants via a polling-based XML pipeline. DataSync replaces this with a modern push-based approach:

KPI Management Platform

KPI Management provides the framework for defining, categorizing, and tracking Key Performance Indicators across modules. KPIs are either manually entered, auto-computed from TGOAL/TAPPRAISAL data, or ingested from external sources via ETL.

KPI Schema

MKPI — KPI Master

TKPILIST — KPI List (Computed)

Stores periodically computed KPI values — either from module data (PERM, OKR) via Dapr events, or from SQL queries scheduled through the ETL pipeline.

TKPIMANUAL — Manual KPI Entry

Allows HR / Finance / managers to enter KPI values directly for KPIs that cannot be auto-computed from system data (e.g., NPS score, customer satisfaction from survey, market share from external report).

MROLEKPI — Role-Based KPI Assignments

Maps which KPIs are relevant for which roles. Drives role-specific KPI dashboard tiles in ESS and manager views.

AnalysisQuery Designer

AnalysisQuery is GB5's ad-hoc query builder — a no-code dynamic SQL composer that allows analysts and power users to define custom analytical queries without writing SQL directly. Queries are defined once in MANALYSIS and executed on-demand.

Analysis Schema

AnalysisQuery Execution Flow

// AnalysisQueryBLL.ExecuteAnalysisAsync — simplified safe column composition
var whitelist = await _dal.GetColumnsAsync(analysisId, login, ct);
// Validate: reject any request.Columns not in whitelist
var invalid = req.Columns.Where(c => !whitelist.ContainsKey(c)).ToList();
if (invalid.Any()) throw new ValidationException($"Invalid columns: {string.Join(',', invalid)}");

// Build SELECT using server-side expressions only — never req.Columns directly
var selectClauses = req.Columns.Select(c => $"{whitelist[c].ColumnExpression} AS [{c}]");
var sql = $"SELECT {string.Join(", ", selectClauses)} FROM {analysis.BaseTable} {analysis.JoinsJson}";

// Add WHERE params — all parameterized
if (req.Filters?.Any() == true)
    foreach (var f in req.Filters)
        sql += $" AND {whitelist[f.Column].ColumnExpression} {f.Operator} @{f.Column}";
sql += $" AND TenantId = @TenantId";  // mandatory tenant filter

Performance KPI Feed (PerfKPIFeed)

PerfKPIFeed is the analytics bridge for the Performance Management Platform — it ingests finalized appraisal scores and closed OKR cycles into the KPI analytics framework via Dapr pub/sub events. This keeps performance data current in the KPI dashboard without polling.

TPERFKPIFEED Schema

Dapr Subscribers

PerformanceKPISubscriber — subscribes to perm.score.finalized

// AnalyticsSL/Subscribers/PerformanceKPISubscriber.cs
[ApiController][Route("analytics/subscribe")]
public class PerformanceKPISubscriber : ControllerBase
{
    [HttpPost("perm-score-finalized")]
    [Topic("pubsub", "perm.score.finalized")]
    public async Task<IActionResult> OnPermScoreFinalized(
        [FromBody] PermScoreFinalizedEvent evt, CancellationToken ct)
    {
        // evt: { AppraisalPlanId, PeriodEnd, EmployeeCount, Scores, GradeDistribution, TenantId }
        await _perfKpiBll.IngestPermScoresAsync(evt, ct);
        return Ok();
    }

    [HttpPost("okr-cycle-closed")]
    [Topic("pubsub", "okr.cycle.closed")]
    public async Task<IActionResult> OnOkrCycleClosed(
        [FromBody] OkrCycleClosedEvent evt, CancellationToken ct)
    {
        // evt: { OKRCycleId, PeriodEnd, TotalGoals, CompletedGoals, AverageProgress, TenantId }
        await _perfKpiBll.IngestOkrCycleAsync(evt, ct);
        return Ok();
    }
}

PerfKPIFeedBLL — metric computation on ingest

// IngestPermScoresAsync — writes TPERFKPIFEED rows per metric per OUID
public async Task IngestPermScoresAsync(PermScoreFinalizedEvent evt, CancellationToken ct)
{
    // Aggregate: company-level + per-OU breakdown
    var metrics = new List<PerfKPIFeedDTO>();

    // AVG_FINAL_SCORE — average FinalPercentage across all employees in plan
    metrics.Add(new() { FeedSource=0, SourceId=evt.AppraisalPlanId, Period=evt.PeriodEnd,
        MetricKey="AVG_FINAL_SCORE", MetricValue=evt.Scores.Average(s => s.FinalPercentage) });

    // BELL_CURVE_DIST — store grade distribution as JSON detail
    metrics.Add(new() { FeedSource=0, SourceId=evt.AppraisalPlanId, Period=evt.PeriodEnd,
        MetricKey="BELL_CURVE_DIST",
        MetricValue=evt.GradeDistribution.Count,  // count of distinct grades
        MetricDetailJson=JsonSerializer.Serialize(evt.GradeDistribution) });

    // Per-OU: avg score per org unit
    foreach (var ou in evt.Scores.GroupBy(s => s.OUID))
        metrics.Add(new() { FeedSource=0, SourceId=evt.AppraisalPlanId, Period=evt.PeriodEnd,
            OUID=ou.Key, MetricKey="AVG_FINAL_SCORE", MetricValue=ou.Average(s => s.FinalPercentage) });

    await _dal.BulkInsertPerfKPIFeedAsync(metrics, login, ct);

    // Propagate to TKPILIST for KPI ID mapped to AVG_FINAL_SCORE
    await _kpiListBll.UpdateFromPerfFeedAsync(metrics, ct);
}

Dapr Topic Subscriptions (3 total)

API Endpoints Reference

Core Report Platform

DataSource Management

Workspace

AnalysisQuery

KPI Management

Performance KPI Feed

Sample Scenarios

Scenario A — Standard Report with DataSource Routing

GET /CommonReport/Report?ReportViewId=501&DateFrom=2023-01-01&DateTo=2025-12-31&Format=Excel

// AsyncDecisionEngine checks row estimate: ~250,000 rows > threshold (100k)
// → Queues TSYSJOB (JobId=8821)
// Response: { "IsAsync": true, "JobId": 8821, "Message": "Report is being generated..." }

// User checks My Reports after 2 min:
GET /CommonReport/GetMyReports
// Response includes: { JobId: 8821, Status: 2 (Completed), FileUrl: "/report/8821" }

// DataSource resolution that happened internally:
// 1. Load MREPORTDATASOURCERULE for ReportViewId=501
// 2. Query date range = 1095 days > rule.DateRangeDays = 730
// 3. Selected DataSourceId = 3 (Archive DB)
// 4. Resolved connection string from Vault path "gb5/finance/archive-db"

Scenario B — AnalysisQuery Designer (Sales Dashboard)

// Step 1: Load available columns for Sales Analysis (AnalysisId: 12)
GET /Analysis/GetAnalysisColumns?AnalysisId=12
// Returns whitelist: OrderDate, CustomerName, ProductCode, Revenue, Qty, Region, SalesRep

// Step 2: Execute ad-hoc query — user selected columns + filter
POST /Analysis/Execute
{
  "AnalysisId": 12,
  "Columns": ["OrderDate", "Region", "Revenue", "Qty"],
  "Filters": [
    { "Column": "Region", "Operator": "=", "Value": "North" },
    { "Column": "OrderDate", "Operator": ">=", "Value": "2026-01-01" }
  ],
  "GroupBy": ["Region"],
  "AggregateColumns": { "Revenue": "SUM", "Qty": "SUM" },
  "SortColumn": "Revenue", "SortDirection": "DESC"
}
// Backend builds: SELECT Region, SUM([o].[Revenue]) AS Revenue, SUM([o].[Qty]) AS Qty
//   FROM [TORDER] o ... WHERE Region = @Region AND OrderDate >= @OrderDate AND TenantId = @TenantId
// Column expressions come from whitelist — user-supplied column names used only as keys

// Step 3: Save as workspace for reuse
POST /Analytics/Workspace/SaveWorkspace
{ "WorkspaceName": "North Region Q1 2026", "AnalysisId": 12, "IsShared": false,
  "ColumnConfigJson": "[{\"col\":\"Region\",...}, ...]",
  "FilterJson": "[{\"Column\":\"Region\",\"Value\":\"North\"}]" }

Scenario C — KPI Dashboard Setup for HR Role

Scenario D — PerfKPIFeed: Dapr Event → TPERFKPIFEED → KPI Dashboard

-- TPERFKPIFEED rows inserted for AppraisalPlanId=301, PeriodEnd=2026-03-31:
| FeedSource | SourceId | MetricKey           | MetricValue | MetricDetailJson        |
|------------|----------|---------------------|-------------|-------------------------|
| 0 (PERM)   | 301      | AVG_FINAL_SCORE     | 72.4        | NULL                    |
| 0 (PERM)   | 301      | BELL_CURVE_DIST     | 5           | [{"grade":"A","pct":14.2},{"grade":"B","pct":38.1},...]  |
| 0 (PERM)   | 301      | HIGH_PERFORMER_PCT  | 14.2        | NULL                    |
| 0 (PERM)   | 301      | PIP_TRIGGER_COUNT   | 6           | NULL                    |

-- TKPILIST updated for KPIId mapped to AVG_FINAL_SCORE:
| KPIId | Period     | ActualValue | TargetValue | Variance | SourceType | SourceRefId |
|-------|------------|-------------|-------------|----------|------------|-------------|
| 801   | 2026-03-31 | 72.4        | 70.0        | +2.4     | 1 (PERM)   | 301         |

Scenario E — ETL DataSync: GB4 Master Data Replication

// MSWSYNCRULE for MPRODUCT (delta sync — only rows modified in last N hours):
{
  "SyncRuleCode": "MPRODUCT_DELTA",
  "SourceDatabaseId": 1,       // GB4 OLTP SQL Server
  "TargetDatabaseId": 5,       // GB5 DataSync mirror
  "TargetTable": "MPRODUCT",
  "SyncMode": 1,               // Delta (0=Full)
  "DeltaColumn": "MODIFIEDON", // Incremental watermark column
  "BatchSize": 500,
  "Schedule": "*/15 * * * *",  // Every 15 min
  "IsActive": true
}

// On schedule fire — QuartzJob:
// 1. Read last successful sync watermark from TSWSYNCRUN
// 2. Extract: SELECT * FROM MPRODUCT WHERE MODIFIEDON > @lastWatermark
// 3. IAsyncEnumerable — 500 rows per batch
// 4. Transform: apply MSWOBJECTMAP column renames (GB4 COLNAME → GB5 ColumnName)
// 5. Load: BulkInsertAsync on new rows; ExecuteAsync UPDATE for existing
// 6. TSWSYNCRUN row: { TotalRows: 42, LoadedRows: 42, ErrorRows: 0, Status: 2 }

Scenario F — Pivot Report: Appraisal Bell Curve by Grade × Department

GET /CommonReport/Report?ReportViewId=721&AppraisalPlanId=301&IsPivotView=true&GroupBy=Grade&CrossBy=Department

// BE pivot engine returns:
// Columns: Grade | IT | Finance | HR | Sales | Total
// Row A    | 3   | 2  | 1  | 4   | 10
// Row B    | 12  | 8  | 5  | 13  | 38
// Row C    | 6   | 4  | 3  | 5   | 18
// Row D    | 1   | 1  | 0  | 2   | 4
// Total    | 22  | 15 | 9  | 24  | 70

// Same endpoint with Format=Excel → ClosedXML cross-tab xlsx with conditional formatting
// Same endpoint with Format=PDF → PuppeteerSharp rendered table with page header/footer

Caching Strategy

Security & Multi-Tenancy

Multi-Tenancy Rules

Role-Based Analytics Access

Sensitive Data Rules

• Never log individual appraisal scores, bell curve data, or EmployeeId in log messages
• PerfKPIFeed stores aggregate metrics only — no individual employee data in TPERFKPIFEED
• AsyncJob file URLs are signed with a short-lived token (60 min) — not permanent download links
• DataSync connections encrypted in transit; never stored in plain text in any table
• TSWSYNCERROR never stores full row data for tables with sensitive columns (MEMPLOYEE, TPAYSLIP)