Finance teams across industries share a version of the same operational challenge. Revenue numbers sit in one place. Margin data in another. Expense ratios somewhere else entirely. Every Monday morning, someone spends two to three hours extracting from multiple sources, reconciling figures in a spreadsheet, and distributing a report that is already outdated before anyone reads it.
We set out to solve this — not with a workaround, but with a properly architected solution. The goal was a live Financial Intelligence Dashboard that required no SQL knowledge to use, answered direct analytical questions in plain language, and gave every member of the finance team access to current, trustworthy numbers at any time.
The solution is built on Databricks, powered by a structured Gold data layer, and integrated with Genie — a natural language AI assistant that responds to questions like “What region had the lowest margin last quarter?”, “What caused the Q3 revenue decline?”, and “Is our expense ratio improving or deteriorating?”
The Problem We Were Solving
The finance team was responsible for tracking three categories of information every quarter: revenue by region and product, gross profit and margin percentage, and expense ratio — the proportion of each revenue dollar consumed by the cost of goods.
The data existed. That was never the issue. The issue was access. Every time a member of the finance team needed to answer a question — whether one of those three core metrics or something adjacent — they either had to write their own SQL query or wait for a data analyst to write one for them. That wait was typically measured in days, not hours.
What the team needed was a solution that anyone could open in a browser, see all relevant numbers in real time, and answer their own follow-up questions without technical assistance and without waiting.
What We Used
- Dataset — TPC-H. A supply chain benchmark dataset pre-loaded in every Databricks workspace under
samples.tpch. It models a global supply chain business with orders, customers, regions, products, and suppliers. - Storage — Unity Catalog. Data is stored in
dbw_pixel_demo.financials_domain, with clean governance, namespace organisation, and a reliable single source of truth. - Gold Layer — Six SQL views sit on top of the TPC-H raw tables, pre-calculating all KPIs so that the dashboard and Genie always read from consistent, pre-computed values.
- Dashboard — Databricks AI/BI. Built using the native drag-and-drop interface, entirely within the Databricks environment.
- AI Layer — Genie. Finance users type questions in plain English and receive accurate, well-routed answers without writing SQL.
How the Architecture Works
Layer 1 — Raw Data (TPC-H)
Eight tables form the source of truth: Orders, Lineitem, Customer, Nation, Region, Part, Supplier, and Partsupp. These are not queried directly on the dashboard. The join operation is multi-table and complex, and column names are not friendly enough for non-technical users. This is the foundation layer; Gold views handle all translation into business-ready KPIs.
Layer 2 — Gold Views (KPI Definitions)
Six SQL views handle all the analytical heavy lifting:
| View Name | What It Computes |
|---|---|
| kpi_executive_summary | Quarterly headline numbers: Revenue, Profit, Margin %, Expense Ratio, Orders, Average Order Value |
| revenue_by_region_time | Revenue by region, nation, and time period |
| gross_profit_by_category | Profitability by material, manufacturer, and size tier |
| expense_ratio_trend | Expense efficiency metrics over time, segmented by region |
| qoq_revenue_growth | Quarter-over-quarter and year-over-year revenue growth with growth accelerators |
| margin_drill_down | Bottom-up line-item detail for root cause analysis, including late shipment flags |
Layer 3 — Dashboard and Genie
The dashboard reads from the Gold views. Genie reads from the same Gold views. Both surfaces always show the same numbers, calculated in the same way. There is no possibility of discrepancy between a chart and an AI-generated answer because both originate from the same definitions.
The Dashboard Layout
- Row 1 — Title and description. What the dashboard covers, what data it draws on, and a one-line instruction for using the filters.
- Row 2 — Four filters. Year, Quarter, Region, and Product Material. Every chart and KPI tile updates instantly when any filter is changed.
- Row 3 — Six KPI tiles. Net Revenue, Gross Profit, Gross Margin %, Expense Ratio %, Total Orders, Average Order Value. Each tile displays a current filtered value and a directional indicator relative to the previous quarter.
- Rows 4–5 — Four charts. Revenue by region (bar), revenue quarterly trend (line), gross margin by product material (horizontal bar), and expense ratio over time (line). Regional colour scheme is consistent across all charts — Asia is always blue, Europe always teal, America always amber, Africa always coral, Middle East always purple.
- Row 6 — QoQ growth chart. A full-width bar chart displaying quarter-over-quarter revenue growth percentage by region, with a reference line at zero.
- Row 7 — Margin drill-down table. Line-item level detail sorted by lowest margin first, enabling finance users to pinpoint exactly which orders, regions, suppliers, and products are compressing margin.
The entire dashboard is live. No refresh button. No export to spreadsheet. Opening it means seeing current numbers.
How We Configured Genie for Finance
Genie is connected to all six Gold views, all designated as Trusted Assets. We then wrote a set of business instructions that teach Genie how to interpret financial language and route questions to the correct view.
Key routing instructions:
- “Revenue” means net revenue — not gross revenue, not net extended price.
- “Margin” means gross margin percentage.
- For trend questions, always display at least four quarters of history.
- For comparison questions, always rank all five regions.
- Never use SQL column names in responses — answer in business language only.
- Revenue questions route to revenue_by_region_time.
- Margin and product questions route to gross_profit_by_category.
- Root cause and “why” questions route to margin_drill_down.
- Headline KPI questions route to kpi_executive_summary.
“Give me a financial summary for last quarter”
Returns Net Revenue, Gross Profit, Gross Margin %, Expense Ratio %, and Total Orders for the most recent completed quarter — in plain English, with no analyst involved.
“What region had the lowest margin last quarter?”
Returns all five regions ranked by gross margin percentage, highlighting the lowest performer with supporting revenue and expense data.
“Is our expense ratio improving or deteriorating?”
Returns four quarters of expense ratio trends with directional indicators showing improvement or deterioration by region.
“What caused the Q3 revenue decline?”
Routes to the margin_drill_down view and returns line-item detail showing which products, regions, and suppliers contributed to the decline.
Four Things We Learned
1. The Gold Layer Is the Most Important Architectural Decision
It is not hard to make a dashboard look attractive. The hard part is making the numbers right. If the definitions of KPIs are inconsistent — “revenue” defined slightly differently in one place than in another — the users will not trust the dashboard within a week. Defining all KPIs once, in one SQL query, and having all downstream queries consume from the same source is what separates a good dashboard from a bad one.
2. Genie Instructions Are Not Optional
Genie is capable out of the box, but it is generic. It does not know that a particular organisation uses net revenue as its default revenue definition, or that “last quarter” should refer to the most recently completed quarter in the dataset. The business instructions transform a general-purpose AI into a financial intelligence assistant. Writing them carefully is the highest-value hour in the entire project.
3. Non-Technical Users Need Starting Points, Not Blank Interfaces
We anticipated that finance users would value an open-ended question interface. What we observed was that they engaged far more readily with pre-built sample questions than with an empty input box. The sample questions we added to Genie became the most-used feature on the entire dashboard. Any deployment should include at least fifteen to twenty sample questions before being shared with end users.
4. Proactive Analysis Emerges When Data Is Always Available
The finance team had been operating reactively — waiting for the weekly report, analysing it after the fact. With live, always-current data, their behaviour changed. They began spotting trends without waiting for reports. One team member noticed that late shipments from the Middle East correlated with a 3.2 percentage point reduction in line-item margin. This would never have been caught in a snapshot report. The biggest change was the shift from reactive to proactive.
How to Build This Yourself
- Load the data. In Databricks SQL Editor, run
CREATE TABLE AS SELECTfromsamples.tpchfor all eight tables into your own catalog and schema. - Create the Gold schema and views. Create a schema called
finance_goldinside your catalog, then build the six view definitions. Each is a standard SQL SELECT statement — no complex transformations, no user-defined functions. - Build the dashboard. In Databricks One, create a new dashboard, connect it to your SQL warehouse, add each Gold view as a dataset, and construct the widgets using the drag-and-drop canvas.
- Configure Genie. Add all six Gold views as Trusted Assets, paste the business instructions, add sample questions, and test at least five questions before publishing.
- Publish and share. Set auto-refresh to hourly, configure view permissions for the finance team, and share the URL.
The only step that requires SQL expertise is Step 2. Every other step is configuration within the Databricks interface. Total setup time: approximately three to four hours.
What Changed for the Finance Team
Before this dashboard, preparing the weekly Monday morning report consumed two to three hours. Extract data from multiple sources, reconcile figures in a spreadsheet, cross-check numbers, write the summary, distribute it. By the time recipients read it, the underlying numbers had already moved.
That process no longer exists. The numbers are never stale. Any member of the finance team can open the dashboard in under ten seconds and see current quarter KPIs. Follow-up questions go to Genie.
The time saving is real, but it is not the most significant outcome. The more important shift is from reactive to proactive analysis. The team no longer waits to receive information — they go looking for it. And because the data is always available and always current, they find things they would never have found in a weekly report cycle.
Final Thought
The combination of a well-structured data layer, a clean dashboard, and a natural language interface is genuinely powerful — not because any individual component is novel, but because Databricks has integrated all three into a single workflow that non-technical users can operate without training, documentation, or ongoing support from a data team.
What we built here is a pattern: define your KPIs once in a Gold layer, surface them through a live dashboard, and connect a natural language assistant that understands how your business speaks about its data. That pattern works for finance. It works for sales. It works for marketing. And it scales cleanly as the organisation's analytical needs grow.
We have done this for finance. We are ready to do it for you.