CODEWITHSUNDEEP
data-warehouseolapstar-schema
mingchau
mingchau

Reputation: 470

Should we put all the fields related to the `user` in a `dim_user` table in a data warehouse?

Considering there is a data warehouse contains one fact table and three dimension tables.

Fact table:

Dimension tables:

All the data of these tables are extracted from our business systems.

In the business system, the user has many attributes, some of which could change upon time(mobile, avatar_url, nick_name, status), some others won't change once the record is created(id,gender,register_channel).

So generally in the dim_user table, which fields should we use and why?

Upvotes: 0

Views: 250

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12989

Dim_User should have both changeable and unchangeable fields. In denormalized model, it is preferrable to keep all the related attributes of a dimension in a single table.

Also, it is preferrable to keep all the information available about user in the dimension table, as they might be used for reporting purposes. If they won't be needed for reporting purpose, you can skip them.

If you want to keep the history of change of the user, you can consider implementing slowly changing dimensions. Otherwise, you can update the dimension attributes, as and when they change. It is called SCD Type I.

Upvotes: 1

Related Questions