Building a Sales & Presales Intelligence Hub with Microsoft Fabric and Rayfin

Sales and Presales activities are closely connected in a corporate B2B sales process.

A single opportunity may involve:

Sales
  ↓
Qualification
  ↓
Discovery
  ↓
Presales Engagement
  ↓
Demo / Workshop / PoC
  ↓
Proposal
  ↓
Negotiation
  ↓
Closed Won / Closed Lost

However, the information used across those stages can be spread across different systems and formats.

For example:

CRM
Excel
Internal reports
Presales notes
PoC tracking
Sales targets
Opportunity updates

The challenge is not always that the data does not exist.

The bigger issue is that Sales, Presales, and Management may not have a single analytical view that connects commercial pipeline data with technical engagement.

That is the idea behind this MVP:

Can Microsoft Fabric and Rayfin be used to build a web-based Sales & Presales Intelligence Hub that consolidates pipeline, opportunity, presales engagement, PoC, workload, and solution demand into one analytical application?

The goal is not to build a new CRM.

The goal is to build an intelligence and visibility layer on top of Sales and Presales data.

What Are We Building?

The project is called:

Sales & Presales Intelligence Hub

It is a web-based internal analytical application designed to provide visibility across:

Executive Overview
Pipeline
Opportunities
Accounts
Sales Performance
Presales Engagement
PoC Tracking
Presales Workload
Solution Demand

The initial MVP focuses on descriptive and analytical use cases.

It does not initially include:

AI recommendation
Predictive forecasting
Opportunity risk prediction
Stalled deal detection
Full CRM functionality
Complex workflow automation
Native mobile application

The first objective is much simpler:

Build a reliable analytical foundation first, then introduce more advanced intelligence later.

A Realistic Industry Scenario

To make the use case concrete, assume the solution is being built for Nusantara Technology Services, a fictional regional B2B technology integrator. The company sells cloud migration, data platforms, cybersecurity, and application modernization services to mid-market and enterprise customers.

The organization has approximately:

3 sales regions
28 account executives
12 presales engineers
6 solution practices
250 active customer accounts

The numbers are fictional, but the operating model reflects a common enterprise sales environment. A typical opportunity may involve an account executive, a practice lead, one or more presales engineers, a delivery architect, procurement, and a customer sponsor.

The Current Operating Problem

Nusantara Technology Services already has systems in place. The problem is that each team uses a different operational view:

The weekly forecast meeting exposes the problem. Sales may report a healthy pipeline, while Presales sees that several large opportunities have no technical owner. A CRM opportunity may be marked as "proposal" even though the architecture workshop has not happened. A PoC can remain open in a tracker after the related opportunity has already been lost.

The issue is therefore not the absence of data. It is the absence of a governed analytical view across the commercial and technical lifecycle.

The Business Request

The regional sales director asks for a Monday morning view that answers five operational questions:

Which opportunities are expected to close in the next 90 days?
Which pipeline is qualified enough to include in the forecast?
Which deals need presales support but have no assigned engineer?
Where are active PoCs consuming capacity?
Which solution practices are generating demand but missing target?

The first release is intentionally read-only. Account executives continue updating the CRM, Presales continues managing engagement details in its tracker, and Finance continues owning targets. The Intelligence Hub gives those teams a shared decision view; it does not become another system of record.

Users and Access Boundaries

The application has four primary audiences:

This distinction matters because a realistic implementation must address not only what can be calculated, but also who is allowed to see each record. Customer names, deal values, margins, and employee workload should not automatically be visible to every authenticated user.

Why Use Microsoft Fabric?

Microsoft Fabric can act as the central data and analytics platform for this experiment.

The simplified architecture looks like this:

Synthetic / Semi-Real Corporate Data
              ↓
      Fabric Data Factory
              ↓
           OneLake
              ↓
       Fabric Warehouse
              ↓
    Power BI Semantic Model
         + Direct Lake
              ↓
     Fabric Apps / Rayfin
              ↓
 Sales & Presales Intelligence Hub
              ↓
      Microsoft Entra ID

Each component has a clear responsibility.

Fabric Data Factory
→ Data ingestion and orchestration

OneLake
→ Unified analytical storage

Fabric Warehouse
→ Structured analytical data

Power BI Semantic Model
→ Business metrics and relationships

Direct Lake
→ Analytical data access

Fabric Apps / Rayfin
→ Web application layer

Microsoft Entra ID
→ Authentication and enterprise identity

This keeps the MVP relatively simple and avoids introducing unnecessary backend infrastructure too early.

The Technology Stack

The main stack for the MVP is:

The key architectural principle is:

Use Fabric capabilities first before introducing additional Azure services.

For example:

Data ingestion
→ Fabric Data Factory

Analytical storage
→ OneLake

