One Fact or two?

View previous topic View next topic Go down

One Fact or two?

Post  dunyamunya on Mon Jun 08, 2015 9:45 am

Hello.

The dilemma we are having is that we have two large tables and need to build a Fact table (or two) out of them. One table is about 270 columns with 3 millions of rows and the other one is about 15 columns with 30 million rows. The wide table is a header (shipments) and the long table is detail (charges). We have split opinions: one says that two need to be combined into one Fact table and the other says to build two Facts: Header and Detail. In either case, both or one will be refreshed nightly. There are Dimensions already defined besides these two.

What would you suggest?

Thank you in advance to all for the help.

dunyamunya

Posts : 3
Join date : 2015-06-08

View user profile

Back to top Go down

Re: One Fact or two?

Post  BoxesAndLines on Mon Jun 08, 2015 10:10 am

You need two. The first fatal flaw of fact design is mixing the grains. And if you end up with a fact table with 270 columns, you've encountered another fatal design flaw, misunderstanding facts and dimensions.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: One Fact or two?

Post  dunyamunya on Mon Jun 08, 2015 11:45 am

That was exactly my point, but it is hard to pass it across considering I am new in the company. Would you expand your thoughts on what would you do? Thank you.

dunyamunya

Posts : 3
Join date : 2015-06-08

View user profile

Back to top Go down

Re: One Fact or two?

Post  ngalemmo on Mon Jun 08, 2015 12:41 pm

B&L is correct IF there are measures for the header data (unique measures, not aggregates of detail measures).

If all measures are at the detail level, there is no reason why you cannot move header level dimensional references down to the detail level, resulting in only one fact table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: One Fact or two?

Post  dunyamunya on Mon Jun 08, 2015 3:04 pm

Yes, I understand that, but having over 250 columns and 30 million rows of data in Fact table would create performance issues when refreshing nightly and pulling into report daily when measures are not required.

Let's say I want to pull shipments for specific origin, but there is no index on it and all that data is part of same Fact table. Having separate Fact Header with that information and Fact Detail with all charges (measures) would help as the table size would be much smaller (3 million vs. 30 million). I hope I make sense here.

dunyamunya

Posts : 3
Join date : 2015-06-08

View user profile

Back to top Go down

Re: One Fact or two?

Post  ngalemmo on Mon Jun 08, 2015 4:07 pm

How do you figure 250 columns? A fact table should primarily be made up of dimension foreign keys and measures, and possibly a handful of degenerate dimension values. If you have a boat load of attributes, eliminate those covered by existing dimensions and create junk dimensions for combinations of most of the remainder. Odds are there may be a few that wind up as degenerate values, but it should not be very many.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: One Fact or two?

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