Fact Table - Measure

View previous topic View next topic Go down

Fact Table - Measure

Post  apc on Thu Oct 11, 2012 10:27 am

Hi,

Is there any good reason to add a date type field as a measure in a fact table? (not as a surrogate key, but only as a non-aggregatable measure for descriptive purposes) I don't even think that we could call it a measure.

This fact table holds at least 160 bytes per row with a daily batch load of 50.000.000 rows. Daily history storage of 180 days. Roughly this new fact column would mean a 12,5% daily increase of information comparing to the current daily total of information processed.
This date type field is intended to be used for descriptive purposes only, since it cannot be aggregated in anyway.

I'm trying to understand this modeling team decision. Wouldn't the best approach be to add this date type field as a dimension attribute?
This information will be presented in a SSAS MOLAP cube. In my opinion it would be less expensive to add a new column to the dimension table instead of adding a new column to the fact table. Is there any logical reason not to do this?

Since the front end tool will be Excel through SSAS MOLAP cubes it would be far more easy to present this dimension attribute to the user as a property of the dimension member.

What do you think? Am I on the right track?

Thanks!

apc

Posts : 8
Join date : 2012-10-11

View user profile

Back to top Go down

Re: Fact Table - Measure

Post  ngalemmo on Thu Oct 11, 2012 12:29 pm

There should be a date dimensions somewhere... there is ALWAYS a date dimension. You can always reference that.

But if you don't need attributes about the date, it can exist in the fact table as a degenerate dimension (i.e. just store the date as-is).

As far as SSAS goes, I would assume in either case you can present the date as a dimension member.
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 Table - Measure

Post  apc on Fri Oct 12, 2012 7:10 am

This specific date is an attribute of the product dimension, but they decided to add it as a fact table measure.

I was trying to figure out the rationale behind this decision, because I think it might be a bad one in terms of fact table/overall performance.

Thanks.

apc

Posts : 8
Join date : 2012-10-11

View user profile

Back to top Go down

Re: Fact Table - Measure

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