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
Dear Eva,
Today I also stumbled upon the RAQL and wondered what it could do ;-) Do you know if there is a reference documentaion to RAQL? And if so, can you please share it?
Other question, as you seem to be the expert, can I use RAQL to join (combine) 2 data feeds based on more complex text match?
For example, data_feed1.name contains a business process name. Data_feed2.description contains long text witch might include the name. The goal is to find the matching records from both data_feeds. In pseudo-SQL:
select *
from data_feed1, data_feed2
where data_feed1.name is part of data_feed2.descriptionThanks for helping out.
BR,
Harm
