Search code examples
data-modelingdata-warehouse

how should I manage changes in a dimension field when using big flat table?


Let's say that I have a sale (a fact entry) made by a user (a dimension). initially I would insert this sale+user into my big flat table in my data warehouse. So far sounds very standard

But my doubt is about what should I do when a dimension entry have a change?, for example, the user change his address.

How should I proceed in this scenario?, should I introduce a new sale, using the same data as before, with the user information updated, into my big flat table?

NOTE: I'm speaking about dimensions, etc, but I'm not pretending to use at all an star-schema as an intermediate step (at least for my learning problem)


Solution

  • The user dimension is usually called conformed/shared dimension.

    A conformed dimension is a dimension that has exactly the same meaning and content when being referred from different fact tables.

    In your case :

    But my doubt is about what should I do when a dimension entry have a change?, for example, the user change his address.

    1. If you have a need to store the User's address for analytic purposes, you can opt for Slowly Changing Dimension Type 2 – Row Versioning. Therefore you need to update your fact table and link it to the last version of the User.
    2. If you don't need to track the addresses changes of a User you can use the Slowly Changing Dimension Type 1 – No History. Therefore no need to update the fact rows since they will always point to the unique version of a user