Fact Measures that don't change for every Dimension value

[xoffender]

Hi,

I've spent many hours researching this question and no amount of Googling or trying to find the answer in a book has helped so I'm hoping someone here might be able to help me.

The DW is more involved in this but I'll put the question as simply as I can.

We have 3 fact tables that are of a very similar grain.

FACT 1

Year
[Measures]

FACT 2

Year
Inflation
[Measures]

FACT 3

Year
Inflation
Discount
[Measures]

The question that's driving me insane is could, and should, these tables be merged into a single fact table.

Obviously measures that are currently in FACT 1 that don't have the Inflation or Discount dimensions in common with FACT 3 would repeat for each Year/Inflation/Discount combination. Also if you were only looking for measures not affected by Inflation and Discount you would still need to filter a query on FACT 3 so that you didn't get over inflated results (is that generally an OK thing).

But in summary, should this be 3 fact tables or 1 ?

Any help appreciated or if you can point me a resource where this is discussed that would be great too.

Many thanks.

[BoxesAndLines]

The reason people create aggregate fact tables is to improve performance. If the volume of data is such that the aggregate facts provide no additional performance over the lowest grain fact table, then yes, you can dump the aggregates. That said, the requirement to query a lower grain fact table from an aggregate of that same fact table is a design flaw. It defeats the whole purpose of creating the aggregate.

[xoffender]

Hi,

I think I might have confused the matter.

FACT 1 and FACT 2 aren't aggregates of FACT 3.

For instance FACT 2 has measures that only relate to Year and Inflation but not to Discount and FACT 3 has measures that relate purely to Year/Inflation/Discount.

So my question was is it ok to have Year/Inflation measures in FACT 3 even though there value isn't determined by Discount.

[ngalemmo]

I think where B&L was going with this is you should keep the facts as they are. The idea of combining these facts is to create an aggregate. So, to your question, you could create a fact table which combines the contents of the three, but not replace the three that you have. And, as B&L pointed out, the reason to do something like that would be to resolve performance issues.

Also, as you pointed out, due to the mixed grains, such a aggregate would be difficult to work with. When facts are combined into an aggregate it is always done at a grain common to all facts involved. In this particular case, the only common dimension is year, which doesn't do you much good.

[xoffender]

Thank you both for taking the time to answer and resolve my question, I appreciate it your help.

[Sponsored content]