Short answer: You cannot do direct join from one database to another in Brio Intelligence. However you can query the multiple different database data, and then join it with another query locally.

I don’t know if this is a weakness in Brioquery, but I’ve seen in SQL Management Studio, it does allow you to this easily with a few sql code if there are common data elements. I think that’s the big thing about joining from different database, no common data elements or very little common data elements which could cause some repeated records (rows) similar to a union join.

So maybe it’s a reason Briquery doesn’t have a direct join from one database to another due to this well known issue with common data elements not available. Even in SQL, you would have to do a lot of clean up work, clean the data before it can be properly join between multiple databases.

The way I achieve this in Brioquery is, query database1 first, then query database2 second, you can do more, then create a new query without any database connection – simply right click on the left side panel to pull up local table from query result of database1 and 2, then do another join. One thing to keep in mind is, depend on the volume of the data and how it’s join, this could take long time to run, off course the computer hardware can also impact the performance.

brio-query-from-multiple-database

This is my first post on SQL related contents. I have to be using an old program call Brio Intelligence. Although it’s old but still very useful, it’s like a GUI of MSSQL Management Studio, but a lot better. I must say that I’m GUI person, never really done real sql hand code 🙂

OK so how to combine Date and Time columns into one column in Brio Intelligence. This is similar to using MS Excel to combine cell 1 to cell 2 = date and time (cell 1 = date, cell 2 = time).

In Brio Intelligence. Query level.

1. Click on Add Computed Item
2. *** I just found a quicker way, simply + the two columns, change data type to date, then at the result change the data type to date time again with the time and that’s it 🙂
3. Under Definition, type in these.
   Combined = CAST(Document_Entry_Date AS DATETIME) + CAST(Document_Entry_Time AS DATETIME)Set your Data type = Date
4. Run the query result, then change the combined column Number to Date Category with format mm/dd/yy hh:mm:ss or whatever you prefer.

Voila! now you have date and time properly defined for calculation.

how-to-combine-date-time-into-one-column-using-brio-intelligence