How to use Sales Pipeline DataMarts

by

The Sales Pipeline DataMarts are designed to help you move confidently from opportunities, through orders, and on to invoices — without having to relearn the data model each time.

The most important thing to remember is this:

these views are meant to be combined.

Once you understand the pattern, building modular, reusable reporting becomes much easier.

Start by recognising the pattern

There are three Sales Pipeline domains:

  • Busopp – opportunities and pipeline intent
  • Order – contracted commitments
  • Invoice – billed and delivered value

Each domain follows the same structure:

  • Core details – header information and links to other domains
  • Meta codes – categorisation and grouping context
  • Meta dates – lifecycle, proposal, delivery and terms dates
  • Item values (lines) – numeric values (with currency context)

The consistency is deliberate. Learn it once, reuse it everywhere.

A simple way to think about the views

Rather than thinking in terms of tables, think in terms of questions:

  • What is it and who is it linked to?
    → start with core details
  • How should I slice or group it?
    → add meta codes
  • When did things happen?
    → bring in meta dates
  • What are the values I want to measure?
    → use item values (lines)

Most useful reports combine more than one of these layers.

Joining the views (use OBJECT_SEQ)

All Sales Pipeline views expose an OBJECT_SEQ field.

This is a paired identifier that combines:

  • Company identifier
  • Domain object identifier (e.g. Opportunity_NO, Order_NO, Invoice_ID)

It has been created specifically for your convenience.

When joining views within a domain, always join on OBJECT_SEQ.

This:

  • Avoids accidental cross-company joins
  • Removes the need to manage composite keys manually
  • Keeps joins consistent across Busopp, Order and Invoice

If you are joining across domains, OBJECT_SEQ remains the safest anchor, with domain link fields (e.g. Opportunity Number, Order ID) used intentionally on top.

A quick word on grain (this saves a lot of pain)

Each view operates at a different level of detail:

  • Core details
    One row per opportunity, order or invoice
  • Meta codes
    Multiple rows per record — one per code type
  • Meta dates
    Multiple rows per record — one per date type
  • Item values (lines)
    Multiple rows per record — this is where the numbers live

If totals ever look duplicated or unexpectedly large, it’s usually a grain issue rather than a data issue.

A recommended shaping step

Once you’ve applied some basic filtering (for example, limiting to the codes or dates you actually need), we strongly recommend creating a derived attribute field based on META_TYPE and ATTRIBUTE.

This gives each row a clear, reusable identity and makes pivoting and slicing much easier.

Example (Power Query):

= Table.AddColumn(
    Source,
    "CODE_ATTRIBUTE",
    each [META_TYPE] & "_" & [ATTRIBUTE],
    type text
)

Pivot-me: Working with long, thin meta tables

This approach:

  • Preserves the flexibility of the long thin model
  • Makes report visuals easier to build
  • Reduces ambiguity when multiple attribute types are in play

It’s a small step that pays off quickly.

How the domains relate to each other

Busopp, Order and Invoice represent sales pipeline progression, not duplication.

  • An opportunity may never become an order
  • One opportunity can lead to multiple orders
  • One order can be invoiced in parts over time

When joining across domains, do it intentionally and in one direction:

Busopp → Order → Invoice

Avoid assuming one-to-one relationships.

About currency and values

All numeric values are supplied as raw numbers with explicit currency context.

This means:

  • The DataMart does not decide the reporting currency
  • Currency conversion is handled in the report layer
  • Values remain transparent and auditable

If something looks “nearly right but not quite”, currency is often the first thing to check.

Using early aggregation with meta_values

In practice, it’s often helpful to aggregate item_values_lines into a meta_values-style layer early in report design.

This can:

  • Simplify your model
  • Reduce repetition in visuals
  • Make measures easier to reason about
  • Speed up early development

This is a valid and encouraged approach — as long as it’s done deliberately.

Common aggregation gotchas

Currency

  • Be explicit about which currency you’re aggregating
  • Don’t mix currencies unless you’re intentionally converting
  • Group by currency where appropriate

Units

  • Amounts and quantities usually sum cleanly
  • Percentages, rates and ratios often don’t
  • Some values need weighted calculations or should stay at line level

If a value describes how something was calculated rather than how much, pause before summing it.

A lightweight meta_values checklist

Before creating or using an aggregated value, ask:

  • ✔ Am I aggregating at the correct grain?
  • ✔ Is the currency explicit and consistent?
  • ✔ Is this value appropriate to sum?
  • ✔ Will this value be aggregated again later?
  • ✔ Is it clear this is a derived measure?

If you’re comfortable answering these, your aggregation is probably sound.

Where to start if you’re new

A simple starting journey is:

  1. Choose one domain (Busopp is a good place to begin)
  2. Build a basic count from core details
  3. Add meta dates to explore lifecycle timing
  4. Aggregate item values (lines) into a working meta_values layer
  5. Shape long thin meta data using a derived attribute field
  6. Use meta codes for slicing and grouping

When the second domain feels familiar, the model is doing its job.

Leave a Comment