Hi @dacou
We have officially supported creating adhoc AQL Dimension in the Reporting side.
Do let me know if you have any questions 
2 Likes
This is an interesting use case for a problem i’m having. When you add Country as a dimention to aggregate by - the executed query is showing that you start with order item and then left join country by an id. What happens then in a scenario where a country exists in the country table but doesn’t have an order? From that executed query you would be missing a data point saying that there are 0 revenue. How would you build the AQL / relationships to ensure you see that data point?
Thanks for raising the concerns. To explain why Holistics choose such joining strategy, there are 2 reasons:
- It prevents fan-out/double aggregation on measures.
- It avoids data anomalies where the data doesn’t conform to the relationship. For example: Given that we have
orders > country(n-1 relationship). Then, orders without country info will show up with a null country column when you aggregate them by country.
What happens then in a scenario where a country exists in the country table but doesn’t have an order? From that executed query you would be missing a data point saying that there are 0 revenue.
Your concern is indeed valid. As for now, If you wish to see the countries that are having 0 revenue, you could workaround by adding count(countries.id) field into your visualization.
How would you build the AQL / relationships to ensure you see that data point?
As for now, AQL does not provide such utility but we would definitely consider it carefully.
Please don’t hesitate to reach out to us if you have further concerns.
2 Likes