March 4, 2015 at 7:46 am
We are figuring what type of data constitutes master data for our organization. At present we are dealing with just one entity - Person.
A person can be an applicant when we first become aware of them. After the application process they can become nothing (they don't progress further), or they can become an employee, student, intern etc).
So now I have a person entity.
I have a related PersonType entity.
It would also be good to store the duration a person spent as a particular PersonType, which is pretty much an SCD.
Besides fact data, this is all looking very much like a data warehouse, and honestly, something I could much easier without trying to shoehorn it into MDS.
Am I taking a wrong approach here? Do I need to look at the problem in a different way?
March 4, 2015 at 7:57 am
winston Smith (3/4/2015)
We are figuring what type of data constitutes master data for our organization. At present we are dealing with just one entity - Person.A person can be an applicant when we first become aware of them. After the application process they can become nothing (they don't progress further), or they can become an employee, student, intern etc).
So now I have a person entity.
I have a related PersonType entity.
It would also be good to store the duration a person spent as a particular PersonType.
Besides fact data, this is all looking very much like a data warehouse, and honestly, something I could much easier without trying to shoehorn it into MDS.
Am I taking a wrong approach here? Do I need to look at the problem in a different way?
In my opinion, MDS provides a good platform to store and maintain your "Person" entity. But it is still going to be in normalized form, and would probably be the source of your data warehouse where you'd track the changes over time.
Hope this makes sense and helps?
March 4, 2015 at 8:01 am
Martin Schoombee (3/4/2015)
winston Smith (3/4/2015)
We are figuring what type of data constitutes master data for our organization. At present we are dealing with just one entity - Person.A person can be an applicant when we first become aware of them. After the application process they can become nothing (they don't progress further), or they can become an employee, student, intern etc).
So now I have a person entity.
I have a related PersonType entity.
It would also be good to store the duration a person spent as a particular PersonType.
Besides fact data, this is all looking very much like a data warehouse, and honestly, something I could much easier without trying to shoehorn it into MDS.
Am I taking a wrong approach here? Do I need to look at the problem in a different way?
In my opinion, MDS provides a good platform to store and maintain your "Person" entity. But it is still going to be in normalized form, and would probably be the source of your data warehouse where you'd track the changes over time.
Hope this makes sense and helps?
That makes sense.
To be sure an example would work as follows:
Person A is stored in MDS with an attribute of Applicant.
When they change to Employee, the attribute is updated to Employee, and the DWH then worries about tracking the change.
i.e. MDS is a Snapshot in time of all persons and their type, but the DWH can show their history.
March 4, 2015 at 8:06 am
Yes, MDS is your typical line-of-business application and used to store and maintain the current version of that entity. In your data warehouse, you would make use of type 2 dimension changes to track the history of that entity over time.
Edit: To add to that, the whole premise of MDS is to maintain entities (like employees) in one place. You would then use MDS as a source to all your other applications as well as reporting systems (like a data warehouse).
March 4, 2015 at 8:17 am
winston Smith (3/4/2015)
Martin Schoombee (3/4/2015)
winston Smith (3/4/2015)
We are figuring what type of data constitutes master data for our organization. At present we are dealing with just one entity - Person.A person can be an applicant when we first become aware of them. After the application process they can become nothing (they don't progress further), or they can become an employee, student, intern etc).
So now I have a person entity.
I have a related PersonType entity.
It would also be good to store the duration a person spent as a particular PersonType.
Besides fact data, this is all looking very much like a data warehouse, and honestly, something I could much easier without trying to shoehorn it into MDS.
Am I taking a wrong approach here? Do I need to look at the problem in a different way?
In my opinion, MDS provides a good platform to store and maintain your "Person" entity. But it is still going to be in normalized form, and would probably be the source of your data warehouse where you'd track the changes over time.
Hope this makes sense and helps?
That makes sense.
To be sure an example would work as follows:
Person A is stored in MDS with an attribute of Applicant.
When they change to Employee, the attribute is updated to Employee, and the DWH then worries about tracking the change.
i.e. MDS is a Snapshot in time of all persons and their type, but the DWH can show their history.
Correct. MDS is not concerned with history, that's the job of the data warehouse 🙂
MDS does keep versions though, so you can track what values an entity has had over the course of history. But this is more of an auditing purpose, not a reporting purpose.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2015 at 8:35 am
I have one more scenario to expand this further:
SourceSystem has Persons of type Applicant and test scores for each applicant.
The persons would be pushed into MDS for sure, but MDS is not a place for scores (IMO).
So any system downstream that wants to use at Applicants and scores will have to:
1. pull the applicant data from MDS.
2. Once they have the Applicant data, they then pull the score data from the source system (or DWH if present).
is that the correct way to approach that problem?
March 4, 2015 at 8:41 am
winston Smith (3/4/2015)
I have one more scenario to expand this further:SourceSystem has Persons of type Applicant and test scores for each applicant.
The persons would be pushed into MDS for sure, but MDS is not a place for scores (IMO).
So any system downstream that wants to use at Applicants and scores will have to:
1. pull the applicant data from MDS.
2. Once they have the Applicant data, they then pull the score data from the source system (or DWH if present).
is that the correct way to approach that problem?
Yip.
March 4, 2015 at 8:47 am
Thanks guys, awesome advise as always!:-D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply