Forum Discussion

barabum's avatar
barabum
Frequent Visitor
7 years ago

Analyzing Big Data

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?

 

 

 

Need Help

4 Replies

    • barabum's avatar
      barabum
      Frequent Visitor
      7 years ago

      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's avatar
      barabum
      Frequent Visitor
      7 years ago

      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.