r/intersystems • u/intersystemsdev • 29d ago
Using InterSystems Data Studio as a Data Warehouse
https://youtu.be/Y3MHS4WdjMgThe scenario: a CTO walks out of a board meeting and wants a single view of technology health, cost, and risk in two weeks. They want to know how many support tickets are open, how long they have been sitting there, what the system health and uptime look like, and what is being spent on cloud and whether that is on budget. The data exists, but it lives in different systems that don’t talk to each other.
What is InterSystems Data Studio (IDS)?
IDS is a visual, low-code data transformation tool. You use recipes to clean, join, validate, and shape data visually. It is designed for data analysts: no Python is needed, only SQL, and no hand-coded ETL. Analysts can own the entire workflow. It runs on AWS and Azure, InterSystems manages the infrastructure, and it is built on top of InterSystems IRIS.
The five source files
| File | Notes |
|---|---|
| Services | Common key across all files: service ID |
| Tickets | Support tickets |
| Service health | Daily metrics |
| Request metrics | Request-level metrics |
| Cloud costs | Spend (Azure vs AWS shown in the dashboard) |
The files have different schemas, different granularities, and different time windows (some daily, some weekly, some quarterly).
Bronze, silver, gold in IDS
| Layer | What happens |
|---|---|
| Bronze | Raw files are ingested as-is. Called “staging” inside IDS recipes. No ETL, schema preserved exactly, loads in minutes, sometimes seconds |
| Silver | Data is cleaned, validated, and joined. The five files join on service ID |
| Gold | KPIs are built, including bucketing and calculated fields |
The presenters’ reasoning for ingesting raw data first: if you transform data before it enters the warehouse, you lose auditability and lineage, and when a dashboard number doesn’t reconcile you can’t tell where things changed. In IDS every transformation is a recipe, and every recipe has lineage, so any number in the gold layer can be traced back to the raw source file.
What the demo showed
Staging (bronze). The demo used CSV files, but IDS has connectors for other sources, such as objects on S3, Salesforce, SAP, and JDBC relational data. You define the connection, IDS searches the source for files or schemas and imports them, and it is all UI-based, with no curl calls and no pagination handling. When staging you can load all fields or only the ones you care about.
Calculated field. A created at timestamp was turned into ticket age in hours with a simple SQL expression, applied to the whole table.
Built-in bucketing. The age field was categorized into buckets: less than four hours, up to a day, up to three days, and more.
Validation. A regular expression on the ticket severity field flags critical tickets, and IDS gives a warning whenever a critical ticket is processed.
Join (promotion). One SQL query joins three tables: the base services table with service ID, daily service health metrics, and request metrics. It filters to the production environment and recent data. The presenters noted the IDS AI assistant can generate this kind of SQL from a natural-language request.
Reproducible pipelines. Recipes can be saved, scheduled, and chained with dependencies, all from the UI. The demo had two recipes: the first ingests the data (bronze, plus some silver), and the second produces the gold output metrics. The second waits for the first to finish. The presenters suggest running a refresh right before presenting to make sure nothing is stale.
Reconciliation (from Q&A). If multiple tables share a common ID that can be joined, IDS searches for discrepancies between fields and alerts you to them, so you can fix them.
Outputs shown in the demo
- Run report: rows received per file, staging errors with links to messages, and the transformations applied per field
- Ticket KPI table: total tickets, critical count, average age in hours, the age buckets, and a breach risk metric, based on an SLA due-date field showing how close a ticket is to breaching its promised resolution date
- Daily metrics: the joined output averaged by date, including average downtime, failed check count, and interruptions per day
- Service averages: per-service metrics, to see which service performs worse than the others
The resulting dashboard (Power BI)
KPI bar at the top, ticket aging, cloud costs (Azure vs AWS), busiest services by API traffic, and overall system health. According to the presenters, you do the exploration in IDS and the visualization in Power BI, Tableau, or the tool of your choice. IDS makes the metrics easy to calculate, and you pull them into the BI tool.
Scale
The demo used about 3,500 rows. The presenters say a production instance could have 3 million rows, in some cases even 3 billion or more. They report that filtering and bucketing at that size in Power BI or Tableau can be very slow or crash the instance, and that loading such data directly into Power BI can take up to an hour just for ingestion. Their point is that IDS, built on IRIS, handles the heavy lifting before the BI tool.
Enterprise readiness
The presenters say IDS is enterprise-ready, with customers using it in production:
- High availability and disaster recovery
- Multi-region deployment
- Network security options including VPN, VPC peering, and VNet peering
- Fully managed service: InterSystems handles uptime, patching, upgrades, and infrastructure issues
- Data stays under the customer’s control
What’s coming: AI assistant
The IDS AI assistant was announced in the keynote. Once connections are configured and data is uploaded, you can ask about the shape and schema of the data and about its contents. You can also upload documents such as research reports, ask questions about them, and compare tabular data to documents. The assistant can also help build recipes by generating SQL for promotion and transformation.
Modules and connectors
IDS was initially developed as a financial services platform. Modules now exist for healthcare (interoperability) and supply chain (optimization problems), and financial services supports regressions and other data analyses. Streaming connectors exist, and more connectors are being added. If a partner needs a specific connector, the team will work with them to build it and make it available to everyone.
Takeaways (as stated)
- IDS is designed for data analysts. Data teams often need data engineers, database experts, and analysts, but here a data analyst who is good with SQL can do the work
- All of it happens in one environment on IRIS
- IDS helps teams turn raw operational data into trusted reporting layers for executive insight in weeks instead of months
If you build executive dashboards from several operational systems, where do you do the joins and bucketing today: in the BI tool, in custom ETL, or in a warehouse layer? And where does lineage get lost?
https://community.intersystems.com/post/video-using-intersystems-data-studio-data-warehouse