Step-by-step guide

Build a live dashboard in Excel.

We'll build the exact Retail Analytics dashboard you can download from the benchmark page — connected live to a Tessallite model, with KPI cards, charts, and clean formatting. No coding, no SQL. If you can drag a box and type a few words, you can do this. Plan about 30–45 minutes the first time.

First, the big picture (in plain words). A model in Tessallite is like a ready-made menu of measurements (“net sales”, “orders”) and ways to slice them (“by month”, “by category”). Excel can read that menu directly. You drag the things you want onto the page, and Excel asks the model for the numbers. You never touch the raw database — the model does the hard part and keeps every number meaning the same.

What's in this guide
  1. What you need before you start
  2. Connect Excel to your model
  3. The layout map & exact cell ranges
  4. Build the data tables (PivotTables)
  5. Build the KPI cards (the big numbers)
  6. Add the charts
  7. Lay out and format the dashboard
  8. Save and refresh
  9. If something goes wrong

1What you need before you start

Three things, and then your login:

  • A published, active model. In Tessallite, open your project and make sure the Model enabled switch is green. That “turns on” the model so Excel can see it.
  • A data source connected to it. The model needs to be plugged into where the real data lives (here it's BigQuery). You can see this in the Connections panel.
  • Excel on your computer (Windows or Mac desktop). The live connection we use (called XMLA) needs desktop Excel — the browser version can't do it.
  • Your Tessallite email and password (Excel will ask for them once).

Throughout this guide we use the example model TPC-DS Retail Analytics (the “SF1” size) — the same model behind the public benchmark. Its cube is named tpcds_retail.

Tessallite model canvas with Model enabled switch on and a connected BigQuery data source
The example model is enabled (top toggle) and has a data source (TPC-DS BigQuery) in the Connections panel. That's the “ready to use” state you need.
Why this matters: if the model is switched off, or has no data source, Excel will connect but see nothing — like opening a fridge that's unplugged. Green switch + a data source = good to go.

2Connect Excel to your model

First, get the connection details from Tessallite. Open your project and click the Endpoints panel, then open XMLA / DAX and pick the Excel tab. Tessallite shows you the exact steps and the address to use.

Tessallite Endpoints panel showing the XMLA connection string and the Excel connection steps
The Endpoints → XMLA / DAX → Excel tab gives you the server address, the catalog (your tenant), and the model/cube name. Keep this open while you work in Excel.

Now in Excel, follow these steps:

  1. Go to Data → Get Data → From Database → From Analysis Services.
  2. Server name: paste the XMLA address from the Endpoints panel (for the demo it is https://sql.cloud.tessallite.io:8080/api/v1/xmla/<your-tenant>). Click Next.
  3. Log on credentials: choose “Use the following user name and password”, then type your Tessallite email and password. Click Next.
  4. Select the database (catalog): pick your tenant (the demo is acme-demo).
  5. Select the cube: tick Connect to a specific cube and choose tpcds_retail. Click Next, then Finish.
Excel Data Connection Wizard selecting the tpcds_retail database and cube
Step 4–5: pick your catalog, tick “Connect to a specific cube”, and select tpcds_retail.

Excel then asks how to drop the data in. Choose PivotTable Report and click OK.

Excel Import Data dialog with PivotTable Report selected
Pick PivotTable Report — this gives you a live table you can build on. (You can place it on a new sheet.)

You're connected. On the right you'll see the PivotTable Fields list — this is the model's menu.

Reading the menu: the items under the little Σ (sigma) are measures — the numbers, like net_sales, orders, quantity. The folders below (like month, item_category, quarter) are dimensions — the ways to slice those numbers.
Excel with the PivotTable Fields list showing the model measures and dimensions next to a finished dashboard
The finished dashboard (left) and the model's field list (right). Everything you'll build comes from this menu.

★The layout map & exact cell ranges

Before building, here is the finished dashboard as a map. Columns run A–P. Build each block to the ranges shown and your workbook will match the download exactly. Keep this picture beside you for the rest of the guide.

Dashboard sheet — what goes where

Retail Analytics Dashboard title row — A1:P1
NET SALES label A3 · value A4
GROSS SALES E3 · E4
UNITS SOLD I3 · I4
AVG TICKET M3 · M4
ORDERS A6 · A7
ACTIVE CUSTOMERS E6 · E7
REV / CUSTOMER I6 · I7
DISCOUNT RATE M6 · M7
Monthly Sales — line A10:H23
Category Sales — column H10:P23
Net Sales by Quarter — bar A23:H36
Top Product Classes — bar H23:P36

Eight KPI cards in two rows (rows 3–4 and 6–7), then a 2×2 grid of charts (rows 10–36).

Data sheet — the four PivotTables, side by side

By Month pivot at B4 · B4:D17
By Category F4 · F4:H16
By Quarter J4 · J4:L10
By Product Class N4 · N4:P104

Title in A1, a label over each block in row 3. The dashboard's charts read these pivots via GETPIVOTDATA.

Every range in one place

BlockCell / range (Dashboard sheet)
TitleA1 (merged across A1:L1)
KPI cards — row 1A4, E4, I4, M4 (labels in row 3)
KPI cards — row 2A7, E7, I7, M7 (labels in row 6)
Chart — MonthlyA10:H23
Chart — CategoryH10:P23
Chart — QuarterA23:H36
Chart — Product ClassH23:P36
Backing table — MonthlyA41:I53 (months in rows 42–53)
Backing table — CategoryK41:T47
Backing table — QuarterK56:Q60
Backing table — Product ClassA64:H70 (chart block R64:T70)
Model-map legendK64:P70
PivotTables (Data sheet)Data!B4 · Data!F4 · Data!J4 · Data!N4

3Build the data tables (PivotTables)

A dashboard is just a few small tables turned into charts. We'll make one small PivotTable per slice on a single helper sheet (call it Data). Each one is a single, fast question to the model.

For your first table — sales by month:

  1. Click inside the PivotTable. In the field list, drag month into the Rows box.
  2. Drag net_sales into the Values box, then drag gross_sales in too.

That's it — you now have 12 months with two numbers each, pulled live from the model. Repeat for the other slices (each is a brand-new PivotTable: Insert → PivotTable → From this workbook's connection):

TableDrag to RowsDrag to Values
By monthmonthnet_sales, gross_sales
By categoryitem_categorynet_sales, gross_sales
By quarterquarternet_sales, gross_sales
By product classitem_classnet_sales, gross_sales

Line them up side by side on the Data sheet and write a small label over each (“By Month”, “By Category”, and so on) so the sheet looks tidy, not like leftovers.

Why it's fast: the model keeps small pre-computed summaries (“aggregates”). Each of these tables reads a few hundred bytes instead of scanning the whole dataset — so refreshes are quick and cheap.

4Build the KPI cards (the big numbers)

The big headline numbers at the top (Net Sales, Orders, Customers…) are single values. For those we use a small formula called CUBEVALUE — it asks the model for exactly one number.

Think of CUBEVALUE like asking one question: “Hey model — what's the total net sales?” and it answers with one number you can put in a cell.

First, find your connection's name: Data → Queries & Connections. Say it's tpcds_retail. Then a KPI cell looks like this (the /1000000 just turns pounds into millions so it reads “£4.7” not a huge number):

=CUBEVALUE("tpcds_retail","[Measures].[net_sales]")/1000000

Here are the eight cards and their formulas (swap in your connection name):

CardFormula
Net Sales=CUBEVALUE("tpcds_retail","[Measures].[net_sales]")/1000000
Gross Sales=CUBEVALUE("tpcds_retail","[Measures].[gross_sales]")/1000000
Units Sold=CUBEVALUE("tpcds_retail","[Measures].[quantity]")/1000000
Avg Ticket=CUBEVALUE("tpcds_retail","[Measures].[average_ticket]")
Orders=CUBEVALUE("tpcds_retail","[Measures].[order_count]")/1000
Active Customers=CUBEVALUE("tpcds_retail","[Measures].[customer_count]")/1000
Rev / Customer=CUBEVALUE("tpcds_retail","[Measures].[revenue_per_customer]")
Discount Rate=CUBEVALUE("tpcds_retail","[Measures].[discount_amount]")/CUBEVALUE("tpcds_retail","[Measures].[gross_sales]")
Shortcut: type one formula, get it right, then copy the cell and just change the measure name in the quotes. The measure names are exactly what you see in the field list.

5Add the charts

Charts come straight from the little tables you built in Step 3.

  1. Click your By Month table, then Insert → Chart and pick a Line or Column chart.
  2. Do the same for the others: By Category → column, By Product Class → bar, By Quarter → column.
  3. Give each chart a clear title (“Monthly Sales”, “Category Sales”, …).
Want the charts on a clean page, away from the tables? Keep the PivotTables on the Data sheet, and on your Dashboard sheet pull the values across with GETPIVOTDATA (Excel writes this for you if you click a pivot cell with =). Then chart those cells. That's how the downloadable file is built — a tidy dashboard up front, the pivots tucked on the Data sheet.

6Lay out and format the dashboard

This is what turns a few tables into something you'd proudly send. Take your time here.

KPI cards

  • Put the eight numbers in a row of “cards” at the top. Merge a couple of cells for each, add a soft background colour, a small grey label above, and a big bold number below.
  • Set the number look with Format Cells → Custom:
    Looks likeCustom format code
    £4.7bn"£"0.0,"bn"
    £508.0m"£"0.0"m"
    138.9M (units)#,##0.0"M"
    240k#,##0"k"
    9.4%0.0%
    £187"£"0

Charts & colours

  • Arrange the charts in a neat 2×2 grid under the cards.
  • Use one or two brand colours (a green and a soft grey work well), remove busy gridlines, and keep titles short.
  • View → uncheck Gridlines on the dashboard sheet for a clean, print-ready look.

Tidy the Data sheet

  • Keep all the PivotTables together on the Data sheet with a title row (“Live model data — do not edit”) and a label over each block.
  • If you don't want viewers poking at it, right-click the sheet tab → Hide.

7Save and refresh

  • Save as a normal .xlsx — no macros needed.
  • To pull fresh numbers any time: Data → Refresh All.
  • Refreshing talks to the live model, so it needs the connection and your credentials. If you send the file to someone without a Tessallite login, it still opens and shows the last saved numbers — they just can't refresh.
Download the finished workbook See the benchmark

Tip: open the finished workbook and click around — seeing the pieces in place makes every step above click into focus.

8If something goes wrong

  • Refresh spins forever. Turn off background refresh: Data → Queries & Connections → right-click the connection → Properties → untick Enable background refresh. Then refresh again.
  • Cells show #N/A. The connection isn't live or a name is mis-typed. Re-check the measure name in the field list, and make sure you can connect (Step 2).
  • “Can't find the cube.” The model probably isn't enabled, or you picked the wrong catalog. Go back to Step 1 and check the green Model enabled switch.
  • It asks for a password every time. That's normal for a live file — the password isn't saved inside the file for safety.
Remember: you never edit the database. Everything — the measures, the slices, the security — comes from the one governed model, so the same number means the same thing in Excel, Power BI, and everywhere else.