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
                ↓
     Lakehouse / OneLake
                ↓
        Bronze → Silver → Gold
                ↓
    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 and file landing

Fabric Lakehouse
→ Bronze, Silver, and Gold Delta tables

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 or Fabric Notebook

Analytical storage
→ OneLake / Fabric Lakehouse

Curated analytical model
→ Lakehouse Gold tables

Business metrics
→ Semantic Model

Web application
→ Fabric Apps / Rayfin

Identity
→ Microsoft Entra ID

For this Lakehouse-first MVP, the following are not required initially:

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 Lakehouse
        ↓
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 a schema-enabled Fabric Lakehouse, use schemas to make the layer boundary visible:

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

The tables in this guide are the Lakehouse implementation of the medallion architecture. Bronze, Silver, and Gold are logical data layers inside the same Fabric Lakehouse, backed by OneLake. The Lakehouse SQL analytics endpoint can be used to inspect and query the tables with SQL, while notebooks or Data Factory can perform the transformations.

When to Use Fabric Warehouse Instead

Fabric Warehouse remains a valid alternative when the team needs a SQL-first relational serving layer, existing Warehouse conventions, or a separate downstream mart. In that design, load the validated Gold output from the Lakehouse into Warehouse tables and connect the semantic model to the approved serving layer. Do not use Warehouse as a reason to skip the raw landing, quality checks, and Silver transformations.

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.

Lakehouse Engineering Best Practices

Make Every Load Idempotent

An ingestion pipeline should be safe to retry. If the same file or batch is processed twice, it must not create duplicate business records.

Use a control or manifest table in the Lakehouse to record:

batch_id
source_file_name
source_system
file_size_bytes
file_hash
received_at_utc
started_at_utc
completed_at_utc
rows_received
rows_accepted
rows_rejected
pipeline_status

Use the source system's business key together with source_system and the source version or batch ID to identify a record. Use record_hash to detect changed content. Do not use the current row number as a permanent business identifier.

The expected behavior is:

Same file + same hash       → skip or mark as already processed
Same business key + changed hash → update the Silver current-state row
New business key             → insert a new Silver row
Invalid row                  → quarantine with a rejection reason

Use Deterministic Keys

ROW_NUMBER() is useful for demonstration queries, but it is not a stable surrogate-key strategy because row ordering can change between runs. For repeatable Gold loads, persist the generated key or use a deterministic key strategy based on the business key and source system.

Keep both values:

account_key  → warehouse or dimensional surrogate key
account_id   → source business key used for traceability

Facts should reference account_key, sales_rep_key, and solution_key. The original source IDs should remain available for reconciliation and troubleshooting.

Handle Late-Arriving Data Explicitly

An opportunity may arrive before its account or sales representative dimension record. Do not silently drop the fact through an inner join. Use an explicit unknown member, quarantine the row, or delay the Gold load until the required dimension is available.

For changing attributes such as sales region, manager, or practice assignment, choose one of these strategies before production onboarding:

The MVP can use current-state facts, but the chosen limitation must be visible to dashboard users. Do not describe a current-state model as historical forecasting.

Treat Data Quality as a Pipeline Contract

Data quality checks should produce measurable results, not just a list in documentation. At minimum, expose these checks per batch:

Duplicate business keys
Null required fields
Invalid dates
Deal values less than or equal to zero
Probabilities outside 0 to 1
Unknown account, sales rep, presales, or solution references
Invalid status transitions
Rejected-row percentage

Rows that cannot be safely transformed belong in a quarantine table such as silver.quarantine_record with the original payload, source file, batch ID, entity name, and rejection reason. A pipeline should fail or raise an alert when a configured threshold is exceeded; the threshold is a business decision and must be agreed with data owners.

Plan for Schema Changes

Keep Bronze tolerant of additive source columns, but apply strict types and required-field rules in Silver. When a source makes a breaking change, version the ingestion contract or raw table rather than silently changing the meaning of an existing column. Record schema changes in the manifest and review their impact on Gold facts, semantic measures, and Rayfin pages.

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 1

