CODEWITHSUNDEEP
sql-serverdata-warehousestar-schemaschema-design
WhatsUp
WhatsUp

Reputation: 73

Data warehouse design - Multiple lookup values

Looking for inputs on a data warehouse schema design. Here is the scenario:

I have an Action Table and User Table that is currently joined based on UserId to get the details about the user who did an action.

Action Table:
    UserId   Action
    123      Test001

User Table:
    UserId    UserName
    123       Adam

Now, we have to migrate the users to a new user management system (UMS) and here is how it works:

  1. Existing users will be migrated to UMS and new UserId (let's call this as a Modern UserId and existing UserId as Legacy UserId) will be assigned. So, the new records coming-in for new actions will carry the new UserId.
  2. New users created in UMS will only have Modern UserId and Legacy UserId will be run.
  3. Migrated users will have both Legacy UserId and Modern UserId.

Now, when we do reporting, we have to expose both historic and new action data. Wondering what should be the ideal schema design so we can report both historic & new actions and map them to the right user.

Platform: SQL Server 2016, Analysis Services

Please let me know if you need more details.

Upvotes: 0

Views: 526

Answers (2)

Alexis.Rolland
Alexis.Rolland

Reputation: 6353

Agreed with the previous answer. When your upstream team performs the migration to UMS, they should preserve in some way a mapping between Legacy User IDs and Modern User IDs. Downstream in the warehouse, I suggest that you keep both IDs in your User dimension table but generate a surrogate key in this table which will serve as a primary key (it can be just an incremental integer). This way you can use the surrogate key as a foreign key in your Action fact table whether the User is a modern or legacy one.

Here is my proposal of data model design for your tables:

DIM_USER
- USER_KEY (pk)
- USER_ID
- USER_ID_LEGACY
- USERNAME
- ....

DIM_ACTION
- ACTION_KEY (pk)
- ACTION
- ....

FACT_ACTION
- USER_KEY
- ACTION_KEY
- ....

Upvotes: 0

Random_User
Random_User

Reputation: 363

You have not given us any additional information as to the details of how this is going to be done, so the sql-server tag doesn't really help us here. This is more of a modeling question.

When you speak of a new id for a column there has to be some way during the creation of said key to ensure integrity, that process will dictate to some degree the methods you have to provide the solution.

The User table looks to be a table of unique values and where the creation of the 'Modern Key' is created, if you can edit this table, you should add the 'legacy key' here. This becomes your mapping table, mapping tables do not have to be a separate object.

Upvotes: 1

Related Questions