CODEWITHSUNDEEP
sqloracle-databasebrio
Murat
Murat

Reputation:

Access to multiple data sources using Oracle Brio

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?

Upvotes: 0

Views: 827

Answers (2)

Trevel
Trevel

Reputation: 811

If you're using different OCE files for each source -- as you probably are -- then there's no easy way to do this. You'll need a separate query for each OCE file

If all your databases can be found under one OCE file, you can use the UNION trick; more to point, in Brio it's found as "Append Query". Just make sure you build the query identically to the first query in terms of what it returns; all the column headings will be from it.

If you need to consolidate the different queries into a single results section, the easiest -- well, only -- way that I've found to do it using only standard Brio functions is to join each set through a full outer join on every field and then coalesce each field together ... but this is horribly inefficient and will not finish for larger files. You'd probably be better off sorting the files and writing a javascript routine to parse them together, or doing the whole thing outside of Brio in the first place.

Upvotes: 1

Noah
Noah

Reputation: 15340

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

Upvotes: 1

Related Questions