Eva Klein's picture

Did you ever create an ARIS Aware dashboard and data feed and struggled to get the data from your data source the way you need it? If not, you’re a dashboarding expert. Otherwise, welcome to the club ;).

I love to create dashboards in ARIS Aware and challenge myself by finding solutions how I can get exactly the data from my data sources that I need.

One of my biggest challenges lead me to one of the most interesting operators in ARIS Aware – “RAQL Inline”. I ignored this operator until this day – what a huge mistake :).

What exactly challenged me so much.? Let’s start at the beginning. I had a data source file with different columns like this one:

Product

Risk Group 1

Risk Group 2

Risk Group 3

Product A

Risk A1

Risk B2

Risk 3A

Product A

Risk B1

Risk A2

Risk 3A

Product A

Risk A1

Risk A2

Risk 3A

Product B

Risk C1

Risk B2

Risk 3A

 

As you can see the table was nothing special, the data source includes unique rows (each row is a unique combination), and has only a few unique risks (the same risk can appear in multiple rows).

The question was how I can reach the following result table in the data feed that displays the sum of the different risks per product and risk groups.

Product

Risk Group 1

Risk Group 2

Risk Group 3

Product A

2

2

1

Product B

1

1

1

 

To reach this table you only have to add the RAQL Inline operator with the following code:

SELECT Product, COUNT(DISTINCT "Risk Group 1"), COUNT(DISTINCT "Risk Group 2"), COUNT(DISTINCT "Risk Group 3")  
FROM feed1
GROUP BY Product
ORDER BY Product ASC

That’s everything. You don’t need to try to solve the problem with the aggregation operator and additional complex data feeds, only one operator with a few lines of code solves the problems in seconds.

The first line aggregates the different products and counts the different risks in the risk groups, but only once with the help of the DISTINCT keyword. DISTINCT eliminates the repetitive appearance of the same data. The second line FROM feed1 references the connected previous operator, and if you only have one the name is every time feed1. If you have more than one connected previous operators the first connected is feed1, the second feed2 and so on counted from left to right. GROUP BY Product aggregates by product and reduces the duplicates. ORDER BY Product ASC is no necessary line of code, but it is helpful if you have a long list of rows.

When should you use the RAQL inline operator? You should try this operator to reduce the number of operators in your data feed and if you like to do operations that no other operator can do. And for sure you have to use it if you have SQL knowledge ;).

Cheers
Eva

Tags: ARIS Aware dashboard MashZone