Handling High Cardinality Text Hierarchy

[akarkal]

I am new to modelling fact tables and wanted to reach to figure if high cardinality string columns should be moved to a dimension table ?

I have a fact table which basically boils down to 

1. Type
2. Type1
3. Type2
4. Type3
5. Amt
6. Other Columns..

---

1. Type / Type 1 / Type 2 / Type 3 for an Heirarchy. and they have high cardinality.
2. These columns are used in reports where you make a grid of them and the user navigates through the heirarchy
3. Would it be advisb

[v-jingzhang · Accepted solution]

Hi @akarkal 

If you want to move Type / Type 1 / Type 2 / Type 3 columns to a dimension table, you can create a dimension table in Power Query editor. Duplicate the original query to get a new query. In the new query, select these four Type columns and remove other columns. Then also select these four columns and remove duplicated rows (Remove Duplicates feature). Then you will have a dimension table. 

As this table is disconnected from the fact table, if you want to use it to filter the fact table, you need to create a relationship between them. An option is to create a key column in both tables which combines values in four Type columns and create a relationship on these key columns. You can also create relationships on other columns per your need.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lbendlin]

Define what you mean by "high" .  While you generally want to keep the cardinality low for dimension table you can still have good performance with tens of thousands of rows. With millions of rows - not so much.