One Fact or two?

[dunyamunya]

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.

[BoxesAndLines]

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.

[dunyamunya]

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.

[ngalemmo]

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.

[dunyamunya]

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.

[ngalemmo]

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.

[Sponsored content]