Fact Measures that don't change for every Dimension value

View previous topic View next topic Go down

Fact Measures that don't change for every Dimension value

Post  xoffender on Thu Jan 23, 2014 9:32 am

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.

xoffender

Posts : 3
Join date : 2014-01-23

View user profile

Back to top Go down

Re: Fact Measures that don't change for every Dimension value

Post  BoxesAndLines on Thu Jan 23, 2014 11:45 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact Measures that don't change for every Dimension value

Post  xoffender on Thu Jan 23, 2014 11:52 am

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.


xoffender

Posts : 3
Join date : 2014-01-23

View user profile

Back to top Go down

Re: Fact Measures that don't change for every Dimension value

Post  ngalemmo on Thu Jan 23, 2014 4:07 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Measures that don't change for every Dimension value

Post  xoffender on Thu Jan 23, 2014 4:11 pm

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

xoffender

Posts : 3
Join date : 2014-01-23

View user profile

Back to top Go down

Re: Fact Measures that don't change for every Dimension value

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