Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GSE
Resolver I
Resolver I

Memory Usage spikes when adding dimension column to table visual

Hi all,

 

I have a table with 2,3million rows and a many-to-one relationship with a dimension table (dimension: one, fact table: many). 

 

I created a table visual with columns from the fact table and everything works as expected but as soon as I drag a column from the dimension table into the table visual, then memory usage spikes, the visual takes 25 seconds to load and if I drag a column from another dimension into the same visual the visual loads in 200 seconds and memory usage goes up to 50 GB! 

 

When I add a calculated column in the fact table with RELATED ( column of dimension table) then there is no issue, so the relationship seems to be working fine. However, the whole point of having a relationship is not to have to add calculated columns in the fact with columns from the dimension table...

 

Can you help me understand what the problem is?

 

Best regards

GSE

1 ACCEPTED SOLUTION
GSE
Resolver I
Resolver I

The problem was four columns which were basically place-holders. Three columns should have headers but no entries (blank) and one column should have a header but all entries should say FALSE. In order to do that, I created three blank measures and one false measure. When I add a single blank measure as a column, then the visual shows no rows anymore. However, when I add the false measure as a column then it shows all rows again. Pretty strange behaviour as far as I can see. Would be nice to be able to create measures rather than calculated columns for this purpose but the loading times are just too long. Without the blanks and false column the visual loads in no time just as expected. 

 

Thanks for the suppoert @lbendlin 

View solution in original post

5 REPLIES 5
GSE
Resolver I
Resolver I

The problem was four columns which were basically place-holders. Three columns should have headers but no entries (blank) and one column should have a header but all entries should say FALSE. In order to do that, I created three blank measures and one false measure. When I add a single blank measure as a column, then the visual shows no rows anymore. However, when I add the false measure as a column then it shows all rows again. Pretty strange behaviour as far as I can see. Would be nice to be able to create measures rather than calculated columns for this purpose but the loading times are just too long. Without the blanks and false column the visual loads in no time just as expected. 

 

Thanks for the suppoert @lbendlin 

I want to understand your solution because I am having the same issue. But your explanation is difficult to understand

 

lbendlin
Super User
Super User

Maybe you can show the data model and indicate where the offending column comes from in relation to the columns that are already in the visual.  Starts to sound more like a "simple" cartesian product.

 

You can use DAX Studio to look at the query plan before and after adding the column

GSE
Resolver I
Resolver I

@lbendlin 

All tables are in import mode. Should I still have a look at the query log? How do I do that? SQL Profiler?

lbendlin
Super User
Super User

I assume this is Direct Query?  Potentially in a Mixed mode with other datasets as source?  Have a look at your query log... you may find that your mixed mode creates monster queries because your key columns linking the data model have high cardinality.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.