Reuse or replicate dimensions?

View previous topic View next topic Go down

Reuse or replicate dimensions?

Post  vjsp90 on Tue Jul 14, 2015 9:21 pm

Hello,

I have several fields on my fact table that make reference to the completeness of different data (i.e. personal data, test data ...) and they can take three values (No data, complete, incomplete). What would be a better practice, to reuse the same dimension table for all this fields, or to have separate dimensions tables?

On one hand, separate dimensions allow you to modify the values for one field if it was necessary, but on the other hand, if this values are not modified (what is the most probable), you avoid to have identical tables.

I don't know what the way to go is, to reuse as many table as possible or to have one tabld for each dimension (field) (except for thr time dimension).

Thank you.

vjsp90

Posts : 4
Join date : 2015-05-10

View user profile

Back to top Go down

Re: Reuse or replicate dimensions?

Post  nick_white on Tue Jul 21, 2015 3:43 am

Why not add this attribute to the dimension that is having its completeness measured?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Reuse or replicate dimensions?

Post  vjsp90 on Tue Jul 21, 2015 1:36 pm

I think I don't understand your answer.

The fact table have have a lot of fields with foreign keys that make reference to the dimension tables. I.E. personal data (-1,0,1) and in the dimension table (-1 -> No data, 0 -> Incomplete, 1 -> Complete).

If you are suggesting degenerate dimensions, this is only the easiest example I have, so it's not a real option.

vjsp90

Posts : 4
Join date : 2015-05-10

View user profile

Back to top Go down

Re: Reuse or replicate dimensions?

Post  ngalemmo on Tue Jul 21, 2015 6:57 pm

Nick is suggesting that completeness is an characteristic of the dimension. In other words, you store the completeness flag on each dimension table.

Storing as a degenerate on the fact doesn't appear to make sense. Could you elaborate why you think it belongs on the fact table?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Reuse or replicate dimensions?

Post  vjsp90 on Wed Jul 22, 2015 10:58 am

Ok. There's no a dimension for these data. The only thing we know it's if something is complete, but we don't have such data in the database. It's just for statatistics, so we don't have the real data in this model.

vjsp90

Posts : 4
Join date : 2015-05-10

View user profile

Back to top Go down

Re: Reuse or replicate dimensions?

Post  ngalemmo on Wed Jul 22, 2015 2:02 pm

In that case, a degenerate value on the fact should be fine.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Reuse or replicate dimensions?

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