Can someone give me an explanation of how Local.PD.Linkage.Definition.Individual works? This was setup by another company as part of our implementation.

Below is my configuration..

We are getting a lot of matches on Given Name, but then the Family Name does not match at all, so I am wondering if these need to be adjusted. I just don't understand if they need to be positive or negative.

if I use the MLE CALIBRATION MONITOR, it seems that none of the values should be negative.

Hi everyone, on HealthShare Unified Care Record 2024.1.0 Build, we're using the Analytics section to create a dashboard containing a time chart showing a cumulative curve of the number of documents indexed in the registry for each documentSource of each repository. We tried the following steps: we created a cube whose dimensions are the CreationDate, SourceValue, and repositoryUniqueID of the HS_Registry.Document table; in the Analytics section, we created a pivot table that lists the document creation date on each row, along with as many columns as each repository's documentSources. However,

Hello everyone! 👋

I have a question regarding roles and resources. 

To give you some context: I have a user who has been assigned only the role %HS_UsageDashboard_Access.This allows them to access the dashboards correctly (by giving the direct URL). Then,  if I try to access the Management Portal with this same user, I can log in with no access to any resources within it (as expected).

Hi community
I have a persistent data class where there are some properties that contain underscores in their names, for example:
Property "client_name" As %String(MAXLEN = 250) [ SqlFieldName = client_name ];
In Architect, I would like to use this property in an expression, in this field:

But I can't figure it out. If I use %source.client_name, it gives a compilation error. If I use %source."client_name", it treats it as the string "client_name" instead of getting the value from the field. Any suggestions on how to do this?

Window functions in InterSystems IRIS let you perform powerful analytics — like running totals, rankings, and moving averages — directly in SQL.
They operate over a "window" of rows related to the current row, without collapsing results like GROUP BY.
This means you can write cleaner, faster, and more maintainable queries — no loops, no joins, no temp tables.

In this article let's understand the mechanics of window functions by addressing some common data analisys tasks.

Introduction to SQL Window Functions in InterSystems IRIS

SQL window functions are a powerful tool for data analysis.
They allow you to compute aggregates and rankings across rows while preserving individual row visibility.
Whether you're building dashboards, reports, or complex analytics, window functions simplify your logic and boost performance.

Note: I'm not an expert in window functions, but I’d like to share the insights and resources that helped me understand them. Suggestions or corrections are very welcome!

🚀 Why Window Functions Matter

Have you ever written multiple SQL queries, or even procedural loops, just to calculate running totals, ranks, or differences between rows?

Window functions let you do all that in a single SQL query.

They bring powerful analytics directly into SQL — no extra joins, no temporary tables, and no procedural loops.

🧠 What Are Window Functions?

A window function performs a calculation across a set of rows that are somehow related to the current row — this set of rows is called a window.

As depicted in Figure 1, unlike GROUP BY, window functions don’t collapse rows. They allow you to compute aggregates while still keeping each row visible.

Figure 1 - Differences between Aggregations and Window Functions

The general syntax looks like this:

window_function_name(...) OVER (
  PARTITION BY column_name
  ORDER BY column_name
  ROWS BETWEEN ...
)

Where:

• PARTITION BY defines groups of rows (like "per customer" or "per department").
• ORDER BY defines the order of rows within each partition.
• ROWS BETWEEN ... defines which subset of rows are visible to the function (the window frame).

⚙️ Why Use Window Functions?

Before window functions, developers often had to:

• Run multiple queries to get intermediate results.
• Use temporary tables or subqueries to merge partial aggregates.
• Write procedural code in ObjectScript to simulate ranking or running totals.

Window functions solve this neatly — one query, no loops, no extra state to manage.

🧩 Example 1 — Running Total Per Customer

Let’s start with a simple example: compute the running total of each customer’s orders over time.

🛠️ Create and Populate Table for the example

CREATE TABLE Orders (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE,
  OrderAmount DECIMAL(10,2)
)

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount)
SELECT 1, 101, '2023-01-01', 100.00 UNION
SELECT 2, 101, '2023-01-05', 150.00 UNION
SELECT 3, 102, '2023-01-02', 200.00 UNION
SELECT 4, 101, '2023-01-10', 50.00 UNION
SELECT 5, 102, '2023-01-07', 100.00 