Closed Won probability = 1

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 Lakehouse Data Layer

After the dataset is validated, create the Fabric Lakehouse in the Fabric workspace.

Suggested name:

sales_presales_lakehouse

Create or enable schemas for the three medallion layers:

bronze
silver
gold

The tables exposed to the semantic model will be:

gold.dim_date
gold.dim_account
gold.dim_sales_rep
gold.dim_presales
gold.dim_solution

gold.fact_opportunity
gold.fact_sales_activity
gold.fact_presales_engagement
gold.fact_poc
gold.fact_sales_target

The initial ingestion flow:

CSV
 ↓
Lakehouse Files / landing
 ↓
Fabric Data Factory or Notebook
 ↓
Lakehouse Bronze
 ↓
Lakehouse Silver
 ↓
Lakehouse Gold

After loading the data, validate row counts.

For example:

SELECT COUNT(*)
FROM gold.fact_opportunity;

Then validate basic business aggregation:

SELECT
    stage,
    COUNT(*) AS opportunity_count,
    SUM(deal_value) AS pipeline_value
FROM gold.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 Lakehouse Gold layer is stable, create a Power BI Semantic Model.

The flow becomes:

Fabric Lakehouse Gold
       ↓
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]
)

The guide stores probability as a decimal from 0 to 1; for example, 75% is stored as 0.75. Keep this convention consistent in Bronze parsing, Silver validation, Gold calculations, DAX measures, and the UI.

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 Lakehouse SQL endpoint query that calculates the same definition.

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

Security, Performance, and Governance Recommendations

Apply Row-Level Security in the Semantic Model

Do not implement row-level access only in Rayfin filters. Define the access model in the Power BI Semantic Model so every consumer receives the same security behavior.

Start with the four personas defined earlier:

Executive        → company-wide or approved aggregate visibility
Sales manager    → opportunities for the manager's region or team
Presales manager → presales assignments and technical workload
Contributor      → the user's own opportunities and engagements

Maintain a security mapping table such as gold.user_access with the user's Entra ID, role, region, team, and optional employee or sales-rep key. Apply semantic-model roles against that mapping table, then test the model with representative users. A Rayfin page filter is not a substitute for semantic-model RLS because users may access the same model through another report or application.

Keep Direct Lake at the Gold Boundary

The semantic model should consume curated Gold tables or approved Gold views. Do not expose Bronze or Silver tables directly to Rayfin. Direct Lake is an access mode for the semantic model to read Fabric data; it does not remove the need for a modeled Gold layer, relationships, metric definitions, or security roles.

Measure performance with representative filters before changing capacity or architecture. Useful acceptance targets for this MVP are:

Overview page interactive query: target under 5 seconds
Daily pipeline completion: before the agreed reporting cut-off
Semantic model refresh or synchronization: observable and alertable
No full Bronze scans from interactive application queries

If the Gold model becomes too large or slow, first reduce columns, fix relationships, materialize expensive aggregations, and review query plans. Only then consider a Warehouse serving layer, additional capacity, or a different access mode.

Separate Development and Production

Use at least two Fabric workspaces when the MVP moves toward real data:

Sales-Presales-Intelligence-Dev
Sales-Presales-Intelligence-Prod

Promote notebooks, Data Factory pipelines, Lakehouse schemas, semantic-model definitions, and Rayfin configuration through source control or the organization's deployment process. Keep production data and credentials out of development. Use Entra ID groups and managed identity or the approved secret store for service authentication; never commit tokens or connection secrets to the repository.

Monitor Freshness and Reconciliation

The dashboard should expose whether its data is trustworthy today. Record and monitor:

Last successful source ingestion
Last successful Silver transformation
Last successful Gold load
Last semantic-model refresh
Rows accepted and rejected by batch
Unknown-key count
Metric reconciliation status

An alert should identify the failed stage, batch ID, source file, and rejection summary. This is more actionable than a generic "dashboard refresh failed" notification.

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 Lakehouse Gold

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 Lakehouse
        ✓

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:

The Fabric Lakehouse Gold layer 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.

