The Join You Cannot Make
A report here is built on one dataset. That is a sound design and it forecloses a whole class of question — the ones whose answer needs a supplier and a customer, or a purchase and a complaint, in the same row.
The questions that make a business better are almost never about one thing. They are about the relationship between two things that live in different parts of the system, and that is exactly the shape a report builder finds hardest.
What a dataset is
A curated surface. Rather than exposing the database, this product offers named datasets, and a report is built on one — its fields, its filters and its calculations all come from that dataset's definition.
That is a genuinely good design. It means a report cannot reference a column that was never meant to be reportable, it means the field-level visibility model has somewhere to attach, and it means a schema change does not silently break every saved report.
It also means the question you can ask is bounded by the dataset somebody built.
The questions this forecloses
| The question | What it needs in one row | Available |
|---|---|---|
| Which supplier's goods generate the most complaints? | A purchase order and a support ticket | No |
| Do late deliveries correlate with lost sales? | A receipt date and a sales line | No |
| Which items are discounted most at the till and also slowest moving? | A discount and a movement history | No |
| Are the branches with the most stock variance also the ones with the highest staff turnover? | A count session and an employee record | No |
| What is our margin by item category? | Sales and cost, one domain | Yes |
| What is our stock value by location? | Stock, one domain | Yes |
Every question in the top half is a good one. None of them is exotic. All of them need two domains in one row, and that is the join the builder cannot make.
What people do instead, and what it costs
Two exports and a spreadsheet, joined on whatever identifier appears in both. It works, and it is how most of these questions actually get answered anywhere.
The cost is not the effort. It is that the answer now lives outside the system: it does not refresh, it is not governed by the field-level model, nobody can certify it, and it will be maintained by whoever built it until they stop.
And a spreadsheet join is a place where a subtle error can live for years. Joining on a name rather than an identifier, silently dropping unmatched rows, double-counting where the relationship is one-to-many — all three are ordinary and none of them announces itself.
The two legitimate ways out
The question is recurring and important
Ask for a dataset
A new curated surface joining the two domains properly, once, in code. This is a far smaller request than database access and it keeps every control.
The question is one-off
Two exports and a join
Legitimate. Join on identifiers rather than names, and check the row count before and after.
The question is really about one domain
Look again
Many cross-domain questions are one domain plus an attribute. "Margin by category" feels cross-domain and is not.
You want to join everything to everything
You want SQL
Which is a different product decision, and a different trust boundary — worth having as an explicit conversation rather than as a feature request.
The first option is the one people do not think to ask for. A dataset is a bounded, reviewable piece of work: somebody decides what the join key is, what happens to unmatched rows, and which fields are exposed — the three decisions a spreadsheet makes silently and badly.
What AWRA OpsHub does today
- A dataset registry bounding what a report can reference, with a version recorded on the definition.
- A custom report builder with selectable fields, filters, calculations and chart configuration.
- A per-field visibility model that a definition cannot circumvent.
- A certification workflow on a definition, with requester and reviewer recorded separately.
- Exports in several formats and a read API for repeatable extraction.
What it does not do
- Arbitrary joins across datasets. A report is built on one.
- SQL access or a query workbench.
- A self-service way to create a new dataset — they are defined in code.
- Any warning that a question requires a join the builder cannot make.
- A semantic layer shared with an external analysis tool.
Not ours, by choice
- One dataset per report is a deliberate design with real benefits, and we would keep it. The honest cost is a specific class of question, and this page names the class rather than implying the builder is generally weak.
- Requesting a dataset is a genuine path and not a deflection — it is bounded work with a reviewable outcome, and it is how the cross-domain questions that matter should be answered.
- Nothing here is Japanese, Korean or Hong Kong. East Asia is here because tightly-integrated supply operations make supplier-to-outcome questions the ordinary management question rather than an analytical luxury.
Two, and the first is the one to ask for
The dataset registry is the extension point. Using it is much cheaper than working around it, and almost nobody realises it is available to ask for.
A dataset joining the two domains you care about
Supplier and ticket, receipt and sale, count session and location — defined once, with the join key, the unmatched-row behaviour and the exposed fields decided deliberately. Then every existing builder feature works on top of it, including certification and the field-level model.
A warning when a question needs a join
Smaller and quietly valuable: when somebody looks for a field that lives in another dataset, say so and name the dataset rather than simply not offering it. Today the field is absent, which is indistinguishable from the field not existing.
How it works: you describe the requirement, we return a written scope, timeline and cost, and once agreed it is built into your environment and maintained as part of the product. Bring the question rather than the schema — the question is what determines the join.
Talk to us about a datasetOur position
Before building a spreadsheet that joins two exports every month, ask whether the join should be a dataset. A recurring cross-domain question deserves to be answered inside the controls once, rather than outside them forever — and the difference in effort is smaller than it looks, because somebody is already doing the join by hand.
Four questions about report construction
Can one report span two subject areas?
A good answer sounds like
A direct yes or no.
What it actually means
Ours is a no. It is the single most consequential structural fact about a report builder.
How do I get a new dataset?
A good answer sounds like
A named process.
What it actually means
If there is no answer, the surface you can report on is frozen at whatever shipped.
What happens to unmatched rows in a join?
A good answer sounds like
A stated behaviour.
What it actually means
The question a spreadsheet answers silently, usually by dropping them, usually without anyone noticing.
Which of my recurring reports are spreadsheets?
A good answer sounds like
You can list them.
What it actually means
Ask this of yourself rather than a vendor. That list is your real reporting backlog.
List the spreadsheets that join two exports
Each one is a dataset somebody has been building by hand every month. That list is the most useful thing you can bring to a conversation about reporting.
Talk about cross-domain reportingFrequently asked questions
Why not just allow joins in the builder?
Because an arbitrary join is a query, and a query needs to know the join key, the cardinality and what to do with unmatched rows. Getting those wrong produces a plausible number rather than an error, which is the worst possible failure for a reporting tool.
Are datasets versioned?
A definition records the dataset version it was built against, which is what protects a saved report from a surface that changes underneath it. That is a different thing from versioning the report definition itself, which does not exist.
Can I see which datasets are available?
Yes, when building a report — the dataset is the first choice you make, and the field list follows from it. If the one you need is not there, that is the request worth making rather than working around.
