Analyzing Big Data

SQL Profiler is deprecated and doesn't work with Azure SQL Server.

I used sys.dm_exec_query_stats and sys.dm_exec_sql_text views to see actual ueries. They look like this one, where <ListOfColumns> are other columns:

SELECT TOP (1000001) 
[t12].[CompositeKey],<ListOfColumns> 
FROM ( (select [$Table].[CompositeKey] as [CompositeKey], <ListOfColumns> ) AS [t12] 
GROUP BY [t12].[CompositeKey],<ListOfColumns>

There is no WHERE clause in the query, so it tries loading all data.

Table component shows this error:

Error Message:
The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

Is it possible that PowerBI doesn't support filtering on SQL server side when there is a 1:1 relationsip?

For example, there is one to many relationship between Categories and Products and one to one relationship between Products and ProductsDetails.

User selects a category.

Expected behaviour:

- PowerBI filters Products by the category;

- using CompositeKey from filtered products, it loads data from ProductsDetails table (1:1 relationship).

Current behaviour:

- PowerBI loads data from ProductsDetails table for products from all categories and fails.