Run the MVP from Scratch

The following sequence turns the architecture above into one practical execution path. Each stage has a validation checkpoint. Do not continue to the next stage when the checkpoint fails; a later layer should not hide an issue in an earlier layer.

1. Prepare the Local Project

Create or clone the project repository, then install the tools required for local validation:

git clone <repository-url>
cd sales-presales-intelligence
npm install

Before using Fabric, confirm that the local environment and the target tenant have the required access:

Node.js and npm
VS Code
Microsoft Fabric workspace access
Fabric capacity
Fabric Lakehouse availability
Power BI Semantic Model permissions
Fabric Apps / Rayfin availability
Microsoft Entra ID application permissions

Checkpoint: the repository installs successfully, the Fabric workspace is accessible, and the user can create or modify the required Fabric items.

2. Generate and Validate the Synthetic Data

Which Files Are Used?

The MVP uses nine CSV files. Each file represents one source entity and has a different role in the pipeline:

The first four files are reference or master data. They should be loaded before the transaction files because opportunities, engagements, PoCs, activities, and targets refer to their IDs, names, or email addresses.

Where Are the Files Locally?

In this repository, the ready-to-use fictional files are stored at:

public/sample-data/
├── 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

public/sample-data/ is the website's downloadable sample-data folder. It is useful for readers who want to inspect or download the files, but it should not be treated as the production ingestion directory.

The actual MVP data platform is built in Microsoft Fabric. The local folders only support preparation, local validation, documentation, and source control. The Fabric Lakehouse, Fabric Data Factory pipeline, OneLake landing area, semantic model, and Rayfin application are the implementation environment described by this tutorial.

For a separate data-engineering project, use a pipeline-oriented structure instead:

sales-presales-intelligence/
├── data/
│   ├── raw/
│   │   └── sample-data/       # untouched copies received from a source
│   ├── synthetic/             # generated or curated demo CSV files
│   ├── rejected/              # rows rejected during local validation
│   └── processed/             # optional local transformation output
├── scripts/
├── sql/
└── docs/

Copy the files from public/sample-data/ into data/raw/sample-data/ or data/synthetic/ in the separate pipeline repository. Keep the original files unchanged. Do not overwrite the raw files with cleaned output; the ability to replay an ingestion run is part of the Bronze design.

For a Fabric-first project, this local copy step is optional. You can upload the files from public/sample-data/ directly into the Fabric landing area during the MVP setup. The local project structure is shown only for teams that want reproducible file validation and versioned source artifacts before uploading to Fabric.

Where Are the Files Uploaded in Fabric?

There are three different destinations in the Fabric flow, and they should not be confused:

1. OneLake Files / landing area
   The uploaded CSV files are kept as source artifacts.

2. Fabric Lakehouse Bronze schema
   The CSV rows are loaded into raw source-shaped SQL tables.

3. Fabric Lakehouse Silver and Gold schemas
   Cleaned and modeled rows are written by transformation steps.

For a repeatable implementation, create a landing folder in the Fabric workspace or its OneLake-backed storage:

Files/landing/sales-presales-mvp/
├── accounts/accounts.csv
├── sales-reps/sales_reps.csv
├── presales-engineers/presales_engineers.csv
├── solutions/solutions.csv
├── opportunities/opportunities.csv
├── presales-engagements/presales_engagements.csv
├── pocs/pocs.csv
├── sales-activities/sales_activities.csv
└── sales-targets/sales_targets.csv

The landing folders preserve the uploaded files and support replay. Fabric Data Factory then reads each file from its landing path and writes rows into the matching Bronze table. The landing area is not the reporting layer and should not be used directly by Rayfin.

The destination map is:

Files/landing/sales-presales-mvp/accounts/accounts.csv
        → bronze.account_raw
        → silver.account
        → gold.dim_account

Files/landing/sales-presales-mvp/sales-reps/sales_reps.csv
        → bronze.sales_rep_raw
        → silver.sales_rep
        → gold.dim_sales_rep

