Access to multiple data sources using Oracle Brio

Question

Trying to build a dashboard using Oracle's Brio. I have to access 6 different databases to grab the same type of data, aggregate it and display it. Except that when I do it, Brio grabs the data from the first source just fine. When I grab the data from the second data source, Brio replaces the original data with the second set. So I am not able to aggregate the data. Can anyone help me figure out how I can do this in Brio please?

Answer 1

You need to use a UNION statement, rather than running the query 6 times.

For example:

Don't do this

SELECT * FROM DATABASE_1..TABLE_1
GO
SELECT * FROM DATABASE_2..TABLE_1
GO
SELECT * FROM DATABASE_3..TABLE_1
GO

Do this instead

SELECT * FROM DATABASE_1..TABLE_1
UNION
SELECT * FROM DATABASE_2..TABLE_1
UNION
SELECT * FROM DATABASE_3..TABLE_1
GO