Skip to content
sandadocs
Console Start free trial

Connect Excel

Load your semantic fluid into Excel as live tables with Data, Get Data, From OData Feed. Use a Basic sign-in with an integration token, then refresh.

View as Markdown

Excel reads sanda's feed with its built-in OData connector. You paste one address and a token, tick the tables you want and load them. Refreshing re-reads your warehouse.

The menu names below are from Excel for Microsoft 365 on Windows and may differ in other versions. The address and the token are the same everywhere.

Before you start

  • An integration token. An owner or admin creates one under Settings · Integrations. A feed needs none of the optional switches. Copy the token when it is shown, because sanda cannot show it again. If you are not an owner or admin, ask one to create it for you. See agent tokens.
  • Something on the map. The feed offers the tables on your semantic fluid. If the map is empty, there is nothing to load.

Connect

  1. Open the OData feed connector. In Excel, go to Data · Get Data · From Other Sources · From OData Feed.

  2. Paste sanda's address. Paste this exactly, including the trailing slash, and press OK:

    Text
    https://console.sanda-os.com.au/api/odata/

  3. Choose Basic. Excel asks how to sign in. Choose Basic.

  4. Enter the token. Paste the token into Password. sanda ignores the user name, so leave it empty. If your version of Excel insists on a value, type any text. If Excel asks which level to apply the sign-in to, leave it on the sanda address. Press Connect.

  5. Choose tables. The Navigator lists everything on your map. Each table appears twice where it has metrics: its rows (invoices) and its summary (invoices_summary). Tick one and press Load, or press Transform Data to shape it first.

The table lands on a worksheet as an Excel table. Numbers arrive as numbers and dates as dates, because Excel reads the column types from the feed.

Which table to pick

  • The summary (..._summary) holds your metrics grouped by your dimensions. It is the one to use for numbers: net revenue there is the definition your business agreed on, and it is small and quick to refresh.
  • The rows hold every column of the table. Use them for detail, and narrow them first (below), because a large table takes a while.

Ignore the _row column. It is sanda's row number for that read, and it is not stable from one refresh to the next.

Narrow what you load

Choosing fewer columns and fewer rows makes a refresh cheaper as well as smaller, because sanda does the narrowing in the warehouse and not after the rows arrive.

In the Power Query editor (Transform Data), remove the columns you do not need and filter the rows. Power Query sends what it can to sanda as $select and $filter. A few rules follow from what sanda accepts:

  • Filters combine with "and". A filter that needs "or" or "not" comes back as an error. Split it into separate steps, or filter after loading.
  • Filter dates with "on or after" and "before". sanda compares dates as a window that includes its first day and excludes its end, so a month filter never loses its last day. A comparison that means "after" or "on or before" is refused with a message saying which to use.
  • Filter on a dimension, not a metric. A metric is worked out from the rows a query reads, so it cannot decide which rows are read.

The complete list of what sanda accepts is in the feed reference.

Refresh

  • Once: choose Data · Refresh All.
  • On a schedule: choose Data · Queries & Connections, right-click the query, choose Properties, and set Refresh every and Refresh data when opening the file.

Every refresh is a live read of your warehouse. It is billed to your workspace like any other query, so a workbook that refreshes every five minutes is a query every five minutes, indefinitely. Pick a schedule somebody will actually look at. See usage.

When a table has more than 1,000 rows, Excel follows sanda's links to the next pages on its own. A 40,000-row table takes a little longer, and nothing more is needed. sanda pages up to 1,000,000 rows into a table. Past that, filter the table or build a view under Data · Modelling. See views.

Change or clear the saved token

Excel keeps the credential in its data source settings on your computer. To swap in a new token after you rotate one, or to be asked again:

  1. Open the settings. Go to Data · Get Data · Data Source Settings.
  2. Choose the sanda address in the list.
  3. Edit or clear. Press Edit Permissions and then Edit beside the credentials to enter a new token, or press Clear Permissions and Excel will ask for a credential again on the next refresh.

When a token is revoked or expires, the next refresh fails with a sign-in error. sanda answers a missing or wrong token with a 401.

If something goes wrong

Excel shows sanda's own message when it can. These are the common ones.

What Excel shows What it means What to do
A sign-in dialog or a credentials error The token is wrong, revoked or expired Create a new token and update the saved one
"That token cannot run queries, so it cannot read a feed" The token lacks the permission to ask questions, which every token normally has Create a new token
"This workspace has nothing on its semantic map yet" No tables are on the map Import a table under Intelligence · Semantic fluid
"This feed has no table called ..." The name is not one sanda offers Open the Navigator and choose from the list. The message names the tables it has
"sanda can only combine filters with “and”" A filter used “or” Rewrite it without “or”
"... looks like it holds a secret, so sanda won't select it" The table has a column whose name looks like a password or key, and sanda never reads those Rename the column if the name is wrong, or build a view that leaves it out
"Reading ... took longer than sanda will wait" The read ran past 30 seconds Filter it, or remove columns
"sanda could not read ... just now" A temporary fault reading the warehouse. Nothing was lost Refresh again
"This workspace is suspended" The workspace is suspended, for example because a trial ended An owner can check Settings · Plan & budget, or ask sanda support

More help is in troubleshooting.

Next steps

Something unclear or out of date? Tell us, and we will fix the page.