Forum Discussion
Does Power BI fully support Azure SQL Data Warehouse?
Hi cbailiss,
What do you mean about "When Power BI is connected to Azure DWH via Direct Query, it presumably won't generate the last line in the query above (the additional join criteria on SessionID), since it only supports one field joins."? Do you mean you write the specific query when connect to the Azure SQL data warehouse? Or you just connect to those three tables, and merge queries in Query Editor?
When you write specific query in DirectQuery, you can use SQL Profile to trace query execution. Also you can try to connect to Azure SQL data warehouse in Import mode, then write the query to see if the results is the same as in SSMS.
If you just connect to three tables, then you can open Query Editor, use Merge Queries to merge PageView and PageEvent twice. For more information, please refer this article: Shape and combine data in Power BI Desktop.
Best Regards,
Qiuyun Yu
Hello v-qiuyu-msft
Thank you for the reply.
>> Do you mean you write the specific query when connect to
>> the Azure SQL data warehouse?
No, not writing a specific query.
>> Or you just connect to those three tables, and merge
>> queries in Query Editor?
No, not joining three tables in the query editor.
I connect to the Azure DWH via a direct query connection.
Then I select those three tables.
I don't join the tables in the query editor.
The three tables come through to Power BI as three tables in the Power BI Model.
In the diagram view in Power BI I create the relationships between the tables. (These relationships can only be between one field in each table).
>> If you just connect to three tables, then you can open
>> Query Editor, use Merge Queries to merge PageView
>> and PageEvent twice. For more information, please
>> refer this article: Shape and combine data in Power BI Desktop.
I don't want to merge the tables in the query editor, because this limits the analysis that can be done in Power BI.
This scenario is about creating a general Power BI model that reflects the way the data is structured in the DWH.
I.e. the Power BI model is acting as a general client to the DWH.
But when used in this way, Power BI desktop cannot generate the additional join criteria highlighted in my first post (since it supports only one field joins) - this means unnecessary data movement steps appear in the query plan in Azure DWH.
Merging tables in the Query Editor is OK when performing a very specific analysis (but even then, I think the join is only on one one field if done via the UI, so the same problem happens).
But merging tables in the query stage doesn't work when wanting to use Power BI as a general DWH client.
Also, importing instead of DirectQuery doesn't work at the scale of data in Azure DWH. I.e. it is far bigger than fits in a Power BI Online model.
Thanks
Chris
