Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a dataset, containing products and categories (it is an example).
There are tens of millions of products and thousands of categories.
Every product has a category and other columns with additional data.
I want to build a report, which shows the number of products per category (bar chart).
When a user clicks on a category, I want to show products from that category in a separate table visual.
The problem is that the dataset doesn't fit in 1GB limit.
I decided to split product columns into two tabless: one table contains columns, which are used for aggregation (category) and another table contains columns with additional data about a product.
There is 1:1 relationship between the tables.
I imported the first table to Power BI, because it was small. The second table was too big to be imported, so I decided to use Direct Query to load products from a category, when user selects it in the bar chart.
Unfortunatelly, Power BI ignores selected category and 1:1 relationship and tries to load additional data for all products (>10 millions). Is there a way to tell Power BI to use selected category and generate SQL with WHERE clause?
It seems to work. You may try using SQL Profiler.
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.
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.
Up
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |