Junk dimension approach

View previous topic View next topic Go down

Junk dimension approach

Post  BiSolution on Wed Jun 05, 2013 2:28 pm

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.

BiSolution

Posts : 6
Join date : 2013-05-19

View user profile

Back to top Go down

Re: Junk dimension approach

Post  wonka on Wed Jun 05, 2013 2:50 pm

You could have 10 junk dimensions that hang off the fact

wonka

Posts : 13
Join date : 2011-08-10

View user profile

Back to top Go down

Re: Junk dimension approach

Post  BiSolution on Wed Jun 05, 2013 3:14 pm

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.

BiSolution

Posts : 6
Join date : 2013-05-19

View user profile

Back to top Go down

Re: Junk dimension approach

Post  wonka on Thu Jun 06, 2013 7:38 am

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.

wonka

Posts : 13
Join date : 2011-08-10

View user profile

Back to top Go down

Re: Junk dimension approach

Post  BiSolution on Thu Jun 06, 2013 11:26 am

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

BiSolution

Posts : 6
Join date : 2013-05-19

View user profile

Back to top Go down

Re: Junk dimension approach

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum