- Post History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
an hour ago
A practical guide to connecting your BI tools — Power BI, Tableau, Excel, and others — directly to ServiceNow data using standard SQL, without building ETL pipelines or moving data out of your instance.
If your team wants to report on ServiceNow data in Power BI or Tableau, the instinctive answer is to extract it — pipe it to Snowflake, Databricks, or an internal data lake, and query it there. That works, but it creates a problem: the moment data leaves ServiceNow, it starts aging, and someone has to maintain the pipeline that keeps it fresh.
Live Connect is ServiceNow's answer to this. It exposes your RaptorDB Pro database as a standard SQL endpoint — your BI tool connects via a JDBC or ODBC driver, runs SQL queries, and gets back current results. No data movement, no pipeline, no staleness.
Your existing ACLs govern what each service account can see — there is no separate security model to replicate in a third-party store. And because queries push down to RaptorDB Pro, cross-table joins, aggregations, and filters run at the database, not on the BI tool side.
Live Connect is designed for BI and analytics workloads — interactive queries, dashboards, and reports. It is read-only by design (SELECT only). It is not intended for mass data export, replication pipelines, or continuous high-frequency data feeds. For event-driven streaming, use Direct Connect (Kafka) instead.
A common question is how Live Connect differs from the Table API many teams already use for integrations. They were designed for different jobs — understanding the distinction upfront prevents the wrong tool being applied to an analytics use case.
Table API is one table at a time, capped at 10,000 rows per call — fine for integrations, wrong for analytics. Live Connect pushes SQL joins, aggregations, and filters down to RaptorDB Pro, so complex analytical queries return only the result set rather than raw rows for processing elsewhere.
Four concepts to understand before configuring anything.
Live Connect connections authenticate with a service account using Basic Auth. Using a service account (rather than a personal user account) ensures dashboards continue working if the original author leaves the organisation, and allows multiple BI users to share the same controlled access without sharing individual credentials.
An admin must explicitly grant the egress_sql ACL on each table before it is queryable via Live Connect. This is mandatory and cannot be bypassed — even a system administrator cannot query a table via Live Connect until it has been explicitly opened. Row-level and field-level ACLs are also enforced by default, meaning users see only the data their roles permit.
Every Live Connect query consumes Workflow Data Fabric (WDF) tokens at a rate of 1 MB per token. Tokens are shared across all WDF-metered capabilities on your entitlement. There is also a 500 API calls per hour rate limit, shared across all service accounts and connections on the instance. This limit resets at the top of each wall-clock hour.
Any query that runs longer than 5 minutes (300 seconds) is automatically cancelled — from first byte received to last byte returned, including both execution and result streaming. No partial data is returned. Design queries with selective filters and targeted column lists; avoid SELECT * on large tables. The timeout can be extended via a ServiceNow Support case, though increasing it may impact instance performance.
RaptorDB Pro subscription (Australia release or Zurich P8 backport). WDF token entitlement. Admin role on the ServiceNow instance. Client machine running your BI tool (Windows for ODBC; either platform for JDBC).
Complete setup flow from a fresh instance to a working BI tool connection.
-
1Install the SQL API server plugin. Navigate to
All > Application Manager, search for "SQL API" or "Live Connect", and install the plugin. This activates the SQL endpoint on your instance. No instance restart is required. -
2Create a service account. In
All > User Administration > Users, create a dedicated user for BI tool access. Assign it the read role for the tables you plan to expose. Use a service account rather than a personal user account so that dashboards do not break if someone leaves the organisation. -
3Grant egress_SQL ACL on each table. Navigate to
All > System Security > Access Control (ACL). For each table you want to expose, create or find the ACL record and add theegress_sqloperation, granting it to the service account's role. This step is mandatory for every table — no table is queryable until explicitly opened. -
4Whitelist client IP addresses. Navigate to the IP restriction settings in
All > System Propertiesand add the IP addresses of all machines that will connect to Live Connect. Connections from non-whitelisted IPs are rejected. For BI server environments (Tableau Server, Power BI Gateway), whitelist the server IP, not the end-user's machine IP. -
5Download and install the client driver. On the ServiceNow Store, search for "SQL API client" — this package includes both the ODBC and JDBC drivers. Install the driver appropriate for your BI tool and operating system. ODBC is typical for Windows-based tools like Power BI Desktop; JDBC works on any platform.
-
6Configure the connection in your BI tool. Use your instance URL as the server, the service account username and password for authentication, and select the ODBC or JDBC driver you just installed. In Power BI, choose Import mode — Direct Query is not supported. Once connected, your ServiceNow tables appear as a database with columns you can query directly.
-
7Run a test query. Start with a simple, bounded query to confirm the connection and ACLs are working:
SELECT number, priority, state FROM incident WHERE state = 6 LIMIT 100;— If you get results, the setup is complete. If the table is not found, check the ACL grant. If the connection is refused, check the IP whitelist.
Live Connect supports a rich subset of ANSI SQL — joins, aggregations, window functions, date operations, subqueries, and more. Here are the patterns that matter most for ServiceNow data.
Reference fields in ServiceNow store sys_ids and choice fields store integers. The DV() function resolves these to human-readable labels for use in reports:
FROM incident
WHERE state IN (1,2,3);
With Table API you would pull incidents and users separately, then join them in your BI tool or data warehouse. Live Connect pushes the join to the database:
FROM incident i
LEFT JOIN sys_user u ON i.assigned_to = u.sys_id
WHERE i.state IN (1,2,3);
Push GROUP BY, COUNT, AVG, and SUM server-side so only the summary result crosses the wire:
DV(priority) AS priority,
COUNT(*) AS incident_count,
AVG(CAST(resolve_time AS FLOAT) / 60.0) AS avg_resolve_hours
FROM incident
WHERE opened_at >= CAST(NOW() AS DATE) - INTERVAL '12 months'
GROUP BY month, priority
ORDER BY month DESC;
If you use Live Archive, archived records live in ar_incident, ar_problem, etc. You can query them with the same SQL syntax — just note that archive tables need their own egress_sql ACL grant (separate from the live table). Use UNION ALL to combine live and archive results into a single historical view.
Live Connect has been tested with the following clients. A few tool-specific notes to save you time.
- Power BI Direct Query mode
- Data replication tools (Fivetran, dbt) — not an intended use case
- DML or DDL operations (INSERT, UPDATE, DELETE, CREATE, DROP)
- Parameterized queries or prepared statements
- Self-hosted / hyperscaler deployments (on roadmap; not available today)
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'incident'; to see what columns are available. A few minutes of schema exploration prevents a lot of report-building rework.SELECT *. Column selection is proportional to I/O cost in the columnar engine — selecting only the columns you need makes queries faster and reduces WDF token consumption.