aim of my project is to do Reference data automation.I have data in Sql server (Database 1 and Database 2).The data is is not in proper way that we can access directly. So first I have to do the analysis from the database [login to view URL] I have categorized the data into valid and invalid after [login to view URL] i sent it to SME's for review the [login to view URL] that started with grouping the data for the correspond groups and sub groups in a seperate table called Group [login to view URL] steps followed for both [login to view URL] Again send it for review whether correct grouping done or not. After that collected distinct records(reference data id's ,groups and sub groups) from both data databases and inserted into the global [login to view URL] any database came like database 3 in future again no need to do the analysis on that database for that one i had created a stored procedure it will do the data categorization,grouping and maintaining in the global table.
FEATURES :
[login to view URL] are created on Global table to trigger all updates at all group table levels,instead of going to each group table and updating manually.
[login to view URL] procedure with parameter as source table on which we want to do the analysis.
Example :
Data id =1 has code value as 'Male' in Database 1
Data id =1 has code value as 'M' in Database 2
There is a data inconsistency from those database tables but the meaning is same. So i have fixed that problem by above steps.
Finally
Group tabel has following columns
[login to view URL] Name
[login to view URL] Group
[login to view URL] Id
[login to view URL] Flag
Global Table has following columns
[login to view URL] Name
[login to view URL] Group
[login to view URL] Id
[login to view URL] Name(database 1/database 2/database 3 etc)
[login to view URL](count of code values from database 1/database 2/database 3 etc)
[login to view URL] 1(Flag for data id whether existing or not Y/N)
[login to view URL] 2(Flag for data id whether existing or not Y/N)