Junk dimension approach

[BiSolution]

We have fact table which is related to 10 lookup table each contains status or description/columns. Which is the best approach for designing text columns.

1. Keeping the text columns in fact table. But the size of the fact and update if required is concern

2. Grouping all the text column into single dimension by referencing with fact table id. Again fact table count and dimension count will be same. Say if 1M record in fact then 1M record in dimension as this dimension is loaded by referencing fact table.

3. Creating one or more junk dimension by grouping related text columns by considering row counts doesn't exceed the fact table.

Which one will be best approach for huge fable table. I have confusion between option 2 and 3.

[wonka]

You could have 10 junk dimensions that hang off the fact

[BiSolution]

We can include it as 4th option by keeping text column in respective dimension table itself. But at the end, each dimension will have single attributes and more number is surrogate keys in fact table and joins for all these dim.

I prefer option 3 of keeping one or more junk dimnesion. By this way I can reduce the dimension list. Any suggestion to choose out of 4 options now.

[wonka]

I still prefer creating a junk dimension for each, which I think is what you're trying to describe in option 3. Whether it's 10 separate dimensions or you group them together depends on the content, in my opinion. For instance, if 2 of them are (1) favorite food and (2) region of country then it doesn't make sense to group them into a single dimension so they're be left as standalone but say if it's (1) region of country and (2) accent style then maybe it makes sense to group into one.

[BiSolution]

Yes, we both are in same page. As you said grouping depends on content. if not then keep it as standalone dim.

[Sponsored content]