The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Reference External DB and Application DB in one SQL Query
Hello, I am writing a SQL Query that references tables in different databases, one that exists in the External database (BI Blend) and one that exists in the Application database. I am having troubl...
As Teresa and Daniel have pointed out. You can achieve this with a Dashboard DataSet BR.
You would need to execute both sql commands separately e.g datatable1 is from application database and datatable2 is from framework database
You can then use the DataTable Merge method (assuming both datatables have the same columns as per your union statement) to merge the two datatables.
Then return the merged DataTable to your Data Adapter.
https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.merge?view=net-9.0&redirectedfrom=MSDN#overloads
Hope this helps
As T_Kress mentioned the way to do this is by utilising a Business Rule. You will need to change the Data Adapter to Command Type: Method, Method Type: Business Rule instead of SQL. Can't remember if it gives an example of the method query syntax there but if not it should be in the docs and forums.
Your business rule will be a Dashboard Data Set business rule which returns a datatable (or is it a DataSet? You can query each database separately into data tables and then join them into a single datatable using .NET commands.
I think you should be able to search forums and the web for how to achieve the various bits and pieces from above. The docs might have some examples particularly around the method queries.
Regards,
Daniel
Thanks, Daniel! This was the route I began to go with. I will follow up if I have any further questions.