Metrics from your own data warehouse

The metrics that matter most to a claims or underwriting initiative, such as days to settle a claim, quotes per underwriter, or submissions by line of business, rarely live in a help desk or a CRM. They live in your data warehouse, already modeled. A metric query reads one of them from there, every night, so a metric Roiva would otherwise ask you to type in fills itself.

Metric queries run on Snowflake, Databricks, BigQuery and SQL Server, which includes Azure SQL Database. Write the SQL in that warehouse's own dialect. They aren't available while the account is set to Aggregate figures only, which allows no connections.

What a query returns

A metric query is one SELECT that returns one row per month:

period: a date in the month. Any day in the month will do; Roiva reads the month.
value: the month's figure, as a number in the metric's own unit. A percentage metric runs 0 to 100, so 12.5 means twelve and a half percent.
entity: only when the query breaks the metric down, by line of business, region or team. You name the breakdown when you make the query, and every row then needs one. A breakdown has at most 200 values. It is never a list of claimants, policies or people. A value formula doesn't read a breakdown, because no single line of business is the metric's figure. To feed a formula, make a second query for the same metric with no breakdown. For the same reason, a query with a breakdown can't take a metric from a connection, and doesn't stop anyone recording the metric by hand.
sample_size: optional. It records how many records an average was taken over.

For example, the average days to settle a closed claim:

SELECT DATE_TRUNC('month', closed_on) AS period,
       AVG(days_to_settle)            AS value,
       COUNT(*)                       AS sample_size
FROM ROIVA_METRICS.PUBLIC.CLAIMS_CLOSED
GROUP BY 1

Each query feeds one metric. It can feed:

Any metric Roiva ships that is recorded by hand or imported. Every insurance metric in the Metric registry is one.
A metric a kind of connection can feed that none of yours does, such as ticket volume when your help desk isn't connected to Roiva, or is one Roiva has no connection for.
A metric one of your connections feeds now, with no breakdown. Saving the query moves the metric to it, as described under Moving a metric from a connection to a query below.
A metric your account defined itself.

Once a query with no breakdown feeds a metric, it stays that metric's source. If you later connect a platform that could feed it too, that platform feeds the other metrics of its kind, and the query's readings still count for this one. That holds while the query is paused, too, because its readings stand. It ends when the query is retired.

A query feeds the account, so every initiative reading the metric sees it, or it feeds one initiative. An initiative is offered once it's linked to the connection as an activity source. See Linking connections to initiatives.

Give Roiva a schema to read, and nothing else

Each warehouse's setup script, on its platform page, gives Roiva a place of its own to read and nothing else:

Snowflake: a database, ROIVA_METRICS, whose PUBLIC schema's views Roiva's role may SELECT from. A Snowflake view runs with its owner's rights, so the role reads what each view returns, not the tables behind it. One exception comes from Snowflake itself: every role holds whatever your account grants to the PUBLIC role, Roiva's included. Snowflake's sample data is granted to PUBLIC by default, and anything else your account grants there is readable too.
Databricks: a schema, workspace.roiva_metrics (or one in another catalog, such as main in an older workspace), on which the service principal gets USE SCHEMA and SELECT. On a SQL warehouse, Unity Catalog reads a view's tables with its owner's rights. Grants to a group the service principal is in, such as all account users, reach it too, and every workspace lets all account users read its samples catalog. The service principal's OAuth secret needs the sql scope.
BigQuery: a dataset of views (roiva_metrics, say), authorized on the datasets its views read, with BigQuery Data Viewer for the service account on that dataset alone. The views can read your source tables; the service account can't.
SQL Server: a user, roiva_reader, and a schema, roiva_metrics, owned by dbo, on which the user gets SELECT and nothing else. On Azure SQL the user is a contained user of the database, with its own password; on SQL Server it's a login first. A view reads tables with the same owner with that owner's rights, so the user needs no grant on the tables behind it.

A good split is to keep the views plain and put the logic in the query:

The views do the minimizing. A view like CLAIMS_CLOSED(closed_on, line_of_business, days_to_settle) carries the columns a metric needs and no names, addresses or policy numbers.
The query holds the definition. The rollup to months, the average and any filter live in the query, where Roiva versions them.

