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
Andrew1991
Frequent Visitor

Performance Improvement - Where to reduce number of columns?

Hello!

 

Currently I have create a file that pulls in data from a singular source, multiple tables.

Some of these tables have a huge amount of columns that I dont need (I need 8 of the 76 columns).

I've got another file that has a table with 7m+ records but a few columns (5) and all the visuals/slicers respond really quickly, vs 10k records with 70+ columns where there seems to more of a lag time.

 

Does removing unneeded columns through the query/advanced editor helps with the performance of the report?

 

Reducing the columns in the orginal data source can be done but through a massive bureaucratic process, so I am hoping to do it at this later step. The performance of the the data refresh doesn't matter as much in this instance, the critical component is the performance of the visuals.

 

Anyone run into this situation before?

 

2 ACCEPTED SOLUTIONS
v-lionel-msft
Community Support
Community Support

Hi @Andrew1991 ,

 

Dropping unnecessary columns to reduce the data model is indeed a better way to improve query and refresh performance.

You can delete unnecessary columns in Edit Queries:

Ctrl + left mouse button to select multiple columns, then, Right-click and select "Remove columns".

f1.PNG

 

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Processing time will decrease if you remove the columns you don't need. Also memory usage will decrease. But in most cases the performance of the report should not be affected that much by the number of columns since the SSAS tabular engine works on columns and each column is stored separate (not as in a normal database where data is stored by row).

 

There might however be a few cases where performance is decreased when adding more columns:

- When computer is low on memory

- When you have DAX computations that references the whole table, for eample VALUES('Table')

 

But the short answer is: Always get rid of all columns you don't need!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Processing time will decrease if you remove the columns you don't need. Also memory usage will decrease. But in most cases the performance of the report should not be affected that much by the number of columns since the SSAS tabular engine works on columns and each column is stored separate (not as in a normal database where data is stored by row).

 

There might however be a few cases where performance is decreased when adding more columns:

- When computer is low on memory

- When you have DAX computations that references the whole table, for eample VALUES('Table')

 

But the short answer is: Always get rid of all columns you don't need!

v-lionel-msft
Community Support
Community Support

Hi @Andrew1991 ,

 

Dropping unnecessary columns to reduce the data model is indeed a better way to improve query and refresh performance.

You can delete unnecessary columns in Edit Queries:

Ctrl + left mouse button to select multiple columns, then, Right-click and select "Remove columns".

f1.PNG

 

Best regards,
Lionel Chen

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VasTg
Memorable Member
Memorable Member

@Andrew1991 

 

Reducing the columns is a great performance improvement. Remove them in Power Query immediately after Source or Navigation Step.

 

Home->Choose Columns

Select only the desired columns.

Repeat the process for all the tables.

image.png

 

There might be many threads in the forum talks about performance improvements.

 

If it helps, mark it as a solution.
Kudos are nice too.

Connect on LinkedIn

If I remove the columns as a last step instead of right after the Source, it would still help with refresh speed, right?

 @Andrew1991 I also have same question can you please answer that

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.

Top Solution Authors