Relational analytical model
→ Fabric Warehouse

Business metrics
→ Semantic Model

Web application
→ Fabric Apps / Rayfin

Identity
→ Microsoft Entra ID

The MVP therefore does not initially require:

FastAPI
Custom Node.js backend
Azure Kubernetes Service
Azure Container Apps
Redis
PostgreSQL
Custom API Gateway
Custom authentication service

Dataset & Use Case Simulation

The MVP uses a public CRM sales opportunity dataset as the baseline and extends it with synthetic Presales and PoC data to simulate Nusantara Technology Services' operating model. The dataset is not presented as real company data. It is a controlled test dataset designed to preserve realistic relationships, access patterns, and business exceptions without exposing customer or employee information.

Baseline data:

• Accounts
• Products / Solutions
• Sales Team
• Sales Pipeline / Opportunities

Synthetic extension:

• Presales Engineers
• Presales Engagements
• PoC Tracking
• Sales Targets

Source mapping for the simulation:

The source systems remain authoritative. Fabric owns the analytical copy, transformation history, and conformed definitions used by the application.

Visual flow:

Kaggle CRM Dataset
        ↓
Data Transformation
        ↓
Fabric Warehouse
        ↓
Semantic Model
        ↓
Rayfin Web Application

Use case coverage:

• Pipeline Visibility
• Opportunity Analysis
• Sales Performance
• Presales Engagement
• PoC Tracking
• Solution Demand

Speaker notes:

For the MVP, we do not need to start with sensitive internal corporate data.
We can use a public CRM sales opportunity dataset as a baseline, then extend it with synthetic Presales and PoC data to represent our corporate Sales-Presales process.

This allows us to validate the architecture, data model, dashboard, and business value safely before considering real or masked internal data.

Phase 0 — Project Initialization

The first phase is not about building dashboards.

It is about preparing the project foundation.

Create a repository:

sales-presales-intelligence

A simple initial structure can look like this:

sales-presales-intelligence/

├── app/
│
├── data/
│   ├── raw/
│   ├── synthetic/
│   └── processed/
│
├── scripts/
├── sql/
├── docs/
├── tests/
│
├── README.md
└── .gitignore

The documentation directory should also be prepared early.

docs/

├── 00-project-overview.md
├── 01-business-requirement.md
├── 02-architecture.md
├── 03-data-model.md
├── 04-data-dictionary.md
├── 05-fabric-setup.md
├── 06-semantic-model.md
├── 07-rayfin-setup.md
├── 08-ui-design.md
└── 09-deployment.md

The Fabric workspace can be named:

Sales-Presales-Intelligence-MVP

Before continuing, validate:

Microsoft Fabric access
Fabric capacity
Workspace permission
Fabric Apps availability
Node.js
npm
VS Code

The important lesson here is simple:

Do not start developing the application before confirming that the required Fabric and Rayfin environment is available.

Phase 1 — Define the Business Process

Before creating tables, define how the Sales and Presales process works.

A simplified corporate flow can be:

Lead
 ↓
Qualification
 ↓
Discovery
 ↓
Presales Engagement
 ↓
Demo / PoC
 ↓
Proposal
 ↓
Negotiation
 ↓
Closed Won / Closed Lost

The MVP should model the process sufficiently to support analytics without trying to reproduce every CRM workflow.

Core business entities:

Accounts
Sales Representatives
Presales Engineers
Solution Categories
Opportunities
Sales Activities
Presales Engagements
PoC
Sales Targets

Possible solution categories:

Cloud
Software / Application
Data Platform
AI / GenAI
Infrastructure
Cybersecurity
Managed Services
Professional Services

The exact categories can later be adjusted to match the company's actual portfolio.

What Questions Should the MVP Answer?

A useful analytical system starts with business questions.

For Management:

How large is the current pipeline?

How much is the weighted pipeline?

What is target achievement?

Which solution category has the largest pipeline?

How many active opportunities exist?

How many PoCs are currently running?

For Sales:

Which opportunities are active?

How much pipeline does each salesperson own?

Which accounts have the largest opportunities?

Which solutions are generating demand?

For Presales:

Which opportunities currently require Presales support?

Which Presales engineer is assigned?

How many technical engagements are active?

How many PoCs are active?

How is Presales workload distributed?

These questions become the foundation for the data model and dashboard.

Define the Metric Contract Before Building Charts

The same word can mean different things to different teams. Before building the UI, the project team agrees on a metric contract:

The contract also defines a common reporting timezone, currency treatment, fiscal period, and the owner of each definition. This prevents a sales manager and a finance analyst from seeing different “pipeline” numbers simply because each dashboard applies a different filter.

Phase 2 — Design the Analytical Data Model

The MVP should use a dimensional model.

