Reduce Cardinality on an Index

[Anonymous]

Are there any tricks to reduce cardinality on an index? 

I have a table thats 8m rows, and the index is 9% of the entire model. I need the index to link to two copies of the table, as I need to manage the columns seperately through RLS (OLS will not work, as it does not allow for users to be in multiple roles like RLS does).

Appreciate the index is unique by design, but have seen tricks around taking max and min % of index, but this wont work as I need to use relationships on the tables once they load.

[jdbuchanan71 · Accepted solution]

@Anonymous 

Is your index an integer value?  Those are smaller to store if you can have the engine store using value encoding.  

Also, disable the 'Available in MDX' property of the column so the model does not build the hierarchy.  You can disable this using Tabular Editor from SQLBI.
https://www.sqlbi.com/tools/tabular-editor/

[Anonymous]

This worked so well. Thanks so much for this - complete gamechanger! 

[Anonymous]

thanks for the reply! yep its an integer. i've not heard of available in MDX though, but looks promising so will try that!