more than calendar keys on fact table

View previous topic View next topic Go down

more than calendar keys on fact table

Post  bkj123 on Mon Sep 19, 2011 12:55 am

Hello. We have a monthly fact table that includes a column named 'calendar date'. This date refers to the last date of a given month. Since this date is not always the last calendar date of a given month (e.g. 8/31/2011), users always join to the calendar table (via 'calendar date') to select the the month_id column in the format of CCYYMM. For example, 'August 2011' may end on '9-3-2011' (i.e. calendar date) but month_id = '201108'. Sometimes, users will get more from the calendar table besides month_id (e.g quarter) but 99% of the time the only reason they make the join is to get the month_id.

What do you think of putting the month_id column on the fact table either physically or through a view? Users would like it (one less join on almost every query). Some designers/architects don't like it. They ask 'if you do that, then when to we say no more? When users want the quarter on the fact? When they want the previous month?'. Some designers say the key (calendar date) should be meaningless so the join is more justified.

This has come in multiple environments with different outcomes and reasons? Are there guidelines or preferences? Thank you.

bkj123

Posts : 2
Join date : 2009-09-02

View user profile

Back to top Go down

Re: more than calendar keys on fact table

Post  ngalemmo on Mon Sep 19, 2011 1:26 am

In a word, don't.

Almost all row-based RDMS systems have optimizations built in to handle star queries. There really isn't the kind of cost you presume to occur in performing joins. What hurts more is a bloated fact table. Columnar DB architectures work differently and are not sensitive to table width.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: more than calendar keys on fact table

Post  bkj123 on Mon Sep 19, 2011 2:07 am

Thank you for the quick reply. Can you expand on 'What hurts more is a bloated fact table'? Understanding this and being able to communicating this to users would help.

I'm not concerned about the cost of the join in query performance since as you said it really is not an issue. I'm concerned about cost in user perception and acceptance. Users view typing this join in every query as a nuisance. They view this as a simple request to add one more column that they use in every query, doesn't impact query performance, is more intuitive, is easier to write, and simpler to read.

When I consider this information from their view point, I feel like I'm standing on a soapbox of principle and not dealing with reality and what the users really want. Knowing more about the impact of a bloated fact table will certainly help. Again, thank you for the reply.

bkj123

Posts : 2
Join date : 2009-09-02

View user profile

Back to top Go down

Re: more than calendar keys on fact table

Post  ngalemmo on Mon Sep 19, 2011 3:53 pm

bkj123 wrote:I'm not concerned about the cost of the join in query performance since as you said it really is not an issue. I'm concerned about cost in user perception and acceptance. Users view typing this join in every query as a nuisance. They view this as a simple request to add one more column that they use in every query, doesn't impact query performance, is more intuitive, is easier to write, and simpler to read.

What tools are they using? If they are coding SQL by hand, you could define a view which contains the necessary joins and date columns. If they are using a metadata based tool (BOBJ, Cognos, Microstrategy, etc...), what difference does it make?

The issue with wide fact tables is that, most of the work in a typical query is spent locating rows in the fact table. Smaller (thinner) facts mean less I/O against the slowest piece of equipment (disk drives). This is not to say a wide fact table is bad, it all depends on what the requirements are. It is just that you should avoid unnecessarily adding to the width.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: more than calendar keys on fact table

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