Plexara
What you will take away from this lesson
The supplier’s November quote is a table and the agent knows what its columns mean. This lesson does the work the file was uploaded for: join it to the product catalog and a month of sales, read what the join says, and put the result in front of the person who decides prices.
Every figure below comes from the demo deployment’s warehouse and the sheet registered in 502. The join is three statements. The dashboard is one call.
Learning Objectives
- 01Write the join with the cast every registered column needs, and know which side of the join needs it.
- 02Produce figures the business cares about from the join: cost change by category, the SKUs whose margin falls under a threshold at the current price, and the SKUs the sheet does not cover.
- 03Turn the result into a dashboard asset with provenance, and edit it in place with anchored patches instead of regenerating it.
- 04Share it with the people who need it: a person by email with a note, a link, or a collection.
- 05Choose between querying a registered table and having a dashboard reference the file directly, which re-reads it on every open.
Where this lesson sits
This is the fourth lesson of the 500 series and the one where the file earns its place. The table exists and the agent knows what it means; what follows is the join, the reading, and the dashboard.
500 Series: Spreadsheets as Tables
- 501The last mile of data is a spreadsheetThe join was never the expensive part; loading was, and loading was staffed. Why a chat agent does not close the gap on its own, and what a table over the file where it sits changes.
- 502Uploading a file and registering it as a tableThe resource library, the Excel rule, the Query as a table panel, what a registration answers with, the Scratch Tables page, and the three things a file is refused for.
- 503Teaching the agent what the file meansUnits, grain, effective dating, the join key, and what a missing row means: captured once, promoted to a knowledge page, recalled by every teammate’s agent.
- 504Joining, visualizing, and sharingThe join with the cast, a supplier quote against a month of sales, a dashboard asset with provenance, and when a dashboard should reference the file instead of querying it.
- 505Next month’s fileA new revision leaves the table behind; register again to move it forward. Unregistering, deleting, the monthly procedure as a prompt, and where a join stops being enough.
The 500 series assumes the asset mechanics from the 300 series and the knowledge loop from 206. It ends where the 600 series begins: the point at which a monthly file needs more than a join.
The cast rule
Every column of a registered table arrives as text. That is the storage format’s rule for a CSV, not a choice, and it means a comparison to a typed warehouse column has to cast one side. Which side depends on the column: a text key joins to a text key with no cast at all, and a number the file carries as text is cast where the arithmetic happens.
Every registered column is text; the join decides where the cast goes
| Column | The two sides | Rule |
|---|---|---|
| sku | products.sku is varchar(20); the sheet’s sku is text. | No cast. Both sides are text and the values match exactly (SKU-000001). |
| unit_cost | products.cost and products.price are decimal(10,2); the sheet’s unit_cost is text. | CAST(s.unit_cost AS DECIMAL(10,2)) before any arithmetic or comparison. |
| moq, lead_time_days | Integers in meaning; text in the table. | CAST(... AS INTEGER) when they are compared or summed. |
| effective_date | A date in meaning; text in the table. | CAST(... AS DATE) when it is compared to a date column; as text it still groups and filters. |
The registration answer ends with a sample statement showing the pattern: ON w.id = CAST(t."sku" AS BIGINT). It guesses the first column is the key. For this file the key is text on both sides, so the cast moves to the cost column instead. The knowledge page from 503 says so, which is why the next analyst’s agent gets it right on the first statement.
The worked join
The question the sheet exists to answer is what the supplier’s new quote does to margin at the current shelf price. Three statements answer it: a category rollup weighted by November units, a per-SKU list filtered to the products whose projected margin falls below a threshold, and a coverage count that uses a LEFT JOIN to find the products the sheet does not quote.
The first statement is shown in full. The scratch table sits in the FROM clause beside three warehouse tables, and the only thing that marks it as an uploaded file is its name.
Cost change and projected margin by category, weighted by November units
WITH nov AS (
SELECT ti.product_id, SUM(ti.quantity) AS units
FROM warehouse.public.transaction_items ti
JOIN warehouse.public.transactions t ON t.transaction_id = ti.transaction_id
WHERE t.transaction_date >= TIMESTAMP '2025-11-01'
AND t.transaction_date < TIMESTAMP '2025-12-01'
GROUP BY ti.product_id
)
SELECT c.category_name AS category,
ROUND(100.0 * (SUM(n.units * CAST(s.unit_cost AS DECIMAL(10,2))) - SUM(n.units * p.cost))
/ SUM(n.units * p.cost), 1) AS cost_change_pct,
ROUND(100.0 * (SUM(n.units * p.price) - SUM(n.units * CAST(s.unit_cost AS DECIMAL(10,2))))
/ SUM(n.units * p.price), 1) AS margin_projected_pct
FROM scratch.uploads.admin_supplier_price_sheet s
JOIN warehouse.public.products p ON p.sku = s.sku
JOIN warehouse.public.categories c ON c.category_id = p.category_id
JOIN nov n ON n.product_id = p.product_id
GROUP BY c.category_name
ORDER BY cost_change_pct DESCThe scratch table sits in the same statement as three warehouse tables. Weighting by units sold is what makes a category figure mean something: a 15% increase on a product nobody buys is not the same as 15% on the one that sells every day. The second and third statements of the session used the same skeleton: a per-SKU version with a margin filter, and a coverage count with a LEFT JOIN from products to the sheet.
What the join said
The three results, as returned. The coverage figures frame the rest: 400 of the 450 products in scope are quoted, and the 50 that are not sold about eleven percent of the month’s units, so any category figure has to be read as a figure about the quoted range.
Three categories carry a double-digit cost increase, and one of them, Flowers & Plants, would drop from a 29.9% margin to 19.1% if the quote were accepted at the current price. The per-SKU list is where the analyst’s reading starts, because a margin filter surfaces two different problems side by side.
Coverage of the quote, November 2025
- Quoted
- 400 of 450
- 50 SKUs in scope are not carried by this supplier. They sold 3,279 of 29,605 November units.
- Direction
- 223 up, 163 down
- Against the cost of record. 14 quoted SKUs are unchanged.
- Catalog issue
- 12 SKUs
- carry a cost of record above their shelf price before any quote. That is a data-quality issue in the catalog, reported separately.
Weighted cost change by category, largest movers of 50
| Category | Cost change | Margin now | Margin projected |
|---|---|---|---|
| Flowers & Plants | +15.4% | 29.9% | 19.1% |
| Beer | +13.1% | 35.9% | 27.5% |
| Baking Supplies | +12.6% | 41.1% | 33.7% |
| Organic & Natural | +3.1% | 30.7% | 28.5% |
| Batteries & Electronics | +2.9% | 29.3% | 27.3% |
| Garden Supplies | -1.6% | 43.9% | 44.8% |
| Wine | -3.2% | 27.2% | 29.5% |
Quoted SKUs below a 20% projected margin, by monthly cost impact
| SKU | Category | Price | Cost of record | Quoted | Projected margin | Monthly impact |
|---|---|---|---|---|---|---|
| SKU-000237 | Beer | 39.31 | 31.03 | 34.74 | 11.6% | $222.60 |
| SKU-000290 | Beverages | 77.43 | 61.85 | 65.53 | 15.4% | $209.76 |
| SKU-000172 | Beer | 28.93 | 22.35 | 25.07 | 13.3% | $138.72 |
| SKU-000384 | Clothing Basics | 52.45 | 40.89 | 42.39 | 19.2% | $115.50 |
| SKU-000212 | Organic & Natural | 78.89 | 106.55 | 109.58 | -38.9% | $112.11 |
| SKU-000256 | Personal Care | 61.92 | 84.77 | 86.30 | -39.4% | $102.51 |
| SKU-000368 | School Supplies | 67.49 | 89.91 | 91.17 | -35.1% | $90.72 |
| SKU-000012 | Baby Care | 38.59 | 31.65 | 33.34 | 13.6% | $86.19 |
| SKU-000059 | Baby Food | 29.68 | 25.19 | 26.37 | 11.2% | $86.14 |
| SKU-000175 | Diapers & Wipes | 64.40 | 87.48 | 88.98 | -38.2% | $78.00 |
| SKU-000219 | Baking Supplies | 6.76 | 5.50 | 6.57 | 2.8% | $70.62 |
| SKU-000244 | Kitchen Supplies | 49.13 | 39.87 | 41.18 | 16.2% | $66.81 |
Monthly impact is November units multiplied by the cost delta. Four of the twelve (212, 256, 368, 175) were already priced below their cost of record before the quote arrived; the join surfaces them because a margin filter does not care why the margin is negative. Separating a supplier change from a catalog error is the analyst’s reading, and it is the first thing the dashboard says.
From rows to a dashboard
The agent saves the result as an HTML asset in one call, naming the three queries as its sources. The dashboard is a document in the portal from that moment: versioned, viewable, shareable, and stored in the deployment’s own bucket. When the first version of the chart needed fixing, the fix was three anchored patches, not a regeneration.
From rows to a dashboard asset
What the agent calls. This is the exchange the agent has with Plexara on your behalf, shown for the technical reader. You ask in plain language; you never type any of it.
save_asset name "Blue Harbor quote vs cost of record, November 2025" content_type text/html sources ["e-16onSTB…", "vJ_Qhm05h…", "DU0lBGGLv…"] content <!doctype html> ... -> asset_id d76092e83675e6828067dd1c81bb7d4f provenance_captured true calls_recorded 3 manage_asset action=patch asset_id=d76092e8... (versions 2, 3, 4) change_summary "Rename the chart script's top-level constant ..." change_summary "Place the value label of a decreasing category ..." change_summary "Draw each category label after its bar ..."
The three source call ids are the three statements of the session, so the asset’s provenance names exactly the queries it was built from. The three patches that followed fixed the chart script; each one is an anchored edit with a change summary, recorded as a new version, so the dashboard was never regenerated from scratch. Building and editing assets is the 300 series; here the point is that a registered table feeds it like any warehouse table.
The dashboard
The saved asset, as the merchandising lead opens it. The bar chart states the category result, the table states the per-SKU result, and the note under the table separates the supplier change from the catalog data-quality issue.
Sharing from the portal
The same share is available on the asset’s page, with the note, the permission, and the notify toggle in one dialog.
Reference or query
There is a second way a dashboard can use the file, and it behaves differently enough to choose deliberately. Instead of holding numbers a query produced, a dashboard can name the file by its URI and read it on every open. The file’s page then lists the dashboard under Used by. Which pattern fits depends on whether a version should be a snapshot.
Two ways a dashboard can use the file
Query the registered table
- What moves when the file changes
- Nothing, on its own. The dashboard holds the numbers the join produced when it was saved.
- Is a version a snapshot
- Yes. Every version is an as-of record; an old version still shows the figures it showed.
- Use it for
- Analysis, a monthly review, anything somebody will compare against later.
Reference the file from the dashboard
- What moves when the file changes
- The numbers. The dashboard names the file by its mcp:// URI and re-reads it on every open; the file’s page lists the dashboard under Used by.
- Is a version a snapshot
- No. An old version of a referencing dashboard shows today’s file, because the reference belongs to the asset, not to a version.
- Use it for
- A status board that should always show the current sheet, with no run in between.
The November dashboard is the first kind on purpose: a price review is something the merchandising lead will hold up against December’s. The second kind is the mechanism the 600 series builds on, where a script refreshes the file and every dashboard naming it shows the new numbers without being re-saved.
Used by, on the file’s page
A referencing dashboard shows up on the file it references, which is how a person deleting or replacing the file can see what depends on it.
Where this leads
The November review is saved, shared, and cited. The remaining question is what happens in four weeks.
Key terms
Five terms from this lesson: the cast, the provenance an asset carries, the patch that edits it, the share that delivers it, and the reference that keeps a dashboard live.
Key Terms
- CastCAST(s.unit_cost AS DECIMAL(10,2))
- The conversion a registered column needs before it is compared to a typed warehouse column. Every registered column is text; the join decides which side carries the cast.
- Provenance
- The record of which calls an asset was built from. save_asset captures it from the call ids you name in sources, or from every data call since your last save when you name none.
- Anchored patchmanage_asset action=patch
- An edit to part of an asset anchored on text, never on a line number, recorded as a new version with a change summary. The way a dashboard is corrected without being regenerated.
- Share
- Access to an asset for a person (a real email with your note), for any signed-in user (a link), or for anyone holding a URL (a public link with an expiry). Covered in 303.
- Asset referencemcp://
- A file named from an asset’s content by its URI instead of carried in it. Resolves to the file’s current content on every open, so a referencing dashboard is live and its versions are not snapshots.