❌ Without window functions — multiple queries

SELECT
  o1.CustomerID,
  o1.OrderDate,
  SUM(o2.OrderAmount) AS RunningTotal
FROM Orders o1
JOIN Orders o2
  ON o1.CustomerID = o2.CustomerID
  AND o2.OrderDate <= o1.OrderDate
GROUP BY o1.CustomerID, o1.OrderDate
ORDER BY o1.CustomerID, o1.OrderDate

Result:

This works, but it needs a self-join and a GROUP BY, and becomes expensive for large datasets. Window functions allow to write a much more clean SQL query.

✅ With window functions — one query

SELECT
  CustomerID,
  OrderDate,
  SUM(OrderAmount) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
  ) AS RunningTotal
FROM Orders
ORDER BY CustomerID, OrderDate

Result:

Let's break down each statement in the window function syntax:

• PARTITION BY CustomerID Ensures that the running total is calculated separately for each customer. Without this, the sum would span across all customers.

• ORDER BY OrderDate Defines the sequence of orders for each customer, so the running total accumulates in chronological order.

• SUM(OrderAmount) OVER (...) This is the window function applied over the partitions. In this case, it computes the sum of OrderAmount for each row, accumulating them, including all previous rows in the same partition (customer) up to that point.

Figure 2 - Window function evaluation for example 1

💡 Example 2 — Ranking Employees by Salary

🛠️ Create and Populate Table for the example

CREATE TABLE Employees (
  EmployeeID INT,
  Department VARCHAR(50),
  Name VARCHAR(100),
  Salary DECIMAL(10,2)
)

INSERT INTO Employees (EmployeeID, Department, Name, Salary)
SELECT 1, 'Sales', 'Alice', 70000 UNION
SELECT 2, 'Sales', 'Bob', 65000 UNION
SELECT 3, 'HR', 'Carol', 60000 UNION
SELECT 4, 'HR', 'Dave', 62000 UNION
SELECT 5, 'Sales', 'Eve', 72000

❌ Without window functions — Dynamic SQL and ObjectScript loops

ClassMethod RankEmployeesBySalary()
{
    Set tSQL = "SELECT Department, EmployeeID, Salary " _
              "FROM Employees ORDER BY Department, Salary DESC"
    Set tRS = ##class(%SQL.Statement).%ExecDirect(, tSQL)

    Set prevDept = ""
    Set rank = 0

    While tRS.%Next() {
        Set dept = tRS.%Get("Department")
        Set emp = tRS.%Get("EmployeeID")
        Set sal = tRS.%Get("Salary")

        If dept '= prevDept {
            Set rank = 1
        } Else {
            Set rank = rank + 1
        }

        Write "Dept: ", dept, " | Emp: ", emp, " | Rank: ", rank, " | Salary: ", sal, !
        Set prevDept = dept
    }
}

Result:

USER>Do ##class(tmp.Teste1).RankEmployeesBySalary()
Dept: HR | Emp: 4 | Rank: 1 | Salary: 62000
Dept: HR | Emp: 3 | Rank: 2 | Salary: 60000
Dept: Sales | Emp: 5 | Rank: 1 | Salary: 72000
Dept: Sales | Emp: 1 | Rank: 2 | Salary: 70000
Dept: Sales | Emp: 2 | Rank: 3 | Salary: 65000

✅ With window functions — one declarative SQL

SELECT
  Department,
  EmployeeID,
  Salary,
  RANK() OVER (
    PARTITION BY Department
    ORDER BY Salary DESC
  ) AS SalaryRank
FROM Employees
ORDER BY Department, SalaryRank

Result:

Let's break down each statement in the window function syntax:

• PARTITION BY Department
  Ensures that ranking is calculated separately within each department. Without this clause, employees would be ranked across the entire company, ignoring departmental boundaries.

• ORDER BY Salary DESC
  Sorts employees within each department from highest to lowest salary. This determines the ranking order — higher salaries get lower rank numbers.

• RANK() OVER (...)
  Applies the ranking function over each department's sorted list. It assigns a rank to each employee based on their salary, with ties receiving the same rank and gaps appearing in the sequence.

Figure 3 - Window function evaluation for example 2

🧩 Example 3 — Moving Average of Daily Sales