Files/landing/sales-presales-mvp/presales-engineers/presales_engineers.csv
        → bronze.presales_engineer_raw
        → silver.presales_engineer
        → gold.dim_presales

Files/landing/sales-presales-mvp/solutions/solutions.csv
        → bronze.solution_raw
        → silver.solution
        → gold.dim_solution

Files/landing/sales-presales-mvp/opportunities/opportunities.csv
        → bronze.opportunity_raw
        → silver.opportunity
        → gold.fact_opportunity

Files/landing/sales-presales-mvp/presales-engagements/presales_engagements.csv
        → bronze.presales_engagement_raw
        → silver.presales_engagement
        → gold.fact_presales_engagement

Files/landing/sales-presales-mvp/pocs/pocs.csv
        → bronze.poc_raw
        → silver.poc
        → gold.fact_poc

Files/landing/sales-presales-mvp/sales-activities/sales_activities.csv
        → bronze.sales_activity_raw
        → silver.sales_activity
        → gold.fact_sales_activity

Files/landing/sales-presales-mvp/sales-targets/sales_targets.csv
        → bronze.sales_target_raw
        → silver.sales_target
        → gold.fact_sales_target

The exact Fabric UI label for the landing area can differ by workspace setup. The invariant is the data contract: preserve the original file in a controlled landing location, load it into the matching Bronze table, and only expose Silver or Gold data to downstream analytics.

Recommended Upload Order

Upload and ingest the files in three groups:

Group 1: reference data
accounts.csv
sales_reps.csv
presales_engineers.csv
solutions.csv

Group 2: primary CRM transactions
opportunities.csv

Group 3: related activities and targets
presales_engagements.csv
pocs.csv
sales_activities.csv
sales_targets.csv

The order is important for validation. Group 1 provides the reference values needed to resolve relationships in Group 2 and Group 3. The ingestion pipeline may still land all files first, but Silver and Gold transformations should not run until the required reference data is available.

Do not upload these CSV files directly to silver.* or gold.*. Bronze must receive the source-shaped rows first so that the original values, source file, batch ID, and ingestion timestamp remain auditable.

Generate or Copy the Files

For this article, use the existing fictional files from public/sample-data/. For a new project, generate the same file set in data/synthetic/. The generator should create both valid records and controlled exceptions, such as duplicate CRM rows, missing presales assignments, and unknown foreign keys.

Validate the files before uploading them. At minimum, check:

Required columns exist
IDs are unique within each source
Deal values are positive
Probabilities are between 0 and 1
Dates use one agreed format
Closed-won and closed-lost records follow the metric contract
Foreign-key references resolve to a known source record

Keep invalid rows in a validation output or rejection file. Do not silently remove them.

Checkpoint: the validation report records the number of received, accepted, rejected, and duplicate rows for every file.

3. Create the Fabric Lakehouse Layers

Create the Lakehouse in the Fabric workspace, enable schemas if required by the workspace experience, and create the three medallion layers:

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

Create the raw Bronze tables from the examples, then create the conformed Silver tables and Gold dimensions and facts. Keep the table grain and source identifier documented for every table. These are Lakehouse tables stored in OneLake; use the Lakehouse SQL analytics endpoint when you need to inspect them with SQL.

Checkpoint: all tables exist and their columns use the intended types. No application or semantic-model work is required yet.

4. Load Bronze and Record the Ingestion Run

Use Fabric Data Factory or a Fabric Notebook to read each CSV from the Lakehouse Files/landing/sales-presales-mvp/ folder and write it into its matching Bronze table:

accounts.csv              → bronze.account_raw
sales_reps.csv            → bronze.sales_rep_raw
opportunities.csv         → bronze.opportunity_raw
presales_engagements.csv  → bronze.presales_engagement_raw
pocs.csv                  → bronze.poc_raw

Add the batch identifier, source file name, ingestion timestamp, and record hash to every load. Store the run summary with rows received, duplicates, and rejected rows.

Checkpoint: Bronze row counts match the ingestion report, and the raw values remain traceable to their source files.

5. Transform Bronze into Silver

Parse and standardize the raw values before joining entities:

Currency text       → DECIMAL
Percentage text     → probability from 0 to 1
Date text           → DATE
Status variations   → OPEN, WON, or LOST
Blank values        → NULL where allowed
Duplicate records   → latest valid source version
Unknown references  → quarantine with a rejection reason

The Silver load should be repeatable. Running the same ingestion batch twice must not create a second business record.

Checkpoint: Silver tables contain typed, canonical values, and every rejected row is visible in the data-quality output.

6. Load Gold Dimensions and Facts

Load dimensions first so that facts can resolve surrogate keys:

dim_date
dim_account
dim_sales_rep
dim_presales
dim_solution

Then load the facts:

fact_opportunity
fact_sales_activity
fact_presales_engagement
fact_poc
fact_sales_target

Use deterministic keys and an explicit unknown member for unresolved dimensions. Do not lose fact rows through an inner join without recording the reason.

Run the core reconciliation queries:

SELECT COUNT(*) AS opportunity_count
FROM gold.fact_opportunity;

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
FROM gold.fact_opportunity;

Checkpoint: Gold totals reconcile with the Silver source after applying the documented business rules, and no unexpected orphan fact rows remain.

7. Create and Reconcile the Semantic Model

Connect the Power BI Semantic Model to Gold tables or the approved Gold views. Define relationships, formatting, row-level security, and the metric contract before building the application pages.

Compare the main measures with the Lakehouse SQL endpoint results:

Lakehouse total pipeline       = Semantic Model total pipeline
Lakehouse weighted pipeline    = Semantic Model weighted pipeline
Lakehouse closed-won value     = Semantic Model closed-won value
Lakehouse active PoC count     = Semantic Model active PoC count

Investigate any difference before continuing. Do not compensate for a semantic-model mismatch in React code.

Checkpoint: the semantic model returns the same values as the reconciled Gold queries for the same filters and reporting date.

8. Bootstrap Rayfin and Prove One Metric

Create the Rayfin data application from the supported template for the installed SDK version, then start it locally:

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

cd sales-presales-intelligence
npm install
npm run dev

Connect the application using the authentication and semantic-model configuration required by the selected Rayfin template. The first screen should prove only one end-to-end metric:

Total Pipeline

Checkpoint: the value rendered by Rayfin matches the value returned by the Semantic Model for the same filter context.

9. Add the MVP Views Incrementally

After the single-metric checkpoint passes, add the views in this order:

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

Every page should consume semantic-model measures or approved views. Keep filtering, loading, empty, error, and unauthorized states explicit. Do not recreate core pipeline calculations in individual components.

Checkpoint: each page can be reconciled to the same semantic-model measures and respects its intended access boundary.

10. Configure Identity, Deploy, and Verify

Configure Microsoft Entra ID according to the Rayfin and Fabric environment, then test access using representative executive, sales manager, presales manager, and individual-contributor accounts.

Run the deployment validation before publishing:

npx rayfin up --dry-run
npx rayfin up
npx rayfin up status

Verify the deployed application in this order:

Entra ID login
Workspace and semantic-model access
Row-level visibility
Overview KPI values
Filters and drilldowns
Navigation
Responsive layout
Data freshness and quality status

Final checkpoint: the deployed application loads successfully, each user sees only the data allowed by the access model, and the displayed KPIs match the reconciled Semantic Model.

This runbook is intentionally linear. It makes the distinction between a working data layer, a working semantic layer, and a working application explicit. A successful page load alone is not evidence that the end-to-end pipeline is correct.

Recommended Implementation Decisions

Use the following defaults for this MVP:

Do not add a custom API, PostgreSQL database, Redis, or AI recommendation service to the first release unless a requirement cannot be met by Fabric and the semantic model. Every extra system adds credentials, monitoring, deployment, and reconciliation work.

Before onboarding real corporate data, obtain data-owner approval, classify sensitive fields, mask or minimize customer and employee attributes, define retention rules, test RLS with real access personas, and document who owns each metric. Synthetic data validates the architecture; it does not prove that the production governance model is complete.

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.