How to join application database and Framework databases tables in same data adapter query

If going down the route of cross-joining tables in a single query, then bear in mind that on SAAS / Cloud the Framework database and Application database use different connection strings .  Although this approach may well work on somebody's laptop or local installation, or on-premise server, do ensure it's properly tested if moving to the cloud.

Hi Chand,

you may actually query both app and framework db using a single SQL query against the application db.

E.g. in the example below we are joining journal headers with usernames info. Please note the syntax left join [schemaName].[defaultUser].[tableName] (e.g. OneStream_Framework.dbo.SecUser).

Please note: this approach works if both app and framework db are hosted on the same db server. Furthermore, you may have to use another db schema if the framework db is named differently (you could check this in System tab > Environment). 

select
t.UniqueID
,t.Name As JournalName
,t.Description
,u.Name As PostedUserName
,u.Description As PostedUserDesc
,t.PostedTimeStamp

from JournalHeader t
left join OneStream_Framework.dbo.SecUser u on u.UniqueID=t.PostedUserID

Hello,

You don't give any details on what tables you need to use and what sort of join logic would be needed, so I can only give very general guidance. Nor whether you need to do an inner-join, left-outer join etc..  Or union two tables together?

There might be some Business Rule APIs that combine Framework and Application data , depending on what you need to do.

I have a suspicion that this might have something to do with showing a report that shows user activity, and needing the framework database to lookup the username from the user's uniqueIdentifier (in the Application audit tables).

To join the Framework and Application tables together, assuming I needed to do an inner-join, I would use a Dashboard Data Set Business Rule, and put your Data Adapter on the front of that.

The Business Rule would run the two queries, through separate connections, into two DataTables.
Then you can use features such as LinQ to join the DataTables together in memory.

As Chris mentions, this could be a challenge on Cloud. A different approach would be to pull in data from both tables to a DataTable object, and then join the data. This could be done in a DataAdapter BR.