cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
barabum Frequent Visitor
Frequent Visitor

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?

 

 

 

4 REPLIES 4
barabum Frequent Visitor
Frequent Visitor

Re: Analyzing Big Data

Up

Community Support Team
Community Support Team

Re: Analyzing Big Data

@barabum,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
barabum Frequent Visitor
Frequent Visitor

Re: 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.

 

barabum Frequent Visitor
Frequent Visitor

Re: Analyzing Big Data

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.

 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 2,928 guests
Please welcome our newest community members: