Same field Description but 2 different businiss meanings in data warehouse

Radi Soufan 6 Reputation points
2021-03-02T17:45:19.007+00:00

Hello,
I have a new Datawarehouse for a labor force census...
I have a lot of questions in the survey... sometimes a question X that is represented in my data warehouse as
column X which for example has an answer inside its related Dim (Yes or No Question) ...
e.g. in round 1 of the census, the total persons answered Yes: 500 and No: 200, and this question was asked for all persons in the round
until now everything is ok ...

next round they change the business role for question X to be asked only for employed persons
so in this round will be answered as Yes: 10 and No: 3

so what should I do now... Question X has the same description but with 2 totally different business meanings
so I can't describe the total answers for Yes to be decreased by 490 in the second quarter of the Year...
Should I cut the time series for Question X to be
a Column X1 for Answers in the first round and
a second Column X2 answers in the second round?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,797 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vahid Ghafarpour 17,870 Reputation points
    2023-07-27T21:37:38.71+00:00

    I think creating two separate columns, X1 and X2, for the answers in the first and second rounds, respectively, is an excellent approach to handling this situation

    0 comments