Analyzing Big Data

[barabum]

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?

[v-chuncz-msft]

@barabum,

It seems to work. You may try using SQL Profiler.

[barabum]

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.

[barabum]

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.

[barabum]

Up