A simplified star-schema-oriented structure:

                    dim_date
                       │
                       │
dim_account ─── fact_opportunity ─── dim_sales_rep
                       │
                       │
                       ├──── dim_solution
                       │
                       ├──── fact_sales_activity
                       │
                       ├──── fact_presales_engagement
                       │
                       └──── fact_poc


dim_presales ─── fact_presales_engagement


dim_sales_rep ─── fact_sales_target

The initial dimensions:

dim_date
dim_account
dim_sales_rep
dim_presales
dim_solution

The initial fact tables:

fact_opportunity
fact_sales_activity
fact_presales_engagement
fact_poc
fact_sales_target

Example Opportunity Table

A simplified fact_opportunity may contain:

opportunity_id
account_id
sales_rep_id
solution_id
opportunity_name
stage
deal_value
probability
created_date
expected_close_date
actual_close_date
status

Each table should have a data dictionary documenting:

Column Name
Data Type
Description
Example
Nullable
Business Rule

For example:

Designing this before generating the dataset helps prevent inconsistent analytics later.

Example Database Schema: Bronze to Silver to Gold

The following example shows how the same Sales and Presales use case can be organized across three data layers.

Bronze: raw source-shaped data
        CRM, Excel, Presales tracker, and PoC tracker files
                                ↓
Silver: cleaned and conformed operational data
        standardized types, deduplicated records, and validated relationships
                                ↓
Gold: dimensional marts for analytics
        dimensions, facts, KPI views, and semantic-model-ready data

In Fabric Warehouse, use schemas to make the layer boundary visible:

CREATE SCHEMA bronze;
CREATE SCHEMA silver;
CREATE SCHEMA gold;

Common ingestion columns in Bronze and Silver are:

source_system       Source application, file, or tracker name
source_file_name    Original file name or object path
source_record_id    Identifier from the source system
ingestion_batch_id  Unique pipeline run identifier
ingested_at_utc     Timestamp when Fabric received the row
record_hash         Hash used to detect a changed source row

Bronze Layer: Raw Landing Tables

Bronze tables stay close to the input format. Do not apply business calculations here. A CRM export may contain currency symbols, percentage strings, inconsistent status values, and repeated snapshots.

CREATE TABLE bronze.opportunity_raw (
                source_system       VARCHAR(50),
                source_file_name    VARCHAR(255),
                source_record_id    VARCHAR(100),
                opportunity_name    VARCHAR(255),
                account_name        VARCHAR(255),
                sales_rep_email     VARCHAR(255),
                solution_name       VARCHAR(150),
                stage               VARCHAR(100),
                deal_value_text     VARCHAR(100),
                probability_text    VARCHAR(50),
                created_date_text   VARCHAR(50),
                expected_close_text VARCHAR(50),
                actual_close_text   VARCHAR(50),
                status              VARCHAR(100),
                snapshot_date_text  VARCHAR(50),
                ingestion_batch_id  VARCHAR(100),
                ingested_at_utc     DATETIME2(6),
                record_hash         VARCHAR(64)
);

CREATE TABLE bronze.presales_engagement_raw (
                source_system       VARCHAR(50),
                source_file_name    VARCHAR(255),
                source_record_id    VARCHAR(100),
                opportunity_ref     VARCHAR(100),
                presales_email      VARCHAR(255),
                engagement_type     VARCHAR(100),
                engagement_status   VARCHAR(100),
                start_date_text     VARCHAR(50),
                end_date_text       VARCHAR(50),
                estimated_days_text VARCHAR(50),
                notes               VARCHAR(2000),
                ingestion_batch_id  VARCHAR(100),
                ingested_at_utc     DATETIME2(6),
                record_hash         VARCHAR(64)
);

CREATE TABLE bronze.poc_raw (
                source_system        VARCHAR(50),
                source_file_name     VARCHAR(255),
                source_record_id     VARCHAR(100),
                opportunity_ref      VARCHAR(100),
                customer_name        VARCHAR(255),
                solution_name        VARCHAR(150),
                presales_email       VARCHAR(255),
                poc_status            VARCHAR(100),
                start_date_text      VARCHAR(50),
                target_end_date_text VARCHAR(50),
                outcome              VARCHAR(100),
                ingestion_batch_id   VARCHAR(100),
                ingested_at_utc      DATETIME2(6),
                record_hash          VARCHAR(64)
);

Apply the same pattern to bronze.account_raw, bronze.sales_rep_raw, bronze.presales_engineer_raw, bronze.solution_raw, bronze.sales_activity_raw, and bronze.sales_target_raw. The table names describe the business entity, while the schema identifies the pipeline layer.

Bronze is an auditable landing zone, not the reporting layer. If a source sends "Closed Won", "closed_won", and "Won", all values may remain in Bronze while Silver applies the canonical mapping.

For a sample pipeline, keep the uploaded file names aligned with the business entity. The pipeline can then add the layer-specific schema during ingestion:

The minimum pipeline sequence is:

Upload CSV files
        ↓
Load rows into the matching *_raw table
        ↓
Parse dates, currency, percentages, and status values
        ↓
Validate IDs and foreign-key relationships
        ↓
Upsert cleaned records into the curated tables
        ↓
Resolve dimension keys and load fact tables
        ↓
Refresh the semantic model

This approach keeps the sample files reusable. The same opportunities.csv can later come from a CRM export, while the pipeline contract and analytical table names remain unchanged.

The concrete fictional CSV files for this example are available in public/sample-data/, together with an upload order and pipeline notes in public/sample-data/README.md.

The files can also be downloaded directly from the website:

• accounts.csv
• sales_reps.csv
• presales_engineers.csv
• solutions.csv
• opportunities.csv
• presales_engagements.csv
• pocs.csv
• sales_activities.csv
• sales_targets.csv
• README.md

Silver Layer: Cleaned and Conformed Tables

Silver tables use stable data types, canonical values, and validated business keys. They are useful for data quality checks and for downstream products that do not need the Gold star schema.

CREATE TABLE silver.account (
        account_id         VARCHAR(50),
        account_name       VARCHAR(255),
        industry           VARCHAR(100),
        country_code       CHAR(2),
        segment            VARCHAR(50),
        annual_revenue     DECIMAL(19, 4),
        is_active           BIT,
        source_system      VARCHAR(50),
        source_record_id   VARCHAR(100),
        updated_at_utc     DATETIME2(6),
        ingestion_batch_id VARCHAR(100)
);

CREATE TABLE silver.sales_rep (
        sales_rep_id       VARCHAR(50),
        full_name           VARCHAR(255),
        email               VARCHAR(255),
        region              VARCHAR(100),
        manager_name        VARCHAR(255),
        is_active           BIT,
        source_system       VARCHAR(50),
        source_record_id    VARCHAR(100),
        updated_at_utc      DATETIME2(6),
        ingestion_batch_id  VARCHAR(100)
);

CREATE TABLE silver.presales_engineer (
        presales_id         VARCHAR(50),
        full_name            VARCHAR(255),
        email                VARCHAR(255),
        specialization       VARCHAR(150),
        region               VARCHAR(100),
        capacity_days_month  DECIMAL(10, 2),
        is_active             BIT,
        source_system         VARCHAR(50),
        source_record_id      VARCHAR(100),
        updated_at_utc        DATETIME2(6),
        ingestion_batch_id    VARCHAR(100)
);

CREATE TABLE silver.solution (
        solution_id          VARCHAR(50),
        solution_name        VARCHAR(150),
        solution_group       VARCHAR(100),
        is_active             BIT,
        source_system         VARCHAR(50),
        source_record_id      VARCHAR(100),
        updated_at_utc        DATETIME2(6),
        ingestion_batch_id    VARCHAR(100)
);

CREATE TABLE silver.opportunity (
        opportunity_id       VARCHAR(50),
        account_id            VARCHAR(50),
        sales_rep_id          VARCHAR(50),
        solution_id           VARCHAR(50),
        opportunity_name     VARCHAR(255),
        stage                 VARCHAR(50),
        deal_value            DECIMAL(19, 4),
        probability           DECIMAL(5, 4),
        currency_code         CHAR(3),
        created_date          DATE,
        expected_close_date   DATE,
        actual_close_date     DATE,
        status                VARCHAR(20),
        source_system         VARCHAR(50),
        source_record_id      VARCHAR(100),
        updated_at_utc        DATETIME2(6),
        ingestion_batch_id    VARCHAR(100)
);

CREATE TABLE silver.presales_engagement (
        engagement_id         VARCHAR(50),
        opportunity_id        VARCHAR(50),
        presales_id           VARCHAR(50),
        solution_id           VARCHAR(50),
        engagement_type       VARCHAR(100),
        status                VARCHAR(20),
        start_date            DATE,
        end_date              DATE,
        estimated_days        DECIMAL(10, 2),
        source_system         VARCHAR(50),
        source_record_id      VARCHAR(100),
        updated_at_utc        DATETIME2(6),
        ingestion_batch_id    VARCHAR(100)
);

CREATE TABLE silver.poc (
        poc_id                VARCHAR(50),
        opportunity_id        VARCHAR(50),
        account_id            VARCHAR(50),
        solution_id           VARCHAR(50),
        presales_id           VARCHAR(50),
        poc_name              VARCHAR(255),
        status                VARCHAR(20),
        start_date            DATE,
        target_end_date       DATE,
        actual_end_date       DATE,
        outcome               VARCHAR(50),
        source_system         VARCHAR(50),
        source_record_id      VARCHAR(100),
        updated_at_utc        DATETIME2(6),
        ingestion_batch_id    VARCHAR(100)
);

