Excel and Power BI (OData)

Your semantic fluid is also a live data feed that Excel, Power BI and Power Query read without an add-in, a driver or a database connection. You paste an address and a token, choose the tables you want, and they arrive as refreshable tables in the words your business agreed on, not in the warehouse's column names.

The feed uses OData, an open standard that Excel and Power BI already understand. It is on every edition.

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

The trailing slash matters to some clients.

How it works

Nothing is installed, and the feed stores no copy of your data. Each refresh reads your warehouse live.

What the feed offers

For each table on your semantic map, the feed offers up to two tables:

For example, a workspace with an invoices table, a region dimension, an issue date dimension and a net revenue metric offers invoices with the columns issue_date, region and the rest, and invoices_summary with region, issue_date and net_revenue. The names here are examples. Yours come from your own fluid.

Use the summary tables for numbers. net revenue in a summary is the definition your business signed off, composed by the same engine that answers it in sanda's own workbench. It is not a SUM somebody wrote in a cell and nobody reviewed. Use the rows table when you need the detail.

A few things to know about the tables:

• Names are folded into identifiers a URL can carry. order details is order_details, and on-time delivery % is on_time_delivery. Two names that fold to the same identifier both stay reachable, and the second gets a numeric suffix.
• A column with a dimension takes the dimension's name. Your header row reads region and issue date, not cust_ctry_cd and issued_at.
• Only what is on the map. A table that is not on the semantic map is not in the feed. A view you build under Data · Modelling and put on the map appears as a table like any other. See views.
• Every row has a _row column. It is the row's position in that read, and OData needs every row to have a key. It is not a business key and it does not survive a refresh. Ignore it, or remove the column in Power Query.

If your map is empty, the feed has no tables. Import a table under Intelligence · Semantic fluid and it appears.

Signing in

An integration token is the only credential a feed needs, and it needs no special switches.

• Excel: choose Basic, and paste the token as the password. The user name is ignored.
• Power BI: the same Basic credential.
• A script: send Authorization: Bearer bat_....

The token identifies the workspace, not the person holding it. An owner or admin creates it and can revoke it at any time under Settings · Integrations. Details are in the feed reference.

Refreshing and what it costs

Every refresh is a live read of your warehouse, through the same read-only role that answers questions. It is billed to your workspace like any other query, and it is subject to the same compute budget. A workbook set to refresh every five minutes is a query every five minutes, indefinitely, so choose a schedule somebody will actually look at. See usage.

A page is 1,000 rows. When a table has more, sanda hands the client a link to the next page, and Excel and Power BI follow it without being asked, so a 40,000-row table simply takes a moment.

What the feed does not do

The feed is read only and deliberately small.

• It cannot write. Anything other than a read is refused.
• or and not in a filter are refused. sanda combines conditions with and only, and says so when a filter asks for more.
• $count, $expand, $apply and $search are refused by name. A filter that was silently ignored would hand you a bigger number than you asked for, so nothing is dropped quietly.
• A metric cannot be filtered on. It is worked out from the rows a query reads. Filter on a dimension.
• It pages up to 1,000,000 rows into a table and no further. After that, narrow the read with a filter, or build a view.

The feed reference has the details.

Next steps