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

[MK]

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

[ngalemmo]

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.

[MK]

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

[ngalemmo]

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.

[MK]

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

[ngalemmo]

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.

[MK]

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

[ngalemmo]

#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.

[MK]

Thank you very much, you help me a lot.

Best regards, MK

[Sponsored content]