Let’s illustrate how ROWS BETWEEN works with a moving average.

🛠️ Create and Populate Table for the example

CREATE TABLE DailySales (
  SaleDate DATE,
  Amount DECIMAL(10,2)
)

INSERT INTO DailySales (SaleDate, Amount)
SELECT '2023-01-01', 100 UNION 
SELECT '2023-01-02', 150 UNION
SELECT '2023-01-03', 200 UNION
SELECT '2023-01-04', 250 UNION
SELECT '2023-01-05', 300

❌ Without window functions — multiple queries and ObjectScript loops

ClassMethod MovingAverageWithoutWindow()
{
    // Query all sales ordered by date
    Set sql = "SELECT SaleDate, Amount FROM DailySales ORDER BY SaleDate"
    Set stmt = ##class(%SQL.Statement).%New()
    Set status = stmt.%Prepare(sql)
    If $$$ISERR(status) {
        Write "SQL Prepare failed: ", status, !
        Quit
    }

    Set rset = stmt.%Execute()

    // Store rows in memory for lookback
    Set rowCount = 0
    While rset.%Next() {
        Set rowCount = rowCount + 1
        Set sales(rowCount, "Date") = rset.%Get("SaleDate")
        Set sales(rowCount, "Amount") = rset.%Get("Amount")
    }

    // Loop through and calculate 3-day moving average
    For i = 1:1:rowCount {
        Set total = 0
        Set count = 0

        For j = i-2:1:i {
            If j >= 1 {
                Set total = total + sales(j, "Amount")
                Set count = count + 1
            }
        }

        Set movingAvg = total / count
        Write "Date: ", sales(i, "Date"), " | Amount: ", sales(i, "Amount"), " | MovingAvg: ", $FN(movingAvg, "", 2), !
    }
}

Result:

USER>Do ##class(tmp.Teste1).MovingAverageWithoutWindow()
Date: 66475 | Amount: 100 | MovingAvg: 100.00
Date: 66476 | Amount: 150 | MovingAvg: 125.00
Date: 66477 | Amount: 200 | MovingAvg: 150.00
Date: 66478 | Amount: 250 | MovingAvg: 200.00
Date: 66479 | Amount: 300 | MovingAvg: 250.00

✅ With window functions — one declarative SQL

