Direct Query - SQL - Modeling - Limitations?

[ACPBI]

Hi All,

My company and I are new Power BI users and I would like to have someone please confirm limitations in Power BI using Direct Query based on the below scenario please. 

I have been asked to create a Power BI Report that includes real-time data.  This is not possible with Import Mode due to the limitations on refreshes and timespan across all shifts.  To accomplish this I am using Direct Query.

I am also being asked to pull data from 2 different servers.  In SQL these 2 servers do not have a linked server.  So, in sql I can not perform a join of any sort on the data that I am being asked to report on in Power BI.  In Power BI I have specifically been directed not to use queries but to use the data model only.  

I created a model that appears to join the tables in Power BI.  I have tableA.OrderID joined to tableB.OrderID.  I did set it as a OneToOne Relationship.  It only provides the option for cross-filter direction "both".  Also, 'Assume referential integrity' is grayed out and not an option.  My "FROM" table is tableA.  It is a new data source with relatively few records.  TableB contains years of data. 

I did read about merging queries here: https://learn.microsoft.com/en-us/power-query/merge-queries-overview My understanding is this is not an option with Direct Query as it would be for Import Mode.

When I create visuals in Power BI I am not seeing data restricted to only orders that exist in TableA where the OrderID matches when I attempt to pull in the needed field.  

I have requested that we either have a linked server added between the servers or that we add the single field value we need to our new data source.  I have not received approval yet and am wondering if my understanding on any of this is incorrect and/or if there may be another option.

Thanks in advance for any time and feedback!

[rajendraongole1 · Accepted solution]

Hi @ACPBI - Your understanding of Power BI's limitations in DirectQuery mode and how relationships behave when working across two different servers is largely correct. 

In DirectQuery mode, Power Query transformations like merging are limited because the data isn't loaded into memory but queried from the source.

If you can get approval for a linked server, the issue can be resolved by writing a SQL query that joins the two tables directly on the database side.

This ensures Power BI works with a single data source and can query it efficiently.

If possible, implement linked server . These approach simplify your data model and resolve the core limitation of cross-server joins. If those are not approved, consider combining the data into a single source via an ETL process or pre-aggregations.

Hope this helps.

[ACPBI]

Thank you for making time to reply @rajendraongole1   I am discussing this with my team.  

In Crystal Reports we are able to access the data using connection strings.  If you can provide any insight into this option please share.  Either way, yes, you have been very helpful!