NETWORK.RINGS OpenBI
Rings: groups of nodes held together by triangles, such as accounts sharing phones and addresses, with their size, density and level.
Group: Networks. OpenBI’s own: DAX has no function of this name.
Syntax
NETWORK.RINGS ( <links>, <from>, <to> [, <level>] [, <smallest>] )| Parameter | Type | Description |
|---|---|---|
links | Table | The rows that make the network, one link a row: a table of the model or a table expression, as the filters leave it. |
from | Expression evaluated for each row | One end of each row's link, such as a payer, a customer or a place. |
to | Expression evaluated for each row | The other end of each row's link. |
level | Whole number optional | How tight a ring must be: 3 when left out (each of its links closes a triangle with two other links of the ring), 4 (each closes two), and so on. |
smallest | Whole number optional | The fewest nodes a ring must have: 3 or more, 3 when left out. |
Returns
Table. A row a node of each ring, the largest ring first and its nodes in the nodes' order. Its columns: Ring and Node; the ring's Size, Links and Density; Ring level and the node's own Level; and Triangles.
Remarks
- A ring is a group of nodes held together by triangles: at level 3 every link inside it is one side of a triangle of the ring, at level 4 of two, and so on. Raising the level keeps only the tighter cores. Groups like this, among accounts, phones and addresses, are where fraud rings and cartels show.
- Ring numbers the rings from 1, the one with the most nodes; on a tie, the most links, and after that the ring whose first node comes first. Size and Links count the ring's nodes and links; Density is its links over all the pairs it could link, 1 when everyone in it is linked.
- Ring level is the tightest level any link of the ring reaches. Level is the node's own: the tightest level of its links, so the nodes at the ring's core have the highest. Triangles counts the triangles the node makes inside its ring.
- A node is in one ring at most, and two rings that share a node are one; nodes in no ring are not listed. The links are taken both ways, and rows repeated between two nodes make one link.
- Rows where an end is blank, or where both ends are the same node, add no link; the end that is there still counts as a node.
- Nodes are compared the way OAX compares values: text without regard to case, and 1 and 1.0 are the same node. A node written two ways is shown with the spelling whose characters sort first by code (ALICE before Alice); a column of the model keeps one spelling of each text, the first it loaded.
- In a measure, the table is the rows the filters leave, so the network follows the slicers and the page: a visual measures the network of the rows in view.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
Examples
Accounts tied together by shared phones and addresses
Wim, Xi, Yan and Zoe all share with each other: the largest ring and the tightest, at level 4. The two triangles Ann, Bo, Cy and Di, Ed, Fay are rings of their own, and the square Ola, Per, Liv, Kim is none.
EVALUATE
VAR Shared =
DATATABLE (
"Account", STRING, "Shares with", STRING,
{
{ "Ann", "Bo" }, { "Ann", "Cy" }, { "Bo", "Cy" }, { "Cy", "Di" }, { "Di", "Ed" }, { "Ed", "Fay" },
{ "Di", "Fay" }, { "Fay", "Gus" }, { "Wim", "Xi" }, { "Xi", "Yan" }, { "Yan", "Zoe" }, { "Zoe", "Wim" },
{ "Wim", "Yan" }, { "Xi", "Zoe" }, { "Zoe", "Ola" }, { "Ola", "Per" }, { "Per", "Liv" }, { "Liv", "Kim" },
{ "Kim", "Ola" }
}
)
RETURN
SELECTCOLUMNS (
NETWORK.RINGS ( Shared, [Account], [Shares with] ),
"Ring", [Ring],
"Node", [Node],
"Size", [Size],
"Density", [Density],
"Level", [Level],
"Triangles", [Triangles]
)| Ring | Node | Size | Density | Level | Triangles |
|---|---|---|---|---|---|
| 1 | Wim | 4 | 1 | 4 | 3 |
| 1 | Xi | 4 | 1 | 4 | 3 |
| 1 | Yan | 4 | 1 | 4 | 3 |
| 1 | Zoe | 4 | 1 | 4 | 3 |
| 2 | Ann | 3 | 1 | 3 | 1 |
| 2 | Bo | 3 | 1 | 3 | 1 |
| 2 | Cy | 3 | 1 | 3 | 1 |
| 3 | Di | 3 | 1 | 3 | 1 |
| 3 | Ed | 3 | 1 | 3 | 1 |
| 3 | Fay | 3 | 1 | 3 | 1 |
Only the tightest
At level 4 each link must close two triangles: only the four who all share remain.
EVALUATE
VAR Shared =
DATATABLE (
"Account", STRING, "Shares with", STRING,
{
{ "Ann", "Bo" }, { "Ann", "Cy" }, { "Bo", "Cy" }, { "Cy", "Di" }, { "Di", "Ed" }, { "Ed", "Fay" },
{ "Di", "Fay" }, { "Fay", "Gus" }, { "Wim", "Xi" }, { "Xi", "Yan" }, { "Yan", "Zoe" }, { "Zoe", "Wim" },
{ "Wim", "Yan" }, { "Xi", "Zoe" }, { "Zoe", "Ola" }, { "Ola", "Per" }, { "Per", "Liv" }, { "Liv", "Kim" },
{ "Kim", "Ola" }
}
)
RETURN
SELECTCOLUMNS (
NETWORK.RINGS ( Shared, [Account], [Shares with], 4 ),
"Ring", [Ring],
"Node", [Node],
"Size", [Size],
"Density", [Density],
"Level", [Level],
"Triangles", [Triangles]
)| Ring | Node | Size | Density | Level | Triangles |
|---|---|---|---|---|---|
| 1 | Wim | 4 | 1 | 4 | 3 |
| 1 | Xi | 4 | 1 | 4 | 3 |
| 1 | Yan | 4 | 1 | 4 | 3 |
| 1 | Zoe | 4 | 1 | 4 | 3 |
Related functions
NETWORK.CLIQUES, NETWORK.COMMUNITIES, NETWORK.NODES, NETWORK.CYCLES.
Open Graph, OpenBI's graph library, does the work: trussness, count_adjacent_triangles. See the OAX network functions and Open Graph.