查询Azure SQL数据仓库上外部表的筛选器 [英] Query filters on external tables on Azure SQL Data Warehouse

问题描述

嗨专家，

我们在Azure SQL数据仓库中拥有大量数据（每个TB范围从100 GB到早期TB）的表。我们的报告图层包含位于Azure SQL数据库之上的Azure Analysis Services，该数据库具有聚合数据。对于Azure数据仓库中驻留
的详细数据，我们在Azure SQL DB中创建了外部表。 Azure Analysis Services语义模型使用直接查询来使用外部表访问聚合数据（来自Azure DB）或详细数据（来自Azure DW）。我们面临
的问题是，当我们尝试使用AAS模型访问详细数据（来自Azure DW）时，查询将永远运行。请注意，我们不会尝试获取大量数据。筛选结果仅包含几百行。当
我们在Azure DB外部表上使用SSMS运行相同的SQL查询并应用相同的过滤器时，查询会以秒为单位返回结果。 

We have tables with high volume of data (ranging from 100 GB to early TBs each) in Azure SQL Data Warehouse. Our reporting layer contains Azure Analysis Services sitting on top of an Azure SQL Database which has aggregated data. For the detailed data residing in Azure Data warehouse, we have created external tables in Azure SQL DB. Azure Analysis Services semantic model uses direct query to access aggregated data (from Azure DB) or detailed data (from Azure DW) using external tables. The issue that we are facing is, when we try accessing the detailed data (from Azure DW) using the AAS model, the query runs forever. Please note that we are not trying to fetch a large volume of data. The filtered result contains a few hundreds of rows only. When we run the same SQL query using SSMS on Azure DB external tables with the same filters applied, the query returns results in seconds. 

但是应用了相同的过滤器，AAS模型不会返回任何内容，其后端查询将永远运行。我的猜测是，过滤器不会通过Azure数据库外部表从AAS传递到Azure DW，而Azure数据库会尝试从
外部表中获取所有数据以应用过滤器。我们正在探索在聚合数据（Azure DB）和详细数据（Azure DW）上结合使用通用语义模型的可能性，但这个问题似乎是一个停滞不前。

However with the same filters applied, the AAS model does not return anything and its backend queries run forever. My guess is that, the filters are not passed to Azure DW from AAS via Azure DB external tables and Azure DB tries to get all the data from the external table to apply the filter. We are exploring a possibility to have a common semantic model on the aggregated data (Azure DB) and detailed data (Azure DW) combined but this issue appears to be a showstopper.

有没有人遇到过这个？你能提出什么解决方案/解决办法吗？

Has anyone encountered this? Is there any solution / work around that you can suggest?

提前致谢！

推荐答案

您好MSBI Dev，

Hi MSBI Dev,

如果这是一个关键问题，并且您有支持计划，那么最好的做法是通过Azure支持打开支持请求。 

If this is a critical issue, and you have a support plan, the best course of action is to open a Support Request with Azure Support. 

在开始调查此问题时，我想看看您是否对Azure数据仓库实例执行任何维护？ Azure SQL数据库具有自动调整功能，其中Azure SQL数据仓库需要手动干预，例如

维护统计信息。 

In starting to investigate this issue, I want to see if you are performing any maintenance on your Azure Data Warehouse instance? Azure SQL Database has an automatic tuning functionality where Azure SQL Data Warehouse requires manual intervention, such as maintaining statistics. 

您能否确保所有调整步骤已经实施。如果这些已经解决并且您仍然遇到此问题，您可以从以下
开始
性能文档，用于检查查询以查看Azure Analysis Services是否在两个数据源中执行了确切的查询。 

Can you please ensure that all tuning steps have been implemented. If those have been addressed and you are still having this issue, can you start with the following performance documentation to check the queries to see if the exact query is being executed in both data sources by Azure Analysis Services. 

我希望这会有所帮助！

Mike

这篇关于查询Azure SQL数据仓库上外部表的筛选器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！