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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.