SELECT
  SaleDate,
  Amount,
  AVG(Amount) OVER (
    ORDER BY SaleDate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS MovingAvg
FROM DailySales
ORDER BY SaleDate

This computes the average of the current day and the two previous days — a rolling 3-day average.

Result:

Let's break down each statement in the window function syntax:

• ORDER BY SaleDate
  Defines the chronological order of sales, which is essential for calculating a time-based moving average.

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  Specifies the window frame: the current row and the two rows before it. This creates a rolling 3-day window for the average calculation.

• AVG(Amount) OVER (...)
  Applies the window function to compute the average of Amount across the defined frame. For each row, it includes the current day and the two previous days in the calculation.

Figure 4 - Window function evaluation for example 3

Note that there's no PARTITION BY in this example. This is because the goal is to calculate the moving average across all daily sales, not separately by category, region, or customer.

Using PARTITION BY would split the data into independent groups, causing the moving average to reset within each partition. Since we want a continuous rolling average over time — treating the dataset as a single chronological sequence — we omit PARTITION BY to keep the window unbroken. But off course you can use it if it makes sense for your query requirements.

🏁 Key Takeaway

Window functions let you express what used to require multiple queries or procedural code in a single, elegant SQL statement.

They make your queries more readable, your code more maintainable, and your analytics faster — all without leaving SQL.

🧭 Final Thoughts

Window functions in InterSystems IRIS offer a powerful and elegant way to perform complex analytics directly in SQL. Whether you're calculating running totals, assigning ranks, or computing moving averages, these functions simplify your logic and improve performance — all while keeping your queries readable and maintainable.

By embracing window functions, you unlock a deeper level of insight from your data without resorting to procedural code or convoluted joins. They’re not just a convenience — they’re a leap forward in how we think about SQL analytics.

You can checkout more details and more window function in the Window Functions reference in IRIS documentation.

This article was written with the help of AI tools to clarify concepts and improve readability.

Hey Community,

Enjoy the new video on InterSystems Developers YouTube:

⏯ Analytics and AI with InterSystems IRIS - From Zero to Hero @ Ready 2025

Hi community.
I have a query:

SELECT
nameField,
dateField,
anotherDateField
FROM
(      
SELECTMIN(someDate) as dateField,       
nameField,
anotherDateField
FROM $$$SOURCEWHERE $$$RESTRICT               
GROUPby someOtherField    
)
WHERE dateField >= anotherDateField

This query should filter the data by the minimum value of the somDate field, but it doesn't. It displays all values ​​together, regardless of the external filter. The exact same query (without the $$$ tokens, of course) works fine in a regular SQL runtime.
My guess is that the $$$RESTRICT does this

Real Time FHIR® to OMOP Transformation

This part of the OMOP Journey,  we reflect before attempting to challenge Scylla on how fortunate we are that InterSystems OMOP transform is built on the Bulk FHIR Export as the source payload.  This opens up hands off interoperability with the InterSystems OMOP transform across several FHIR® vendors, this time with the Google Cloud Healthcare API.

Google Cloud Healthcare API FHIR® Export

Hi everyone! 👋
I’m excited to share the project I’ve submitted to the current InterSystems .Net, Java, Python, and JavaScript Contest — it’s called IRIStool and Data Manager, and you can find it on the InterSystems Open Exchange and on my GitHub page.

I work a lot with IRIS BI, but some features are still a mystery to me. There is a great feature here that allows you to create expressions in Architect.
  
I'm already very familiar with it, but I still don't quite understand what environment it uses.

Ready to elevate your Business Intelligence game?

We've got an engaging video, perfect for all skill levels.

Discover how to get started with IRIS BI, optimize cube performance, and drive analytics adoption. Plus, get a sneak peek at what's next!

BroadSea - Core OHDSI Walkthrough with InterSystems OMOP

Hades DatabaseConnector 6.4.0 now does IRIS stuff!

InterSystems OMOP, The Cloud Service (Troy)

Hi everyone,

I am trying to create a treeMapChart in IRIS BI that will then be displayed on my DeepSeeWeb dashboard. In the IRIS BI User Portal, this is an example of what my treeMapChart looks like:

I know there is a huge amount of rectangles in this graphic - I care most about the common components (the largest boxes) but I still want all of the boxes to show. However, it projects to my DeepSeeWeb dashboard as the following: 

Sometimes your client may request documentation of your BI or interoperability project in a formal document. In this case, MS Word is a good alternative, as it has an advanced editor that allows you to generate professional documentation. Now it's possible!
The iris4word app has this functionality!

Final MS Word Document	Word Template

iris4word business logic

Hi Community,

Looking for a more personal approach to learning InterSystems products?

Try one of our live classroom courses! In just a few days, our experienced instructors will help you get up to speed, no matter your role.

Maybe this is well known but wanted to help share.

Consider that you have the following persistent class defintions

An Invoice Class with a property reference to Provider

I need to download IRIS STUDIO 2024.1.3

I have a widget that uses "choose Data source" control option. Termlist for the control consist of the two data sources and i want to set one of them by default. For example: I have two data sources, one is grouped by month, the other by year. I need to set the one that is grouped by year by default 

Using default value at the bottom return an error

How can i achive that?
 

Hey, Community,

Enjoy the new video on InterSystems Developers YouTube:

⏯ A Whirlwind Tour of the Analytics Buffet @ Global Summit 2024

Hey Community,

Enjoy the new video on InterSystems Developers YouTube:

⏯ Using Power BI for health insights

Hi!
I have question about MDX functionality in context of IRIS Analytics.

How does IRIS MDX distinct selection works? Is there any restruqtion when analyzing strings? Like special symbols or length?
Here is an example
I have this data:
 6 rows and 2 of them unique
Then we create data cube based on this model and examine it with Analyzer
 
Detailed listing 
6 rows an ONE unique string. Which is obviously not true. 
This happed only with string with symbols in it
My task is to get the right amount of unique strings

InterSystems has been at the forefront of database technology since its inception, pioneering innovations that consistently outperform competitors like Oracle, IBM, and Microsoft. By focusing on an efficient kernel design and embracing a no-compromise approach to data performance, InterSystems has carved out a niche in mission-critical applications, ensuring reliability, speed, and scalability.

A History of Technical Excellence