Performance Improvement - Where to reduce number of columns?

[Andrew1991]

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?

[v-lionel-msft · Accepted solution]

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".

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.

[Anonymous · Accepted solution]

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 · Accepted solution]

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".

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]

@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.

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

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

[kpost]

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

[asgar_gormalone]

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