The remaining Silver tables follow the same conventions:

silver.sales_activity
  One row per recorded sales activity.

silver.sales_target
  One row per sales representative, target period, and target type.

Typical transformations are:

"Rp 1.250.000.000"   → 1250000000.0000
"75%"                → 0.7500
"Closed Won", "Won"  → status = "WON"
Blank end date        → NULL
Repeated source row   → keep the latest ingested version
Unknown account       → quarantine or reject, never silently join

Gold Layer: Dimensional Model

Gold is the contract consumed by the Power BI Semantic Model. Dimension tables use surrogate keys while retaining the business key for traceability.

CREATE TABLE gold.dim_date (
        date_key          INT,
        calendar_date     DATE,
        year_number       SMALLINT,
        quarter_number    TINYINT,
        month_number      TINYINT,
        month_name        VARCHAR(20),
        year_month        CHAR(7),
        is_month_end      BIT
);

CREATE TABLE gold.dim_account (
        account_key       BIGINT,
        account_id        VARCHAR(50),
        account_name      VARCHAR(255),
        industry          VARCHAR(100),
        country_code      CHAR(2),
        segment           VARCHAR(50),
        is_current        BIT,
        valid_from_date   DATE,
        valid_to_date     DATE
);

CREATE TABLE gold.dim_sales_rep (
        sales_rep_key     BIGINT,
        sales_rep_id      VARCHAR(50),
        full_name         VARCHAR(255),
        email             VARCHAR(255),
        region            VARCHAR(100),
        manager_name      VARCHAR(255),
        is_current        BIT
);

CREATE TABLE gold.dim_presales (
        presales_key       BIGINT,
        presales_id        VARCHAR(50),
        full_name          VARCHAR(255),
        email              VARCHAR(255),
        specialization     VARCHAR(150),
        region             VARCHAR(100),
        capacity_days_month DECIMAL(10, 2),
        is_current         BIT
);

CREATE TABLE gold.dim_solution (
        solution_key       BIGINT,
        solution_id        VARCHAR(50),
        solution_name      VARCHAR(150),
        solution_group     VARCHAR(100),
        is_current         BIT
);

The main Gold facts can be defined as follows:

CREATE TABLE gold.fact_opportunity (
        opportunity_key          BIGINT,
        opportunity_id           VARCHAR(50),
        account_key              BIGINT,
        sales_rep_key            BIGINT,
        solution_key             BIGINT,
        created_date_key         INT,
        expected_close_date_key  INT,
        actual_close_date_key    INT,
        opportunity_name         VARCHAR(255),
        stage                    VARCHAR(50),
        status                   VARCHAR(20),
        currency_code            CHAR(3),
        deal_value               DECIMAL(19, 4),
        probability              DECIMAL(5, 4),
        weighted_value           DECIMAL(19, 4),
        opportunity_count        INT
);

CREATE TABLE gold.fact_presales_engagement (
        engagement_key     BIGINT,
        engagement_id      VARCHAR(50),
        opportunity_key    BIGINT,
        account_key        BIGINT,
        presales_key       BIGINT,
        solution_key       BIGINT,
        start_date_key     INT,
        end_date_key       INT,
        engagement_type    VARCHAR(100),
        status             VARCHAR(20),
        estimated_days     DECIMAL(10, 2),
        engagement_count   INT
);

CREATE TABLE gold.fact_poc (
        poc_key             BIGINT,
        poc_id              VARCHAR(50),
        opportunity_key     BIGINT,
        account_key         BIGINT,
        presales_key        BIGINT,
        solution_key        BIGINT,
        start_date_key      INT,
        target_end_date_key INT,
        actual_end_date_key INT,
        poc_name            VARCHAR(255),
        status              VARCHAR(20),
        outcome             VARCHAR(50),
        poc_count           INT
);

CREATE TABLE gold.fact_sales_activity (
        activity_key        BIGINT,
        activity_id         VARCHAR(50),
        opportunity_key     BIGINT,
        account_key         BIGINT,
        sales_rep_key       BIGINT,
        activity_date_key   INT,
        activity_type       VARCHAR(100),
        activity_status     VARCHAR(20),
        duration_minutes    INT,
        activity_count      INT
);

CREATE TABLE gold.fact_sales_target (
        target_key           BIGINT,
        sales_rep_key        BIGINT,
        target_date_key      INT,
        target_type          VARCHAR(50),
        target_value         DECIMAL(19, 4),
        currency_code        CHAR(3)
);

The grain of each fact must be documented and tested:

For a first MVP, facts can be current-state tables. If historical pipeline movement matters, add snapshot_date_key to fact_opportunity and make the grain one row per opportunity per snapshot date. This distinguishes current pipeline from historical pipeline evolution.

Silver-to-Gold Mapping and KPI View

The transformation into Gold should be explicit. A simplified opportunity load looks like this:

INSERT INTO gold.fact_opportunity (
        opportunity_key, opportunity_id, account_key, sales_rep_key, solution_key,
        created_date_key, expected_close_date_key, opportunity_name, stage, status,
        currency_code, deal_value, probability, weighted_value, opportunity_count
)
SELECT
        ROW_NUMBER() OVER (ORDER BY opportunity_id),
        opportunity_id,
        account.account_key,
        sales_rep.sales_rep_key,
        solution.solution_key,
        CONVERT(INT, FORMAT(created_date, 'yyyyMMdd')),
        CONVERT(INT, FORMAT(expected_close_date, 'yyyyMMdd')),
        opportunity_name,
        stage,
        status,
        currency_code,
        deal_value,
        probability,
        deal_value * probability,
        1
FROM silver.opportunity
JOIN gold.dim_account AS account
        ON account.account_id = silver.opportunity.account_id
   AND account.is_current = 1
JOIN gold.dim_sales_rep AS sales_rep
        ON sales_rep.sales_rep_id = silver.opportunity.sales_rep_id
   AND sales_rep.is_current = 1
JOIN gold.dim_solution AS solution
        ON solution.solution_id = silver.opportunity.solution_id
   AND solution.is_current = 1;

CREATE VIEW gold.vw_executive_pipeline AS
SELECT
        SUM(CASE WHEN status = 'OPEN' THEN deal_value ELSE 0 END) AS total_pipeline,
        SUM(CASE WHEN status = 'OPEN' THEN weighted_value ELSE 0 END) AS weighted_pipeline,
        SUM(CASE WHEN status = 'WON' THEN deal_value ELSE 0 END) AS closed_won_value,
        COUNT(CASE WHEN status = 'OPEN' THEN 1 END) AS open_opportunity_count
FROM gold.fact_opportunity;

In production, use a deterministic surrogate-key strategy instead of relying on ROW_NUMBER() for repeatable loads. The example is intentionally short; a real pipeline should also resolve dimension keys, handle unknown members, and make the load idempotent.

The semantic model should connect to Gold tables or purpose-built views, never directly to Bronze. Calculate weighted_value once in Gold or the semantic layer and reuse that definition everywhere. Rayfin should consume the validated metric rather than reimplementing pipeline logic in multiple React components.

Phase 3 — Generate Synthetic Corporate Data

The initial MVP should avoid production corporate data.

Use fictional but realistic synthetic data.

The synthetic records should also include realistic data-quality exceptions that the pipeline must handle:

An opportunity with a missing presales assignment
A closed-won deal whose actual close date differs from the forecast date
A PoC linked to an opportunity with a different solution label
An inactive sales representative who still owns historical opportunities
Duplicate CRM exports from two ingestion batches
An engagement that runs beyond its original estimated end date

These are not random errors added to make the demo look difficult. They represent the kinds of exceptions that determine whether an operational dashboard can be trusted.

A reasonable dataset size is:

Sales Representatives       28
Presales Engineers          12
Accounts                    250
Solution Categories         6
Opportunities               800–1,200
Sales Activities            5,000+
Presales Engagements        1,000+
PoCs                        120–180
Sales Targets               600+

Historical range:

January 2025
to
August 2026

The generated files may look like:

data/synthetic/

accounts.csv
sales_reps.csv
presales.csv
solutions.csv
opportunities.csv
sales_activities.csv
presales_engagements.csv
pocs.csv
sales_targets.csv

Synthetic Data Should Not Be Purely Random

A good synthetic dataset should contain recognizable business patterns.

For example:

One salesperson with high pipeline and strong conversion

One salesperson with large pipeline but low achievement

Several strategic enterprise opportunities

Several small opportunities

Different demand across solution categories

Different Presales workload distributions

Active and completed PoCs

Won opportunities

Lost opportunities

The purpose is not to manipulate the result.

The purpose is to ensure the dataset is rich enough to demonstrate analytical scenarios.

Validate the Dataset Before Fabric

Do not immediately upload generated CSV files.

Validate:

No duplicate IDs

No broken foreign keys

Deal value > 0

Probability between 0 and 100%

Closed Won probability = 100%

Closed Lost probability = 0%

Valid account relationships

Valid Sales Rep relationships

Valid Presales relationships

Valid solution relationships

Reasonable date relationships

This stage matters because bad synthetic data produces misleading dashboard results.

Phase 4 — Build the Fabric Data Layer

After the dataset is validated, create the Fabric Warehouse.

Suggested name:

sales_presales_wh

Tables:

dbo.dim_date
dbo.dim_account
dbo.dim_sales_rep
dbo.dim_presales
dbo.dim_solution

dbo.fact_opportunity
dbo.fact_sales_activity
dbo.fact_presales_engagement
dbo.fact_poc
dbo.fact_sales_target

The initial ingestion flow:

CSV
 ↓
Fabric Data Factory
 ↓
Fabric Warehouse

After loading the data, validate row counts.

For example:

SELECT COUNT(*)
FROM dbo.fact_opportunity;

Then validate basic business aggregation:

SELECT
    stage,
    COUNT(*) AS opportunity_count,
    SUM(deal_value) AS pipeline_value
FROM dbo.fact_opportunity
GROUP BY stage;

At this stage the goal is not visualization.

The goal is:

Can the Fabric data layer answer basic Sales and Presales questions correctly?

Data Quality and Operational Controls

An industry implementation also needs an operational contract around the data. For each ingestion run, record:

Source file or API endpoint
Ingestion timestamp
Batch identifier
Rows received
Rows accepted
Rows rejected
Duplicate count
Unknown-key count
Pipeline status

Rows with an unknown account, sales representative, or solution should be placed in a quarantine table with a rejection reason. They should not silently disappear through an inner join. A daily refresh should fail or raise an alert when rejected rows exceed an agreed threshold, for example:

CRM opportunity rejects       < 1% of received rows
Broken foreign-key references 0 for Gold facts
Stale source refresh          < 26 hours

The threshold is a business decision, not a universal technical constant. The important point is that the dashboard must expose freshness and quality status to its users.

Phase 5 — Build the Semantic Model

Once the Warehouse is stable, create a Power BI Semantic Model.

The flow becomes:

Fabric Warehouse
       ↓
Power BI Semantic Model
       ↓
Business Measures
       ↓
Rayfin Web Application

The semantic model should contain:

Relationships
Business measures
Formatting
Business terminology
Calculation logic

Initial measures can include:

Total Pipeline
Weighted Pipeline
Closed Won Revenue
Opportunity Count
Open Opportunities
Sales Target
Target Achievement %
Pipeline by Solution
Pipeline by Industry
Active Presales Engagement
Active PoC
Presales Engagement Count

For example:

Total Pipeline :=
CALCULATE(
    SUM(fact_opportunity[deal_value]),
    fact_opportunity[status] = "Open"
)

Weighted pipeline:

Weighted Pipeline :=
SUMX(
    fact_opportunity,
    fact_opportunity[deal_value]
        * fact_opportunity[probability]
)

If probability is stored as a percentage from 0 to 100, normalize it accordingly.

The important architectural rule is:

Do not duplicate core business metric calculations across frontend components.

Business metrics should live in the semantic layer whenever practical.

Validate Semantic Measures

Every important DAX measure should be checked against SQL.

For example:

Semantic Model:

Total Pipeline
Rp18.7B

should match a Warehouse query that calculates the same definition.

This makes the semantic model a trustworthy analytical layer rather than just another abstraction.

Phase 6 — Bootstrap the Rayfin Web Application

Only after the semantic model is stable should the web application be introduced.

The application will use:

React
TypeScript
Fabric Apps
Rayfin

The initial Rayfin Data App project can be created from the supported data app template.

Example:

npm create @microsoft/rayfin@latest -- \
"sales-presales-intelligence" \
--template dataapp \
--workspace "Sales-Presales-Intelligence-MVP"

Then run locally:

cd sales-presales-intelligence

npm run dev

Do not immediately build every dashboard.

The first milestone is simply:

TOTAL PIPELINE
Rp18.7B

If the application can retrieve and display one validated semantic-model metric, the end-to-end flow has been proven:

Rayfin
   ↓
Semantic Model
   ↓
Fabric Warehouse

That is an important checkpoint.

Phase 7 — Build the Web Application Incrementally

The UI should be built in small iterations.

Sprint 1 — Executive Overview

Display:

Total Pipeline
Weighted Pipeline
Closed Won
Target Achievement
Active Opportunities
Active PoC
Active Presales Engagement

Add:

Pipeline by Stage
Pipeline by Solution
Target vs Achievement

Sprint 2 — Opportunities

Create an opportunity list containing:

Opportunity
Account
Sales Owner
Solution
Stage
Deal Value
Probability
Expected Close Date

Add filters:

Stage
Solution
Sales Rep
Industry
Period

Sprint 3 — Sales Performance

Display:

Sales Rep
Pipeline
Closed Won
Opportunity Count
Target
Achievement

Sprint 4 — Presales Engagement

Display:

