> Sample blueprint generated by Blueprint BI (https://app.milestonebi.com/samples/saas). Shown exactly as generated.

# SaaS Executive Revenue Overview

> An executive Power BI report tracking ARR, Net Revenue Retention and Logo Churn against target, built on a Snowflake medallion warehouse fed by Salesforce and HubSpot. A monthly ARR snapshot fact drives retention and churn, while pipeline and marketing facts explain future growth.

## 1. Scope and inputs

**Purpose.** Are we on track to $12M ARR, are we retaining and expanding customers, and is pipeline sufficient to close the gap?

**Audience.** CEO, CFO, CRO and board members

**Default period.** Current fiscal year to date by month, refreshed daily

### Business rules
- **ARR.** Sum of annualised recurring subscription value (MRR x 12) on active contracts at month end, excluding one-off services. (calculated in: Snowflake gold layer (dbt), monthly snapshot)
- **Net Revenue Retention.** ARR today from customers active 12 months ago divided by those customers' ARR 12 months ago, including expansion, contraction and churn. (calculated in: Power BI measure over FactArrSnapshot)
- **Logo churn.** A customer churns in the month their ARR falls from above zero to zero; churn % = churned logos / logos active at start of month. (calculated in: dbt flag ChurnFlag in gold; rate in Power BI measure)
- **Active customer.** A customer with ARR greater than zero at month end. (calculated in: Snowflake gold layer (dbt), monthly snapshot)

## 2. Data architecture

### Data sources
| Source | System | Refresh | Notes |
|---|---|---|---|
| Salesforce CRM | Salesforce Sales Cloud | Daily 01:00 | Accounts, opportunities, contracts and subscription line items. |
| HubSpot Marketing | HubSpot | Daily 01:00 | Marketing-sourced leads, MQLs and campaign attribution. |
| Snowflake Billing Data | Snowflake | Daily 02:00 | Billing and subscription invoices already landed from the billing platform. |
| ARR Targets | Snowflake (finance planning upload) | Monthly | Board-approved monthly ARR targets by segment. |

### Ingestion: Fivetran into Snowflake, transformed with dbt Cloud
Incremental sync on SystemModstamp / updatedAt watermarks. Fivetran lands raw SaaS objects; dbt builds silver and gold models daily.

### Layers
- **Bronze** (Snowflake RAW database): Raw Fivetran replicas of Salesforce and HubSpot objects, untransformed.
- **Silver** (Snowflake + dbt staging models): Cleaned, deduplicated, typed entities with conformed account IDs.
- **Gold** (Snowflake + dbt marts): Kimball star: ARR snapshot, pipeline and lead facts with conformed dimensions.

### Semantic layer: Power BI semantic model (Import)
Imported star schema from Snowflake gold views, refreshed after dbt completes.
- Caveat: Data is only as fresh as the last scheduled refresh, not real time.
- Caveat: Model size must stay within the Premium/Fabric capacity memory limit.
- Caveat: Incremental refresh needs RangeStart/RangeEnd folding to Snowflake.

| Area | Design |
|---|---|
| Refresh and alerts | Ready by 06:00 ET daily; failures alert the BI support Teams channel and RevOps lead |
| History | 4 years of monthly snapshots, covering 12-month NRR lookback plus prior-year comparison |
| Environments | Dev, Test and Prod workspaces; dbt dev and prod targets |
| Deployment | Power BI deployment pipelines; dbt Cloud jobs |
| Source control | Semantic model and report as PBIP in Git, dbt project in the same repository |

### Governance and security
- RLS by Region using UserRegionMap from Salesforce user territory, CEO/CFO unrestricted.
- dbt tests: unique and not-null keys, ARR non-negative, snapshot month continuity.
- ARR reconciled to billing monthly with tolerance 0.5%, alerting Finance.
- Refresh failure alerts to the BI support Teams channel.
- Certified dataset endorsement and Purview lineage.

## 3. Data model: Star schema (galaxy with conformed dimensions)

Measures are stored on a dedicated `_Measures` table.

### FactArrSnapshot (fact)
_Grain: One row per customer per month end_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | foreign key | → DimDate[DateKey]; hidden | Month-end date key. |
| CustomerKey | INT | foreign key | → DimCustomer[CustomerKey]; hidden | Customer surrogate key. |
| ProductKey | INT | foreign key | → DimProduct[ProductKey]; hidden | Primary product plan. |
| ARR | DECIMAL(18,2) | semi additive | hidden | Annual recurring revenue at month end. |
| NewARR | DECIMAL(18,2) | additive | hidden | ARR from new logos this month. |
| ExpansionARR | DECIMAL(18,2) | additive | hidden | Upsell and cross-sell ARR increase. |
| ContractionARR | DECIMAL(18,2) | additive | hidden | Downgrade ARR decrease, negative. |
| ChurnedARR | DECIMAL(18,2) | additive | hidden | ARR lost from churned logos, negative. |
| ChurnFlag | INT | additive | hidden | 1 if customer churned this month. |
| ActiveFlag | INT | semi additive | hidden | 1 if ARR greater than zero at month end. |

### FactPipeline (fact)
_Grain: One row per open or closed opportunity per daily snapshot date_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | foreign key | → DimDate[DateKey]; hidden | Snapshot date key. |
| CustomerKey | INT | foreign key | → DimCustomer[CustomerKey]; hidden | Account surrogate key. |
| OpportunityStageKey | INT | foreign key | → DimOpportunityStage[OpportunityStageKey]; hidden | Pipeline stage. |
| OpportunityId | NVARCHAR(18) | degenerate | hidden | Salesforce opportunity ID. |
| CloseDate | DATE | date |  | Expected or actual close date. |
| PipelineARR | DECIMAL(18,2) | semi additive | hidden | Opportunity ARR amount on snapshot date. |
| IsOpen | INT | semi additive | hidden | 1 if open on snapshot date. |

### FactArrTarget (fact)
_Grain: One row per segment per month_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | foreign key | → DimDate[DateKey]; hidden | Month-end date key. |
| SegmentKey | INT | foreign key | → DimSegment[SegmentKey]; hidden | Customer segment. |
| TargetARR | DECIMAL(18,2) | semi additive | hidden | Planned ARR at month end. |

### DimDate (dimension · marked as date table)
_Grain: One row per calendar day_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | primary key | hidden | yyyymmdd key. |
| Date | DATE | date |  | Calendar date. |
| MonthNumber | INT | attribute | hidden | Month 1-12. |
| MonthName | NVARCHAR(10) | attribute | sort by MonthNumber | Short month name. |
| YearMonthNumber | INT | attribute | hidden | yyyymm sort key. |
| YearMonth | NVARCHAR(8) | attribute | sort by YearMonthNumber | Label e.g. Mar 2025. |
| QuarterNumber | INT | attribute | hidden | Quarter 1-4. |
| Quarter | NVARCHAR(2) | attribute | sort by QuarterNumber | Q1-Q4. |
| Year | INT | attribute |  | Calendar year. |
| IsMonthEnd | BIT | attribute | hidden | True on last day of month. |

### DimCustomer (dimension · SCD type 2)
_Grain: One row per customer account version_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| CustomerKey | INT | surrogate key | hidden | Generated surrogate key. |
| AccountId | NVARCHAR(18) | business key | hidden | Salesforce account ID. |
| SegmentKey | INT | foreign key | → DimSegment[SegmentKey]; hidden | Current segment. |
| CustomerName | NVARCHAR(200) | attribute |  | Account name. |
| Region | NVARCHAR(20) | attribute |  | Sales region, used by RLS. |
| Industry | NVARCHAR(50) | attribute |  | Customer industry vertical. |
| AcquisitionChannel | NVARCHAR(30) | attribute |  | HubSpot original source. |
| ValidFrom | DATE | date | hidden | SCD2 start. |
| ValidTo | DATE | date | hidden | SCD2 end. |

### DimSegment (dimension · SCD type 1)
_Grain: One row per customer segment_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| SegmentKey | INT | surrogate key | hidden | Generated surrogate key. |
| SegmentCode | NVARCHAR(10) | business key | hidden | Segment code. |
| Segment | NVARCHAR(20) | attribute | sort by SegmentOrder | SMB, Mid-Market, Enterprise, Strategic. |
| SegmentOrder | INT | attribute | hidden | Sort order. |

### DimProduct (dimension · SCD type 1)
_Grain: One row per subscription product plan_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ProductKey | INT | surrogate key | hidden | Generated surrogate key. |
| ProductCode | NVARCHAR(20) | business key | hidden | SKU code. |
| ProductFamily | NVARCHAR(50) | attribute |  | Platform, Analytics, Security, Integrations. |
| PlanTier | NVARCHAR(20) | attribute | sort by PlanTierOrder | Starter, Professional, Enterprise. |
| PlanTierOrder | INT | attribute | hidden | Tier sort order. |

### DimOpportunityStage (dimension · SCD type 1)
_Grain: One row per opportunity stage_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| OpportunityStageKey | INT | surrogate key | hidden | Generated surrogate key. |
| StageName | NVARCHAR(40) | business key | sort by StageOrder | Salesforce stage name. |
| StageOrder | INT | attribute | hidden | Funnel order. |
| WinProbability | DECIMAL(5,2) | non additive | hidden | Default stage probability. |

### Relationships

| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactArrSnapshot.DateKey | DimDate.DateKey | many-to-one | single | Yes |
| FactArrSnapshot.CustomerKey | DimCustomer.CustomerKey | many-to-one | single | Yes |
| FactArrSnapshot.ProductKey | DimProduct.ProductKey | many-to-one | single | Yes |
| DimCustomer.SegmentKey | DimSegment.SegmentKey | many-to-one | single | Yes |
| FactPipeline.DateKey | DimDate.DateKey | many-to-one | single | Yes |
| FactPipeline.CustomerKey | DimCustomer.CustomerKey | many-to-one | single | Yes |
| FactPipeline.OpportunityStageKey | DimOpportunityStage.OpportunityStageKey | many-to-one | single | Yes |
| FactArrTarget.DateKey | DimDate.DateKey | many-to-one | single | Yes |
| FactArrTarget.SegmentKey | DimSegment.SegmentKey | many-to-one | single | Yes |

## 4. DAX measures

28 measures in 9 display folders.

### ARR

**ARR** · `$#,0`
```dax
ARR =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactArrSnapshot ) ) )
RETURN CALCULATE ( SUM ( FactArrSnapshot[ARR] ), _LastDate )
```
ARR at the last snapshot date in context.

### Time Intelligence

**ARR PY** · `$#,0`
```dax
ARR PY =
CALCULATE ( [ARR], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
ARR at same point last year.

**ARR YoY %** · `0.0%`
```dax
ARR YoY % =
VAR _PY = [ARR PY]
RETURN IF ( _PY > 0, DIVIDE ( [ARR] - _PY, _PY ) )
```
ARR growth versus prior year.

**Net Revenue Retention PY** · `0.0%`
```dax
Net Revenue Retention PY =
CALCULATE ( [Net Revenue Retention], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
NRR at same point last year.

**Logo Churn % PY** · `0.00%`
```dax
Logo Churn % PY =
CALCULATE ( [Logo Churn %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Logo churn rate same period last year.

### Targets

**ARR Target** · `$#,0`
```dax
ARR Target =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactArrTarget ) ) )
RETURN CALCULATE ( SUM ( FactArrTarget[TargetARR] ), _LastDate )
```
Planned ARR at last target month in context.

**ARR vs Target** · `$#,0;-$#,0`
```dax
ARR vs Target =
VAR _T = [ARR Target]
RETURN IF ( NOT ISBLANK ( _T ), [ARR] - _T )
```
ARR gap to target in dollars.

**ARR vs Target %** · `0.0%`
```dax
ARR vs Target % =
VAR _T = [ARR Target]
RETURN IF ( _T > 0, DIVIDE ( [ARR], _T ) )
```
ARR attainment against target.

**NRR Target** · `0.0%`
```dax
NRR Target =
1.10
```
Board target for NRR, 110%.

**NRR vs Target** · `+0.0%;-0.0%`
```dax
NRR vs Target =
VAR _N = [Net Revenue Retention]
RETURN IF ( NOT ISBLANK ( _N ), _N - [NRR Target] )
```
NRR gap to target in percentage points.

**Logo Churn Target** · `0.00%`
```dax
Logo Churn Target =
0.015
```
Board target monthly logo churn, 1.5%.

**Logo Churn vs Target** · `+0.00%;-0.00%`
```dax
Logo Churn vs Target =
VAR _C = [Logo Churn %]
RETURN IF ( NOT ISBLANK ( _C ), _C - [Logo Churn Target] )
```
Churn gap to target; positive is worse.

### ARR Movement

**New ARR** · `$#,0`
```dax
New ARR =
SUM ( FactArrSnapshot[NewARR] )
```
ARR from new logos in period.

**Expansion ARR** · `$#,0`
```dax
Expansion ARR =
SUM ( FactArrSnapshot[ExpansionARR] )
```
Upsell and cross-sell ARR in period.

**Contraction ARR** · `$#,0;-$#,0`
```dax
Contraction ARR =
SUM ( FactArrSnapshot[ContractionARR] )
```
Downgrade ARR in period, negative.

**Churned ARR** · `$#,0;-$#,0`
```dax
Churned ARR =
SUM ( FactArrSnapshot[ChurnedARR] )
```
ARR lost to churn in period, negative.

**Net New ARR** · `$#,0;-$#,0`
```dax
Net New ARR =
[New ARR] + [Expansion ARR] + [Contraction ARR] + [Churned ARR]
```
Net ARR change in period.

### Customers

**Active Customers** · `#,0`
```dax
Active Customers =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactArrSnapshot ) ) )
RETURN CALCULATE ( SUM ( FactArrSnapshot[ActiveFlag] ), _LastDate )
```
Customers with ARR at last snapshot.

**Churned Logos** · `#,0`
```dax
Churned Logos =
SUM ( FactArrSnapshot[ChurnFlag] )
```
Customers that churned in period.

### Retention

**Net Revenue Retention** · `0.0%`
```dax
Net Revenue Retention =
VAR _End = MAX ( DimDate[Date] )
VAR _Start = EOMONTH ( _End, -12 )
VAR _Cohort = CALCULATETABLE ( VALUES ( FactArrSnapshot[CustomerKey] ), FactArrSnapshot[ActiveFlag] = 1, DimDate[Date] = _Start, REMOVEFILTERS ( DimDate ) )
VAR _StartARR = CALCULATE ( SUM ( FactArrSnapshot[ARR] ), _Cohort, DimDate[Date] = _Start, REMOVEFILTERS ( DimDate ) )
VAR _EndARR = CALCULATE ( SUM ( FactArrSnapshot[ARR] ), _Cohort, DimDate[Date] = EOMONTH ( _End, 0 ), REMOVEFILTERS ( DimDate ) )
RETURN IF ( _StartARR > 0, DIVIDE ( _EndARR, _StartARR ) )
```
Trailing 12-month ARR retained and expanded from the year-ago cohort.

**Logo Churn %** · `0.00%`
```dax
Logo Churn % =
VAR _Months = DISTINCTCOUNT ( DimDate[YearMonthNumber] )
VAR _Opening = CALCULATE ( [Active Customers], PREVIOUSMONTH ( DimDate[Date] ) )
RETURN IF ( _Opening > 0 && _Months > 0, DIVIDE ( DIVIDE ( [Churned Logos], _Months ), _Opening ) )
```
Average monthly logo churn rate on opening customers.

### Pipeline

**Open Pipeline ARR** · `$#,0`
```dax
Open Pipeline ARR =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactPipeline ) ) )
RETURN CALCULATE ( SUM ( FactPipeline[PipelineARR] ), FactPipeline[IsOpen] = 1, _LastDate )
```
Open opportunity ARR at latest snapshot.

**Pipeline Coverage** · `0.0x`
```dax
Pipeline Coverage =
VAR _Gap = [ARR Target] - [ARR]
RETURN IF ( _Gap > 0, DIVIDE ( [Open Pipeline ARR], _Gap ) )
```
Open pipeline over remaining ARR gap; blank once target is met.

**Open Opportunities** · `#,0`
```dax
Open Opportunities =
VAR _LastDate = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactPipeline ) ) )
RETURN CALCULATE ( DISTINCTCOUNT ( FactPipeline[OpportunityId] ), FactPipeline[IsOpen] = 1, _LastDate )
```
Distinct open opportunities at latest snapshot.

### Labels

**Selected Period Label** · `Text`
```dax
Selected Period Label =
VAR _Min = MIN ( DimDate[Date] )
VAR _Max = MAX ( DimDate[Date] )
RETURN "Period: " & FORMAT ( _Min, "MMM yyyy" ) & " - " & FORMAT ( _Max, "MMM yyyy" )
```
Selected period label for page subtitles.

### RAG

**ARR RAG** · `0`
```dax
ARR RAG =
VAR _Value = [ARR]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 12000000, 1,    -- Green
        _Value >= 11000000, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ $12,000,000), 2 Amber (≥ $11,000,000), 3 Red (< $11,000,000). Drives conditional formatting for ARR.

**Net Revenue Retention RAG** · `0`
```dax
Net Revenue Retention RAG =
VAR _Value = [Net Revenue Retention]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 1.1, 1,    -- Green
        _Value >= 1, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ 110.0%), 2 Amber (≥ 100.0%), 3 Red (< 100.0%). Drives conditional formatting for Net Revenue Retention.

**Logo Churn RAG** · `0`
```dax
Logo Churn RAG =
VAR _Value = [Logo Churn %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.015, 1,    -- Green
        _Value <= 0.02, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 1.50%), 2 Amber (≤ 2.00%), 3 Red (> 2.00%). Drives conditional formatting for Logo Churn %.

## 5. KPIs and RAG rules

| KPI | Definition | Value measure | RAG measure | Target | Green | Amber | Red |
|---|---|---|---|---|---|---|---|
| ARR | Annualised recurring subscription value of active contracts at period end. | [ARR] | [ARR RAG] | $12M | ≥ $12,000,000 | ≥ $11,000,000 | < $11,000,000 |
| Net Revenue Retention | Current ARR of year-ago customers / their ARR 12 months ago. | [Net Revenue Retention] | [Net Revenue Retention RAG] | 110% | ≥ 110.0% | ≥ 100.0% | < 100.0% |
| Logo Churn % | Churned logos per month / customers active at start of month. | [Logo Churn %] | [Logo Churn RAG] | 1.5% | ≤ 1.50% | ≤ 2.00% | > 2.00% |

RAG measures return 1 (green #047857), 2 (amber #B45309) or 3 (red #B91C1C). Use conditional formatting by rules on the RAG measure. Status is never shown by colour alone: pair each colour with an icon shape or the word.

## 6. Report design

### Page 1: Executive Summary
_Show ARR, NRR and churn against target at a glance._

- **Audience:** CEO, CFO, board
- **Default filter:** Current fiscal year to date
- **Slicers:** DimDate[Year] (dropdown); DimSegment[Segment] (buttons); DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Headline KPIs | kpi strip | values: [ARR]; values: [Net Revenue Retention]; values: [Logo Churn %]; tooltip: [ARR vs Target %]; tooltip: [ARR YoY %] | Status icon and word from each KPI's RAG measure; Variance vs target shown below value |
| ARR vs Target by Month | line | axis: DimDate[YearMonth]; values: [ARR]; values: [ARR Target]; tooltip: [ARR vs Target] | Target as dashed grey line; Data labels on last point in $M |
| ARR by Segment | donut | legend: DimSegment[Segment]; values: [ARR] | Labels show percent and $M |
| ARR Bridge YTD | waterfall | axis: [New ARR]; values: [Expansion ARR]; values: [Contraction ARR]; values: [Churned ARR] | Increases in brand indigo, decreases in red with labels; Data labels in $K |

### Page 2: ARR Growth
_Explain ARR growth by product, segment and region._

- **Audience:** CFO, CRO
- **Default filter:** Current fiscal year to date
- **Slicers:** DimProduct[ProductFamily] (dropdown); DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| ARR Movement by Month | stacked column | axis: DimDate[YearMonth]; values: [New ARR]; values: [Expansion ARR]; values: [Contraction ARR]; values: [Churned ARR] | Net New ARR shown as line overlay |
| ARR by Product Family | bar | axis: DimProduct[ProductFamily]; values: [ARR]; tooltip: [ARR YoY %] | Sorted descending; Data labels in $M |
| ARR vs Target by Segment and Region | matrix | rows: DimSegment[Segment]; values: [ARR]; values: [ARR Target]; values: [ARR vs Target %] | Icon and colour from [ARR RAG]; Region not used: targets are by segment only |

### Page 3: Retention & Churn
_Track NRR and logo churn, and find at-risk segments._

- **Audience:** CEO, VP Customer Success
- **Default filter:** Trailing 12 months
- **Slicers:** DimSegment[Segment] (buttons); DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| NRR Trend | line | axis: DimDate[YearMonth]; values: [Net Revenue Retention]; values: [Net Revenue Retention PY] | Constant line at 110% target |
| Logo Churn % Trend | line | axis: DimDate[YearMonth]; values: [Logo Churn %]; values: [Logo Churn % PY] | Constant line at 1.5% target |
| Churn by Segment | bar | axis: DimSegment[Segment]; values: [Logo Churn %]; tooltip: [Churned Logos] | Bar colour from [Logo Churn RAG] with labels showing status |
| Top Churned Accounts | table | values: DimCustomer[CustomerName]; values: DimSegment[Segment]; values: DimCustomer[Region]; values: [Churned ARR] | Sorted by Churned ARR ascending; Filter: Top 10 by largest [Churned ARR] loss |

### Page 4: Pipeline & Outlook
_Assess whether pipeline covers the remaining ARR gap._

- **Audience:** CRO, CFO
- **Default filter:** Latest snapshot, closing this fiscal year
- **Slicers:** DimSegment[Segment] (buttons); DimCustomer[Region] (dropdown): Limited to permitted regions for RLS-restricted users

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Open Pipeline by Stage | funnel | axis: DimOpportunityStage[StageName]; values: [Open Pipeline ARR]; tooltip: [Open Opportunities] | Data labels in $K |
| Pipeline Coverage by Segment | bar | axis: DimSegment[Segment]; values: [Pipeline Coverage]; tooltip: [Open Pipeline ARR] | Constant line at 3.0x; Blank shown as 'Target met' |
| Largest Open Opportunities | table | values: DimCustomer[CustomerName]; values: DimOpportunityStage[StageName]; values: FactPipeline[CloseDate]; values: [Open Pipeline ARR] | Sorted by Open Pipeline ARR descending; Filter: Top 15 by [Open Pipeline ARR] |

### Theme

| Role | Colours |
|---|---|
| Data colours (in order) | #312E81, #4338CA, #4F46E5, #6366F1, #818CF8, #A5B4FC, #9CA3AF |
| Good / neutral / bad | #047857, #B45309, #B91C1C |
| Text / background | #1F2937, #FFFFFF |
| Font | Segoe UI |

## 7. Assumptions and open questions

| # | Assumption or question | Impact if different |
|---|---|---|
| 1 | Reporting uses the calendar year; prior-year comparisons use calendar months. | Add fiscal columns to DimDate and rebase YoY measures on fiscal periods. |
| 2 | ARR excludes one-off services, usage overages and is net of discounts. | Change ARR logic in dbt and restate snapshot history. |
| 3 | Logo churn is counted at account level; a reduction to zero followed by return within 30 days is not churn. | Adjust ChurnFlag rule and churn counts in gold. |
| 4 | ARR targets are set by segment and month only, so target measures are not sliced by region or product. | Add RegionKey or ProductKey to FactArrTarget and relate it to those dimensions. |
| 5 | RLS region mapping comes from Salesforce user territory assignments. | Source a separate security table from HR or Entra ID groups. |
| 6 | Customer segment changes are tracked as SCD2 and NRR uses current segment. | Use historical segment on snapshot rows if cohorts must keep original segment. |

## Recommendations
- Configure incremental refresh on FactPipeline by snapshot date, keeping 2 years and refreshing the last 7 days.
- Build NRR cohort logic in dbt as a monthly table if DAX performance degrades beyond 2 seconds.
- Document every measure description and publish a KPI glossary page in the Power BI app.
- Use a field parameter later to let executives switch between ARR, NRR and churn on one trend chart.
- Schedule a monthly ARR reconciliation review with Finance before the board pack subscription sends.
- Track app usage metrics and retire visuals unused after 90 days.
