Combining ID and Description Columns(State ID , State Name) for better performance during filtering or in where clause

Filter dropdowns on attributes have to show the description in suggestions, but in the SQL should resolve the Key or ID of the dimension in the where clause. This is important to avoid performance issues. The Key columns are index, whereas the description columns are not. Our warehouse is pretty large and houses close to 2 TB data and growing.

Taking a simple example of state table, that has three columns, State_Key, State_Abbr and State_Description. State Description is pulled onto the look as a filter and Tennessee is selected. The SQL generated should be as below for optimal performance.

Select State_Description from State
Where State_Key = 45

Thanks in advance!!

2 Likes

a big +1 here. When we have derived tables and we use templated filters to better performance our derived table sql explodes since we need to join in all the mapping tables to get from the ID to the string values for the many templated filter values we want to use.

2 Likes

Hi @SadhanaAmpalam and @IanT,

I am happy to pass this along to our product team. This seems like a reasonable feature.

Best,

Sasha

1 Like

Came here to request this exact feature. +1 From here as well. This would give us some massive performance gains.

1 Like