Opportunity
Presales Owner
Engagement Type
Solution
Start Date
Status

Possible engagement types:

Discovery
Technical Workshop
Demo
Solution Design
Architecture Review
PoC
Proposal Support

Sprint 5 — PoC Tracking

Display:

PoC Name
Opportunity
Customer
Solution
Presales Owner
Start Date
End Date
Status

Sprint 6 — Presales Workload

Keep the first version simple.

Display:

Presales Engineer
Active Opportunities
Active Engagements
Active PoCs

Do not introduce advanced utilization or efficiency scoring yet.

Sprint 7 — Solution Demand

Display:

Opportunity Count by Solution
Pipeline Value by Solution
Presales Engagement by Solution
PoC Count by Solution

This provides a simple view of which technology or solution areas are generating demand.

Phase 8 — Refine the UI/UX

Once the analytical flow works, improve the web experience.

Design principles:

Desktop-first
Responsive
Clean enterprise UI
Readable information hierarchy
Low visual noise
Fast rendering
Consistent components

Suggested navigation:

Overview
Pipeline
Opportunities
Accounts
Sales
Presales
PoC
Solution Demand

Avoid:

Excessive animations
Heavy gradients
Too many KPI cards
Overdecorated charts
Generic "AI dashboard" visuals

The UI should look like an enterprise analytical product rather than a dashboard template.

Phase 9 — Deploy the Fabric Application

Before deployment, run a dry run.

npx rayfin up --dry-run

Then deploy:

npx rayfin up

Check status:

npx rayfin up status

After deployment, validate:

Microsoft Entra ID login
Dashboard access
Semantic model access
Filters
Navigation
Responsive layout
Data consistency

Deployment is not complete just because the application URL loads.

The data and metrics must still match the validated semantic model.

Phase 10 — Corporate Demo and Validation

The MVP should be presented as an internal PoC.

Do not position it as:

CRM replacement
Production-ready enterprise platform
AI-powered sales platform

Instead position it as:

An exploration of how Microsoft Fabric and Rayfin can provide a unified Sales and Presales analytical layer.

A good demo sequence:

Executive Overview
        ↓
Pipeline by Solution
        ↓
Opportunity Drilldown
        ↓
Sales Performance
        ↓
Presales Engagement
        ↓
Active PoC
        ↓
Presales Workload
        ↓
Solution Demand

Then collect stakeholder feedback.

Questions to ask:

Is this information relevant?

Which data sources actually exist internally?

Which KPIs matter most to Management?

How are Presales engagements currently tracked?

How are PoCs currently tracked?

Who should be the primary user?

Which corporate data could be safely used in Phase 2?

The purpose of the first PoC is not only to demonstrate technology.

It is also to validate whether the use case has enough business value to continue.

Definition of Done

The initial MVP is considered complete when the following are working:

Corporate synthetic dataset
        ✓

Fabric Workspace
        ✓

Fabric Warehouse
        ✓

Star Schema
        ✓

Semantic Model
        ✓

Core Business Measures
        ✓

Rayfin Web Application
        ✓

Executive Overview
        ✓

Pipeline Dashboard
        ✓

Opportunity Analysis
        ✓

Sales Performance
        ✓

Presales Engagement
        ✓

PoC Tracking
        ✓

Presales Workload
        ✓

Solution Demand
        ✓

Microsoft Entra Authentication
        ✓

Fabric Deployment
        ✓

Corporate Demo
        ✓

The Implementation Principle

One of the most important lessons for this project is the order of implementation.

Use:

Data
 ↓
Validate
 ↓
Model
 ↓
Validate
 ↓
Semantic Layer
 ↓
Validate
 ↓
Application
 ↓
Validate
 ↓
Deploy

Avoid:

Build everything
      ↓
Test everything at the end

The dashboard should not be the first thing we build.

The first real milestone should be:

Fabric Warehouse can calculate Total Pipeline correctly from a validated synthetic corporate dataset.

The second milestone:

The Power BI Semantic Model exposes that metric consistently.

The third:

Rayfin can retrieve and display that metric inside the web application.

Only after those three steps are proven should the project expand into additional pages and visualizations.

What Comes After the MVP?

Once the analytical foundation is validated, the next phase can explore:

Real / masked corporate data

CRM integration

Dynamics 365 integration

Opportunity risk scoring

Stalled opportunity detection

Win / Loss analysis

PoC effectiveness

Presales efficiency

AI-assisted Sales insights

Predictive forecasting

But those capabilities should be treated as extensions.

The MVP exists to answer a more fundamental question:

Can Sales and Presales data be consolidated in Microsoft Fabric and exposed through a useful web-based analytical application using Rayfin?

If the answer is yes, the project has a strong foundation for a much more advanced Sales and Presales intelligence platform.