How to combine data from two different databases and keep the mode Direct query?

[anuragccsu]

I've a problem where I'm asked to keep my model in direct query. I'm pulling data from MS SQL Server DB and Oracle SQL DB in DIRECT query mode.

I need to combine the data pulled in direct query from MS SQL Server DB and Oracle SQL DB, and I used Power Query to do it, but when i see the combined(Append) data in the model, it's shown in IMPORT mode!!!

Is there a way out?

Actual requirements - I've two databases via MS SQL Server DB and Oracle SQL DB. I need to filter the combined data from the two data sources via a slicer and pass those slicer selected values (as query parameter) to the native SQL queries(configured in direct query mode) of the two databases. This part is working fine for individual queries pulled in Direct query mode, whatever i select in the slicer, the direct queries are receiving the value using bind parameter and pulling only data for the selected slicer value! However all this fails for the combined (Appended) query, i mean it shows no data and only show the default value of the parameters which is passed as part of parameter configuration.

I need to fitler the combined table in direct query, However Power Query is changing the mode to Import when i combine (Append) individual MS SQL Server DB and Oracle SQL DB queries which are configured in direct query mode.

@DirectQuery, @powerquerybabe , @OmidR and anyone expert in Power Query please help?

[Akash_Varuna]

Hi I think You could use Power BI native query which will enable you Query Folding Option instead of using Power Query for merging you could directly do it there in Your Case Oracle or Sql Server

While for the second one i think Power BI has Composite model option which you can use but i think it is limited From my Knowledge these two options would keep it in Direct Query Mode

[Anonymous]

Hi @anuragccsu ,

Power BI supports composite models, which allow you to combine DirectQuery and Import mode data. However, when you append data from different sources, it often defaults to Import mode. To maintain DirectQuery, you might need to use relationships instead of appending the tables directly. Or, for security reasons, you can switch unimportant data sources to import mode.

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

Another solution is to create custom SQL views in one of your databases that pull data from the other database. For example, create a view in SQL Server that pulls data from Oracle using a linked server. Then, use this view in DirectQuery mode.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anuragccsu]

@Anonymous thanks for your response. I need a combined(appended or unioned) table which i wanted to filter. This means that single column coming from two different databases needs to be filter simultaneously for values in those table. I think relation won't work here as it'll mean to get two columns from the two tables and put them side by side via a relation. I need values in a single column from both the tables. Say in your example SalesID is avaiable in both tables coming from two different data sources, a relation will only help to put them side side by but not as union! That's what i tried and understand now.

Composite model is something which means your model has tables in direct query as well as import mode. My requirements was different.

Thanks for your resonse. 

[BeaBF]

@anuragccsu Hi!

Use DAX to calculate a third combined table, like:

CombinedTable =
UNION(
SELECTCOLUMNS(MSSQLTable, "Column1", [Col1], "Column2", [Col2]),
SELECTCOLUMNS(OracleTable, "Column1", [ColA], "Column2", [ColB])
)

BBF

[anuragccsu]

@BeaBF thanks for response, however DAX also doens't help either!

I created the required columns using DAX and selected columns but no value in the combined union DAX table.