A view can change without anyone telling Roiva. If you name the schema on the connection (Metrics Schema, or Metrics Dataset for BigQuery, in its settings), Roiva records something of each view in it with every result, and a view that changed since the last result shows up in the query's history. BigQuery shows a view's text to anyone who may read it, so Roiva records a digest of the text. SQL Server computes that digest itself where the login may see the text, so the text never reaches Roiva. Snowflake and Databricks show it only to the view's owner, so for them, and for a SQL Server login that can't see it, Roiva records when each view was created and last altered: that it changed, not what changed.

Write, test and save a query

Only account Owners and Admins write, test, pause or retire a query. Everyone in the account can read a query, its versions and what each returned.

Open the warehouse's connection under Integrations, then its Metric Queries tab, and click New Query.
Choose the metric and what it feeds, and name a breakdown if it has one.
Paste the SQL and click Test Query. Roiva runs it the way the nightly sync will, shows the months it would read, and keeps nothing. A test runs on your warehouse at your usual cost.
Click Save Query. It runs straight away, and then every night with the connection's other syncs.

The Test Query panel says why Roiva wouldn't read a query: a column that isn't there, two rows for one month, a value off the metric's range. It quotes the warehouse's own message when the warehouse refused the query.

Versions, and what a reading remembers

Nothing edits a query's text in place. A changed query is saved as the next version, with a line saying what changed. What a query feeds (its metric, its initiative and its breakdown) is fixed once it's made. For another metric, make another query.

Every reading a query writes names the version and the rows that version returned. Its What's behind it page shows those rows, with its own row marked, beside the SQL that produced them. Every run is labeled roiva-metric-query-<id>-v<version> in the warehouse's own history (Snowflake's query tag, a Databricks query tag keyed roiva, a BigQuery job label keyed roiva, a comment at the start of a SQL Server statement), so you can find the same statement there. On SQL Server, Roiva's sessions are also named Roiva, so Query Store and Extended Events show each one.

A value formula keeps that too. An entry computed from a query's reading names the version and result it was computed from, and still does after the query changes. So an approved figure reproduces from the rows it was actually computed from. See What makes a figure auditable.

Changing a query's text changes the definition of the metric. So two more things happen:

The before-value is recalculated. A baseline Roiva calculated from the old version's readings is calculated again from the new version's, so before and after stay one definition. A baseline you entered yourself is never replaced.
The next entry is held for review. Where auto-approval is on, an entry computed from a version the formula's last approved entry didn't read waits for a person, however close its amount is to the last one.

A month the new version no longer returns loses its reading. Absence means the query has no figure for that month now, not that the figure is zero.

Moving a metric from a connection to a query

A connection's figure and your analysts' figure for the same metric rarely agree. Ticket volume from your help desk counts every ticket; the one in your warehouse may leave out spam and merged tickets. When the warehouse's is the one your company reports, an Owner or Admin can move the metric to a query:

On the metric's page, under Sources, follow the link to read it from your warehouse's query instead, or choose the metric in a new query's form. Metrics your connections feed now are listed under their own heading.
Click Test Query. Each month the query returns sits beside the figure Roiva reads for it now, and the panel lists the months that would have no figure after the move.
Tick the box that confirms the move, and save. The confirmation names the connection, any initiative that moves with it, and any baseline a person entered.

The connection's figures keep counting until the query's first run returns months, so a query your warehouse refuses leaves the metric as it was. After that:

Only the query counts for this metric, in every month. Your connection still feeds the other metrics it feeds. A month the query doesn't return has no figure, rather than the connection's, because a line made from two definitions is what the move gets rid of.
Entries are calculated again. Entries that aren't approved yet are recalculated from the query, and so is a baseline Roiva calculated from the connection's readings. Approved entries keep the figures they were approved with, and each still names where its inputs came from. The Inbox lists any whose readings changed under Changed since approval, so an approver can send them back to be recalculated (once the month is reopened, if it's locked).
A baseline a person entered stays. It was taken from the old figure, so check it against the query's months.
The next entry is held for review where auto-approval is on, because the last approved entry read no query.

To move the metric back, retire the query.

Pausing, retiring and deleting a query

Pause stops the query running. Its readings stay and still count, and it stays the metric's source. Resume runs it again.
Retire ends the query for good. Its readings are removed, and the metric goes back to being recorded by hand, or to the connection that feeds its kind if you have one. Entries that are still open are calculated again from what the metric reads now, and so is a baseline Roiva calculated from the query's readings. Approved entries keep the figures they were approved with, and the query's versions and results stay on its page, because those entries name them. The Inbox lists those entries under Changed since approval, for an approver to send back if they should be recalculated. A retired query can't be resumed. To feed the metric from your warehouse again, make a new query.
Delete is only for a query that hasn't returned anything yet, since nothing points to it.

How it stays read-only

Roiva never runs your text as written. It places your query inside a SELECT of its own and reads only the period, value, entity and sample_size columns from it, so no other column leaves your warehouse. The warehouse's parser refuses anything else in that position, such as an INSERT, a DELETE, a CREATE, a CALL or a second statement. Roiva doesn't parse your SQL itself. SQL Server is the one exception, described below.

The grants are still what decide what a query can reach, which is why each setup script grants read access to one schema and nothing more. The connection page's setup check warns when Roiva's role, service principal or service account can write. Beyond that, each warehouse says a different amount about what a statement did:

Snowflake doesn't report a statement's type. If it ever reports that a run changed rows, Roiva keeps nothing from that run and pauses the query. The check also looks at PUBLIC, and ignores Snowflake's own SNOWFLAKE database.
Databricks doesn't report a statement's type either, so after each run Roiva asks the workspace's query history, and pauses the query if it names anything but a SELECT. The check reads only what's granted to the service principal directly, not through a group.
BigQuery says what a statement is before it runs. Roiva dry-runs every query first, which bills nothing, and refuses anything BigQuery doesn't call a SELECT, or anything that would read more than 10 GB. The run itself can't bill past 10 GB. Roiva's token for the service account is also minted with a read-only scope. The check reads write permissions granted on the project, not on a single dataset.
SQL Server runs every statement in a request together, so a query that closed Roiva's parenthesis could add a statement of its own. Before sending a query, Roiva checks only that its text stays inside that parenthesis, past strings, quoted names and comments, and refuses it if not. SQL Server compiles the whole request before running any of it, so a query it refuses runs nothing. After each run, SQL Server must report exactly one SELECT: if it reports another statement, or a procedure, Roiva keeps nothing from that run and pauses the query. The check fails a login that can write, run procedures, change objects or permissions, or act as another user, and warns about any linked server it can see, because a query reaches a linked server as whatever login that server maps it to.

Each run stops after 120 seconds, reads the last 36 months, and returns at most 5,000 rows. Roiva stores only the rows a query returns.

Sending Roiva's own figures back to your warehouse never uses this connection. Roiva delivers its reporting tables to an S3 bucket through a separate role you create for that alone, which can add files to one folder and can't read your warehouse or anything else. See Roiva's figures in Power BI and your data warehouse.

Connecting to SQL Server

Roiva reaches only a server with a public endpoint, such as Azure SQL Database or SQL Server on a cloud machine. It connects from one fixed IPv4 address, 3.231.16.92, so let that address alone through the server's firewall (on Azure SQL, Networking › Firewall rules), on port 1433 or the port you enter. A server on your own network with no public address isn't supported yet.

Encrypted, and the certificate checked. Every connection is encrypted from before sign-in to the end. Roiva signs in only to a server whose certificate comes from a public certificate authority, as Azure SQL's does, and names the host you entered. A self-signed certificate is refused.
A SQL login. Roiva signs in with a SQL login or an Azure SQL contained user. Microsoft Entra ID sign-in, and with it Microsoft Fabric's warehouse, isn't supported yet.
The password follows the host. The password is stored encrypted and never shown again. Changing the connection's host or port needs the password entered again, so a stored password can't be sent to another server.
The instance's port, not its name. Roiva doesn't read SQL Server Browser, so server\instance won't work.
Azure SQL's connection policy. Keep it at Default or Proxy. Roiva doesn't follow the Redirect policy's hand-off to another address.
No WITH, no ORDER BY. SQL Server won't take either inside the derived table Roiva reads the query from. Put a common table expression in a view, and leave the sorting to Roiva.

Metric keys, and where a value comes from: what a metric, a baseline and a reading are
What makes a figure auditable: why a past figure still reproduces
Metric registry: every metric Roiva ships, and which a warehouse query can feed