To put atribute in both tables: in a fact and in a dimenzion

View previous topic View next topic Go down

To put atribute in both tables: in a fact and in a dimenzion

Post  MK on Mon Feb 04, 2013 5:50 am

I would like to ask if it is in practice to add attribute in dimension and
in the FACT table. I have such data to be collected on many reports.

Benefit also, because I have no information on the source of expiration. From FACT table data can be obtained, which is then considered.
Thank you, K

MK

Posts : 16
Join date : 2013-02-04
Location : Slovenia

View user profile

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  ngalemmo on Mon Feb 04, 2013 11:26 am

That is unusual. But it sometimes done building aggregates. Usually you just include the dimension's FK and leave it at that. You may also consider isolating some attributes in its own dimension.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  MK on Wed Feb 06, 2013 2:49 am

Ngalemmo tkank you for your replay.

I am fairly new in the construction of data warehouses and I appreciate your answers. Last I read, when someone
wrote, it is good if a lot of searching for some data that is better to put it in both tables. I have a case for example gender to which they will be working a lot of analysis. I have a dimension of person and I am in doubt or just give gender in persons dimensional table or a foreign key in fect table. Then I also have a gender dimension.

I would ask for another opinion about the use of foreign keys. I find it useful also in terms of maintenance. My co-worker wants that we use natural keys for data that it usually do not change and are integer or small integer.

You have a lot of experience and you will know to advise what is about the null values ​​in the FACT table. Is it better to have null values in fact tables and this is then regulated in later analyzes. Or is it better to provided for null keys titles in dimensions like: Data does not exist


thank you very much, MK

MK

Posts : 16
Join date : 2013-02-04
Location : Slovenia

View user profile

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  ngalemmo on Wed Feb 06, 2013 3:19 am

You don't need to put one value in its own dimension, but it is not always a good idea to leave them as a degenerate dimension.

One may, instead, construct a 'demographic' dimension which contains sex, as well as other useful information such as age and income level. This doesn't result in many rows in the dimension, yet consolidates like information into a more compact form. You don't want to aggregate the data you are storing, but at the same time you don't want a very wide fact overrun with minor dimensional references.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  MK on Thu Feb 07, 2013 5:27 am

Helou,

Thanks for your replay and all advicess.

In previous post I ask about a foreigen keys and null values. What can you advice about that. Thank you very much,

Best regars, Mk


MK

Posts : 16
Join date : 2013-02-04
Location : Slovenia

View user profile

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  ngalemmo on Thu Feb 07, 2013 7:46 am

Foreign keys should never have null values. Attributes may, but never a FK.

If you query a fact table that has null FKs you will need to do outer joins to the dimension. If you don't, instances where a null FK exists will eliminate that fact row from the query. This will often cause invalid results.

Best practice is to include an 'unknown' member in the dimension table, with appropriate attribute values, and reference that row rather than leave the FK null. This will allow you to perform inner joins and get consistent results.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  MK on Thu Feb 07, 2013 6:21 pm

Helou,

I did not mean the null values ​​for fk keys. I mean to ask two question, but I maybe wrote wrong. Question is:
1. Use Natural or foreign keys. I mean especially on this, because at work colleague wants to attributes, most of which do not change and are integer, that we have natural keys.
2. What do you think about, that to have Null values ​​in the degenerated dimensions, dates or metrics?

Thanks again, best regards M

MK

Posts : 16
Join date : 2013-02-04
Location : Slovenia

View user profile

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

Post  ngalemmo on Thu Feb 07, 2013 9:37 pm

#1: The question should be natural or surrogate key. Any key can be a foreign key. In a dimensional model, use of surrogate keys is best practice. The term 'surrogate' means substitute. You use a meaningless integer as a substitute for a natural (or business) key. It provides key stability (business may change a natural key) and performs better as an index (its small).

#2: As long as it isn't a key, a null value can be acceptable. Allowing nulls is more an issue with user interfaces. Locating null values requires a different predicate syntax.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

To put atribute in both tables: in a fact and in a dimenzion

Post  MK on Fri Feb 08, 2013 5:54 pm

Thank you very much, you help me a lot.

Best regards, MK

MK

Posts : 16
Join date : 2013-02-04
Location : Slovenia

View user profile

Back to top Go down

Re: To put atribute in both tables: in a fact and in a dimenzion

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